| Bug #27451 | INFORMATION_SCHEMA with EXPLAIN and derived table crashes server | ||
|---|---|---|---|
| Submitted: | 26 Mar 2007 17:27 | Modified: | 26 Mar 2007 18:08 |
| Reporter: | Jay Pipes | Email Updates: | |
| Status: | Can't repeat | Impact on me: | |
| Category: | MySQL Server: Information schema | Severity: | S3 (Non-critical) |
| Version: | 5.0.24a-debian-log | OS: | Linux (Linux 2.6.17-11) |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | I_S | ||
[26 Mar 2007 18:08]
MySQL Verification Team
Thank you for the bug report. I was unable to repeat on Windows with version
5.0.37 and on Linux with current source tree. Could you please try with latest
released version. Thanks in advance.
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 17
Server version: 5.0.37-community-nt MySQL Community Edition (GPL)
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> EXPLAIN SELECT
-> t.TABLE_SCHEMA
-> , t.TABLE_NAME
-> , s.INDEX_NAME
-> , s.COLUMN_NAME
<cut>
-> , ROUND(((s.CARDINALITY / IFNULL(t.TABLE_ROWS, 0.01)) * 100), 2) AS "SEL%"
-> FROM INFORMATION_SCHEMA.STATISTICS s
-> INNER JOIN INFORMATION_SCHEMA.TABLES t
-> ON s.TABLE_SCHEMA = t.TABLE_SCHEMA
-> AND s.TABLE_NAME = t.TABLE_NAME
-> WHERE t.TABLE_SCHEMA != 'mysql'
-> AND t.TABLE_ROWS > 10
-> AND s.CARDINALITY IS NOT NULL
-> AND (s.CARDINALITY / IFNULL(t.TABLE_ROWS, 0.01)) < 1.00
-> ORDER BY "SEL %", TABLE_SCHEMA, TABLE_NAME
-> LIMIT 10;
+----+--------------------+-------+------+---------------+------+---------+------+------+------------------------------------
----------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra
|
+----+--------------------+-------+------+---------------+------+---------+------+------+------------------------------------
----------+
| 1 | PRIMARY | s | ALL | NULL | NULL | NULL | NULL | 2 | Using where; Using temporary; Using
filesort |
| 1 | PRIMARY | t | ALL | NULL | NULL | NULL | NULL | 2 | Using where
|
| 2 | DEPENDENT SUBQUERY | s2 | ALL | NULL | NULL | NULL | NULL | 2 | Using where
|
+----+--------------------+-------+------+---------------+------+---------+------+------+------------------------------------
----------+
3 rows in set (0.23 sec)
mysql>
mysql> EXPLAIN SELECT
-> t.TABLE_SCHEMA
-> , t.TABLE_NAME
-> , s.INDEX_NAME
-> , s.COLUMN_NAME
<cut>
-> AND (s.CARDINALITY / IFNULL(t.TABLE_ROWS, 0.01)) < 1.00
-> ORDER BY "SEL %", TABLE_SCHEMA, TABLE_NAME
-> LIMIT 10;
+----+-------------+------------+------+---------------+------+---------+------+------+--------------------------------------
---------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra
|
+----+-------------+------------+------+---------------+------+---------+------+------+--------------------------------------
---------------+
| 1 | PRIMARY | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Impossible WHERE noticed after readin
g const tables |
| 2 | DERIVED | STATISTICS | ALL | NULL | NULL | NULL | NULL | 2 | Using where; Using temporary; Using f
ilesort |
+----+-------------+------------+------+---------------+------+---------+------+------+--------------------------------------
---------------+
2 rows in set (0.04 sec)
[miguel@light 5.1f]$ bin/mysql -uroot
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.0.40-debug Source distribution
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> EXPLAIN SELECT
-> t.TABLE_SCHEMA
-> , t.TABLE_NAME
-> , s.INDEX_NAME
-> , s.COLUMN_NAME
-> , s.SEQ_IN_INDEX
-> , (
<cut>
-> ORDER BY "SEL %", TABLE_SCHEMA, TABLE_NAME
-> LIMIT 10;
+----+--------------------+-------+------+---------------+------+---------+------+------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------------+-------+------+---------------+------+---------+------+------+----------------------------------------------+
| 1 | PRIMARY | s | ALL | NULL | NULL | NULL | NULL | 2 | Using where; Using temporary; Using filesort |
| 1 | PRIMARY | t | ALL | NULL | NULL | NULL | NULL | 2 | Using where |
| 2 | DEPENDENT SUBQUERY | s2 | ALL | NULL | NULL | NULL | NULL | 2 | Using where |
+----+--------------------+-------+------+---------------+------+---------+------+------+----------------------------------------------+
3 rows in set (0.03 sec)
mysql> EXPLAIN SELECT
-> t.TABLE_SCHEMA
-> , t.TABLE_NAME
<cut>
-> ORDER BY "SEL %", TABLE_SCHEMA, TABLE_NAME
-> LIMIT 10;
+----+-------------+------------+------+---------------+------+---------+------+------+-----------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+------+---------------+------+---------+------+------+-----------------------------------------------------+
| 1 | PRIMARY | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Impossible WHERE noticed after reading const tables |
| 2 | DERIVED | STATISTICS | ALL | NULL | NULL | NULL | NULL | 2 | Using where; Using temporary; Using filesort |
+----+-------------+------------+------+---------------+------+---------+------+------+-----------------------------------------------------+
2 rows in set (0.02 sec)
mysql> select version();
+--------------+
| version() |
+--------------+
| 5.0.40-debug |
+--------------+
1 row in set (0.01 sec)
mysql>
[19 Apr 2007 20:34]
Jay Pipes
Something is really wacky with I_S and EXPLAIN... I retested this on a 5.0.42 server, and after loading the dump file from my local databases, check out the wildly different performances on the I_S SELECTs below. Also note the wacky "Impossible WHERE after reading const tables" in the second EXPLAIN. So, although the server no longer crashes, there are still definite issues. See attached file.
[19 Apr 2007 20:35]
Jay Pipes
Results file from 5.0.42 test
Attachment: bug27451.txt (text/plain), 10.80 KiB.
[19 Apr 2007 20:51]
Jay Pipes
Recommend closing this bug, as the original bug (server crash) seems to have been fixed. I added a new bug for the unrelated comments above: http://bugs.mysql.com/bug.php?id=27963
Description: I am submitting two bugs about INFORMATION_SCHEMA issues. This first one shows a crash when using a derived table upon the I_S when using EXPLAIN before the SELECT. Note that if you remove the EXPLAIN, the crash does not occur (but there are other problems, which I will include in another bug). Note that I have looked through the I_S bugs and decided to open a new one because of the weirdness that this only happens when using EXPLAIN before the SELECT. How to repeat: jpipes@shakedown:~$ mysql --user=root --password=XXXXXXXX Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 8 to server version: 5.0.24a-Debian_9ubuntu0.1-log Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> EXPLAIN SELECT -> t.TABLE_SCHEMA -> , t.TABLE_NAME -> , s.INDEX_NAME -> , s.COLUMN_NAME -> , s.SEQ_IN_INDEX -> , ( -> SELECT MAX(SEQ_IN_INDEX) -> FROM INFORMATION_SCHEMA.STATISTICS s2 -> WHERE s.TABLE_SCHEMA = s2.TABLE_SCHEMA -> AND s.TABLE_NAME = s2.TABLE_NAME -> AND s.INDEX_NAME = s2.INDEX_NAME -> ) AS "COLS_IN_INDEX" -> , s.CARDINALITY AS "CARD" -> , t.TABLE_ROWS AS "ROWS" -> , ROUND(((s.CARDINALITY / IFNULL(t.TABLE_ROWS, 0.01)) * 100), 2) AS "SEL %" -> FROM INFORMATION_SCHEMA.STATISTICS s -> INNER JOIN INFORMATION_SCHEMA.TABLES t -> ON s.TABLE_SCHEMA = t.TABLE_SCHEMA -> AND s.TABLE_NAME = t.TABLE_NAME -> WHERE t.TABLE_SCHEMA != 'mysql' -> AND t.TABLE_ROWS > 10 -> AND s.CARDINALITY IS NOT NULL -> AND (s.CARDINALITY / IFNULL(t.TABLE_ROWS, 0.01)) < 1.00 -> ORDER BY "SEL %", TABLE_SCHEMA, TABLE_NAME -> LIMIT 10; +----+--------------------+-------+------+---------------+------+---------+------+------+----------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+--------------------+-------+------+---------------+------+---------+------+------+----------------------------------------------+ | 1 | PRIMARY | s | ALL | NULL | NULL | NULL | NULL | 2 | Using where; Using temporary; Using filesort | | 1 | PRIMARY | t | ALL | NULL | NULL | NULL | NULL | 2 | Using where | | 2 | DEPENDENT SUBQUERY | s2 | ALL | NULL | NULL | NULL | NULL | 2 | Using where | +----+--------------------+-------+------+---------------+------+---------+------+------+----------------------------------------------+ 3 rows in set (0.06 sec) mysql> EXPLAIN SELECT -> t.TABLE_SCHEMA -> , t.TABLE_NAME -> , s.INDEX_NAME -> , s.COLUMN_NAME -> , s.SEQ_IN_INDEX -> , s2.max_columns AS "COLS_IN_INDEX" -> , s.CARDINALITY AS "CARD" -> , t.TABLE_ROWS AS "ROWS" -> , ROUND(((s.CARDINALITY / IFNULL(t.TABLE_ROWS, 0.01)) * 100), 2) AS "SEL %" -> FROM INFORMATION_SCHEMA.STATISTICS s -> INNER JOIN INFORMATION_SCHEMA.TABLES t -> ON s.TABLE_SCHEMA = t.TABLE_SCHEMA -> AND s.TABLE_NAME = t.TABLE_NAME -> INNER JOIN ( -> SELECT -> TABLE_SCHEMA -> , TABLE_NAME -> , INDEX_NAME -> , MAX(SEQ_IN_INDEX) AS max_columns -> FROM INFORMATION_SCHEMA.STATISTICS -> WHERE TABLE_SCHEMA != 'mysql' -> GROUP BY TABLE_SCHEMA, TABLE_NAME, INDEX_NAME -> ) AS s2 -> ON s.TABLE_SCHEMA = s2.TABLE_SCHEMA -> AND s.TABLE_NAME = s2.TABLE_NAME -> AND s.INDEX_NAME = s2.INDEX_NAME -> WHERE t.TABLE_SCHEMA != 'mysql' -> AND t.TABLE_ROWS > 10 -> AND s.CARDINALITY IS NOT NULL -> AND (s.CARDINALITY / IFNULL(t.TABLE_ROWS, 0.01)) < 1.00 -> ORDER BY "SEL %", TABLE_SCHEMA, TABLE_NAME -> LIMIT 10; ERROR 2013 (HY000): Lost connection to MySQL server during query Suggested fix: No idea.