Bug #42305 It is not possible to list existing key caches
Submitted: 23 Jan 2009 14:53 Modified: 23 Jan 2009 15:20
Reporter: Sergey Petrunya Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Options Severity:S3 (Non-critical)
Version:5.1/6.0 OS:Any
Assigned to: CPU Architecture:Any
Triage: Triaged: D5 (Feature request)

[23 Jan 2009 14:53] Sergey Petrunya
Description:
It is possible to have multiple MyISAM Key Caches as described here: 

http://dev.mysql.com/doc/refman/5.0/en/multiple-key-caches.html

One can create a key cache, and then, provided they know key cache name, use it or delete it.  If you forget the name though, you're out -  it is not possible to list what key caches are present.

How to repeat:
One could expect key cache variables to be listable with SHOW VARIABLES but they are not there:

mysql> set global my_new_key_cache.key_buffer_size=5000000;
Query OK, 0 rows affected (2.40 sec)

mysql> select @@my_new_key_cache.key_buffer_size;
+------------------------------------+
| @@my_new_key_cache.key_buffer_size |
+------------------------------------+
|                            4997120 | 
+------------------------------------+
1 row in set (0.00 sec)

# the following will only show the default key cache
mysql> show variables like '%key_buffer_size%';
+-----------------+--------+
| Variable_name   | Value  |
+-----------------+--------+
| key_buffer_size | 999424 | 
+-----------------+--------+
1 row in set (0.00 sec)

Suggested fix:
According to Serg, this is a known limitation (=not a bug) for 5.0, but is a bug for 5.1/6.0 (can't remember which version)  because that version has a mechanism to dynamically inject variables into SHOW VARIABLES list, which could be used to make non-default key caches show up there.
[23 Jan 2009 15:20] Miguel Solorzano
Thank you for the bug report. Verified as described.
[23 Jan 2009 17:19] Kevin Lewis
Guessing that this bug belongs to Geogi Kodinov
[23 Jan 2009 21:54] Shane Bester
"mysqladmin -uroot debug" can list each keycache.  See also bug #20679