Bug #27508 Semantic of SHOW [SESSION|GLOBAL] STATUS
Submitted: 28 Mar 2007 22:19 Modified: 29 Mar 2007 5:35
Reporter: Marc Alff Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Information schema Severity:S4 (Feature request)
Version:5.0, 5.1 OS:Any
Assigned to: Marc Alff CPU Architecture:Any
Triage: Triaged: D5 (Feature request)

[28 Mar 2007 22:19] Marc Alff
Description:
SHOW SESSION STATUS,
SHOW GLOBAL STATUS,
select * from INFORMATION_SCHEMA.SESSION_STATUS (in 5.1),
select * from INFORMATION_SCHEMA.GLOBAL_STATUS (in 5.1),

return status data that either does not make sense, or returns incorrect results.

The expected result is that, if a status variable:

- is a property of a *session* only, it should be displayed in SHOW SESSION
status, but not in SHOW GLOBAL STATUS.

- is a property of the *global* server status, if should be displayed in
SHOW GLOBAL STATUS only, but not in SHOW SESSION STATUS

- is a property of a *session*, that can be aggregated (summed) for the entire
server, if should be displayed in SHOW SESSION STATUS and SHOW GLOBAL STATUS,
and the result for SHOW GLOBAL STATUS should actually be the sum of the status
for each session.

In other words, if SHOW STATUS returns a status variable,
this status variable should have a well defined semantic,
as well as a correct associated value.

Actual results :

Every status variable is displayed both in SESSION and GLOBAL status,
leading to invalid output.

How to repeat:
SHOW SESSION STATUS displays the following:
- Aborted_clients, Aborted_connects, Connections ... (many more)
--> What does that mean ?
How can "Aborted_clients" be a property of a session ?

- Delayed_errors, Delayed_insert_threads, Delayed_writes ... (many more)
--> wrong result, the stats returned are not for the session but global

SHOW GLOBAL STATUS displays the following:
- Ssl_version (and others)
--> what does that mean, considering that Ssl_version seems to be a property
of each session ?

- Ssl_sessions_reused
--> wrong results, the code returns this counter for the current connection,
not the sum (as one could expect with other counters like Bytes_received)
for all the sessions in the server. 

Suggested fix:
Add a bit flag to the SHOW_VAR structure, to indicate if it's a:
- session variable
- global variable
- both

and make use of the flag to filter the status_vars[] array when implementing
SHOW [SESSION|GLOBAL] STATUS,
as well as
select * from INFORMATION_SCHEMA.SESSION_STATUS (in 5.1),
select * from INFORMATION_SCHEMA.GLOBAL_STATUS (in 5.1)
[29 Mar 2007 5:35] Valeriy Kravchuk
Thank you for a bug report. Verified just as described. I am not sure, though, that this behaviour can be changed in 5.0 (or even 5.1), so this may later be treated as a feature request.
[1 Jul 2013 14:17] Marc Alff
See related:
Bug #68969 	add "show all variables" for distinguish global-only variables
[3 Dec 2013 12:30] Simon Mudd
Related: http://bugs.mysql.com/bug.php?id=71057