[MDEV-26772] InnoDB DDL fails with DUPLICATE KEY error Created: 2021-10-06  Updated: 2022-03-18  Resolved: 2021-10-19

Status: Closed
Project: MariaDB Server
Component/s: Storage Engine - InnoDB
Affects Version/s: N/A
Fix Version/s: 10.6.5, 10.7.1

Type: Bug Priority: Critical
Reporter: Thirunarayanan Balathandayuthapani Assignee: Marko Mäkelä
Resolution: Fixed Votes: 0
Labels: regression-10.6

Issue Links:
Problem/Incident
is caused by MDEV-25919 InnoDB reports misleading lock wait t... Closed
Relates
relates to MDEV-27909 InnoDB: Failing assertion: state == T... Closed

 Description   

InnoDB fails to drop the intermediate table during DDL using copy algorithm.
Failure happens due to lock wait while acquiring the lock on stats table.
So it leads to orphaned intermediate table name in InnoDB dictionary.
Consecutive alter tries to rename the source table to intermediate table
and fails with DUPLICATE key error.

Test case:
=========

 
 
--source include/have_innodb.inc
 
set global innodb_lock_wait_timeout=0;
 
create table t1(f1 int not null primary key,
 
                f2 int not null, index idx(f2))engine=innodb;
 
insert into t1 values(1, 1);
 
set DEBUG_SYNC="before_delete_table_stats SIGNAL con1_wait WAIT_FOR con1_signal";
 
send alter ignore table t1 drop primary key, lock=shared, algorithm=copy;
 
connect(con1,localhost,root,,,);
 
SET DEBUG_SYNC="now WAIT_FOR con1_wait";
 
begin;
 
SELECT * FROM mysql.innodb_table_stats FOR UPDATE;
 
SET DEBUG_SYNC="now SIGNAL con1_signal";
 
connection default;
 
reap;
 
set DEBUG_SYNC="now SIGNAL con1_skip";
 
connection con1;
 
set DEBUG_SYNC="now WAIT_FOR con1_skip";
 
commit;
 
set DEBUG_SYNC="now SIGNAL con1_finish";
 
connection default;
 
set DEBUG_SYNC="now WAIT_FOR con1_finish";
 
disconnect con1;
 
alter ignore table t1 rename key if exists idx to idx1, algorithm=copy;
 
drop table t1;

The following patch is to demonstrate the problem:

diff --git a/storage/innobase/handler/ha_innodb.cc b/storage/innobase/handler/ha_innodb.cc
 
index e431e0499c4..34010ea9899 100644
 
--- a/storage/innobase/handler/ha_innodb.cc
 
+++ b/storage/innobase/handler/ha_innodb.cc
 
@@ -13488,6 +13488,8 @@ int ha_innobase::delete_table(const char *name)
 
   }
 
 #endif
 
 
+  DEBUG_SYNC(thd, "before_delete_table_stats");
 
+
 
   if (err == DB_SUCCESS && dict_stats_is_persistent_enabled(table) &&
 
       !table->is_stats_table())



 Comments   
Comment by Elena Stepanova [ 2021-10-06 ]

For the sake of JIRA search:

