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

Can't write; duplicate key in table 'mysql.innodb_table_stats'

Details

    Description

      Run the following statements, in which the ALTER statement is expected to execute successfully. However, the ALTER statement failed, and it seems that the table `mysql.innodb_table_stats` does not timely update its info.

      CREATE TABLE t1 (c1 INT) ENGINE=InnoDB;
      		 
      CREATE TABLE t2 (c1 INT) ENGINE=InnoDB;
      		 
      ALTER TABLE t1 STATS_PERSISTENT 0;
      		 
      DROP TABLE IF EXISTS t1;
      		 
      ALTER TABLE t2 RENAME t1; -- [23000][1022] (conn=33) Can't write; duplicate key in table 'mysql.innodb_table_stats'

      Attachments

        Activity

          Ascending order - Click to sort in descending order
          John Jove John Jove created issue -
          John Jove John Jove added a comment -

          By the way, If I create the same table t1 as follows, the execution results become valid.

          CREATE TABLE t1 (c1 INT) ENGINE=InnoDB STATS_PERSISTENT=0;
          		 
          CREATE TABLE t2 (c1 INT) ENGINE=InnoDB;
          		 
          DROP TABLE IF EXISTS t1;
          		 
          ALTER TABLE t2 RENAME t1; -- Normal

          John Jove John Jove added a comment - By the way, If I create the same table t1 as follows, the execution results become valid. CREATE TABLE t1 (c1 INT ) ENGINE=InnoDB STATS_PERSISTENT=0; CREATE TABLE t2 (c1 INT ) ENGINE=InnoDB; DROP TABLE IF EXISTS t1; ALTER TABLE t2 RENAME t1; -- Normal
          marko Marko Mäkelä added a comment -

          Thank you for the simple test case. I can reproduce this:

          10.6 2d5cba22a98e54c0c8975d8d7fe7e04981d73c8a

          		 
          mysqltest: At line 6: query 'ALTER TABLE t2 RENAME t1' failed: ER_DUP_KEY (1022): Can't write; duplicate key in table 'mysql.innodb_table_stats'

          The line number is off by one, because I added --source include/have_innodb.inc to make the input valid for our regression test tool.

          I think that the problem is that ALTER TABLE t1 STATS_PERSISTENT 0 fails to delete the statistics.

          marko Marko Mäkelä added a comment - Thank you for the simple test case. I can reproduce this: 10.6 2d5cba22a98e54c0c8975d8d7fe7e04981d73c8a mysqltest: At line 6: query 'ALTER TABLE t2 RENAME t1' failed: ER_DUP_KEY (1022): Can't write; duplicate key in table 'mysql.innodb_table_stats' The line number is off by one, because I added --source include/have_innodb.inc to make the input valid for our regression test tool. I think that the problem is that ALTER TABLE t1 STATS_PERSISTENT 0 fails to delete the statistics.
          marko Marko Mäkelä made changes -
          Field Original Value New Value
          Component/s Storage Engine - InnoDB [ 10129 ]
          Component/s Server [ 13907 ]
          Fix Version/s 10.6 [ 24028 ]
          Fix Version/s 10.11 [ 27614 ]
          Fix Version/s 11.1 [ 28549 ]
          Fix Version/s 11.2 [ 28603 ]
          Fix Version/s 11.4 [ 29301 ]
          Affects Version/s 10.6 [ 24028 ]
          Affects Version/s 10.7 [ 24805 ]
          Affects Version/s 10.8 [ 26121 ]
          Affects Version/s 10.9 [ 26905 ]
          Affects Version/s 10.10 [ 27530 ]
          Affects Version/s 10.11 [ 27614 ]
          Affects Version/s 11.0 [ 28320 ]
          Affects Version/s 11.1 [ 28549 ]
          Affects Version/s 11.2 [ 28603 ]
          Affects Version/s 11.3 [ 28565 ]
          Affects Version/s 11.4 [ 29301 ]
          Assignee Marko Mäkelä [ marko ]
          marko Marko Mäkelä made changes -
          Status Open [ 1 ] Confirmed [ 10101 ]
          serg Sergei Golubchik made changes -
          Summary  Can't write; duplicate key in table 'mysql.innodb_table_stats' Can't write; duplicate key in table 'mysql.innodb_table_stats'
          julien.fritsch Julien Fritsch made changes -
          Fix Version/s 11.1 [ 28549 ]
          marko Marko Mäkelä made changes -
          Status Confirmed [ 10101 ] In Progress [ 3 ]
          marko Marko Mäkelä added a comment -

          I ended up fixing this so that ALTER TABLE t1 STATS_PERSISTENT=0 will delete the statistics.

          In DROP TABLE I would not try to drop InnoDB persistent statistics if they had not been enabled for the table. The reason is that there could be locking conflicts with other DDL statements that would make the operation fail, and many applications might not be prepared to deal with a failing DROP TABLE operation.

          marko Marko Mäkelä added a comment - I ended up fixing this so that ALTER TABLE t1 STATS_PERSISTENT=0 will delete the statistics. In DROP TABLE I would not try to drop InnoDB persistent statistics if they had not been enabled for the table. The reason is that there could be locking conflicts with other DDL statements that would make the operation fail, and many applications might not be prepared to deal with a failing DROP TABLE operation.
          marko Marko Mäkelä made changes -
          Assignee Marko Mäkelä [ marko ] Thirunarayanan Balathandayuthapani [ thiru ]
          Status In Progress [ 3 ] In Review [ 10002 ]
          marko Marko Mäkelä made changes -
          issue.field.resolutiondate 2024-09-30 17:23:02.0 2024-09-30 17:23:01.731
          marko Marko Mäkelä made changes -
          Fix Version/s 10.6.20 [ 29903 ]
          Fix Version/s 10.11.10 [ 29904 ]
          Fix Version/s 11.2.6 [ 29906 ]
          Fix Version/s 11.4.4 [ 29907 ]
          Fix Version/s 11.6.2 [ 29908 ]
          Fix Version/s 10.6 [ 24028 ]
          Fix Version/s 10.11 [ 27614 ]
          Fix Version/s 11.2 [ 28603 ]
          Fix Version/s 11.4 [ 29301 ]
          Assignee Thirunarayanan Balathandayuthapani [ thiru ] Marko Mäkelä [ marko ]
          Resolution Fixed [ 1 ]
          Status In Review [ 10002 ] Closed [ 6 ]

          People

            marko Marko Mäkelä
            John Jove John Jove
            Votes:
            0 Vote for this issue
            Watchers:
            3 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.