Sequence created by one connection remains invisible to another

--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.

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.