Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.3(EOL), 10.4(EOL), 10.5
-
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.
Attachments
Activity
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.
|
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
|
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: 0InnoDB should for SEQUENCE tables allow anyone to read from them, even if the table did not exists at transactions start.
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( |
maybe related to
MDEV-23831?