[MDEV-30583] DB_DUPLICATE_KEY on CREATE OR REPLACE after ALTER TABLE change engine Created: 2023-02-06  Updated: 2023-12-05

Status: Stalled
Project: MariaDB Server
Component/s: Data Definition - Create Table
Affects Version/s: 10.6
Fix Version/s: 10.6

Type: Bug Priority: Major
Reporter: Elena Stepanova Assignee: Vladislav Lesin
Resolution: Unresolved Votes: 0
Labels: None

Issue Links:
Relates
relates to MDEV-29566 Failed to read from the .par file upo... Stalled
relates to MDEV-30992 Assertion err != DB_DUPLICATE_KEY fai... Open

 Description   

The test case is very non-deterministic, run with high repeat=N. It usually fails for me within 5-20 attempts, but it can vary a lot on different machines and builds.

I didn't get any problem on 11.0 with the same test case in a limited number of attempts, but given non-determinism, it is not a guarantee.

--source include/have_innodb.inc
 
--source include/have_partition.inc
 
 
 
--connect (con1,localhost,root,,)
 
CREATE TABLE t1 (a INT) ENGINE=InnoDB;
 
CREATE TABLE t2 ENGINE=InnoDB AS SELECT * FROM t1;
 
CREATE TABLE t3 (b INT) ENGINE=InnoDB PARTITION BY LIST (b) (PARTITION p0 VALUES IN (6, NULL), PARTITION pdef DEFAULT);
 
--send
 
  ALTER TABLE t2 NOWAIT ENGINE Aria;
 
 
 
--connection default
 
CREATE OR REPLACE TABLE t3 (col3 INT) ENGINE=InnoDB;
 
CREATE OR REPLACE TABLE t2 LIKE t1;
 
 
 
--connection con1
 
--reap
 
DROP TABLE t3, t2, t1;
 
--disconnect con1

