Bug #24939 showing ALL structured system variables
Submitted: 9 Dec 2006 22:08 Modified: 12 Feb 2007 12:06
Reporter: Martin Friebe (Gold Quality Contributor) (OCA) Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: General Severity:S4 (Feature request)
Version:4.1 5.0 OS:Any (*)
Assigned to: CPU Architecture:Any
Tags: variables

[9 Dec 2006 22:08] Martin Friebe
Description:
http://dev.mysql.com/doc/refman/4.1/en/structured-system-variables.html

details structured system variables. 
"
To retrieve the cache size, do this:
mysql> SELECT @@global.hot_cache.key_buffer_size;
"

unfortunately this works only if you know the prefix. there is no way to retrieve all variables.

The documentation specifies you cannot do
"SHOW GLOBAL VARIABLES LIKE "
because the combound name is interpreted as string.

The documentation does  not specify, if "show variables" without like, is supposed to show the structured variables. But they are not part of the output.
(this could be considered a bug, rather than feature request)

How to repeat:
SET GLOBAL hot_cache.key_buffer_size = 10*1024*1024;

show variables;

Suggested fix:
show variables should show all structured variables.

in mysql 5.0 add an information schema table

show variables like 'name.type' may not work due to the like syntax, but show variables like 'key_buffer';  could show all key buffers.
[12 Feb 2007 12:06] Valeriy Kravchuk
Thank you for a reasonable feature request/documentation request.
[2 Jul 2007 10:34] MySQL Verification Team
Martin, "mysqladmin debug" will print out all key_caches and their stats to the error log.

<cut>
Key caches:
default
Buffer_size:     209715200
Block_size:           1024
Division_limit:        100
Age_limit:             300
blocks used:             7
not flushed:             0
w_requests:              7
writes:                  7
r_requests:              0
reads:                   0

keycache1
Buffer_size:     314572800
Block_size:           1024
Division_limit:        100
Age_limit:             300
blocks used:             0
not flushed:             0
w_requests:              0
writes:                  0
r_requests:              0
reads:                   0
</cut>