Description:
As stated in summary, a long running DDL statement like CREATE SELECT could cause queries to information_schema.tables to hang.
We can't find this issue reported in MySQL documentation that the side effect of a DDL could block queries to information_schema.tables. So, if it's a known issue, it would be nice to add this to MySQL docs.
How to repeat:
sysbench --test=/usr/share/sysbench/db/oltp.lua --oltp-tables-count=1 --oltp-table-size=10000000 --mysql-user=sbtest --mysql-password=sbtest --mysql-host=127.0.0.1 --mysql-port=5629 --mysql-db=sbtest prepare
Run a long running DDL:
create table sbtesting as select s1.* from sbtest.sbtest1 s1 join sbtest.sbtest1 s2 on s1.c=s2.c join sbtest.sbtest1 s3 on s1.c = s3.c;
On a second session, run and it will hang:
select * from information_schema.tables;
On a third session, check processlist:
mysql> show full processlist;
+----+------+-----------------+--------+---------+------+---------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------+-----------+---------------+
| Id | User | Host | db | Command | Time | State | Info | Rows_sent | Rows_examined |
+----+------+-----------------+--------+---------+------+---------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------+-----------+---------------+
| 12 | root | localhost:38494 | sbtest | Query | 125 | Sending data | create table sbtesting as select s1.* from sbtest.sbtest1 s1 join sbtest.sbtest1 s2 on s1.c=s2.c join sbtest.sbtest1 s3 on s1.c = s3.c | 0 | 0 |
| 13 | root | localhost:39394 | NULL | Query | 122 | Waiting for table metadata lock | select * from information_schema.tables | 0 | 0 |
| 16 | root | localhost:41310 | NULL | Query | 0 | init | show full processlist | 0 | 0 |
+----+------+-----------------+--------+---------+------+---------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------+-----------+---------------+
3 rows in set (0.00 sec)
However, if you try to query anything but sbtest1 and sbtesting, it will succeed.
select * from information_schema.tables where table_schema='employees';
+---------------+--------------+--------------+------------+--------+---------+------------+------------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-------------------+----------+----------------+---------------+
| TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME | TABLE_TYPE | ENGINE | VERSION | ROW_FORMAT | TABLE_ROWS | AVG_ROW_LENGTH | DATA_LENGTH | MAX_DATA_LENGTH | INDEX_LENGTH | DATA_FREE | AUTO_INCREMENT | CREATE_TIME | UPDATE_TIME | CHECK_TIME | TABLE_COLLATION | CHECKSUM | CREATE_OPTIONS | TABLE_COMMENT |
+---------------+--------------+--------------+------------+--------+---------+------------+------------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-------------------+----------+----------------+---------------+
| def | employees | departments | BASE TABLE | InnoDB | 10 | Compact | 9 | 1820 | 16384 | 0 | 16384 | 0 | NULL | 2016-09-28 14:56:49 | NULL | NULL | latin1_swedish_ci | NULL | | |
| def | employees | dept_emp | BASE TABLE | InnoDB | 10 | Compact | 331570 | 36 | 12075008 | 0 | 10518528 | 4194304 | NULL | 2016-09-28 14:56:50 | NULL | NULL | latin1_swedish_ci | NULL | | |
| def | employees | dept_manager | BASE TABLE | InnoDB | 10 | Compact | 24 | 682 | 16384 | 0 | 32768 | 0 | NULL | 2016-09-28 14:56:50 | NULL | NULL | latin1_swedish_ci | NULL | | |
| def | employees | employees | BASE TABLE | InnoDB | 10 | Compact | 299290 | 50 | 15220736 | 0 | 0 | 4194304 | NULL | 2016-09-28 14:56:49 | NULL | NULL | latin1_swedish_ci | NULL | | |
| def | employees | salaries | BASE TABLE | InnoDB | 10 | Compact | 2838426 | 35 | 100270080 | 0 | 36241408 | 5242880 | NULL | 2016-09-28 14:56:50 | NULL | NULL | latin1_swedish_ci | NULL | | |
| def | employees | titles | BASE TABLE | InnoDB | 10 | Compact | 442129 | 46 | 20512768 | 0 | 11059200 | 6291456 | NULL | 2016-09-28 14:56:50 | NULL | NULL | latin1_swedish_ci | NULL | | |
+---------------+--------------+--------------+------------+--------+---------+------------+------------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-------------------+----------+----------------+---------------+
Another test example of that can block IS queries is CREATE TABLE SELECT SLEEP:
1st session:
create table sbtesting as select sleep(3600);
2nd session
select * from information_schema.tables;
Once the long running DDL execution is complete, it would release the metadata lock and so those IS queries can now run without issues.
