Description:
A CREATE IF NOT EXIST on an event that currently exists and is enabled will cause multiple instances of the event to run. Disabling the event does not help. If the event is dropped, the event stops running, but when created again, multile instances of the event are still running. The only way to get out of this situation is to restart the server.
I have flagged this as serious, as if someone is using EVENTs in a live environment, having multiple instances of an event may well cause considerable harm. Also, if the event is dropped and recreated with the same name, the "old" events will reappear, running the code of the "new" event, which again may cause harm.
How to repeat:
Enable the event scheduler, then run:
<SQL>
USE test
DROP TABLE IF EXISTS `eventbug_table`;
CREATE TABLE `eventbug_table`(logtime DATETIME);
delimiter //
CREATE EVENT IF NOT EXISTS `eventbug`
ON SCHEDULE EVERY 1 SECOND
ENABLE
DO
BEGIN
INSERT INTO `eventbug_table` VALUES(NOW());
END
//
delimiter ;
delimiter //
CREATE EVENT IF NOT EXISTS `eventbug`
ON SCHEDULE EVERY 1 SECOND
ENABLE
DO
BEGIN
INSERT INTO `eventbug_table` VALUES(NOW());
END
//
delimiter ;
delimiter //
CREATE EVENT IF NOT EXISTS `eventbug`
ON SCHEDULE EVERY 1 SECOND
ENABLE
DO
BEGIN
INSERT INTO `eventbug_table` VALUES(NOW());
END
//
delimiter ;
</SQL>
After this, select from event_bug_table, and you will see multiple tuples for the same time:
mysql> select * from eventbug_table;
+---------------------+
| logtime |
+---------------------+
| 2011-04-29 10:12:37 |
| 2011-04-29 10:12:38 |
| 2011-04-29 10:12:38 |
| 2011-04-29 10:12:38 |
| 2011-04-29 10:12:38 |
| 2011-04-29 10:12:39 |
| 2011-04-29 10:12:39 |
| 2011-04-29 10:12:39 |
| 2011-04-29 10:12:39 |
| 2011-04-29 10:12:40 |
| 2011-04-29 10:12:40 |
| 2011-04-29 10:12:40 |
| 2011-04-29 10:12:40 |
| 2011-04-29 10:12:41 |
| 2011-04-29 10:12:41 |
| 2011-04-29 10:12:41 |
+---------------------+
16 rows in set (0.00 sec)
