[MDEV-31607] ER_DUP_KEY in mysql.innodb_table_stats upon RENAME on sequence Created: 2023-07-03  Updated: 2023-07-03  Resolved: 2023-07-03

Status: Closed
Project: MariaDB Server
Component/s: Sequences, Storage Engine - InnoDB
Affects Version/s: 10.6, 10.9, 10.10, 10.11, 11.0, 11.1
Fix Version/s: 10.6.15, 10.9.8, 10.10.6, 10.11.5, 11.0.3, 11.1.2, 11.2.1

Type: Bug Priority: Major
Reporter: Elena Stepanova Assignee: Marko Mäkelä
Resolution: Fixed Votes: 0
Labels: None


 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



 Comments   
Comment by Marko Mäkelä [ 2023-07-03 ]

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)

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