CREATE TABLE for a table that already exists does not fail immediately

[Ovais Tariq]

A CREATE TABLE statement containing the name of the table that already exists does not fail immediately, and instead waits on table meta_data_lock if there is a transaction that executed a query that opened that table. This is only evident in 5.5 because this has to do with how Meta Data Locking is implemented.

-- Create a table and insert some data
CREATE TABLE `create_mdl_test` (
`ID` bigint(20) NOT NULL,
PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

insert into create_mdl_test(ID) values (1);
insert into create_mdl_test(ID) values (2);
insert into create_mdl_test(ID) values (3);
insert into create_mdl_test(ID) values (4);
insert into create_mdl_test(ID) values (5);

-- Execute the statements in different sessions as shown below:
session 1 (test) > start transaction;
Query OK, 0 rows affected (0.00 sec)

session 1 (test) > select * from create_mdl_test;
+----+
| ID |
+----+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
+----+
5 rows in set (0.00 sec)

session 2 (test) > CREATE TABLE `create_mdl_test` (
    -> `ID` bigint(20) NOT NULL,
    -> PRIMARY KEY (`ID`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8; <-- this blocks as can be seen from the process list checked on session 3 below

session 3 (test) > show processlist;
+----+----------+-----------+------+---------+------+---------------------------------+------------------------------------------------------------------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+----------+-----------+------+---------+------+---------------------------------+------------------------------------------------------------------------------------------------------+
| 4 | msandbox | localhost | test | Sleep | 45 | | NULL |
| 5 | msandbox | localhost | test | Query | 21 | Waiting for table metadata lock | CREATE TABLE `create_mdl_test` (
`ID` bigint(20) NOT NULL,
PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAUL |
| 6 | msandbox | localhost | test | Query | 0 | NULL | show processlist |
+----+----------+-----------+------+---------+------+---------------------------------+------------------------------------------------------------------------------------------------------+
3 rows in set (0.00 sec)

This bug has been fixed in MariaDB 5.5.29 (https://mariadb.atlassian.net/browse/MDEV-3941), and I have tested it. I have also tested MariaDB 5.5.29 for other MDL related use cases, and except for this case where the CREATE TABLE fails immediately when the table already exists, I have found the rest of the behaviour with respect to MDL, to be the same.

This is a request to port the fix from MariaDB 5.5.29 to Percona Server

[Alexey Kopytov (akopytov)]

The upstream bug has been marked as a duplicate of http://bugs.mysql.com/bug.php?id=63144. We should refer to that one in docs when describing the fix.

[Shahriyar Rzayev (rzayev-sehriyar)]

Percona now uses JIRA for bug reports so this bug report is migrated to: https://jira.percona.com/browse/PS-40