Not the answer you need?
Register and ask your own question!

Bug: Explicit definer is ignored when I create an event

andrew_soloshkoandrew_soloshko Current User Role Novice
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
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?




Answers

  • lorraine.pocklingtonlorraine.pocklington Percona Community Manager Legacy User Role Patron
    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
  • andrew_soloshkoandrew_soloshko Current User Role Novice
    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


  • lorraine.pocklingtonlorraine.pocklington Percona Community Manager Legacy User Role Patron
    Thanks for the update, that's likely to help others in the future too.
Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.