1CREATE DATABASE IF NOT EXISTS events_test; 2use events_test; 3CREATE EVENT one_event ON SCHEDULE EVERY 10 SECOND DO SELECT 123; 4Warnings: 5Warning 1105 Event scheduler is switched off, use SET GLOBAL event_scheduler=ON to enable it. 6SHOW EVENTS; 7Db Name Definer Time zone Type Execute at Interval value Interval field Starts Ends Status Originator character_set_client collation_connection Database Collation 8events_test one_event root@localhost SYSTEM RECURRING NULL 10 # # NULL ENABLED 1 latin1 latin1_swedish_ci latin1_swedish_ci 9SELECT EVENT_CATALOG, EVENT_SCHEMA, EVENT_NAME, DEFINER, EVENT_BODY, EVENT_DEFINITION, EVENT_TYPE, EXECUTE_AT, INTERVAL_VALUE, INTERVAL_FIELD, STATUS,ON_COMPLETION, EVENT_COMMENT FROM INFORMATION_SCHEMA.EVENTS ORDER BY EVENT_SCHEMA, EVENT_NAME; 10EVENT_CATALOG EVENT_SCHEMA EVENT_NAME DEFINER EVENT_BODY EVENT_DEFINITION EVENT_TYPE EXECUTE_AT INTERVAL_VALUE INTERVAL_FIELD STATUS ON_COMPLETION EVENT_COMMENT 11def events_test one_event root@localhost SQL SELECT 123 RECURRING NULL 10 SECOND ENABLED NOT PRESERVE 12CREATE DATABASE events_test2; 13CREATE USER ev_test@localhost; 14GRANT ALL ON events_test.* to ev_test@localhost; 15GRANT ALL ON events_test2.* to ev_test@localhost; 16REVOKE EVENT ON events_test2.* FROM ev_test@localhost; 17connect ev_con1,localhost,ev_test,,events_test2; 18select "NEW CONNECTION"; 19NEW CONNECTION 20NEW CONNECTION 21SELECT USER(), DATABASE(); 22USER() DATABASE() 23ev_test@localhost events_test2 24SHOW GRANTS; 25Grants for ev_test@localhost 26GRANT USAGE ON *.* TO `ev_test`@`localhost` 27GRANT ALL PRIVILEGES ON `events_test`.* TO `ev_test`@`localhost` 28GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, TRIGGER, DELETE HISTORY ON `events_test2`.* TO `ev_test`@`localhost` 29"Here comes an error:"; 30SHOW EVENTS; 31ERROR 42000: Access denied for user 'ev_test'@'localhost' to database 'events_test2' 32USE events_test; 33"We should see one event"; 34SHOW EVENTS; 35Db Name Definer Time zone Type Execute at Interval value Interval field Starts Ends Status Originator character_set_client collation_connection Database Collation 36events_test one_event root@localhost SYSTEM RECURRING NULL 10 # # NULL ENABLED 1 latin1 latin1_swedish_ci latin1_swedish_ci 37SELECT CONCAT("Let's create some new events from the name of ", USER()); 38CONCAT("Let's create some new events from the name of ", USER()) 39Let's create some new events from the name of ev_test@localhost 40CREATE EVENT one_event ON SCHEDULE EVERY 20 SECOND DO SELECT 123; 41ERROR HY000: Event 'one_event' already exists 42CREATE EVENT two_event ON SCHEDULE EVERY 20 SECOND ON COMPLETION NOT PRESERVE COMMENT "two event" DO SELECT 123; 43Warnings: 44Warning 1105 Event scheduler is switched off, use SET GLOBAL event_scheduler=ON to enable it. 45CREATE EVENT three_event ON SCHEDULE EVERY 20 SECOND ON COMPLETION PRESERVE COMMENT "three event" DO SELECT 123; 46Warnings: 47Warning 1105 Event scheduler is switched off, use SET GLOBAL event_scheduler=ON to enable it. 48"Now we should see 3 events:"; 49SHOW EVENTS; 50Db Name Definer Time zone Type Execute at Interval value Interval field Starts Ends Status Originator character_set_client collation_connection Database Collation 51events_test one_event root@localhost SYSTEM RECURRING NULL 10 # # NULL ENABLED 1 latin1 latin1_swedish_ci latin1_swedish_ci 52events_test three_event ev_test@localhost SYSTEM RECURRING NULL 20 # # NULL ENABLED 1 latin1 latin1_swedish_ci latin1_swedish_ci 53events_test two_event ev_test@localhost SYSTEM RECURRING NULL 20 # # NULL ENABLED 1 latin1 latin1_swedish_ci latin1_swedish_ci 54"This should show us only 2 events:"; 55SHOW EVENTS LIKE 't%event'; 56Db Name Definer Time zone Type Execute at Interval value Interval field Starts Ends Status Originator character_set_client collation_connection Database Collation 57events_test three_event ev_test@localhost SYSTEM RECURRING NULL 20 # # NULL ENABLED 1 latin1 latin1_swedish_ci latin1_swedish_ci 58events_test two_event ev_test@localhost SYSTEM RECURRING NULL 20 # # NULL ENABLED 1 latin1 latin1_swedish_ci latin1_swedish_ci 59"This should show us no events:"; 60SHOW EVENTS FROM test LIKE '%'; 61Db Name Definer Time zone Type Execute at Interval value Interval field Starts Ends Status Originator character_set_client collation_connection Database Collation 62connection default; 63GRANT EVENT ON events_test2.* TO ev_test@localhost; 64connection ev_con1; 65USE events_test2; 66CREATE EVENT four_event ON SCHEDULE EVERY 20 SECOND DO SELECT 42; 67Warnings: 68Warning 1105 Event scheduler is switched off, use SET GLOBAL event_scheduler=ON to enable it. 69connection default; 70USE events_test; 71"We should see 4 events : one_event, two_event, three_event & four_event" 72SELECT EVENT_CATALOG, EVENT_SCHEMA, EVENT_NAME, DEFINER, EVENT_BODY, EVENT_DEFINITION, EVENT_TYPE, EXECUTE_AT, INTERVAL_VALUE, INTERVAL_FIELD, STATUS,ON_COMPLETION, EVENT_COMMENT FROM INFORMATION_SCHEMA.EVENTS ORDER BY EVENT_SCHEMA, EVENT_NAME; 73EVENT_CATALOG EVENT_SCHEMA EVENT_NAME DEFINER EVENT_BODY EVENT_DEFINITION EVENT_TYPE EXECUTE_AT INTERVAL_VALUE INTERVAL_FIELD STATUS ON_COMPLETION EVENT_COMMENT 74def events_test one_event root@localhost SQL SELECT 123 RECURRING NULL 10 SECOND ENABLED NOT PRESERVE 75def events_test three_event ev_test@localhost SQL SELECT 123 RECURRING NULL 20 SECOND ENABLED PRESERVE three event 76def events_test two_event ev_test@localhost SQL SELECT 123 RECURRING NULL 20 SECOND ENABLED NOT PRESERVE two event 77def events_test2 four_event ev_test@localhost SQL SELECT 42 RECURRING NULL 20 SECOND ENABLED NOT PRESERVE 78DROP DATABASE events_test2; 79"We should see 3 events : one_event, two_event, three_event" 80SELECT EVENT_CATALOG, EVENT_SCHEMA, EVENT_NAME, DEFINER, EVENT_BODY, EVENT_DEFINITION, EVENT_TYPE, EXECUTE_AT, INTERVAL_VALUE, INTERVAL_FIELD, STATUS,ON_COMPLETION, EVENT_COMMENT FROM INFORMATION_SCHEMA.EVENTS ORDER BY EVENT_SCHEMA, EVENT_NAME; 81EVENT_CATALOG EVENT_SCHEMA EVENT_NAME DEFINER EVENT_BODY EVENT_DEFINITION EVENT_TYPE EXECUTE_AT INTERVAL_VALUE INTERVAL_FIELD STATUS ON_COMPLETION EVENT_COMMENT 82def events_test one_event root@localhost SQL SELECT 123 RECURRING NULL 10 SECOND ENABLED NOT PRESERVE 83def events_test three_event ev_test@localhost SQL SELECT 123 RECURRING NULL 20 SECOND ENABLED PRESERVE three event 84def events_test two_event ev_test@localhost SQL SELECT 123 RECURRING NULL 20 SECOND ENABLED NOT PRESERVE two event 85connection default; 86CREATE DATABASE events_test2; 87USE events_test2; 88CREATE EVENT five_event ON SCHEDULE EVERY 20 SECOND DO SELECT 42; 89Warnings: 90Warning 1105 Event scheduler is switched off, use SET GLOBAL event_scheduler=ON to enable it. 91connection ev_con1; 92"Should see 4 events - one, two, three & five" 93SELECT EVENT_CATALOG, EVENT_SCHEMA, EVENT_NAME, DEFINER, EVENT_BODY, EVENT_DEFINITION, EVENT_TYPE, EXECUTE_AT, INTERVAL_VALUE, INTERVAL_FIELD, STATUS,ON_COMPLETION, EVENT_COMMENT FROM INFORMATION_SCHEMA.EVENTS ORDER BY EVENT_SCHEMA, EVENT_NAME; 94EVENT_CATALOG EVENT_SCHEMA EVENT_NAME DEFINER EVENT_BODY EVENT_DEFINITION EVENT_TYPE EXECUTE_AT INTERVAL_VALUE INTERVAL_FIELD STATUS ON_COMPLETION EVENT_COMMENT 95def events_test one_event root@localhost SQL SELECT 123 RECURRING NULL 10 SECOND ENABLED NOT PRESERVE 96def events_test three_event ev_test@localhost SQL SELECT 123 RECURRING NULL 20 SECOND ENABLED PRESERVE three event 97def events_test two_event ev_test@localhost SQL SELECT 123 RECURRING NULL 20 SECOND ENABLED NOT PRESERVE two event 98def events_test2 five_event root@localhost SQL SELECT 42 RECURRING NULL 20 SECOND ENABLED NOT PRESERVE 99connection default; 100REVOKE EVENT ON events_test2.* FROM ev_test@localhost; 101connection ev_con1; 102USE test; 103"Should see 3 events - one, two & three" 104SELECT EVENT_CATALOG, EVENT_SCHEMA, EVENT_NAME, DEFINER, EVENT_BODY, EVENT_DEFINITION, EVENT_TYPE, EXECUTE_AT, INTERVAL_VALUE, INTERVAL_FIELD, STATUS,ON_COMPLETION, EVENT_COMMENT FROM INFORMATION_SCHEMA.EVENTS ORDER BY EVENT_SCHEMA, EVENT_NAME; 105EVENT_CATALOG EVENT_SCHEMA EVENT_NAME DEFINER EVENT_BODY EVENT_DEFINITION EVENT_TYPE EXECUTE_AT INTERVAL_VALUE INTERVAL_FIELD STATUS ON_COMPLETION EVENT_COMMENT 106def events_test one_event root@localhost SQL SELECT 123 RECURRING NULL 10 SECOND ENABLED NOT PRESERVE 107def events_test three_event ev_test@localhost SQL SELECT 123 RECURRING NULL 20 SECOND ENABLED PRESERVE three event 108def events_test two_event ev_test@localhost SQL SELECT 123 RECURRING NULL 20 SECOND ENABLED NOT PRESERVE two event 109"Let's test ALTER EVENT which changes the definer" 110USE events_test; 111ALTER EVENT one_event ON SCHEDULE EVERY 10 SECOND; 112"The definer should be ev_test@localhost" 113SELECT EVENT_CATALOG, EVENT_SCHEMA, EVENT_NAME, DEFINER, EVENT_BODY, EVENT_DEFINITION, EVENT_TYPE, EXECUTE_AT, INTERVAL_VALUE, INTERVAL_FIELD, STATUS,ON_COMPLETION, EVENT_COMMENT FROM INFORMATION_SCHEMA.EVENTS WHERE EVENT_NAME='one_event'; 114EVENT_CATALOG EVENT_SCHEMA EVENT_NAME DEFINER EVENT_BODY EVENT_DEFINITION EVENT_TYPE EXECUTE_AT INTERVAL_VALUE INTERVAL_FIELD STATUS ON_COMPLETION EVENT_COMMENT 115def events_test one_event ev_test@localhost SQL SELECT 123 RECURRING NULL 10 SECOND ENABLED NOT PRESERVE 116connection default; 117USE events_test; 118ALTER EVENT one_event COMMENT "comment"; 119connection ev_con1; 120"The definer should be root@localhost" 121SELECT EVENT_CATALOG, EVENT_SCHEMA, EVENT_NAME, DEFINER, EVENT_BODY, EVENT_DEFINITION, EVENT_TYPE, EXECUTE_AT, INTERVAL_VALUE, INTERVAL_FIELD, STATUS,ON_COMPLETION, EVENT_COMMENT FROM INFORMATION_SCHEMA.EVENTS WHERE EVENT_NAME='one_event'; 122EVENT_CATALOG EVENT_SCHEMA EVENT_NAME DEFINER EVENT_BODY EVENT_DEFINITION EVENT_TYPE EXECUTE_AT INTERVAL_VALUE INTERVAL_FIELD STATUS ON_COMPLETION EVENT_COMMENT 123def events_test one_event root@localhost SQL SELECT 123 RECURRING NULL 10 SECOND ENABLED NOT PRESERVE comment 124ALTER EVENT one_event DO SELECT 12; 125"The definer should be ev_test@localhost" 126SELECT EVENT_CATALOG, EVENT_SCHEMA, EVENT_NAME, DEFINER, EVENT_BODY, EVENT_DEFINITION, EVENT_TYPE, EXECUTE_AT, INTERVAL_VALUE, INTERVAL_FIELD, STATUS,ON_COMPLETION, EVENT_COMMENT FROM INFORMATION_SCHEMA.EVENTS WHERE EVENT_NAME='one_event'; 127EVENT_CATALOG EVENT_SCHEMA EVENT_NAME DEFINER EVENT_BODY EVENT_DEFINITION EVENT_TYPE EXECUTE_AT INTERVAL_VALUE INTERVAL_FIELD STATUS ON_COMPLETION EVENT_COMMENT 128def events_test one_event ev_test@localhost SQL SELECT 12 RECURRING NULL 10 SECOND ENABLED NOT PRESERVE comment 129connection default; 130"make the definer again root@localhost" 131ALTER EVENT one_event COMMENT "new comment"; 132connection ev_con1; 133"test DROP by another user" 134DROP EVENT one_event; 135connection default; 136"One event should not be there" 137SELECT EVENT_CATALOG, EVENT_SCHEMA, EVENT_NAME, DEFINER, EVENT_BODY, EVENT_DEFINITION, EVENT_TYPE, EXECUTE_AT, INTERVAL_VALUE, INTERVAL_FIELD, STATUS,ON_COMPLETION, EVENT_COMMENT FROM INFORMATION_SCHEMA.EVENTS ORDER BY EVENT_SCHEMA, EVENT_NAME; 138EVENT_CATALOG EVENT_SCHEMA EVENT_NAME DEFINER EVENT_BODY EVENT_DEFINITION EVENT_TYPE EXECUTE_AT INTERVAL_VALUE INTERVAL_FIELD STATUS ON_COMPLETION EVENT_COMMENT 139def events_test three_event ev_test@localhost SQL SELECT 123 RECURRING NULL 20 SECOND ENABLED PRESERVE three event 140def events_test two_event ev_test@localhost SQL SELECT 123 RECURRING NULL 20 SECOND ENABLED NOT PRESERVE two event 141def events_test2 five_event root@localhost SQL SELECT 42 RECURRING NULL 20 SECOND ENABLED NOT PRESERVE 142connection ev_con1; 143disconnect ev_con1; 144connection default; 145DROP USER ev_test@localhost; 146DROP DATABASE events_test2; 147DROP DATABASE events_test; 148