MySQL Bugs: #67873: CREATE TABLE for a table that already exists does not fail immediately

Bug #67873	CREATE TABLE for a table that already exists does not fail immediately
Submitted:	11 Dec 2012 9:37	Modified:	14 Dec 2012 15:18
Reporter:	Ovais Tariq	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	5.5.28	OS:	Linux
Assigned to:		CPU Architecture:	Any

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. I have tested this bug on releases 5.5.23 and 5.5.28 (latest release) and the bug is present in both the versions which leads me to believe that this has been present since MDL was implemented?

How to repeat:
-- 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)

Suggested fix:
The correct behaviour would be for the CREATE TABLE STATEMENT to fail immediately with the error:
ERROR 1050 (42S01): Table 'create_mdl_test' already exists

There is already an identical (or similar at least) report here I believe.

Peter
(not a MySQL/Oracle person)

Peter,

I am sorry I could not find an identical bug report, may be you can point me to it.

Finding something here is almost hopeless! I just think I remember a similar report a couple of months old (I 'skim through' all bug reports every day and read those that may affect our applications).

It is easy to find older bug for this, bug #63144. It's a matter of seconds for well trained person :)

Valeriy,

Indeed CREATE TABLE IF EXISTS ... also blocks on meta_data_locks.

This is not a bug per se, it's an implementation choice. For example, consider the case where the table might be being dropped in another session. Should a CREATE TABLE immediately fail? Metadata locking better have consistent behavior and not change because of the operations that need these locks.

Davi,

I do not quite understand why we should relate the CREATE TABLE happening in one session to the DROP waiting in another session. Those are two different sessions and shouldnt be related, it is as if saying that an insert should not fail immediately on a duplicate key error, because there might be another delete statement running in another session that could be trying to delete the record that caused the duplicate key error.

> I do not quite understand why we should relate the CREATE TABLE happening in one session to the DROP waiting in another session.

The DROP TABLE will be holding an exclusive lock on the table. A CREATE TABLE waits for the lock to be released and proceeds to create the table. If it did not wait for the lock to be released, it would immediately fail as it would assume that the table exists. Correlate this with the suggested fix.

Also, your duplicate key case does not apply here because of MVCC. If you use READ UNCOMMITTED, the INSERT will wait.

The way to change this would be to make CREATE TABLE first take a shared lock and later upgrade it to an exclusive lock if the table does not really exist. The shared lock would suffice to verify whether the table exists or not and would still wait for exclusive locks, but not for other shared locks (like these used in the how to reproduce section). Although this might not be entirely possible, Dmitry Lenev will know.

Davi,

You might have a different understanding, but it still does not look like an expected behaviour. If a table exists CREATE should fail immediately. The insert/delete example that I was giving was merely to draw comparison. If an insert tries to insert a record that already exists and a unique constraint fails then the insert fails immediately, it does not wait assuming that the record that causes the unique key constraint to fail might get deleted by a different transaction that might execute a delete query in the future. What you say about READ-UNCOMMITTED is also wrong, because the INSERT would still fail :)

mysql [localhost] {msandbox} (test) > select * from create_mdl_test;
+----+
| ID |
+----+
|  1 |
|  2 |
|  3 |
|  4 |
|  5 |
+----+
5 rows in set (0.00 sec)

mysql [localhost] {msandbox} (test) > set session binlog_format=row;
Query OK, 0 rows affected (0.00 sec)

mysql [localhost] {msandbox} (test) > set session tx_isolation='READ-UNCOMMITTED';
Query OK, 0 rows affected (0.00 sec)

mysql [localhost] {msandbox} (test) > start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql [localhost] {msandbox} (test) > insert into create_mdl_test(ID) values(1);
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'

So to reiterate the current behaviour of CREATE TABLE does not seem like "expected behaviour" to me.

You forgot to delete the key within a transaction in another session.

Davi,

That is exactly what I meant, the INSERT would wait only if another transaction has deleted a record, hence similarly the CREATE should wait only if a DROP has been executed. And as the insert does not wait if it is executing on its own, similarly the CREATE should not wait if there is no DROP :)

CREATE table does not wait if there is no DROP. Again, think about the lock types that the operations requires. CREATE TABLE starts with an exclusive lock, so it will conflict with any other lock type. The way that InnoDB does locking is somewhat different in that it can check the existence of a row without resorting to exclusive locks all the way through. What you are saying amounts to expecting all cars to behave the same just because they have wheels or something form of resemblance.

Sorry, but I'm gonna stop now, it is very hard to follow the logic here. You said "I do not quite understand why we should relate the CREATE TABLE happening in one session to the DROP waiting in another session." and proceed to correlate that with an analogy about insert on duplicate key, yet they are not comparable if there is no delete in a different session.

Davi,

CREATE TABLE waits even if there is no DROP. It waits for any open transaction that executed a SELECT on that table to finish. Please have a look at the test case provided and then you would see that there is no DROP TABLE in the test case.

Davi,

Indeed it must have been difficult for you to follow the logic because you probably didn't take a look at the bug report and the test case provided. My analogy is completely fine as it compares one transactional behaviour to another.

Duplicate of the bug #63144.

Please, continue a destiny of this behavior in that bug.

I am able to reproduce this bug in 5.6.9-rc. Any chance we could see this bug fixed in 5.6