MySQL Bugs: #31535: ALTER EVENT fails if 'RENAME...' and 'ON COMPLETION...' clauses are included

Bug #31535	ALTER EVENT fails if 'RENAME...' and 'ON COMPLETION...' clauses are included
Submitted:	11 Oct 2007 14:23	Modified:	29 Nov 2007 18:38
Reporter:	Omer Barnir (OCA)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S3 (Non-critical)
Version:	5.1.22	OS:	Any
Assigned to:	Paul DuBois	CPU Architecture:	Any

Description:
The ALTER EVENT COMMAND allows for all EVENT definition values to be included in the same ALTER statement (see: http://dev.mysql.com/doc/refman/5.1/en/alter-event.html)

However, trying to remane an event and change the 'Preserve' setting in the same alter statement fails.:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.1.22-rc-log MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> create table tb1 (i int);
Query OK, 0 rows affected (0.02 sec)

mysql> CREATE EVENT ev1 ON SCHEDULE EVERY 3 SECOND DO insert into tb1 values (1);
Query OK, 0 rows affected (0.00 sec)

mysql> alter event ev1 RENAME TO ev2 ON COMPLETION PRESERVE;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ON COMPLETION PRESERVE' at line 1

Note that permorming these in seperate steps works (and can be used as a workaround):

mysql> alter event ev1 RENAME TO ev2;
Query OK, 0 rows affected (0.00 sec)

mysql> alter event ev1 ON COMPLETION PRESERVE;
Query OK, 0 rows affected (0.00 sec)

How to repeat:
1) Start a mysql server
2) Login using the 'mysql' client
3) Run the following sqlmysql> use test;
CREATE TABLE tb1 (i INT);
CREATE EVENT ev1 ON SCHEDULE EVERY 3 SECOND DO INSERT INTO tb1 VALUES (1);
ALTER EVENT ev1 RENAME TO ev2 ON COMPLETION PRESERVE;
>>> error shows here

Suggested fix:
Fix to allow for both statements in the same 'alter' command

Not sure if should be considered a bug, there is an order. If a bug, then in the parser.

Server version: 5.1.23-beta-debug Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> CREATE EVENT ev1 ON SCHEDULE EVERY 3 SECOND DO insert into tb1 values (1);
Query OK, 0 rows affected (0.03 sec)

mysql> alter event ev1 RENAME TO ev2 ON COMPLETION PRESERVE;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ON COMPLETION PRESERVE' at line 1
mysql> alter event ev1 ON COMPLETION PRESERVE RENAME TO ev2;
Query OK, 0 rows affected (0.00 sec)

The documentation lsts the RENAME before the ON COMPLETION. So if the ALTER is order dependednt, the doc needs to be updated.

This isn't a bug, but rather a documentation issue. The example given will work if this statement:

  alter event ev1 RENAME TO ev2 ON COMPLETION PRESERVE;

Is changed to:

  alter event ev1 ON COMPLETION PRESERVE RENAME TO ev2;

The documentation that lists the params for "alter event" should be changed to this:

ALTER EVENT
    [DEFINER = { user | CURRENT_USER }]
    event_name
    [ON SCHEDULE schedule]
    [ON COMPLETION [NOT] PRESERVE]
    [RENAME TO new_event_name]
    [ENABLE | DISABLE | DISABLE ON SLAVE]
    [COMMENT 'comment']
    [DO sql_statement]

Thank you for your bug report. This issue has been addressed in the documentation. The updated documentation will appear on our website shortly, and will be included in the next release of the relevant products.