Bug #81994 ALTER EVENT errors on definer change
Submitted: 24 Jun 2016 3:39 Modified: 24 Jun 2016 9:34
Reporter: Trey Raymond Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server: DDL Severity:S4 (Feature request)
Version:5.6+ OS:Any
Assigned to: CPU Architecture:Any
Tags: Definer, Event

[24 Jun 2016 3:39] Trey Raymond
Description:
ALTER EVENT throws a syntax error when just changing the definer.  You need to discover and re-specify another part of the definition to get the command to run.

How to repeat:
> CREATE DEFINER=`root`@`localhost` EVENT `test_event` ON SCHEDULE EVERY 10 SECOND STARTS '2000-01-01 00:00:00' ON COMPLETION PRESERVE ENABLE DO select 1;

> alter definer='root'@'127.0.0.1' event test_event;
"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 '' at line 1"

using a same-name rename as a dummy operation also fails:
> alter definer='root'@'127.0.0.1' event test_event rename to test_event;
"Same old and new event name"

Suggested fix:
support an alter with only a definer clause
[24 Jun 2016 9:34] MySQL Verification Team
Hello Trey,

Thank you for the feature request.
This reminds me of Bug #73894.

As hartmut pointed, ALTER EVENT can change the DEFINER, but only if at least one other attribute is changed at the same time.

-- 5.6.31

mysql> CREATE DEFINER=`root`@`localhost` EVENT `test_event` ON SCHEDULE EVERY 10 SECOND STARTS '2000-01-01 00:00:00' ON COMPLETION PRESERVE ENABLE DO select 1;
Query OK, 0 rows affected (0.00 sec)

mysql>  alter definer=`root`@`127.0.0.1` event test_event;
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 '' at line 1
mysql>
mysql>
mysql>  alter definer='root'@'127.0.0.1' event test_event COMMENT '';
Query OK, 0 rows affected (0.00 sec)

Thanks,
Umesh