Bug #16417 Events: busy servers miss events
Submitted: 12 Jan 2006 4:08 Modified: 17 Sep 2006 5:49
Reporter: Peter Gulutzan Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Stored Routines Severity:S3 (Non-critical)
Version:5.1.6-alpha-debug OS:Linux (SUSE 10.0)
Assigned to: Andrey Hristov CPU Architecture:Any

[12 Jan 2006 4:08] Peter Gulutzan
Description:
If the server is busy, it misses events. That is,
if an event takes longer than 1 second to execute,
and it's an "every 1 second" event, then execution
will not happen every 1 second.

How to repeat:

Assume the existence of a database db1.

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

mysql>  create event e_24
    ->     on schedule
    ->     every 1 second
    ->     on completion preserve
    ->     do insert into db1.t_24 select current_timestamp,sleep(1);
Query OK, 1 row affected (0.01 sec)

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

Wait a while, then I see what is in table t:

mysql> select * from t_24;
+---------------------+------+
| s1                  | s2   |
+---------------------+------+
| 2006-01-11 17:23:05 |    0 |
| 2006-01-11 17:23:07 |    0 |
| 2006-01-11 17:23:09 |    0 |
| 2006-01-11 17:23:11 |    0 |
| 2006-01-11 17:23:13 |    0 |
| 2006-01-11 17:23:15 |    0 |
| 2006-01-11 17:23:17 |    0 |
| 2006-01-11 17:23:19 |    0 |
+---------------------+------+
8 rows in set (0.00 sec)

The EVERY 1 SECOND event is working every 2 seconds.
[12 Jan 2006 16:00] MySQL Verification Team
miguel@hegel:~/dbs/5.1> bin/mysql -uroot  db1
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 5.1.6-alpha-debug

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

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

mysql> create event e_24
    -> on schedule
    -> every 1 second
    -> on completion preserve
    -> do insert into db1.t_24 select current_timestamp,sleep(1);
Query OK, 1 row affected (0.02 sec)

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

mysql> select * from t_24;
+---------------------+------+
| s1                  | s2   |
+---------------------+------+
| 2003-04-12 14:01:10 |    0 |
| 2003-04-12 14:01:12 |    0 |
| 2003-04-12 14:01:14 |    0 |
| 2003-04-12 14:01:16 |    0 |
| 2003-04-12 14:01:18 |    0 |
| 2003-04-12 14:01:20 |    0 |
+---------------------+------+
6 rows in set (0.00 sec)
[15 Sep 2006 15:11] Dmitry Lenev
Fixed in 5.1.12
[15 Sep 2006 20:49] Peter Gulutzan
I tried the exact same statements as I used in the original bug report.
This time, the client hangs during "select * from t_24;" and stays
that way until, on another connection, I say "set global event_scheduler=0".

Accordingly, I have re-opened this bug.
[16 Sep 2006 8:24] Dmitry Lenev
Hi, Peter!

If you inspect contents of 't_24' table after stopping event scheduler (or simply use innodb engine for this table) you will notice that your event was executed every second. This means that problem you have reported originally is fixed.
So I think problem that you observe currently is separate bug. I have reported it as bug#22412 and moved this report back to Documenting state.
[17 Sep 2006 5:49] Jon Stephens
Thank you for your bug report. This issue has been committed to our source repository of that product and will be incorporated into the next release.

If necessary, you can access the source repository and build the latest available version, including the bug fix. More information about accessing the source trees is available at

    http://dev.mysql.com/doc/en/installing-source.html

Documented bugfix in 5.1.12 changelog.