10.6 41c66ef6 + DEBUG_SYNC from the description

 
mariadbd: /data/src/10.6-bug/storage/innobase/row/row0mysql.cc:2758: dberr_t row_rename_table_for_mysql(const char*, const char*, trx_t*, bool): Assertion `err != DB_DUPLICATE_KEY' failed.
 
211006 17:48:36 [ERROR] mysqld got signal 6 ;
 
 
 
#7  0x00007fa29e84df36 in __GI___assert_fail (assertion=0x555ba7bfa4dd "err != DB_DUPLICATE_KEY", file=0x555ba7bf8a50 "/data/src/10.6-bug/storage/innobase/row/row0mysql.cc", line=2758, function=0x555ba7bfa3b0 "dberr_t row_rename_table_for_mysql(const char*, const char*, trx_t*, bool)") at assert.c:101
 
#8  0x0000555ba7483098 in row_rename_table_for_mysql (old_name=0x7fa298291c70 "test/t1", new_name=0x7fa298291a70 "test/#sql-backup-3b72a1-4", trx=0x7fa298d42390, use_fk=true) at /data/src/10.6-bug/storage/innobase/row/row0mysql.cc:2758
 
#9  0x0000555ba72ca62c in innobase_rename_table (trx=0x7fa298d42390, from=0x7fa2982927d0 "./test/t1", to=0x7fa2982929d0 "./test/#sql-backup-3b72a1-4", use_fk=true) at /data/src/10.6-bug/storage/innobase/handler/ha_innodb.cc:13653
 
#10 0x0000555ba72cbb87 in ha_innobase::rename_table (this=0x7fa240016ea0, from=0x7fa2982927d0 "./test/t1", to=0x7fa2982929d0 "./test/#sql-backup-3b72a1-4") at /data/src/10.6-bug/storage/innobase/handler/ha_innodb.cc:13995
 
#11 0x0000555ba6e90d2d in handler::ha_rename_table (this=0x7fa240016ea0, from=0x7fa2982927d0 "./test/t1", to=0x7fa2982929d0 "./test/#sql-backup-3b72a1-4") at /data/src/10.6-bug/sql/handler.cc:5301
 
#12 0x0000555ba6be5da1 in mysql_rename_table (base=0x555ba9358d88, old_db=0x7fa2982945e0, old_name=0x7fa2982945f0, new_db=0x7fa2982945e0, new_name=0x7fa298293140, id=0x7fa298294680, flags=2) at /data/src/10.6-bug/sql/sql_table.cc:4970
 
#13 0x0000555ba6bf64db in mysql_alter_table (thd=0x7fa240000db8, new_db=0x7fa2400059b8, new_name=0x7fa240005dd0, create_info=0x7fa2982953e0, table_list=0x7fa2400156f0, alter_info=0x7fa2982952f0, order_num=0, order=0x0, ignore=true, if_exists=false) at /data/src/10.6-bug/sql/sql_table.cc:10529
 
#14 0x0000555ba6cab2b7 in Sql_cmd_alter_table::execute (this=0x7fa240015e28, thd=0x7fa240000db8) at /data/src/10.6-bug/sql/sql_alter.cc:550
 
#15 0x0000555ba6af1059 in mysql_execute_command (thd=0x7fa240000db8, is_called_from_prepared_stmt=false) at /data/src/10.6-bug/sql/sql_parse.cc:5997
 
#16 0x0000555ba6af7205 in mysql_parse (thd=0x7fa240000db8, rawbuf=0x7fa2400155c0 "alter ignore table t1 rename key if exists idx to idx1, algorithm=copy", length=70, parser_state=0x7fa298296480) at /data/src/10.6-bug/sql/sql_parse.cc:8030
 
#17 0x0000555ba6ae3635 in dispatch_command (command=COM_QUERY, thd=0x7fa240000db8, packet=0x7fa24000b879 "", packet_length=70, blocking=true) at /data/src/10.6-bug/sql/sql_parse.cc:1896
 
#18 0x0000555ba6ae1fd1 in do_command (thd=0x7fa240000db8, blocking=true) at /data/src/10.6-bug/sql/sql_parse.cc:1404
 
#19 0x0000555ba6ca007b in do_handle_one_connection (connect=0x555ba9901938, put_in_cache=true) at /data/src/10.6-bug/sql/sql_connect.cc:1418
 
#20 0x0000555ba6c9fd0b in handle_one_connection (arg=0x555ba98b3d68) at /data/src/10.6-bug/sql/sql_connect.cc:1312
 
#21 0x0000555ba71d095d in pfs_spawn_thread (arg=0x555ba9933988) at /data/src/10.6-bug/storage/perfschema/pfs.cc:2201
 
#22 0x00007fa29ed66609 in start_thread (arg=<optimized out>) at pthread_create.c:477
 
#23 0x00007fa29e939293 in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:95

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