[MDEV-24545] Sequence created by one connection remains invisible to another Created: 2021-01-07  Updated: 2021-04-27  Resolved: 2021-04-27

Status: Closed
Project: MariaDB Server
Component/s: Sequences, Storage Engine - InnoDB
Affects Version/s: 10.3, 10.4, 10.5
Fix Version/s: 10.3.29, 10.4.19, 10.5.10, 10.6.1

Type: Bug Priority: Major
Reporter: Elena Stepanova Assignee: Marko Mäkelä
Resolution: Fixed Votes: 0
Labels: None


 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.

 Comments   
Comment by Anel Husakovic [ 2021-01-07 ]

maybe related to MDEV-23831 ?

Comment by Elena Stepanova [ 2021-01-07 ]

Here is another representation of the described problem.

--source include/have_innodb.inc
 
 
 
CREATE TABLE t1 (a INT) ENGINE=InnoDB;
 
CREATE SEQUENCE s2 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;
 
 
 
RENAME TABLE s2 TO s1;
 
 
 
# Cleanup
 
DROP TABLE t1;
 
DROP SEQUENCE s1;

RENAME TABLE here, contrary to NEXTVAL in the initial test case, causes ER_TABLE_EXISTS_ERROR which seems to be reasonable.
However, InnoDB complains in the error log:

2021-01-07 19:51:17 9 [ERROR] InnoDB: Possible reasons:
 
2021-01-07 19:51:17 9 [ERROR] InnoDB: (1) Table rename would cause two FOREIGN KEY constraints to have the same internal name in case-insensitive comparison.
 
2021-01-07 19:51:17 9 [ERROR] InnoDB: (2) Table `test`.`s1` exists in the InnoDB internal data dictionary though MySQL is trying to rename table `test`.`s2` to it. Have you deleted the .frm file and not used DROP TABLE?
 
2021-01-07 19:51:17 9 [Note] InnoDB: Please refer to https://mariadb.com/kb/en/innodb-troubleshooting/ for how to resolve the issue.
 
2021-01-07 19:51:17 9 [ERROR] InnoDB: If table `test`.`s1` is a temporary table #sql..., then it can be that there are still queries running on the table, and it will be dropped automatically when the queries end. You can drop the orphaned table inside InnoDB by creating an InnoDB table with the same name in another database and copying the .frm file to the current database. Then MySQL thinks the table exists, and DROP TABLE will succeed.

Comment by Elena Stepanova [ 2021-01-07 ]

And now after this commit

commit f0baa8648493a6368f45c6cbf459832d5027aaff
 
Author: Nikita Malyavin
 
Date:   Wed Dec 23 23:59:00 2020 +1000
 
 
 
    ut_ad(err != DB_DUPLICATE_KEY) in row_rename_table_for_mysql

the RENAME above causes the added assertion failure. I don't consider it a regression since the underlying problem obviously existed before.

