Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-38986

Unexpected ER_NON_UPDATABLE_TABLE upon executing trigger

    XMLWordPrintable

Details

    Description

      Set to minor because it only affects 10.6 and 10.11, and there is a viable workaround.

      CREATE TABLE t1 (f INT);
      CREATE TABLE t2 (a INT);
      CREATE VIEW v AS SELECT 'x' UNION SELECT 'x' UNION SELECT 'x';
      CREATE TRIGGER tr BEFORE INSERT ON t1 FOR EACH ROW DELETE FROM v;
      --error ER_NON_UPDATABLE_TABLE
      INSERT INTO t1 VALUES (1);
      DROP VIEW v;
      CREATE VIEW v AS SELECT * FROM t2;
      INSERT INTO t1 VALUES (2);
       
      DROP VIEW v;
      DROP TABLE t1, t2;
      

      10.11 14f96a2e08073e7fa4aee5b182ca0111380089ad

      mysqltest: At line 9: query 'INSERT INTO t1 VALUES (2)' failed: ER_NON_UPDATABLE_TABLE (1288): The target table v of the DELETE is not updatable
      

      The first insert in the test case is expected to fail with ER_NON_UPDATABLE_TABLE; but then, we fix the view, but the error still occurs.

      The workaround is FLUSH TABLES after re-creating the view.

      Reproducible on 10.6 and 10.11, but not on 11.4+.

      Attachments

        Activity

          People

            sanja Oleksandr Byelkin
            elenst Elena Stepanova
            Votes:
            0 Vote for this issue
            Watchers:
            1 Start watching this issue

            Dates

              Created:
              Updated:

              Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.