Bug #100110 The database is deadlocked after the partition table is deleted

• Submitted: 4 Jul 2020 9:42
• Modified: 6 Jul 2020 13:58
• Reporter: kfpanda kf
• Status: Not a Bug
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 5.7
• OS: Any
• CPU Architecture: Any

[4 Jul 2020 9:42] kfpanda kf

Description:
Modified test case innodb-truncate-debug.test. If partition tables are created and deleted at the beginning, the database will be deadlocked.

How to repeat:
Below is the add partition table action statement at the beginning of the test case innodb-truncate-debug.test.

```
diff --git a/mysql-test/suite/innodb/t/innodb-truncate-debug.test b/mysql-test/suite/innodb/t/innodb-truncate-debug.test
index b165b1990b6..7d5d76e4d58 100644
--- a/mysql-test/suite/innodb/t/innodb-truncate-debug.test
+++ b/mysql-test/suite/innodb/t/innodb-truncate-debug.test
@@ -4,6 +4,77 @@
 #Enabling valgrind only when this test is run with big test suite
 --source include/no_valgrind_without_big.inc
 
+CREATE TABLE `t3` (
+  `col1` bit(1) DEFAULT NULL,
+  `col2` tinyint(1) DEFAULT NULL,
+  `col3` tinyint(4) DEFAULT NULL,
+  `col4` smallint(6) DEFAULT NULL,
+  `col5` mediumint(9) DEFAULT NULL,
+  `col6` int(11) DEFAULT NULL,
+  `col7` bigint(20) DEFAULT NULL,
+  `col8` float(14,3) DEFAULT NULL,
+  `col9` double(14,3) DEFAULT NULL,
+  `col10` varchar(20) DEFAULT NULL,
+  `col11` text,
+  `col12` enum('a','b','c') DEFAULT NULL,
+  `col13` text,
+  `col14` char(20) DEFAULT NULL,
+  `col15` varbinary(400) DEFAULT NULL,
+  `col16` binary(40) DEFAULT NULL,
+  `col17` blob,
+  `col18` int(11) NOT NULL,
+  `col19` date DEFAULT NULL,
+  `col20` datetime DEFAULT NULL,
+  `col21` timestamp NULL DEFAULT NULL,
+  `col22` time DEFAULT NULL,
+  `col23` year(4) DEFAULT NULL,
+  PRIMARY KEY (`col18`),
+  KEY `idx1` (`col18`),
+  KEY `prefix_idx` (`col14`(10))
+) ENGINE=InnoDB DEFAULT CHARSET=latin1
+/*!50100 PARTITION BY RANGE (col18)
+SUBPARTITION BY HASH (col18)
+SUBPARTITIONS 3
+(PARTITION First_partition VALUES LESS THAN (10) ENGINE = InnoDB,
+ PARTITION Last_partition VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */;
+
+CREATE TABLE `t4` (
+  `col1` bit(1) DEFAULT NULL,
+  `col2` tinyint(1) DEFAULT NULL,
+  `col3` tinyint(4) DEFAULT NULL,
+  `col4` smallint(6) DEFAULT NULL,
+  `col5` mediumint(9) DEFAULT NULL,
+  `col6` int(11) DEFAULT NULL,
+  `col7` bigint(20) DEFAULT NULL,
+  `col8` float(14,3) DEFAULT NULL,
+  `col9` double(14,3) DEFAULT NULL,
+  `col10` varchar(20) DEFAULT NULL,
+  `col11` text,
+  `col12` enum('a','b','c') DEFAULT NULL,
+  `col13` text,
+  `col14` char(20) DEFAULT NULL,
+  `col15` varbinary(400) DEFAULT NULL,
+  `col16` binary(40) DEFAULT NULL,
+  `col17` blob,
+  `col18` int(11) NOT NULL,
+  `col19` date DEFAULT NULL,
+  `col20` datetime DEFAULT NULL,
+  `col21` timestamp NULL DEFAULT NULL,
+  `col22` time DEFAULT NULL,
+  `col23` year(4) DEFAULT NULL,
+  PRIMARY KEY (`col18`),
+  KEY `idx1` (`col18`),
+  KEY `prefix_idx` (`col14`(10))
+) ENGINE=InnoDB DEFAULT CHARSET=latin1
+/*!50100 PARTITION BY RANGE (col18)
+SUBPARTITION BY HASH (col18)
+SUBPARTITIONS 3
+(PARTITION First_partition VALUES LESS THAN (10) ENGINE = InnoDB,
+ PARTITION Last_partition VALUES LESS THAN MAXVALUE ENGINE = InnoDB)*/;
+
+drop table t3 ;
+drop table t4 ;
+
 --echo #
 --echo # Bug #23070734	CONCURRENT TRUNCATE TABLES CAUSE STALLS
 --echo #
```

Suggested fix:

The creation and deletion of partitioned tables should not result in subsequent SQL deadlocks.

[4 Jul 2020 9:45] kfpanda kf

Creating and deleting partitioned tables in the first place should not result in subsequent SQL deadlocks.

[6 Jul 2020 12:06] MySQL Verification Team

Hi Mr. kf,

Thank you for your bug report.

However, this is not a bug.

Deletion of the partitions well may lead to the wait timeouts. This is explained in our Reference Manual.

Not a bug.

[6 Jul 2020 13:45] kfpanda kf

It will wait forever.

[6 Jul 2020 13:58] kfpanda kf

Can you tell me the link to the reference manual on this issue?

[6 Jul 2020 15:18] MySQL Verification Team

Mainly 15.7 and 15.12 in 8.0 manual.