10.3 9a645dae

 
mysqld: /data/src/10.3/storage/innobase/row/row0mysql.cc:4352: dberr_t row_rename_table_for_mysql(const char*, const char*, trx_t*, bool, bool): Assertion `err != DB_DUPLICATE_KEY' failed.
 
210107 20:07:35 [ERROR] mysqld got signal 6 ;
 
 
 
#7  0x00007fd9c6fbbf36 in __GI___assert_fail (assertion=0x55c00e542005 "err != DB_DUPLICATE_KEY", file=0x55c00e53f288 "/data/src/10.3/storage/innobase/row/row0mysql.cc", line=4352, function=0x55c00e541e88 "dberr_t row_rename_table_for_mysql(const char*, const char*, trx_t*, bool, bool)") at assert.c:101
 
#8  0x000055c00dda2465 in row_rename_table_for_mysql (old_name=0x7fd9c0175330 "test/s1", new_name=0x7fd9c0175130 "test/s2", trx=0x7fd9c0c79188, commit=true, use_fk=true) at /data/src/10.3/storage/innobase/row/row0mysql.cc:4352
 
#9  0x000055c00dc234ce in innobase_rename_table (trx=0x7fd9c0c79188, from=0x7fd9c01762e0 "./test/s1", to=0x7fd9c01764f0 "./test/s2", commit=true, use_fk=true) at /data/src/10.3/storage/innobase/handler/ha_innodb.cc:13402
 
#10 0x000055c00dc0aa8d in ha_innobase::rename_table (this=0x7fd970013be0, from=0x7fd9c01762e0 "./test/s1", to=0x7fd9c01764f0 "./test/s2") at /data/src/10.3/storage/innobase/handler/ha_innodb.cc:13594
 
#11 0x000055c00d9cbed5 in handler::ha_rename_table (this=0x7fd970013be0, from=0x7fd9c01762e0 "./test/s1", to=0x7fd9c01764f0 "./test/s2") at /data/src/10.3/sql/handler.cc:4692
 
#12 0x000055c00d7780d2 in mysql_rename_table (base=0x55c00fe60fb0, old_db=0x7fd970012bd0, old_name=0x7fd9c0176b80, new_db=0x7fd970013238, new_name=0x7fd9c0176b90, flags=0) at /data/src/10.3/sql/sql_table.cc:5564
 
#13 0x000055c00d6c88e3 in do_rename (thd=0x7fd970000d90, ren_table=0x7fd970012bb8, new_db=0x7fd970013238, new_table_name=0x7fd970013248, new_table_alias=0x7fd970013268, skip_error=false) at /data/src/10.3/sql/sql_rename.cc:294
 
#14 0x000055c00d6c8b74 in rename_tables (thd=0x7fd970000d90, table_list=0x7fd970012bb8, skip_error=false) at /data/src/10.3/sql/sql_rename.cc:379
 
#15 0x000055c00d6c834b in mysql_rename_tables (thd=0x7fd970000d90, table_list=0x7fd970012bb8, silent=false) at /data/src/10.3/sql/sql_rename.cc:154
 
#16 0x000055c00d698f80 in mysql_execute_command (thd=0x7fd970000d90) at /data/src/10.3/sql/sql_parse.cc:4238
 
#17 0x000055c00d6a58d0 in mysql_parse (thd=0x7fd970000d90, rawbuf=0x7fd970012ab8 "RENAME TABLE s1 TO s2", length=21, parser_state=0x7fd9c01775c0, is_com_multi=false, is_next_command=false) at /data/src/10.3/sql/sql_parse.cc:7840
 
#18 0x000055c00d692034 in dispatch_command (command=COM_QUERY, thd=0x7fd970000d90, packet=0x7fd970008f11 "RENAME TABLE s1 TO s2", packet_length=21, is_com_multi=false, is_next_command=false) at /data/src/10.3/sql/sql_parse.cc:1852
 
#19 0x000055c00d6909d4 in do_command (thd=0x7fd970000d90) at /data/src/10.3/sql/sql_parse.cc:1398
 
#20 0x000055c00d80eed1 in do_handle_one_connection (connect=0x55c01010d8a0) at /data/src/10.3/sql/sql_connect.cc:1403
 
#21 0x000055c00d80ec2d in handle_one_connection (arg=0x55c01010d8a0) at /data/src/10.3/sql/sql_connect.cc:1308
 
#22 0x000055c00e1daa03 in pfs_spawn_thread (arg=0x55c0100ef620) at /data/src/10.3/storage/perfschema/pfs.cc:1869
 
#23 0x00007fd9c74cb609 in start_thread (arg=<optimized out>) at pthread_create.c:477
 
#24 0x00007fd9c70a7293 in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:95

Comment by Michael Widenius [ 2021-04-06 ]

This is what happens in the code for SELECT NEXTVAL(s1);

  • Sequence table s1 is opened successfully
  • SEQUENCE::read_stored_values() tries to read the row created by con1's CREATE SEQUENCE: 

     >SEQUENCE::read_stored_values
    		 
     | >handler::read_first_row
    		 
     | | >ha_rnd_init
    		 
     | | | | >innobase_get_index
    		 
     | | | | <innobase_get_index
    		 
     | | | | | >push_warning
    		 
     | | | | | | enter: code: 159, msg: InnoDB: insufficient history for index 64
    		 
    ....
    		 
     | >ha_sequence::print_error
    		 
     | | >handler::print_error
    		 
     | | | | | error: error: 1412  message: 'Table definition has changed, please retry transaction'  Flag: 0

    InnoDB should for SEQUENCE tables allow anyone to read from them, even if the table did not exists at transactions start.

Comment by Marko Mäkelä [ 2021-04-26 ]

This seems to fix the problem for me:

diff --git a/storage/innobase/row/row0merge.cc b/storage/innobase/row/row0merge.cc
 
index f77eae9a76d..9e84f9db033 100644
 
--- a/storage/innobase/row/row0merge.cc
 
+++ b/storage/innobase/row/row0merge.cc
 
@@ -4478,7 +4478,7 @@ row_merge_is_index_usable(
 
 	}
 
 
 	return(!index->is_corrupted()
 
-	       && (index->table->is_temporary()
 
+	       && (index->table->is_temporary() || index->table->no_rollback()
 
 		   || index->trx_id == 0
 
 		   || !trx->read_view.is_open()
 
 		   || trx->read_view.changes_visible(

Generated at Thu Feb 08 09:30:48 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.