Description:
Queries against INFORMATION_SCHEMA tables can result in warnings because of problems in the underlying objects (invalid MERGE tables, VIEWs with non-existent DEFINERs, etc.). In SELECT statements, these are exposed as warnings, but they cause unknown errors when executing CREATE TABLE t1 AS SELECT statements:
mysql> CREATE TABLE t1 (a INT);
Query OK, 0 rows affected (0.08 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> SELECT * FROM information_schema.columns
-> WHERE table_schema='test' LIMIT 1;
+---------------+--------------+------------+-------------+------------------+--
--------------+-------------+-----------+--------------------------+------------
------------+-------------------+---------------+--------------------+----------
------+---------------+------------+-------+---------------------------------+--
--------------+
| TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME | COLUMN_NAME | ORDINAL_POSITION | C
OLUMN_DEFAULT | IS_NULLABLE | DATA_TYPE | CHARACTER_MAXIMUM_LENGTH | CHARACTER_O
CTET_LENGTH | NUMERIC_PRECISION | NUMERIC_SCALE | CHARACTER_SET_NAME | COLLATION
_NAME | COLUMN_TYPE | COLUMN_KEY | EXTRA | PRIVILEGES | C
OLUMN_COMMENT |
+---------------+--------------+------------+-------------+------------------+--
--------------+-------------+-----------+--------------------------+------------
------------+-------------------+---------------+--------------------+----------
------+---------------+------------+-------+---------------------------------+--
--------------+
| NULL | test | address | ID | 1 | N
ULL | NO | decimal | NULL |
NULL | 22 | 0 | NULL | NULL
| decimal(22,0) | PRI | | select,insert,update,references |
|
+---------------+--------------+------------+-------------+------------------+--
--------------+-------------+-----------+--------------------------+------------
------------+-------------------+---------------+--------------------+----------
------+---------------+------------+-------+---------------------------------+--
--------------+
1 row in set, 1 warning (0.00 sec)
mysql>
mysql> SHOW WARNINGS;
+---------+------+-------------------------------------------------------+
| Level | Code | Message |
+---------+------+-------------------------------------------------------+
| Warning | 1449 | There is no 'not_exists'@'no_such_account' registered |
+---------+------+-------------------------------------------------------+
1 row in set (0.00 sec)
mysql>
mysql> CREATE TABLE counts ENGINE = InnoDB AS
-> SELECT * FROM information_schema.columns
-> WHERE table_schema='test' LIMIT 1;
ERROR 1105 (HY000): Unknown error
mysql>
Note that this is not repeatable in 5.0.38 or 5.0.42, but I was unable to identify a specific bug fix in those versions that is not in 5.0.41 that is related.
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;
Suggested fix:
Don't generate errors when using queries against INFORMATION_SCHEMA tables in CREATE TABLE AS SELECT statements.
Description: Queries against INFORMATION_SCHEMA tables can result in warnings because of problems in the underlying objects (invalid MERGE tables, VIEWs with non-existent DEFINERs, etc.). In SELECT statements, these are exposed as warnings, but they cause unknown errors when executing CREATE TABLE t1 AS SELECT statements: mysql> CREATE TABLE t1 (a INT); Query OK, 0 rows affected (0.08 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> SELECT * FROM information_schema.columns -> WHERE table_schema='test' LIMIT 1; +---------------+--------------+------------+-------------+------------------+-- --------------+-------------+-----------+--------------------------+------------ ------------+-------------------+---------------+--------------------+---------- ------+---------------+------------+-------+---------------------------------+-- --------------+ | TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME | COLUMN_NAME | ORDINAL_POSITION | C OLUMN_DEFAULT | IS_NULLABLE | DATA_TYPE | CHARACTER_MAXIMUM_LENGTH | CHARACTER_O CTET_LENGTH | NUMERIC_PRECISION | NUMERIC_SCALE | CHARACTER_SET_NAME | COLLATION _NAME | COLUMN_TYPE | COLUMN_KEY | EXTRA | PRIVILEGES | C OLUMN_COMMENT | +---------------+--------------+------------+-------------+------------------+-- --------------+-------------+-----------+--------------------------+------------ ------------+-------------------+---------------+--------------------+---------- ------+---------------+------------+-------+---------------------------------+-- --------------+ | NULL | test | address | ID | 1 | N ULL | NO | decimal | NULL | NULL | 22 | 0 | NULL | NULL | decimal(22,0) | PRI | | select,insert,update,references | | +---------------+--------------+------------+-------------+------------------+-- --------------+-------------+-----------+--------------------------+------------ ------------+-------------------+---------------+--------------------+---------- ------+---------------+------------+-------+---------------------------------+-- --------------+ 1 row in set, 1 warning (0.00 sec) mysql> mysql> SHOW WARNINGS; +---------+------+-------------------------------------------------------+ | Level | Code | Message | +---------+------+-------------------------------------------------------+ | Warning | 1449 | There is no 'not_exists'@'no_such_account' registered | +---------+------+-------------------------------------------------------+ 1 row in set (0.00 sec) mysql> mysql> CREATE TABLE counts ENGINE = InnoDB AS -> SELECT * FROM information_schema.columns -> WHERE table_schema='test' LIMIT 1; ERROR 1105 (HY000): Unknown error mysql> Note that this is not repeatable in 5.0.38 or 5.0.42, but I was unable to identify a specific bug fix in those versions that is not in 5.0.41 that is related. 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; Suggested fix: Don't generate errors when using queries against INFORMATION_SCHEMA tables in CREATE TABLE AS SELECT statements.