Description:
Suppose that event X is scheduled every 1 second, but X is slow, sometimes it takes more than one second to execute. The event scheduler won't detect such cases and starts execution of event X even if its earlier instance is in running state.
Output of show processlist command:
mysql> show processlist;
+-----+-----------------+-------------------+------+---------+------+-----------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+-----+-----------------+-------------------+------+---------+------+-----------------------------+------------------+
| 326 | root | localhost | test | Query | 0 | NULL | show processlist |
| 327 | event_scheduler | localhost | NULL | Daemon | 0 | Waiting for next activation | NULL |
+-----+-----------------+-------------------+------+---------+------+-----------------------------+------------------+
4 rows in set (0.00 sec)
mysql>
mysql> show processlist;
+-----+-----------------+-------------------+------+---------+------+-----------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+-----+-----------------+-------------------+------+---------+------+-----------------------------+------------------+
| 326 | root | localhost | test | Query | 0 | NULL | show processlist |
| 327 | event_scheduler | localhost | NULL | Daemon | 1 | Waiting for next activation | NULL |
+-----+-----------------+-------------------+------+---------+------+-----------------------------+------------------+
4 rows in set (0.00 sec)
mysql> show processlist;
+-----+-----------------+-------------------+------+---------+------+-----------------------------+-------------------+
| Id | User | Host | db | Command | Time | State | Info |
+-----+-----------------+-------------------+------+---------+------+-----------------------------+-------------------+
| 326 | root | localhost | test | Query | 0 | NULL | show processlist |
| 327 | event_scheduler | localhost | NULL | Daemon | 1 | Waiting for next activation | NULL |
| 427 | qa | % | test | Connect | 10 | Copying to tmp table | INSERT INTO Temp |
| 428 | qa | % | test | Connect | 10 | Copying to tmp table | INSERT INTO Temp |
| 429 | qa | % | test | Connect | 10 | Copying to tmp table | INSERT INTO Temp |
| 430 | qa | % | test | Connect | 9 | Copying to tmp table | INSERT INTO Temp |
| 431 | qa | % | test | Connect | 8 | Copying to tmp table | INSERT INTO Temp |
| 432 | qa | % | test | Connect | 7 | Copying to tmp table | INSERT INTO Temp |
| 433 | qa | % | test | Connect | 6 | Copying to tmp table | INSERT INTO Temp |
| 434 | qa | % | test | Connect | 5 | Copying to tmp table | INSERT INTO Temp |
How to repeat:
Please execute the code below to repeat this issue:
======================================================================
create table temp
(id int,
name varchar(10))engine = innodb;
Insert into temp
values(rand() * 1000, rand() * 1000000000);
Insert into temp
select rand() * 10, rand() * 10000000000 from temp;
Insert into temp
select rand() * 10, rand() * 10000000000 from temp;
Insert into temp
select rand() * 10, rand() * 10000000000 from temp;
Insert into temp
select rand() * 10, rand() * 10000000000 from temp;
Insert into temp
select rand() * 10, rand() * 10000000000 from temp;
Insert into temp
select rand() * 10, rand() * 10000000000 from temp;
Insert into temp
select rand() * 10, rand() * 10000000000 from temp;
Insert into temp
select rand() * 10, rand() * 10000000000 from temp;
Insert into temp
select rand() * 10, rand() * 10000000000 from temp;
Insert into temp
select rand() * 10, rand() * 10000000000 from temp;
DELIMITER $$
/*
This procedure is only for re-producing the above mentioned scenario
*/
create procedure sp_temp()
BEGIN
INSERT INTO TEMP
SELECT t.id, t.name
from temp t, temp t1, temp t2;
END $$
DELIMITER ;
DELIMITER $$
DROP EVENT IF EXISTS `ev_test`;
CREATE EVENT `ev_test`
ON SCHEDULE EVERY 1 SECOND
STARTS cast(current_date() as DATETIME)
ENDS '2037-01-09 03:14:07'
ENABLE
COMMENT 'NULL'
DO CALL test.sp_temp() $$
DELIMITER ;
set global event_scheduler=1;
======================================================================
Suggested fix:
As per documentation this scenario will not occur:
url :http://dev.mysql.com/tech-resources/articles/mysql-events.html#12
==========================================
What if the event is already running?
Suppose that event X is scheduled every 1 second, but X is slow, sometimes it takes more than one second to execute. The event scheduler will detect such cases. It will skip X and try again a second later. I have found that, even on a system with a fairly light load, even if X does little more than insert into a table, a few 'misses' of 1-second events can occur each day.
==========================================
This scenario should not occur.
Description: Suppose that event X is scheduled every 1 second, but X is slow, sometimes it takes more than one second to execute. The event scheduler won't detect such cases and starts execution of event X even if its earlier instance is in running state. Output of show processlist command: mysql> show processlist; +-----+-----------------+-------------------+------+---------+------+-----------------------------+------------------+ | Id | User | Host | db | Command | Time | State | Info | +-----+-----------------+-------------------+------+---------+------+-----------------------------+------------------+ | 326 | root | localhost | test | Query | 0 | NULL | show processlist | | 327 | event_scheduler | localhost | NULL | Daemon | 0 | Waiting for next activation | NULL | +-----+-----------------+-------------------+------+---------+------+-----------------------------+------------------+ 4 rows in set (0.00 sec) mysql> mysql> show processlist; +-----+-----------------+-------------------+------+---------+------+-----------------------------+------------------+ | Id | User | Host | db | Command | Time | State | Info | +-----+-----------------+-------------------+------+---------+------+-----------------------------+------------------+ | 326 | root | localhost | test | Query | 0 | NULL | show processlist | | 327 | event_scheduler | localhost | NULL | Daemon | 1 | Waiting for next activation | NULL | +-----+-----------------+-------------------+------+---------+------+-----------------------------+------------------+ 4 rows in set (0.00 sec) mysql> show processlist; +-----+-----------------+-------------------+------+---------+------+-----------------------------+-------------------+ | Id | User | Host | db | Command | Time | State | Info | +-----+-----------------+-------------------+------+---------+------+-----------------------------+-------------------+ | 326 | root | localhost | test | Query | 0 | NULL | show processlist | | 327 | event_scheduler | localhost | NULL | Daemon | 1 | Waiting for next activation | NULL | | 427 | qa | % | test | Connect | 10 | Copying to tmp table | INSERT INTO Temp | | 428 | qa | % | test | Connect | 10 | Copying to tmp table | INSERT INTO Temp | | 429 | qa | % | test | Connect | 10 | Copying to tmp table | INSERT INTO Temp | | 430 | qa | % | test | Connect | 9 | Copying to tmp table | INSERT INTO Temp | | 431 | qa | % | test | Connect | 8 | Copying to tmp table | INSERT INTO Temp | | 432 | qa | % | test | Connect | 7 | Copying to tmp table | INSERT INTO Temp | | 433 | qa | % | test | Connect | 6 | Copying to tmp table | INSERT INTO Temp | | 434 | qa | % | test | Connect | 5 | Copying to tmp table | INSERT INTO Temp | How to repeat: Please execute the code below to repeat this issue: ====================================================================== create table temp (id int, name varchar(10))engine = innodb; Insert into temp values(rand() * 1000, rand() * 1000000000); Insert into temp select rand() * 10, rand() * 10000000000 from temp; Insert into temp select rand() * 10, rand() * 10000000000 from temp; Insert into temp select rand() * 10, rand() * 10000000000 from temp; Insert into temp select rand() * 10, rand() * 10000000000 from temp; Insert into temp select rand() * 10, rand() * 10000000000 from temp; Insert into temp select rand() * 10, rand() * 10000000000 from temp; Insert into temp select rand() * 10, rand() * 10000000000 from temp; Insert into temp select rand() * 10, rand() * 10000000000 from temp; Insert into temp select rand() * 10, rand() * 10000000000 from temp; Insert into temp select rand() * 10, rand() * 10000000000 from temp; DELIMITER $$ /* This procedure is only for re-producing the above mentioned scenario */ create procedure sp_temp() BEGIN INSERT INTO TEMP SELECT t.id, t.name from temp t, temp t1, temp t2; END $$ DELIMITER ; DELIMITER $$ DROP EVENT IF EXISTS `ev_test`; CREATE EVENT `ev_test` ON SCHEDULE EVERY 1 SECOND STARTS cast(current_date() as DATETIME) ENDS '2037-01-09 03:14:07' ENABLE COMMENT 'NULL' DO CALL test.sp_temp() $$ DELIMITER ; set global event_scheduler=1; ====================================================================== Suggested fix: As per documentation this scenario will not occur: url :http://dev.mysql.com/tech-resources/articles/mysql-events.html#12 ========================================== What if the event is already running? Suppose that event X is scheduled every 1 second, but X is slow, sometimes it takes more than one second to execute. The event scheduler will detect such cases. It will skip X and try again a second later. I have found that, even on a system with a fairly light load, even if X does little more than insert into a table, a few 'misses' of 1-second events can occur each day. ========================================== This scenario should not occur.