| Bug #77318 | Selects waiting on MDL when altering partitioned table | ||
|---|---|---|---|
| Submitted: | 11 Jun 2015 14:40 | Modified: | 12 Jun 2015 7:29 |
| Reporter: | Przemyslaw Malkowski | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: InnoDB storage engine | Severity: | S3 (Non-critical) |
| Version: | 5.5.44, 5.5.45 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | mdl | ||
[12 Jun 2015 7:29]
MySQL Verification Team
Hello Przemyslaw, Thank you for the report and test case. Observed this with 5.5.45 builds. Thanks, Umesh
[12 Jun 2015 7:29]
MySQL Verification Team
test results
Attachment: 77318.txt (text/plain), 5.98 KiB.
[12 Jun 2015 12:42]
MySQL Verification Team
I just read https://dev.mysql.com/doc/refman/5.5/en/partitioning-limitations-locking.html It starts out sounds not-so-bad "This is not true for statements effecting partitioned tables using storage engines such as InnoDB, that employ row-level locking and do not actually perform (or need to perform) the locks prior to partition pruning." but when you actually read the DML descriptions, it says entire table gets locked for everything ;)
Description: When a simple ALTER is in progress against paritioned InnoDB table, SELECTs to the table are blocked on MDL. This does not happen for a non-partitioned table. Not reproducible on MySQL 5.6. How to repeat: Create a table with enough rows to make ALTERs take some time, and have it partitioned: mysql [localhost] {msandbox} (db1) > show create table sbtest1\G *************************** 1. row *************************** Table: sbtest1 Create Table: CREATE TABLE `sbtest1` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `k` int(10) unsigned NOT NULL DEFAULT '0', `c` char(120) NOT NULL DEFAULT '', `pad` char(60) NOT NULL DEFAULT '', PRIMARY KEY (`id`), KEY `k_1` (`k`) ) ENGINE=InnoDB AUTO_INCREMENT=2000001 DEFAULT CHARSET=latin1 MAX_ROWS=1000000 /*!50100 PARTITION BY HASH (id) PARTITIONS 8 */ 1 row in set (0.00 sec) Then do in 1st session: mysql [localhost] {msandbox} (db1) > alter table sbtest1 add key(c); Query OK, 0 rows affected (19.45 sec) Records: 0 Duplicates: 0 Warnings: 0 While the alter is running, SELECT from another session waits on MDL: mysql [localhost] {msandbox} ((none)) > show processlist; +----+----------+-----------+------+---------+------+---------------------------------+--------------------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+----------+-----------+------+---------+------+---------------------------------+--------------------------------+ | 4 | msandbox | localhost | db1 | Query | 19 | manage keys | alter table sbtest1 add key(c) | | 6 | msandbox | localhost | db1 | Query | 16 | Waiting for table metadata lock | select * from sbtest1 limit 5 | | 7 | msandbox | localhost | NULL | Query | 0 | NULL | show processlist | +----+----------+-----------+------+---------+------+---------------------------------+--------------------------------+ 3 rows in set (0.00 sec) Suggested fix: Fix the MDL behaviour for partitioned tables to work the same way as it does for non-partitioned ones.