Bug #29867 Querying KEY_COLUMN_USAGE stays in Opening tables state

• Submitted: 18 Jul 2007 10:51
• Modified: 12 May 2008 11:26
• Reporter: Jakub Vrána (Candidate Quality Contributor)
• Status: Can't repeat
• Category: MySQL Server: Information schema
• Severity: S3 (Non-critical)
• Version: 5.0.51-community-nt
• OS: Windows
• CPU Architecture: Any
• Tags: qc

[18 Jul 2007 10:51] Jakub Vrána

Description:
Querying information_schema table KEY_COLUMN_USAGE by REFERENCED_TABLE_SCHEMA and REFERENCED_TABLE_NAME columns doesn't return result in a reasonable time and stays in "Opening tables" state.

KEY_COLUMN_USAGE holds 1819 rows. The error is unfortunatelly only occasional (about 1:10) and I don't know about any circumstances which causes it.

How to repeat:
SELECT * FROM KEY_COLUMN_USAGE WHERE REFERENCED_TABLE_SCHEMA = 'test' AND REFERENCED_TABLE_NAME = 'test' ORDER BY ORDINAL_POSITION;

SHOW PROCESSLIST;

Time: 22
State: Opening tables
Info: SELECT * FROM KEY_COLUMN_USAGE ...

[18 Jul 2007 11:21] Sveta Smirnova

Thank you for the report.

Version 5.0.41 is old. Please upgrade to current version 5.0.45, try with it and if you can repeat provide dump of the KEY_COLUMN_USAGE table.

[18 Jul 2007 12:13] Jakub Vrána

Problem persists, dump uploaded.

[18 Jul 2007 12:38] MySQL Verification Team

Thank you for the feedback. Which result you expect?. All the time I got:

mysql> SELECT * FROM KEY_COLUMN_USAGE WHERE REFERENCED_TABLE_SCHEMA = 'test' AND
    -> REFERENCED_TABLE_NAME = 'test' ORDER BY ORDINAL_POSITION;
Empty set (0.01 sec)

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.0.46-nt |
+-----------+
1 row in set (0.00 sec)

[18 Jul 2007 15:16] Jakub Vrána

The query stays in "Opening tables" state for any REFERENCED_TABLE_NAME, e.g.

SELECT * FROM KEY_COLUMN_USAGE WHERE REFERENCED_TABLE_SCHEMA = 'test' AND
REFERENCED_TABLE_NAME = 'znacky' ORDER BY ORDINAL_POSITION;

However only with approx. 1:10 probability.

[19 Jul 2007 14:16] Sveta Smirnova

Thank you for the feedback.

I can not repeat described behaviour in my environment. Please provide your configuration file.

[30 Jan 2008 11:41] Valeriy Kravchuk

Please, try to repeat with a newer version, 5.0.51a, and inform about the results.

[18 Feb 2008 11:28] Jakub Vrána

Problem persists.

[12 May 2008 11:26] MySQL Verification Team

Thank you for the feedback. I could not repeat with server built from latest
source and similar my.ini file, most probably there is an environment detail
which is not present in our test. Please comment here if you are able to
provide a consistent test case. Thanks in advance.