Percona Server with XtraDB

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

Reported by Ovais Tariq on 2013-02-16
6
This bug affects 1 person
Affects Status Importance Assigned to Milestone
MySQL Server
Unknown
Unknown
Percona Server
Status tracked in 5.6
5.1
Undecided
Unassigned
5.5
High
Sergei Glushchenko
5.6
High
Laurynas Biveinis

Bug Description

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

tags: added: i29581
Alexey Kopytov (akopytov) wrote :

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.

Changed in percona-server:
status: New → Triaged
importance: Undecided → High
Changed in percona-server:
assignee: nobody → Sergei Glushchenko (sergei.glushchenko)
status: Triaged → In Progress
tags: added: upstream
To post a comment you must log in.
This report contains Public information  Edit
Everyone can see this information.

Other bug subscribers

Remote bug watches

Bug watches keep track of this bug in other bug trackers.