MySQL Bugs: #16422: Events: Only global statistics updated

Bug #16422	Events: Only global statistics updated
Submitted:	12 Jan 2006 4:15	Modified:	15 Aug 2007 8:55
Reporter:	Peter Gulutzan	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S3 (Non-critical)
Version:	5.1.6-alpha-debug	OS:	Linux (SUSE 10.0)
Assigned to:	Jon Stephens	CPU Architecture:	Any
Tags:	events, status variables

Description:
SHOW STATUS has Com_create_event and Com_drop_event,
but the events themselves don't cause updates of all
statistics.

How to repeat:

create table t_30 (s1 int);
show status;
insert into t_30 values (0);
show status;
-- notice that the value for Com_insert has gone up
create event e_30 on schedule every 1 second do insert into t values (0); 
set global event_scheduler = 1;
-- wait 5 seconds
show status;
-- notice that the value for Com_insert has not gone up

Thank you for a problem report. Verified as described on 5.1-BK (ChangeSet@1.2012.25.1, 2006-01-12 11:41:57+04:00):

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.1.6-alpha

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

mysql> create table t_30 (s1 int);
Query OK, 0 rows affected (0.01 sec)

mysql> show status like 'com_in%';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Com_insert        | 0     |
| Com_insert_select | 0     |
+-------------------+-------+
2 rows in set (0.00 sec)

mysql> insert into t_30 values(0);
Query OK, 1 row affected (0.00 sec)

mysql> show status like 'com_in%';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Com_insert        | 1     |
| Com_insert_select | 0     |
+-------------------+-------+
2 rows in set (0.00 sec)

mysql> create event e_30 on schedule every 1 second do insert into test.t_30 values (0);
Query OK, 1 row affected (0.01 sec)

mysql> set global event_scheduler = 1;
Query OK, 0 rows affected (0.01 sec)

mysql> select count(*) from t_30;
+----------+
| count(*) |
+----------+
|        2 |
+----------+
1 row in set (0.00 sec)

mysql> show status like 'com_in%'; e every 1 second
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Com_insert        | 1     |
| Com_insert_select | 0     |
+-------------------+-------+
2 rows in set (0.00 sec)

mysql> select count(*) from t_30;
+----------+
| count(*) |
+----------+
|        8 |
+----------+
1 row in set (0.00 sec)

mysql> show status like 'com_in%';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Com_insert        | 1     |
| Com_insert_select | 0     |
+-------------------+-------+
2 rows in set (0.01 sec)

mysql> set global event_scheduler = 0;
Query OK, 0 rows affected (0.00 sec)

A possible solution could be to account events the same way as compound SQL statements, when these are implemented.

Event execution can not and should not update session status, since event execution happens outside the context of the current session.
The global status is updated correctly:
mysql> insert into t_30 values (0);
Query OK, 1 row affected (0.00 sec)

mysql> show status like 'Com_insert';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Com_insert    | 1     | 
+---------------+-------+
1 row in set (0.00 sec)

mysql> show global status like 'Com_insert';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Com_insert    | 1     | 
+---------------+-------+
1 row in set (0.00 sec)

mysql> set global event_scheduler=1;
Query OK, 0 rows affected (0.00 sec)

mysql> create event e_30 on schedule every 1 second do insert into t values (0); 
Query OK, 0 rows affected (0.00 sec)

mysql> show global status like 'Com_insert';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Com_insert    | 5     | 
+---------------+-------+
1 row in set (0.01 sec)

mysql> show global status like 'Com_insert';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Com_insert    | 6     | 
+---------------+-------+
1 row in set (0.00 sec)

mysql> show global status like 'Com_insert';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Com_insert    | 7     | 
+---------------+-------+
1 row in set (0.00 sec)

mysql> show global status like 'Com_insert';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Com_insert    | 8     | 
+---------------+-------+
1 row in set (0.00 sec)

mysql> drop event e_30;
Query OK, 0 rows affected (0.34 sec)

mysql> show global status like 'Com_insert';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Com_insert    | 16    | 
+---------------+-------+
1 row in set (0.00 sec)

Execution of an event correctly updates Com_insert counter, but incorrectly
updates 'Questions' counter, similarly to stored procedures (see Bug#24289).
This will be fixed along with the fix for Bug#24289.

Request to the documentation team: clarify how event execution influences server status.
Conceptually, each execution of an event is equivalent to execution in a new connection established to the server, so only changes to the global status
can be observed externally.

Changed category to Docs since this is expected behaviour and the docs need to reflect this.

Thank you for your bug report. This issue has been addressed in the documentation. The updated documentation will appear on our website shortly, and will be included in the next release of the relevant products.

Updated "Effects on statement counts" item in Event Limitations/Restrictions section of 5.1/5.2 Manual per notes in bug report.