| Bug #35981 | ALTER EVENT causes the server to change the PRESERVE option. | ||
|---|---|---|---|
| Submitted: | 11 Apr 2008 0:00 | Modified: | 17 Oct 2008 17:25 |
| Reporter: | Santo Leto | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Stored Routines | Severity: | S2 (Serious) |
| Version: | 5.1.23-rc | OS: | Windows (XPSP2) |
| Assigned to: | Tatiana Azundris Nuernberg | CPU Architecture: | Any |
| Tags: | ALTER EVENT, preserve | ||
[11 Apr 2008 0:28]
MySQL Verification Team
Thank you for the bug report. Verified on Linux and Windows:
c:\dbs>5.1\bin\mysql -uroot
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.25-rc-nt-log Source distribution
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> SELECT CURRENT_USER(), VERSION(), CONNECTION_ID(), @@global.event_scheduler;
+----------------+------------------+-----------------+--------------------------+
| CURRENT_USER() | VERSION() | CONNECTION_ID() | @@global.event_scheduler |
+----------------+------------------+-----------------+--------------------------+
| root@localhost | 5.1.25-rc-nt-log | 1 | OFF |
+----------------+------------------+-----------------+--------------------------+
1 row in set (0.03 sec)
mysql>
mysql> DROP DATABASE IF EXISTS `alter_event_bug`;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> CREATE DATABASE IF NOT EXISTS `alter_event_bug`;
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> DELIMITER $$
mysql>
mysql> CREATE
-> DEFINER = 'root'@'localhost'
-> EVENT `alter_event_bug`.`test_bug`
-> ON SCHEDULE EVERY 6 MONTH
-> STARTS CURRENT_TIMESTAMP
-> ON COMPLETION PRESERVE
-> DISABLE
-> COMMENT ''
-> DO
-> BEGIN
-> # do nothing
-> END$$
Query OK, 0 rows affected (0.05 sec)
mysql>
mysql> DELIMITER ;
mysql>
mysql> SELECT
-> `EVENT_TYPE`,
-> `EXECUTE_AT`,
-> `STATUS`,
-> `ON_COMPLETION`,
-> `INTERVAL_VALUE`,
-> `INTERVAL_FIELD`,
-> `STARTS`,
-> `ENDS`,
-> `CREATED`,
-> `LAST_ALTERED`,
-> `LAST_EXECUTED`,
-> `SQL_MODE`,
-> `TIME_ZONE`,
-> `DEFINER`,
-> `ORIGINATOR`,
-> `EVENT_COMMENT`,
-> `EVENT_DEFINITION`
-> FROM
-> `INFORMATION_SCHEMA`.`EVENTS`
-> WHERE `EVENT_SCHEMA` = 'alter_event_bug'
-> AND `EVENT_NAME` = 'test_bug'\G
*************************** 1. row ***************************
EVENT_TYPE: RECURRING
EXECUTE_AT: NULL
STATUS: DISABLED
ON_COMPLETION: PRESERVE
INTERVAL_VALUE: 6
INTERVAL_FIELD: MONTH
STARTS: 2008-04-10 21:13:00
ENDS: NULL
CREATED: 2008-04-10 21:13:00
LAST_ALTERED: 2008-04-10 21:13:00
LAST_EXECUTED: NULL
SQL_MODE:
TIME_ZONE: SYSTEM
DEFINER: root@localhost
ORIGINATOR: 3
EVENT_COMMENT:
EVENT_DEFINITION: BEGIN
END
1 row in set (0.05 sec)
mysql>
mysql> ALTER
-> EVENT `alter_event_bug`.`test_bug`
-> ENABLE;
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> SELECT
-> `EVENT_TYPE`,
-> `EXECUTE_AT`,
-> `STATUS`,
-> `ON_COMPLETION`,
-> `INTERVAL_VALUE`,
-> `INTERVAL_FIELD`,
-> `STARTS`,
-> `ENDS`,
-> `CREATED`,
-> `LAST_ALTERED`,
-> `LAST_EXECUTED`,
-> `SQL_MODE`,
-> `TIME_ZONE`,
-> `DEFINER`,
-> `ORIGINATOR`,
-> `EVENT_COMMENT`,
-> `EVENT_DEFINITION`
-> FROM
-> `INFORMATION_SCHEMA`.`EVENTS`
-> WHERE `EVENT_SCHEMA` = 'alter_event_bug'
-> AND `EVENT_NAME` = 'test_bug'\G
*************************** 1. row ***************************
EVENT_TYPE: RECURRING
EXECUTE_AT: NULL
STATUS: ENABLED
ON_COMPLETION: NOT PRESERVE
INTERVAL_VALUE: 6
INTERVAL_FIELD: MONTH
STARTS: 2008-04-10 21:13:00
ENDS: NULL
CREATED: 2008-04-10 21:13:00
LAST_ALTERED: 2008-04-10 21:13:01
LAST_EXECUTED: NULL
SQL_MODE:
TIME_ZONE: SYSTEM
DEFINER: root@localhost
ORIGINATOR: 3
EVENT_COMMENT:
EVENT_DEFINITION: BEGIN
END
1 row in set (0.01 sec)
mysql>
[14 Apr 2008 19:00]
Omer Barnir
workaround: include all event parameters in the alter statement (even those that are not changing
[28 May 2008 6:10]
Tatiana Azundris Nuernberg
notes event-object is always created with a default of "DROP" (NOT PRESERVE), hence we can't tell on ALTER EVENT whether the user really meant DROP, or just got the default as a result of not stating anything specifically. fix like so: - add new state ON_COMPLETION_DEFAULT - default to this state on create event-object - change yy so CREATE EVENT sets this to DROP (default as per the docs) if not given by user - ALTER EVENT will have _DEFAULT set unless anything is stated specifically mysql_event_fill_row() will not update "completion" if ALTER EVENT hands in _DEFAULT. (assert() against getting this flag on CREATE EVENT, just to be sure)
[28 May 2008 6:27]
Tatiana Azundris Nuernberg
note ALTER EVENT w/o explicit [NOT] PRESERVE will call Event_parse_data::check_if_in_the_past() while we don't have PRESERVE/DROP info yet, so if the date is in the past, we're stuck not knowing whether to react according to PRESERVE or to DROP. (added extra tests for that.)
[18 Aug 2008 11:09]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/51829 2654 Tatiana A. Nurnberg 2008-08-18 Bug#35981: ALTER EVENT causes the server to change the PRESERVE option. If [NOT] PRESERVE was not given, parser always defaulted to NOT PRESERVE, making it impossible for the "not given = no change" rule to work in ALTER EVENT. Leaving out the PRESERVE-clause defaults to NOT PRESERVE on CREATE now, and to "no change" in ALTER.
[11 Sep 2008 7:55]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/53785 2684 Tatiana A. Nurnberg 2008-09-11 [merge] Bug#35981: ALTER EVENT causes the server to change the PRESERVE option. If [NOT] PRESERVE was not given, parser always defaulted to NOT PRESERVE, making it impossible for the "not given = no change" rule to work in ALTER EVENT. Leaving out the PRESERVE-clause defaults to NOT PRESERVE on CREATE now, and to "no change" in ALTER.
[15 Sep 2008 13:57]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/54105 2699 Tatiana A. Nurnberg 2008-09-15 [merge] Bug#35981: ALTER EVENT causes the server to change the PRESERVE option. If [NOT] PRESERVE was not given, parser always defaulted to NOT PRESERVE, making it impossible for the "not given = no change" rule to work in ALTER EVENT. Leaving out the PRESERVE-clause defaults to NOT PRESERVE on CREATE now, and to "no change" in ALTER.
[18 Sep 2008 15:21]
Tatiana Azundris Nuernberg
q'd for 5.1.30, 6.0.8
[1 Oct 2008 10:15]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/54904 2753 Georgi Kodinov 2008-10-01 fixed a win32 warning caused by the fix for bug #35981
[1 Oct 2008 10:15]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/54905 2753 Georgi Kodinov 2008-10-01 fixed a win32 warning caused by the fix for bug #35981
[7 Oct 2008 19:44]
Paul DuBois
Noted in 5.1.29 changelog. ALTER EVENT changed the PRESERVE attribute of an event even when PRESERVE was not specified in the statement. Leaving report status unchanged; this is early documentation of an upcoming push into 5.1.29.
[9 Oct 2008 17:57]
Bugs System
Pushed into 5.1.30 (revid:kgeorge@mysql.com-20081001101208-iz5u26r71khu4el7) (version source revid:kgeorge@mysql.com-20081001101208-iz5u26r71khu4el7) (pib:4)
[9 Oct 2008 18:41]
Paul DuBois
Setting report to NDI pending push into 6.0.x.
[17 Oct 2008 16:45]
Bugs System
Pushed into 6.0.8-alpha (revid:kgeorge@mysql.com-20081001101208-iz5u26r71khu4el7) (version source revid:kgeorge@mysql.com-20081001101819-4v95jij3w9sdlpxo) (pib:5)
[17 Oct 2008 17:25]
Paul DuBois
Noted in 6.0.8 changelog.
[28 Oct 2008 21:04]
Bugs System
Pushed into 5.1.29-ndb-6.2.17 (revid:kgeorge@mysql.com-20081001101208-iz5u26r71khu4el7) (version source revid:tomas.ulin@sun.com-20081028140209-u4emkk1xphi5tkfb) (pib:5)
[28 Oct 2008 22:23]
Bugs System
Pushed into 5.1.29-ndb-6.3.19 (revid:kgeorge@mysql.com-20081001101208-iz5u26r71khu4el7) (version source revid:tomas.ulin@sun.com-20081028194045-0353yg8cvd2c7dd1) (pib:5)
[1 Nov 2008 9:49]
Bugs System
Pushed into 5.1.29-ndb-6.4.0 (revid:kgeorge@mysql.com-20081001101208-iz5u26r71khu4el7) (version source revid:jonas@mysql.com-20081101082305-qx5a1bj0z7i8ueys) (pib:5)
Description: ALTER EVENT causes the server to change the PRESERVE option - even if not specified. Doc. Ref.: "Alter Event Syntax" "It is necessary to include only those options in an ALTER EVENT statement which correspond to characteristics that you actually wish to change; options which are omitted retain their existing values. This includes any default values for CREATE EVENT such as ENABLE." How to repeat: Test Script: SELECT CURRENT_USER(), VERSION(), CONNECTION_ID(), @@global.event_scheduler; DROP DATABASE IF EXISTS `alter_event_bug`; CREATE DATABASE IF NOT EXISTS `alter_event_bug`; DELIMITER $$ CREATE DEFINER = 'root'@'localhost' EVENT `alter_event_bug`.`test_bug` ON SCHEDULE EVERY 6 MONTH STARTS CURRENT_TIMESTAMP ON COMPLETION PRESERVE DISABLE COMMENT '' DO BEGIN # do nothing END$$ DELIMITER ; SELECT `EVENT_TYPE`, `EXECUTE_AT`, `STATUS`, `ON_COMPLETION`, `INTERVAL_VALUE`, `INTERVAL_FIELD`, `STARTS`, `ENDS`, `CREATED`, `LAST_ALTERED`, `LAST_EXECUTED`, `SQL_MODE`, `TIME_ZONE`, `DEFINER`, `ORIGINATOR`, `EVENT_COMMENT`, `EVENT_DEFINITION` FROM `INFORMATION_SCHEMA`.`EVENTS` WHERE `EVENT_SCHEMA` = 'alter_event_bug' AND `EVENT_NAME` = 'test_bug'\G ALTER EVENT `alter_event_bug`.`test_bug` ENABLE; SELECT `EVENT_TYPE`, `EXECUTE_AT`, `STATUS`, `ON_COMPLETION`, `INTERVAL_VALUE`, `INTERVAL_FIELD`, `STARTS`, `ENDS`, `CREATED`, `LAST_ALTERED`, `LAST_EXECUTED`, `SQL_MODE`, `TIME_ZONE`, `DEFINER`, `ORIGINATOR`, `EVENT_COMMENT`, `EVENT_DEFINITION` FROM `INFORMATION_SCHEMA`.`EVENTS` WHERE `EVENT_SCHEMA` = 'alter_event_bug' AND `EVENT_NAME` = 'test_bug'\G Script Output: mysql> SELECT CURRENT_USER(), VERSION(), CONNECTION_ID(), @@global.event_schedul er; +----------------+-------------------------+-----------------+------------------ --------+ | CURRENT_USER() | VERSION() | CONNECTION_ID() | @@global.event_sc heduler | +----------------+-------------------------+-----------------+------------------ --------+ | root@localhost | 5.1.23-rc-community-log | 554 | ON | +----------------+-------------------------+-----------------+------------------ --------+ 1 row in set (0.00 sec) mysql> mysql> DROP DATABASE IF EXISTS `alter_event_bug`; Query OK, 0 rows affected (0.01 sec) mysql> CREATE DATABASE IF NOT EXISTS `alter_event_bug`; Query OK, 1 row affected (0.00 sec) mysql> mysql> DELIMITER $$ mysql> mysql> CREATE -> DEFINER = 'root'@'localhost' -> EVENT `alter_event_bug`.`test_bug` -> ON SCHEDULE EVERY 6 MONTH -> STARTS CURRENT_TIMESTAMP -> ON COMPLETION PRESERVE -> DISABLE -> COMMENT '' -> DO -> BEGIN -> # do nothing -> END$$ Query OK, 0 rows affected (0.00 sec) mysql> mysql> DELIMITER ; mysql> mysql> SELECT -> `EVENT_TYPE`, -> `EXECUTE_AT`, -> `STATUS`, -> `ON_COMPLETION`, -> `INTERVAL_VALUE`, -> `INTERVAL_FIELD`, -> `STARTS`, -> `ENDS`, -> `CREATED`, -> `LAST_ALTERED`, -> `LAST_EXECUTED`, -> `SQL_MODE`, -> `TIME_ZONE`, -> `DEFINER`, -> `ORIGINATOR`, -> `EVENT_COMMENT`, -> `EVENT_DEFINITION` -> FROM -> `INFORMATION_SCHEMA`.`EVENTS` -> WHERE `EVENT_SCHEMA` = 'alter_event_bug' -> AND `EVENT_NAME` = 'test_bug'\G *************************** 1. row *************************** EVENT_TYPE: RECURRING EXECUTE_AT: NULL STATUS: DISABLED ON_COMPLETION: PRESERVE INTERVAL_VALUE: 6 INTERVAL_FIELD: MONTH STARTS: 2008-04-11 01:55:18 ENDS: NULL CREATED: 2008-04-11 01:55:18 LAST_ALTERED: 2008-04-11 01:55:18 LAST_EXECUTED: NULL SQL_MODE: TIME_ZONE: SYSTEM DEFINER: root@localhost ORIGINATOR: 2 EVENT_COMMENT: EVENT_DEFINITION: BEGIN END 1 row in set (0.02 sec) mysql> mysql> ALTER -> EVENT `alter_event_bug`.`test_bug` -> ENABLE; Query OK, 0 rows affected (0.00 sec) mysql> mysql> SELECT -> `EVENT_TYPE`, -> `EXECUTE_AT`, -> `STATUS`, -> `ON_COMPLETION`, -> `INTERVAL_VALUE`, -> `INTERVAL_FIELD`, -> `STARTS`, -> `ENDS`, -> `CREATED`, -> `LAST_ALTERED`, -> `LAST_EXECUTED`, -> `SQL_MODE`, -> `TIME_ZONE`, -> `DEFINER`, -> `ORIGINATOR`, -> `EVENT_COMMENT`, -> `EVENT_DEFINITION` -> FROM -> `INFORMATION_SCHEMA`.`EVENTS` -> WHERE `EVENT_SCHEMA` = 'alter_event_bug' -> AND `EVENT_NAME` = 'test_bug'\G *************************** 1. row *************************** EVENT_TYPE: RECURRING EXECUTE_AT: NULL STATUS: ENABLED ON_COMPLETION: NOT PRESERVE INTERVAL_VALUE: 6 INTERVAL_FIELD: MONTH STARTS: 2008-04-11 01:55:18 ENDS: NULL CREATED: 2008-04-11 01:55:18 LAST_ALTERED: 2008-04-11 01:55:19 LAST_EXECUTED: NULL SQL_MODE: TIME_ZONE: SYSTEM DEFINER: root@localhost ORIGINATOR: 2 EVENT_COMMENT: EVENT_DEFINITION: BEGIN END 1 row in set (0.02 sec) Please, note the change of the 'ON_COMPLETION' option: from PRESERVE to NOT PRESERVE.