On July 12 2007 I posted Bug #29768 - ‘ALTER EVENT .. RENAME TO .. drops the event if event_scheduler is ON’ - for MySQL® 5.1.20-beta and Miguel verified it one hour after my post with MySQL® 5.1.21-beta.
Some months later, on Oct 19 2007, Damien cannot reproduce that bug with the latest versions 5.1.23-rc and 5.2.6-alpha, but unfortunately I was still able to reproduce it with version 5.1.23-rc, as I posted on Feb 16 2008.
Now it seem that I can’t reproduce it with the new 5.1.24-rc. Maybe it has been fixed by fixing other bugs. The problem is that I am still able to reproduce it with version 6.0.4-alpha.
Just to summarize:
5.1.23-rc - VERIFIED
5.1.24-rc - CAN’T REPEAT
6.0.4-alpha - VERIFIED
What do you think? What about version 6.0.5-alpha?
Here’s the exact Script for reproducing it with old versions of the Server, as Miguel commented on July 12 2007:
DROP DATABASE IF EXISTS event_test;
CREATE DATABASE event_test;
USE event_test;
SET GLOBAL event_scheduler = ‘OFF’;
delimiter $$
CREATE DEFINER = ‘root’@'localhost’ EVENT `event_test`.`at_event` ON SCHEDULE AT
CURRENT_TIMESTAMP ON COMPLETION PRESERVE ENABLE DO
BEGIN
#do nothing
END$$
delimiter ;
SHOW EVENTS FROM `event_test`\G
SET GLOBAL event_scheduler=’ON’;
ALTER EVENT `event_test`.`at_event` RENAME TO `event_test`.`at_event2`;
SHOW EVENTS FROM `event_test`\G
And here’s the script output:
5.1.23-rc - VERIFIED
mysql> DROP DATABASE IF EXISTS event_test;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> CREATE DATABASE event_test;
Query OK, 1 row affected (0.00 sec)
mysql> USE event_test;
Database changed
mysql> SET GLOBAL event_scheduler = ‘OFF’;
Query OK, 0 rows affected (0.00 sec)
mysql> delimiter $$
mysql> CREATE DEFINER = ‘root’@'localhost’ EVENT `event_test`.`at_event` ON SCH
EDULE AT
-> CURRENT_TIMESTAMP ON COMPLETION PRESERVE ENABLE DO
-> BEGIN
-> #do nothing
-> END$$
Query OK, 0 rows affected (0.00 sec)
mysql> delimiter ;
mysql> SHOW EVENTS FROM `event_test`\G
*************************** 1. row ***************************
Db: event_test
Name: at_event
Definer: root@localhost
Time zone: SYSTEM
Type: ONE TIME
Execute at: 2008-05-02 09:14:23
Interval value: NULL
Interval field: NULL
Starts: NULL
Ends: NULL
Status: ENABLED
Originator: 1
character_set_client: latin1
collation_connection: latin1_swedish_ci
Database Collation: latin1_swedish_ci
1 row in set (0.02 sec)
mysql> SET GLOBAL event_scheduler=’ON’;
Query OK, 0 rows affected (0.00 sec)
mysql> ALTER EVENT `event_test`.`at_event` RENAME TO `event_test`.`at_event2`;
Query OK, 0 rows affected (0.00 sec)
mysql> SHOW EVENTS FROM `event_test`\G
Empty set (0.02 sec)
mysql>
5.1.24-rc - CAN’T REPEAT
mysql> DROP DATABASE IF EXISTS event_test;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> CREATE DATABASE event_test;
Query OK, 1 row affected (0.00 sec)
mysql> USE event_test;
Database changed
mysql> SET GLOBAL event_scheduler = ‘OFF’;
Query OK, 0 rows affected (0.00 sec)
mysql> delimiter $$
mysql> CREATE DEFINER = ‘root’@'localhost’ EVENT `event_test`.`at_event` ON SCH
EDULE AT
-> CURRENT_TIMESTAMP ON COMPLETION PRESERVE ENABLE DO
-> BEGIN
-> #do nothing
-> END$$
Query OK, 0 rows affected (0.03 sec)
mysql> delimiter ;
mysql> SHOW EVENTS FROM `event_test`\G
*************************** 1. row ***************************
Db: event_test
Name: at_event
Definer: root@localhost
Time zone: SYSTEM
Type: ONE TIME
Execute at: 2008-05-02 09:13:26
Interval value: NULL
Interval field: NULL
Starts: NULL
Ends: NULL
Status: ENABLED
Originator: 1
character_set_client: latin1
collation_connection: latin1_swedish_ci
Database Collation: latin1_swedish_ci
1 row in set (0.00 sec)
mysql> SET GLOBAL event_scheduler=’ON’;
Query OK, 0 rows affected (0.00 sec)
mysql> ALTER EVENT `event_test`.`at_event` RENAME TO `event_test`.`at_event2`;
Query OK, 0 rows affected (0.00 sec)
mysql> SHOW EVENTS FROM `event_test`\G
*************************** 1. row ***************************
Db: event_test
Name: at_event2
Definer: root@localhost
Time zone: SYSTEM
Type: ONE TIME
Execute at: 2008-05-02 09:13:26
Interval value: NULL
Interval field: NULL
Starts: NULL
Ends: NULL
Status: ENABLED
Originator: 1
character_set_client: latin1
collation_connection: latin1_swedish_ci
Database Collation: latin1_swedish_ci
1 row in set (0.00 sec)
mysql>
6.0.4-alpha - VERIFIED
mysql> DROP DATABASE IF EXISTS event_test;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> CREATE DATABASE event_test;
Query OK, 1 row affected (0.00 sec)
mysql> USE event_test;
Database changed
mysql> SET GLOBAL event_scheduler = ‘OFF’;
Query OK, 0 rows affected (0.00 sec)
mysql> delimiter $$
mysql> CREATE DEFINER = ‘root’@'localhost’ EVENT `event_test`.`at_event` ON SCH
EDULE AT
-> CURRENT_TIMESTAMP ON COMPLETION PRESERVE ENABLE DO
-> BEGIN
-> #do nothing
-> END$$
Query OK, 0 rows affected (0.03 sec)
mysql> delimiter ;
mysql> SHOW EVENTS FROM `event_test`\G
*************************** 1. row ***************************
Db: event_test
Name: at_event
Definer: root@localhost
Time zone: SYSTEM
Type: ONE TIME
Execute at: 2008-05-02 09:28:19
Interval value: NULL
Interval field: NULL
Starts: NULL
Ends: NULL
Status: ENABLED
Originator: 1
character_set_client: latin1
collation_connection: latin1_swedish_ci
Database Collation: latin1_swedish_ci
1 row in set (0.00 sec)
mysql> SET GLOBAL event_scheduler=’ON’;
Query OK, 0 rows affected (0.00 sec)
mysql> ALTER EVENT `event_test`.`at_event` RENAME TO `event_test`.`at_event2`;
Query OK, 0 rows affected (0.02 sec)
mysql> SHOW EVENTS FROM `event_test`\G
Empty set (0.02 sec)
mysql>
