MySQL Bugs: #81690: executing prepared statements increases com_stmt_execute but also com

Bug #81690	executing prepared statements increases com_stmt_execute but also com_select
Submitted:	2 Jun 2016 13:55	Modified:	3 Jun 2016 6:40
Reporter:	Kenny Gryp	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Options	Severity:	S3 (Non-critical)
Version:	5.7.12	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
when you execute a prepared statement, Com_select is incremented.

This is not expected according to the documentation:

[The Com_xxx statement counter variables indicate the number of times each xxx statement has been executed. There is one status variable for each type of statement. ]
<http://dev.mysql.com/doc/refman/5.7/en/server-status-variables.html#statvar_Com_xxx>

How to repeat:
run this:

# cat queries 
show global status like 'Com_select';
show global status like 'Com_stmt_execute';
prepare stmt from 'select 1';
execute stmt ;
show global status like 'Com_select';
show global status like 'Com_stmt_execute';
execute stmt ;
show global status like 'Com_select';
show global status like 'Com_stmt_execute';
show global status like 'Com_select';
show global status like 'Com_stmt_execute';
deallocate prepare stmt;

# mysql -N < queries 
Com_select	60
Com_stmt_execute	15
1
Com_select	61
Com_stmt_execute	16
1
Com_select	62
Com_stmt_execute	17
Com_select	62
Com_stmt_execute	17

You can see that on an idle server, com_select also increments.

Suggested fix:

fix: do not increment Com_select when executing a prepared statement
(or last resort: document properly when com_select is incremented)

The reason why I open this bug is to make it clear that this happens. 

We found this while working on some global status dashboards and were doing sum(com_) (a regular thing to see) and it skewed results.

We have to filter out com_stmt_ and make a separate graph for that.

I don't see this being documented clearly.

Hello Kenny,

Thank you for the report and feedback!

Thanks,
Umesh

Hi,

I think this is valuable to be able to see what kind of commands are being executed through prepared statements.   

Yet I think there is a design problem here as the Com_XXX handling was evolving with MySQL architecture. 

There are the COMMANDS which are being run and when there is also  HOW they are being run which can be 

   - Text Statements
   - Prepared Statements
   - Statements ran through Protocol X
   - Replicated statements 
   - Statements executed by stored procedures

Right now it looks like WHAT statement is run and HOW it is run id mixed in Com_XXX at least for prepared statements