Bug: Explicit definer is ignored when I create an event

MySQL & MariaDB Percona Server for MySQL 5.7
1

Hi,I logged in to Percona MySQL5.7 (version 5.7.24-26-log Percona Server (GPL), Release 26, Revision c8fe767) as user ‘andrew’@‘localhost’.
And I need to create an event with DEFINER = ‘root’@‘localhost’, and I am executing

CREATE DEFINER = 'root'@'localhost'
EVENT ps_helper.event1
ON SCHEDULE EVERY '1' DAY
STARTS '2020-04-22 10:12:42'
DO 
BEGIN
-- some code is here
END;
ALTER EVENT ps_helper.event1 ENABLE<br>

but when I execute CREATE EVENT event1 then I get:

CREATE DEFINER = 'andrew'@'localhost'
EVENT ps_helper.event1
ON SCHEDULE EVERY '1' DAY
STARTS '2020-04-22 10:12:42'
DO 
BEGIN
-- some code is here
END;
ALTER EVENT ps_helper.event1 ENABLE

As you can see MySql replaced definer from ‘root’@‘localhost’ to my current user ‘andrew’@‘localhost’.The same issue happens in Percona MySql8 (version 8.0.18-9 Percona Server (GPL), Release 9, Revision 53e606f)Why MySql ignores definer when I create the event?
And how to force MySql to use definer I want?Are there any plans to fix this bug in any nearest version?




2

Can I just make sure with you that the user root has the right privilege to run the event? I found this on StackOverflow (see 7th answer) so just thought we should cover that off first. Thanks! https://stackoverflow.com/questions/16767923/mysql-event-not-working

3

Yes, root has the right privilege to run the event.After more testing, I have identified that the CREATE EVENT query (mentioned above) works correctly with definer.And the definer is replaced by the second ALTER query:

ALTER EVENT ps_helper.event1 ENABLE

Possibly this is not a bug, and I must always explicitly add definer to ALTER queries as well like this:

ALTER DEFINER = 'root'@'localhost' EVENT ps_helper.event1 ENABLE



4

Thanks for the update, that’s likely to help others in the future too.