[MDEV-25642] 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) Created: 2021-05-10  Updated: 2021-07-12  Resolved: 2021-06-10

Status: Closed
Project: MariaDB Server
Component/s: Server, Storage Engine - InnoDB
Affects Version/s: 10.5, 10.6
Fix Version/s: 10.5.11, 10.6.3

Type: Bug Priority: Critical
Reporter: Roel Van de Paar Assignee: Thirunarayanan Balathandayuthapani
Resolution: Fixed Votes: 0
Labels: corruption, regression

Issue Links:
Relates
relates to MDEV-25885 Rename table operation using copy alg... Stalled
relates to MDEV-26127 Assertion `err != DB_DUPLICATE_KEY' f... Closed
relates to MDEV-25509 Atomic DDL: Assertion `err != DB_DUPL... Closed

 Description    

SET old_alter_table=1;
 
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:

10.6.1 f8665314d4ba190679001b81bb7d9fd7a38fc0f6 (Optimized)

 
10.6.1-opt>SET old_alter_table=1;
 
Query OK, 0 rows affected, 1 warning (0.000 sec)
 
 
 
10.6.1-opt>CREATE TABLE t1 (a VARCHAR(10) NOT NULL PRIMARY KEY) ENGINE=InnoDB;
 
Query OK, 0 rows affected (0.015 sec)
 
 
 
10.6.1-opt>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;
 
Query OK, 0 rows affected (0.091 sec)
 
 
 
10.6.1-opt>ALTER TABLE t1 RENAME TO tm1;
 
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails
 
10.6.1-opt>CREATE TABLE t1 (c1 BIGINT NOT NULL, c2 BIGINT NOT NULL, PRIMARY KEY(c1), UNIQUE KEY(c2)) ENGINE=MEMORY;
 
Query OK, 0 rows affected (0.008 sec)
 
 
 
10.6.1-opt>ALTER TABLE t1 ENGINE=InnoDB;
 
ERROR 1050 (42S01): Table './test/t1' already exists
 
10.6.1-opt>



 Comments   
Comment by Roel Van de Paar [ 2021-05-10 ]

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

Comment by Roel Van de Paar [ 2021-05-11 ]

It also crashes debug builds.

SET old_alter_table=1;
 
CREATE TABLE t (c VARCHAR(10) PRIMARY KEY) ENGINE=InnoDB;
 
CREATE TABLE tf (c VARCHAR(10), KEY k(c), CONSTRAINT fk FOREIGN KEY(c) REFERENCES t(c)) ENGINE=InnoDB;
 
ALTER TABLE t RENAME TO tm;
 
CREATE TABLE t (c INT) ENGINE=MEMORY;
 
ALTER TABLE t ENGINE=InnoDB;

Leads to:

10.6.1 f8665314d4ba190679001b81bb7d9fd7a38fc0f6 (Debug)

 
mysqld: /test/10.6_dbg/storage/innobase/row/row0mysql.cc:4195: dberr_t row_rename_table_for_mysql(const char*, const char*, trx_t*, bool, bool): Assertion `err != DB_DUPLICATE_KEY' failed.

10.6.1 f8665314d4ba190679001b81bb7d9fd7a38fc0f6 (Debug)

 
Core was generated by `/test/MD100521-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 0x146cf0102700 (LWP 3090115))]
 
(gdb) bt
 
#0  __GI_raise (sig=sig@entry=6) at ../sysdeps/unix/sysv/linux/raise.c:50
 
#1  0x0000146cf1365859 in __GI_abort () at abort.c:79
 
#2  0x0000146cf1365729 in __assert_fail_base (fmt=0x146cf14fb588 "%s%s%s:%u: %s%sAssertion `%s' failed.\n%n", assertion=0x56069b1fcbb9 "err != DB_DUPLICATE_KEY", file=0x56069b1fcee8 "/test/10.6_dbg/storage/innobase/row/row0mysql.cc", line=4195, function=<optimized out>) at assert.c:92
 
#3  0x0000146cf1376f36 in __GI___assert_fail (assertion=assertion@entry=0x56069b1fcbb9 "err != DB_DUPLICATE_KEY", file=file@entry=0x56069b1fcee8 "/test/10.6_dbg/storage/innobase/row/row0mysql.cc", line=line@entry=4195, function=function@entry=0x56069b1fe598 "dberr_t row_rename_table_for_mysql(const char*, const char*, trx_t*, bool, bool)") at assert.c:101
 
#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

Bug confirmed present in:
MariaDB: 10.5.10 (dbg), 10.6.1 (dbg)

Bug (or feature/syntax) confirmed not present in:
MariaDB: 10.2.38 (dbg), 10.2.38 (opt), 10.3.29 (dbg), 10.3.29 (opt), 10.4.19 (dbg), 10.4.19 (opt), 10.5.10 (opt), 10.6.1 (opt)
MySQL: 5.5.62 (dbg), 5.5.62 (opt), 5.6.51 (dbg), 5.6.51 (opt), 5.7.34 (dbg), 5.7.34 (opt), 8.0.24 (dbg), 8.0.24 (opt)

Comment by Thirunarayanan Balathandayuthapani [ 2021-05-14 ]

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.

Comment by Thirunarayanan Balathandayuthapani [ 2021-05-17 ]

After discussing with Roel, we're increasing the priority to Critical due to data corruption on optimized builds

Comment by Thirunarayanan Balathandayuthapani [ 2021-05-24 ]

Patch is in bb-10.5-thiru

Comment by Marko Mäkelä [ 2021-05-25 ]

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?

Comment by Michael Widenius [ 2021-06-08 ]

Done

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