Assertion `err != DB_DUPLICATE_KEY' failed in row_rename_table_for_mysql & ERROR 1050 (42S01): Table './test/t1' already exists for simple ALTER TABLE on optimized builds (Related to MDEV-25509)
CREATE TABLE t1 (a VARCHAR(10) NOT NULL PRIMARY KEY) ENGINE=InnoDB;
CREATE TABLE t1_fk (a VARCHAR(40), KEY a (a), FULLTEXT KEY(a), CONSTRAINT fk FOREIGN KEY(a) REFERENCES t1 (a) ON UPDATE CASCADE) ENGINE=InnoDB;
ALTER TABLE t1 RENAME TO tm1;
CREATE TABLE t1 (c1 BIGINT NOT NULL, c2 BIGINT NOT NULL, PRIMARY KEY(c1), UNIQUE KEY(c2)) ENGINE=MEMORY;
ALTER TABLE t1 ENGINE=InnoDB;
This testcase would sporadically (4 out of 5 times) crash build 10.6 at 9db14e93acc4ec9023d50686c66dbef7d4d8c15c, and would also crash 10.5 at dd07cfcecd4aabb0aeae9c4f5087f82b4080c1bd. See MDEV-25509.
Now, it does not look healthy on a build from today:
#4 0x00005615328d9715 in row_rename_table_for_mysql (old_name=<optimized out>, old_name@entry=0x14a5185605a0 "test/#sql-alter-1abf9d-4", new_name=<optimized out>, new_name@entry=0x14a5185603a0 "test/t1", trx=trx@entry=0x14a52422e360, commit=commit@entry=true, use_fk=use_fk@entry=true) at /test/10.5_dbg/storage/innobase/row/row0mysql.cc:4340
#5 0x000056153273cc25 in innobase_rename_table (commit=true, to=0x14a518560ff0 "./test/t1", from=0x14a518560de0 "./test/#sql-alter-1abf9d-4", trx=0x14a52422e360) at /test/10.5_dbg/storage/innobase/handler/ha_innodb.cc:13533
#6 ha_innobase::rename_table (this=<optimized out>, from=0x14a518560de0 "./test/#sql-alter-1abf9d-4", to=0x14a518560ff0 "./test/t1") at /test/10.5_dbg/storage/innobase/handler/ha_innodb.cc:13726
#7 0x0000561532346fac in handler::ha_rename_table (this=this@entry=0x14a4d8015bc8, from=from@entry=0x14a518560de0 "./test/#sql-alter-1abf9d-4", to=to@entry=0x14a518560ff0 "./test/t1") at /test/10.5_dbg/sql/handler.cc:4972
#8 0x0000561532139bff in mysql_rename_table (base=base@entry=0x56153446f688, old_db=old_db@entry=0x14a5185628f0, old_name=old_name@entry=0x14a518562920, new_db=new_db@entry=0x14a5185628f0, new_name=new_name@entry=0x14a518562910, flags=flags@entry=1) at /test/10.5_dbg/sql/sql_table.cc:5892
#9 0x000056153214fa69 in mysql_alter_table (thd=thd@entry=0x14a4d8000db8, new_db=new_db@entry=0x14a4d8005810, new_name=new_name@entry=0x14a4d8005c30, create_info=create_info@entry=0x14a5185634c0, table_list=<optimized out>, table_list@entry=0x14a4d8013dc0, alter_info=alter_info@entry=0x14a5185633f0, order_num=0, order=0x0, ignore=false, if_exists=false) at /test/10.5_dbg/sql/sql_table.cc:11043
#10 0x00005615321d4165 in Sql_cmd_alter_table::execute (this=<optimized out>, thd=0x14a4d8000db8) at /test/10.5_dbg/sql/structs.h:559
#11 0x000056153207766d in mysql_execute_command (thd=thd@entry=0x14a4d8000db8) at /test/10.5_dbg/sql/sql_parse.cc:6055
#12 0x000056153205d34f in mysql_parse (thd=thd@entry=0x14a4d8000db8, rawbuf=<optimized out>, length=<optimized out>, parser_state=parser_state@entry=0x14a5185643d0, is_com_multi=is_com_multi@entry=false, is_next_command=is_next_command@entry=false) at /test/10.5_dbg/sql/sql_parse.cc:8099
#13 0x000056153206c565 in dispatch_command (command=command@entry=COM_QUERY, thd=thd@entry=0x14a4d8000db8, packet=packet@entry=0x14a4d800b499 "", packet_length=packet_length@entry=28, is_com_multi=is_com_multi@entry=false, is_next_command=is_next_command@entry=false) at /test/10.5_dbg/sql/sql_class.h:1270
#14 0x000056153206fdea in do_command (thd=0x14a4d8000db8) at /test/10.5_dbg/sql/sql_parse.cc:1370
#15 0x00005615321cc379 in do_handle_one_connection (connect=<optimized out>, connect@entry=0x561534f005a8, put_in_cache=put_in_cache@entry=true) at /test/10.5_dbg/sql/sql_connect.cc:1410
#16 0x00005615321cca7d in handle_one_connection (arg=arg@entry=0x561534f005a8) at /test/10.5_dbg/sql/sql_connect.cc:1312
#17 0x0000561532685885 in pfs_spawn_thread (arg=0x561534e333c8) at /test/10.5_dbg/storage/perfschema/pfs.cc:2201
#18 0x000014a52d8e6609 in start_thread (arg=<optimized out>) at pthread_create.c:477
#19 0x000014a52d4d5293 in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:95
#4 0x000055db8be202d1 in row_rename_table_for_mysql (old_name=<optimized out>, old_name@entry=0x1459883f65e0 "test/#sql-alter-1ad78c-4", new_name=<optimized out>, new_name@entry=0x1459883f63e0 "test/t1", trx=trx@entry=0x1459896b62a0, commit=commit@entry=true, use_fk=use_fk@entry=true) at /test/10.6_dbg/storage/innobase/row/row0mysql.cc:4255
#5 0x000055db8bc94ab3 in innobase_rename_table (commit=true, to=0x1459883f7030 "./test/t1", from=0x1459883f6e20 "./test/#sql-alter-1ad78c-4", trx=0x1459896b62a0) at /test/10.6_dbg/storage/innobase/handler/ha_innodb.cc:13281
#6 ha_innobase::rename_table (this=<optimized out>, from=0x1459883f6e20 "./test/#sql-alter-1ad78c-4", to=0x1459883f7030 "./test/t1") at /test/10.6_dbg/storage/innobase/handler/ha_innodb.cc:13474
#7 0x000055db8b8a78de in handler::ha_rename_table (this=this@entry=0x145934015ec0, from=from@entry=0x1459883f6e20 "./test/#sql-alter-1ad78c-4", to=to@entry=0x1459883f7030 "./test/t1") at /test/10.6_dbg/sql/handler.cc:4980
#8 0x000055db8b698edb in mysql_rename_table (base=base@entry=0x55db8e8611d8, old_db=old_db@entry=0x1459883f8950, old_name=old_name@entry=0x1459883f8980, new_db=new_db@entry=0x1459883f8950, new_name=new_name@entry=0x1459883f8970, flags=flags@entry=1) at /test/10.6_dbg/sql/sql_table.cc:5891
#9 0x000055db8b6af0d4 in mysql_alter_table (thd=thd@entry=0x145934000db8, new_db=new_db@entry=0x1459340059d8, new_name=new_name@entry=0x145934005e00, create_info=create_info@entry=0x1459883f9540, table_list=<optimized out>, table_list@entry=0x1459340140b0, alter_info=alter_info@entry=0x1459883f9450, order_num=0, order=0x0, ignore=false, if_exists=false) at /test/10.6_dbg/sql/sql_table.cc:11145
#10 0x000055db8b73270f in Sql_cmd_alter_table::execute (this=<optimized out>, thd=0x145934000db8) at /test/10.6_dbg/sql/structs.h:564
#11 0x000055db8b5d79a1 in mysql_execute_command (thd=thd@entry=0x145934000db8) at /test/10.6_dbg/sql/sql_parse.cc:5986
#12 0x000055db8b5bdf81 in mysql_parse (thd=thd@entry=0x145934000db8, rawbuf=<optimized out>, length=<optimized out>, parser_state=parser_state@entry=0x1459883fa410) at /test/10.6_dbg/sql/sql_parse.cc:8018
#13 0x000055db8b5ccdb3 in dispatch_command (command=command@entry=COM_QUERY, thd=thd@entry=0x145934000db8, packet=packet@entry=0x14593400b789 "", packet_length=packet_length@entry=28, blocking=blocking@entry=true) at /test/10.6_dbg/sql/sql_class.h:1332
#14 0x000055db8b5d01a6 in do_command (thd=0x145934000db8, blocking=blocking@entry=true) at /test/10.6_dbg/sql/sql_parse.cc:1406
#15 0x000055db8b72aa6a in do_handle_one_connection (connect=<optimized out>, connect@entry=0x55db8ed4d9b8, put_in_cache=put_in_cache@entry=true) at /test/10.6_dbg/sql/sql_connect.cc:1410
#16 0x000055db8b72b06f in handle_one_connection (arg=arg@entry=0x55db8ed4d9b8) at /test/10.6_dbg/sql/sql_connect.cc:1312
#17 0x000055db8bbdd2ca in pfs_spawn_thread (arg=0x55db8ec371f8) at /test/10.6_dbg/storage/perfschema/pfs.cc:2201
#18 0x000014598c4c7609 in start_thread (arg=<optimized out>) at pthread_create.c:477
#19 0x000014598c0b6293 in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:95
Roel Van de Paar
added a comment - - edited For completeness, here are the stacks it used to crash with on older builds (i.e. MDEV-25509 with the testcase above):
10.5.10 dd07cfcecd4aabb0aeae9c4f5087f82b4080c1bd (Debug)
mysqld: /test/10.5_dbg/storage/innobase/row/row0mysql.cc:4340: dberr_t row_rename_table_for_mysql(const char*, const char*, trx_t*, bool, bool): Assertion `err != DB_DUPLICATE_KEY' failed.
10.5.10 dd07cfcecd4aabb0aeae9c4f5087f82b4080c1bd (Debug)
Core was generated by `/test/MD270421-mariadb-10.5.10-linux-x86_64-dbg/bin/mysqld --no-defaults --core'.
Program terminated with signal SIGABRT, Aborted.
#0 __GI_raise (sig=sig@entry=6) at ../sysdeps/unix/sysv/linux/raise.c:50
[Current thread is 1 (Thread 0x14a518565700 (LWP 1755010))]
(gdb) bt
#0 __GI_raise (sig=sig@entry=6) at ../sysdeps/unix/sysv/linux/raise.c:50
#1 0x000014a52d3d8859 in __GI_abort () at abort.c:79
#2 0x000014a52d3d8729 in __assert_fail_base (fmt=0x14a52d56e588 "%s%s%s:%u: %s%sAssertion `%s' failed.\n%n", assertion=0x561532fea9ae "err != DB_DUPLICATE_KEY", file=0x561532fead18 "/test/10.5_dbg/storage/innobase/row/row0mysql.cc", line=4340, function=<optimized out>) at assert.c:92
#3 0x000014a52d3e9f36 in __GI___assert_fail (assertion=assertion@entry=0x561532fea9ae "err != DB_DUPLICATE_KEY", file=file@entry=0x561532fead18 "/test/10.5_dbg/storage/innobase/row/row0mysql.cc", line=line@entry=4340, function=function@entry=0x561532fec3e0 "dberr_t row_rename_table_for_mysql(const char*, const char*, trx_t*, bool, bool)") at assert.c:101
#4 0x00005615328d9715 in row_rename_table_for_mysql (old_name=<optimized out>, old_name@entry=0x14a5185605a0 "test/#sql-alter-1abf9d-4", new_name=<optimized out>, new_name@entry=0x14a5185603a0 "test/t1", trx=trx@entry=0x14a52422e360, commit=commit@entry=true, use_fk=use_fk@entry=true) at /test/10.5_dbg/storage/innobase/row/row0mysql.cc:4340
#5 0x000056153273cc25 in innobase_rename_table (commit=true, to=0x14a518560ff0 "./test/t1", from=0x14a518560de0 "./test/#sql-alter-1abf9d-4", trx=0x14a52422e360) at /test/10.5_dbg/storage/innobase/handler/ha_innodb.cc:13533
#6 ha_innobase::rename_table (this=<optimized out>, from=0x14a518560de0 "./test/#sql-alter-1abf9d-4", to=0x14a518560ff0 "./test/t1") at /test/10.5_dbg/storage/innobase/handler/ha_innodb.cc:13726
#7 0x0000561532346fac in handler::ha_rename_table (this=this@entry=0x14a4d8015bc8, from=from@entry=0x14a518560de0 "./test/#sql-alter-1abf9d-4", to=to@entry=0x14a518560ff0 "./test/t1") at /test/10.5_dbg/sql/handler.cc:4972
#8 0x0000561532139bff in mysql_rename_table (base=base@entry=0x56153446f688, old_db=old_db@entry=0x14a5185628f0, old_name=old_name@entry=0x14a518562920, new_db=new_db@entry=0x14a5185628f0, new_name=new_name@entry=0x14a518562910, flags=flags@entry=1) at /test/10.5_dbg/sql/sql_table.cc:5892
#9 0x000056153214fa69 in mysql_alter_table (thd=thd@entry=0x14a4d8000db8, new_db=new_db@entry=0x14a4d8005810, new_name=new_name@entry=0x14a4d8005c30, create_info=create_info@entry=0x14a5185634c0, table_list=<optimized out>, table_list@entry=0x14a4d8013dc0, alter_info=alter_info@entry=0x14a5185633f0, order_num=0, order=0x0, ignore=false, if_exists=false) at /test/10.5_dbg/sql/sql_table.cc:11043
#10 0x00005615321d4165 in Sql_cmd_alter_table::execute (this=<optimized out>, thd=0x14a4d8000db8) at /test/10.5_dbg/sql/structs.h:559
#11 0x000056153207766d in mysql_execute_command (thd=thd@entry=0x14a4d8000db8) at /test/10.5_dbg/sql/sql_parse.cc:6055
#12 0x000056153205d34f in mysql_parse (thd=thd@entry=0x14a4d8000db8, rawbuf=<optimized out>, length=<optimized out>, parser_state=parser_state@entry=0x14a5185643d0, is_com_multi=is_com_multi@entry=false, is_next_command=is_next_command@entry=false) at /test/10.5_dbg/sql/sql_parse.cc:8099
#13 0x000056153206c565 in dispatch_command (command=command@entry=COM_QUERY, thd=thd@entry=0x14a4d8000db8, packet=packet@entry=0x14a4d800b499 "", packet_length=packet_length@entry=28, is_com_multi=is_com_multi@entry=false, is_next_command=is_next_command@entry=false) at /test/10.5_dbg/sql/sql_class.h:1270
#14 0x000056153206fdea in do_command (thd=0x14a4d8000db8) at /test/10.5_dbg/sql/sql_parse.cc:1370
#15 0x00005615321cc379 in do_handle_one_connection (connect=<optimized out>, connect@entry=0x561534f005a8, put_in_cache=put_in_cache@entry=true) at /test/10.5_dbg/sql/sql_connect.cc:1410
#16 0x00005615321cca7d in handle_one_connection (arg=arg@entry=0x561534f005a8) at /test/10.5_dbg/sql/sql_connect.cc:1312
#17 0x0000561532685885 in pfs_spawn_thread (arg=0x561534e333c8) at /test/10.5_dbg/storage/perfschema/pfs.cc:2201
#18 0x000014a52d8e6609 in start_thread (arg=<optimized out>) at pthread_create.c:477
#19 0x000014a52d4d5293 in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:95
10.6.1 9db14e93acc4ec9023d50686c66dbef7d4d8c15c (Debug)
mysqld: /test/10.6_dbg/storage/innobase/row/row0mysql.cc:4255: dberr_t row_rename_table_for_mysql(const char*, const char*, trx_t*, bool, bool): Assertion `err != DB_DUPLICATE_KEY' failed.
10.6.1 9db14e93acc4ec9023d50686c66dbef7d4d8c15c (Debug)
Core was generated by `/test/MD270421-mariadb-10.6.1-linux-x86_64-dbg/bin/mysqld --no-defaults --core-'.
Program terminated with signal SIGABRT, Aborted.
#0 __GI_raise (sig=sig@entry=6) at ../sysdeps/unix/sysv/linux/raise.c:50
[Current thread is 1 (Thread 0x1459883fb700 (LWP 1760819))]
(gdb) bt
#0 __GI_raise (sig=sig@entry=6) at ../sysdeps/unix/sysv/linux/raise.c:50
#1 0x000014598bfb9859 in __GI_abort () at abort.c:79
#2 0x000014598bfb9729 in __assert_fail_base (fmt=0x14598c14f588 "%s%s%s:%u: %s%sAssertion `%s' failed.\n%n", assertion=0x55db8c50392d "err != DB_DUPLICATE_KEY", file=0x55db8c503c90 "/test/10.6_dbg/storage/innobase/row/row0mysql.cc", line=4255, function=<optimized out>) at assert.c:92
#3 0x000014598bfcaf36 in __GI___assert_fail (assertion=assertion@entry=0x55db8c50392d "err != DB_DUPLICATE_KEY", file=file@entry=0x55db8c503c90 "/test/10.6_dbg/storage/innobase/row/row0mysql.cc", line=line@entry=4255, function=function@entry=0x55db8c505390 "dberr_t row_rename_table_for_mysql(const char*, const char*, trx_t*, bool, bool)") at assert.c:101
#4 0x000055db8be202d1 in row_rename_table_for_mysql (old_name=<optimized out>, old_name@entry=0x1459883f65e0 "test/#sql-alter-1ad78c-4", new_name=<optimized out>, new_name@entry=0x1459883f63e0 "test/t1", trx=trx@entry=0x1459896b62a0, commit=commit@entry=true, use_fk=use_fk@entry=true) at /test/10.6_dbg/storage/innobase/row/row0mysql.cc:4255
#5 0x000055db8bc94ab3 in innobase_rename_table (commit=true, to=0x1459883f7030 "./test/t1", from=0x1459883f6e20 "./test/#sql-alter-1ad78c-4", trx=0x1459896b62a0) at /test/10.6_dbg/storage/innobase/handler/ha_innodb.cc:13281
#6 ha_innobase::rename_table (this=<optimized out>, from=0x1459883f6e20 "./test/#sql-alter-1ad78c-4", to=0x1459883f7030 "./test/t1") at /test/10.6_dbg/storage/innobase/handler/ha_innodb.cc:13474
#7 0x000055db8b8a78de in handler::ha_rename_table (this=this@entry=0x145934015ec0, from=from@entry=0x1459883f6e20 "./test/#sql-alter-1ad78c-4", to=to@entry=0x1459883f7030 "./test/t1") at /test/10.6_dbg/sql/handler.cc:4980
#8 0x000055db8b698edb in mysql_rename_table (base=base@entry=0x55db8e8611d8, old_db=old_db@entry=0x1459883f8950, old_name=old_name@entry=0x1459883f8980, new_db=new_db@entry=0x1459883f8950, new_name=new_name@entry=0x1459883f8970, flags=flags@entry=1) at /test/10.6_dbg/sql/sql_table.cc:5891
#9 0x000055db8b6af0d4 in mysql_alter_table (thd=thd@entry=0x145934000db8, new_db=new_db@entry=0x1459340059d8, new_name=new_name@entry=0x145934005e00, create_info=create_info@entry=0x1459883f9540, table_list=<optimized out>, table_list@entry=0x1459340140b0, alter_info=alter_info@entry=0x1459883f9450, order_num=0, order=0x0, ignore=false, if_exists=false) at /test/10.6_dbg/sql/sql_table.cc:11145
#10 0x000055db8b73270f in Sql_cmd_alter_table::execute (this=<optimized out>, thd=0x145934000db8) at /test/10.6_dbg/sql/structs.h:564
#11 0x000055db8b5d79a1 in mysql_execute_command (thd=thd@entry=0x145934000db8) at /test/10.6_dbg/sql/sql_parse.cc:5986
#12 0x000055db8b5bdf81 in mysql_parse (thd=thd@entry=0x145934000db8, rawbuf=<optimized out>, length=<optimized out>, parser_state=parser_state@entry=0x1459883fa410) at /test/10.6_dbg/sql/sql_parse.cc:8018
#13 0x000055db8b5ccdb3 in dispatch_command (command=command@entry=COM_QUERY, thd=thd@entry=0x145934000db8, packet=packet@entry=0x14593400b789 "", packet_length=packet_length@entry=28, blocking=blocking@entry=true) at /test/10.6_dbg/sql/sql_class.h:1332
#14 0x000055db8b5d01a6 in do_command (thd=0x145934000db8, blocking=blocking@entry=true) at /test/10.6_dbg/sql/sql_parse.cc:1406
#15 0x000055db8b72aa6a in do_handle_one_connection (connect=<optimized out>, connect@entry=0x55db8ed4d9b8, put_in_cache=put_in_cache@entry=true) at /test/10.6_dbg/sql/sql_connect.cc:1410
#16 0x000055db8b72b06f in handle_one_connection (arg=arg@entry=0x55db8ed4d9b8) at /test/10.6_dbg/sql/sql_connect.cc:1312
#17 0x000055db8bbdd2ca in pfs_spawn_thread (arg=0x55db8ec371f8) at /test/10.6_dbg/storage/perfschema/pfs.cc:2201
#18 0x000014598c4c7609 in start_thread (arg=<optimized out>) at pthread_create.c:477
#19 0x000014598c0b6293 in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:95
#4 0x000056069ab19cbe in row_rename_table_for_mysql (old_name=<optimized out>, old_name@entry=0x146cf00fd5e0 "test/#sql-alter-2f250d-4", new_name=<optimized out>, new_name@entry=0x146cf00fd3e0 "test/t", trx=trx@entry=0x146cf01bc290, commit=commit@entry=true, use_fk=use_fk@entry=true) at /test/10.6_dbg/storage/innobase/row/row0mysql.cc:4195
#5 0x000056069a98f432 in innobase_rename_table (commit=true, to=0x146cf00fe030 "./test/t", from=0x146cf00fde20 "./test/#sql-alter-2f250d-4", trx=0x146cf01bc290) at /test/10.6_dbg/storage/innobase/handler/ha_innodb.cc:13270
#6 ha_innobase::rename_table (this=<optimized out>, from=0x146cf00fde20 "./test/#sql-alter-2f250d-4", to=0x146cf00fe030 "./test/t") at /test/10.6_dbg/storage/innobase/handler/ha_innodb.cc:13457
#7 0x000056069a5a2290 in handler::ha_rename_table (this=this@entry=0x146c98015900, from=from@entry=0x146cf00fde20 "./test/#sql-alter-2f250d-4", to=to@entry=0x146cf00fe030 "./test/t") at /test/10.6_dbg/sql/handler.cc:4980
#8 0x000056069a393199 in mysql_rename_table (base=base@entry=0x56069cf66108, old_db=old_db@entry=0x146cf00ff950, old_name=old_name@entry=0x146cf00ff980, new_db=new_db@entry=0x146cf00ff950, new_name=new_name@entry=0x146cf00ff970, flags=flags@entry=1) at /test/10.6_dbg/sql/sql_table.cc:5879
#9 0x000056069a3a9428 in mysql_alter_table (thd=thd@entry=0x146c98000db8, new_db=new_db@entry=0x146c980059e0, new_name=new_name@entry=0x146c98005e08, create_info=create_info@entry=0x146cf0100540, table_list=<optimized out>, table_list@entry=0x146c980140c0, alter_info=alter_info@entry=0x146cf0100450, order_num=0, order=0x0, ignore=false, if_exists=false) at /test/10.6_dbg/sql/sql_table.cc:11141
#10 0x000056069a42ca91 in Sql_cmd_alter_table::execute (this=<optimized out>, thd=0x146c98000db8) at /test/10.6_dbg/sql/structs.h:564
#11 0x000056069a2d1a4b in mysql_execute_command (thd=thd@entry=0x146c98000db8) at /test/10.6_dbg/sql/sql_parse.cc:5986
#12 0x000056069a2b802b in mysql_parse (thd=thd@entry=0x146c98000db8, rawbuf=<optimized out>, length=<optimized out>, parser_state=parser_state@entry=0x146cf0101410) at /test/10.6_dbg/sql/sql_parse.cc:8018
#13 0x000056069a2c6e5d in dispatch_command (command=command@entry=COM_QUERY, thd=thd@entry=0x146c98000db8, packet=packet@entry=0x146c9800b799 "", packet_length=packet_length@entry=27, blocking=blocking@entry=true) at /test/10.6_dbg/sql/sql_class.h:1333
#14 0x000056069a2ca250 in do_command (thd=0x146c98000db8, blocking=blocking@entry=true) at /test/10.6_dbg/sql/sql_parse.cc:1406
#15 0x000056069a424dec in do_handle_one_connection (connect=<optimized out>, connect@entry=0x56069d4529b8, put_in_cache=put_in_cache@entry=true) at /test/10.6_dbg/sql/sql_connect.cc:1410
#16 0x000056069a4253f1 in handle_one_connection (arg=arg@entry=0x56069d4529b8) at /test/10.6_dbg/sql/sql_connect.cc:1312
#17 0x000056069a8d7cee in pfs_spawn_thread (arg=0x56069d33c2a8) at /test/10.6_dbg/storage/perfschema/pfs.cc:2201
#18 0x0000146cf1873609 in start_thread (arg=<optimized out>) at pthread_create.c:477
#19 0x0000146cf1462293 in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:95
CREATE TABLE t1 (a VARCHAR(10) NOT NULL PRIMARY KEY) ENGINE=InnoDB;
CREATE TABLE t1_fk (a VARCHAR(40), KEY a (a), FULLTEXT KEY(a), CONSTRAINT fk FOREIGN KEY(a) REFERENCES t1 (a) ON UPDATE CASCADE) ENGINE=InnoDB;
--error ER_ROW_IS_REFERENCED_2
ALTER TABLE t1 RENAME TO tm1, ALGORITHM=COPY;
CREATE TABLE t1 (c1 BIGINT NOT NULL, c2 BIGINT NOT NULL, PRIMARY KEY(c1), UNIQUE KEY(c2)) ENGINE=MEMORY;
ALTER TABLE t1 ENGINE=InnoDB, ALGORITHM=COPY;
DROP TABLE t1, tm1;
Test case fail in 10.5.9 also. The problem is that InnoDB fails to rename the table using copy algorithm.
ALTER TABLE t1 RENAME TO tm1, ALGORITHM=COPY;
In copy algorithm, there are 3 steps:
1) Create #sql-alter (read and write the rows one by one)
2) Rename #sql-alter to tm1
3) Delete table t1; (old table)
In our case, InnoDB fails to delete the table (step 3) due to existing foreign key
constraint. So InnoDB fails to remove t1.ibd from InnoDB data dictionary and file(t1.ibd)
Consecutive alter ALTER TABLE t1 ENGINE=InnoDB, ALGORITHM=COPY
does the engine conversion and tries to rename the table to t1. It leads to
DB_DUPLICATE_KEY While updating the INNODB_SYS_TABLES with the tablename(t1)
in row_rename_table_for_mysql()
ut_ad(err != DB_DUPLICATE_KEY) in row_rename_table_for_mysql
It basically crashes the debug server if the table rename fails
with DB_DUPLICATE_KEY. Assertion failure happens from 10.5.9 onwards
due to the commit f0baa8648493a6368f45c6cbf459832d5027aaff
Why test case didn't fail before 10.5:
======================================
Till 10.4, InnoDB does the copy alter rename operation like:
1) Create #sql-alter (read and write the rows one by one)
2) Rename t1 to #sql-alter_old
3) Rename #sql-alter to tm1
4) Delete table #sql-alter_old
It does an extra rename operation(step 2). So it does make sure that t1 doesn't
exist in InnoDB dictionary.
The following commit does optimization in 10.5.0:
commit 043a3a0176e220e4648521b2e89881c5261e6dd4
Author: Monty <monty@mariadb.org>
Date: Wed May 8 23:39:29 2019 +0300
Avoid not needed renames in ALTER TABLE
Removed not needed table renames when doing ALTER TABLE when engine
changes and both of the following is true:
- Either new or old engine does not store the table in files
- Neither old or new engine uses files from another engine
We also skip renames when ALTER TABLE does an explicit rename
This improves performance, especially for engines where rename is
a slow operation (like the upcoming S3 engine)
So this rename copy alter operation failure should happen from 10.5.0 onwards.
Solution:
==========
IMO, InnoDB should avoid failing while deleting the tablespace during copy
rename operation.
Removing the blocker label from issue. This issue should exist from 10.5.0 onwards. We can't blame assert which was added in 10.5.9.
Thirunarayanan Balathandayuthapani
added a comment - - edited Test case:
--source include/have_innodb.inc
CREATE TABLE t1 (a VARCHAR(10) NOT NULL PRIMARY KEY) ENGINE=InnoDB;
CREATE TABLE t1_fk (a VARCHAR(40), KEY a (a), FULLTEXT KEY(a), CONSTRAINT fk FOREIGN KEY(a) REFERENCES t1 (a) ON UPDATE CASCADE) ENGINE=InnoDB;
--error ER_ROW_IS_REFERENCED_2
ALTER TABLE t1 RENAME TO tm1, ALGORITHM=COPY;
CREATE TABLE t1 (c1 BIGINT NOT NULL, c2 BIGINT NOT NULL, PRIMARY KEY(c1), UNIQUE KEY(c2)) ENGINE=MEMORY;
ALTER TABLE t1 ENGINE=InnoDB, ALGORITHM=COPY;
DROP TABLE t1, tm1;
Test case fail in 10.5.9 also. The problem is that InnoDB fails to rename the table using copy algorithm.
ALTER TABLE t1 RENAME TO tm1, ALGORITHM=COPY;
In copy algorithm, there are 3 steps:
1) Create #sql-alter (read and write the rows one by one)
2) Rename #sql-alter to tm1
3) Delete table t1; (old table)
In our case, InnoDB fails to delete the table (step 3) due to existing foreign key
constraint. So InnoDB fails to remove t1.ibd from InnoDB data dictionary and file(t1.ibd)
Consecutive alter ALTER TABLE t1 ENGINE=InnoDB, ALGORITHM=COPY
does the engine conversion and tries to rename the table to t1. It leads to
DB_DUPLICATE_KEY While updating the INNODB_SYS_TABLES with the tablename(t1)
in row_rename_table_for_mysql()
In 10.5.9, InnoDB added the following commit:
commit f0baa8648493a6368f45c6cbf459832d5027aaff
Author: Nikita Malyavin <nikitamalyavin@gmail.com>
Date: Wed Dec 23 23:59:00 2020 +1000
ut_ad(err != DB_DUPLICATE_KEY) in row_rename_table_for_mysql
It basically crashes the debug server if the table rename fails
with DB_DUPLICATE_KEY. Assertion failure happens from 10.5.9 onwards
due to the commit f0baa8648493a6368f45c6cbf459832d5027aaff
Why test case didn't fail before 10.5:
======================================
Till 10.4, InnoDB does the copy alter rename operation like:
1) Create #sql-alter (read and write the rows one by one)
2) Rename t1 to #sql-alter_old
3) Rename #sql-alter to tm1
4) Delete table #sql-alter_old
It does an extra rename operation(step 2). So it does make sure that t1 doesn't
exist in InnoDB dictionary.
The following commit does optimization in 10.5.0:
commit 043a3a0176e220e4648521b2e89881c5261e6dd4
Author: Monty <monty@mariadb.org>
Date: Wed May 8 23:39:29 2019 +0300
Avoid not needed renames in ALTER TABLE
Removed not needed table renames when doing ALTER TABLE when engine
changes and both of the following is true:
- Either new or old engine does not store the table in files
- Neither old or new engine uses files from another engine
We also skip renames when ALTER TABLE does an explicit rename
This improves performance, especially for engines where rename is
a slow operation (like the upcoming S3 engine)
So this rename copy alter operation failure should happen from 10.5.0 onwards.
Solution:
==========
IMO, InnoDB should avoid failing while deleting the tablespace during copy
rename operation.
Removing the blocker label from issue. This issue should exist from 10.5.0 onwards. We can't blame assert which was added in 10.5.9.
After discussing with Roel, we're increasing the priority to Critical due to data corruption on optimized builds
Thirunarayanan Balathandayuthapani
added a comment - After discussing with Roel , we're increasing the priority to Critical due to data corruption on optimized builds
The fix (disabling the handler::rename_table optimization if FOREIGN KEY constraints would exist) seems acceptable to me. monty, can you please do the final review, because it is changing some code that you had modified?
Marko Mäkelä
added a comment - The fix (disabling the handler::rename_table optimization if FOREIGN KEY constraints would exist) seems acceptable to me.
monty , can you please do the final review, because it is changing some code that you had modified?
For completeness, here are the stacks it used to crash with on older builds (i.e.
MDEV-25509with the testcase above):10.5.10 dd07cfcecd4aabb0aeae9c4f5087f82b4080c1bd (Debug)
mysqld: /test/10.5_dbg/storage/innobase/row/row0mysql.cc:4340: dberr_t row_rename_table_for_mysql(const char*, const char*, trx_t*, bool, bool): Assertion `err != DB_DUPLICATE_KEY' failed.
10.5.10 dd07cfcecd4aabb0aeae9c4f5087f82b4080c1bd (Debug)
Core was generated by `/test/MD270421-mariadb-10.5.10-linux-x86_64-dbg/bin/mysqld --no-defaults --core'.
Program terminated with signal SIGABRT, Aborted.
#0 __GI_raise (sig=sig@entry=6) at ../sysdeps/unix/sysv/linux/raise.c:50
[Current thread is 1 (Thread 0x14a518565700 (LWP 1755010))]
(gdb) bt
#0 __GI_raise (sig=sig@entry=6) at ../sysdeps/unix/sysv/linux/raise.c:50
#1 0x000014a52d3d8859 in __GI_abort () at abort.c:79
#2 0x000014a52d3d8729 in __assert_fail_base (fmt=0x14a52d56e588 "%s%s%s:%u: %s%sAssertion `%s' failed.\n%n", assertion=0x561532fea9ae "err != DB_DUPLICATE_KEY", file=0x561532fead18 "/test/10.5_dbg/storage/innobase/row/row0mysql.cc", line=4340, function=<optimized out>) at assert.c:92
#3 0x000014a52d3e9f36 in __GI___assert_fail (assertion=assertion@entry=0x561532fea9ae "err != DB_DUPLICATE_KEY", file=file@entry=0x561532fead18 "/test/10.5_dbg/storage/innobase/row/row0mysql.cc", line=line@entry=4340, function=function@entry=0x561532fec3e0 "dberr_t row_rename_table_for_mysql(const char*, const char*, trx_t*, bool, bool)") at assert.c:101
#4 0x00005615328d9715 in row_rename_table_for_mysql (old_name=<optimized out>, old_name@entry=0x14a5185605a0 "test/#sql-alter-1abf9d-4", new_name=<optimized out>, new_name@entry=0x14a5185603a0 "test/t1", trx=trx@entry=0x14a52422e360, commit=commit@entry=true, use_fk=use_fk@entry=true) at /test/10.5_dbg/storage/innobase/row/row0mysql.cc:4340
#5 0x000056153273cc25 in innobase_rename_table (commit=true, to=0x14a518560ff0 "./test/t1", from=0x14a518560de0 "./test/#sql-alter-1abf9d-4", trx=0x14a52422e360) at /test/10.5_dbg/storage/innobase/handler/ha_innodb.cc:13533
#6 ha_innobase::rename_table (this=<optimized out>, from=0x14a518560de0 "./test/#sql-alter-1abf9d-4", to=0x14a518560ff0 "./test/t1") at /test/10.5_dbg/storage/innobase/handler/ha_innodb.cc:13726
#7 0x0000561532346fac in handler::ha_rename_table (this=this@entry=0x14a4d8015bc8, from=from@entry=0x14a518560de0 "./test/#sql-alter-1abf9d-4", to=to@entry=0x14a518560ff0 "./test/t1") at /test/10.5_dbg/sql/handler.cc:4972
#8 0x0000561532139bff in mysql_rename_table (base=base@entry=0x56153446f688, old_db=old_db@entry=0x14a5185628f0, old_name=old_name@entry=0x14a518562920, new_db=new_db@entry=0x14a5185628f0, new_name=new_name@entry=0x14a518562910, flags=flags@entry=1) at /test/10.5_dbg/sql/sql_table.cc:5892
#9 0x000056153214fa69 in mysql_alter_table (thd=thd@entry=0x14a4d8000db8, new_db=new_db@entry=0x14a4d8005810, new_name=new_name@entry=0x14a4d8005c30, create_info=create_info@entry=0x14a5185634c0, table_list=<optimized out>, table_list@entry=0x14a4d8013dc0, alter_info=alter_info@entry=0x14a5185633f0, order_num=0, order=0x0, ignore=false, if_exists=false) at /test/10.5_dbg/sql/sql_table.cc:11043
#10 0x00005615321d4165 in Sql_cmd_alter_table::execute (this=<optimized out>, thd=0x14a4d8000db8) at /test/10.5_dbg/sql/structs.h:559
#11 0x000056153207766d in mysql_execute_command (thd=thd@entry=0x14a4d8000db8) at /test/10.5_dbg/sql/sql_parse.cc:6055
#12 0x000056153205d34f in mysql_parse (thd=thd@entry=0x14a4d8000db8, rawbuf=<optimized out>, length=<optimized out>, parser_state=parser_state@entry=0x14a5185643d0, is_com_multi=is_com_multi@entry=false, is_next_command=is_next_command@entry=false) at /test/10.5_dbg/sql/sql_parse.cc:8099
#13 0x000056153206c565 in dispatch_command (command=command@entry=COM_QUERY, thd=thd@entry=0x14a4d8000db8, packet=packet@entry=0x14a4d800b499 "", packet_length=packet_length@entry=28, is_com_multi=is_com_multi@entry=false, is_next_command=is_next_command@entry=false) at /test/10.5_dbg/sql/sql_class.h:1270
#14 0x000056153206fdea in do_command (thd=0x14a4d8000db8) at /test/10.5_dbg/sql/sql_parse.cc:1370
#15 0x00005615321cc379 in do_handle_one_connection (connect=<optimized out>, connect@entry=0x561534f005a8, put_in_cache=put_in_cache@entry=true) at /test/10.5_dbg/sql/sql_connect.cc:1410
#16 0x00005615321cca7d in handle_one_connection (arg=arg@entry=0x561534f005a8) at /test/10.5_dbg/sql/sql_connect.cc:1312
#17 0x0000561532685885 in pfs_spawn_thread (arg=0x561534e333c8) at /test/10.5_dbg/storage/perfschema/pfs.cc:2201
#18 0x000014a52d8e6609 in start_thread (arg=<optimized out>) at pthread_create.c:477
#19 0x000014a52d4d5293 in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:95
10.6.1 9db14e93acc4ec9023d50686c66dbef7d4d8c15c (Debug)
mysqld: /test/10.6_dbg/storage/innobase/row/row0mysql.cc:4255: dberr_t row_rename_table_for_mysql(const char*, const char*, trx_t*, bool, bool): Assertion `err != DB_DUPLICATE_KEY' failed.
10.6.1 9db14e93acc4ec9023d50686c66dbef7d4d8c15c (Debug)
Core was generated by `/test/MD270421-mariadb-10.6.1-linux-x86_64-dbg/bin/mysqld --no-defaults --core-'.
Program terminated with signal SIGABRT, Aborted.
#0 __GI_raise (sig=sig@entry=6) at ../sysdeps/unix/sysv/linux/raise.c:50
[Current thread is 1 (Thread 0x1459883fb700 (LWP 1760819))]
(gdb) bt
#0 __GI_raise (sig=sig@entry=6) at ../sysdeps/unix/sysv/linux/raise.c:50
#1 0x000014598bfb9859 in __GI_abort () at abort.c:79
#2 0x000014598bfb9729 in __assert_fail_base (fmt=0x14598c14f588 "%s%s%s:%u: %s%sAssertion `%s' failed.\n%n", assertion=0x55db8c50392d "err != DB_DUPLICATE_KEY", file=0x55db8c503c90 "/test/10.6_dbg/storage/innobase/row/row0mysql.cc", line=4255, function=<optimized out>) at assert.c:92
#3 0x000014598bfcaf36 in __GI___assert_fail (assertion=assertion@entry=0x55db8c50392d "err != DB_DUPLICATE_KEY", file=file@entry=0x55db8c503c90 "/test/10.6_dbg/storage/innobase/row/row0mysql.cc", line=line@entry=4255, function=function@entry=0x55db8c505390 "dberr_t row_rename_table_for_mysql(const char*, const char*, trx_t*, bool, bool)") at assert.c:101
#4 0x000055db8be202d1 in row_rename_table_for_mysql (old_name=<optimized out>, old_name@entry=0x1459883f65e0 "test/#sql-alter-1ad78c-4", new_name=<optimized out>, new_name@entry=0x1459883f63e0 "test/t1", trx=trx@entry=0x1459896b62a0, commit=commit@entry=true, use_fk=use_fk@entry=true) at /test/10.6_dbg/storage/innobase/row/row0mysql.cc:4255
#5 0x000055db8bc94ab3 in innobase_rename_table (commit=true, to=0x1459883f7030 "./test/t1", from=0x1459883f6e20 "./test/#sql-alter-1ad78c-4", trx=0x1459896b62a0) at /test/10.6_dbg/storage/innobase/handler/ha_innodb.cc:13281
#6 ha_innobase::rename_table (this=<optimized out>, from=0x1459883f6e20 "./test/#sql-alter-1ad78c-4", to=0x1459883f7030 "./test/t1") at /test/10.6_dbg/storage/innobase/handler/ha_innodb.cc:13474
#7 0x000055db8b8a78de in handler::ha_rename_table (this=this@entry=0x145934015ec0, from=from@entry=0x1459883f6e20 "./test/#sql-alter-1ad78c-4", to=to@entry=0x1459883f7030 "./test/t1") at /test/10.6_dbg/sql/handler.cc:4980
#8 0x000055db8b698edb in mysql_rename_table (base=base@entry=0x55db8e8611d8, old_db=old_db@entry=0x1459883f8950, old_name=old_name@entry=0x1459883f8980, new_db=new_db@entry=0x1459883f8950, new_name=new_name@entry=0x1459883f8970, flags=flags@entry=1) at /test/10.6_dbg/sql/sql_table.cc:5891
#9 0x000055db8b6af0d4 in mysql_alter_table (thd=thd@entry=0x145934000db8, new_db=new_db@entry=0x1459340059d8, new_name=new_name@entry=0x145934005e00, create_info=create_info@entry=0x1459883f9540, table_list=<optimized out>, table_list@entry=0x1459340140b0, alter_info=alter_info@entry=0x1459883f9450, order_num=0, order=0x0, ignore=false, if_exists=false) at /test/10.6_dbg/sql/sql_table.cc:11145
#10 0x000055db8b73270f in Sql_cmd_alter_table::execute (this=<optimized out>, thd=0x145934000db8) at /test/10.6_dbg/sql/structs.h:564
#11 0x000055db8b5d79a1 in mysql_execute_command (thd=thd@entry=0x145934000db8) at /test/10.6_dbg/sql/sql_parse.cc:5986
#12 0x000055db8b5bdf81 in mysql_parse (thd=thd@entry=0x145934000db8, rawbuf=<optimized out>, length=<optimized out>, parser_state=parser_state@entry=0x1459883fa410) at /test/10.6_dbg/sql/sql_parse.cc:8018
#13 0x000055db8b5ccdb3 in dispatch_command (command=command@entry=COM_QUERY, thd=thd@entry=0x145934000db8, packet=packet@entry=0x14593400b789 "", packet_length=packet_length@entry=28, blocking=blocking@entry=true) at /test/10.6_dbg/sql/sql_class.h:1332
#14 0x000055db8b5d01a6 in do_command (thd=0x145934000db8, blocking=blocking@entry=true) at /test/10.6_dbg/sql/sql_parse.cc:1406
#15 0x000055db8b72aa6a in do_handle_one_connection (connect=<optimized out>, connect@entry=0x55db8ed4d9b8, put_in_cache=put_in_cache@entry=true) at /test/10.6_dbg/sql/sql_connect.cc:1410
#16 0x000055db8b72b06f in handle_one_connection (arg=arg@entry=0x55db8ed4d9b8) at /test/10.6_dbg/sql/sql_connect.cc:1312
#17 0x000055db8bbdd2ca in pfs_spawn_thread (arg=0x55db8ec371f8) at /test/10.6_dbg/storage/perfschema/pfs.cc:2201
#18 0x000014598c4c7609 in start_thread (arg=<optimized out>) at pthread_create.c:477
#19 0x000014598c0b6293 in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:95