Server Administration - MySQL - page 779

because it can be put in a library file for use from any number of monitoring ap‐
plications.
The required information can be obtained from either SHOW STATUS or the GLOBAL_STA
TUS table. However, when executing queries within a program and saving the results,
we must account for differences between SHOW statements and selecting from INFORMA
TION_SCHEMA tables. The following queries retrieve similar information, but the column
headings differ in lettercase and sometimes in name, and variable names differ in let‐
tercase:
mysql> SHOW GLOBAL STATUS;
+-----------------------------------------------+-------------+
| Variable_name | Value |
+-----------------------------------------------+-------------+
| Aborted_clients | 1 |
| Aborted_connects | 6 |
…
mysql> SELECT * FROM INFORMATION_SCHEMA.GLOBAL_STATUS;
+-----------------------------------------------+----------------+
| VARIABLE_NAME | VARIABLE_VALUE |
+-----------------------------------------------+----------------+
| ABORTED_CLIENTS | 1 |
| ABORTED_CONNECTS | 6 |
…
To enable applications to be agnostic with respect to whether the variable information
comes from SHOW or INFORMATION_SCHEMA , force variable names to a consistent letter‐
case and use that case in expressions that reference the variables. It doesn't matter which
lettercase you choose, as long as you use it consistently. The following discussion uses
uppercase.
Here's a simple routine (in Ruby) that takes a database handle, fetches the status vari‐
ables, and returns them as a hash of values keyed by names:
def get_status_variables ( dbh )
vars = {}
query = "SELECT VARIABLE_NAME, VARIABLE_VALUE FROM
INFORMATION_SCHEMA.GLOBAL_STATUS"
dbh . select_all ( query ) . each { | name , value | vars [ name . upcase ] = value }
return vars
end
To get the information using a SHOW statement instead, replace the query with this one:
query = "SHOW GLOBAL STATUS"
The code applies the upcase method to the variable names. That way, no matter whether
the routine uses GLOBAL_STATUS or SHOW to obtain the information, the resulting hash
has elements accessed by uppercase variable names.