Description:
When a CREATE TABLE AS SELECT statement fails (see http://bugs.mysql.com/bug.php?id=28787 ), some internal clean-up is not performed correctly. This causes failures in subsequent CREATE TABLE commands. The error depends on the storage engine:
mysql> USE test;
Database changed
mysql>
mysql> DROP TABLE IF EXISTS t1;
Query OK, 0 rows affected (0.08 sec)
mysql> DROP VIEW IF EXISTS v1;
Query OK, 0 rows affected (0.00 sec)
mysql> DROP TABLE IF EXISTS counts;
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> CREATE TABLE t1 (a INT);
Query OK, 0 rows affected (0.17 sec)
mysql> CREATE DEFINER = 'not_exists'@'no_such_account'
-> VIEW v1 AS SELECT * FROM t1;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql>
mysql> # Will fail with unknown error because of bug #28787
mysql>
mysql> CREATE TABLE counts ENGINE = MyISAM AS
-> SELECT * FROM information_schema.columns
-> WHERE table_schema='test' LIMIT 1;
ERROR 1105 (HY000): Unknown error
mysql>
mysql> CREATE TABLE counts (a INT) ENGINE = MyISAM;
ERROR 1105 (HY000): MyISAM table 'counts' is in use (most likely by a MERGE table). Try FLUSH TABLES.
mysql>
mysql> DROP TABLE IF EXISTS counts;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql>
mysql> SHOW WARNINGS;
+-------+------+------------------------+
| Level | Code | Message |
+-------+------+------------------------+
| Note | 1051 | Unknown table 'counts' |
+-------+------+------------------------+
1 row in set (0.00 sec)
mysql>
mysql> CREATE TABLE counts (a INT) ENGINE = MyISAM;
Query OK, 0 rows affected (0.06 sec)
When using InnoDB, the following error is reported, and DROP TABLE IF EXISTS does not clear it:
mysql> CREATE TABLE counts (a INT) ENGINE = InnoDB;
ERROR 1005 (HY000): Can't create table '.\test\counts.frm' (errno: 121)
mysql>
mysql> DROP TABLE IF EXISTS counts;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql>
mysql> SHOW WARNINGS;
+-------+------+------------------------+
| Level | Code | Message |
+-------+------+------------------------+
| Note | 1051 | Unknown table 'counts' |
+-------+------+------------------------+
1 row in set (0.00 sec)
mysql>
mysql> CREATE TABLE counts (a INT) ENGINE = InnoDB;
ERROR 1005 (HY000): Can't create table '.\test\counts.frm' (errno: 121)
MEMORY tables are not affected in any way.
How to repeat:
USE test;
DROP TABLE IF EXISTS t1;
DROP VIEW IF EXISTS v1;
DROP TABLE IF EXISTS counts;
CREATE TABLE t1 (a INT);
CREATE DEFINER = 'not_exists'@'no_such_account'
VIEW v1 AS SELECT * FROM t1;
SELECT * FROM information_schema.columns
WHERE table_schema='test' LIMIT 1;
SHOW WARNINGS;
CREATE TABLE counts ENGINE = MyISAM AS
SELECT * FROM information_schema.columns
WHERE table_schema='test' LIMIT 1;
DROP VIEW IF EXISTS v1;
CREATE TABLE counts (a INT) ENGINE = MyISAM;
Suggested fix:
Clean up resources that are blocking subsequent table creation when initial CREATE command fails.
