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:
None 
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

[11 Jun 2015 14:40] Przemyslaw Malkowski
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.
[12 Jun 2015 7:29] Umesh Shastry
Hello Przemyslaw,

Thank you for the report and test case.
Observed this with 5.5.45 builds.

Thanks,
Umesh
[12 Jun 2015 7:29] Umesh Shastry
test results

Attachment: 77318.txt (text/plain), 5.98 KiB.

[12 Jun 2015 12:42] Shane Bester
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 ;)