bb-11.0-midenok-MDEV-25292 9c054e95e

 
mariadbd: /data/src/bb-11.0-midenok-MDEV-25292/storage/innobase/row/row0mysql.cc:2664: dberr_t row_rename_table_for_mysql(const char*, const char*, trx_t*, rename_fk): Assertion `err != DB_DUPLICATE_KEY' failed.
 
230206 16:47:15 [ERROR] mysqld got signal 6 ;
 
 
 
#9  0x00007f2c88453df2 in __GI___assert_fail (assertion=0x55bfe4a59000 "err != DB_DUPLICATE_KEY", file=0x55bfe4a554c0 "/data/src/bb-11.0-midenok-MDEV-25292/storage/innobase/row/row0mysql.cc", line=2664, function=0x55bfe4a58d80 "dberr_t row_rename_table_for_mysql(const char*, const char*, trx_t*, rename_fk)") at ./assert/assert.c:101
 
#10 0x000055bfe34d6961 in row_rename_table_for_mysql (old_name=0x7f2c791e9280 "test/#sql-create-d0a3b-9-t2", new_name=0x7f2c791e9040 "test/t2", trx=0x7f2c7f3c7940, fk=RENAME_FK) at /data/src/bb-11.0-midenok-MDEV-25292/storage/innobase/row/row0mysql.cc:2664
 
#11 0x000055bfe316bfe0 in innobase_rename_table (trx=0x7f2c7f3c7940, from=0x7f2c791ea0d0 "./test/#sql-create-d0a3b-9-t2", to=0x7f2c791ea310 "./test/t2", fk=RENAME_FK) at /data/src/bb-11.0-midenok-MDEV-25292/storage/innobase/handler/ha_innodb.cc:13706
 
#12 0x000055bfe3170066 in ha_innobase::rename_table (this=0x62900010bde8, from=0x7f2c791ea0d0 "./test/#sql-create-d0a3b-9-t2", to=0x7f2c791ea310 "./test/t2") at /data/src/bb-11.0-midenok-MDEV-25292/storage/innobase/handler/ha_innodb.cc:14128
 
#13 0x000055bfe27c544d in handler::ha_rename_table (this=0x62900010bde8, from=0x7f2c791ea0d0 "./test/#sql-create-d0a3b-9-t2", to=0x7f2c791ea310 "./test/t2") at /data/src/bb-11.0-midenok-MDEV-25292/sql/handler.cc:5349
 
#14 0x000055bfe21ad955 in mysql_rename_table (base=0x615000002618, old_db=0x7f2c791ebd38, old_name=0x7f2c791eb060, new_db=0x7f2c791eafe0, new_name=0x7f2c791eb070, id=0x7f2c791eb080, flags=257) at /data/src/bb-11.0-midenok-MDEV-25292/sql/sql_table.cc:5668
 
#15 0x000055bfe1f96b32 in rename_table_and_triggers (thd=0x62c0001f0218, param=0x7f2c791eb060, ddl_log_state=0x0, ren_table=0x7f2c791ebd38, new_db=0x7f2c791eafe0, skip_error=false, force_if_exists=0x7f2c791eafd0) at /data/src/bb-11.0-midenok-MDEV-25292/sql/sql_rename.cc:385
 
#16 0x000055bfe21a627d in HA_CREATE_INFO::finalize_atomic_replace (this=0x7f2c791ebb00, thd=0x62c0001f0218, orig_table=0x629000109378) at /data/src/bb-11.0-midenok-MDEV-25292/sql/sql_table.cc:4558
 
#17 0x000055bfe21b0d8a in mysql_create_like_table (thd=0x62c0001f0218, table=0x629000109378, src_table=0x629000109af8, create_info=0x7f2c791ed790) at /data/src/bb-11.0-midenok-MDEV-25292/sql/sql_table.cc:6155
 
#18 0x000055bfe21e4368 in Sql_cmd_create_table_like::execute (this=0x6290001092f0, thd=0x62c0001f0218) at /data/src/bb-11.0-midenok-MDEV-25292/sql/sql_table.cc:12995
 
#19 0x000055bfe1eebca4 in mysql_execute_command (thd=0x62c0001f0218, is_called_from_prepared_stmt=false) at /data/src/bb-11.0-midenok-MDEV-25292/sql/sql_parse.cc:6004
 
#20 0x000055bfe1ef8bf9 in mysql_parse (thd=0x62c0001f0218, rawbuf=0x629000109238 "CREATE OR REPLACE TABLE t2 LIKE t1", length=34, parser_state=0x7f2c791eea20) at /data/src/bb-11.0-midenok-MDEV-25292/sql/sql_parse.cc:8005
 
#21 0x000055bfe1ecf582 in dispatch_command (command=COM_QUERY, thd=0x62c0001f0218, packet=0x6290006bd219 "CREATE OR REPLACE TABLE t2 LIKE t1", packet_length=34, blocking=true) at /data/src/bb-11.0-midenok-MDEV-25292/sql/sql_parse.cc:1894
 
#22 0x000055bfe1ecc342 in do_command (thd=0x62c0001f0218, blocking=true) at /data/src/bb-11.0-midenok-MDEV-25292/sql/sql_parse.cc:1407
 
#23 0x000055bfe237a65b in do_handle_one_connection (connect=0x608000002fb8, put_in_cache=true) at /data/src/bb-11.0-midenok-MDEV-25292/sql/sql_connect.cc:1416
 
#24 0x000055bfe2379ff1 in handle_one_connection (arg=0x608000002cb8) at /data/src/bb-11.0-midenok-MDEV-25292/sql/sql_connect.cc:1318
 
#25 0x000055bfe2f58a20 in pfs_spawn_thread (arg=0x617000007418) at /data/src/bb-11.0-midenok-MDEV-25292/storage/perfschema/pfs.cc:2201
 
#26 0x00007f2c884a7fd4 in start_thread (arg=<optimized out>) at ./nptl/pthread_create.c:442
 
#27 0x00007f2c8852866c in clone3 () at ../sysdeps/unix/sysv/linux/x86_64/clone3.S:81



 Comments   
Comment by Aleksey Midenkov [ 2023-07-14 ]

vlad.lesin managed to reproduce in 10.6

Comment by Vladislav Lesin [ 2023-07-14 ]

The scenario is the following:

1. "ALTER TABLE t2 NOWAIT ENGINE Aria" is blocked on the try to lock system dictionary when t2 is being closed before altering:

#15 0x0000564d79e3c827 in dict_sys_t::lock_wait (this=0x564d7aaf2440 <dict_sys>, file=0x564d7a4e02e0 "./storage/innobase/dict/dict0dict.cc", line=225)
 
    at ./storage/innobase/dict/dict0dict.cc:1001
 
#16 0x0000564d79b2d2be in dict_sys_t::lock (this=0x564d7aaf2440 <dict_sys>, file=0x564d7a4e02e0 "./storage/innobase/dict/dict0dict.cc", line=225)
 
    at ./storage/innobase/include/dict0dict.h:1525
 
#17 0x0000564d79e3b264 in dict_table_close (table=0x7f731498c888) at ./storage/innobase/dict/dict0dict.cc:225
 
#18 0x0000564d79cd1c56 in row_prebuilt_free (prebuilt=0x7f7314a723a8) at ./storage/innobase/row/row0mysql.cc:976
 
#19 0x0000564d79b05775 in ha_innobase::close (this=0x7f7314a71b00) at ./storage/innobase/handler/ha_innodb.cc:6352
 
#20 0x0000564d796d3873 in handler::ha_close (this=0x7f7314a71b00) at ./sql/handler.cc:3436
 
#21 0x0000564d794847e8 in closefrm (table=0x7f7314a711e8) at ./sql/table.cc:4537
 
#22 0x0000564d795ee9af in intern_close_table (table=0x7f7314a711e8) at ./sql/table_cache.cc:225
 
#23 0x0000564d795eeb29 in tc_remove_table (table=0x7f7314a711e8) at ./sql/table_cache.cc:263
 
#24 0x0000564d795ef619 in tc_release_table (table=0x7f7314a711e8) at ./sql/table_cache.cc:454
 
#25 0x0000564d7926ddf0 in close_thread_table (thd=0x7f7314005568, table_ptr=0x7f7314005660) at ./sql/sql_base.cc:1015
 
#26 0x0000564d7926d455 in close_all_tables_for_name (thd=0x7f7314005568, share=0x7f7314a0f330, extra=HA_EXTRA_NOT_USED, skip_table=0x0) at ./sql/sql_base.cc:770
 
#27 0x0000564d79436d33 in mysql_alter_table (thd=0x7f7314005568, new_db=0x7f731400a1d0, new_name=0x7f731400a628, create_info=0x7f73291f42d0, table_list=0x7f7314018c48, recreate_info=0x7f73291f41a0,
 
    alter_info=0x7f73291f41e0, order_num=0, order=0x0, ignore=false, if_exists=false) at ./sql/sql_table.cc:10653
 
#28 0x0000564d794ef742 in Sql_cmd_alter_table::execute (this=0x7f7314019360, thd=0x7f7314005568) at ./sql/sql_alter.cc:553
 
#29 0x0000564d7932a400 in mysql_execute_command (thd=0x7f7314005568, is_called_from_prepared_stmt=false) at ./sql/sql_parse.cc:6014
 
#30 0x0000564d79330564 in mysql_parse (thd=0x7f7314005568, rawbuf=0x7f73140189d0 "ALTER TABLE t2 NOWAIT ENGINE Aria", length=33, parser_state=0x7f73291f5360) at ./sql/sql_parse.cc:8041

2. "CREATE OR REPLACE TABLE t3 (col3 INT) ENGINE=InnoDB;" locks mysql/innodb_table_stats:

(rr) fr 1
 
#1  0x0000564d79bc2aba in lock_table_create (table=0x7f7324002e38, type_mode=3, trx=0x7f7339907180, c_lock=0x0) at ./storage/innobase/lock/lock0lock.cc:3309
 
3309            lock->trx->lock.table_locks.push_back(lock);
 
(rr) p table->name
 
$22 = {m_name = 0x7f73240030f8 "mysql/innodb_table_stats", static part_suffix = "#P#"}
 
(rr) bt
 
#0  std::vector<ib_lock_t*, ut_allocator<ib_lock_t*, true> >::push_back (this=0x7f7339907970, __x=@0x7f7338063a58: 0x7f73399076d8) at /usr/include/c++/10/bits/stl_vector.h:1199
 
#1  0x0000564d79bc2aba in lock_table_create (table=0x7f7324002e38, type_mode=3, trx=0x7f7339907180, c_lock=0x0) at ./storage/innobase/lock/lock0lock.cc:3309
 
#2  0x0000564d79bc38d8 in lock_table_low (table=0x7f7324002e38, mode=LOCK_X, thr=0x7f7318126c98, trx=0x7f7339907180) at ./storage/innobase/lock/lock0lock.cc:3545
 
#3  0x0000564d79bc3bda in lock_table (table=0x7f7324002e38, fktable=0x0, mode=LOCK_X, thr=0x7f7318126c98) at ./storage/innobase/lock/lock0lock.cc:3604
 
#4  0x0000564d79bc455c in lock_table_for_trx (table=0x7f7324002e38, trx=0x7f7339907180, mode=LOCK_X, no_wait=false) at ./storage/innobase/lock/lock0lock.cc:3746
 
#5  0x0000564d79b1798f in ha_innobase::delete_table (this=0x7f7318033890, name=0x7f7338064300 "./test/t3#P#p0") at ./storage/innobase/handler/ha_innodb.cc:13635
 
#6  0x0000564d79a02105 in ha_partition::del_ren_table (this=0x7f7318032ff0, from=0x7f7338065700 "./test/t3", to=0x0) at ./sql/ha_partition.cc:2509
 
#7  0x0000564d799fcacf in ha_partition::delete_table (this=0x7f7318032ff0, name=0x7f7338065700 "./test/t3") at ./sql/ha_partition.cc:633
 
#8  0x0000564d796cbced in hton_drop_table (hton=0x564d7ca7b708, path=0x7f7338065700 "./test/t3") at ./sql/handler.cc:576
 
#9  0x0000564d796d23ed in ha_delete_table (thd=0x7f7318003598, hton=0x564d7ca7b708, path=0x7f7338065700 "./test/t3", db=0x7f73380654d0, alias=0x7f73380654e0, generate_warning=false)
 
    at ./sql/handler.cc:3153
 
#10 0x0000564d7941aa48 in mysql_rm_table_no_locks (thd=0x7f7318003598, tables=0x7f7338065c50, current_db=0x7f7318003638, ddl_log_state=0x7f7338066710, if_exists=false, drop_temporary=false, drop_view=false,
 
    drop_sequence=false, dont_log_query=true, dont_free_locks=true) at ./sql/sql_table.cc:1469
 
#11 0x0000564d79423b44 in create_table_impl (thd=0x7f7318003598, ddl_log_state_create=0x7f73380666f0, ddl_log_state_rm=0x7f7338066710, orig_db=..., orig_table_name=..., db=..., table_name=..., path=...,
 
    options=..., create_info=0x7f73380669d0, alter_info=0x7f73380668e0, create_table_mode=0, is_trans=0x7f73380666ce, key_info=0x7f7338066430, key_count=0x7f7338066424, frm=0x7f7338066450)
 
    at ./sql/sql_table.cc:4345
 
#12 0x0000564d794246e9 in mysql_create_table_no_lock (thd=0x7f7318003598, ddl_log_state_create=0x7f73380666f0, ddl_log_state_rm=0x7f7338066710, db=0x7f7318032440, table_name=0x7f7318032450,
 
    create_info=0x7f73380669d0, alter_info=0x7f73380668e0, is_trans=0x7f73380666ce, create_table_mode=0, table_list=0x7f7318032428) at ./sql/sql_table.cc:4583
 
#13 0x0000564d79424c8f in mysql_create_table (thd=0x7f7318003598, create_table=0x7f7318032428, create_info=0x7f73380669d0, alter_info=0x7f73380668e0) at ./sql/sql_table.cc:4695
 
#14 0x0000564d7943b4ae in Sql_cmd_create_table_like::execute (this=0x7f73180323c8, thd=0x7f7318003598) at ./sql/sql_table.cc:11959
 
#15 0x0000564d7932a400 in mysql_execute_command (thd=0x7f7318003598, is_called_from_prepared_stmt=false) at ./sql/sql_parse.cc:6014
 
#16 0x0000564d79330564 in mysql_parse (thd=0x7f7318003598, rawbuf=0x7f7318032300 "CREATE OR REPLACE TABLE t3 (col3 INT) ENGINE=InnoDB", length=51, parser_state=0x7f7338067360)
 
    at ./sql/sql_parse.cc:8041

3. "ALTER TABLE t2 NOWAIT ENGINE Aria" tries to drop t2 innodb table and requests X table lock on mysql.innodb_table_stats, but the lock conflicts with the lock, acquired on the previous step, and the transaction is finished with LOCK_WAIT_TIMEOUT error as NOWAIT keyword is used:

#0  0x0000564d79bc3605 in lock_table_enqueue_waiting (mode=3, table=0x7f7324002e38, thr=0x7f7314a79b98, c_lock=0x7f73399076d8) at ./storage/innobase/lock/lock0lock.cc:3474
 
#1  0x0000564d79bc38bb in lock_table_low (table=0x7f7324002e38, mode=LOCK_X, thr=0x7f7314a79b98, trx=0x7f7339907c80) at ./storage/innobase/lock/lock0lock.cc:3543
 
#2  0x0000564d79bc3bda in lock_table (table=0x7f7324002e38, fktable=0x0, mode=LOCK_X, thr=0x7f7314a79b98) at ./storage/innobase/lock/lock0lock.cc:3604
 
#3  0x0000564d79bc455c in lock_table_for_trx (table=0x7f7324002e38, trx=0x7f7339907c80, mode=LOCK_X, no_wait=false) at ./storage/innobase/lock/lock0lock.cc:3746
 
#4  0x0000564d79b1798f in ha_innobase::delete_table (this=0x7f731401afa8, name=0x7f73291f1c70 "./test/t2") at ./storage/innobase/handler/ha_innodb.cc:13635
 
#5  0x0000564d796cbced in hton_drop_table (hton=0x564d7ca91f58, path=0x7f73291f1c70 "./test/t2") at ./sql/handler.cc:576
 
#6  0x0000564d796d23ed in ha_delete_table (thd=0x7f7314005568, hton=0x564d7ca91f58, path=0x7f73291f1c70 "./test/t2", db=0x7f73291f34c0, alias=0x7f73291f34d0, generate_warning=false)
 
    at ./sql/handler.cc:3153
 
#7  0x0000564d7941c1b5 in quick_rm_table (thd=0x7f7314005568, base=0x564d7ca91f58, db=0x7f73291f34c0, table_name=0x7f73291f34d0, flags=4, table_path=0x0) at ./sql/sql_table.cc:1916
 
#8  0x0000564d794372ca in mysql_alter_table (thd=0x7f7314005568, new_db=0x7f731400a1d0, new_name=0x7f731400a628, create_info=0x7f73291f42d0, table_list=0x7f7314018c48, recreate_info=0x7f73291f41a0,
 
    alter_info=0x7f73291f41e0, order_num=0, order=0x0, ignore=false, if_exists=false) at ./sql/sql_table.cc:10772
 
#9  0x0000564d794ef742 in Sql_cmd_alter_table::execute (this=0x7f7314019360, thd=0x7f7314005568) at ./sql/sql_alter.cc:553
 
#10 0x0000564d7932a400 in mysql_execute_command (thd=0x7f7314005568, is_called_from_prepared_stmt=false) at ./sql/sql_parse.cc:6014
 
#11 0x0000564d79330564 in mysql_parse (thd=0x7f7314005568, rawbuf=0x7f73140189d0 "ALTER TABLE t2 NOWAIT ENGINE Aria", length=33, parser_state=0x7f73291f5360) at ./sql/sql_parse.cc:8041

Note that "test/t2" record was not deleted from sys_tables, as it should be deleted after the lock on mysql.innodb_table_stats is acquired.

4. CREATE OR REPLACE TABLE t2 LIKE t1; gets duplicate key error as "test/t2" record was not deleted from sys_tables:

Thread 2 hit Breakpoint 7, row_ins_duplicate_error_in_clust (flags=0, cursor=0x7f4e800993b0, entry=0x7f4e5805ea28, thr=0x7f4e582aa688) at ./storage/innobase/row/row0ins.cc:2403
 
2403                                    err = DB_DUPLICATE_KEY;
 
(rr) bt
 
#0  row_ins_duplicate_error_in_clust (flags=0, cursor=0x7f4e800993b0, entry=0x7f4e5805ea28, thr=0x7f4e582aa688) at ./storage/innobase/row/row0ins.cc:2403
 
#1  0x0000555d9d720adb in row_ins_clust_index_entry_low (flags=0, mode=BTR_MODIFY_LEAF, index=0x555da05594c8, n_uniq=1, entry=0x7f4e5805ea28, n_ext=0, thr=0x7f4e582aa688)
 
    at ./storage/innobase/row/row0ins.cc:2815
 
#2  0x0000555d9d72218a in row_ins_clust_index_entry (index=0x555da05594c8, entry=0x7f4e5805ea28, thr=0x7f4e582aa688, n_ext=0) at ./storage/innobase/row/row0ins.cc:3239
 
#3  0x0000555d9d7226ec in row_ins_index_entry (index=0x555da05594c8, entry=0x7f4e5805ea28, thr=0x7f4e582aa688) at ./storage/innobase/row/row0ins.cc:3365
 
#4  0x0000555d9d722fc6 in row_ins_index_entry_step (node=0x7f4e5805e2a0, thr=0x7f4e582aa688) at ./storage/innobase/row/row0ins.cc:3533
 
#5  0x0000555d9d7234b8 in row_ins (node=0x7f4e5805e2a0, thr=0x7f4e582aa688) at ./storage/innobase/row/row0ins.cc:3658
 
#6  0x0000555d9d723c20 in row_ins_step (thr=0x7f4e582aa688) at ./storage/innobase/row/row0ins.cc:3787
 
#7  0x0000555d9d6dba92 in que_thr_step (thr=0x7f4e582aa688) at ./storage/innobase/que/que0que.cc:567
 
#8  0x0000555d9d6dbe44 in que_run_threads_low (thr=0x7f4e582aa688) at ./storage/innobase/que/que0que.cc:644
 
#9  0x0000555d9d6dbf5c in que_run_threads (thr=0x7f4e582aa688) at ./storage/innobase/que/que0que.cc:664
 
#10 0x0000555d9d747074 in row_create_table_for_mysql (table=0x7f4e649497c8, trx=0x7f4e80f38180) at ./storage/innobase/row/row0mysql.cc:2110
 
#11 0x0000555d9d5a33ea in create_table_info_t::create_table_def (this=0x7f4e8009a290) at ./storage/innobase/handler/ha_innodb.cc:10957
 
#12 0x0000555d9d586d14 in create_table_info_t::create_table (this=0x7f4e8009a290, create_fk=true) at ./storage/innobase/handler/ha_innodb.cc:12794
 
#13 0x0000555d9d588381 in ha_innobase::create (this=0x7f4e582a91d0, name=0x7f4e8009bcb0 "./test/t2", form=0x7f4e8009a810, create_info=0x7f4e8009c150, file_per_table=true, trx=0x7f4e80f38180)
 
    at ./storage/innobase/handler/ha_innodb.cc:13301
 
#14 0x0000555d9d5887a5 in ha_innobase::create (this=0x7f4e582a91d0, name=0x7f4e8009bcb0 "./test/t2", form=0x7f4e8009a810, create_info=0x7f4e8009c150)
 
    at ./storage/innobase/handler/ha_innodb.cc:13345
 
#15 0x0000555d9d14d266 in handler::ha_create (this=0x7f4e582a91d0, name=0x7f4e8009bcb0 "./test/t2", form=0x7f4e8009a810, info_arg=0x7f4e8009c150) at ./sql/handler.cc:5505
 
#16 0x0000555d9d14eaf1 in ha_create_table (thd=0x7f4e58003598, path=0x7f4e8009bcb0 "./test/t2", db=0x7f4e58032b10 "test", table_name=0x7f4e580323d0 "t2", create_info=0x7f4e8009c150, frm=0x7f4e8009bca0,
 
    skip_frm_file=false) at ./sql/handler.cc:5973
 
#17 0x0000555d9ce9621c in create_table_impl (thd=0x7f4e58003598, ddl_log_state_create=0x7f4e8009bf40, ddl_log_state_rm=0x7f4e8009bf60, orig_db=..., orig_table_name=..., db=..., table_name=..., path=...,
 
    options=..., create_info=0x7f4e8009c150, alter_info=0x7f4e8009c060, create_table_mode=0, is_trans=0x7f4e8009bf13, key_info=0x7f4e8009bc80, key_count=0x7f4e8009bc74, frm=0x7f4e8009bca0)
 
    at ./sql/sql_table.cc:4484
 
#18 0x0000555d9ce966e9 in mysql_create_table_no_lock (thd=0x7f4e58003598, ddl_log_state_create=0x7f4e8009bf40, ddl_log_state_rm=0x7f4e8009bf60, db=0x7f4e58032420, table_name=0x7f4e58032430,
 
    create_info=0x7f4e8009c150, alter_info=0x7f4e8009c060, is_trans=0x7f4e8009bf13, create_table_mode=0, table_list=0x7f4e58032408) at ./sql/sql_table.cc:4583
 
#19 0x0000555d9ce98797 in mysql_create_like_table (thd=0x7f4e58003598, table=0x7f4e58032408, src_table=0x7f4e58032b50, create_info=0x7f4e8009d9d0) at ./sql/sql_table.cc:5207
 
#20 0x0000555d9cead237 in Sql_cmd_create_table_like::execute (this=0x7f4e580323a8, thd=0x7f4e58003598) at ./sql/sql_table.cc:11918
 
#21 0x0000555d9cd9c400 in mysql_execute_command (thd=0x7f4e58003598, is_called_from_prepared_stmt=false) at ./sql/sql_parse.cc:6014
 
#22 0x0000555d9cda2564 in mysql_parse (thd=0x7f4e58003598, rawbuf=0x7f4e58032300 "CREATE OR REPLACE TABLE t2 LIKE t1", length=34, parser_state=0x7f4e8009e360) at ./sql/sql_parse.cc:8041
 
#23 0x0000555d9cd8e878 in dispatch_command (command=COM_QUERY, thd=0x7f4e58003598, packet=0x7f4e5802a1a9 "CREATE OR REPLACE TABLE t2 LIKE t1", packet_length=34, blocking=true)
 
    at ./sql/sql_parse.cc:1896

The opened question for me is why "CREATE OR REPLACE TABLE t2 LIKE t1" does not remove t2 when it gets duplicate key error.

The error is well reproducible with the test in the description on 10.6 and 11.2 with small change - allow to accept LOCK_WAIT_TIMEOUT error just before "--reap". I can't reproduce it If STATS_PERSISTENT=0 is added to the all tables created in the test, i.e. if persistent statistics update is switched off and there is no need to lock mysql.innodb_table_stats.

Comment by Aleksey Midenkov [ 2023-07-18 ]

I lowered the priority since nobody else noticed it in trunk.

Comment by Elena Stepanova [ 2023-07-18 ]

For 10.6 it would be MDEV-30992.

Comment by Marko Mäkelä [ 2023-07-26 ]

To properly fix this as well as MDEV-29566, we may need an API change that would allow the execution of DDL statements to declare transaction boundaries. Currently, some parts of ALTER TABLE are being executed in multiple transactions, and failures of some steps are not tolerated at all.

To name one example: At the end of an ALTER TABLE that does not use the handler::inplace_alter_table API, the original copy of the table would be renamed to an intermediate name, the altered table renamed to the final name, and the original copy dropped. This would consist of 3 storage engine transactions while 1 would be preferred.

Comment by Julien Fritsch [ 2023-12-05 ]

Automated message:
----------------------------
Since this issue has not been updated since 6 weeks, it's time to move it back to Stalled.

Comment by JiraAutomate [ 2023-12-05 ]

Automated message:
----------------------------
Since this issue has not been updated since 6 weeks, it's time to move it back to Stalled.

Generated at Thu Feb 08 10:17:20 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.