MySQL Bugs: #64478: CREATE TABLE IF NOT EXISTS waits for table metadata lock

Bug #64478	CREATE TABLE IF NOT EXISTS waits for table metadata lock
Submitted:	28 Feb 2012 2:20	Modified:	17 Apr 2012 8:24
Reporter:	Ryan Lowe	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: Locking	Severity:	S2 (Serious)
Version:	5.5.19, 5.5.21	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
CREATE TABLE IF NOT EXISTS waits for table metadata lock.  By definition, if it needs to wait for a lock, then the table exists and the CREATE statement should complete as it would for an existing table with a code 1050 ( Table already exists ).

Otherwise, scripts that start with this statement to ensure the table is there can get serialized:-(

How to repeat:
[session 1]
issue a long running query on tbl1

[session 2]
issue a CREATE TABLE IF NOT EXISTS tbl1 ... statement

This is easy to verify:

C:\Program Files\MySQL\MySQL Server 5.5\bin>mysql -uroot -proot test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 13
Server version: 5.5.21 MySQL Community Server (GPL)

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> create table if not exists t1(c1 char(10));
Query OK, 0 rows affected, 1 warning (38.48 sec)

mysql> show warnings\G
*************************** 1. row ***************************
  Level: Note
   Code: 1050
Message: Table 't1' already exists
1 row in set (0.00 sec)

So, it waits for the lock just to produce obvious output. 

I am not 100% sure it's a bug, as we can, for example, wait for DROP TABLE t1 running, and theoretically in this case we can proceed with create table after waiting... Let developers decide.

Duplicate of bug#63144