Bug #42617 dicsrepancy between 'SELECT * FROM I_S.SESSION_STATUS' and 'SHOW STATUS'
Submitted: 5 Feb 2009 11:43 Modified: 5 Feb 2009 18:11
Reporter: Sergei Glukhov Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Information schema Severity:S3 (Non-critical)
Version:5.0+ OS:Any
Assigned to: CPU Architecture:Any
Triage: Triaged: D3 (Medium)

[5 Feb 2009 11:43] Sergei Glukhov
Description:
Example:

+flush status;
+show status like 'Handler_read_rnd_next';
+Variable_name	Value
+Handler_read_rnd_next	0
+show status like 'Handler_read_rnd_next';
+Variable_name	Value
+Handler_read_rnd_next	0
+flush status;
+select * from information_schema.session_status where variable_name = 'Handler_read_rnd_next';
+VARIABLE_NAME	VARIABLE_VALUE
+HANDLER_READ_RND_NEXT	0
+select * from information_schema.session_status where variable_name = 'Handler_read_rnd_next';
+VARIABLE_NAME	VARIABLE_VALUE
+HANDLER_READ_RND_NEXT	2

MySQL 5.1

SHOW STATUS does not affect Handler_read_rnd_next variable.
select * from I_S.SESSION_STATUS increases Handler_read_rnd_next.

MySQL 5.0

Both 'SHOW STATUS' and 'select * from I_S.SESSION_STATUS' increase
Handler_read_rnd_next variable.

How to repeat:
see example

Suggested fix:
'SHOW STATUS' & 'SELECT FROM ..._STATUS' should not change
the values of the statistics(except Com_show_status).
5.1:
make 'select * from I_S.SESSION_STATUS' behaviour the same as 'SHOW STATUS' has.
5.0:
backport fix for 5.1 into 5.0
[5 Feb 2009 18:11] Sveta Smirnova
Thank you for the report.

Verified as described with exception in 5.0: it fails with " query 'select * from information_schema.session_status where variable_name =
'Handler_read_rnd_next'' failed: 1109: Unknown table 'session_status' in information_schema"