MySQL Bugs: #39173: Unable to remove event's ENDS

Bug #39173	Unable to remove event's ENDS
Submitted:	2 Sep 2008 8:39	Modified:	2 Sep 2008 9:16
Reporter:	Jakub Vrana (Candidate Quality Contributor)	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Stored Routines	Severity:	S3 (Non-critical)
Version:	5.1.26, 5.1 bzr	OS:	Any (Linux, Windows)
Assigned to:		CPU Architecture:	Any
Tags:	qc

Description:
ALTER EVENT is not able to remove ENDS clause in ON SCHEDULE.

How to repeat:
CREATE EVENT tab_future ON SCHEDULE EVERY 1 DAY STARTS '2008-09-01 18:47:35' ENDS '2008-10-01 00:00:00' DO DELETE FROM tab WHERE ts > NOW();

ALTER EVENT tab_future ON SCHEDULE EVERY 1 DAY STARTS '2008-09-01 18:47:35' DO DELETE FROM tab WHERE ts > NOW();

SELECT ENDS FROM information_schema.EVENTS WHERE EVENT_NAME = 'tab_future';
-- output: 2008-10-01 00:00:00
-- expected: NULL

Suggested fix:
Clear ENDS clause if not specified in ALTER EVENT ON SCHEDULE.

Thank you for the report.

Verified as described.

Workaround: drop, then create EVENT

From doc:

"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

--> If you don't specify ENDS in the ALTER EVENT.. it retain its old value.

SQL Script:

USE `TEST`;
DROP DATABASE IF EXISTS `test_bug`;
CREATE DATABASE IF NOT EXISTS `test_bug`;

DELIMITER $$

CREATE EVENT `test_bug`.`tab_future`
	ON SCHEDULE EVERY 1 DAY 
	STARTS '2008-09-01 18:47:35' 
	ENDS '2008-10-01 00:00:00' 	
	DO 
		BEGIN
			# do nothing			
		END 
$$

DELIMITER ;

SELECT `ENDS` FROM `information_schema`.`EVENTS` WHERE `EVENT_NAME` = 'tab_future' AND `EVENT_SCHEMA` = 'test_bug';

DELIMITER $$

ALTER EVENT `test_bug`.`tab_future`
	ON SCHEDULE EVERY 1 DAY 
	STARTS '2008-09-01 18:47:35' 
	DO 
		BEGIN
			# do nothing altered			
		END
$$

DELIMITER ;

SELECT `ENDS` FROM `information_schema`.`EVENTS` WHERE `EVENT_NAME` = 'tab_future' AND `EVENT_SCHEMA` = 'test_bug';

Script Output:

mysql> USE `TEST`;
Database changed

mysql> DROP DATABASE IF EXISTS `test_bug`;
Query OK, 0 rows affected (0.05 sec)

mysql> CREATE DATABASE IF NOT EXISTS `test_bug`;
Query OK, 1 row affected (0.06 sec)

mysql>
mysql> DELIMITER $$
mysql>
mysql> CREATE EVENT `test_bug`.`tab_future`
    ->  ON SCHEDULE EVERY 1 DAY
    ->  STARTS '2008-09-01 18:47:35'
    ->  ENDS '2008-10-01 00:00:00'
    ->  DO
    ->          BEGIN
    ->                  # do nothing
    ->          END
    -> $$
Query OK, 0 rows affected (0.03 sec)

mysql>
mysql> DELIMITER ;
mysql>
mysql> SELECT `ENDS` FROM `information_schema`.`EVENTS` WHERE `EVENT_NAME` = 'tab_future' AND `EVENT_SCHEMA` = 'test_bug';
+---------------------+
| ENDS                |
+---------------------+
| 2008-10-01 00:00:00 |
+---------------------+
1 row in set (0.00 sec)

mysql>
mysql> DELIMITER $$
mysql>
mysql> ALTER EVENT `test_bug`.`tab_future`
    ->  ON SCHEDULE EVERY 1 DAY
    ->  STARTS '2008-09-01 18:47:35'
    ->  DO
    ->          BEGIN
    ->                  # do nothing altered
    ->          END
    -> $$
Query OK, 0 rows affected (0.05 sec)

mysql>
mysql> DELIMITER ;
mysql>
mysql> SELECT `ENDS` FROM `information_schema`.`EVENTS` WHERE `EVENT_NAME` = 'tab_future' AND `EVENT_SCHEMA` = 'test_bug';
+---------------------+
| ENDS                |
+---------------------+
| 2008-10-01 00:00:00 |
+---------------------+
1 row in set (0.02 sec)

Suggested fix:

I don't think that clearing ENDS clause if not specified in ALTER EVENT is a good idea.

Perhaps it could be more convenient to parse the NULL value for STARTS and ENDS:

ALTER EVENT `test_bug`.`tab_future`
	ON SCHEDULE EVERY 1 DAY 
	STARTS '2008-09-01 18:47:35' 
	ENDS NULL
	DO 
		BEGIN
			# do nothing altered			
		END
$$

-> I have an alter stat. that allows me to clear the ENDS opt without the need of dropping and recreating the event
-> I preserve backward compatibility with apps already using Scheduled Events (they don't set the ENDS opt each time they execute an ALTER EVENT..)
-> I preserve security: the event ends at the original specified date even if, after many ALTERs, I forgot to set the ENDS opt