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

InnoDB DDL fails with DUPLICATE KEY error

    XMLWordPrintable

    Details

      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())
      
      

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              marko Marko Mäkelä
              Reporter:
              thiru Thirunarayanan Balathandayuthapani
              Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved:

                  Git Integration