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

ER_DUP_KEY in mysql.innodb_table_stats upon RENAME on sequence

Details

    Description

      --source include/have_innodb.inc
      		 
       
      		 
      set global innodb_stats_persistent= on;
      		 
       
      		 
      CREATE SEQUENCE s1 ENGINE=InnoDB;
      		 
      CREATE SEQUENCE s2 ENGINE=InnoDB;
      		 
      SHOW CREATE SEQUENCE s1;
      		 
      SHOW CREATE SEQUENCE s2;
      		 
      DROP SEQUENCE s2;
      		 
      RENAME TABLE s1 TO s2;
      		 
       
      		 
      # Cleanup
      		 
      DROP SEQUENCE s2;

      10.6 8171f9da

      		 
      mysqltest: At line 10: query 'RENAME TABLE s1 TO s2' failed: ER_DUP_KEY (1022): Can't write; duplicate key in table 'mysql.innodb_table_stats'

      The failure started happening after this commit in 10.6.2

      commit 1bd681c8b3c5213ce1f7976940a7dc38b48a0d39
      		 
      Author: Marko Mäkelä
      		 
      Date:   Wed Jun 9 17:02:55 2021 +0300
      		 
       
      		 
          MDEV-25506 (3 of 3): Do not delete .ibd files before commit

      Attachments

        Activity

          Ascending order - Click to sort in descending order
          elenst Elena Stepanova created issue -
          marko Marko Mäkelä added a comment -

          It turns out that even though persistent statistics make no sense for SEQUENCE, they are being created and adjusted, except in DROP TABLE or DROP SEQUENCE, which could indeed have been changed in MDEV-25506. I think that the simplest fix is to only skip the FOREIGN KEY handling when dropping a sequence, but keep dropping the useless persistent statistics:

          diff --git a/storage/innobase/handler/ha_innodb.cc b/storage/innobase/handler/ha_innodb.cc
          		 
          index efbde4b5393..ca03a5cdf0b 100644
          		 
          --- a/storage/innobase/handler/ha_innodb.cc
          		 
          +++ b/storage/innobase/handler/ha_innodb.cc
          		 
          @@ -13748,13 +13748,12 @@ int ha_innobase::delete_table(const char *name)
          		 
             }
          		 
           
             if (!table->no_rollback())
          		 
          -  {
          		 
               err= trx->drop_table_foreign(table->name);
          		 
          -    if (err == DB_SUCCESS && table_stats && index_stats)
          		 
          -      err= trx->drop_table_statistics(table->name);
          		 
          -    if (err != DB_SUCCESS)
          		 
          -      goto err_exit;
          		 
          -  }
          		 
          +
          		 
          +  if (err == DB_SUCCESS && table_stats && index_stats)
          		 
          +    err= trx->drop_table_statistics(table->name);
          		 
          +  if (err != DB_SUCCESS)
          		 
          +    goto err_exit;
          		 
           
             err= trx->drop_table(*table);
          		 
             if (err != DB_SUCCESS)

          marko Marko Mäkelä added a comment - It turns out that even though persistent statistics make no sense for SEQUENCE , they are being created and adjusted, except in DROP TABLE or DROP SEQUENCE , which could indeed have been changed in MDEV-25506 . I think that the simplest fix is to only skip the FOREIGN KEY handling when dropping a sequence, but keep dropping the useless persistent statistics: diff --git a/storage/innobase/handler/ha_innodb.cc b/storage/innobase/handler/ha_innodb.cc index efbde4b5393..ca03a5cdf0b 100644 --- a/storage/innobase/handler/ha_innodb.cc +++ b/storage/innobase/handler/ha_innodb.cc @@ -13748,13 +13748,12 @@ int ha_innobase::delete_table(const char *name) } if (!table->no_rollback()) - { err= trx->drop_table_foreign(table->name); - if (err == DB_SUCCESS && table_stats && index_stats) - err= trx->drop_table_statistics(table->name); - if (err != DB_SUCCESS) - goto err_exit; - } + + if (err == DB_SUCCESS && table_stats && index_stats) + err= trx->drop_table_statistics(table->name); + if (err != DB_SUCCESS) + goto err_exit; err= trx->drop_table(*table); if (err != DB_SUCCESS)
          marko Marko Mäkelä made changes -
          Field Original Value New Value
          issue.field.resolutiondate 2023-07-03 14:53:33.0 2023-07-03 14:53:33.331
          marko Marko Mäkelä made changes -
          Fix Version/s 10.6.15 [ 29013 ]
          Fix Version/s 10.9.8 [ 29015 ]
          Fix Version/s 10.10.6 [ 29017 ]
          Fix Version/s 10.11.5 [ 29019 ]
          Fix Version/s 11.0.3 [ 28920 ]
          Fix Version/s 11.1.2 [ 28921 ]
          Fix Version/s 11.2.1 [ 29034 ]
          Fix Version/s 10.6 [ 24028 ]
          Fix Version/s 10.9 [ 26905 ]
          Fix Version/s 10.10 [ 27530 ]
          Fix Version/s 10.11 [ 27614 ]
          Fix Version/s 11.0 [ 28320 ]
          Fix Version/s 11.1 [ 28549 ]
          Resolution Fixed [ 1 ]
          Status Open [ 1 ] Closed [ 6 ]

          People

            marko Marko Mäkelä
            elenst Elena Stepanova
            Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              Git Integration

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