Bug #27706 Undocumented SHOW OPEN TABLES behavior and syntax
Submitted: 8 Apr 2007 22:43 Modified: 15 Jun 2007 13:59
Reporter: Baron Schwartz (Basic Quality Contributor) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Documentation Severity:S3 (Non-critical)
Version:3.23.58 OS:Any
Assigned to: Paul Dubois
Tags: show open tables
Triage: D4 (Minor)

[8 Apr 2007 22:43] Baron Schwartz
Description:
SHOW OPEN TABLES in accepts a database, and doesn't work unless either a database is specifed or a default database is active, in version 3.23.58.  It doesn't show all open tables, just those from that database.  It has very different columns as well.

Possibly related bug:
http://bugs.mysql.com/bug.php?id=12183

How to repeat:
baron@wabbit:~/ $ mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 27 to server version: 3.23.58

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> show open table;
ERROR 1064: You have an error in your SQL syntax near 'table' at line 1
mysql> show open tables;
ERROR 1046: No Database Selected
mysql> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show open tables;
+---------------------+--------------------+
| Open_tables_in_test | Comment            |
+---------------------+--------------------+
| foo                 | cached=1, in_use=0 |
+---------------------+--------------------+
1 row in set (0.00 sec)

mysql> lock tables foo read;
Query OK, 0 rows affected (0.00 sec)

mysql> show open tables;
+---------------------+--------------------+
| Open_tables_in_test | Comment            |
+---------------------+--------------------+
| foo                 | cached=1, in_use=1 |
+---------------------+--------------------+
1 row in set (0.00 sec)

mysql> show open tables from test;
+---------------------+--------------------+
| Open_tables_in_test | Comment            |
+---------------------+--------------------+
| foo                 | cached=1, in_use=1 |
+---------------------+--------------------+
1 row in set (0.00 sec)

mysql> show open tables from mysql;
Empty set (0.01 sec)

Suggested fix:
Since this is an ancient server version, perhaps just a brief note like "before version ??? SHOW OPEN TABLES only shows open tables from one database, and has different column names."

I don't know what version that should be.  I'm guessing 4.0.
[8 Apr 2007 22:46] Baron Schwartz
I'm sorry, I forgot to reference the relevant manual page:

http://dev.mysql.com/doc/refman/4.1/en/show-open-tables.html

Side note: is it more useful for me to specify a manual section (e.g. "13.5.4.14. SHOW OPEN TABLES Syntax") than paste a link?
[9 Apr 2007 0:37] Miguel Solorzano
Thank you for the bug report. Verified as documentation issue.
[15 Jun 2007 13:59] Paul Dubois
Thank you for your bug report. This issue has been addressed in the documentation. The updated documentation will appear on our website shortly, and will be included in the next release of the relevant products.

Side note: Links are fine. :-)