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

Sequence created by one connection remains invisible to another

    XMLWordPrintable

    Details

      Description

      --source include/have_innodb.inc
       
      CREATE TABLE t1 (a INT) ENGINE=InnoDB;
       
      START TRANSACTION;
      SELECT * FROM t1;
       
      --connect (con1,localhost,root,,test)
      CREATE SEQUENCE s1 ENGINE=InnoDB;
      FLUSH TABLES;
      --disconnect con1
       
      --connection default
      --error ER_TABLE_DEF_CHANGED,ER_NO_SUCH_TABLE
      SELECT NEXTVAL(s1);
      COMMIT;
       
      SELECT NEXTVAL(s1);
       
      # Cleanup
      DROP TABLE t1;
      DROP SEQUENCE s1;
      

      In the test case above, connection default throws ER_NO_SUCH_TABLE upon NEXTVAL(s1) twice. The first time is a relatively minor offence. The connection is inside a transaction (with the default transaction isolation level) when the sequence s1 is created by another connection, and is still within the same transaction when it runs NEXTVAL(s1); if it were not a sequence but a select from a normal table, it would have failed anyway, only with ER_TABLE_DEF_CHANGED instead.

      But then, the transaction is committed, the sequence should become visible and readable from, but it is not, it still causes ER_NO_SUCH_TABLE. That's a real problem.

      10.3 08b0b70d

      connection default;
      SELECT NEXTVAL(s1);
      Got one of the listed errors
      COMMIT;
      SELECT NEXTVAL(s1);
      bug.t 'innodb'                           [ fail ]
              Test ended at 2021-01-07 19:37:44
       
      CURRENT_TEST: bug.t
      mysqltest: At line 18: query 'SELECT NEXTVAL(s1)' failed: 1146: Table 'test.s1' doesn't exist
      

      The important part in the test case is FLUSH TABLES issued after CREATE SEQUENCE. Without it, everything works as expected – inside the transaction NEXTVAL causes ER_TABLE_DEF_CHANGED, and outside the transaction it works.

        Attachments

          Activity

            People

            Assignee:
            marko Marko Mäkelä
            Reporter:
            elenst Elena Stepanova
            Votes:
            0 Vote for this issue
            Watchers:
            6 Start watching this issue

              Dates

              Created:
              Updated:
              Resolved:

                Git Integration