MySQL Bugs: #61728: Server Status Variable Handler_write incremented with SHOW or SELECT

Bug #61728	Server Status Variable Handler_write incremented with SHOW or SELECT
Submitted:	3 Jul 2011 13:55	Modified:	7 Jun 2012 15:47
Reporter:	Ali Onur Uyar	Email Updates:
Status:	Won't fix	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S3 (Non-critical)
Version:	5.1.54	OS:	Linux (Ubuntu 10.10)
Assigned to:	Paul Dubois	CPU Architecture:	Any
Tags:	Handler_write, SELECT, server, show, STATUS, variable
Triage:	Needs Triage: D3 (Medium)

Description:
The Handler_write Server Status Variable seems to get incremented each time rows are returned to a client as an answer to SHOW query.
This variable must track number of rows inserted into tables as documented in MySQL 5.1 Reference Manual:
http://dev.mysql.com/doc/refman/5.1/en/server-status-variables.html#statvar_Handler_write

The Handler_write variable is not incremented when rows are returned to a client executing a SELECT query. The variable is incremented correctly when new rows ara inserted. The bug only occurs when rows are returned to a client executing a SHOW query.

Further tests indicate that not all SHOW queries increment the Handler_write variable. The following SHOW queries increment the variable:
SHOW GLOBAL VARIABLES
SHOW SESSION VARIABLES
SHOW GLOBAL STATUS
SHOW SESSION STATUS
SHOW DATABASES
SHOW TABLES

While the following SHOW queries do not increment the variable:
SHOW AUTHORS
SHOW CONTRIBUTORS

How to repeat:
On MySQL Server without any other connections execute the following query two times:
SHOW GLOBAL STATUS LIKE 'Handler_%';

The query returns exactly 15 rows and the value of the "Handler_write" Server Global Status variable is incremented by 15 each time the query is executed.

No rows are being inserted, so no change in the "Handler_write" variable which is supposed to track row insertions was expected. The "Handler_write" variable is incremented each time a query which returns rows is executed.

Suggested fix:
The SHOW commands must not increment the Handle_write Server Status Variable.

The bug is also triggered for certain SELECT queries. Eventhough no rows are inserted, deleted or modified, Handler_write is incremented.

Sample query on MyISAM table:
SELECT DISTINCT dst FROM cdr;

Probably all these statements create temporary tables. Check with:

show status like '%tmp%';

before and after problematic statements.

I've confirmed that the Created_tmp_tables counter is incremented by one each time one of the problem queries is executed.

I do not know the internal workings of MySQL, but I do not understand why temporary tables are being created for simple SQL SELECT or SHOW queries.

The current behaviour renders the Handler_write counter totally useless for monitoring, because each time a full table scan is performed on a large table by certain SELECT queries, the Handler_write counter is incremented by the number of rows scanned, jumping up by a few millions, when no rows are being inserted into regular tables.

Please, check if this manual page, http://dev.mysql.com/doc/refman/5.1/en/internal-temporary-tables.html, explains all our findings. 

For me this looks like documented (even if unexpected) behavior and, thus, not a bug.

The use of Internal Temporary Tables documented in the MySQL Reference Manual seems to explain most of my findings, but the SHOW query is not among the case cases that are listed. It seems quite strange to me that internal temporary tables are created for simple SHOW queries on status variables.

Apart from this, in my humble opinion the status variables for Internal Temporary Tables must be separated from the status variables of other tables so that these variables would be useful for monitoring purposes. In fact a three tier status variables could be highly desirable: Regular Tables, In-memory Temporary Tables, On-disk Temporary Tables.

I see 2 things here:

1. There is a valid documentation request to add information on temporary tables usage to every SHOW statement. We have to document, explicitly, when temporary table is used for SHOW <something>, and when it will be on-disk temporary table.

2. Feature request to add new status variables to present more details on the reasons why/what for temporary tables are used.

I'd prefer to concentrate on documentation request here and suggest you to open separate feature request referring to this bug.

"1. There is a valid documentation request to add information on temporary tables usage to every SHOW statement. We have to document, explicitly, when temporary table is used for SHOW <something>, and when it will be on-disk temporary table."

Sorry, no. This can change depending on how the optimizer handles things, and thus is subject to optimizer development. SHOW statement descriptions are not going to track that.