Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-26931

Deadlock between ALTER on parent and CREATE OR REPLACE on child

Details

    • Bug
    • Status: Open (View Workflow)
    • Major
    • Resolution: Unresolved
    • 10.6, 10.7(EOL)
    • 10.6
    • Locking

    Description

      The MTR-like test below reproduces the failure for me quite easily, but it can depend on a build, for example, I had a problem reproducing it on certain ASAN builds. If it happens, any tool providing better concurrency can help.

      When it is reproducible, it is also rr-able.

      The deadlock obeys innodb_lock_wait_timeout (which is somewhat counter-intuitive, I would expect lock_wait_timeout. It doesn't obey max_statement_time or KILL QUERY, in the sense that the killed query stays in the process list.

      Not for the regression suite!

      --source include/have_innodb.inc
       
      CREATE TABLE t1 (pk INT PRIMARY KEY) ENGINE=InnoDB;
       
      --connect (con1,localhost,root,,)
       
      --let run= 2000
      while ($run)
      {
        --send ALTER TABLE t1 FORCE
        --connection default
        --error 0,ER_CANT_CREATE_TABLE
        --eval CREATE OR REPLACE TABLE t2 (pk INT PRIMARY KEY, FOREIGN KEY (pk) REFERENCES t1 (pk)) ENGINE=InnoDB /* $run left */
        --connection con1
        --reap
        --dec $run
      }
      

      10.6 58fe6b47

      Thread 13 (Thread 0x7f9b89eff700 (LWP 168021)):
      #0  futex_abstimed_wait_cancelable (private=<optimized out>, abstime=0x7f9b89efa320, clockid=<optimized out>, expected=0, futex_word=0x7f9b8b19a5b0) at ../sysdeps/nptl/futex-internal.h:320
      #1  __pthread_cond_wait_common (abstime=0x7f9b89efa320, clockid=<optimized out>, mutex=0x5621c073a4a8 <lock_sys+232>, cond=0x7f9b8b19a588) at pthread_cond_wait.c:520
      #2  __pthread_cond_timedwait (cond=0x7f9b8b19a588, mutex=0x5621c073a4a8 <lock_sys+232>, abstime=0x7f9b89efa320) at pthread_cond_wait.c:656
      #3  0x00005621bf1ef5d0 in safe_cond_timedwait (cond=0x7f9b8b19a588, mp=0x5621c073a480 <lock_sys+192>, abstime=0x7f9b89efa320, file=0x5621bf62a740 "/data/src/10.6/storage/innobase/lock/lock0lock.cc", line=1831) at /data/src/10.6/mysys/thr_mutex.c:546
      #4  0x00005621bede285e in lock_wait (thr=0x7f9b589dfbf8) at /data/src/10.6/storage/innobase/lock/lock0lock.cc:1831
      #5  0x00005621beef387a in row_mysql_handle_errors (new_err=0x7f9b89efa574, trx=0x7f9b8b19a540, thr=0x7f9b589dfbf8, savept=0x0) at /data/src/10.6/storage/innobase/row/row0mysql.cc:686
      #6  0x00005621bede9cbf in lock_table_for_trx (table=0x7f9b6421e168, trx=0x7f9b8b19a540, mode=LOCK_X) at /data/src/10.6/storage/innobase/lock/lock0lock.cc:3668
      #7  0x00005621bed8783e in ha_innobase::commit_inplace_alter_table (this=0x7f9b589be810, altered_table=0x7f9b89efb270, ha_alter_info=0x7f9b89efb1b0, commit=true) at /data/src/10.6/storage/innobase/handler/handler0alter.cc:10864
      #8  0x00005621be903fb0 in handler::ha_commit_inplace_alter_table (this=0x7f9b589be810, altered_table=0x7f9b89efb270, ha_alter_info=0x7f9b89efb1b0, commit=true) at /data/src/10.6/sql/handler.cc:5194
      #9  0x00005621be65fe94 in mysql_inplace_alter_table (thd=0x7f9b58000db8, table_list=0x7f9b58014230, table=0x7f9b589d8cd8, altered_table=0x7f9b89efb270, ha_alter_info=0x7f9b89efb1b0, target_mdl_request=0x7f9b89efba70, ddl_log_state=0x7f9b89efb150, trigger_param=0x7f9b89efb640, alter_ctx=0x7f9b89efc5d0) at /data/src/10.6/sql/sql_table.cc:7436
      #10 0x00005621be668674 in mysql_alter_table (thd=0x7f9b58000db8, new_db=0x7f9b580059b8, new_name=0x7f9b58005dd0, create_info=0x7f9b89efd3e0, table_list=0x7f9b58014230, alter_info=0x7f9b89efd2f0, order_num=0, order=0x0, ignore=false, if_exists=false) at /data/src/10.6/sql/sql_table.cc:10204
      #11 0x00005621be71e35f in Sql_cmd_alter_table::execute (this=0x7f9b58014918, thd=0x7f9b58000db8) at /data/src/10.6/sql/sql_alter.cc:550
      #12 0x00005621be563b07 in mysql_execute_command (thd=0x7f9b58000db8, is_called_from_prepared_stmt=false) at /data/src/10.6/sql/sql_parse.cc:5997
      #13 0x00005621be569cb3 in mysql_parse (thd=0x7f9b58000db8, rawbuf=0x7f9b58014160 "ALTER TABLE t1 FORCE", length=20, parser_state=0x7f9b89efe480) at /data/src/10.6/sql/sql_parse.cc:8030
      #14 0x00005621be5560e3 in dispatch_command (command=COM_QUERY, thd=0x7f9b58000db8, packet=0x7f9b5800b879 "ALTER TABLE t1 FORCE", packet_length=20, blocking=true) at /data/src/10.6/sql/sql_parse.cc:1896
      #15 0x00005621be554a7f in do_command (thd=0x7f9b58000db8, blocking=true) at /data/src/10.6/sql/sql_parse.cc:1404
      #16 0x00005621be713123 in do_handle_one_connection (connect=0x5621c1b77408, put_in_cache=true) at /data/src/10.6/sql/sql_connect.cc:1418
      #17 0x00005621be712db3 in handle_one_connection (arg=0x5621c1b77408) at /data/src/10.6/sql/sql_connect.cc:1312
      #18 0x00005621bec4404d in pfs_spawn_thread (arg=0x5621c1baaba8) at /data/src/10.6/storage/perfschema/pfs.cc:2201
      #19 0x00007f9b9523c609 in start_thread (arg=<optimized out>) at pthread_create.c:477
      #20 0x00007f9b94e0f293 in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:95
       
      Thread 12 (Thread 0x7f9b89f4a700 (LWP 168018)):
      #0  syscall () at ../sysdeps/unix/sysv/linux/x86_64/syscall.S:38
      #1  0x00005621bef6b07a in ssux_lock_impl<false>::wait (this=0x5621bfce8188 <dict_sys+72>, lk=2147483649) at /data/src/10.6/storage/innobase/sync/srw_lock.cc:354
      #2  0x00005621bef6aa31 in ssux_lock_impl<false>::wr_wait (this=0x5621bfce8188 <dict_sys+72>, lk=2147483649) at /data/src/10.6/storage/innobase/sync/srw_lock.cc:509
      #3  0x00005621bed5902e in ssux_lock_impl<false>::wr_lock (this=0x5621bfce8188 <dict_sys+72>) at /data/src/10.6/storage/innobase/include/srw_lock.h:232
      #4  0x00005621bef6ac5d in srw_lock_impl<false>::psi_wr_lock (this=0x5621bfce8180 <dict_sys+64>, file=0x5621bf5ff328 "/data/src/10.6/storage/innobase/handler/ha_innodb.cc", line=13549) at /data/src/10.6/storage/innobase/sync/srw_lock.cc:598
      #5  0x00005621bee3c974 in srw_lock_impl<false>::wr_lock (this=0x5621bfce8180 <dict_sys+64>, file=0x5621bf5ff328 "/data/src/10.6/storage/innobase/handler/ha_innodb.cc", line=13549) at /data/src/10.6/storage/innobase/include/srw_lock.h:494
      #6  0x00005621bf066c87 in dict_sys_t::lock_wait (this=0x5621bfce8140 <dict_sys>, file=0x5621bf5ff328 "/data/src/10.6/storage/innobase/handler/ha_innodb.cc", line=13549) at /data/src/10.6/storage/innobase/dict/dict0dict.cc:961
      #7  0x00005621bed53079 in dict_sys_t::lock (this=0x5621bfce8140 <dict_sys>, file=0x5621bf5ff328 "/data/src/10.6/storage/innobase/handler/ha_innodb.cc", line=13549) at /data/src/10.6/storage/innobase/include/dict0dict.h:1571
      #8  0x00005621bed3dfd9 in ha_innobase::delete_table (this=0x7f9b640168f8, name=0x7f9b89f47820 "./test/t2") at /data/src/10.6/storage/innobase/handler/ha_innodb.cc:13549
      #9  0x00005621be8f5712 in hton_drop_table (hton=0x5621c15ce578, path=0x7f9b89f47820 "./test/t2") at /data/src/10.6/sql/handler.cc:576
      #10 0x00005621be8fbb0b in ha_delete_table (thd=0x7f9b64000db8, hton=0x5621c15ce578, path=0x7f9b89f47820 "./test/t2", db=0x7f9b89f475f0, alias=0x7f9b89f47600, generate_warning=false) at /data/src/10.6/sql/handler.cc:3100
      #11 0x00005621be64dd07 in mysql_rm_table_no_locks (thd=0x7f9b64000db8, tables=0x7f9b89f47d70, current_db=0x7f9b64000e50, ddl_log_state=0x7f9b89f48810, if_exists=false, drop_temporary=false, drop_view=false, drop_sequence=false, dont_log_query=true, dont_free_locks=true) at /data/src/10.6/sql/sql_table.cc:1469
      #12 0x00005621be656cea in create_table_impl (thd=0x7f9b64000db8, ddl_log_state_create=0x7f9b89f487f0, ddl_log_state_rm=0x7f9b89f48810, orig_db=..., orig_table_name=..., db=..., table_name=..., path=..., options=..., create_info=0x7f9b89f48ac0, alter_info=0x7f9b89f489d0, create_table_mode=0, is_trans=0x7f9b89f487ce, key_info=0x7f9b89f48530, key_count=0x7f9b89f48524, frm=0x7f9b89f48550) at /data/src/10.6/sql/sql_table.cc:4307
      #13 0x00005621be65788f in mysql_create_table_no_lock (thd=0x7f9b64000db8, ddl_log_state_create=0x7f9b89f487f0, ddl_log_state_rm=0x7f9b89f48810, db=0x7f9b64015780, table_name=0x7f9b64015790, create_info=0x7f9b89f48ac0, alter_info=0x7f9b89f489d0, is_trans=0x7f9b89f487ce, create_table_mode=0, table_list=0x7f9b64015768) at /data/src/10.6/sql/sql_table.cc:4545
      #14 0x00005621be657e20 in mysql_create_table (thd=0x7f9b64000db8, create_table=0x7f9b64015768, create_info=0x7f9b89f48ac0, alter_info=0x7f9b89f489d0) at /data/src/10.6/sql/sql_table.cc:4657
      #15 0x00005621be66daa7 in Sql_cmd_create_table_like::execute (this=0x7f9b64015708, thd=0x7f9b64000db8) at /data/src/10.6/sql/sql_table.cc:11777
      #16 0x00005621be563b07 in mysql_execute_command (thd=0x7f9b64000db8, is_called_from_prepared_stmt=false) at /data/src/10.6/sql/sql_parse.cc:5997
      #17 0x00005621be569cb3 in mysql_parse (thd=0x7f9b64000db8, rawbuf=0x7f9b640155c0 "CREATE OR REPLACE TABLE t2 (pk INT PRIMARY KEY, FOREIGN KEY (pk) REFERENCES t1 (pk)) ENGINE=InnoDB /* 1948 left */", length=114, parser_state=0x7f9b89f49480) at /data/src/10.6/sql/sql_parse.cc:8030
      #18 0x00005621be5560e3 in dispatch_command (command=COM_QUERY, thd=0x7f9b64000db8, packet=0x7f9b6400b879 "CREATE OR REPLACE TABLE t2 (pk INT PRIMARY KEY, FOREIGN KEY (pk) REFERENCES t1 (pk)) ENGINE=InnoDB /* 1948 left */", packet_length=114, blocking=true) at /data/src/10.6/sql/sql_parse.cc:1896
      #19 0x00005621be554a7f in do_command (thd=0x7f9b64000db8, blocking=true) at /data/src/10.6/sql/sql_parse.cc:1404
      #20 0x00005621be713123 in do_handle_one_connection (connect=0x5621c1b77328, put_in_cache=true) at /data/src/10.6/sql/sql_connect.cc:1418
      #21 0x00005621be712db3 in handle_one_connection (arg=0x5621c1b29738) at /data/src/10.6/sql/sql_connect.cc:1312
      #22 0x00005621bec4404d in pfs_spawn_thread (arg=0x5621c1ba9378) at /data/src/10.6/storage/perfschema/pfs.cc:2201
      #23 0x00007f9b9523c609 in start_thread (arg=<optimized out>) at pthread_create.c:477
      #24 0x00007f9b94e0f293 in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:95
      

      All threads are attached.

      The failure started happening on 10.6 after this commit:

      commit c3c53926c467c95386ae98d61ada87294bd61478
      Author: Marko Mäkelä
      Date:   Mon Oct 18 18:03:12 2021 +0300
       
          MDEV-26554: Races between INSERT on child and DDL on parent table
      

      Attachments

        Issue Links

          Activity

            When I run the test with

            ./mtr --mysqld=--innodb-lock-wait-timeout=2 innodb.dl
            

            it will fail more quickly as follows:

            10.6 dbd6c6dc01228fe6e63f3f7dc695eb56ca8cd28d

            CREATE OR REPLACE TABLE t2 (pk INT PRIMARY KEY, FOREIGN KEY (pk) REFERENCES t1 (pk)) ENGINE=InnoDB /* 1983 left */;
            connection con1;
            innodb.dl 'innodb'                       [ fail ]
                    Test ended at 2021-10-29 11:06:00
             
            CURRENT_TEST: innodb.dl
            mysqltest: At line 17: query 'reap' failed: ER_LOCK_WAIT_TIMEOUT (1205): Lock wait timeout exceeded; try restarting transaction
            

            This is caused by MDEV-26554, where I implemented an InnoDB work-around of a deficiency of the SQL layer: DDL operations fail to acquire exclusive metadata locks (MDL) on all referencing (child).

            I was forced to do that, because MDEV-26554 was a huge pain in our internal stress testing, with enormous consequences (data dictionary corruption), and it looked like implementing a proper fix (acquiring the necessary MDL on DDL) would not be available soon. Furthermore, an alternative of reverting MDEV-21175 would not only have caused a performance regression but also forced us to revert or rewrite many other changes, such as MDEV-24258 and MDEV-25919.

            The work-around is: InnoDB DDL operations will acquire exclusive table locks on the child tables. As you may know, MDL and InnoDB locks are not part of the same deadlock detection. So, no deadlock will be detected or reported, and an InnoDB lock wait timeout will be reported instead (unless the timeout is infinite: innodb_lock_wait_timeout=100000000).

            I think that this deadlock on ALTER TABLE t1 FORCE can only be avoided by acquiring the necessary exclusive MDL in the SQL layer and reverting the InnoDB work-around. That fix will have to be validated with the mtr stress test case that I posted to MDEV-26554 (executing TRUNCATE TABLE and two forms of ALTER TABLE concurrently with INSERT into a child table).

            marko Marko Mäkelä added a comment - When I run the test with ./mtr --mysqld=--innodb-lock-wait-timeout=2 innodb.dl it will fail more quickly as follows: 10.6 dbd6c6dc01228fe6e63f3f7dc695eb56ca8cd28d CREATE OR REPLACE TABLE t2 (pk INT PRIMARY KEY, FOREIGN KEY (pk) REFERENCES t1 (pk)) ENGINE=InnoDB /* 1983 left */; connection con1; innodb.dl 'innodb' [ fail ] Test ended at 2021-10-29 11:06:00   CURRENT_TEST: innodb.dl mysqltest: At line 17: query 'reap' failed: ER_LOCK_WAIT_TIMEOUT (1205): Lock wait timeout exceeded; try restarting transaction This is caused by MDEV-26554 , where I implemented an InnoDB work-around of a deficiency of the SQL layer: DDL operations fail to acquire exclusive metadata locks (MDL) on all referencing (child). I was forced to do that, because MDEV-26554 was a huge pain in our internal stress testing, with enormous consequences (data dictionary corruption), and it looked like implementing a proper fix (acquiring the necessary MDL on DDL) would not be available soon. Furthermore, an alternative of reverting MDEV-21175 would not only have caused a performance regression but also forced us to revert or rewrite many other changes, such as MDEV-24258 and MDEV-25919 . The work-around is: InnoDB DDL operations will acquire exclusive table locks on the child tables. As you may know, MDL and InnoDB locks are not part of the same deadlock detection. So, no deadlock will be detected or reported, and an InnoDB lock wait timeout will be reported instead (unless the timeout is infinite: innodb_lock_wait_timeout=100000000 ). I think that this deadlock on ALTER TABLE t1 FORCE can only be avoided by acquiring the necessary exclusive MDL in the SQL layer and reverting the InnoDB work-around. That fix will have to be validated with the mtr stress test case that I posted to MDEV-26554 (executing TRUNCATE TABLE and two forms of ALTER TABLE concurrently with INSERT into a child table).

            The ER_CANT_CREATE_TABLE that the test is suppressing is very easily repeatable also under ./mtr --rr (much easier than the deadlock or timeout). I added SHOW WARNINGS to document the cause:

            CREATE OR REPLACE TABLE t2 (pk INT PRIMARY KEY, FOREIGN KEY (pk) REFERENCES t1 (pk)) ENGINE=InnoDB /* 1796 left */;
            SHOW WARNINGS;
            Level	Code	Message
            Warning	150	Create  table `test`.`t2` with foreign key (pk) constraint failed. There is no index in the referenced table where the referenced columns appear as the first columns.
            Error	1005	Can't create table `test`.`t2` (errno: 150 "Foreign key constraint is incorrectly formed")
            Warning	1215	Cannot add foreign key constraint for `t2`
            

            I expect that also this error would be fixed by acquiring necessary exclusive MDL on DDL operations. In my rr replay trace, around the time the function foreign_push_index_error() is being called, another thread is executing this part of ALTER TABLE t1 FORCE:

            10.6 dbd6c6dc01228fe6e63f3f7dc695eb56ca8cd28d

            #16 0x00005578a7a13877 in commit_unlock_and_unlink (trx=0x2a2d212dd540) at /mariadb/10.6m/storage/innobase/handler/handler0alter.cc:4101
            #17 0x00005578a7a29ffe in prepare_inplace_alter_table_dict (ha_alter_info=<optimized out>, altered_table=altered_table@entry=0x34010bffb3e0, old_table=<optimized out>, table_name=<optimized out>, flags=<optimized out>, flags2=<optimized out>, fts_doc_id_col=<optimized out>, add_fts_doc_id=<optimized out>, add_fts_doc_id_idx=<optimized out>) at /mariadb/10.6m/storage/innobase/handler/handler0alter.cc:7086
            #18 0x00005578a7a2dea3 in ha_innobase::prepare_inplace_alter_table (this=0x3b9574a86850, altered_table=<optimized out>, ha_alter_info=<optimized out>) at /mariadb/10.6m/storage/innobase/handler/handler0alter.cc:8249
            

            Moments later, this table with #sql name would be renamed to t1. I find it plausible that during the execution of the operation on t2, the metadata for t1 may be incorrect. There is a strange MDL for an empty table name being acquired during the operation on t2. Perhaps that one should be t1?

            10.6 dbd6c6dc01228fe6e63f3f7dc695eb56ca8cd28d

            Thread 2 hit Breakpoint 6, MDL_context::acquire_lock (this=this@entry=0x2a2d28000e78, mdl_request=mdl_request@entry=0xb98110efb60, lock_wait_timeout=86400) at /mariadb/10.6m/sql/mdl.cc:2272
            2272	  DBUG_PRINT("enter", ("lock_type: %s  timeout: %f",
            2: *mdl_request = {type = MDL_EXCLUSIVE, duration = MDL_STATEMENT, next_in_list = 0x5578a7e35b91 <code_state+31>, prev_in_list = 0x5578a80a2d73, ticket = 0x0, key = {m_length = 3, m_db_name_length = 0, 
                m_hash_value = 65537, 
                m_ptr = "\000\000\000\021\230\v\000\000\001\000\000\000\000\000\000\000\210\275+\247xU\000\000\001?\000\000\000\000\000\000\200Q\001\000\000\000\000\000@\002\017\021\230\v\000\000\240e\000(-*", '\000' <repeats 18 times>, "\260\373\016\021\230\v\000\000\260\373\016\021\230\v\000\000\377\377\377\377\000\000\000\000\327\273\005\000\000\000\000\000 4\000\200\341\003\r\005\000\000\000\000\000\000\000\000:\000\000\001\246\r\340\016test/t2_ibfk_1\004\000\000\000\000\000\000\030\000\016test/t2_ibfk_1\001\004\000\000\000\000\000\000\000\000test\000\000\000\000ibfk_1\000\000$\000\016test/t"...}, 
              m_src_file = 0x5578a7ecf630 "/mariadb/10.6m/sql/sql_base.cc", m_src_line = 2086}
            3: *mdl_request.key.m_ptr@mdl_request.key.m_length = "\000\000"
            (rr) bt
            #0  MDL_context::acquire_lock (this=this@entry=0x2a2d28000e78, mdl_request=mdl_request@entry=0xb98110efb60, lock_wait_timeout=86400) at /mariadb/10.6m/sql/mdl.cc:2272
            #1  0x00005578a72ba594 in open_table (thd=thd@entry=0x2a2d28000d48, table_list=table_list@entry=0xb98110f0240, ot_ctx=ot_ctx@entry=0xb98110efe30) at /mariadb/10.6m/sql/sql_base.cc:2094
            #2  0x00005578a72bbac5 in open_and_process_table (thd=thd@entry=0x2a2d28000d48, tables=tables@entry=0xb98110f0240, counter=counter@entry=0xb98110efecc, flags=flags@entry=131074, 
                prelocking_strategy=prelocking_strategy@entry=0xb98110eff18, has_prelocking_list=has_prelocking_list@entry=false, ot_ctx=0xb98110efe30) at /mariadb/10.6m/sql/sql_base.cc:3807
            #3  0x00005578a72bd9c0 in open_tables (thd=thd@entry=0x2a2d28000d48, options=@0x2a2d280065a0: {m_options = DDL_options_st::OPT_OR_REPLACE}, start=start@entry=0xb98110efeb8, counter=counter@entry=0xb98110efecc, 
                flags=flags@entry=131074, prelocking_strategy=prelocking_strategy@entry=0xb98110eff18) at /mariadb/10.6m/sql/sql_base.cc:4290
            #4  0x00005578a72be2bf in open_and_lock_tables (thd=thd@entry=0x2a2d28000d48, options=@0x2a2d280065a0: {m_options = DDL_options_st::OPT_OR_REPLACE}, tables=<optimized out>, tables@entry=0xb98110f0240, 
                derived=derived@entry=false, flags=131074, prelocking_strategy=prelocking_strategy@entry=0xb98110eff18) at /mariadb/10.6m/sql/sql_base.cc:5262
            #5  0x00005578a72c3c3d in open_and_lock_tables (flags=<optimized out>, derived=false, tables=0xb98110f0240, thd=0x2a2d28000d48) at /mariadb/10.6m/sql/sql_base.h:509
            #6  open_system_tables_for_read (thd=thd@entry=0x2a2d28000d48, table_list=table_list@entry=0xb98110f0240) at /mariadb/10.6m/sql/sql_base.cc:9111
            #7  0x00005578a73fac43 in open_stat_tables (thd=thd@entry=0x2a2d28000d48, tables=tables@entry=0xb98110f0240, for_write=for_write@entry=true) at /mariadb/10.6m/sql/sql_statistics.cc:243
            #8  0x00005578a73fdb27 in delete_statistics_for_table (thd=thd@entry=0x2a2d28000d48, db=db@entry=0x2a2d28016af0, tab=tab@entry=0x2a2d28016b00) at /mariadb/10.6m/sql/sql_statistics.cc:3271
            #9  0x00005578a741fac7 in create_table_impl (thd=thd@entry=0x2a2d28000d48, ddl_log_state_create=ddl_log_state_create@entry=0xb98110f59b0, ddl_log_state_rm=ddl_log_state_rm@entry=0xb98110f59d0, orig_db=
                  @0x2a2d28016af0: {str = 0x2a2d280171b8 "test", length = 4}, orig_table_name=@0x2a2d28016b00: {str = 0x2a2d28016aa0 "t2", length = 2}, db=@0x2a2d28016af0: {str = 0x2a2d280171b8 "test", length = 4}, 
                table_name=@0x2a2d28016b00: {str = 0x2a2d28016aa0 "t2", length = 2}, path=@0xb98110f5700: {str = 0xb98110f5720 "./test/t2", length = 9}, options={m_options = DDL_options_st::OPT_OR_REPLACE}, 
                create_info=0xb98110f5c10, alter_info=0xb98110f5b20, create_table_mode=0, is_trans=0xb98110f59af, key_info=0xb98110f56f8, key_count=0xb98110f56f4, frm=0xb98110f5710) at /mariadb/10.6m/sql/sql_table.cc:4292
            #10 0x00005578a74205d2 in mysql_create_table_no_lock (thd=thd@entry=0x2a2d28000d48, ddl_log_state_create=ddl_log_state_create@entry=0xb98110f59b0, ddl_log_state_rm=ddl_log_state_rm@entry=0xb98110f59d0, 
                db=db@entry=0x2a2d28016af0, table_name=table_name@entry=0x2a2d28016b00, create_info=create_info@entry=0xb98110f5c10, alter_info=0xb98110f5b20, is_trans=0xb98110f59af, create_table_mode=0, 
                table_list=0x2a2d28016ad8) at /mariadb/10.6m/sql/sql_table.cc:4545
            #11 0x00005578a7420a03 in mysql_create_table (thd=thd@entry=0x2a2d28000d48, create_table=create_table@entry=0x2a2d28016ad8, create_info=create_info@entry=0xb98110f5c10, 
                alter_info=alter_info@entry=0xb98110f5b20) at /mariadb/10.6m/sql/sql_table.cc:4657
            #12 0x00005578a74228e3 in Sql_cmd_create_table_like::execute (this=0x2a2d28016a78, thd=0x2a2d28000d48) at /mariadb/10.6m/sql/sql_table.cc:11777
            #13 0x00005578a734d3d0 in mysql_execute_command (thd=thd@entry=0x2a2d28000d48, is_called_from_prepared_stmt=is_called_from_prepared_stmt@entry=false) at /mariadb/10.6m/sql/sql_parse.cc:5997
            #14 0x00005578a734e5fd in mysql_parse (thd=thd@entry=0x2a2d28000d48, rawbuf=<optimized out>, length=<optimized out>, parser_state=parser_state@entry=0xb98110f63c0) at /mariadb/10.6m/sql/sql_parse.cc:8030
            #15 0x00005578a7350ce1 in dispatch_command (command=command@entry=COM_QUERY, thd=thd@entry=0x2a2d28000d48, 
                packet=packet@entry=0x2a2d2800aed9 "CREATE OR REPLACE TABLE t2 (pk INT PRIMARY KEY, FOREIGN KEY (pk) REFERENCES t1 (pk)) ENGINE=InnoDB /* 1784 left */", packet_length=packet_length@entry=114, 
                blocking=blocking@entry=true) at /mariadb/10.6m/sql/sql_parse.cc:1896
            

            So, an additional problem appears to be that the CREATE statement fails to acquire MDL on all mentioned table names. It does acquire an exclusive MDL on the name t2.

            marko Marko Mäkelä added a comment - The ER_CANT_CREATE_TABLE that the test is suppressing is very easily repeatable also under ./mtr --rr (much easier than the deadlock or timeout). I added SHOW WARNINGS to document the cause: CREATE OR REPLACE TABLE t2 (pk INT PRIMARY KEY, FOREIGN KEY (pk) REFERENCES t1 (pk)) ENGINE=InnoDB /* 1796 left */; SHOW WARNINGS; Level Code Message Warning 150 Create table `test`.`t2` with foreign key (pk) constraint failed. There is no index in the referenced table where the referenced columns appear as the first columns. Error 1005 Can't create table `test`.`t2` (errno: 150 "Foreign key constraint is incorrectly formed") Warning 1215 Cannot add foreign key constraint for `t2` I expect that also this error would be fixed by acquiring necessary exclusive MDL on DDL operations. In my rr replay trace, around the time the function foreign_push_index_error() is being called, another thread is executing this part of ALTER TABLE t1 FORCE : 10.6 dbd6c6dc01228fe6e63f3f7dc695eb56ca8cd28d #16 0x00005578a7a13877 in commit_unlock_and_unlink (trx=0x2a2d212dd540) at /mariadb/10.6m/storage/innobase/handler/handler0alter.cc:4101 #17 0x00005578a7a29ffe in prepare_inplace_alter_table_dict (ha_alter_info=<optimized out>, altered_table=altered_table@entry=0x34010bffb3e0, old_table=<optimized out>, table_name=<optimized out>, flags=<optimized out>, flags2=<optimized out>, fts_doc_id_col=<optimized out>, add_fts_doc_id=<optimized out>, add_fts_doc_id_idx=<optimized out>) at /mariadb/10.6m/storage/innobase/handler/handler0alter.cc:7086 #18 0x00005578a7a2dea3 in ha_innobase::prepare_inplace_alter_table (this=0x3b9574a86850, altered_table=<optimized out>, ha_alter_info=<optimized out>) at /mariadb/10.6m/storage/innobase/handler/handler0alter.cc:8249 Moments later, this table with #sql name would be renamed to t1 . I find it plausible that during the execution of the operation on t2 , the metadata for t1 may be incorrect. There is a strange MDL for an empty table name being acquired during the operation on t2 . Perhaps that one should be t1 ? 10.6 dbd6c6dc01228fe6e63f3f7dc695eb56ca8cd28d Thread 2 hit Breakpoint 6, MDL_context::acquire_lock (this=this@entry=0x2a2d28000e78, mdl_request=mdl_request@entry=0xb98110efb60, lock_wait_timeout=86400) at /mariadb/10.6m/sql/mdl.cc:2272 2272 DBUG_PRINT("enter", ("lock_type: %s timeout: %f", 2: *mdl_request = {type = MDL_EXCLUSIVE, duration = MDL_STATEMENT, next_in_list = 0x5578a7e35b91 <code_state+31>, prev_in_list = 0x5578a80a2d73, ticket = 0x0, key = {m_length = 3, m_db_name_length = 0, m_hash_value = 65537, m_ptr = "\000\000\000\021\230\v\000\000\001\000\000\000\000\000\000\000\210\275+\247xU\000\000\001?\000\000\000\000\000\000\200Q\001\000\000\000\000\000@\002\017\021\230\v\000\000\240e\000(-*", '\000' <repeats 18 times>, "\260\373\016\021\230\v\000\000\260\373\016\021\230\v\000\000\377\377\377\377\000\000\000\000\327\273\005\000\000\000\000\000 4\000\200\341\003\r\005\000\000\000\000\000\000\000\000:\000\000\001\246\r\340\016test/t2_ibfk_1\004\000\000\000\000\000\000\030\000\016test/t2_ibfk_1\001\004\000\000\000\000\000\000\000\000test\000\000\000\000ibfk_1\000\000$\000\016test/t"...}, m_src_file = 0x5578a7ecf630 "/mariadb/10.6m/sql/sql_base.cc", m_src_line = 2086} 3: *mdl_request.key.m_ptr@mdl_request.key.m_length = "\000\000" (rr) bt #0 MDL_context::acquire_lock (this=this@entry=0x2a2d28000e78, mdl_request=mdl_request@entry=0xb98110efb60, lock_wait_timeout=86400) at /mariadb/10.6m/sql/mdl.cc:2272 #1 0x00005578a72ba594 in open_table (thd=thd@entry=0x2a2d28000d48, table_list=table_list@entry=0xb98110f0240, ot_ctx=ot_ctx@entry=0xb98110efe30) at /mariadb/10.6m/sql/sql_base.cc:2094 #2 0x00005578a72bbac5 in open_and_process_table (thd=thd@entry=0x2a2d28000d48, tables=tables@entry=0xb98110f0240, counter=counter@entry=0xb98110efecc, flags=flags@entry=131074, prelocking_strategy=prelocking_strategy@entry=0xb98110eff18, has_prelocking_list=has_prelocking_list@entry=false, ot_ctx=0xb98110efe30) at /mariadb/10.6m/sql/sql_base.cc:3807 #3 0x00005578a72bd9c0 in open_tables (thd=thd@entry=0x2a2d28000d48, options=@0x2a2d280065a0: {m_options = DDL_options_st::OPT_OR_REPLACE}, start=start@entry=0xb98110efeb8, counter=counter@entry=0xb98110efecc, flags=flags@entry=131074, prelocking_strategy=prelocking_strategy@entry=0xb98110eff18) at /mariadb/10.6m/sql/sql_base.cc:4290 #4 0x00005578a72be2bf in open_and_lock_tables (thd=thd@entry=0x2a2d28000d48, options=@0x2a2d280065a0: {m_options = DDL_options_st::OPT_OR_REPLACE}, tables=<optimized out>, tables@entry=0xb98110f0240, derived=derived@entry=false, flags=131074, prelocking_strategy=prelocking_strategy@entry=0xb98110eff18) at /mariadb/10.6m/sql/sql_base.cc:5262 #5 0x00005578a72c3c3d in open_and_lock_tables (flags=<optimized out>, derived=false, tables=0xb98110f0240, thd=0x2a2d28000d48) at /mariadb/10.6m/sql/sql_base.h:509 #6 open_system_tables_for_read (thd=thd@entry=0x2a2d28000d48, table_list=table_list@entry=0xb98110f0240) at /mariadb/10.6m/sql/sql_base.cc:9111 #7 0x00005578a73fac43 in open_stat_tables (thd=thd@entry=0x2a2d28000d48, tables=tables@entry=0xb98110f0240, for_write=for_write@entry=true) at /mariadb/10.6m/sql/sql_statistics.cc:243 #8 0x00005578a73fdb27 in delete_statistics_for_table (thd=thd@entry=0x2a2d28000d48, db=db@entry=0x2a2d28016af0, tab=tab@entry=0x2a2d28016b00) at /mariadb/10.6m/sql/sql_statistics.cc:3271 #9 0x00005578a741fac7 in create_table_impl (thd=thd@entry=0x2a2d28000d48, ddl_log_state_create=ddl_log_state_create@entry=0xb98110f59b0, ddl_log_state_rm=ddl_log_state_rm@entry=0xb98110f59d0, orig_db= @0x2a2d28016af0: {str = 0x2a2d280171b8 "test", length = 4}, orig_table_name=@0x2a2d28016b00: {str = 0x2a2d28016aa0 "t2", length = 2}, db=@0x2a2d28016af0: {str = 0x2a2d280171b8 "test", length = 4}, table_name=@0x2a2d28016b00: {str = 0x2a2d28016aa0 "t2", length = 2}, path=@0xb98110f5700: {str = 0xb98110f5720 "./test/t2", length = 9}, options={m_options = DDL_options_st::OPT_OR_REPLACE}, create_info=0xb98110f5c10, alter_info=0xb98110f5b20, create_table_mode=0, is_trans=0xb98110f59af, key_info=0xb98110f56f8, key_count=0xb98110f56f4, frm=0xb98110f5710) at /mariadb/10.6m/sql/sql_table.cc:4292 #10 0x00005578a74205d2 in mysql_create_table_no_lock (thd=thd@entry=0x2a2d28000d48, ddl_log_state_create=ddl_log_state_create@entry=0xb98110f59b0, ddl_log_state_rm=ddl_log_state_rm@entry=0xb98110f59d0, db=db@entry=0x2a2d28016af0, table_name=table_name@entry=0x2a2d28016b00, create_info=create_info@entry=0xb98110f5c10, alter_info=0xb98110f5b20, is_trans=0xb98110f59af, create_table_mode=0, table_list=0x2a2d28016ad8) at /mariadb/10.6m/sql/sql_table.cc:4545 #11 0x00005578a7420a03 in mysql_create_table (thd=thd@entry=0x2a2d28000d48, create_table=create_table@entry=0x2a2d28016ad8, create_info=create_info@entry=0xb98110f5c10, alter_info=alter_info@entry=0xb98110f5b20) at /mariadb/10.6m/sql/sql_table.cc:4657 #12 0x00005578a74228e3 in Sql_cmd_create_table_like::execute (this=0x2a2d28016a78, thd=0x2a2d28000d48) at /mariadb/10.6m/sql/sql_table.cc:11777 #13 0x00005578a734d3d0 in mysql_execute_command (thd=thd@entry=0x2a2d28000d48, is_called_from_prepared_stmt=is_called_from_prepared_stmt@entry=false) at /mariadb/10.6m/sql/sql_parse.cc:5997 #14 0x00005578a734e5fd in mysql_parse (thd=thd@entry=0x2a2d28000d48, rawbuf=<optimized out>, length=<optimized out>, parser_state=parser_state@entry=0xb98110f63c0) at /mariadb/10.6m/sql/sql_parse.cc:8030 #15 0x00005578a7350ce1 in dispatch_command (command=command@entry=COM_QUERY, thd=thd@entry=0x2a2d28000d48, packet=packet@entry=0x2a2d2800aed9 "CREATE OR REPLACE TABLE t2 (pk INT PRIMARY KEY, FOREIGN KEY (pk) REFERENCES t1 (pk)) ENGINE=InnoDB /* 1784 left */", packet_length=packet_length@entry=114, blocking=blocking@entry=true) at /mariadb/10.6m/sql/sql_parse.cc:1896 So, an additional problem appears to be that the CREATE statement fails to acquire MDL on all mentioned table names. It does acquire an exclusive MDL on the name t2 .

            As you may know, MDL and InnoDB locks are not part of the same deadlock detection. So, no deadlock will be detected or reported, and an InnoDB lock wait timeout will be reported instead

            That was my point about "non-intuitive" application of innodb_lock_wait_timeout in the description.
            innodb_lock_wait_timeout is still explicitly documented as a parameter related to row locks, as opposed to table locks. With the absence of any rows to lock, it's not easy to understand why the timeout occurs, although I'm very thankful that it does, it's way better than a timeless deadlock. But if the use of innodb_lock_wait_timeout has been extended, maybe it's worth documenting.

            elenst Elena Stepanova added a comment - As you may know, MDL and InnoDB locks are not part of the same deadlock detection. So, no deadlock will be detected or reported, and an InnoDB lock wait timeout will be reported instead That was my point about "non-intuitive" application of innodb_lock_wait_timeout in the description. innodb_lock_wait_timeout is still explicitly documented as a parameter related to row locks, as opposed to table locks. With the absence of any rows to lock, it's not easy to understand why the timeout occurs, although I'm very thankful that it does, it's way better than a timeless deadlock. But if the use of innodb_lock_wait_timeout has been extended, maybe it's worth documenting.

            The documentation of innodb_lock_wait_timeout needs to be corrected. I just checked the 10.2 source code, and lock_wait_suspend_thread() is definitely being invoked on both table and record locks. In 5.5, the corresponding function was called srv_suspend_mysql_thread(), and also there we would consult innodb_lock_wait_timeout for both table and record locks.

            marko Marko Mäkelä added a comment - The documentation of innodb_lock_wait_timeout needs to be corrected. I just checked the 10.2 source code, and lock_wait_suspend_thread() is definitely being invoked on both table and record locks. In 5.5, the corresponding function was called srv_suspend_mysql_thread() , and also there we would consult innodb_lock_wait_timeout for both table and record locks.

            For me, this still gets into the lock wait timeout, even after the fix of MDEV-29504. Until the innodb_lock_wait_timeout elapsed, we had the following waits:

            10.6 2ac1edb1c319db967e7424793c799bbc9824b8f4

            #6  0x000055fc201b2ab0 in lock_wait (thr=thr@entry=0x7fafec976318) at /mariadb/10.6/storage/innobase/lock/lock0lock.cc:1837
            #7  0x000055fc2024f44e in row_mysql_handle_errors (new_err=new_err@entry=0x7fb052f187fc, trx=trx@entry=0x7fb05823fc80, thr=thr@entry=0x7fafec976318, savept=savept@entry=0x0) at /mariadb/10.6/storage/innobase/row/row0mysql.cc:681
            #8  0x000055fc201b51f2 in lock_table_for_trx (table=0x7faff8203298, trx=trx@entry=0x7fb05823fc80, mode=mode@entry=LOCK_X, no_wait=no_wait@entry=false) at /mariadb/10.6/storage/innobase/lock/lock0lock.cc:3733
            #9  0x000055fc2016f21c in ha_innobase::commit_inplace_alter_table (this=0x7fafec96ab60, altered_table=0x7fb052f19340, ha_alter_info=0x7fb052f19280, commit=true) at /mariadb/10.6/storage/innobase/handler/handler0alter.cc:11055
            … ALTER TABLE t1 FORCE
             
            #6  0x000055fc2007a924 in dict_sys_t::lock_wait (this=this@entry=0x55fc2054f640 <dict_sys>, file=file@entry=0x55fc1f457658 "/mariadb/10.6/storage/innobase/handler/ha_innodb.cc", line=line@entry=13603) at /mariadb/10.6/storage/innobase/dict/dict0dict.cc:1001
            #7  0x000055fc2005e548 in dict_sys_t::lock (this=0x55fc2054f640 <dict_sys>, file=file@entry=0x55fc1f457658 "/mariadb/10.6/storage/innobase/handler/ha_innodb.cc", line=line@entry=13603) at /mariadb/10.6/storage/innobase/include/dict0dict.h:1525
            #8  0x000055fc200569b9 in ha_innobase::delete_table (this=<optimized out>, name=<optimized out>) at /mariadb/10.6/storage/innobase/handler/ha_innodb.cc:13603
            #9  0x000055fc1fb4dcae in hton_drop_table (hton=<optimized out>, path=<optimized out>) at /mariadb/10.6/sql/handler.cc:576
            #10 0x000055fc1fb54a05 in ha_delete_table (thd=thd@entry=0x7faff8000d58, hton=hton@entry=0x55fc21afdc38, path=path@entry=0x7fb052f659e0 "./test/t2", db=db@entry=0x7fb052f657d0, alias=alias@entry=0x7fb052f657e0, generate_warning=generate_warning@entry=false) at /mariadb/10.6/sql/handler.cc:3120
            … CREATE OR REPLACE TABLE t2 (pk INT PRIMARY KEY, FOREIGN KEY (pk) REFERENCES t1 (pk)) ENGINE=InnoDB /* 1966 left */
             
            #8  0x000055fc2007a10d in dict_sys_t::freeze (this=this@entry=0x55fc2054f640 <dict_sys>, file=file@entry=0x55fc1f411960 "/mariadb/10.6/storage/innobase/dict/dict0dict.cc", line=line@entry=849) at /mariadb/10.6/storage/innobase/dict/dict0dict.cc:1037
            #9  0x000055fc20082bb4 in dict_table_open_on_id<true> (table_id=1, dict_locked=dict_locked@entry=false, table_op=table_op@entry=DICT_TABLE_OP_NORMAL, thd=0x55fc21c6d9a8, mdl=mdl@entry=0x55fc21c31868) at /mariadb/10.6/storage/innobase/dict/dict0dict.cc:849
            #10 0x000055fc20263e12 in row_purge_parse_undo_rec (node=node@entry=0x55fc21c316c8, undo_rec=undo_rec@entry=0x55fc21c41aa8 "", thr=thr@entry=0x55fc21c314d8, updated_extern=updated_extern@entry=0x7fb026ffca97) at /mariadb/10.6/storage/innobase/row/row0purge.cc:1072
            #11 0x000055fc2026669a in row_purge (node=node@entry=0x55fc21c316c8, undo_rec=undo_rec@entry=0x55fc21c41aa8 "", thr=thr@entry=0x55fc21c314d8) at /mariadb/10.6/storage/innobase/row/row0purge.cc:1250
            … (in all innodb_purge_threads=4)
            

            marko Marko Mäkelä added a comment - For me, this still gets into the lock wait timeout, even after the fix of MDEV-29504 . Until the innodb_lock_wait_timeout elapsed, we had the following waits: 10.6 2ac1edb1c319db967e7424793c799bbc9824b8f4 #6 0x000055fc201b2ab0 in lock_wait (thr=thr@entry=0x7fafec976318) at /mariadb/10.6/storage/innobase/lock/lock0lock.cc:1837 #7 0x000055fc2024f44e in row_mysql_handle_errors (new_err=new_err@entry=0x7fb052f187fc, trx=trx@entry=0x7fb05823fc80, thr=thr@entry=0x7fafec976318, savept=savept@entry=0x0) at /mariadb/10.6/storage/innobase/row/row0mysql.cc:681 #8 0x000055fc201b51f2 in lock_table_for_trx (table=0x7faff8203298, trx=trx@entry=0x7fb05823fc80, mode=mode@entry=LOCK_X, no_wait=no_wait@entry=false) at /mariadb/10.6/storage/innobase/lock/lock0lock.cc:3733 #9 0x000055fc2016f21c in ha_innobase::commit_inplace_alter_table (this=0x7fafec96ab60, altered_table=0x7fb052f19340, ha_alter_info=0x7fb052f19280, commit=true) at /mariadb/10.6/storage/innobase/handler/handler0alter.cc:11055 … ALTER TABLE t1 FORCE   #6 0x000055fc2007a924 in dict_sys_t::lock_wait (this=this@entry=0x55fc2054f640 <dict_sys>, file=file@entry=0x55fc1f457658 "/mariadb/10.6/storage/innobase/handler/ha_innodb.cc", line=line@entry=13603) at /mariadb/10.6/storage/innobase/dict/dict0dict.cc:1001 #7 0x000055fc2005e548 in dict_sys_t::lock (this=0x55fc2054f640 <dict_sys>, file=file@entry=0x55fc1f457658 "/mariadb/10.6/storage/innobase/handler/ha_innodb.cc", line=line@entry=13603) at /mariadb/10.6/storage/innobase/include/dict0dict.h:1525 #8 0x000055fc200569b9 in ha_innobase::delete_table (this=<optimized out>, name=<optimized out>) at /mariadb/10.6/storage/innobase/handler/ha_innodb.cc:13603 #9 0x000055fc1fb4dcae in hton_drop_table (hton=<optimized out>, path=<optimized out>) at /mariadb/10.6/sql/handler.cc:576 #10 0x000055fc1fb54a05 in ha_delete_table (thd=thd@entry=0x7faff8000d58, hton=hton@entry=0x55fc21afdc38, path=path@entry=0x7fb052f659e0 "./test/t2", db=db@entry=0x7fb052f657d0, alias=alias@entry=0x7fb052f657e0, generate_warning=generate_warning@entry=false) at /mariadb/10.6/sql/handler.cc:3120 … CREATE OR REPLACE TABLE t2 (pk INT PRIMARY KEY, FOREIGN KEY (pk) REFERENCES t1 (pk)) ENGINE=InnoDB /* 1966 left */   #8 0x000055fc2007a10d in dict_sys_t::freeze (this=this@entry=0x55fc2054f640 <dict_sys>, file=file@entry=0x55fc1f411960 "/mariadb/10.6/storage/innobase/dict/dict0dict.cc", line=line@entry=849) at /mariadb/10.6/storage/innobase/dict/dict0dict.cc:1037 #9 0x000055fc20082bb4 in dict_table_open_on_id<true> (table_id=1, dict_locked=dict_locked@entry=false, table_op=table_op@entry=DICT_TABLE_OP_NORMAL, thd=0x55fc21c6d9a8, mdl=mdl@entry=0x55fc21c31868) at /mariadb/10.6/storage/innobase/dict/dict0dict.cc:849 #10 0x000055fc20263e12 in row_purge_parse_undo_rec (node=node@entry=0x55fc21c316c8, undo_rec=undo_rec@entry=0x55fc21c41aa8 "", thr=thr@entry=0x55fc21c314d8, updated_extern=updated_extern@entry=0x7fb026ffca97) at /mariadb/10.6/storage/innobase/row/row0purge.cc:1072 #11 0x000055fc2026669a in row_purge (node=node@entry=0x55fc21c316c8, undo_rec=undo_rec@entry=0x55fc21c41aa8 "", thr=thr@entry=0x55fc21c314d8) at /mariadb/10.6/storage/innobase/row/row0purge.cc:1250 … (in all innodb_purge_threads=4)

            I think that (possibly as part of fixing MDEV-18421) the following needs to happen:

            • ALTER TABLE t1 FORCE is protected by MDL_EXCLUSIVE on all the table names that are associated with t1 before or after the ALTER TABLE statement. This already happens; there are no FOREIGN KEY constraints known to that operation. The ha_innobase::commit_inplace_alter_table(commit=true) is executed while holding MDL_EXCLUSIVE on the table name t1.
            • CREATE OR REPLACE TABLE t2 (pk INT PRIMARY KEY, FOREIGN KEY (pk) REFERENCES t1 (pk)) must hold MDL_EXCLUSIVE not only on t2 but also on t1, which is part of the new table definition. An MDL_EXCLUSIVE on t1 is obviously missing here.

            If there was an old table definition of t2 that was connected to some other tables via FOREIGN KEY constraints, then also on those names, MDL_EXCLUSIVE needs to be acquired before the CREATE OR REPLACE TABLE t2 can proceed. For example, the last statement must acquire MDL_EXCLUSIVE on all of t0, t1, t2, and t3 in the following case:

            --source include/have_innodb.inc
            CREATE TABLE t0(pk INT PRIMARY KEY) ENGINE=InnoDB;
            CREATE TABLE t2(pk INT PRIMARY KEY, FOREIGN KEY(pk) REFERENCES t0(pk)) ENGINE=InnoDB;
            CREATE TABLE t3(pk INT PRIMARY KEY, FOREIGN KEY(pk) REFERENCES t2(pk)) ENGINE=InnoDB;
            CREATE TABLE t1(pk INT PRIMARY KEY) ENGINE=InnoDB;
            SET foreign_key_checks=0;
            CREATE OR REPLACE TABLE t2 (pk INT PRIMARY KEY, FOREIGN KEY (pk) REFERENCES t1 (pk)) ENGINE=InnoDB;
            

            (There is a separate error that the last statement would fail with ER_ROW_IS_REFERENCED_2 if foreign key checks are enabled.)

            marko Marko Mäkelä added a comment - I think that (possibly as part of fixing MDEV-18421 ) the following needs to happen: ALTER TABLE t1 FORCE is protected by MDL_EXCLUSIVE on all the table names that are associated with t1 before or after the ALTER TABLE statement. This already happens; there are no FOREIGN KEY constraints known to that operation. The ha_innobase::commit_inplace_alter_table(commit=true) is executed while holding MDL_EXCLUSIVE on the table name t1 . CREATE OR REPLACE TABLE t2 (pk INT PRIMARY KEY, FOREIGN KEY (pk) REFERENCES t1 (pk)) must hold MDL_EXCLUSIVE not only on t2 but also on t1 , which is part of the new table definition. An MDL_EXCLUSIVE on t1 is obviously missing here. If there was an old table definition of t2 that was connected to some other tables via FOREIGN KEY constraints, then also on those names, MDL_EXCLUSIVE needs to be acquired before the CREATE OR REPLACE TABLE t2 can proceed. For example, the last statement must acquire MDL_EXCLUSIVE on all of t0 , t1 , t2 , and t3 in the following case: --source include/have_innodb.inc CREATE TABLE t0(pk INT PRIMARY KEY ) ENGINE=InnoDB; CREATE TABLE t2(pk INT PRIMARY KEY , FOREIGN KEY (pk) REFERENCES t0(pk)) ENGINE=InnoDB; CREATE TABLE t3(pk INT PRIMARY KEY , FOREIGN KEY (pk) REFERENCES t2(pk)) ENGINE=InnoDB; CREATE TABLE t1(pk INT PRIMARY KEY ) ENGINE=InnoDB; SET foreign_key_checks=0; CREATE OR REPLACE TABLE t2 (pk INT PRIMARY KEY , FOREIGN KEY (pk) REFERENCES t1 (pk)) ENGINE=InnoDB; (There is a separate error that the last statement would fail with ER_ROW_IS_REFERENCED_2 if foreign key checks are enabled.)

            People

              serg Sergei Golubchik
              elenst Elena Stepanova
              Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

              Dates

                Created:
                Updated:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.