Bug #13328 Handler_read_* variable are reported as local values

• Submitted: 19 Sep 2005 20:09
• Modified: 21 Sep 2005 17:56
• Reporter: jocelyn fournier (Silver Quality Contributor)
• Status: Not a Bug
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 5.0.14-rc
• OS: Linux (linux)
• CPU Architecture: Any

[19 Sep 2005 20:09] jocelyn fournier

Description:
Hi,

The Handler_read_* variables seems to be reported as local values instead of global as before.

On a really busy server :

mysqladmin -uroot -p debug

handler status:
read_key:    525505653
read_next:   563624837
read_rnd       3755415
read_first:    7912062
write:       200396845
delete         7657373
update:       17004227

mysql> SHOW STATUS LIKE 'Handler_read%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| Handler_read_first    | 0     |
| Handler_read_key      | 0     |
| Handler_read_next     | 0     |
| Handler_read_prev     | 0     |
| Handler_read_rnd      | 0     |
| Handler_read_rnd_next | 0     |
+-----------------------+-------+
6 rows in set (0.00 sec)

If I try a little bit later :

mysql> SHOW STATUS LIKE '%Handler_read%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| Handler_read_first    | 0     |
| Handler_read_key      | 0     |
| Handler_read_next     | 0     |
| Handler_read_prev     | 0     |
| Handler_read_rnd      | 0     |
| Handler_read_rnd_next | 7    |
+-----------------------+-------+
6 rows in set (0.00 sec)

(only read_rnd_next increases)

If I quit the MySQL client and I restart it, Handler_read_rnd_next is back to 0.

Are now Handler_read* local values of the client ? Is it intended ?

Thanks,
  Jocelyn

How to repeat:
SHOW STATUS LIKE 'Handler_read%'; in the mysql client

[21 Sep 2005 17:56] Jorge del Conde

Hi!

This behaviour is not a bug.

The GLOBAL and SESSION  options are new in MySQL 5.0.2 With GLOBAL, you get the status values for all connections to MySQL. With SESSION, you get the status values for the current connection. If you use neither option, the default is SESSION. LOCAL is a synonym for SESSION.

In other words, SHOW STATUS defaults to local status in 5.0 so you have to use SHOW GLOBAL STATUS:

mysql> SHOW GLOBAL STATUS LIKE 'Handler_read%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| Handler_read_first    | 3     |
| Handler_read_key      | 0     |
| Handler_read_next     | 2     |
| Handler_read_prev     | 0     |
| Handler_read_rnd      | 0     |
| Handler_read_rnd_next | 207   |
+-----------------------+-------+
6 rows in set (0.00 sec)

mysql> exit
Bye
root-/usr/local# mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3 to server version: 5.0.12-beta

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> SHOW GLOBAL STATUS LIKE 'Handler_read%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| Handler_read_first    | 3     |
| Handler_read_key      | 0     |
| Handler_read_next     | 2     |
| Handler_read_prev     | 0     |
| Handler_read_rnd      | 0     |
| Handler_read_rnd_next | 291   |
+-----------------------+-------+
6 rows in set (0.00 sec)

mysql>