diff options
Diffstat (limited to 'mysql-test/suite/events')
35 files changed, 6476 insertions, 0 deletions
diff --git a/mysql-test/suite/events/disabled.def b/mysql-test/suite/events/disabled.def new file mode 100644 index 00000000..d895d7d2 --- /dev/null +++ b/mysql-test/suite/events/disabled.def @@ -0,0 +1,12 @@ +############################################################################## +# +# List the test cases that are to be disabled temporarily. +# +# Separate the test case name and the comment with ':'. +# +# <testcasename> : BUG#<xxxx> <date disabled> <disabler> <comment> +# +# Do not use any TAB characters for whitespace. +# +############################################################################## +events_time_zone : Test is not predictable as it depends on precise timing. diff --git a/mysql-test/suite/events/events_1.result b/mysql-test/suite/events/events_1.result new file mode 100644 index 00000000..2db478ad --- /dev/null +++ b/mysql-test/suite/events/events_1.result @@ -0,0 +1,568 @@ +set sql_mode=""; +call mtr.add_suppression("Column count of mysql.event is wrong. Expected .*, found .*\. The table is probably corrupted"); +call mtr.add_suppression("Incorrect definition of table mysql.event:.*"); +call mtr.add_suppression("Event Scheduler: .* DROP command denied to user"); +create database events_test; +use events_test; +CREATE USER pauline@localhost; +CREATE DATABASE db_x; +GRANT EVENT ON db_x.* TO pauline@localhost; +USE db_x; +CREATE TABLE x_table(a int); +connect priv_conn,localhost,pauline,,db_x; +CREATE EVENT e_x1 ON SCHEDULE EVERY 1 SECOND DO DROP DATABASE db_x; +Warnings: +Warning 1105 Event scheduler is switched off, use SET GLOBAL event_scheduler=ON to enable it. +CREATE EVENT e_x2 ON SCHEDULE EVERY 1 SECOND DO DROP TABLE x_table; +Warnings: +Warning 1105 Event scheduler is switched off, use SET GLOBAL event_scheduler=ON to enable it. +connection default; +SHOW DATABASES LIKE 'db_x'; +Database (db_x) +db_x +SET GLOBAL event_scheduler=1; +SHOW DATABASES LIKE 'db_x'; +Database (db_x) +db_x +SHOW TABLES FROM db_x; +Tables_in_db_x +x_table +SET GLOBAL event_scheduler=off; +connection priv_conn; +DROP EVENT e_x1; +DROP EVENT e_x2; +disconnect priv_conn; +connection default; +DROP DATABASE db_x; +DROP USER pauline@localhost; +USE events_test; +SET GLOBAL event_scheduler=off; +drop event if exists event1; +Warnings: +Note 1305 Event event1 does not exist +create event event1 on schedule every 15 minute starts now() ends date_add(now(), interval 5 hour) DO begin end; +Warnings: +Warning 1105 Event scheduler is switched off, use SET GLOBAL event_scheduler=ON to enable it. +alter event event1 rename to event2 enable; +alter event event2 disable; +alter event event2 enable; +alter event event2 on completion not preserve; +alter event event2 on schedule every 1 year on completion preserve rename to event3 comment "new comment" do begin select 1; end__ +alter event event3 rename to event2; +drop event event2; +create event event2 on schedule every 2 second starts now() ends date_add(now(), interval 5 hour) comment "some" DO begin end; +Warnings: +Warning 1105 Event scheduler is switched off, use SET GLOBAL event_scheduler=ON to enable it. +drop event event2; +CREATE EVENT event_starts_test ON SCHEDULE EVERY 10 SECOND COMMENT "" DO SELECT 1; +Warnings: +Warning 1105 Event scheduler is switched off, use SET GLOBAL event_scheduler=ON to enable it. +SELECT interval_field, interval_value, body FROM mysql.event WHERE db='events_test' AND name='event_starts_test'; +interval_field interval_value body +SECOND 10 SELECT 1 +SELECT execute_at IS NULL, starts IS NULL, ends IS NULL, comment FROM mysql.event WHERE db='events_test' AND name='event_starts_test'; +execute_at IS NULL starts IS NULL ends IS NULL comment +1 0 1 +ALTER EVENT event_starts_test ON SCHEDULE AT '1970-01-02 00:00:00' ON COMPLETION PRESERVE DISABLE; +SELECT execute_at IS NULL, starts IS NULL, ends IS NULL, comment FROM mysql.event WHERE db='events_test' AND name='event_starts_test'; +execute_at IS NULL starts IS NULL ends IS NULL comment +0 1 1 +ALTER EVENT event_starts_test COMMENT "non-empty comment"; +SELECT execute_at IS NULL, starts IS NULL, ends IS NULL, comment FROM mysql.event WHERE db='events_test' AND name='event_starts_test'; +execute_at IS NULL starts IS NULL ends IS NULL comment +0 1 1 non-empty comment +ALTER EVENT event_starts_test COMMENT ""; +SELECT execute_at IS NULL, starts IS NULL, ends IS NULL, comment FROM mysql.event WHERE db='events_test' AND name='event_starts_test'; +execute_at IS NULL starts IS NULL ends IS NULL comment +0 1 1 +DROP EVENT event_starts_test; +CREATE EVENT event_starts_test ON SCHEDULE EVERY 20 SECOND STARTS '1970-01-02 00:00:00' ENDS '1970-01-03 00:00:00' ON COMPLETION PRESERVE DISABLE DO SELECT 2; +Warnings: +Warning 1105 Event scheduler is switched off, use SET GLOBAL event_scheduler=ON to enable it. +SELECT execute_at IS NULL, starts IS NULL, ends IS NULL, comment FROM mysql.event WHERE db='events_test' AND name='event_starts_test'; +execute_at IS NULL starts IS NULL ends IS NULL comment +1 0 0 +ALTER EVENT event_starts_test COMMENT "non-empty comment"; +SELECT execute_at IS NULL, starts IS NULL, ends IS NULL, comment FROM mysql.event WHERE db='events_test' AND name='event_starts_test'; +execute_at IS NULL starts IS NULL ends IS NULL comment +1 0 0 non-empty comment +ALTER EVENT event_starts_test COMMENT ""; +SELECT execute_at IS NULL, starts IS NULL, ends IS NULL, comment FROM mysql.event WHERE db='events_test' AND name='event_starts_test'; +execute_at IS NULL starts IS NULL ends IS NULL comment +1 0 0 +DROP EVENT event_starts_test; +create table test_nested(a int); +create event e_43 on schedule every 1 second do set @a = 5; +Warnings: +Warning 1105 Event scheduler is switched off, use SET GLOBAL event_scheduler=ON to enable it. +alter event e_43 do alter event e_43 do set @a = 4; +ERROR HY000: Recursion of EVENT DDL statements is forbidden when body is present +alter event e_43 do +begin +alter event e_43 on schedule every 5 minute; +insert into test_nested values(1); +end| +set global event_scheduler = on; +select db, name, body, status, interval_field, interval_value from mysql.event; +db name body status interval_field interval_value +events_test e_43 begin +alter event e_43 on schedule every 5 minute; +insert into test_nested values(1); +end ENABLED MINUTE 5 +drop event e_43; +drop table test_nested; +"Let's check whether we can use non-qualified names" +create table non_qualif(a int); +create event non_qualif_ev on schedule every 10 minute do insert into non_qualif values (800219); +select * from non_qualif; +a +800219 +drop event non_qualif_ev; +drop table non_qualif; +alter event non_existant rename to non_existant_too; +ERROR HY000: Unknown event 'non_existant' +set global event_scheduler = off; +create event existant on schedule at now() + interval 1 year do select 12; +Warnings: +Warning 1105 Event scheduler is switched off, use SET GLOBAL event_scheduler=ON to enable it. +alter event non_existant rename to existant; +ERROR HY000: Event 'existant' already exists +alter event existant rename to events_test.existant; +ERROR HY000: Same old and new event name +drop event existant; +create table t_event3 (a int, b float); +drop event if exists event3; +Warnings: +Note 1305 Event event3 does not exist +create event event3 on schedule every 50 + 10 minute starts date_add(curdate(), interval 5 minute) ends date_add(curdate(), interval 5 day) comment "portokala_comment" DO insert into t_event3 values (unix_timestamp(), rand()); +Warnings: +Warning 1105 Event scheduler is switched off, use SET GLOBAL event_scheduler=ON to enable it. +select count(*) from t_event3; +count(*) +0 +drop event event3; +drop table t_event3; +set names utf8; +CREATE EVENT root6 ON SCHEDULE EVERY '10:20' MINUTE_SECOND ON COMPLETION PRESERVE ENABLE COMMENT 'some comment' DO select 1; +Warnings: +Warning 1105 Event scheduler is switched off, use SET GLOBAL event_scheduler=ON to enable it. +SHOW CREATE EVENT root6; +Event sql_mode time_zone Create Event character_set_client collation_connection Database Collation +root6 SYSTEM CREATE DEFINER=`root`@`localhost` EVENT `root6` ON SCHEDULE EVERY '10:20' MINUTE_SECOND STARTS '#' ON COMPLETION PRESERVE ENABLE COMMENT 'some comment' DO select 1 utf8mb3 utf8mb3_general_ci latin1_swedish_ci +create event root7 on schedule every 2 year do select 1; +Warnings: +Warning 1105 Event scheduler is switched off, use SET GLOBAL event_scheduler=ON to enable it. +SHOW CREATE EVENT root7; +Event sql_mode time_zone Create Event character_set_client collation_connection Database Collation +root7 SYSTEM CREATE DEFINER=`root`@`localhost` EVENT `root7` ON SCHEDULE EVERY 2 YEAR STARTS '#' ON COMPLETION NOT PRESERVE ENABLE DO select 1 utf8mb3 utf8mb3_general_ci latin1_swedish_ci +create event root8 on schedule every '2:5' year_month do select 1; +Warnings: +Warning 1105 Event scheduler is switched off, use SET GLOBAL event_scheduler=ON to enable it. +SHOW CREATE EVENT root8; +Event sql_mode time_zone Create Event character_set_client collation_connection Database Collation +root8 SYSTEM CREATE DEFINER=`root`@`localhost` EVENT `root8` ON SCHEDULE EVERY '2-5' YEAR_MONTH STARTS '#' ON COMPLETION NOT PRESERVE ENABLE DO select 1 utf8mb3 utf8mb3_general_ci latin1_swedish_ci +create event root8_1 on schedule every '2:15' year_month do select 1; +Warnings: +Warning 1105 Event scheduler is switched off, use SET GLOBAL event_scheduler=ON to enable it. +SHOW CREATE EVENT root8_1; +Event sql_mode time_zone Create Event character_set_client collation_connection Database Collation +root8_1 SYSTEM CREATE DEFINER=`root`@`localhost` EVENT `root8_1` ON SCHEDULE EVERY '3-3' YEAR_MONTH STARTS '#' ON COMPLETION NOT PRESERVE ENABLE DO select 1 utf8mb3 utf8mb3_general_ci latin1_swedish_ci +create event root9 on schedule every 2 week ON COMPLETION PRESERVE DISABLE COMMENT 'коментар на кирилица' do select 1; +Warnings: +Warning 1105 Event scheduler is switched off, use SET GLOBAL event_scheduler=ON to enable it. +SHOW CREATE EVENT root9; +Event sql_mode time_zone Create Event character_set_client collation_connection Database Collation +root9 SYSTEM CREATE DEFINER=`root`@`localhost` EVENT `root9` ON SCHEDULE EVERY 2 WEEK STARTS '#' ON COMPLETION PRESERVE DISABLE COMMENT 'коментар на кирилица' DO select 1 utf8mb3 utf8mb3_general_ci latin1_swedish_ci +create event root10 on schedule every '20:5' day_hour do select 1; +Warnings: +Warning 1105 Event scheduler is switched off, use SET GLOBAL event_scheduler=ON to enable it. +SHOW CREATE EVENT root10; +Event sql_mode time_zone Create Event character_set_client collation_connection Database Collation +root10 SYSTEM CREATE DEFINER=`root`@`localhost` EVENT `root10` ON SCHEDULE EVERY '20 5' DAY_HOUR STARTS '#' ON COMPLETION NOT PRESERVE ENABLE DO select 1 utf8mb3 utf8mb3_general_ci latin1_swedish_ci +create event root11 on schedule every '20:25' day_hour do select 1; +Warnings: +Warning 1105 Event scheduler is switched off, use SET GLOBAL event_scheduler=ON to enable it. +SHOW CREATE EVENT root11; +Event sql_mode time_zone Create Event character_set_client collation_connection Database Collation +root11 SYSTEM CREATE DEFINER=`root`@`localhost` EVENT `root11` ON SCHEDULE EVERY '21 1' DAY_HOUR STARTS '#' ON COMPLETION NOT PRESERVE ENABLE DO select 1 utf8mb3 utf8mb3_general_ci latin1_swedish_ci +create event root12 on schedule every '20:25' hour_minute do select 1; +Warnings: +Warning 1105 Event scheduler is switched off, use SET GLOBAL event_scheduler=ON to enable it. +SHOW CREATE EVENT root12; +Event sql_mode time_zone Create Event character_set_client collation_connection Database Collation +root12 SYSTEM CREATE DEFINER=`root`@`localhost` EVENT `root12` ON SCHEDULE EVERY '20:25' HOUR_MINUTE STARTS '#' ON COMPLETION NOT PRESERVE ENABLE DO select 1 utf8mb3 utf8mb3_general_ci latin1_swedish_ci +create event root13 on schedule every '25:25' hour_minute do select 1; +Warnings: +Warning 1105 Event scheduler is switched off, use SET GLOBAL event_scheduler=ON to enable it. +SHOW CREATE EVENT root13; +Event sql_mode time_zone Create Event character_set_client collation_connection Database Collation +root13 SYSTEM CREATE DEFINER=`root`@`localhost` EVENT `root13` ON SCHEDULE EVERY '25:25' HOUR_MINUTE STARTS '#' ON COMPLETION NOT PRESERVE ENABLE DO select 1 utf8mb3 utf8mb3_general_ci latin1_swedish_ci +create event root13_1 on schedule every '11:65' hour_minute do select 1; +Warnings: +Warning 1105 Event scheduler is switched off, use SET GLOBAL event_scheduler=ON to enable it. +SHOW CREATE EVENT root13_1; +Event sql_mode time_zone Create Event character_set_client collation_connection Database Collation +root13_1 SYSTEM CREATE DEFINER=`root`@`localhost` EVENT `root13_1` ON SCHEDULE EVERY '12:5' HOUR_MINUTE STARTS '#' ON COMPLETION NOT PRESERVE ENABLE DO select 1 utf8mb3 utf8mb3_general_ci latin1_swedish_ci +create event root14 on schedule every '35:35' minute_second do select 1; +Warnings: +Warning 1105 Event scheduler is switched off, use SET GLOBAL event_scheduler=ON to enable it. +SHOW CREATE EVENT root14; +Event sql_mode time_zone Create Event character_set_client collation_connection Database Collation +root14 SYSTEM CREATE DEFINER=`root`@`localhost` EVENT `root14` ON SCHEDULE EVERY '35:35' MINUTE_SECOND STARTS '#' ON COMPLETION NOT PRESERVE ENABLE DO select 1 utf8mb3 utf8mb3_general_ci latin1_swedish_ci +create event root15 on schedule every '35:66' minute_second do select 1; +Warnings: +Warning 1105 Event scheduler is switched off, use SET GLOBAL event_scheduler=ON to enable it. +SHOW CREATE EVENT root15; +Event sql_mode time_zone Create Event character_set_client collation_connection Database Collation +root15 SYSTEM CREATE DEFINER=`root`@`localhost` EVENT `root15` ON SCHEDULE EVERY '36:6' MINUTE_SECOND STARTS '#' ON COMPLETION NOT PRESERVE ENABLE DO select 1 utf8mb3 utf8mb3_general_ci latin1_swedish_ci +create event root16 on schedule every '35:56' day_minute do select 1; +Warnings: +Warning 1105 Event scheduler is switched off, use SET GLOBAL event_scheduler=ON to enable it. +SHOW CREATE EVENT root16; +Event sql_mode time_zone Create Event character_set_client collation_connection Database Collation +root16 SYSTEM CREATE DEFINER=`root`@`localhost` EVENT `root16` ON SCHEDULE EVERY '1 11:56' DAY_MINUTE STARTS '#' ON COMPLETION NOT PRESERVE ENABLE DO select 1 utf8mb3 utf8mb3_general_ci latin1_swedish_ci +create event root17 on schedule every '35:12:45' day_minute do select 1; +Warnings: +Warning 1105 Event scheduler is switched off, use SET GLOBAL event_scheduler=ON to enable it. +SHOW CREATE EVENT root17; +Event sql_mode time_zone Create Event character_set_client collation_connection Database Collation +root17 SYSTEM CREATE DEFINER=`root`@`localhost` EVENT `root17` ON SCHEDULE EVERY '35 12:45' DAY_MINUTE STARTS '#' ON COMPLETION NOT PRESERVE ENABLE DO select 1 utf8mb3 utf8mb3_general_ci latin1_swedish_ci +create event root17_1 on schedule every '35:25:65' day_minute do select 1; +Warnings: +Warning 1105 Event scheduler is switched off, use SET GLOBAL event_scheduler=ON to enable it. +SHOW CREATE EVENT root17_1; +Event sql_mode time_zone Create Event character_set_client collation_connection Database Collation +root17_1 SYSTEM CREATE DEFINER=`root`@`localhost` EVENT `root17_1` ON SCHEDULE EVERY '36 2:5' DAY_MINUTE STARTS '#' ON COMPLETION NOT PRESERVE ENABLE DO select 1 utf8mb3 utf8mb3_general_ci latin1_swedish_ci +create event root18 on schedule every '35:12:45' hour_second do select 1; +Warnings: +Warning 1105 Event scheduler is switched off, use SET GLOBAL event_scheduler=ON to enable it. +SHOW CREATE EVENT root18; +Event sql_mode time_zone Create Event character_set_client collation_connection Database Collation +root18 SYSTEM CREATE DEFINER=`root`@`localhost` EVENT `root18` ON SCHEDULE EVERY '35:12:45' HOUR_SECOND STARTS '#' ON COMPLETION NOT PRESERVE ENABLE DO select 1 utf8mb3 utf8mb3_general_ci latin1_swedish_ci +create event root19 on schedule every '15:59:85' hour_second do select 1; +Warnings: +Warning 1105 Event scheduler is switched off, use SET GLOBAL event_scheduler=ON to enable it. +SHOW CREATE EVENT root19; +Event sql_mode time_zone Create Event character_set_client collation_connection Database Collation +root19 SYSTEM CREATE DEFINER=`root`@`localhost` EVENT `root19` ON SCHEDULE EVERY '16:0:25' HOUR_SECOND STARTS '#' ON COMPLETION NOT PRESERVE ENABLE DO select 1 utf8mb3 utf8mb3_general_ci latin1_swedish_ci +create event root20 on schedule every '50:20:12:45' day_second do select 1; +Warnings: +Warning 1105 Event scheduler is switched off, use SET GLOBAL event_scheduler=ON to enable it. +SHOW CREATE EVENT root20; +Event sql_mode time_zone Create Event character_set_client collation_connection Database Collation +root20 SYSTEM CREATE DEFINER=`root`@`localhost` EVENT `root20` ON SCHEDULE EVERY '50 20:12:45' DAY_SECOND STARTS '#' ON COMPLETION NOT PRESERVE ENABLE DO select 1 utf8mb3 utf8mb3_general_ci latin1_swedish_ci +set names cp1251; +create event 21 on schedule every '50:23:59:95' day_second COMMENT ' 1251 ' do select 1; +Warnings: +Warning 1105 Event scheduler is switched off, use SET GLOBAL event_scheduler=ON to enable it. +SHOW CREATE EVENT 21; +Event sql_mode time_zone Create Event character_set_client collation_connection Database Collation +21 SYSTEM CREATE DEFINER=`root`@`localhost` EVENT `руут21` ON SCHEDULE EVERY '51 0:0:35' DAY_SECOND STARTS '#' ON COMPLETION NOT PRESERVE ENABLE COMMENT 'това е 1251 коментар' DO select 1 cp1251 cp1251_general_ci latin1_swedish_ci +insert into mysql.event ( +db, +name, +body, +definer, +interval_value, +interval_field, +originator, +character_set_client, +collation_connection, +db_collation, +body_utf8) +values ( +database(), +"root22", +"select 1", +user(), +100, +"SECOND_MICROSECOND", +1, +'utf8', +'utf8_general_ci', +'utf8_general_ci', +'select 1'); +show create event root22; +ERROR 42000: This version of MariaDB doesn't yet support 'MICROSECOND' +SHOW EVENTS; +ERROR 42000: This version of MariaDB doesn't yet support 'MICROSECOND' +drop event root22; +create event root23 on schedule every -100 year do select 1; +ERROR HY000: INTERVAL is either not positive or too big +create event root23 on schedule every 222222222222222222222 year do select 1; +ERROR HY000: INTERVAL is either not positive or too big +drop event root6; +drop event root7; +drop event root8; +drop event root8_1; +drop event root9; +drop event root10; +drop event root11; +drop event root12; +drop event root13; +drop event root13_1; +drop event root14; +drop event root15; +drop event root16; +drop event root17; +drop event root17_1; +drop event root18; +drop event root19; +drop event root20; +drop event 21; +set names latin1; +Create a test event. Only event metadata is relevant, +the actual schedule and body are not. +CREATE EVENT intact_check ON SCHEDULE EVERY 10 HOUR DO SELECT "nothing"; +Warnings: +Warning 1105 Event scheduler is switched off, use SET GLOBAL event_scheduler=ON to enable it. +SHOW EVENTS; +Db Name Definer Time zone Type Execute at Interval value Interval field Starts Ends Status Originator character_set_client collation_connection Database Collation +events_test intact_check root@localhost SYSTEM RECURRING NULL 10 # # NULL ENABLED 1 latin1 latin1_swedish_ci latin1_swedish_ci +Try to alter mysql.event: the server should fail to load +event information after mysql.event was tampered with. + +First, let's add a column to the end and check the error is emitted. + +ALTER TABLE mysql.event ADD dummy INT; +SHOW EVENTS; +ERROR HY000: Failed to open mysql.event +SELECT event_name FROM INFORMATION_SCHEMA.events; +ERROR HY000: Failed to open mysql.event +SHOW CREATE EVENT intact_check; +ERROR HY000: Failed to open mysql.event +DROP EVENT no_such_event; +ERROR HY000: Failed to open mysql.event +CREATE EVENT intact_check_1 ON SCHEDULE EVERY 5 HOUR DO SELECT 5; +ERROR HY000: Failed to open mysql.event +ALTER EVENT intact_check_1 ON SCHEDULE EVERY 8 HOUR DO SELECT 8; +ERROR HY000: Failed to open mysql.event +ALTER EVENT intact_check_1 RENAME TO intact_check_2; +ERROR HY000: Failed to open mysql.event +DROP EVENT intact_check_1; +ERROR HY000: Failed to open mysql.event +DROP EVENT intact_check_2; +ERROR HY000: Failed to open mysql.event +DROP EVENT intact_check; +ERROR HY000: Failed to open mysql.event +DROP DATABASE IF EXISTS mysqltest_no_such_database; +Warnings: +Note 1008 Can't drop database 'mysqltest_no_such_database'; database doesn't exist +CREATE DATABASE mysqltest_db2; +DROP DATABASE mysqltest_db2; +Warnings: +Error 1545 Failed to open mysql.event +SELECT @@event_scheduler; +@@event_scheduler +OFF +SHOW VARIABLES LIKE 'event_scheduler'; +Variable_name Value +event_scheduler OFF +SET GLOBAL event_scheduler=OFF; +ALTER TABLE mysql.event DROP dummy; +DROP EVENT intact_check; +CREATE EVENT intact_check ON SCHEDULE EVERY 10 HOUR DO SELECT "nothing"; +Warnings: +Warning 1105 Event scheduler is switched off, use SET GLOBAL event_scheduler=ON to enable it. + +Now let's add a column to the first position: the server +expects to see event schema name there + +ALTER TABLE mysql.event ADD dummy INT FIRST; +SHOW EVENTS; +ERROR HY000: Failed to open mysql.event +SELECT event_name FROM INFORMATION_SCHEMA.events; +ERROR HY000: Failed to open mysql.event +SHOW CREATE EVENT intact_check; +ERROR HY000: Failed to open mysql.event +DROP EVENT no_such_event; +ERROR HY000: Failed to open mysql.event +CREATE EVENT intact_check_1 ON SCHEDULE EVERY 5 HOUR DO SELECT 5; +ERROR HY000: Failed to open mysql.event +ALTER EVENT intact_check_1 ON SCHEDULE EVERY 8 HOUR DO SELECT 8; +ERROR HY000: Failed to open mysql.event +ALTER EVENT intact_check_1 RENAME TO intact_check_2; +ERROR HY000: Failed to open mysql.event +DROP EVENT intact_check_1; +ERROR HY000: Failed to open mysql.event +DROP EVENT intact_check_2; +ERROR HY000: Failed to open mysql.event +DROP EVENT intact_check; +ERROR HY000: Failed to open mysql.event +DROP DATABASE IF EXISTS mysqltest_no_such_database; +Warnings: +Note 1008 Can't drop database 'mysqltest_no_such_database'; database doesn't exist +CREATE DATABASE mysqltest_db2; +DROP DATABASE mysqltest_db2; +Warnings: +Error 1545 Failed to open mysql.event +SELECT @@event_scheduler; +@@event_scheduler +OFF +SHOW VARIABLES LIKE 'event_scheduler'; +Variable_name Value +event_scheduler OFF +SET GLOBAL event_scheduler=OFF; +Clean up +ALTER TABLE mysql.event DROP dummy; +DELETE FROM mysql.event; +CREATE EVENT intact_check ON SCHEDULE EVERY 10 HOUR DO SELECT "nothing"; +Warnings: +Warning 1105 Event scheduler is switched off, use SET GLOBAL event_scheduler=ON to enable it. +Back up the table, further changes are not reversible +CREATE TABLE event_like LIKE mysql.event; +INSERT INTO event_like SELECT * FROM mysql.event; + +Drop some columns and try more checks. + + +ALTER TABLE mysql.event DROP comment, DROP starts; +SHOW EVENTS; +ERROR HY000: Failed to open mysql.event +SELECT event_name FROM INFORMATION_SCHEMA.EVENTS; +ERROR HY000: Failed to open mysql.event +SHOW CREATE EVENT intact_check; +ERROR HY000: Failed to open mysql.event +DROP EVENT no_such_event; +ERROR HY000: Failed to open mysql.event +CREATE EVENT intact_check_1 ON SCHEDULE EVERY 5 HOUR DO SELECT 5; +ERROR HY000: Failed to open mysql.event +ALTER EVENT intact_check_1 ON SCHEDULE EVERY 8 HOUR DO SELECT 8; +ERROR HY000: Failed to open mysql.event +ALTER EVENT intact_check_1 RENAME TO intact_check_2; +ERROR HY000: Failed to open mysql.event +DROP EVENT intact_check_1; +ERROR HY000: Failed to open mysql.event +DROP EVENT intact_check_2; +ERROR HY000: Failed to open mysql.event +DROP EVENT intact_check; +ERROR HY000: Failed to open mysql.event +DROP DATABASE IF EXISTS mysqltest_no_such_database; +Warnings: +Note 1008 Can't drop database 'mysqltest_no_such_database'; database doesn't exist +CREATE DATABASE mysqltest_db2; +DROP DATABASE mysqltest_db2; +Warnings: +Error 1545 Failed to open mysql.event +SELECT @@event_scheduler; +@@event_scheduler +OFF +SHOW VARIABLES LIKE 'event_scheduler'; +Variable_name Value +event_scheduler OFF +SET GLOBAL event_scheduler=OFF; + +Now drop the table, and test again + + +DROP TABLE mysql.event; +SHOW EVENTS; +ERROR 42S02: Table 'mysql.event' doesn't exist +SELECT event_name FROM INFORMATION_SCHEMA.events; +ERROR 42S02: Table 'mysql.event' doesn't exist +SHOW CREATE EVENT intact_check; +ERROR 42S02: Table 'mysql.event' doesn't exist +DROP EVENT no_such_event; +ERROR 42S02: Table 'mysql.event' doesn't exist +CREATE EVENT intact_check_1 ON SCHEDULE EVERY 5 HOUR DO SELECT 5; +ERROR 42S02: Table 'mysql.event' doesn't exist +ALTER EVENT intact_check_1 ON SCHEDULE EVERY 8 HOUR DO SELECT 8; +ERROR 42S02: Table 'mysql.event' doesn't exist +ALTER EVENT intact_check_1 RENAME TO intact_check_2; +ERROR 42S02: Table 'mysql.event' doesn't exist +DROP EVENT intact_check_1; +ERROR 42S02: Table 'mysql.event' doesn't exist +DROP EVENT intact_check_2; +ERROR 42S02: Table 'mysql.event' doesn't exist +DROP EVENT intact_check; +ERROR 42S02: Table 'mysql.event' doesn't exist +DROP DATABASE IF EXISTS mysqltest_no_such_database; +Warnings: +Note 1008 Can't drop database 'mysqltest_no_such_database'; database doesn't exist +CREATE DATABASE mysqltest_db2; +DROP DATABASE mysqltest_db2; +Warnings: +Error 1146 Table 'mysql.event' doesn't exist +OK, there is an unnecessary warning about the non-existent table +but it's not easy to fix and no one complained about it. +A similar warning is printed if mysql.proc is missing. +SHOW WARNINGS; +Level Code Message +Error 1146 Table 'mysql.event' doesn't exist +SELECT @@event_scheduler; +@@event_scheduler +OFF +SHOW VARIABLES LIKE 'event_scheduler'; +Variable_name Value +event_scheduler OFF +SET GLOBAL event_scheduler=OFF; +Restore the original table. +CREATE TABLE mysql.event like event_like; +DROP TABLE event_like; +SHOW EVENTS; +Db Name Definer Time zone Type Execute at Interval value Interval field Starts Ends Status Originator character_set_client collation_connection Database Collation + +# +# Bug#12394306: the sever may crash if mysql.event is corrupted +# + +CREATE EVENT ev1 ON SCHEDULE EVERY 5 HOUR DO SELECT 5; +Warnings: +Warning 1105 Event scheduler is switched off, use SET GLOBAL event_scheduler=ON to enable it. +ALTER EVENT ev1 ON SCHEDULE EVERY 8 HOUR DO SELECT 8; + +CREATE TABLE event_original LIKE mysql.event; +INSERT INTO event_original SELECT * FROM mysql.event; + +ALTER TABLE mysql.event MODIFY modified CHAR(1); +Warnings: +Warning 1265 Data truncated for column 'modified' at row 1 + +SHOW EVENTS; +ERROR HY000: Failed to open mysql.event + +SELECT event_name, created, last_altered FROM information_schema.events; +ERROR HY000: Failed to open mysql.event + +CREATE EVENT ev2 ON SCHEDULE EVERY 5 HOUR DO SELECT 5; +ERROR HY000: Failed to open mysql.event + +ALTER EVENT ev1 ON SCHEDULE EVERY 9 HOUR DO SELECT 9; +ERROR HY000: Failed to open mysql.event + +DROP TABLE mysql.event; +RENAME TABLE event_original TO mysql.event; + +DROP EVENT ev1; + +SHOW EVENTS; +Db Name Definer Time zone Type Execute at Interval value Interval field Starts Ends Status Originator character_set_client collation_connection Database Collation +# +# MDEV-9524 Cannot load from mysql.event when sql_mode is set to PAD_CHAR_TO_FULL_LENGTH +# +CREATE TABLE t1 (a INT); +CREATE EVENT ev1 ON SCHEDULE EVERY 5 SECOND DO DELETE FROM t1; +Warnings: +Warning 1105 Event scheduler is switched off, use SET GLOBAL event_scheduler=ON to enable it. +SHOW EVENTS; +Db Name Definer Time zone Type Execute at Interval value Interval field Starts Ends Status Originator character_set_client collation_connection Database Collation +events_test ev1 root@localhost SYSTEM RECURRING NULL 5 # # NULL ENABLED 1 latin1 latin1_swedish_ci latin1_swedish_ci +SET sql_mode=PAD_CHAR_TO_FULL_LENGTH; +SHOW EVENTS; +Db Name Definer Time zone Type Execute at Interval value Interval field Starts Ends Status Originator character_set_client collation_connection Database Collation +events_test ev1 root@localhost SYSTEM RECURRING NULL 5 # # NULL ENABLED 1 latin1 latin1_swedish_ci latin1_swedish_ci +DROP EVENT ev1; +CREATE EVENT ev1 ON SCHEDULE EVERY 5 SECOND DO DELETE FROM t1; +Warnings: +Warning 1105 Event scheduler is switched off, use SET GLOBAL event_scheduler=ON to enable it. +SHOW EVENTS; +Db Name Definer Time zone Type Execute at Interval value Interval field Starts Ends Status Originator character_set_client collation_connection Database Collation +events_test ev1 root@localhost SYSTEM RECURRING NULL 5 # # NULL ENABLED 1 latin1 latin1_swedish_ci latin1_swedish_ci +DROP EVENT ev1; +DROP TABLE t1; +SET sql_mode=DEFAULT; + +# +# End of tests +# +drop database events_test; diff --git a/mysql-test/suite/events/events_1.test b/mysql-test/suite/events/events_1.test new file mode 100644 index 00000000..cace9eb3 --- /dev/null +++ b/mysql-test/suite/events/events_1.test @@ -0,0 +1,494 @@ +# changes 2008-02-20 hhunger splitted events.test into events_1 and events_2 +# changes 2008-02-22 hhunger replaced all sleep by wait_condition +# +# Can't test with embedded server that doesn't support grants +-- source include/not_embedded.inc + +set sql_mode=""; +--source include/default_charset.inc + +call mtr.add_suppression("Column count of mysql.event is wrong. Expected .*, found .*\. The table is probably corrupted"); +call mtr.add_suppression("Incorrect definition of table mysql.event:.*"); +call mtr.add_suppression("Event Scheduler: .* DROP command denied to user"); + +create database events_test; +use events_test; + +# +# START: BUG #17289 Events: missing privilege check for drop database +# +CREATE USER pauline@localhost; +CREATE DATABASE db_x; +GRANT EVENT ON db_x.* TO pauline@localhost; +USE db_x; +CREATE TABLE x_table(a int); +connect (priv_conn,localhost,pauline,,db_x); +CREATE EVENT e_x1 ON SCHEDULE EVERY 1 SECOND DO DROP DATABASE db_x; +CREATE EVENT e_x2 ON SCHEDULE EVERY 1 SECOND DO DROP TABLE x_table; +connection default; +SHOW DATABASES LIKE 'db_x'; +SET GLOBAL event_scheduler=1; +let $wait_condition= SELECT count(*)= 1 FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME LIKE 'db_x'; +--source include/wait_condition.inc +SHOW DATABASES LIKE 'db_x'; +SHOW TABLES FROM db_x; +SET GLOBAL event_scheduler=off; +connection priv_conn; +DROP EVENT e_x1; +DROP EVENT e_x2; +disconnect priv_conn; +connection default; +let $wait_condition= SELECT count(*)=1 from information_schema.processlist; +--source include/wait_condition.inc +DROP DATABASE db_x; +DROP USER pauline@localhost; +USE events_test; +# +# END: BUG #17289 Events: missing privilege check for drop database +# +SET GLOBAL event_scheduler=off; +drop event if exists event1; +create event event1 on schedule every 15 minute starts now() ends date_add(now(), interval 5 hour) DO begin end; +alter event event1 rename to event2 enable; +alter event event2 disable; +alter event event2 enable; +alter event event2 on completion not preserve; +delimiter __; +alter event event2 on schedule every 1 year on completion preserve rename to event3 comment "new comment" do begin select 1; end__ +delimiter ;__ +alter event event3 rename to event2; + +drop event event2; +create event event2 on schedule every 2 second starts now() ends date_add(now(), interval 5 hour) comment "some" DO begin end; +drop event event2; + +# +# BUG #16537 (Events: mysql.event.starts is null) +# +CREATE EVENT event_starts_test ON SCHEDULE EVERY 10 SECOND COMMENT "" DO SELECT 1; +SELECT interval_field, interval_value, body FROM mysql.event WHERE db='events_test' AND name='event_starts_test'; +SELECT execute_at IS NULL, starts IS NULL, ends IS NULL, comment FROM mysql.event WHERE db='events_test' AND name='event_starts_test'; +ALTER EVENT event_starts_test ON SCHEDULE AT '1970-01-02 00:00:00' ON COMPLETION PRESERVE DISABLE; +SELECT execute_at IS NULL, starts IS NULL, ends IS NULL, comment FROM mysql.event WHERE db='events_test' AND name='event_starts_test'; +ALTER EVENT event_starts_test COMMENT "non-empty comment"; +SELECT execute_at IS NULL, starts IS NULL, ends IS NULL, comment FROM mysql.event WHERE db='events_test' AND name='event_starts_test'; +ALTER EVENT event_starts_test COMMENT ""; +SELECT execute_at IS NULL, starts IS NULL, ends IS NULL, comment FROM mysql.event WHERE db='events_test' AND name='event_starts_test'; +DROP EVENT event_starts_test; + +CREATE EVENT event_starts_test ON SCHEDULE EVERY 20 SECOND STARTS '1970-01-02 00:00:00' ENDS '1970-01-03 00:00:00' ON COMPLETION PRESERVE DISABLE DO SELECT 2; +SELECT execute_at IS NULL, starts IS NULL, ends IS NULL, comment FROM mysql.event WHERE db='events_test' AND name='event_starts_test'; +ALTER EVENT event_starts_test COMMENT "non-empty comment"; +SELECT execute_at IS NULL, starts IS NULL, ends IS NULL, comment FROM mysql.event WHERE db='events_test' AND name='event_starts_test'; +ALTER EVENT event_starts_test COMMENT ""; +SELECT execute_at IS NULL, starts IS NULL, ends IS NULL, comment FROM mysql.event WHERE db='events_test' AND name='event_starts_test'; +DROP EVENT event_starts_test; +# +# +create table test_nested(a int); +create event e_43 on schedule every 1 second do set @a = 5; +--error ER_EVENT_RECURSION_FORBIDDEN +alter event e_43 do alter event e_43 do set @a = 4; +delimiter |; +alter event e_43 do +begin + alter event e_43 on schedule every 5 minute; + insert into test_nested values(1); +end| +delimiter ;| +set global event_scheduler = on; +let $wait_condition= SELECT count(*)>0 from mysql.event where name='e_43' and interval_value= 5; +--source include/wait_condition.inc +let $wait_condition= SELECT count(*)=2 from information_schema.processlist; +--source include/wait_condition.inc +select db, name, body, status, interval_field, interval_value from mysql.event; +drop event e_43; +drop table test_nested; + +--echo "Let's check whether we can use non-qualified names" +create table non_qualif(a int); +create event non_qualif_ev on schedule every 10 minute do insert into non_qualif values (800219); +let $wait_condition=SELECT count(*)= 1 from non_qualif where a=800219; +--source include/wait_condition.inc +select * from non_qualif; +drop event non_qualif_ev; +drop table non_qualif; + +--error ER_EVENT_DOES_NOT_EXIST +alter event non_existant rename to non_existant_too; + +set global event_scheduler = off; +create event existant on schedule at now() + interval 1 year do select 12; +--error ER_EVENT_ALREADY_EXISTS +alter event non_existant rename to existant; +--error ER_EVENT_SAME_NAME +alter event existant rename to events_test.existant; +drop event existant; + + +create table t_event3 (a int, b float); +drop event if exists event3; +create event event3 on schedule every 50 + 10 minute starts date_add(curdate(), interval 5 minute) ends date_add(curdate(), interval 5 day) comment "portokala_comment" DO insert into t_event3 values (unix_timestamp(), rand()); +let $wait_condition=SELECT count(*)=0 from t_event3; +--source include/wait_condition.inc +select count(*) from t_event3; +drop event event3; +drop table t_event3; + + +set names utf8; +# +# SHOW CREATE EVENT test begin +# +CREATE EVENT root6 ON SCHEDULE EVERY '10:20' MINUTE_SECOND ON COMPLETION PRESERVE ENABLE COMMENT 'some comment' DO select 1; +--replace_regex /STARTS '[^']+'/STARTS '#'/ +SHOW CREATE EVENT root6; +create event root7 on schedule every 2 year do select 1; +--replace_regex /STARTS '[^']+'/STARTS '#'/ +SHOW CREATE EVENT root7; +create event root8 on schedule every '2:5' year_month do select 1; +--replace_regex /STARTS '[^']+'/STARTS '#'/ +SHOW CREATE EVENT root8; +create event root8_1 on schedule every '2:15' year_month do select 1; +--replace_regex /STARTS '[^']+'/STARTS '#'/ +SHOW CREATE EVENT root8_1; +create event root9 on schedule every 2 week ON COMPLETION PRESERVE DISABLE COMMENT 'коментар на кирилица' do select 1; +--replace_regex /STARTS '[^']+'/STARTS '#'/ +SHOW CREATE EVENT root9; +create event root10 on schedule every '20:5' day_hour do select 1; +--replace_regex /STARTS '[^']+'/STARTS '#'/ +SHOW CREATE EVENT root10; +create event root11 on schedule every '20:25' day_hour do select 1; +--replace_regex /STARTS '[^']+'/STARTS '#'/ +SHOW CREATE EVENT root11; +create event root12 on schedule every '20:25' hour_minute do select 1; +--replace_regex /STARTS '[^']+'/STARTS '#'/ +SHOW CREATE EVENT root12; +create event root13 on schedule every '25:25' hour_minute do select 1; +--replace_regex /STARTS '[^']+'/STARTS '#'/ +SHOW CREATE EVENT root13; +create event root13_1 on schedule every '11:65' hour_minute do select 1; +--replace_regex /STARTS '[^']+'/STARTS '#'/ +SHOW CREATE EVENT root13_1; +create event root14 on schedule every '35:35' minute_second do select 1; +--replace_regex /STARTS '[^']+'/STARTS '#'/ +SHOW CREATE EVENT root14; +create event root15 on schedule every '35:66' minute_second do select 1; +--replace_regex /STARTS '[^']+'/STARTS '#'/ +SHOW CREATE EVENT root15; +create event root16 on schedule every '35:56' day_minute do select 1; +--replace_regex /STARTS '[^']+'/STARTS '#'/ +SHOW CREATE EVENT root16; +create event root17 on schedule every '35:12:45' day_minute do select 1; +--replace_regex /STARTS '[^']+'/STARTS '#'/ +SHOW CREATE EVENT root17; +create event root17_1 on schedule every '35:25:65' day_minute do select 1; +--replace_regex /STARTS '[^']+'/STARTS '#'/ +SHOW CREATE EVENT root17_1; +create event root18 on schedule every '35:12:45' hour_second do select 1; +--replace_regex /STARTS '[^']+'/STARTS '#'/ +SHOW CREATE EVENT root18; +create event root19 on schedule every '15:59:85' hour_second do select 1; +--replace_regex /STARTS '[^']+'/STARTS '#'/ +SHOW CREATE EVENT root19; +create event root20 on schedule every '50:20:12:45' day_second do select 1; +--replace_regex /STARTS '[^']+'/STARTS '#'/ +SHOW CREATE EVENT root20; +set names cp1251; +create event 21 on schedule every '50:23:59:95' day_second COMMENT ' 1251 ' do select 1; +--replace_regex /STARTS '[^']+'/STARTS '#'/ +SHOW CREATE EVENT 21; +insert into mysql.event ( + db, + name, + body, + definer, + interval_value, + interval_field, + originator, + character_set_client, + collation_connection, + db_collation, + body_utf8) +values ( + database(), + "root22", + "select 1", + user(), + 100, + "SECOND_MICROSECOND", + 1, + 'utf8', + 'utf8_general_ci', + 'utf8_general_ci', + 'select 1'); +--error ER_NOT_SUPPORTED_YET +show create event root22; +--error ER_NOT_SUPPORTED_YET +SHOW EVENTS; +drop event root22; +--error ER_EVENT_INTERVAL_NOT_POSITIVE_OR_TOO_BIG +create event root23 on schedule every -100 year do select 1; +--error ER_EVENT_INTERVAL_NOT_POSITIVE_OR_TOO_BIG +create event root23 on schedule every 222222222222222222222 year do select 1; +drop event root6; +drop event root7; +drop event root8; +drop event root8_1; +drop event root9; +drop event root10; +drop event root11; +drop event root12; +drop event root13; +drop event root13_1; +drop event root14; +drop event root15; +drop event root16; +drop event root17; +drop event root17_1; +drop event root18; +drop event root19; +drop event root20; +drop event 21; + +set names latin1; +# +# SHOW CREATE EVENT test end +# + +# +# mysql.event intact checking +# Check that the server does not crash if +# one has destroyed or tampered with the event table. +# Please see see for events_restart_phase* tests to +# see the server behavior at start up with bad mysql.event +# table. +# +# +--echo Create a test event. Only event metadata is relevant, +--echo the actual schedule and body are not. +# +CREATE EVENT intact_check ON SCHEDULE EVERY 10 HOUR DO SELECT "nothing"; +--replace_column 8 # 9 # +SHOW EVENTS; +# +--echo Try to alter mysql.event: the server should fail to load +--echo event information after mysql.event was tampered with. +--echo +--echo First, let's add a column to the end and check the error is emitted. +--echo +ALTER TABLE mysql.event ADD dummy INT; +--error ER_EVENT_OPEN_TABLE_FAILED +SHOW EVENTS; +--error ER_EVENT_OPEN_TABLE_FAILED +SELECT event_name FROM INFORMATION_SCHEMA.events; +--error ER_EVENT_OPEN_TABLE_FAILED +SHOW CREATE EVENT intact_check; +--error ER_EVENT_OPEN_TABLE_FAILED +DROP EVENT no_such_event; +--error ER_EVENT_OPEN_TABLE_FAILED +CREATE EVENT intact_check_1 ON SCHEDULE EVERY 5 HOUR DO SELECT 5; +--error ER_EVENT_OPEN_TABLE_FAILED +ALTER EVENT intact_check_1 ON SCHEDULE EVERY 8 HOUR DO SELECT 8; +--error ER_EVENT_OPEN_TABLE_FAILED +ALTER EVENT intact_check_1 RENAME TO intact_check_2; +--error ER_EVENT_OPEN_TABLE_FAILED +DROP EVENT intact_check_1; +--error ER_EVENT_OPEN_TABLE_FAILED +DROP EVENT intact_check_2; +--error ER_EVENT_OPEN_TABLE_FAILED +DROP EVENT intact_check; +DROP DATABASE IF EXISTS mysqltest_no_such_database; +CREATE DATABASE mysqltest_db2; +DROP DATABASE mysqltest_db2; +SELECT @@event_scheduler; +SHOW VARIABLES LIKE 'event_scheduler'; +SET GLOBAL event_scheduler=OFF; +# Clean up +ALTER TABLE mysql.event DROP dummy; +DROP EVENT intact_check; +CREATE EVENT intact_check ON SCHEDULE EVERY 10 HOUR DO SELECT "nothing"; +--echo +--echo Now let's add a column to the first position: the server +--echo expects to see event schema name there +--echo +ALTER TABLE mysql.event ADD dummy INT FIRST; +--error ER_EVENT_OPEN_TABLE_FAILED +SHOW EVENTS; +--error ER_EVENT_OPEN_TABLE_FAILED +SELECT event_name FROM INFORMATION_SCHEMA.events; +--error ER_EVENT_OPEN_TABLE_FAILED +SHOW CREATE EVENT intact_check; +--error ER_EVENT_OPEN_TABLE_FAILED +DROP EVENT no_such_event; +--error ER_EVENT_OPEN_TABLE_FAILED +CREATE EVENT intact_check_1 ON SCHEDULE EVERY 5 HOUR DO SELECT 5; +--error ER_EVENT_OPEN_TABLE_FAILED +ALTER EVENT intact_check_1 ON SCHEDULE EVERY 8 HOUR DO SELECT 8; +--error ER_EVENT_OPEN_TABLE_FAILED +ALTER EVENT intact_check_1 RENAME TO intact_check_2; +--error ER_EVENT_OPEN_TABLE_FAILED +DROP EVENT intact_check_1; +--error ER_EVENT_OPEN_TABLE_FAILED +DROP EVENT intact_check_2; +--error ER_EVENT_OPEN_TABLE_FAILED +DROP EVENT intact_check; +# Should work OK +DROP DATABASE IF EXISTS mysqltest_no_such_database; +CREATE DATABASE mysqltest_db2; +DROP DATABASE mysqltest_db2; +SELECT @@event_scheduler; +SHOW VARIABLES LIKE 'event_scheduler'; +SET GLOBAL event_scheduler=OFF; +--echo Clean up +ALTER TABLE mysql.event DROP dummy; +DELETE FROM mysql.event; +CREATE EVENT intact_check ON SCHEDULE EVERY 10 HOUR DO SELECT "nothing"; +--echo Back up the table, further changes are not reversible +CREATE TABLE event_like LIKE mysql.event; +INSERT INTO event_like SELECT * FROM mysql.event; +--echo +--echo Drop some columns and try more checks. +--echo +--echo +ALTER TABLE mysql.event DROP comment, DROP starts; +--error ER_EVENT_OPEN_TABLE_FAILED +SHOW EVENTS; +--error ER_EVENT_OPEN_TABLE_FAILED +SELECT event_name FROM INFORMATION_SCHEMA.EVENTS; +--error ER_EVENT_OPEN_TABLE_FAILED +SHOW CREATE EVENT intact_check; +--error ER_EVENT_OPEN_TABLE_FAILED +DROP EVENT no_such_event; +--error ER_EVENT_OPEN_TABLE_FAILED +CREATE EVENT intact_check_1 ON SCHEDULE EVERY 5 HOUR DO SELECT 5; +--error ER_EVENT_OPEN_TABLE_FAILED +ALTER EVENT intact_check_1 ON SCHEDULE EVERY 8 HOUR DO SELECT 8; +--error ER_EVENT_OPEN_TABLE_FAILED +ALTER EVENT intact_check_1 RENAME TO intact_check_2; +--error ER_EVENT_OPEN_TABLE_FAILED +DROP EVENT intact_check_1; +--error ER_EVENT_OPEN_TABLE_FAILED +DROP EVENT intact_check_2; +--error ER_EVENT_OPEN_TABLE_FAILED +DROP EVENT intact_check; +DROP DATABASE IF EXISTS mysqltest_no_such_database; +CREATE DATABASE mysqltest_db2; +DROP DATABASE mysqltest_db2; +SELECT @@event_scheduler; +SHOW VARIABLES LIKE 'event_scheduler'; +SET GLOBAL event_scheduler=OFF; +--echo +--echo Now drop the table, and test again +--echo +--echo +DROP TABLE mysql.event; +--error ER_NO_SUCH_TABLE +SHOW EVENTS; +--error ER_NO_SUCH_TABLE +SELECT event_name FROM INFORMATION_SCHEMA.events; +--error ER_NO_SUCH_TABLE +SHOW CREATE EVENT intact_check; +--error ER_NO_SUCH_TABLE +DROP EVENT no_such_event; +--error ER_NO_SUCH_TABLE +CREATE EVENT intact_check_1 ON SCHEDULE EVERY 5 HOUR DO SELECT 5; +--error ER_NO_SUCH_TABLE +ALTER EVENT intact_check_1 ON SCHEDULE EVERY 8 HOUR DO SELECT 8; +--error ER_NO_SUCH_TABLE +ALTER EVENT intact_check_1 RENAME TO intact_check_2; +--error ER_NO_SUCH_TABLE +DROP EVENT intact_check_1; +--error ER_NO_SUCH_TABLE +DROP EVENT intact_check_2; +--error ER_NO_SUCH_TABLE +DROP EVENT intact_check; +DROP DATABASE IF EXISTS mysqltest_no_such_database; +CREATE DATABASE mysqltest_db2; +DROP DATABASE mysqltest_db2; +--echo OK, there is an unnecessary warning about the non-existent table +--echo but it's not easy to fix and no one complained about it. +--echo A similar warning is printed if mysql.proc is missing. +SHOW WARNINGS; +SELECT @@event_scheduler; +SHOW VARIABLES LIKE 'event_scheduler'; +SET GLOBAL event_scheduler=OFF; +--echo Restore the original table. +CREATE TABLE mysql.event like event_like; +DROP TABLE event_like; +--replace_column 8 # 9 # +SHOW EVENTS; + +--echo +--echo # +--echo # Bug#12394306: the sever may crash if mysql.event is corrupted +--echo # + +--echo +CREATE EVENT ev1 ON SCHEDULE EVERY 5 HOUR DO SELECT 5; +ALTER EVENT ev1 ON SCHEDULE EVERY 8 HOUR DO SELECT 8; + +--echo +CREATE TABLE event_original LIKE mysql.event; +INSERT INTO event_original SELECT * FROM mysql.event; + +--echo +ALTER TABLE mysql.event MODIFY modified CHAR(1); + +--echo +--error ER_EVENT_OPEN_TABLE_FAILED +SHOW EVENTS; + +--echo +--error ER_EVENT_OPEN_TABLE_FAILED +SELECT event_name, created, last_altered FROM information_schema.events; + +--echo +--error ER_EVENT_OPEN_TABLE_FAILED +CREATE EVENT ev2 ON SCHEDULE EVERY 5 HOUR DO SELECT 5; + +--echo +--error ER_EVENT_OPEN_TABLE_FAILED +ALTER EVENT ev1 ON SCHEDULE EVERY 9 HOUR DO SELECT 9; + +--echo +DROP TABLE mysql.event; +RENAME TABLE event_original TO mysql.event; + +--echo +DROP EVENT ev1; + +--echo +SHOW EVENTS; + + +--echo # +--echo # MDEV-9524 Cannot load from mysql.event when sql_mode is set to PAD_CHAR_TO_FULL_LENGTH +--echo # +CREATE TABLE t1 (a INT); +CREATE EVENT ev1 ON SCHEDULE EVERY 5 SECOND DO DELETE FROM t1; +--replace_column 8 # 9 # +SHOW EVENTS; +SET sql_mode=PAD_CHAR_TO_FULL_LENGTH; +--replace_column 8 # 9 # +SHOW EVENTS; +DROP EVENT ev1; +CREATE EVENT ev1 ON SCHEDULE EVERY 5 SECOND DO DELETE FROM t1; +--replace_column 8 # 9 # +SHOW EVENTS; +DROP EVENT ev1; +DROP TABLE t1; +SET sql_mode=DEFAULT; + + +--echo +--echo # +--echo # End of tests +--echo # + +let $wait_condition= + select count(*) = 0 from information_schema.processlist + where db='events_test' and command = 'Connect' and user=current_user(); +--source include/wait_condition.inc + +drop database events_test; diff --git a/mysql-test/suite/events/events_2.result b/mysql-test/suite/events/events_2.result new file mode 100644 index 00000000..1753fa41 --- /dev/null +++ b/mysql-test/suite/events/events_2.result @@ -0,0 +1,439 @@ +set sql_mode=""; +drop database if exists events_test; +create database events_test; +use events_test; +create event e_26 on schedule at '2037-01-01 00:00:00' disable do set @a = 5; +Warnings: +Warning 1105 Event scheduler is switched off, use SET GLOBAL event_scheduler=ON to enable it. +select db, name, body, definer, convert_tz(execute_at, 'UTC', 'SYSTEM'), on_completion from mysql.event; +db name body definer convert_tz(execute_at, 'UTC', 'SYSTEM') on_completion +events_test e_26 set @a = 5 root@localhost 2037-01-01 00:00:00 DROP +drop event e_26; +create event e_26 on schedule at NULL disable do set @a = 5; +ERROR HY000: Incorrect AT value: 'NULL' +create event e_26 on schedule at 'definitely not a datetime' disable do set @a = 5; +ERROR HY000: Incorrect AT value: 'definitely not a datetime' +set names utf8; +create event задачка on schedule every 123 minute starts now() ends now() + interval 1 month do select 1; +Warnings: +Warning 1105 Event scheduler is switched off, use SET GLOBAL event_scheduler=ON to enable it. +drop event задачка; +"DISABLE the scheduler. Testing that it does not work when the variable is 0" +set global event_scheduler=off; +select definer, name, db from mysql.event; +definer name db +select get_lock("test_lock1", 20); +get_lock("test_lock1", 20) +1 +create event закачка on schedule every 10 hour do select get_lock("test_lock1", 20); +Warnings: +Warning 1105 Event scheduler is switched off, use SET GLOBAL event_scheduler=ON to enable it. +"Should return 1 row" +select definer, name, db from mysql.event; +definer name db +root@localhost закачка events_test +"Should be only 0 process" +select /*1*/ user, host, db, command, state, info +from information_schema.processlist +where (user='event_scheduler') +order by info; +user host db command state info +select release_lock("test_lock1"); +release_lock("test_lock1") +1 +drop event закачка; +"Should have 0 events" +select count(*) from mysql.event; +count(*) +0 +"ENABLE the scheduler and get a lock" +set global event_scheduler=on; +select get_lock("test_lock2", 20); +get_lock("test_lock2", 20) +1 +"Create an event which tries to acquire a mutex. The event locks on the mutex" +create event закачка on schedule every 10 hour do select get_lock("test_lock2", 20); +"Should have only 2 processes: the scheduler and the locked event" +select /*2*/ user, host, db, command, state, info +from information_schema.processlist +where (info like "select get_lock%" OR user='event_scheduler') +order by info; +user host db command state info +event_scheduler localhost NULL Daemon Waiting for next activation NULL +root localhost events_test Connect User lock select get_lock("test_lock2", 20) +"Release the mutex, the event worker should finish." +select release_lock("test_lock2"); +release_lock("test_lock2") +1 +drop event закачка; +set global event_scheduler=1; +select get_lock("test_lock2_1", 20); +get_lock("test_lock2_1", 20) +1 +create event закачка21 on schedule every 10 hour do select get_lock("test_lock2_1", 20); +"Should have only 2 processes: the scheduler and the locked event" +select /*3*/ user, host, db, command, state, info +from information_schema.processlist +where (info like "select get_lock%" OR user='event_scheduler') +order by info; +user host db command state info +event_scheduler localhost NULL Daemon Waiting for next activation NULL +root localhost events_test Connect User lock select get_lock("test_lock2_1", 20) +set global event_scheduler=off; +"Should have only our process now:" +select /*4*/ user, host, db, command, state, info +from information_schema.processlist +where (info like "select get_lock%" OR user='event_scheduler') +order by info; +user host db command state info +root localhost events_test Connect User lock select get_lock("test_lock2_1", 20) +select release_lock("test_lock2_1"); +release_lock("test_lock2_1") +1 +drop event закачка21; +create table t_16 (s1 int); +create trigger t_16_bi before insert on t_16 for each row create event e_16 on schedule every 1 second do set @a=5; +ERROR HY000: Recursion of EVENT DDL statements is forbidden when body is present +drop table t_16; +create event white_space +on schedule every 10 hour +disable +do +select 1; +Warnings: +Warning 1105 Event scheduler is switched off, use SET GLOBAL event_scheduler=ON to enable it. +select event_schema, event_name, definer, event_definition from information_schema.events where event_name='white_space'; +event_schema event_name definer event_definition +events_test white_space root@localhost select 1 +drop event white_space; +create event white_space on schedule every 10 hour disable do +select 2; +Warnings: +Warning 1105 Event scheduler is switched off, use SET GLOBAL event_scheduler=ON to enable it. +select event_schema, event_name, definer, event_definition from information_schema.events where event_name='white_space'; +event_schema event_name definer event_definition +events_test white_space root@localhost select 2 +drop event white_space; +create event white_space on schedule every 10 hour disable do select 3; +Warnings: +Warning 1105 Event scheduler is switched off, use SET GLOBAL event_scheduler=ON to enable it. +select event_schema, event_name, definer, event_definition from information_schema.events where event_name='white_space'; +event_schema event_name definer event_definition +events_test white_space root@localhost select 3 +drop event white_space; +create event e1 on schedule every 1 year do set @a = 5; +Warnings: +Warning 1105 Event scheduler is switched off, use SET GLOBAL event_scheduler=ON to enable it. +create table t1 (s1 int); +create trigger t1_ai after insert on t1 for each row show create event e1; +ERROR 0A000: Not allowed to return a result set from a trigger +drop table t1; +drop event e1; +SHOW EVENTS FROM aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa; +ERROR 42000: Incorrect database name 'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa' +SHOW EVENTS FROM ``; +ERROR 42000: Incorrect database name '' +SHOW EVENTS FROM `events\\test`; +Db Name Definer Time zone Type Execute at Interval value Interval field Starts Ends Status Originator character_set_client collation_connection Database Collation + +LOCK TABLES mode. + +create table t1 (a int); +create event e1 on schedule every 10 hour do select 1; +Warnings: +Warning 1105 Event scheduler is switched off, use SET GLOBAL event_scheduler=ON to enable it. +lock table t1 read; +show create event e1; +Event sql_mode time_zone Create Event character_set_client collation_connection Database Collation +e1 SYSTEM CREATE DEFINER=`root`@`localhost` EVENT `e1` ON SCHEDULE EVERY 10 HOUR STARTS '#' ON COMPLETION NOT PRESERVE ENABLE DO select 1 utf8mb3 utf8mb3_general_ci latin1_swedish_ci +select event_name from information_schema.events; +event_name +e1 +create event e2 on schedule every 10 hour do select 1; +ERROR HY000: Can't execute the given command because you have active locked tables or an active transaction +alter event e2 disable; +ERROR HY000: Can't execute the given command because you have active locked tables or an active transaction +alter event e2 rename to e3; +ERROR HY000: Can't execute the given command because you have active locked tables or an active transaction +drop event e2; +ERROR HY000: Can't execute the given command because you have active locked tables or an active transaction +drop event e1; +ERROR HY000: Can't execute the given command because you have active locked tables or an active transaction +unlock tables; +lock table t1 write; +show create event e1; +Event sql_mode time_zone Create Event character_set_client collation_connection Database Collation +e1 SYSTEM CREATE DEFINER=`root`@`localhost` EVENT `e1` ON SCHEDULE EVERY 10 HOUR STARTS '#' ON COMPLETION NOT PRESERVE ENABLE DO select 1 utf8mb3 utf8mb3_general_ci latin1_swedish_ci +select event_name from information_schema.events; +event_name +e1 +create event e2 on schedule every 10 hour do select 1; +ERROR HY000: Can't execute the given command because you have active locked tables or an active transaction +alter event e2 disable; +ERROR HY000: Can't execute the given command because you have active locked tables or an active transaction +alter event e2 rename to e3; +ERROR HY000: Can't execute the given command because you have active locked tables or an active transaction +drop event e2; +ERROR HY000: Can't execute the given command because you have active locked tables or an active transaction +drop event e1; +ERROR HY000: Can't execute the given command because you have active locked tables or an active transaction +unlock tables; +lock table t1 read, mysql.event read; +show create event e1; +Event sql_mode time_zone Create Event character_set_client collation_connection Database Collation +e1 SYSTEM CREATE DEFINER=`root`@`localhost` EVENT `e1` ON SCHEDULE EVERY 10 HOUR STARTS '#' ON COMPLETION NOT PRESERVE ENABLE DO select 1 utf8mb3 utf8mb3_general_ci latin1_swedish_ci +select event_name from information_schema.events; +event_name +e1 +create event e2 on schedule every 10 hour do select 1; +ERROR HY000: Can't execute the given command because you have active locked tables or an active transaction +alter event e2 disable; +ERROR HY000: Can't execute the given command because you have active locked tables or an active transaction +alter event e2 rename to e3; +ERROR HY000: Can't execute the given command because you have active locked tables or an active transaction +drop event e2; +ERROR HY000: Can't execute the given command because you have active locked tables or an active transaction +drop event e1; +ERROR HY000: Can't execute the given command because you have active locked tables or an active transaction +unlock tables; +lock table t1 write, mysql.event read; +show create event e1; +Event sql_mode time_zone Create Event character_set_client collation_connection Database Collation +e1 SYSTEM CREATE DEFINER=`root`@`localhost` EVENT `e1` ON SCHEDULE EVERY 10 HOUR STARTS '#' ON COMPLETION NOT PRESERVE ENABLE DO select 1 utf8mb3 utf8mb3_general_ci latin1_swedish_ci +select event_name from information_schema.events; +event_name +e1 +create event e2 on schedule every 10 hour do select 1; +ERROR HY000: Can't execute the given command because you have active locked tables or an active transaction +alter event e2 disable; +ERROR HY000: Can't execute the given command because you have active locked tables or an active transaction +alter event e2 rename to e3; +ERROR HY000: Can't execute the given command because you have active locked tables or an active transaction +drop event e2; +ERROR HY000: Can't execute the given command because you have active locked tables or an active transaction +drop event e1; +ERROR HY000: Can't execute the given command because you have active locked tables or an active transaction +unlock tables; +lock table t1 read, mysql.event write; +ERROR HY000: You can't combine write-locking of system tables with other tables or lock types +lock table t1 write, mysql.event write; +ERROR HY000: You can't combine write-locking of system tables with other tables or lock types +lock table mysql.event write; +show create event e1; +Event sql_mode time_zone Create Event character_set_client collation_connection Database Collation +e1 SYSTEM CREATE DEFINER=`root`@`localhost` EVENT `e1` ON SCHEDULE EVERY 10 HOUR STARTS '#' ON COMPLETION NOT PRESERVE ENABLE DO select 1 utf8mb3 utf8mb3_general_ci latin1_swedish_ci +select event_name from information_schema.events; +event_name +e1 +create event e2 on schedule every 10 hour do select 1; +ERROR HY000: Can't execute the given command because you have active locked tables or an active transaction +alter event e2 disable; +ERROR HY000: Can't execute the given command because you have active locked tables or an active transaction +alter event e2 rename to e3; +ERROR HY000: Can't execute the given command because you have active locked tables or an active transaction +drop event e3; +ERROR HY000: Can't execute the given command because you have active locked tables or an active transaction +drop event e1; +ERROR HY000: Can't execute the given command because you have active locked tables or an active transaction +unlock tables; +drop event e1; +Make sure we have left no events +select event_name from information_schema.events; +event_name + +Events in sub-statements, events and prelocking + + +create event e1 on schedule every 10 hour do select 1; +Warnings: +Warning 1105 Event scheduler is switched off, use SET GLOBAL event_scheduler=ON to enable it. +create function f1() returns int +begin +show create event e1; +return 1; +end| +ERROR 0A000: Not allowed to return a result set from a function +create trigger trg before insert on t1 for each row +begin +show create event e1; +end| +ERROR 0A000: Not allowed to return a result set from a trigger +create function f1() returns int +begin +select event_name from information_schema.events; +return 1; +end| +ERROR 0A000: Not allowed to return a result set from a function +create trigger trg before insert on t1 for each row +begin +select event_name from information_schema.events; +end| +ERROR 0A000: Not allowed to return a result set from a trigger +create function f1() returns int +begin +create event e2 on schedule every 10 hour do select 1; +return 1; +end| +ERROR HY000: Recursion of EVENT DDL statements is forbidden when body is present +create function f1() returns int +begin +alter event e1 rename to e2; +return 1; +end| +ERROR HY000: Explicit or implicit commit is not allowed in stored function or trigger +create function f1() returns int +begin +drop event e2; +return 1; +end| +ERROR HY000: Explicit or implicit commit is not allowed in stored function or trigger +---------------------------------------------------------------------- +create trigger trg before insert on t1 for each row +begin +set new.a= f1(); +end| +create function f1() returns int +begin +call p1(); +return 0; +end| +create procedure p1() +begin +select event_name from information_schema.events; +end| +insert into t1 (a) values (1)| +ERROR 0A000: Not allowed to return a result set from a trigger +drop procedure p1| +create procedure p1() +begin +show create event e1; +end| +insert into t1 (a) values (1)| +ERROR 0A000: Not allowed to return a result set from a trigger +drop procedure p1| +create procedure p1() +begin +create temporary table tmp select event_name from information_schema.events; +end| +expected to work, since we redirect the output into a tmp table +insert into t1 (a) values (1)| +select * from tmp| +event_name +e1 +drop temporary table tmp| +drop procedure p1| +create procedure p1() +begin +alter event e1 rename to e2; +end| +insert into t1 (a) values (1)| +ERROR HY000: Explicit or implicit commit is not allowed in stored function or trigger +drop procedure p1| +create procedure p1() +begin +drop event e1; +end| +insert into t1 (a) values (1)| +ERROR HY000: Explicit or implicit commit is not allowed in stored function or trigger +drop table t1| +drop event e1| +set names utf8; +create event имя_события_в_кодировке_утф8_длиной_больше_чем_48 on schedule every 2 year do select 1; +Warnings: +Warning 1105 Event scheduler is switched off, use SET GLOBAL event_scheduler=ON to enable it. +select EVENT_NAME from information_schema.events +where event_schema='test'; +EVENT_NAME +drop event имя_события_в_кодировке_утф8_длиной_больше_чем_48; +create event +очень_очень_очень_очень_очень_очень_очень_очень_длинная_строка_66 +on schedule every 2 year do select 1; +ERROR 42000: Identifier name 'очень_очень_очень_очень_очень_очень_очень_очень_длин...' is too long +create event event_35981 on schedule every 6 month on completion preserve +disable +do +select 1; +Warnings: +Warning 1105 Event scheduler is switched off, use SET GLOBAL event_scheduler=ON to enable it. +The following SELECTs should all give 1 +select count(*) from information_schema.events +where event_schema = database() and event_name = 'event_35981' and +on_completion = 'PRESERVE'; +count(*) +1 +alter event event_35981 enable; +select count(*) from information_schema.events +where event_schema = database() and event_name = 'event_35981' and +on_completion = 'PRESERVE'; +count(*) +1 +alter event event_35981 on completion not preserve; +select count(*) from information_schema.events +where event_schema = database() and event_name = 'event_35981' and +on_completion = 'NOT PRESERVE'; +count(*) +1 +alter event event_35981 disable; +select count(*) from information_schema.events +where event_schema = database() and event_name = 'event_35981' and +on_completion = 'NOT PRESERVE'; +count(*) +1 +alter event event_35981 on completion preserve; +select count(*) from information_schema.events +where event_schema = database() and event_name = 'event_35981' and +on_completion = 'PRESERVE'; +count(*) +1 +drop event event_35981; +create event event_35981 on schedule every 6 month disable +do +select 1; +Warnings: +Warning 1105 Event scheduler is switched off, use SET GLOBAL event_scheduler=ON to enable it. +select count(*) from information_schema.events +where event_schema = database() and event_name = 'event_35981' and +on_completion = 'NOT PRESERVE'; +count(*) +1 +drop event event_35981; +create event event_35981 on schedule every 1 hour starts current_timestamp +on completion not preserve +do +select 1; +Warnings: +Warning 1105 Event scheduler is switched off, use SET GLOBAL event_scheduler=ON to enable it. +alter event event_35981 on schedule every 1 hour starts '1999-01-01 00:00:00' + ends '1999-01-02 00:00:00'; +ERROR HY000: Event execution time is in the past and ON COMPLETION NOT PRESERVE is set. The event was not changed. Specify a time in the future +drop event event_35981; +create event event_35981 on schedule every 1 hour starts current_timestamp +on completion not preserve +do +select 1; +Warnings: +Warning 1105 Event scheduler is switched off, use SET GLOBAL event_scheduler=ON to enable it. +alter event event_35981 on schedule every 1 hour starts '1999-01-01 00:00:00' + ends '1999-01-02 00:00:00' on completion preserve; +Warnings: +Note 1544 Event execution time is in the past. Event has been disabled +drop event event_35981; +create event event_35981 on schedule every 1 hour starts current_timestamp +on completion preserve +do +select 1; +Warnings: +Warning 1105 Event scheduler is switched off, use SET GLOBAL event_scheduler=ON to enable it. +alter event event_35981 on schedule every 1 hour starts '1999-01-01 00:00:00' + ends '1999-01-02 00:00:00'; +Warnings: +Note 1544 Event execution time is in the past. Event has been disabled +alter event event_35981 on schedule every 1 hour starts '1999-01-01 00:00:00' + ends '1999-01-02 00:00:00' on completion not preserve; +ERROR HY000: Event execution time is in the past and ON COMPLETION NOT PRESERVE is set. The event was not changed. Specify a time in the future +alter event event_35981 on schedule every 1 hour starts '1999-01-01 00:00:00' + ends '1999-01-02 00:00:00' on completion preserve; +Warnings: +Note 1544 Event execution time is in the past. Event has been disabled +drop event event_35981; +drop database events_test; diff --git a/mysql-test/suite/events/events_2.test b/mysql-test/suite/events/events_2.test new file mode 100644 index 00000000..cd0d908b --- /dev/null +++ b/mysql-test/suite/events/events_2.test @@ -0,0 +1,528 @@ +# changes 2008-02-20 hhunger splitted events.test into events_1 and events_2 +# +# Can't test with embedded server that doesn't support grants +-- source include/not_embedded.inc + +--disable_service_connection + +set sql_mode=""; +--source include/default_charset.inc + +--disable_warnings +drop database if exists events_test; +--enable_warnings +create database events_test; +use events_test; + +# +# mysql.event intact checking end +# + +create event e_26 on schedule at '2037-01-01 00:00:00' disable do set @a = 5; +select db, name, body, definer, convert_tz(execute_at, 'UTC', 'SYSTEM'), on_completion from mysql.event; +drop event e_26; +--error ER_WRONG_VALUE +create event e_26 on schedule at NULL disable do set @a = 5; +--error ER_WRONG_VALUE +create event e_26 on schedule at 'definitely not a datetime' disable do set @a = 5; + +set names utf8; +create event задачка on schedule every 123 minute starts now() ends now() + interval 1 month do select 1; +drop event задачка; + +--echo "DISABLE the scheduler. Testing that it does not work when the variable is 0" +set global event_scheduler=off; +select definer, name, db from mysql.event; +select get_lock("test_lock1", 20); +create event закачка on schedule every 10 hour do select get_lock("test_lock1", 20); +--echo "Should return 1 row" +select definer, name, db from mysql.event; + +--echo "Should be only 0 process" +select /*1*/ user, host, db, command, state, info + from information_schema.processlist + where (user='event_scheduler') + order by info; +select release_lock("test_lock1"); +drop event закачка; +--echo "Should have 0 events" +select count(*) from mysql.event; + +# +# +# +--echo "ENABLE the scheduler and get a lock" +set global event_scheduler=on; +select get_lock("test_lock2", 20); +--echo "Create an event which tries to acquire a mutex. The event locks on the mutex" +create event закачка on schedule every 10 hour do select get_lock("test_lock2", 20); + +--echo "Should have only 2 processes: the scheduler and the locked event" +let $wait_condition= select count(*) = 2 from information_schema.processlist + where ( (state like 'User lock%' AND info like 'select get_lock%') + OR (command='Daemon' AND user='event_scheduler' AND + state = 'Waiting for next activation')); +--source include/wait_condition.inc + +select /*2*/ user, host, db, command, state, info + from information_schema.processlist + where (info like "select get_lock%" OR user='event_scheduler') + order by info; +--echo "Release the mutex, the event worker should finish." +select release_lock("test_lock2"); +drop event закачка; + +# Wait for get_lock("test_lock2") to complete, +# to avoid polluting the next test information_schema.processlist +let $wait_condition= select count(*) = 0 from information_schema.processlist + where info='select get_lock("test_lock2", 20)'; +--source include/wait_condition.inc + + +## +## 1. get a lock +## 2. create an event +## 3. sleep so it has time to start +## 4. should appear in processlist +## 5. kill the scheduler, it will wait for the child to stop +## 6. both processes should be there on show processlist +## 7. release the lock and sleep, both scheduler and child should end +set global event_scheduler=1; +select get_lock("test_lock2_1", 20); +create event закачка21 on schedule every 10 hour do select get_lock("test_lock2_1", 20); + +--echo "Should have only 2 processes: the scheduler and the locked event" +let $wait_condition= select count(*) = 2 from information_schema.processlist + where ( (state like 'User lock%' AND info like 'select get_lock%') + OR (command='Daemon' AND user='event_scheduler' AND + state = 'Waiting for next activation')); +--source include/wait_condition.inc + +select /*3*/ user, host, db, command, state, info + from information_schema.processlist + where (info like "select get_lock%" OR user='event_scheduler') + order by info; + +set global event_scheduler=off; + +let $wait_condition= select count(*) =1 from information_schema.processlist + where (info like "select get_lock%" OR user='event_scheduler'); +--source include/wait_condition.inc + +--echo "Should have only our process now:" +select /*4*/ user, host, db, command, state, info + from information_schema.processlist + where (info like "select get_lock%" OR user='event_scheduler') + order by info; +select release_lock("test_lock2_1"); +drop event закачка21; +let $wait_condition= + select count(*) = 0 from information_schema.processlist + where db='events_test' and command = 'Connect' and user=current_user(); +--source include/wait_condition.inc + +#### +# Bug #16410 Events: CREATE EVENT is legal in a CREATE TRIGGER statement +# +create table t_16 (s1 int); +--error ER_EVENT_RECURSION_FORBIDDEN +create trigger t_16_bi before insert on t_16 for each row create event e_16 on schedule every 1 second do set @a=5; +drop table t_16; +# +# end of test case +#### + +# +# START: BUG #17453: Creating Event crash the server +# +create event white_space +on schedule every 10 hour +disable +do +select 1; +select event_schema, event_name, definer, event_definition from information_schema.events where event_name='white_space'; +drop event white_space; +create event white_space on schedule every 10 hour disable do + +select 2; +select event_schema, event_name, definer, event_definition from information_schema.events where event_name='white_space'; +drop event white_space; +create event white_space on schedule every 10 hour disable do select 3; +select event_schema, event_name, definer, event_definition from information_schema.events where event_name='white_space'; +drop event white_space; +# +# END: BUG #17453: Creating Event crash the server +# + +# +# Bug#17403 "Events: packets out of order with show create event" +# +create event e1 on schedule every 1 year do set @a = 5; +create table t1 (s1 int); +--error ER_SP_NO_RETSET +create trigger t1_ai after insert on t1 for each row show create event e1; +drop table t1; +drop event e1; + +##set global event_scheduler=1; +##select get_lock("test_lock3", 20); +##create event закачка on schedule every 10 hour do select get_lock("test_lock3", 20); +##select sleep(2); +##select /*5*/ user, host, db, command, state, info from information_schema.processlist where info is null or info not like '%processlist%' order by info; +##drop event закачка; +##select release_lock("test_lock3"); + +# +# test with very often occuring event +# (disabled for now, locks) +##select get_lock("test_lock4", 20); +##create event закачка4 on schedule every 1 second do select get_lock("test_lock4", 20); +##select sleep(3); +##select /*6*/ user, host, db, command, state, info from information_schema.processlist where info is null or info not like '%processlist%' order by info; +##drop event закачка4; +##select release_lock("test_lock4"); + +##set global event_scheduler=off; +##select sleep(2); +##--replace_column 1 # 6 # +##show processlist; +##select count(*) from mysql.event; + +# +# Test wrong syntax +# + +--error ER_WRONG_DB_NAME +SHOW EVENTS FROM aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa; +--error ER_WRONG_DB_NAME +SHOW EVENTS FROM ``; + +SHOW EVENTS FROM `events\\test`; +# +# A check for events SQL under LOCK TABLES and in pre-locked mode. +# +--echo +--echo LOCK TABLES mode. +--echo +# +# SHOW CREATE EVENT and INFORMATION_SCHEMA.events are available and +# cause an implicit lock/unlock of mysql.event table, regardless of the +# currently locked tables. +# +create table t1 (a int); +create event e1 on schedule every 10 hour do select 1; +# +lock table t1 read; +# +--replace_regex /STARTS '[^']+'/STARTS '#'/ +show create event e1; +select event_name from information_schema.events; +--error ER_LOCK_OR_ACTIVE_TRANSACTION +create event e2 on schedule every 10 hour do select 1; +--error ER_LOCK_OR_ACTIVE_TRANSACTION +alter event e2 disable; +--error ER_LOCK_OR_ACTIVE_TRANSACTION +alter event e2 rename to e3; +--error ER_LOCK_OR_ACTIVE_TRANSACTION +drop event e2; +--error ER_LOCK_OR_ACTIVE_TRANSACTION +drop event e1; +unlock tables; +# +lock table t1 write; +# +--replace_regex /STARTS '[^']+'/STARTS '#'/ +show create event e1; +select event_name from information_schema.events; +--error ER_LOCK_OR_ACTIVE_TRANSACTION +create event e2 on schedule every 10 hour do select 1; +--error ER_LOCK_OR_ACTIVE_TRANSACTION +alter event e2 disable; +--error ER_LOCK_OR_ACTIVE_TRANSACTION +alter event e2 rename to e3; +--error ER_LOCK_OR_ACTIVE_TRANSACTION +drop event e2; +--error ER_LOCK_OR_ACTIVE_TRANSACTION +drop event e1; +unlock tables; +# +lock table t1 read, mysql.event read; +# +--replace_regex /STARTS '[^']+'/STARTS '#'/ +show create event e1; +select event_name from information_schema.events; +--error ER_LOCK_OR_ACTIVE_TRANSACTION +create event e2 on schedule every 10 hour do select 1; +--error ER_LOCK_OR_ACTIVE_TRANSACTION +alter event e2 disable; +--error ER_LOCK_OR_ACTIVE_TRANSACTION +alter event e2 rename to e3; +--error ER_LOCK_OR_ACTIVE_TRANSACTION +drop event e2; +--error ER_LOCK_OR_ACTIVE_TRANSACTION +drop event e1; +unlock tables; +# +lock table t1 write, mysql.event read; +# +--replace_regex /STARTS '[^']+'/STARTS '#'/ +show create event e1; +select event_name from information_schema.events; +--error ER_LOCK_OR_ACTIVE_TRANSACTION +create event e2 on schedule every 10 hour do select 1; +--error ER_LOCK_OR_ACTIVE_TRANSACTION +alter event e2 disable; +--error ER_LOCK_OR_ACTIVE_TRANSACTION +alter event e2 rename to e3; +--error ER_LOCK_OR_ACTIVE_TRANSACTION +drop event e2; +--error ER_LOCK_OR_ACTIVE_TRANSACTION +drop event e1; +unlock tables; +# +--error ER_WRONG_LOCK_OF_SYSTEM_TABLE +lock table t1 read, mysql.event write; +# +--error ER_WRONG_LOCK_OF_SYSTEM_TABLE +lock table t1 write, mysql.event write; +# +lock table mysql.event write; +--replace_regex /STARTS '[^']+'/STARTS '#'/ +show create event e1; +select event_name from information_schema.events; +--error ER_LOCK_OR_ACTIVE_TRANSACTION +create event e2 on schedule every 10 hour do select 1; +--error ER_LOCK_OR_ACTIVE_TRANSACTION +alter event e2 disable; +--error ER_LOCK_OR_ACTIVE_TRANSACTION +alter event e2 rename to e3; +--error ER_LOCK_OR_ACTIVE_TRANSACTION +drop event e3; +--error ER_LOCK_OR_ACTIVE_TRANSACTION +drop event e1; +unlock tables; +drop event e1; +--echo Make sure we have left no events +select event_name from information_schema.events; +--echo +--echo Events in sub-statements, events and prelocking +--echo +--echo +create event e1 on schedule every 10 hour do select 1; +delimiter |; +--error ER_SP_NO_RETSET +create function f1() returns int +begin + show create event e1; + return 1; +end| +--error ER_SP_NO_RETSET +create trigger trg before insert on t1 for each row +begin + show create event e1; +end| +--error ER_SP_NO_RETSET +create function f1() returns int +begin + select event_name from information_schema.events; + return 1; +end| +--error ER_SP_NO_RETSET +create trigger trg before insert on t1 for each row +begin + select event_name from information_schema.events; +end| +--error ER_EVENT_RECURSION_FORBIDDEN +create function f1() returns int +begin + create event e2 on schedule every 10 hour do select 1; + return 1; +end| +--error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG +create function f1() returns int +begin + alter event e1 rename to e2; + return 1; +end| +--error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG +create function f1() returns int +begin + drop event e2; + return 1; +end| +--echo ---------------------------------------------------------------------- +create trigger trg before insert on t1 for each row +begin + set new.a= f1(); +end| +create function f1() returns int +begin + call p1(); + return 0; +end| +create procedure p1() +begin + select event_name from information_schema.events; +end| +--error ER_SP_NO_RETSET +insert into t1 (a) values (1)| +drop procedure p1| +create procedure p1() +begin + show create event e1; +end| +--error ER_SP_NO_RETSET +insert into t1 (a) values (1)| +drop procedure p1| +create procedure p1() +begin + create temporary table tmp select event_name from information_schema.events; +end| +--echo expected to work, since we redirect the output into a tmp table +insert into t1 (a) values (1)| +select * from tmp| +drop temporary table tmp| +drop procedure p1| +create procedure p1() +begin + alter event e1 rename to e2; +end| +--error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG +insert into t1 (a) values (1)| +drop procedure p1| +create procedure p1() +begin + drop event e1; +end| +--error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG +insert into t1 (a) values (1)| +drop table t1| +drop event e1| +delimiter ;| + +# +# Bug#21432 Database/Table name limited to 64 bytes, not chars, problems with multi-byte +# +set names utf8; +create event имя_события_в_кодировке_утф8_длиной_больше_чем_48 on schedule every 2 year do select 1; +select EVENT_NAME from information_schema.events +where event_schema='test'; +drop event имя_события_в_кодировке_утф8_длиной_больше_чем_48; +--error 1059 +create event +очень_очень_очень_очень_очень_очень_очень_очень_длинная_строка_66 +on schedule every 2 year do select 1; + +# +# Bug#35981: ALTER EVENT causes the server to change the PRESERVE option. +# + +create event event_35981 on schedule every 6 month on completion preserve +disable +do + select 1; + +echo The following SELECTs should all give 1; + +# show current ON_COMPLETION +select count(*) from information_schema.events +where event_schema = database() and event_name = 'event_35981' and + on_completion = 'PRESERVE'; + +# show ON_COMPLETION remains "PRESERVE" when not given in ALTER EVENT +alter event event_35981 enable; +select count(*) from information_schema.events +where event_schema = database() and event_name = 'event_35981' and + on_completion = 'PRESERVE'; + +# show we can change ON_COMPLETION +alter event event_35981 on completion not preserve; +select count(*) from information_schema.events +where event_schema = database() and event_name = 'event_35981' and + on_completion = 'NOT PRESERVE'; + +# show ON_COMPLETION remains "NOT PRESERVE" when not given in ALTER EVENT +alter event event_35981 disable; +select count(*) from information_schema.events +where event_schema = database() and event_name = 'event_35981' and + on_completion = 'NOT PRESERVE'; + +# show we can change ON_COMPLETION +alter event event_35981 on completion preserve; +select count(*) from information_schema.events +where event_schema = database() and event_name = 'event_35981' and + on_completion = 'PRESERVE'; + + +drop event event_35981; + +create event event_35981 on schedule every 6 month disable +do + select 1; + +# show that the defaults for CREATE EVENT are still correct (NOT PRESERVE) +select count(*) from information_schema.events +where event_schema = database() and event_name = 'event_35981' and + on_completion = 'NOT PRESERVE'; + +drop event event_35981; + + +# show that backdating doesn't break + +create event event_35981 on schedule every 1 hour starts current_timestamp + on completion not preserve +do + select 1; + +# should fail thanks to above's NOT PRESERVE +--error ER_EVENT_CANNOT_ALTER_IN_THE_PAST +alter event event_35981 on schedule every 1 hour starts '1999-01-01 00:00:00' + ends '1999-01-02 00:00:00'; + +drop event event_35981; + +create event event_35981 on schedule every 1 hour starts current_timestamp + on completion not preserve +do + select 1; + +# succeed with warning +alter event event_35981 on schedule every 1 hour starts '1999-01-01 00:00:00' + ends '1999-01-02 00:00:00' on completion preserve; + +drop event event_35981; + + + +create event event_35981 on schedule every 1 hour starts current_timestamp + on completion preserve +do + select 1; + +# this should succeed thanks to above PRESERVE! give a warning though. +alter event event_35981 on schedule every 1 hour starts '1999-01-01 00:00:00' + ends '1999-01-02 00:00:00'; + +# this should fail, as the event would have passed already +--error ER_EVENT_CANNOT_ALTER_IN_THE_PAST +alter event event_35981 on schedule every 1 hour starts '1999-01-01 00:00:00' + ends '1999-01-02 00:00:00' on completion not preserve; + +# should succeed giving a warning +alter event event_35981 on schedule every 1 hour starts '1999-01-01 00:00:00' + ends '1999-01-02 00:00:00' on completion preserve; + +drop event event_35981; + +# +# End of tests +# + +let $wait_condition= + select count(*) = 0 from information_schema.processlist + where db='events_test' and command = 'Connect' and user=current_user(); +--source include/wait_condition.inc + +drop database events_test; +--enable_service_connection diff --git a/mysql-test/suite/events/events_bugs-master.opt b/mysql-test/suite/events/events_bugs-master.opt new file mode 100644 index 00000000..f93413a6 --- /dev/null +++ b/mysql-test/suite/events/events_bugs-master.opt @@ -0,0 +1 @@ +--event-scheduler diff --git a/mysql-test/suite/events/events_bugs.result b/mysql-test/suite/events/events_bugs.result new file mode 100644 index 00000000..ee4411e6 --- /dev/null +++ b/mysql-test/suite/events/events_bugs.result @@ -0,0 +1,872 @@ +call mtr.add_suppression('Event Scheduler:'); +SET SQL_MODE=""; +drop database if exists events_test; +drop database if exists mysqltest_db1; +drop database if exists mysqltest_db2; +set collation_server=latin1_swedish_ci; +create database events_test; +use events_test; +set @concurrent_insert= @@global.concurrent_insert; +set @@global.concurrent_insert = 0; +select * from information_schema.global_variables where variable_name like 'event_scheduler'; +VARIABLE_NAME VARIABLE_VALUE +EVENT_SCHEDULER ON +SET GLOBAL event_scheduler = 'OFF'; +CREATE EVENT lower_case ON SCHEDULE EVERY 1 MINUTE DO SELECT 1; +Warnings: +Warning 1105 Event scheduler is switched off, use SET GLOBAL event_scheduler=ON to enable it. +CREATE EVENT Lower_case ON SCHEDULE EVERY 2 MINUTE DO SELECT 2; +ERROR HY000: Event 'Lower_case' already exists +DROP EVENT Lower_case; +SET NAMES cp1251; +CREATE EVENT __1251 ON SCHEDULE EVERY 1 YEAR DO SELECT 100; +Warnings: +Warning 1105 Event scheduler is switched off, use SET GLOBAL event_scheduler=ON to enable it. +CREATE EVENT __1251 ON SCHEDULE EVERY 2 YEAR DO SELECT 200; +ERROR HY000: Event '__1251' already exists +DROP EVENT __1251; +SET NAMES utf8; +CREATE EVENT долен_регистър_утф8 ON SCHEDULE EVERY 3 YEAR DO SELECT 300; +Warnings: +Warning 1105 Event scheduler is switched off, use SET GLOBAL event_scheduler=ON to enable it. +CREATE EVENT ДОЛЕН_регистър_утф8 ON SCHEDULE EVERY 4 YEAR DO SELECT 400; +ERROR HY000: Event 'ДОЛЕН_регистър_утф8' already exists +DROP EVENT ДОЛЕН_регистър_утф8; +SET NAMES latin1; +set @a=3; +CREATE PROCEDURE p_16 () CREATE EVENT e_16 ON SCHEDULE EVERY @a SECOND DO SET @a=5; +ERROR HY000: Recursion of EVENT DDL statements is forbidden when body is present +create event e_55 on schedule at 99990101000000 do drop table t; +ERROR HY000: Incorrect AT value: '99990101000000' +create event e_55 on schedule every 10 hour starts 99990101000000 do drop table t; +ERROR HY000: Incorrect STARTS value: '99990101000000' +create event e_55 on schedule every 10 minute ends 99990101000000 do drop table t; +ERROR HY000: ENDS is either invalid or before STARTS +create event e_55 on schedule at 10000101000000 do drop table t; +ERROR HY000: Incorrect AT value: '10000101000000' +create event e_55 on schedule at 20000101000000 do drop table t; +Warnings: +Note 1588 Event execution time is in the past and ON COMPLETION NOT PRESERVE is set. The event was dropped immediately after creation +show events; +Db Name Definer Time zone Type Execute at Interval value Interval field Starts Ends Status Originator character_set_client collation_connection Database Collation +create event e_55 on schedule at 20200101000000 starts 10000101000000 do drop table t; +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'starts 10000101000000 do drop table t' at line 1 +create event e_55 on schedule at 20200101000000 ends 10000101000000 do drop table t; +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'ends 10000101000000 do drop table t' at line 1 +create event e_55 on schedule at 20200101000000 starts 10000101000000 ends 10000101000000 do drop table t; +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'starts 10000101000000 ends 10000101000000 do drop table t' at line 1 +create event e_55 on schedule every 10 hour starts 10000101000000 do drop table t; +ERROR HY000: Incorrect STARTS value: '10000101000000' +set global event_scheduler=off; +delete from mysql.event; +set global event_scheduler= on; +set @old_sql_mode:=@@sql_mode; +set sql_mode=ansi; +select get_lock('test_bug16407', 60); +get_lock('test_bug16407', 60) +1 +create event e_16407 on schedule every 60 second do +begin +select get_lock('test_bug16407', 60); +end| +"Now if everything is fine the event has compiled and is locked" +select /*1*/ user, host, db, info from information_schema.processlist +where state = 'User lock' and info = 'select get_lock(\'test_bug16407\', 60)'; +user host db info +root localhost events_test select get_lock('test_bug16407', 60) +select release_lock('test_bug16407'); +release_lock('test_bug16407') +1 +set global event_scheduler= off; +select event_schema, event_name, sql_mode from information_schema.events order by event_schema, event_name; +event_schema event_name sql_mode +events_test e_16407 REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ANSI +"Let's check whether we change the sql_mode on ALTER EVENT" +set sql_mode='traditional'; +alter event e_16407 do select 1; +select event_schema, event_name, sql_mode from information_schema.events order by event_schema, event_name; +event_schema event_name sql_mode +events_test e_16407 STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION +drop event e_16407; +set sql_mode="ansi"; +select get_lock('ee_16407_2', 60); +get_lock('ee_16407_2', 60) +1 +set global event_scheduler= 1; +"Another sql_mode test" +set sql_mode="traditional"; +create table events_smode_test(ev_name char(10), a date); +"This should never insert something" +create event ee_16407_2 on schedule every 60 second do +begin +select get_lock('ee_16407_2', 60); /*ee_16407_2*/ +select release_lock('ee_16407_2'); +insert into events_test.events_smode_test values('ee_16407_2','1980-19-02'); +end| +insert into events_test.events_smode_test values ('test','1980-19-02')| +ERROR 22007: Incorrect date value: '1980-19-02' for column `events_test`.`events_smode_test`.`a` at row 1 +"This is ok" +create event ee_16407_3 on schedule every 60 second do +begin +select get_lock('ee_16407_2', 60); /*ee_16407_3*/ +select release_lock('ee_16407_2'); +insert into events_test.events_smode_test values ('ee_16407_3','1980-02-19'); +insert into events_test.events_smode_test values ('ee_16407_3','1980-02-29'); +end| +set sql_mode=""| +"This will insert rows but they will be truncated" +create event ee_16407_4 on schedule every 60 second do +begin +select get_lock('ee_16407_2', 60); /*ee_16407_4*/ +select release_lock('ee_16407_2'); +insert into events_test.events_smode_test values ('ee_16407_4','10-11-1956'); +end| +select event_schema, event_name, sql_mode from information_schema.events order by event_schema, event_name; +event_schema event_name sql_mode +events_test ee_16407_2 STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION +events_test ee_16407_3 STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION +events_test ee_16407_4 +select /*2*/ user, host, db, info from information_schema.processlist +where state = 'User lock' and info = 'select get_lock(\'ee_16407_2\', 60)'; +user host db info +root localhost events_test select get_lock('ee_16407_2', 60) +root localhost events_test select get_lock('ee_16407_2', 60) +root localhost events_test select get_lock('ee_16407_2', 60) +select release_lock('ee_16407_2'); +release_lock('ee_16407_2') +1 +select /*3*/ user, host, db, info from information_schema.processlist +where state = 'User lock' and info = 'select get_lock(\'ee_16407_2\', 60)'; +user host db info +set global event_scheduler= off; +select * from events_test.events_smode_test order by ev_name, a; +ev_name a +ee_16407_3 1980-02-19 +ee_16407_3 1980-02-29 +ee_16407_4 0000-00-00 +"OK, last check before we drop them" +select event_schema, event_name, sql_mode from information_schema.events order by event_schema, event_name; +event_schema event_name sql_mode +events_test ee_16407_2 STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION +events_test ee_16407_3 STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION +events_test ee_16407_4 +drop event ee_16407_2; +drop event ee_16407_3; +drop event ee_16407_4; +"And now one last test regarding sql_mode and call of SP from an event" +delete from events_test.events_smode_test; +set sql_mode='ansi'; +select get_lock('ee_16407_5', 60); +get_lock('ee_16407_5', 60) +1 +set global event_scheduler= on; +set sql_mode='traditional'; +create procedure ee_16407_5_pendant() begin insert into events_test.events_smode_test values('ee_16407_5','2001-02-29'); end| +create procedure ee_16407_6_pendant() begin insert into events_test.events_smode_test values('ee_16407_6','2004-02-29'); end| +create event ee_16407_5 on schedule every 60 second do +begin +select get_lock('ee_16407_5', 60); /*ee_16407_5*/ +select release_lock('ee_16407_5'); +call events_test.ee_16407_5_pendant(); +end| +create event ee_16407_6 on schedule every 60 second do +begin +select get_lock('ee_16407_5', 60); /*ee_16407_6*/ +select release_lock('ee_16407_5'); +call events_test.ee_16407_6_pendant(); +end| +"Should have 2 locked processes" +select /*4*/ user, host, db, info from information_schema.processlist +where state = 'User lock' and info = 'select get_lock(\'ee_16407_5\', 60)'; +user host db info +root localhost events_test select get_lock('ee_16407_5', 60) +root localhost events_test select get_lock('ee_16407_5', 60) +select release_lock('ee_16407_5'); +release_lock('ee_16407_5') +1 +"Should have 0 processes locked" +select /*5*/ user, host, db, info from information_schema.processlist +where state = 'User lock' and info = 'select get_lock(\'ee_16407_5\', 60)'; +user host db info +select * from events_test.events_smode_test order by ev_name, a; +ev_name a +ee_16407_6 2004-02-29 +"And here we check one more time before we drop the events" +select event_schema, event_name, sql_mode from information_schema.events order by event_schema, event_name; +event_schema event_name sql_mode +events_test ee_16407_5 STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION +events_test ee_16407_6 STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION +drop event ee_16407_5; +drop event ee_16407_6; +drop procedure ee_16407_5_pendant; +drop procedure ee_16407_6_pendant; +set global event_scheduler= off; +drop table events_smode_test; +set sql_mode=@old_sql_mode; +set global event_scheduler=off; +delete from mysql.user where User like 'mysqltest_%'; +delete from mysql.db where User like 'mysqltest_%'; +flush privileges; +drop database if exists mysqltest_db1; +create user mysqltest_user1@localhost; +create database mysqltest_db1; +grant event on events_test.* to mysqltest_user1@localhost; +connect conn2,localhost,mysqltest_user1,,events_test; +create event mysqltest_user1 on schedule every 10 second do select 42; +Warnings: +Warning 1105 Event scheduler is switched off, use SET GLOBAL event_scheduler=ON to enable it. +alter event mysqltest_user1 rename to mysqltest_db1.mysqltest_user1; +ERROR 42000: Access denied for user 'mysqltest_user1'@'localhost' to database 'mysqltest_db1' +"Let's test now rename when there is no select DB" +disconnect conn2; +connect conn2,localhost,mysqltest_user1,,*NO-ONE*; +select database(); +database() +NULL +alter event events_test.mysqltest_user1 rename to mysqltest_user1; +ERROR 3D000: No database selected +select event_schema, event_name, definer, event_type, status from information_schema.events; +event_schema event_name definer event_type status +events_test mysqltest_user1 mysqltest_user1@localhost RECURRING ENABLED +drop event events_test.mysqltest_user1; +disconnect conn2; +connection default; +drop user mysqltest_user1@localhost; +drop database mysqltest_db1; +create event e_53 on schedule at (select s1 from ttx) do drop table t; +ERROR 42000: CREATE/ALTER EVENT does not support subqueries or stored functions +create event e_53 on schedule every (select s1 from ttx) second do drop table t; +ERROR 42000: CREATE/ALTER EVENT does not support subqueries or stored functions +create event e_53 on schedule every 5 second starts (select s1 from ttx) do drop table t; +ERROR 42000: CREATE/ALTER EVENT does not support subqueries or stored functions +create event e_53 on schedule every 5 second ends (select s1 from ttx) do drop table t; +ERROR 42000: CREATE/ALTER EVENT does not support subqueries or stored functions +drop event if exists e_16; +drop procedure if exists p_16; +create event e_16 on schedule every 1 second do set @a=5; +Warnings: +Warning 1105 Event scheduler is switched off, use SET GLOBAL event_scheduler=ON to enable it. +create procedure p_16 () alter event e_16 on schedule every @a second; +set @a = null; +call p_16(); +ERROR HY000: Incorrect INTERVAL value: 'NULL' +call p_16(); +ERROR HY000: Incorrect INTERVAL value: 'NULL' +set @a= 6; +call p_16(); +drop procedure p_16; +drop event e_16; +drop function if exists f22830; +drop event if exists e22830; +drop event if exists e22830_1; +drop event if exists e22830_2; +drop event if exists e22830_3; +drop event if exists e22830_4; +drop table if exists t1; +drop table if exists t2; +create table t1 (a int); +insert into t1 values (2); +create table t2 (a char(20)); +insert into t2 values ("e22830_1"); +create function f22830 () returns int return 5; +select get_lock('ee_22830', 60); +get_lock('ee_22830', 60) +1 +set global event_scheduler=on; +create procedure p22830_wait() +begin +select get_lock('ee_22830', 60); +select release_lock('ee_22830'); +end| +create event e22830 on schedule every f22830() second do +begin +call p22830_wait(); +select 123; +end| +ERROR 42000: CREATE/ALTER EVENT does not support subqueries or stored functions +create event e22830_1 on schedule every 1 hour do +begin +call p22830_wait(); +alter event e22830_1 on schedule every (select 8 from dual) hour; +end| +create event e22830_2 on schedule every 1 hour do +begin +call p22830_wait(); +alter event e22830_2 on schedule every (select 8 from t1) hour; +end| +create event e22830_3 on schedule every 1 hour do +begin +call p22830_wait(); +alter event e22830_3 on schedule every f22830() hour; +end| +create event e22830_4 on schedule every 1 hour do +begin +call p22830_wait(); +alter event e22830_4 on schedule every (select f22830() from dual) hour; +end| +"All events should be blocked in get_lock()" +select event_name, event_definition, interval_value, interval_field from information_schema.events order by event_name; +event_name event_definition interval_value interval_field +e22830_1 begin +call p22830_wait(); +alter event e22830_1 on schedule every (select 8 from dual) hour; +end 1 HOUR +e22830_2 begin +call p22830_wait(); +alter event e22830_2 on schedule every (select 8 from t1) hour; +end 1 HOUR +e22830_3 begin +call p22830_wait(); +alter event e22830_3 on schedule every f22830() hour; +end 1 HOUR +e22830_4 begin +call p22830_wait(); +alter event e22830_4 on schedule every (select f22830() from dual) hour; +end 1 HOUR +select release_lock('ee_22830'); +release_lock('ee_22830') +1 +set global event_scheduler=off; +select event_name, event_definition, interval_value, interval_field from information_schema.events order by event_name; +event_name event_definition interval_value interval_field +e22830_1 begin +call p22830_wait(); +alter event e22830_1 on schedule every (select 8 from dual) hour; +end 8 HOUR +e22830_2 begin +call p22830_wait(); +alter event e22830_2 on schedule every (select 8 from t1) hour; +end 1 HOUR +e22830_3 begin +call p22830_wait(); +alter event e22830_3 on schedule every f22830() hour; +end 1 HOUR +e22830_4 begin +call p22830_wait(); +alter event e22830_4 on schedule every (select f22830() from dual) hour; +end 1 HOUR +drop procedure p22830_wait; +drop function f22830; +drop event (select a from t2); +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '(select a from t2)' at line 1 +drop event e22830_1; +drop event e22830_2; +drop event e22830_3; +drop event e22830_4; +drop table t1; +drop table t2; +DROP USER mysqltest_u1@localhost; +CREATE USER mysqltest_u1@localhost; +GRANT EVENT ON events_test.* TO mysqltest_u1@localhost; +CREATE EVENT e1 ON SCHEDULE EVERY 1 DAY DO SELECT 1; +Warnings: +Warning 1105 Event scheduler is switched off, use SET GLOBAL event_scheduler=ON to enable it. +SELECT event_name, definer FROM INFORMATION_SCHEMA.EVENTS; +event_name definer +e1 root@localhost +DROP EVENT e1; +CREATE DEFINER=CURRENT_USER EVENT e1 ON SCHEDULE EVERY 1 DAY DO SELECT 1; +Warnings: +Warning 1105 Event scheduler is switched off, use SET GLOBAL event_scheduler=ON to enable it. +SELECT event_name, definer FROM INFORMATION_SCHEMA.EVENTS; +event_name definer +e1 root@localhost +ALTER DEFINER=mysqltest_u1@localhost EVENT e1 ON SCHEDULE EVERY 1 HOUR; +SELECT event_name, definer FROM INFORMATION_SCHEMA.EVENTS; +event_name definer +e1 mysqltest_u1@localhost +DROP EVENT e1; +CREATE DEFINER=CURRENT_USER() EVENT e1 ON SCHEDULE EVERY 1 DAY DO SELECT 1; +Warnings: +Warning 1105 Event scheduler is switched off, use SET GLOBAL event_scheduler=ON to enable it. +SELECT event_name, definer FROM INFORMATION_SCHEMA.EVENTS; +event_name definer +e1 root@localhost +DROP EVENT e1; +CREATE DEFINER=mysqltest_u1@localhost EVENT e1 ON SCHEDULE EVERY 1 DAY DO +SELECT 1; +Warnings: +Warning 1105 Event scheduler is switched off, use SET GLOBAL event_scheduler=ON to enable it. +SELECT event_name, definer FROM INFORMATION_SCHEMA.EVENTS; +event_name definer +e1 mysqltest_u1@localhost +DROP EVENT e1; +connect conn1, localhost, mysqltest_u1, , events_test; +CREATE EVENT e1 ON SCHEDULE EVERY 1 DAY DO SELECT 1; +Warnings: +Warning 1105 Event scheduler is switched off, use SET GLOBAL event_scheduler=ON to enable it. +SELECT event_name, definer FROM INFORMATION_SCHEMA.EVENTS; +event_name definer +e1 mysqltest_u1@localhost +DROP EVENT e1; +CREATE DEFINER=CURRENT_USER EVENT e1 ON SCHEDULE EVERY 1 DAY DO SELECT 1; +Warnings: +Warning 1105 Event scheduler is switched off, use SET GLOBAL event_scheduler=ON to enable it. +SELECT event_name, definer FROM INFORMATION_SCHEMA.EVENTS; +event_name definer +e1 mysqltest_u1@localhost +ALTER DEFINER=root@localhost EVENT e1 ON SCHEDULE EVERY 1 HOUR; +ERROR 42000: Access denied; you need (at least one of) the SET USER privilege(s) for this operation +SELECT event_name, definer FROM INFORMATION_SCHEMA.EVENTS; +event_name definer +e1 mysqltest_u1@localhost +DROP EVENT e1; +CREATE DEFINER=CURRENT_USER() EVENT e1 ON SCHEDULE EVERY 1 DAY DO SELECT 1; +Warnings: +Warning 1105 Event scheduler is switched off, use SET GLOBAL event_scheduler=ON to enable it. +SELECT event_name, definer FROM INFORMATION_SCHEMA.EVENTS; +event_name definer +e1 mysqltest_u1@localhost +DROP EVENT e1; +CREATE DEFINER=root@localhost EVENT e1 ON SCHEDULE EVERY 1 DAY DO SELECT 1; +ERROR 42000: Access denied; you need (at least one of) the SET USER privilege(s) for this operation +DROP EVENT e1; +ERROR HY000: Unknown event 'e1' +disconnect conn1; +connection default; +DROP USER mysqltest_u1@localhost; +SET GLOBAL EVENT_SCHEDULER= OFF; +SET @save_time_zone= @@TIME_ZONE; +SET TIME_ZONE= '+00:00'; +SET TIMESTAMP= UNIX_TIMESTAMP('2005-12-31 23:58:59'); +CREATE EVENT e1 ON SCHEDULE EVERY 1 DAY DO SELECT 1; +Warnings: +Warning 1105 Event scheduler is switched off, use SET GLOBAL event_scheduler=ON to enable it. +SHOW EVENTS; +Db Name Definer Time zone Type Execute at Interval value Interval field Starts Ends Status Originator character_set_client collation_connection Database Collation +events_test e1 root@localhost +00:00 RECURRING NULL 1 DAY 2005-12-31 23:58:59 NULL ENABLED 1 latin1 latin1_swedish_ci latin1_swedish_ci +SET TIME_ZONE= '-01:00'; +ALTER EVENT e1 ON SCHEDULE EVERY 1 DAY STARTS '2000-01-01 00:00:00'; +SHOW EVENTS; +Db Name Definer Time zone Type Execute at Interval value Interval field Starts Ends Status Originator character_set_client collation_connection Database Collation +events_test e1 root@localhost -01:00 RECURRING NULL 1 DAY 2000-01-01 00:00:00 NULL ENABLED 1 latin1 latin1_swedish_ci latin1_swedish_ci +SET TIME_ZONE= '+02:00'; +ALTER EVENT e1 ON SCHEDULE AT '2000-01-02 00:00:00' + ON COMPLETION PRESERVE DISABLE; +SHOW EVENTS; +Db Name Definer Time zone Type Execute at Interval value Interval field Starts Ends Status Originator character_set_client collation_connection Database Collation +events_test e1 root@localhost +02:00 ONE TIME 2000-01-02 00:00:00 NULL NULL NULL NULL DISABLED 1 latin1 latin1_swedish_ci latin1_swedish_ci +SET TIME_ZONE= '-03:00'; +ALTER EVENT e1 ON SCHEDULE EVERY 1 DAY ENDS '2030-01-03 00:00:00' + ON COMPLETION PRESERVE DISABLE; +SHOW EVENTS; +Db Name Definer Time zone Type Execute at Interval value Interval field Starts Ends Status Originator character_set_client collation_connection Database Collation +events_test e1 root@localhost -03:00 RECURRING NULL 1 DAY 2005-12-31 20:58:59 2030-01-03 00:00:00 DISABLED 1 latin1 latin1_swedish_ci latin1_swedish_ci +SET TIME_ZONE= '+04:00'; +ALTER EVENT e1 DO SELECT 2; +SHOW EVENTS; +Db Name Definer Time zone Type Execute at Interval value Interval field Starts Ends Status Originator character_set_client collation_connection Database Collation +events_test e1 root@localhost -03:00 RECURRING NULL 1 DAY 2005-12-31 20:58:59 2030-01-03 00:00:00 DISABLED 1 latin1 latin1_swedish_ci latin1_swedish_ci +DROP EVENT e1; +SET TIME_ZONE='+05:00'; +CREATE EVENT e1 ON SCHEDULE EVERY 1 DAY STARTS '2006-01-01 00:00:00' DO +SELECT 1; +Warnings: +Warning 1105 Event scheduler is switched off, use SET GLOBAL event_scheduler=ON to enable it. +SET TIMESTAMP= @@TIMESTAMP + 1; +SET TIME_ZONE='-05:00'; +CREATE EVENT e2 ON SCHEDULE EVERY 1 DAY STARTS '2006-01-01 00:00:00' DO +SELECT 1; +Warnings: +Warning 1105 Event scheduler is switched off, use SET GLOBAL event_scheduler=ON to enable it. +SET TIMESTAMP= @@TIMESTAMP + 1; +SET TIME_ZONE='+00:00'; +CREATE EVENT e3 ON SCHEDULE EVERY 1 DAY STARTS '2006-01-01 00:00:00' DO +SELECT 1; +Warnings: +Warning 1105 Event scheduler is switched off, use SET GLOBAL event_scheduler=ON to enable it. +SELECT * FROM INFORMATION_SCHEMA.EVENTS ORDER BY event_name; +EVENT_CATALOG EVENT_SCHEMA EVENT_NAME DEFINER TIME_ZONE EVENT_BODY EVENT_DEFINITION EVENT_TYPE EXECUTE_AT INTERVAL_VALUE INTERVAL_FIELD SQL_MODE STARTS ENDS STATUS ON_COMPLETION CREATED LAST_ALTERED LAST_EXECUTED EVENT_COMMENT ORIGINATOR CHARACTER_SET_CLIENT COLLATION_CONNECTION DATABASE_COLLATION +def events_test e1 root@localhost +05:00 SQL SELECT 1 RECURRING NULL 1 DAY 2006-01-01 00:00:00 NULL ENABLED NOT PRESERVE 2005-12-31 23:58:59 2005-12-31 23:58:59 NULL 1 latin1 latin1_swedish_ci latin1_swedish_ci +def events_test e2 root@localhost -05:00 SQL SELECT 1 RECURRING NULL 1 DAY 2006-01-01 00:00:00 NULL ENABLED NOT PRESERVE 2005-12-31 23:59:00 2005-12-31 23:59:00 NULL 1 latin1 latin1_swedish_ci latin1_swedish_ci +def events_test e3 root@localhost +00:00 SQL SELECT 1 RECURRING NULL 1 DAY 2006-01-01 00:00:00 NULL ENABLED NOT PRESERVE 2005-12-31 23:59:01 2005-12-31 23:59:01 NULL 1 latin1 latin1_swedish_ci latin1_swedish_ci +SHOW EVENTS; +Db Name Definer Time zone Type Execute at Interval value Interval field Starts Ends Status Originator character_set_client collation_connection Database Collation +events_test e1 root@localhost +05:00 RECURRING NULL 1 DAY 2006-01-01 00:00:00 NULL ENABLED 1 latin1 latin1_swedish_ci latin1_swedish_ci +events_test e2 root@localhost -05:00 RECURRING NULL 1 DAY 2006-01-01 00:00:00 NULL ENABLED 1 latin1 latin1_swedish_ci latin1_swedish_ci +events_test e3 root@localhost +00:00 RECURRING NULL 1 DAY 2006-01-01 00:00:00 NULL ENABLED 1 latin1 latin1_swedish_ci latin1_swedish_ci +SHOW CREATE EVENT e1; +Event sql_mode time_zone Create Event character_set_client collation_connection Database Collation +e1 +05:00 CREATE DEFINER=`root`@`localhost` EVENT `e1` ON SCHEDULE EVERY 1 DAY STARTS '2006-01-01 00:00:00' ON COMPLETION NOT PRESERVE ENABLE DO SELECT 1 latin1 latin1_swedish_ci latin1_swedish_ci +SHOW CREATE EVENT e2; +Event sql_mode time_zone Create Event character_set_client collation_connection Database Collation +e2 -05:00 CREATE DEFINER=`root`@`localhost` EVENT `e2` ON SCHEDULE EVERY 1 DAY STARTS '2006-01-01 00:00:00' ON COMPLETION NOT PRESERVE ENABLE DO SELECT 1 latin1 latin1_swedish_ci latin1_swedish_ci +SHOW CREATE EVENT e3; +Event sql_mode time_zone Create Event character_set_client collation_connection Database Collation +e3 +00:00 CREATE DEFINER=`root`@`localhost` EVENT `e3` ON SCHEDULE EVERY 1 DAY STARTS '2006-01-01 00:00:00' ON COMPLETION NOT PRESERVE ENABLE DO SELECT 1 latin1 latin1_swedish_ci latin1_swedish_ci +The following should fail, and nothing should be altered. +ALTER EVENT e1 ON SCHEDULE EVERY 1 HOUR STARTS '1999-01-01 00:00:00' + ENDS '1999-01-02 00:00:00'; +ERROR HY000: Event execution time is in the past and ON COMPLETION NOT PRESERVE is set. The event was not changed. Specify a time in the future +ALTER EVENT e1 ON SCHEDULE EVERY 1 HOUR STARTS '1999-01-01 00:00:00' + ENDS '1999-01-02 00:00:00' DISABLE; +ERROR HY000: Event execution time is in the past and ON COMPLETION NOT PRESERVE is set. The event was not changed. Specify a time in the future +The following should give warnings, and nothing should be created. +CREATE EVENT e4 ON SCHEDULE EVERY 1 HOUR STARTS '1999-01-01 00:00:00' + ENDS '1999-01-02 00:00:00' +DO +SELECT 1; +Warnings: +Note 1588 Event execution time is in the past and ON COMPLETION NOT PRESERVE is set. The event was dropped immediately after creation +CREATE EVENT e4 ON SCHEDULE EVERY 1 HOUR STARTS '1999-01-01 00:00:00' + ENDS '1999-01-02 00:00:00' DISABLE +DO +SELECT 1; +Warnings: +Note 1588 Event execution time is in the past and ON COMPLETION NOT PRESERVE is set. The event was dropped immediately after creation +CREATE EVENT e4 ON SCHEDULE AT '1999-01-01 00:00:00' DO +SELECT 1; +Warnings: +Note 1588 Event execution time is in the past and ON COMPLETION NOT PRESERVE is set. The event was dropped immediately after creation +CREATE EVENT e4 ON SCHEDULE AT '1999-01-01 00:00:00' DISABLE +DO +SELECT 1; +Warnings: +Note 1588 Event execution time is in the past and ON COMPLETION NOT PRESERVE is set. The event was dropped immediately after creation +SHOW EVENTS; +Db Name Definer Time zone Type Execute at Interval value Interval field Starts Ends Status Originator character_set_client collation_connection Database Collation +events_test e1 root@localhost +05:00 RECURRING NULL 1 DAY 2006-01-01 00:00:00 NULL ENABLED 1 latin1 latin1_swedish_ci latin1_swedish_ci +events_test e2 root@localhost -05:00 RECURRING NULL 1 DAY 2006-01-01 00:00:00 NULL ENABLED 1 latin1 latin1_swedish_ci latin1_swedish_ci +events_test e3 root@localhost +00:00 RECURRING NULL 1 DAY 2006-01-01 00:00:00 NULL ENABLED 1 latin1 latin1_swedish_ci latin1_swedish_ci +The following should succeed giving a warning. +ALTER EVENT e1 ON SCHEDULE EVERY 1 HOUR STARTS '1999-01-01 00:00:00' + ENDS '1999-01-02 00:00:00' ON COMPLETION PRESERVE; +Warnings: +Note 1544 Event execution time is in the past. Event has been disabled +CREATE EVENT e4 ON SCHEDULE EVERY 1 HOUR STARTS '1999-01-01 00:00:00' + ENDS '1999-01-02 00:00:00' ON COMPLETION PRESERVE +DO +SELECT 1; +Warnings: +Note 1544 Event execution time is in the past. Event has been disabled +Warning 1105 Event scheduler is switched off, use SET GLOBAL event_scheduler=ON to enable it. +CREATE EVENT e5 ON SCHEDULE AT '1999-01-01 00:00:00' + ON COMPLETION PRESERVE +DO +SELECT 1; +Warnings: +Note 1544 Event execution time is in the past. Event has been disabled +Warning 1105 Event scheduler is switched off, use SET GLOBAL event_scheduler=ON to enable it. +The following should succeed without warnings. +ALTER EVENT e2 ON SCHEDULE EVERY 1 HOUR STARTS '1999-01-01 00:00:00'; +ALTER EVENT e3 ON SCHEDULE EVERY 1 HOUR STARTS '1999-01-01 00:00:00' + ENDS '1999-01-02 00:00:00' ON COMPLETION PRESERVE DISABLE; +CREATE EVENT e6 ON SCHEDULE EVERY 1 HOUR STARTS '1999-01-01 00:00:00' DO +SELECT 1; +Warnings: +Warning 1105 Event scheduler is switched off, use SET GLOBAL event_scheduler=ON to enable it. +CREATE EVENT e7 ON SCHEDULE EVERY 1 HOUR STARTS '1999-01-01 00:00:00' + ENDS '1999-01-02 00:00:00' ON COMPLETION PRESERVE DISABLE +DO +SELECT 1; +Warnings: +Warning 1105 Event scheduler is switched off, use SET GLOBAL event_scheduler=ON to enable it. +CREATE EVENT e8 ON SCHEDULE AT '1999-01-01 00:00:00' + ON COMPLETION PRESERVE DISABLE +DO +SELECT 1; +Warnings: +Warning 1105 Event scheduler is switched off, use SET GLOBAL event_scheduler=ON to enable it. +SHOW EVENTS; +Db Name Definer Time zone Type Execute at Interval value Interval field Starts Ends Status Originator character_set_client collation_connection Database Collation +events_test e1 root@localhost +00:00 RECURRING NULL 1 HOUR 1999-01-01 00:00:00 1999-01-02 00:00:00 DISABLED 1 latin1 latin1_swedish_ci latin1_swedish_ci +events_test e2 root@localhost +00:00 RECURRING NULL 1 HOUR 1999-01-01 00:00:00 NULL ENABLED 1 latin1 latin1_swedish_ci latin1_swedish_ci +events_test e3 root@localhost +00:00 RECURRING NULL 1 HOUR 1999-01-01 00:00:00 1999-01-02 00:00:00 DISABLED 1 latin1 latin1_swedish_ci latin1_swedish_ci +events_test e4 root@localhost +00:00 RECURRING NULL 1 HOUR 1999-01-01 00:00:00 1999-01-02 00:00:00 DISABLED 1 latin1 latin1_swedish_ci latin1_swedish_ci +events_test e5 root@localhost +00:00 ONE TIME 1999-01-01 00:00:00 NULL NULL NULL NULL DISABLED 1 latin1 latin1_swedish_ci latin1_swedish_ci +events_test e6 root@localhost +00:00 RECURRING NULL 1 HOUR 1999-01-01 00:00:00 NULL ENABLED 1 latin1 latin1_swedish_ci latin1_swedish_ci +events_test e7 root@localhost +00:00 RECURRING NULL 1 HOUR 1999-01-01 00:00:00 1999-01-02 00:00:00 DISABLED 1 latin1 latin1_swedish_ci latin1_swedish_ci +events_test e8 root@localhost +00:00 ONE TIME 1999-01-01 00:00:00 NULL NULL NULL NULL DISABLED 1 latin1 latin1_swedish_ci latin1_swedish_ci +DROP EVENT e8; +DROP EVENT e7; +DROP EVENT e6; +DROP EVENT e5; +DROP EVENT e4; +DROP EVENT e3; +DROP EVENT e2; +DROP EVENT e1; +SET TIME_ZONE=@save_time_zone; +SET TIMESTAMP=DEFAULT; +drop event if exists new_event; +CREATE EVENT new_event ON SCHEDULE EVERY 0 SECOND DO SELECT 1; +ERROR HY000: INTERVAL is either not positive or too big +CREATE EVENT new_event ON SCHEDULE EVERY (SELECT 0) SECOND DO SELECT 1; +ERROR HY000: INTERVAL is either not positive or too big +CREATE EVENT new_event ON SCHEDULE EVERY "abcdef" SECOND DO SELECT 1; +ERROR HY000: INTERVAL is either not positive or too big +CREATE EVENT new_event ON SCHEDULE EVERY "0abcdef" SECOND DO SELECT 1; +ERROR HY000: INTERVAL is either not positive or too big +CREATE EVENT new_event ON SCHEDULE EVERY "a1bcdef" SECOND DO SELECT 1; +ERROR HY000: INTERVAL is either not positive or too big +CREATE EVENT new_event ON SCHEDULE EVERY (SELECT "abcdef" UNION SELECT "abcdef") SECOND DO SELECT 1; +ERROR HY000: INTERVAL is either not positive or too big +CREATE EVENT new_event ON SCHEDULE EVERY (SELECT "0abcdef") SECOND DO SELECT 1; +ERROR HY000: INTERVAL is either not positive or too big +CREATE EVENT new_event ON SCHEDULE EVERY (SELECT "a1bcdef") SECOND DO SELECT 1; +ERROR HY000: INTERVAL is either not positive or too big +CREATE EVENT new_event ON SCHEDULE AT "every day" DO SELECT 1; +ERROR HY000: Incorrect AT value: 'every day' +CREATE EVENT new_event ON SCHEDULE AT "0every day" DO SELECT 1; +ERROR HY000: Incorrect AT value: '0every day' +CREATE EVENT new_event ON SCHEDULE AT (SELECT "every day") DO SELECT 1; +ERROR HY000: Incorrect AT value: 'every day' +CREATE EVENT new_event ON SCHEDULE AT NOW() STARTS NOW() DO SELECT 1; +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'STARTS NOW() DO SELECT 1' at line 1 +CREATE EVENT new_event ON SCHEDULE AT NOW() ENDS NOW() DO SELECT 1; +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'ENDS NOW() DO SELECT 1' at line 1 +CREATE EVENT new_event ON SCHEDULE AT NOW() STARTS NOW() ENDS NOW() DO SELECT 1; +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'STARTS NOW() ENDS NOW() DO SELECT 1' at line 1 +USE test; +SHOW GRANTS FOR CURRENT_USER; +Grants for root@localhost +GRANT ALL PRIVILEGES ON *.* TO `root`@`localhost` WITH GRANT OPTION +GRANT PROXY ON ''@'%' TO 'root'@'localhost' WITH GRANT OPTION +SET GLOBAL event_scheduler = ON; +CREATE TABLE events_test.event_log +(id int KEY AUTO_INCREMENT, ev_nm char(40), ev_cnt int, ev_tm timestamp) +ENGINE=MyISAM; +SET autocommit=0; +CREATE USER evtest1@localhost; +SET PASSWORD FOR evtest1@localhost = password('ev1'); +REVOKE ALL PRIVILEGES, GRANT OPTION FROM evtest1@localhost; +GRANT create, insert, select, event ON events_test.* TO evtest1@localhost; +GRANT select,insert ON test.* TO evtest1@localhost; +SHOW GRANTS FOR evtest1@localhost; +Grants for evtest1@localhost +GRANT USAGE ON *.* TO `evtest1`@`localhost` IDENTIFIED BY PASSWORD '*3170F3644E31580C25DE4A08F4C07CC9A2D40C32' +GRANT SELECT, INSERT ON `test`.* TO `evtest1`@`localhost` +GRANT SELECT, INSERT, CREATE, EVENT ON `events_test`.* TO `evtest1`@`localhost` +connect e1,localhost,evtest1,ev1,events_test,$MASTER_MYPORT,$MASTER_MYSOCK; +CREATE EVENT ev_sched_1823 ON SCHEDULE EVERY 2 SECOND +DO BEGIN +SET AUTOCOMMIT = 0; +SET @evname = 'ev_sched_1823'; +SET @cnt = 0; +SELECT COUNT(*) INTO @cnt FROM events_test.event_log WHERE ev_nm = @evname; +IF @cnt < 6 THEN +INSERT INTO events_test.event_log VALUES (NULL,@evname,@cnt+1,current_timestamp()); +COMMIT; +END IF; +SELECT COUNT(*) INTO @cnt FROM events_test.event_log WHERE ev_nm = @evname; +IF @cnt < 6 THEN +INSERT INTO events_test.event_log VALUES (NULL,@evname,@cnt+1,current_timestamp()); +ROLLBACK; +END IF; +END;| +Sleep till the first INSERT into events_test.event_log occurred +SELECT COUNT(*) > 0 AS "Expect 1" FROM events_test.event_log; +Expect 1 +1 +connection default; +DROP USER evtest1@localhost; +Sleep 4 seconds +SELECT COUNT(*) INTO @row_cnt FROM events_test.event_log; +Sleep 4 seconds +SELECT COUNT(*) > @row_cnt AS "Expect 0" FROM events_test.event_log; +Expect 0 +0 +disconnect e1; +DROP EVENT events_test.ev_sched_1823; +DROP TABLE events_test.event_log; +SET GLOBAL event_scheduler = OFF; +SET GLOBAL event_scheduler= ON; +CREATE EVENT bug28641 ON SCHEDULE AT '2038.01.18 03:00:00' +DO BEGIN +SELECT 1; +END;| +SET GLOBAL event_scheduler= OFF; +DROP EVENT bug28641; + +##################################################################### +# +# BUG#31111: --read-only crashes MySQL (events fail to load). +# +##################################################################### + +DROP USER mysqltest_u1@localhost; +DROP EVENT IF EXISTS e1; +DROP EVENT IF EXISTS e2; + +GRANT EVENT ON *.* TO mysqltest_u1@localhost; + +SET GLOBAL READ_ONLY = 1; + +connect u1_con,localhost,mysqltest_u1,,events_test; + +CREATE EVENT e1 ON SCHEDULE AT '2038-01-01 00:00:00' DO SET @a = 1; +ERROR HY000: The MariaDB server is running with the --read-only option so it cannot execute this statement + +ALTER EVENT e1 COMMENT 'comment'; +ERROR HY000: The MariaDB server is running with the --read-only option so it cannot execute this statement + +DROP EVENT e1; +ERROR HY000: The MariaDB server is running with the --read-only option so it cannot execute this statement + +connect root_con,localhost,root,,events_test; + +CREATE EVENT e1 ON SCHEDULE AT '2038-01-01 00:00:00' DO SET @a = 1; +Warnings: +Warning 1105 Event scheduler is switched off, use SET GLOBAL event_scheduler=ON to enable it. + +ALTER EVENT e1 COMMENT 'comment'; + +DROP EVENT e1; + +SET GLOBAL READ_ONLY = 0; + +connection u1_con; + +CREATE EVENT e1 ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 SECOND DO SET @a = 1; +Warnings: +Warning 1105 Event scheduler is switched off, use SET GLOBAL event_scheduler=ON to enable it. +CREATE EVENT e2 ON SCHEDULE EVERY 1 SECOND DO SET @a = 1; +Warnings: +Warning 1105 Event scheduler is switched off, use SET GLOBAL event_scheduler=ON to enable it. + +SELECT +event_name, +last_executed IS NULL, +definer +FROM INFORMATION_SCHEMA.EVENTS +WHERE event_schema = 'events_test'; +event_name last_executed IS NULL definer +e1 1 mysqltest_u1@localhost +e2 1 mysqltest_u1@localhost + +connection root_con; + +SET GLOBAL READ_ONLY = 1; + +SET GLOBAL EVENT_SCHEDULER = ON; + +# Waiting for the event scheduler to execute and drop event e1... + +# Waiting for the event scheduler to execute and update event e2... + +SET GLOBAL EVENT_SCHEDULER = OFF; + +SELECT +event_name, +last_executed IS NULL, +definer +FROM INFORMATION_SCHEMA.EVENTS +WHERE event_schema = 'events_test'; +event_name last_executed IS NULL definer +e2 0 mysqltest_u1@localhost + +DROP EVENT e1; +ERROR HY000: Unknown event 'e1' + +# Cleanup. + +DROP EVENT e2; + +SET GLOBAL READ_ONLY = 0; + +disconnect u1_con; +disconnect root_con; +connection default; + +DROP USER mysqltest_u1@localhost; + +##################################################################### +# +# End of BUG#31111. +# +##################################################################### + +drop procedure if exists p; +set @old_mode= @@sql_mode; +set @@sql_mode= cast(pow(2,32)-1 as unsigned integer); +create event e1 on schedule every 1 day do select 1; +Warnings: +Warning 1105 Event scheduler is switched off, use SET GLOBAL event_scheduler=ON to enable it. +select @@sql_mode into @full_mode; +set @@sql_mode= @old_mode; +select replace(@full_mode, 'ALLOW_INVALID_DATES', 'INVALID_DATES') into @full_mode; +select name from mysql.event where name = 'e1' and sql_mode = @full_mode; +name +e1 +drop event e1; +SET @old_server_id = @@GLOBAL.server_id; +SET GLOBAL server_id = (1 << 32) - 1; +SELECT @@GLOBAL.server_id; +@@GLOBAL.server_id +4294967295 +CREATE EVENT ev1 ON SCHEDULE EVERY 1 DAY DO SELECT 1; +Warnings: +Warning 1105 Event scheduler is switched off, use SET GLOBAL event_scheduler=ON to enable it. +SELECT event_name, originator FROM INFORMATION_SCHEMA.EVENTS; +event_name originator +ev1 4294967295 +DROP EVENT ev1; +SET GLOBAL server_id = @old_server_id; +CREATE DATABASE event_test12; +USE event_test12; +CREATE EVENT ev1 ON SCHEDULE EVERY 1 DAY DO SELECT 1; +Warnings: +Warning 1105 Event scheduler is switched off, use SET GLOBAL event_scheduler=ON to enable it. +CREATE DATABASE event_test1; +USE event_test1; +SHOW EVENTS; +Db Name Definer Time zone Type Execute at Interval value Interval field Starts Ends Status Originator character_set_client collation_connection Database Collation +DROP DATABASE event_test1; +DROP DATABASE event_test12; +# +# Bug#12546938 (formerly known as bug#61005): +# CREATE IF NOT EXIST EVENT WILL CREATE MULTIPLE RUNNING EVENTS +# +USE events_test; +SET GLOBAL event_scheduler = ON; +DROP TABLE IF EXISTS table_bug12546938; +DROP EVENT IF EXISTS event_Bug12546938; +CREATE TABLE table_bug12546938 (i INT); +# Create an event which will be executed with a small delay +# and won't be automatically dropped after that. +CREATE EVENT event_Bug12546938 +ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 SECOND ON COMPLETION PRESERVE +ENABLE DO +BEGIN +INSERT INTO table_bug12546938 VALUES(1); +END +| +# Now try to create the same event using CREATE EVENT IF NOT EXISTS. +# A warning should be emitted. A new event should not be created nor +# the old event should be re-executed. +CREATE EVENT IF NOT EXISTS event_bug12546938 +ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 SECOND ON COMPLETION PRESERVE +ENABLE DO +BEGIN +INSERT INTO table_bug12546938 VALUES (1); +END +| +Warnings: +Note 1537 Event 'event_bug12546938' already exists +# Wait until at least one instance of event is executed. +# Check that only one instance of our event was executed. +SELECT COUNT(*) FROM table_bug12546938; +COUNT(*) +1 +# Clean-up. +DROP EVENT IF EXISTS event_Bug12546938; +DROP TABLE table_bug12546938; +SET GLOBAL EVENT_SCHEDULER = OFF; +DROP DATABASE IF EXISTS event_test11764334; +CREATE DATABASE event_test11764334; +USE event_test11764334; +CREATE EVENT ev1 ON SCHEDULE EVERY 3 SECOND DISABLE DO SELECT 1; +Warnings: +Warning 1105 Event scheduler is switched off, use SET GLOBAL event_scheduler=ON to enable it. +SHOW EVENTS IN event_test11764334 WHERE NAME='ev1'; +Db Name Definer Time zone Type Execute at Interval value Interval field Starts Ends Status Originator character_set_client collation_connection Database Collation +event_test11764334 ev1 root@localhost SYSTEM RECURRING NULL 3 SECOND # # DISABLED 1 latin1 latin1_swedish_ci latin1_swedish_ci +ALTER EVENT ev1 ON SCHEDULE EVERY 4 SECOND; +SHOW EVENTS IN event_test11764334 WHERE NAME='ev1'; +Db Name Definer Time zone Type Execute at Interval value Interval field Starts Ends Status Originator character_set_client collation_connection Database Collation +event_test11764334 ev1 root@localhost SYSTEM RECURRING NULL 4 SECOND # # DISABLED 1 latin1 latin1_swedish_ci latin1_swedish_ci +DROP EVENT ev1; +DROP DATABASE event_test11764334; +USE test; +DROP DATABASE events_test; +SET GLOBAL event_scheduler= 'ON'; +SET @@global.concurrent_insert= @concurrent_insert; diff --git a/mysql-test/suite/events/events_bugs.test b/mysql-test/suite/events/events_bugs.test new file mode 100644 index 00000000..04ce048a --- /dev/null +++ b/mysql-test/suite/events/events_bugs.test @@ -0,0 +1,1306 @@ +# Tests will be skipped for the view protocol because the view protocol creates +# an additional util connection and other statistics data +-- source include/no_view_protocol.inc +call mtr.add_suppression('Event Scheduler:'); +#### t/events_bugs.test +# +# Last change: +# 2009-01-08 mleich +# - Fix for +# Bug#41111 events_bugs fails sporadically on pushbuild +# - Avoid effects of +# Bug#41925 Warning 1366 Incorrect string value: ... for column processlist.info +# +# Please set $ +SET SQL_MODE=""; +let $fixed_bug41925= 0; +# +# Dear maintainer of this test. Please do NOT remove the next big comment. +# The tests for events were quite unstable over a significant time because the +# effects of events in general and their representation within the processlist +# were partially not known. Therefore I had to make excessive experiments. +# The comment with the outcome of these experiments will be moved into a README +# file as soon as the tests for events get their own testsuite. +# +# Outcome of some experiments (mleich, mysql-5.1 2008-12): +# -------------------------------------------------------- +# 0. Most time used setup: +# High parallel I/O load +# set global event_scheduler= off; +# sleep 3; +# use events_test; +# create event e_16407 on schedule every 2 second do +# begin +# select 'FIRST COMMAND', sleep(0.5); +# select 'SECOND COMMAND'; +# end| +# set global event_scheduler= on; +# Start observation of the processlist +# +# +# 1. SET GLOBAL event_scheduler = 'ON' and immediate observation of the processlist. +# 1.1 Effects around event scheduler: +# 1.1.1 First phase (very short) +# No user 'event_scheduler' within information_schema.processlist. +# 1.1.2 Second phase observed (independend of probably existing events and very short) was +# USER HOST DB COMMAND TIME STATE INFO +# event_scheduler localhost NULL Daemon 0 Initialized NULL +# 1.1.3 Third phase observed: +# 1.1.3.1 Case we do not have existing events (rather long) +# USER HOST DB COMMAND TIME STATE INFO +# event_scheduler localhost NULL Daemon 0 Waiting on empty queue NULL +# 1.1.3.2 Case there exists already an event +# 1.1.3.2.1 Event executor is not visible in processlist but comes up soon +# USER HOST DB COMMAND TIME STATE INFO +# event_scheduler localhost NULL Daemon 0 NULL +# or +# event_scheduler localhost NULL Daemon 0 NULL NULL +# 1.1.3.2.2 A bit later, at least one event executor is or was visible in processlist +# The states mentioned in 3.2.1 or a bit later +# USER HOST DB COMMAND TIME STATE INFO +# event_scheduler localhost NULL Daemon 0 Waiting for next activation NULL +# 1.2 Effects around event executor: +# Typical processlist content: +# USER evtest1 -- Definer of event +# DB events_test -- DB during time of event creation (use DB is not allowed in events) +# COMMAND Connect +# STATE NULL +# INFO SET @evname = 'ev_sched_1823' -- Part of the event code +# +# State before "User sleep select 'FIRST COMMAND', sleep(0.5);" +# ID USER HOST DB COMMAND TIME STATE INFO +# 7 event_scheduler localhost NULL Connect 0 NULL NULL +# !! The user is not the event creator and the DB is different. !! +# !! This means that we must get later a change of the identity. !! +# or +# USER HOST DB COMMAND TIME STATE INFO +# root localhost events_test Connect 0 checking permissions +# or +# USER HOST DB COMMAND TIME STATE INFO +# root localhost events_test Connect 0 checking permissions CREATE PROCEDURE .... +# or +# USER HOST DB COMMAND TIME STATE INFO +# root localhost events_test Connect 0 NULL select 'FIRST COMMAND', sleep(0.5) +# or +# USER HOST DB COMMAND TIME STATE INFO +# root localhost events_test Connect 0 checking permissions select 'FIRST COMMAND'... +# or +# USER HOST DB COMMAND TIME STATE INFO +# root localhost events_test Connect 0 Opening table select 'FIRST COMMAND', sleep(0.5) +# or +# USER HOST DB COMMAND TIME STATE INFO +# root localhost events_test Connect 0 Locked select 'FIRST COMMAND', sleep(0.5) +# or +# USER HOST DB COMMAND TIME STATE INFO +# root localhost events_test Connect 0 executing select 'FIRST COMMAND', sleep(0.5) +# +# State "User sleep select 'FIRST COMMAND', sleep(0.5);" reached +# USER HOST DB COMMAND TIME STATE INFO +# root localhost events_test Connect 0 User sleep select 'FIRST COMMAND', sleep(0.5) +# +# State at end (! It looks like a slow CREATE PROC !) of event code execution was sometimes +# USER HOST DB COMMAND TIME STATE INFO +# root localhost events_test Connect 0 logging slow query CREATE PROCEDURE `e_16407`... +# +# State after running some event code was sometimes +# USER HOST DB COMMAND TIME STATE INFO +# root localhost events_test Connect 0 logging slow query select 'SECOND COMMAND' +# +# State somewhere (I guess just before the event executor disappears) +# USER HOST DB COMMAND TIME STATE INFO +# Event thread fin <empty> events_test Connect 0 Clearing NULL +# +# +# 2. SET GLOBAL event_scheduler = 'OFF'; +# Immediate observation of the processlist. +# Effects: +# 1. I never found the user 'event_scheduler' within the processlist. +# 2. Events just during execution could be found within the processlist +# = It does not look like "SET GLOBAL event_scheduler = 'OFF'" stops them. +# ==> Everything mentioned in 1.2 above could be observed. +# +# Several subtests were weak because they showed random result set differences after issuing +# "SET GLOBAL EVENT_SCHEDULER= off;". +# 1. Reason one: There were already event executors +# Fix: Wait till there is no event executor active ==> no session WHERE +# - command IN ('Connect') +# There must be no parallel session being just in "Connect" phase! +# or +# - user = <who created the maybe current running events> +# There must be no parallel session of this person. +# or user = 'event_scheduler' with command = 'Connect' +# The session which will soon change its identity to event creator. +# +# 2. Reason two: If an event modifies a MyISAM table than a delayed visibilty of changes +# might occur (concurrent_inserts=on or delayed inserts ...). +# + +# Can't test with embedded server that doesn't support grants +-- source include/not_embedded.inc + +--disable_warnings +drop database if exists events_test; +drop database if exists mysqltest_db1; +drop database if exists mysqltest_db2; +--enable_warnings +set collation_server=latin1_swedish_ci; +create database events_test; +use events_test; +# We use MyISAM tables and must avoid effects (visibility of changes might be +# delayed) caused by concurrent_insert on (default). +set @concurrent_insert= @@global.concurrent_insert; +set @@global.concurrent_insert = 0; + +# +# START: Bug #31332 --event-scheduler option misbehaving +# + +# NOTE!! this test must come first! It's testing that the --event-scheduler +# option with no argument in events_bugs-master.opt turns the scheduler on. + +select * from information_schema.global_variables where variable_name like 'event_scheduler'; +# Check that the event_scheduler is really running +--source include/running_event_scheduler.inc + +SET GLOBAL event_scheduler = 'OFF'; +--source include/check_events_off.inc + +# +# END: Bug #31332 +# + +# +# START - 16415: Events: event names are case sensitive +# +CREATE EVENT lower_case ON SCHEDULE EVERY 1 MINUTE DO SELECT 1; +--error ER_EVENT_ALREADY_EXISTS +CREATE EVENT Lower_case ON SCHEDULE EVERY 2 MINUTE DO SELECT 2; +DROP EVENT Lower_case; +SET NAMES cp1251; +CREATE EVENT __1251 ON SCHEDULE EVERY 1 YEAR DO SELECT 100; +--error ER_EVENT_ALREADY_EXISTS +CREATE EVENT __1251 ON SCHEDULE EVERY 2 YEAR DO SELECT 200; +DROP EVENT __1251; +SET NAMES utf8; +CREATE EVENT долен_регистър_утф8 ON SCHEDULE EVERY 3 YEAR DO SELECT 300; +--error ER_EVENT_ALREADY_EXISTS +CREATE EVENT ДОЛЕН_регистър_утф8 ON SCHEDULE EVERY 4 YEAR DO SELECT 400; +DROP EVENT ДОЛЕН_регистър_утф8; +SET NAMES latin1; +# +# END - 16415: Events: event names are case sensitive +# + +# +# START - BUG#16408: Events: crash for an event in a procedure +# +set @a=3; +--error ER_EVENT_RECURSION_FORBIDDEN +CREATE PROCEDURE p_16 () CREATE EVENT e_16 ON SCHEDULE EVERY @a SECOND DO SET @a=5; +# +# END - BUG#16408: Events: crash for an event in a procedure +# + +# +# Start - 16396: Events: Distant-future dates become past dates +# +--error ER_WRONG_VALUE +create event e_55 on schedule at 99990101000000 do drop table t; +--error ER_WRONG_VALUE +create event e_55 on schedule every 10 hour starts 99990101000000 do drop table t; +--error ER_EVENT_ENDS_BEFORE_STARTS +create event e_55 on schedule every 10 minute ends 99990101000000 do drop table t; +--error ER_WRONG_VALUE +create event e_55 on schedule at 10000101000000 do drop table t; + +# For the purpose of backup we allow times in the past. Here, no +# error will be given, but the event won't be created. One may think +# of that as if the event was created, then it turned out it's in the +# past, so it was dropped because of implicit ON COMPLETION NOT +# PRESERVE. +create event e_55 on schedule at 20000101000000 do drop table t; +show events; + +--error ER_PARSE_ERROR +create event e_55 on schedule at 20200101000000 starts 10000101000000 do drop table t; +--error ER_PARSE_ERROR +create event e_55 on schedule at 20200101000000 ends 10000101000000 do drop table t; +--error ER_PARSE_ERROR +create event e_55 on schedule at 20200101000000 starts 10000101000000 ends 10000101000000 do drop table t; +--error ER_WRONG_VALUE +create event e_55 on schedule every 10 hour starts 10000101000000 do drop table t; + +# +# End - 16396: Events: Distant-future dates become past dates +# + +# +# Start - 16407: Events: Changes in sql_mode won't be taken into account +# +set global event_scheduler=off; +--source include/check_events_off.inc +delete from mysql.event; +set global event_scheduler= on; +--source include/running_event_scheduler.inc +set @old_sql_mode:=@@sql_mode; +set sql_mode=ansi; +select get_lock('test_bug16407', 60); +delimiter |; +create event e_16407 on schedule every 60 second do +begin + select get_lock('test_bug16407', 60); +end| +delimiter ;| + +# The default session has the user lock. +# We wait till one event runs and hangs when trying to get the user lock. +let $wait_condition= + select count(*) > 0 from information_schema.processlist + where state = 'User lock' and info = 'select get_lock(\'test_bug16407\', 60)'; +--source include/wait_condition.inc + +--echo "Now if everything is fine the event has compiled and is locked" +# Expect to see one session in events_test waiting for the user lock. +select /*1*/ user, host, db, info from information_schema.processlist +where state = 'User lock' and info = 'select get_lock(\'test_bug16407\', 60)'; +select release_lock('test_bug16407'); +# Try to avoid +# Bug#39863 events_bugs fails sporadically on pushbuild (extra processes in I_S.PROCESSLIST) +# which is most probably caused by +# Bug#32782 User lock hash fails to find lock +# "various issues related to missing or incorrect return results +# from release_lock()." +# Therefore we check here if the event executor is no more locked or +# we waited >= 5 seconds for this to happen. +let $wait_timeout= 5; +let $wait_condition= + select count(*) = 0 from information_schema.processlist + where state = 'User lock' and info = 'select get_lock(\'test_bug16407\', 60)'; +--source include/wait_condition.inc +if (!`$wait_condition`) +{ + --echo ERROR: There must be no session with + --echo state = 'User lock' and info = 'select get_lock('test_bug16407', 60) + --echo within the processlist. + --echo We probably hit Bug#32782 User lock hash fails to find lock + SELECT * FROM information_schema.processlist; + --echo Abort + exit; +} + +set global event_scheduler= off; +# Wait till the event scheduler and all event executors have finished their work. +--source include/check_events_off.inc + +select event_schema, event_name, sql_mode from information_schema.events order by event_schema, event_name; +--echo "Let's check whether we change the sql_mode on ALTER EVENT" +set sql_mode='traditional'; +alter event e_16407 do select 1; +select event_schema, event_name, sql_mode from information_schema.events order by event_schema, event_name; +drop event e_16407; + +set sql_mode="ansi"; +select get_lock('ee_16407_2', 60); + +set global event_scheduler= 1; +--source include/running_event_scheduler.inc +--echo "Another sql_mode test" +set sql_mode="traditional"; +create table events_smode_test(ev_name char(10), a date); +--echo "This should never insert something" +delimiter |; +create event ee_16407_2 on schedule every 60 second do +begin + select get_lock('ee_16407_2', 60); /*ee_16407_2*/ + select release_lock('ee_16407_2'); + insert into events_test.events_smode_test values('ee_16407_2','1980-19-02'); +end| +--error ER_TRUNCATED_WRONG_VALUE +insert into events_test.events_smode_test values ('test','1980-19-02')| +--echo "This is ok" +create event ee_16407_3 on schedule every 60 second do +begin + select get_lock('ee_16407_2', 60); /*ee_16407_3*/ + select release_lock('ee_16407_2'); + insert into events_test.events_smode_test values ('ee_16407_3','1980-02-19'); + insert into events_test.events_smode_test values ('ee_16407_3','1980-02-29'); +end| +set sql_mode=""| +--echo "This will insert rows but they will be truncated" +create event ee_16407_4 on schedule every 60 second do +begin + select get_lock('ee_16407_2', 60); /*ee_16407_4*/ + select release_lock('ee_16407_2'); + insert into events_test.events_smode_test values ('ee_16407_4','10-11-1956'); +end| +delimiter ;| +select event_schema, event_name, sql_mode from information_schema.events order by event_schema, event_name; + +# We wait till we have three event executors waiting for the removal of the lock. +let $wait_condition= + select count(*) = 3 from information_schema.processlist + where state = 'User lock' and info = 'select get_lock(\'ee_16407_2\', 60)'; +--source include/wait_condition.inc + +# There is an extreme low risk that an additional event execution is just coming +# up because +# - the events have to be started every 60 seconds. +# - we are just after event creation + waiting for seeing 3 locked +# We expect to see three event executors in state 'User lock'. +select /*2*/ user, host, db, info from information_schema.processlist +where state = 'User lock' and info = 'select get_lock(\'ee_16407_2\', 60)'; + +select release_lock('ee_16407_2'); + +# Try to avoid +# Bug#39863 events_bugs fails sporadically on pushbuild (extra processes in I_S.PROCESSLIST) +# which is most probably caused by +# Bug#32782 User lock hash fails to find lock +# "various issues related to missing or incorrect return results +# from release_lock()." +# Therefore we check here if the event executing sessions disappeared or +# we waited >= 5 seconds for this to happen. +let $wait_timeout= 5; +let $wait_condition= + select count(*) = 0 + from information_schema.processlist + where state = 'User lock' and info = 'select get_lock(\'ee_16407_2\', 60)'; +--source include/wait_condition.inc +if (!`$wait_condition`) +{ + --echo ERROR: There must be no session with + --echo state = 'User lock' and info = 'select get_lock('test_bug16407_2', 60) + --echo within the processlist. + --echo We probably hit Bug#32782 User lock hash fails to find lock + SELECT * FROM information_schema.processlist; + --echo Abort + exit; +} + +# We expect to see no event executors in state 'User lock'. +if(!$fixed_bug41925) +{ + --disable_warnings +} +select /*3*/ user, host, db, info from information_schema.processlist +where state = 'User lock' and info = 'select get_lock(\'ee_16407_2\', 60)'; +if(!$fixed_bug41925) +{ + --enable_warnings +} + +set global event_scheduler= off; +# Wait till the event scheduler and all event executors have finished their work. +# Without this the next queries will give unstable results. +--source include/check_events_off.inc + +select * from events_test.events_smode_test order by ev_name, a; +--echo "OK, last check before we drop them" +select event_schema, event_name, sql_mode from information_schema.events order by event_schema, event_name; +drop event ee_16407_2; +drop event ee_16407_3; +drop event ee_16407_4; + + +--echo "And now one last test regarding sql_mode and call of SP from an event" +delete from events_test.events_smode_test; +set sql_mode='ansi'; +select get_lock('ee_16407_5', 60); + +set global event_scheduler= on; +--source include/running_event_scheduler.inc + +set sql_mode='traditional'; +delimiter |; +# ee_16407_5_pendant() should not insert anything because of invalid date. +create procedure ee_16407_5_pendant() begin insert into events_test.events_smode_test values('ee_16407_5','2001-02-29'); end| +create procedure ee_16407_6_pendant() begin insert into events_test.events_smode_test values('ee_16407_6','2004-02-29'); end| +create event ee_16407_5 on schedule every 60 second do +begin + select get_lock('ee_16407_5', 60); /*ee_16407_5*/ + select release_lock('ee_16407_5'); + call events_test.ee_16407_5_pendant(); +end| +create event ee_16407_6 on schedule every 60 second do +begin + select get_lock('ee_16407_5', 60); /*ee_16407_6*/ + select release_lock('ee_16407_5'); + call events_test.ee_16407_6_pendant(); +end| +delimiter ;| + +let $wait_condition= + select count(*) = 2 from information_schema.processlist + where state = 'User lock' and info = 'select get_lock(\'ee_16407_5\', 60)'; +--source include/wait_condition.inc + +--echo "Should have 2 locked processes" +select /*4*/ user, host, db, info from information_schema.processlist +where state = 'User lock' and info = 'select get_lock(\'ee_16407_5\', 60)'; + +select release_lock('ee_16407_5'); + +let $wait_condition= + select count(*) = 0 from information_schema.processlist + where state = 'User lock' and info = 'select get_lock(\'ee_16407_5\', 60)'; +--source include/wait_condition.inc + +--echo "Should have 0 processes locked" +if(!$fixed_bug41925) +{ + --disable_warnings +} +select /*5*/ user, host, db, info from information_schema.processlist +where state = 'User lock' and info = 'select get_lock(\'ee_16407_5\', 60)'; +if(!$fixed_bug41925) +{ + --enable_warnings +} + +# Wait till all event executors have finished their work, so that we can be sure +# that their changes to events_smode_test are done. +--source include/no_running_events.inc + +select * from events_test.events_smode_test order by ev_name, a; +--echo "And here we check one more time before we drop the events" +select event_schema, event_name, sql_mode from information_schema.events order by event_schema, event_name; + +drop event ee_16407_5; +drop event ee_16407_6; +drop procedure ee_16407_5_pendant; +drop procedure ee_16407_6_pendant; +set global event_scheduler= off; +--source include/check_events_off.inc +drop table events_smode_test; +set sql_mode=@old_sql_mode; +# +# End - 16407: Events: Changes in sql_mode won't be taken into account +# + +# +# START - 18897: Events: unauthorized action possible with alter event rename +# +set global event_scheduler=off; +--source include/check_events_off.inc +--disable_warnings +delete from mysql.user where User like 'mysqltest_%'; +delete from mysql.db where User like 'mysqltest_%'; +flush privileges; +drop database if exists mysqltest_db1; +--enable_warnings +create user mysqltest_user1@localhost; +create database mysqltest_db1; +grant event on events_test.* to mysqltest_user1@localhost; +connect (conn2,localhost,mysqltest_user1,,events_test); +create event mysqltest_user1 on schedule every 10 second do select 42; +--error ER_DBACCESS_DENIED_ERROR +alter event mysqltest_user1 rename to mysqltest_db1.mysqltest_user1; +--echo "Let's test now rename when there is no select DB" +disconnect conn2; +connect (conn2,localhost,mysqltest_user1,,*NO-ONE*); +select database(); +--error ER_NO_DB_ERROR +alter event events_test.mysqltest_user1 rename to mysqltest_user1; +select event_schema, event_name, definer, event_type, status from information_schema.events; +drop event events_test.mysqltest_user1; +disconnect conn2; +connection default; +drop user mysqltest_user1@localhost; +drop database mysqltest_db1; +# +# END - 18897: Events: unauthorized action possible with alter event rename +# + +# +# START - BUG#16394: Events: Crash if schedule contains SELECT +# +--error ER_SUBQUERIES_NOT_SUPPORTED +create event e_53 on schedule at (select s1 from ttx) do drop table t; +--error ER_SUBQUERIES_NOT_SUPPORTED +create event e_53 on schedule every (select s1 from ttx) second do drop table t; +--error ER_SUBQUERIES_NOT_SUPPORTED +create event e_53 on schedule every 5 second starts (select s1 from ttx) do drop table t; +--error ER_SUBQUERIES_NOT_SUPPORTED +create event e_53 on schedule every 5 second ends (select s1 from ttx) do drop table t; +# +# END - BUG#16394: Events: Crash if schedule contains SELECT +# + +# +# START - BUG#22397: Events: crash with procedure which alters events +# +--disable_warnings +drop event if exists e_16; +drop procedure if exists p_16; +--enable_warnings +create event e_16 on schedule every 1 second do set @a=5; +create procedure p_16 () alter event e_16 on schedule every @a second; +set @a = null; +--error ER_WRONG_VALUE +call p_16(); +--error ER_WRONG_VALUE +call p_16(); +set @a= 6; +call p_16(); + +drop procedure p_16; +drop event e_16; + +# +# START - BUG#22830 Events: crash with procedure which alters events with function +# +--disable_warnings +drop function if exists f22830; +drop event if exists e22830; +drop event if exists e22830_1; +drop event if exists e22830_2; +drop event if exists e22830_3; +drop event if exists e22830_4; +drop table if exists t1; +drop table if exists t2; +--enable_warnings +create table t1 (a int); +insert into t1 values (2); +create table t2 (a char(20)); +insert into t2 values ("e22830_1"); +create function f22830 () returns int return 5; + +select get_lock('ee_22830', 60); +set global event_scheduler=on; +--source include/running_event_scheduler.inc + +delimiter |; +create procedure p22830_wait() +begin + select get_lock('ee_22830', 60); + select release_lock('ee_22830'); +end| + +--error ER_SUBQUERIES_NOT_SUPPORTED +create event e22830 on schedule every f22830() second do +begin + call p22830_wait(); + select 123; +end| +create event e22830_1 on schedule every 1 hour do +begin + call p22830_wait(); + alter event e22830_1 on schedule every (select 8 from dual) hour; +end| +create event e22830_2 on schedule every 1 hour do +begin + call p22830_wait(); + alter event e22830_2 on schedule every (select 8 from t1) hour; +end| +create event e22830_3 on schedule every 1 hour do +begin + call p22830_wait(); + alter event e22830_3 on schedule every f22830() hour; +end| +create event e22830_4 on schedule every 1 hour do +begin + call p22830_wait(); + alter event e22830_4 on schedule every (select f22830() from dual) hour; +end| +delimiter ;| + +--echo "All events should be blocked in get_lock()" +select event_name, event_definition, interval_value, interval_field from information_schema.events order by event_name; + +select release_lock('ee_22830'); + +let $wait_condition= + select group_concat(interval_value order by interval_value) = '1,1,1,8' + from information_schema.events; +--source include/wait_condition.inc + +set global event_scheduler=off; +--source include/check_events_off.inc +select event_name, event_definition, interval_value, interval_field from information_schema.events order by event_name; +drop procedure p22830_wait; +drop function f22830; +--error ER_PARSE_ERROR +drop event (select a from t2); +drop event e22830_1; +drop event e22830_2; +drop event e22830_3; +drop event e22830_4; +drop table t1; +drop table t2; + + +# +# BUG#16425: Events: no DEFINER clause +# +--error 0,ER_CANNOT_USER +DROP USER mysqltest_u1@localhost; + +CREATE USER mysqltest_u1@localhost; +GRANT EVENT ON events_test.* TO mysqltest_u1@localhost; + +CREATE EVENT e1 ON SCHEDULE EVERY 1 DAY DO SELECT 1; +SELECT event_name, definer FROM INFORMATION_SCHEMA.EVENTS; +DROP EVENT e1; + +CREATE DEFINER=CURRENT_USER EVENT e1 ON SCHEDULE EVERY 1 DAY DO SELECT 1; +SELECT event_name, definer FROM INFORMATION_SCHEMA.EVENTS; +ALTER DEFINER=mysqltest_u1@localhost EVENT e1 ON SCHEDULE EVERY 1 HOUR; +SELECT event_name, definer FROM INFORMATION_SCHEMA.EVENTS; +DROP EVENT e1; + +CREATE DEFINER=CURRENT_USER() EVENT e1 ON SCHEDULE EVERY 1 DAY DO SELECT 1; +SELECT event_name, definer FROM INFORMATION_SCHEMA.EVENTS; +DROP EVENT e1; + +CREATE DEFINER=mysqltest_u1@localhost EVENT e1 ON SCHEDULE EVERY 1 DAY DO + SELECT 1; +SELECT event_name, definer FROM INFORMATION_SCHEMA.EVENTS; +DROP EVENT e1; + +connect (conn1, localhost, mysqltest_u1, , events_test); + +CREATE EVENT e1 ON SCHEDULE EVERY 1 DAY DO SELECT 1; +SELECT event_name, definer FROM INFORMATION_SCHEMA.EVENTS; +DROP EVENT e1; + +CREATE DEFINER=CURRENT_USER EVENT e1 ON SCHEDULE EVERY 1 DAY DO SELECT 1; +SELECT event_name, definer FROM INFORMATION_SCHEMA.EVENTS; +--error ER_SPECIFIC_ACCESS_DENIED_ERROR +ALTER DEFINER=root@localhost EVENT e1 ON SCHEDULE EVERY 1 HOUR; +SELECT event_name, definer FROM INFORMATION_SCHEMA.EVENTS; +DROP EVENT e1; + +CREATE DEFINER=CURRENT_USER() EVENT e1 ON SCHEDULE EVERY 1 DAY DO SELECT 1; +SELECT event_name, definer FROM INFORMATION_SCHEMA.EVENTS; +DROP EVENT e1; + +--error ER_SPECIFIC_ACCESS_DENIED_ERROR +CREATE DEFINER=root@localhost EVENT e1 ON SCHEDULE EVERY 1 DAY DO SELECT 1; +--error ER_EVENT_DOES_NOT_EXIST +DROP EVENT e1; + +disconnect conn1; +connection default; + +DROP USER mysqltest_u1@localhost; + + +# +# BUG#16420: Events: timestamps become UTC +# BUG#26429: SHOW CREATE EVENT is incorrect for an event that +# STARTS NOW() +# BUG#26431: Impossible to re-create an event from backup if its +# STARTS clause is in the past +# WL#3698: Events: execution in local time zone +# +# Here we only check non-concurrent aspects of the patch. +# For the actual tests of time zones please see events_time_zone.test +# +SET GLOBAL EVENT_SCHEDULER= OFF; +--source include/check_events_off.inc +SET @save_time_zone= @@TIME_ZONE; + +#---------------------------------------------------------------------- + +# We will use a separate connection because SET TIMESTAMP will stop +# the clock in that connection. + +SET TIME_ZONE= '+00:00'; +SET TIMESTAMP= UNIX_TIMESTAMP('2005-12-31 23:58:59'); + + +# Test when event time zone is updated on ALTER EVENT. +# + +CREATE EVENT e1 ON SCHEDULE EVERY 1 DAY DO SELECT 1; +SHOW EVENTS; + +# Test storing and updating of the event time zone. +# +SET TIME_ZONE= '-01:00'; +ALTER EVENT e1 ON SCHEDULE EVERY 1 DAY STARTS '2000-01-01 00:00:00'; +SHOW EVENTS; + +# This will update event time zone. +SET TIME_ZONE= '+02:00'; +ALTER EVENT e1 ON SCHEDULE AT '2000-01-02 00:00:00' + ON COMPLETION PRESERVE DISABLE; +SHOW EVENTS; + +# This will update event time zone. +SET TIME_ZONE= '-03:00'; +ALTER EVENT e1 ON SCHEDULE EVERY 1 DAY ENDS '2030-01-03 00:00:00' + ON COMPLETION PRESERVE DISABLE; +SHOW EVENTS; + +# This will not update event time zone, as no time is being adjusted. +SET TIME_ZONE= '+04:00'; +ALTER EVENT e1 DO SELECT 2; +SHOW EVENTS; + +DROP EVENT e1; + +#---------------------------------------------------------------------- + +# Create some events. +SET TIME_ZONE='+05:00'; +CREATE EVENT e1 ON SCHEDULE EVERY 1 DAY STARTS '2006-01-01 00:00:00' DO + SELECT 1; + +SET TIMESTAMP= @@TIMESTAMP + 1; + +SET TIME_ZONE='-05:00'; +CREATE EVENT e2 ON SCHEDULE EVERY 1 DAY STARTS '2006-01-01 00:00:00' DO + SELECT 1; + +SET TIMESTAMP= @@TIMESTAMP + 1; + +SET TIME_ZONE='+00:00'; +CREATE EVENT e3 ON SCHEDULE EVERY 1 DAY STARTS '2006-01-01 00:00:00' DO + SELECT 1; + + +# Test INFORMATION_SCHEMA.EVENTS. +# + +SELECT * FROM INFORMATION_SCHEMA.EVENTS ORDER BY event_name; + + +# Test SHOW EVENTS. +# + +SHOW EVENTS; + + +# Test SHOW CREATE EVENT. +# + +SHOW CREATE EVENT e1; +SHOW CREATE EVENT e2; +SHOW CREATE EVENT e3; + +#---------------------------------------------------------------------- + +# Test times in the past. +# + +--echo The following should fail, and nothing should be altered. + +--error ER_EVENT_CANNOT_ALTER_IN_THE_PAST +ALTER EVENT e1 ON SCHEDULE EVERY 1 HOUR STARTS '1999-01-01 00:00:00' + ENDS '1999-01-02 00:00:00'; + +--error ER_EVENT_CANNOT_ALTER_IN_THE_PAST +ALTER EVENT e1 ON SCHEDULE EVERY 1 HOUR STARTS '1999-01-01 00:00:00' + ENDS '1999-01-02 00:00:00' DISABLE; + +--echo The following should give warnings, and nothing should be created. + +CREATE EVENT e4 ON SCHEDULE EVERY 1 HOUR STARTS '1999-01-01 00:00:00' + ENDS '1999-01-02 00:00:00' +DO + SELECT 1; + +CREATE EVENT e4 ON SCHEDULE EVERY 1 HOUR STARTS '1999-01-01 00:00:00' + ENDS '1999-01-02 00:00:00' DISABLE +DO + SELECT 1; + +CREATE EVENT e4 ON SCHEDULE AT '1999-01-01 00:00:00' DO + SELECT 1; + +CREATE EVENT e4 ON SCHEDULE AT '1999-01-01 00:00:00' DISABLE +DO + SELECT 1; + +SHOW EVENTS; + +--echo The following should succeed giving a warning. + +ALTER EVENT e1 ON SCHEDULE EVERY 1 HOUR STARTS '1999-01-01 00:00:00' + ENDS '1999-01-02 00:00:00' ON COMPLETION PRESERVE; + +CREATE EVENT e4 ON SCHEDULE EVERY 1 HOUR STARTS '1999-01-01 00:00:00' + ENDS '1999-01-02 00:00:00' ON COMPLETION PRESERVE +DO + SELECT 1; + +CREATE EVENT e5 ON SCHEDULE AT '1999-01-01 00:00:00' + ON COMPLETION PRESERVE +DO + SELECT 1; + +--echo The following should succeed without warnings. + +ALTER EVENT e2 ON SCHEDULE EVERY 1 HOUR STARTS '1999-01-01 00:00:00'; + +ALTER EVENT e3 ON SCHEDULE EVERY 1 HOUR STARTS '1999-01-01 00:00:00' + ENDS '1999-01-02 00:00:00' ON COMPLETION PRESERVE DISABLE; + +CREATE EVENT e6 ON SCHEDULE EVERY 1 HOUR STARTS '1999-01-01 00:00:00' DO + SELECT 1; + +CREATE EVENT e7 ON SCHEDULE EVERY 1 HOUR STARTS '1999-01-01 00:00:00' + ENDS '1999-01-02 00:00:00' ON COMPLETION PRESERVE DISABLE +DO + SELECT 1; + +CREATE EVENT e8 ON SCHEDULE AT '1999-01-01 00:00:00' + ON COMPLETION PRESERVE DISABLE +DO + SELECT 1; + +SHOW EVENTS; + + +DROP EVENT e8; +DROP EVENT e7; +DROP EVENT e6; +DROP EVENT e5; +DROP EVENT e4; +DROP EVENT e3; +DROP EVENT e2; +DROP EVENT e1; + +SET TIME_ZONE=@save_time_zone; +SET TIMESTAMP=DEFAULT; + +# +# START - BUG#28666 CREATE EVENT ... EVERY 0 SECOND let server crash +# +--disable_warnings +drop event if exists new_event; +--enable_warnings +--error ER_EVENT_INTERVAL_NOT_POSITIVE_OR_TOO_BIG +CREATE EVENT new_event ON SCHEDULE EVERY 0 SECOND DO SELECT 1; +--error ER_EVENT_INTERVAL_NOT_POSITIVE_OR_TOO_BIG +CREATE EVENT new_event ON SCHEDULE EVERY (SELECT 0) SECOND DO SELECT 1; +--error ER_EVENT_INTERVAL_NOT_POSITIVE_OR_TOO_BIG +CREATE EVENT new_event ON SCHEDULE EVERY "abcdef" SECOND DO SELECT 1; +--error ER_EVENT_INTERVAL_NOT_POSITIVE_OR_TOO_BIG +CREATE EVENT new_event ON SCHEDULE EVERY "0abcdef" SECOND DO SELECT 1; +--error ER_EVENT_INTERVAL_NOT_POSITIVE_OR_TOO_BIG +CREATE EVENT new_event ON SCHEDULE EVERY "a1bcdef" SECOND DO SELECT 1; + +--error ER_EVENT_INTERVAL_NOT_POSITIVE_OR_TOO_BIG +CREATE EVENT new_event ON SCHEDULE EVERY (SELECT "abcdef" UNION SELECT "abcdef") SECOND DO SELECT 1; +--error ER_EVENT_INTERVAL_NOT_POSITIVE_OR_TOO_BIG +CREATE EVENT new_event ON SCHEDULE EVERY (SELECT "0abcdef") SECOND DO SELECT 1; +--error ER_EVENT_INTERVAL_NOT_POSITIVE_OR_TOO_BIG +CREATE EVENT new_event ON SCHEDULE EVERY (SELECT "a1bcdef") SECOND DO SELECT 1; + +--error ER_WRONG_VALUE +CREATE EVENT new_event ON SCHEDULE AT "every day" DO SELECT 1; +--error ER_WRONG_VALUE +CREATE EVENT new_event ON SCHEDULE AT "0every day" DO SELECT 1; +--error ER_WRONG_VALUE +CREATE EVENT new_event ON SCHEDULE AT (SELECT "every day") DO SELECT 1; + +--error ER_PARSE_ERROR +CREATE EVENT new_event ON SCHEDULE AT NOW() STARTS NOW() DO SELECT 1; +--error ER_PARSE_ERROR +CREATE EVENT new_event ON SCHEDULE AT NOW() ENDS NOW() DO SELECT 1; +--error ER_PARSE_ERROR +CREATE EVENT new_event ON SCHEDULE AT NOW() STARTS NOW() ENDS NOW() DO SELECT 1; + +# +# START - BUG#28924 If I drop the user who is the definer of an active event +# then server cores +# +let $engine=MyISAM; +USE test; +SHOW GRANTS FOR CURRENT_USER; +SET GLOBAL event_scheduler = ON; +--source include/running_event_scheduler.inc +eval +CREATE TABLE events_test.event_log +(id int KEY AUTO_INCREMENT, ev_nm char(40), ev_cnt int, ev_tm timestamp) +ENGINE=$engine; +SET autocommit=0; +CREATE USER evtest1@localhost; +SET PASSWORD FOR evtest1@localhost = password('ev1'); +REVOKE ALL PRIVILEGES, GRANT OPTION FROM evtest1@localhost; +GRANT create, insert, select, event ON events_test.* TO evtest1@localhost; +GRANT select,insert ON test.* TO evtest1@localhost; +SHOW GRANTS FOR evtest1@localhost; + +--replace_result $MASTER_MYPORT MASTER_MYPORT $MASTER_MYSOCK MASTER_MYSOCK +connect (e1,localhost,evtest1,ev1,events_test,$MASTER_MYPORT,$MASTER_MYSOCK); +DELIMITER |; +CREATE EVENT ev_sched_1823 ON SCHEDULE EVERY 2 SECOND +DO BEGIN + SET AUTOCOMMIT = 0; + SET @evname = 'ev_sched_1823'; + SET @cnt = 0; + SELECT COUNT(*) INTO @cnt FROM events_test.event_log WHERE ev_nm = @evname; + IF @cnt < 6 THEN + INSERT INTO events_test.event_log VALUES (NULL,@evname,@cnt+1,current_timestamp()); + COMMIT; + END IF; + SELECT COUNT(*) INTO @cnt FROM events_test.event_log WHERE ev_nm = @evname; + IF @cnt < 6 THEN + INSERT INTO events_test.event_log VALUES (NULL,@evname,@cnt+1,current_timestamp()); + ROLLBACK; + END IF; +END;| +DELIMITER ;| +# 1. Show that the new created event is runnable and gets executed within a +# reasonable time like 4 seconds. Till ~ 2 seconds could pass on a heavy +# loaded testing box before something gets executed). +# Detection of execution is via the records inserted by the event. +--echo Sleep till the first INSERT into events_test.event_log occurred +let $wait_timeout= 4; +let $wait_condition= +SELECT COUNT(*) > 0 FROM events_test.event_log; +--source include/wait_condition.inc +SELECT COUNT(*) > 0 AS "Expect 1" FROM events_test.event_log; +# +connection default; +DROP USER evtest1@localhost; +# 2. If we meet BUG#28924 again than a server crash happens within the next +# few seconds +# 3. Check that the event is never executed again +# It could be that an event execution was running before the DROP USER +# and all implicite actions belonging to this are completed. +# Lets assume that ~ 4 seconds waiting are enough for the event +# scheduler to detect that +--echo Sleep 4 seconds +sleep 4; +SELECT COUNT(*) INTO @row_cnt FROM events_test.event_log; +# Give the event mechanism ~ 4 seconds to do something wrong +# (execute the event of the dropped user -> inser rows). +--echo Sleep 4 seconds +sleep 4; +SELECT COUNT(*) > @row_cnt AS "Expect 0" FROM events_test.event_log; +# +# Cleanup +disconnect e1; +DROP EVENT events_test.ev_sched_1823; +DROP TABLE events_test.event_log; +SET GLOBAL event_scheduler = OFF; +--source include/check_events_off.inc + + +# +# Bug#28641 CREATE EVENT with '2038.01.18 03:00:00' let server crash. +# +SET GLOBAL event_scheduler= ON; +--source include/running_event_scheduler.inc +DELIMITER |; +CREATE EVENT bug28641 ON SCHEDULE AT '2038.01.18 03:00:00' +DO BEGIN + SELECT 1; +END;| + +DELIMITER ;| +SET GLOBAL event_scheduler= OFF; +--source include/check_events_off.inc +DROP EVENT bug28641; + +########################################################################### + +--echo +--echo ##################################################################### +--echo # +--echo # BUG#31111: --read-only crashes MySQL (events fail to load). +--echo # +--echo ##################################################################### +--echo + +--error 0,ER_CANNOT_USER +DROP USER mysqltest_u1@localhost; + +--disable_warnings +DROP EVENT IF EXISTS e1; +DROP EVENT IF EXISTS e2; +--enable_warnings + +--echo + +# Check that an ordinary user can not create/update/drop events in the +# read-only mode. + +GRANT EVENT ON *.* TO mysqltest_u1@localhost; + +--echo + +SET GLOBAL READ_ONLY = 1; + +--echo + +--connect(u1_con,localhost,mysqltest_u1,,events_test) + +--echo + +--error ER_OPTION_PREVENTS_STATEMENT +CREATE EVENT e1 ON SCHEDULE AT '2038-01-01 00:00:00' DO SET @a = 1; + +--echo + +--error ER_OPTION_PREVENTS_STATEMENT +ALTER EVENT e1 COMMENT 'comment'; + +--echo + +--error ER_OPTION_PREVENTS_STATEMENT +DROP EVENT e1; + +--echo + +# Check that the super user still can create/update/drop events. + +--connect(root_con,localhost,root,,events_test) + +--echo + +CREATE EVENT e1 ON SCHEDULE AT '2038-01-01 00:00:00' DO SET @a = 1; + +--echo + +ALTER EVENT e1 COMMENT 'comment'; + +--echo + +DROP EVENT e1; + +--echo + +# +# Switch to read-write mode; create test events under the user mysqltest_u1; +# switch back to read-only mode. +# + +SET GLOBAL READ_ONLY = 0; + +--echo + +--connection u1_con + +--echo + +CREATE EVENT e1 ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 SECOND DO SET @a = 1; +CREATE EVENT e2 ON SCHEDULE EVERY 1 SECOND DO SET @a = 1; + +--echo + +SELECT + event_name, + last_executed IS NULL, + definer +FROM INFORMATION_SCHEMA.EVENTS +WHERE event_schema = 'events_test'; + +--echo + +--connection root_con + +--echo + +SET GLOBAL READ_ONLY = 1; + +# Check that the event scheduler is able to update event. + +--echo + +SET GLOBAL EVENT_SCHEDULER = ON; +--source include/running_event_scheduler.inc + +--echo + +--echo # Waiting for the event scheduler to execute and drop event e1... + +let $wait_timeout = 4; +let $wait_condition = + SELECT COUNT(*) = 0 + FROM INFORMATION_SCHEMA.EVENTS + WHERE event_schema = 'events_test' AND event_name = 'e1'; +--source include/wait_condition.inc + +--echo + +--echo # Waiting for the event scheduler to execute and update event e2... + +let $wait_condition = + SELECT last_executed IS NOT NULL + FROM INFORMATION_SCHEMA.EVENTS + WHERE event_schema = 'events_test' AND event_name = 'e2'; +--source include/wait_condition.inc + +--echo + +SET GLOBAL EVENT_SCHEDULER = OFF; +--source include/check_events_off.inc + +--echo + +SELECT + event_name, + last_executed IS NULL, + definer +FROM INFORMATION_SCHEMA.EVENTS +WHERE event_schema = 'events_test'; + +--echo + +--error ER_EVENT_DOES_NOT_EXIST +DROP EVENT e1; + +--echo +--echo # Cleanup. +--echo + +DROP EVENT e2; + +--echo + +SET GLOBAL READ_ONLY = 0; + +--echo + +--disconnect u1_con +--disconnect root_con +--connection default + +--echo + +DROP USER mysqltest_u1@localhost; + +--echo +--echo ##################################################################### +--echo # +--echo # End of BUG#31111. +--echo # +--echo ##################################################################### +--echo + +# +# Bug#32633 Can not create any routine if SQL_MODE=no_engine_substitution +# +# Ensure that when new SQL modes are introduced, they are also added to +# the mysql.event table. +# + +--disable_warnings +drop procedure if exists p; +--enable_warnings +set @old_mode= @@sql_mode; +set @@sql_mode= cast(pow(2,32)-1 as unsigned integer); +create event e1 on schedule every 1 day do select 1; +select @@sql_mode into @full_mode; +set @@sql_mode= @old_mode; +select replace(@full_mode, 'ALLOW_INVALID_DATES', 'INVALID_DATES') into @full_mode; +select name from mysql.event where name = 'e1' and sql_mode = @full_mode; +drop event e1; + +# +# Bug#36540: CREATE EVENT and ALTER EVENT statements fail with large server_id +# + +SET @old_server_id = @@GLOBAL.server_id; +SET GLOBAL server_id = (1 << 32) - 1; +SELECT @@GLOBAL.server_id; +CREATE EVENT ev1 ON SCHEDULE EVERY 1 DAY DO SELECT 1; +SELECT event_name, originator FROM INFORMATION_SCHEMA.EVENTS; +DROP EVENT ev1; +SET GLOBAL server_id = @old_server_id; + +# +# Bug#11751148: show events shows events in other schema +# + +CREATE DATABASE event_test12; +USE event_test12; +CREATE EVENT ev1 ON SCHEDULE EVERY 1 DAY DO SELECT 1; +CREATE DATABASE event_test1; +USE event_test1; +# Following show events should not show ev1 +SHOW EVENTS; +DROP DATABASE event_test1; +DROP DATABASE event_test12; + +--echo # +--echo # Bug#12546938 (formerly known as bug#61005): +--echo # CREATE IF NOT EXIST EVENT WILL CREATE MULTIPLE RUNNING EVENTS +--echo # +USE events_test; +SET GLOBAL event_scheduler = ON; + +--disable_warnings +DROP TABLE IF EXISTS table_bug12546938; +DROP EVENT IF EXISTS event_Bug12546938; +--enable_warnings +CREATE TABLE table_bug12546938 (i INT); + +delimiter |; + +--echo # Create an event which will be executed with a small delay +--echo # and won't be automatically dropped after that. +CREATE EVENT event_Bug12546938 +ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 SECOND ON COMPLETION PRESERVE +ENABLE DO +BEGIN + INSERT INTO table_bug12546938 VALUES(1); +END +| + +--echo # Now try to create the same event using CREATE EVENT IF NOT EXISTS. +--echo # A warning should be emitted. A new event should not be created nor +--echo # the old event should be re-executed. +CREATE EVENT IF NOT EXISTS event_bug12546938 +ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 SECOND ON COMPLETION PRESERVE +ENABLE DO +BEGIN + INSERT INTO table_bug12546938 VALUES (1); +END +| + +delimiter ;| + +--echo # Wait until at least one instance of event is executed. +let $wait_condition= SELECT COUNT(*) FROM table_bug12546938; +--source include/wait_condition.inc + +--echo # Check that only one instance of our event was executed. +SELECT COUNT(*) FROM table_bug12546938; + +--echo # Clean-up. +DROP EVENT IF EXISTS event_Bug12546938; +DROP TABLE table_bug12546938; +SET GLOBAL EVENT_SCHEDULER = OFF; + +# +# Bug#11764334 - 57156: ALTER EVENT CHANGES THE EVENT STATUS +# +--disable_warnings +DROP DATABASE IF EXISTS event_test11764334; +--enable_warnings +CREATE DATABASE event_test11764334; +USE event_test11764334; +CREATE EVENT ev1 ON SCHEDULE EVERY 3 SECOND DISABLE DO SELECT 1; +--replace_column 9 # 10 # +SHOW EVENTS IN event_test11764334 WHERE NAME='ev1'; +ALTER EVENT ev1 ON SCHEDULE EVERY 4 SECOND; +--replace_column 9 # 10 # +SHOW EVENTS IN event_test11764334 WHERE NAME='ev1'; +DROP EVENT ev1; +DROP DATABASE event_test11764334; +USE test; +########################################################################### +# +# End of tests +# +# !!! KEEP this section AT THE END of this file !!! +# +########################################################################### + +# Ensure that all event executors have finished their work and cannot harm +# the next test. +--source include/no_running_events.inc + +DROP DATABASE events_test; +# Restore the original state i.e to how it was when test started +# (option with no argument in events_bugs-master.opt turns the scheduler on). +SET GLOBAL event_scheduler= 'ON'; +--source include/running_event_scheduler.inc +SET @@global.concurrent_insert= @concurrent_insert; +# THIS MUST BE THE LAST LINE in this file. diff --git a/mysql-test/suite/events/events_embedded.result b/mysql-test/suite/events/events_embedded.result new file mode 100644 index 00000000..1a02188f --- /dev/null +++ b/mysql-test/suite/events/events_embedded.result @@ -0,0 +1,2 @@ +set global event_scheduler=ON; +ERROR HY000: Unknown system variable 'event_scheduler' diff --git a/mysql-test/suite/events/events_embedded.test b/mysql-test/suite/events/events_embedded.test new file mode 100644 index 00000000..c607573b --- /dev/null +++ b/mysql-test/suite/events/events_embedded.test @@ -0,0 +1,4 @@ +--source include/is_embedded.inc + +--error ER_UNKNOWN_SYSTEM_VARIABLE +set global event_scheduler=ON; diff --git a/mysql-test/suite/events/events_grant.result b/mysql-test/suite/events/events_grant.result new file mode 100644 index 00000000..6441d7b3 --- /dev/null +++ b/mysql-test/suite/events/events_grant.result @@ -0,0 +1,149 @@ +CREATE DATABASE IF NOT EXISTS events_test; +use events_test; +CREATE EVENT one_event ON SCHEDULE EVERY 10 SECOND DO SELECT 123; +Warnings: +Warning 1105 Event scheduler is switched off, use SET GLOBAL event_scheduler=ON to enable it. +SHOW EVENTS; +Db Name Definer Time zone Type Execute at Interval value Interval field Starts Ends Status Originator character_set_client collation_connection Database Collation +events_test one_event root@localhost SYSTEM RECURRING NULL 10 # # NULL ENABLED 1 latin1 latin1_swedish_ci latin1_swedish_ci +SELECT 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; +EVENT_CATALOG EVENT_SCHEMA EVENT_NAME DEFINER EVENT_BODY EVENT_DEFINITION EVENT_TYPE EXECUTE_AT INTERVAL_VALUE INTERVAL_FIELD STATUS ON_COMPLETION EVENT_COMMENT +def events_test one_event root@localhost SQL SELECT 123 RECURRING NULL 10 SECOND ENABLED NOT PRESERVE +CREATE DATABASE events_test2; +CREATE USER ev_test@localhost; +GRANT ALL ON events_test.* to ev_test@localhost; +GRANT ALL ON events_test2.* to ev_test@localhost; +GRANT ALL ON test.* TO ev_test@localhost; +REVOKE EVENT ON events_test2.* FROM ev_test@localhost; +connect ev_con1,localhost,ev_test,,events_test2; +select "NEW CONNECTION"; +NEW CONNECTION +NEW CONNECTION +SELECT USER(), DATABASE(); +USER() DATABASE() +ev_test@localhost events_test2 +SHOW GRANTS; +Grants for ev_test@localhost +GRANT USAGE ON *.* TO `ev_test`@`localhost` +GRANT ALL PRIVILEGES ON `test`.* TO `ev_test`@`localhost` +GRANT ALL PRIVILEGES ON `events_test`.* TO `ev_test`@`localhost` +GRANT 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, SHOW CREATE ROUTINE ON `events_test2`.* TO `ev_test`@`localhost` +"Here comes an error:"; +SHOW EVENTS; +ERROR 42000: Access denied for user 'ev_test'@'localhost' to database 'events_test2' +USE events_test; +"We should see one event"; +SHOW EVENTS; +Db Name Definer Time zone Type Execute at Interval value Interval field Starts Ends Status Originator character_set_client collation_connection Database Collation +events_test one_event root@localhost SYSTEM RECURRING NULL 10 # # NULL ENABLED 1 latin1 latin1_swedish_ci latin1_swedish_ci +SELECT CONCAT("Let's create some new events from the name of ", USER()); +CONCAT("Let's create some new events from the name of ", USER()) +Let's create some new events from the name of ev_test@localhost +CREATE EVENT one_event ON SCHEDULE EVERY 20 SECOND DO SELECT 123; +ERROR HY000: Event 'one_event' already exists +CREATE EVENT two_event ON SCHEDULE EVERY 20 SECOND ON COMPLETION NOT PRESERVE COMMENT "two event" DO SELECT 123; +Warnings: +Warning 1105 Event scheduler is switched off, use SET GLOBAL event_scheduler=ON to enable it. +CREATE EVENT three_event ON SCHEDULE EVERY 20 SECOND ON COMPLETION PRESERVE COMMENT "three event" DO SELECT 123; +Warnings: +Warning 1105 Event scheduler is switched off, use SET GLOBAL event_scheduler=ON to enable it. +"Now we should see 3 events:"; +SHOW EVENTS; +Db Name Definer Time zone Type Execute at Interval value Interval field Starts Ends Status Originator character_set_client collation_connection Database Collation +events_test one_event root@localhost SYSTEM RECURRING NULL 10 # # NULL ENABLED 1 latin1 latin1_swedish_ci latin1_swedish_ci +events_test three_event ev_test@localhost SYSTEM RECURRING NULL 20 # # NULL ENABLED 1 latin1 latin1_swedish_ci latin1_swedish_ci +events_test two_event ev_test@localhost SYSTEM RECURRING NULL 20 # # NULL ENABLED 1 latin1 latin1_swedish_ci latin1_swedish_ci +"This should show us only 2 events:"; +SHOW EVENTS LIKE 't%event'; +Db Name Definer Time zone Type Execute at Interval value Interval field Starts Ends Status Originator character_set_client collation_connection Database Collation +events_test three_event ev_test@localhost SYSTEM RECURRING NULL 20 # # NULL ENABLED 1 latin1 latin1_swedish_ci latin1_swedish_ci +events_test two_event ev_test@localhost SYSTEM RECURRING NULL 20 # # NULL ENABLED 1 latin1 latin1_swedish_ci latin1_swedish_ci +"This should show us no events:"; +SHOW EVENTS FROM test LIKE '%'; +Db Name Definer Time zone Type Execute at Interval value Interval field Starts Ends Status Originator character_set_client collation_connection Database Collation +connection default; +GRANT EVENT ON events_test2.* TO ev_test@localhost; +connection ev_con1; +USE events_test2; +CREATE EVENT four_event ON SCHEDULE EVERY 20 SECOND DO SELECT 42; +Warnings: +Warning 1105 Event scheduler is switched off, use SET GLOBAL event_scheduler=ON to enable it. +connection default; +USE events_test; +"We should see 4 events : one_event, two_event, three_event & four_event" +SELECT 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; +EVENT_CATALOG EVENT_SCHEMA EVENT_NAME DEFINER EVENT_BODY EVENT_DEFINITION EVENT_TYPE EXECUTE_AT INTERVAL_VALUE INTERVAL_FIELD STATUS ON_COMPLETION EVENT_COMMENT +def events_test one_event root@localhost SQL SELECT 123 RECURRING NULL 10 SECOND ENABLED NOT PRESERVE +def events_test three_event ev_test@localhost SQL SELECT 123 RECURRING NULL 20 SECOND ENABLED PRESERVE three event +def events_test two_event ev_test@localhost SQL SELECT 123 RECURRING NULL 20 SECOND ENABLED NOT PRESERVE two event +def events_test2 four_event ev_test@localhost SQL SELECT 42 RECURRING NULL 20 SECOND ENABLED NOT PRESERVE +DROP DATABASE events_test2; +"We should see 3 events : one_event, two_event, three_event" +SELECT 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; +EVENT_CATALOG EVENT_SCHEMA EVENT_NAME DEFINER EVENT_BODY EVENT_DEFINITION EVENT_TYPE EXECUTE_AT INTERVAL_VALUE INTERVAL_FIELD STATUS ON_COMPLETION EVENT_COMMENT +def events_test one_event root@localhost SQL SELECT 123 RECURRING NULL 10 SECOND ENABLED NOT PRESERVE +def events_test three_event ev_test@localhost SQL SELECT 123 RECURRING NULL 20 SECOND ENABLED PRESERVE three event +def events_test two_event ev_test@localhost SQL SELECT 123 RECURRING NULL 20 SECOND ENABLED NOT PRESERVE two event +connection default; +CREATE DATABASE events_test2; +USE events_test2; +CREATE EVENT five_event ON SCHEDULE EVERY 20 SECOND DO SELECT 42; +Warnings: +Warning 1105 Event scheduler is switched off, use SET GLOBAL event_scheduler=ON to enable it. +connection ev_con1; +"Should see 4 events - one, two, three & five" +SELECT 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; +EVENT_CATALOG EVENT_SCHEMA EVENT_NAME DEFINER EVENT_BODY EVENT_DEFINITION EVENT_TYPE EXECUTE_AT INTERVAL_VALUE INTERVAL_FIELD STATUS ON_COMPLETION EVENT_COMMENT +def events_test one_event root@localhost SQL SELECT 123 RECURRING NULL 10 SECOND ENABLED NOT PRESERVE +def events_test three_event ev_test@localhost SQL SELECT 123 RECURRING NULL 20 SECOND ENABLED PRESERVE three event +def events_test two_event ev_test@localhost SQL SELECT 123 RECURRING NULL 20 SECOND ENABLED NOT PRESERVE two event +def events_test2 five_event root@localhost SQL SELECT 42 RECURRING NULL 20 SECOND ENABLED NOT PRESERVE +connection default; +REVOKE EVENT ON events_test2.* FROM ev_test@localhost; +connection ev_con1; +USE test; +"Should see 3 events - one, two & three" +SELECT 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; +EVENT_CATALOG EVENT_SCHEMA EVENT_NAME DEFINER EVENT_BODY EVENT_DEFINITION EVENT_TYPE EXECUTE_AT INTERVAL_VALUE INTERVAL_FIELD STATUS ON_COMPLETION EVENT_COMMENT +def events_test one_event root@localhost SQL SELECT 123 RECURRING NULL 10 SECOND ENABLED NOT PRESERVE +def events_test three_event ev_test@localhost SQL SELECT 123 RECURRING NULL 20 SECOND ENABLED PRESERVE three event +def events_test two_event ev_test@localhost SQL SELECT 123 RECURRING NULL 20 SECOND ENABLED NOT PRESERVE two event +"Let's test ALTER EVENT which changes the definer" +USE events_test; +ALTER EVENT one_event ON SCHEDULE EVERY 10 SECOND; +"The definer should be ev_test@localhost" +SELECT 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'; +EVENT_CATALOG EVENT_SCHEMA EVENT_NAME DEFINER EVENT_BODY EVENT_DEFINITION EVENT_TYPE EXECUTE_AT INTERVAL_VALUE INTERVAL_FIELD STATUS ON_COMPLETION EVENT_COMMENT +def events_test one_event ev_test@localhost SQL SELECT 123 RECURRING NULL 10 SECOND ENABLED NOT PRESERVE +connection default; +USE events_test; +ALTER EVENT one_event COMMENT "comment"; +connection ev_con1; +"The definer should be root@localhost" +SELECT 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'; +EVENT_CATALOG EVENT_SCHEMA EVENT_NAME DEFINER EVENT_BODY EVENT_DEFINITION EVENT_TYPE EXECUTE_AT INTERVAL_VALUE INTERVAL_FIELD STATUS ON_COMPLETION EVENT_COMMENT +def events_test one_event root@localhost SQL SELECT 123 RECURRING NULL 10 SECOND ENABLED NOT PRESERVE comment +ALTER EVENT one_event DO SELECT 12; +"The definer should be ev_test@localhost" +SELECT 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'; +EVENT_CATALOG EVENT_SCHEMA EVENT_NAME DEFINER EVENT_BODY EVENT_DEFINITION EVENT_TYPE EXECUTE_AT INTERVAL_VALUE INTERVAL_FIELD STATUS ON_COMPLETION EVENT_COMMENT +def events_test one_event ev_test@localhost SQL SELECT 12 RECURRING NULL 10 SECOND ENABLED NOT PRESERVE comment +connection default; +"make the definer again root@localhost" +ALTER EVENT one_event COMMENT "new comment"; +connection ev_con1; +"test DROP by another user" +DROP EVENT one_event; +connection default; +"One event should not be there" +SELECT 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; +EVENT_CATALOG EVENT_SCHEMA EVENT_NAME DEFINER EVENT_BODY EVENT_DEFINITION EVENT_TYPE EXECUTE_AT INTERVAL_VALUE INTERVAL_FIELD STATUS ON_COMPLETION EVENT_COMMENT +def events_test three_event ev_test@localhost SQL SELECT 123 RECURRING NULL 20 SECOND ENABLED PRESERVE three event +def events_test two_event ev_test@localhost SQL SELECT 123 RECURRING NULL 20 SECOND ENABLED NOT PRESERVE two event +def events_test2 five_event root@localhost SQL SELECT 42 RECURRING NULL 20 SECOND ENABLED NOT PRESERVE +connection ev_con1; +disconnect ev_con1; +connection default; +DROP USER ev_test@localhost; +DROP DATABASE events_test2; +DROP DATABASE events_test; diff --git a/mysql-test/suite/events/events_grant.test b/mysql-test/suite/events/events_grant.test new file mode 100644 index 00000000..2fe39a6c --- /dev/null +++ b/mysql-test/suite/events/events_grant.test @@ -0,0 +1,117 @@ +# Can't test with embedded server that doesn't support grants +-- source include/not_embedded.inc +--source include/default_charset.inc + +--disable_service_connection +CREATE DATABASE IF NOT EXISTS events_test; +use events_test; +# +# Events grants test begin +# +CREATE EVENT one_event ON SCHEDULE EVERY 10 SECOND DO SELECT 123; +--replace_column 8 # 9 # +SHOW EVENTS; +SELECT 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; +CREATE DATABASE events_test2; +CREATE USER ev_test@localhost; +GRANT ALL ON events_test.* to ev_test@localhost; +GRANT ALL ON events_test2.* to ev_test@localhost; +GRANT ALL ON test.* TO ev_test@localhost; +REVOKE EVENT ON events_test2.* FROM ev_test@localhost; +#now we are on con1 +connect (ev_con1,localhost,ev_test,,events_test2); +select "NEW CONNECTION"; +SELECT USER(), DATABASE(); +SHOW GRANTS; +--echo "Here comes an error:"; +#NO EVENT_ACL on events_test2 +--error 1044 +SHOW EVENTS; +USE events_test; +--echo "We should see one event"; +--replace_column 8 # 9 # +SHOW EVENTS; +#now create an event with the same name but we are different user +SELECT CONCAT("Let's create some new events from the name of ", USER()); +--error ER_EVENT_ALREADY_EXISTS +CREATE EVENT one_event ON SCHEDULE EVERY 20 SECOND DO SELECT 123; +CREATE EVENT two_event ON SCHEDULE EVERY 20 SECOND ON COMPLETION NOT PRESERVE COMMENT "two event" DO SELECT 123; +CREATE EVENT three_event ON SCHEDULE EVERY 20 SECOND ON COMPLETION PRESERVE COMMENT "three event" DO SELECT 123; + +--echo "Now we should see 3 events:"; +--replace_column 8 # 9 # +SHOW EVENTS; + +--echo "This should show us only 2 events:"; +--replace_column 8 # 9 # +SHOW EVENTS LIKE 't%event'; + +--echo "This should show us no events:"; +--replace_column 8 # 9 # +SHOW EVENTS FROM test LIKE '%'; +#ok, we are back +connection default; +GRANT EVENT ON events_test2.* TO ev_test@localhost; +connection ev_con1; +USE events_test2; +CREATE EVENT four_event ON SCHEDULE EVERY 20 SECOND DO SELECT 42; +connection default; +USE events_test; +--echo "We should see 4 events : one_event, two_event, three_event & four_event" +SELECT 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; +DROP DATABASE events_test2; +--echo "We should see 3 events : one_event, two_event, three_event" +SELECT 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; + +connection default; +CREATE DATABASE events_test2; +USE events_test2; +CREATE EVENT five_event ON SCHEDULE EVERY 20 SECOND DO SELECT 42; + +connection ev_con1; +--echo "Should see 4 events - one, two, three & five" +SELECT 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; +connection default; +REVOKE EVENT ON events_test2.* FROM ev_test@localhost; +connection ev_con1; +USE test; +--echo "Should see 3 events - one, two & three" +SELECT 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; +--echo "Let's test ALTER EVENT which changes the definer" +USE events_test; +ALTER EVENT one_event ON SCHEDULE EVERY 10 SECOND; +--echo "The definer should be ev_test@localhost" +SELECT 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'; +connection default; +USE events_test; +ALTER EVENT one_event COMMENT "comment"; +connection ev_con1; +--echo "The definer should be root@localhost" +SELECT 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'; +ALTER EVENT one_event DO SELECT 12; +--echo "The definer should be ev_test@localhost" +SELECT 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'; +connection default; +--echo "make the definer again root@localhost" +ALTER EVENT one_event COMMENT "new comment"; +connection ev_con1; +--echo "test DROP by another user" +DROP EVENT one_event; +connection default; +--echo "One event should not be there" +SELECT 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; +connection ev_con1; +disconnect ev_con1; +--source include/wait_until_disconnected.inc +connection default; +DROP USER ev_test@localhost; +DROP DATABASE events_test2; + +# +# End of tests +# + +--source include/check_events_off.inc + +DROP DATABASE events_test; +--enable_service_connection diff --git a/mysql-test/suite/events/events_logs_tests-master.opt b/mysql-test/suite/events/events_logs_tests-master.opt new file mode 100644 index 00000000..73d26b72 --- /dev/null +++ b/mysql-test/suite/events/events_logs_tests-master.opt @@ -0,0 +1 @@ +--slow-query-log --log-output=table --general-log --general-log-file="" --slow-query-log-file="" diff --git a/mysql-test/suite/events/events_logs_tests.result b/mysql-test/suite/events/events_logs_tests.result new file mode 100644 index 00000000..d332767a --- /dev/null +++ b/mysql-test/suite/events/events_logs_tests.result @@ -0,0 +1,74 @@ +set @save_long_query_time=@@long_query_time; +drop database if exists events_test; +create database if not exists events_test; +use events_test; + +We use procedure here because its statements won't be +logged into the general log. If we had used normal select +that are logged in different ways depending on whether the +test suite is run in normal mode or with --ps-protocol + +create procedure select_general_log() +begin +select user_host, argument from mysql.general_log +where argument like '%events_logs_test%' AND +(command_type = 'Query' OR command_type = 'Execute'); +end| + +Check that general query log works, but sub-statements +of the stored procedure do not leave traces in it. + +truncate mysql.general_log; +select 'events_logs_tests' as outside_event; +outside_event +events_logs_tests +call select_general_log(); +user_host argument +USER_HOST select 'events_logs_tests' as outside_event + +Check that unlike sub-statements of stored procedures, +sub-statements of events are present in the general log. + +set global event_scheduler=on; +truncate mysql.general_log; +create event ev_log_general on schedule at now() on completion not preserve do select 'events_logs_test' as inside_event; +call select_general_log(); +user_host argument +USER_HOST create event ev_log_general on schedule at now() on completion not preserve do select 'events_logs_test' as inside_event +USER_HOST select 'events_logs_test' as inside_event + +Check slow query log + +Ensure that slow logging is on +show variables like 'slow_query_log'; +Variable_name Value +slow_query_log ON + +Demonstrate that session value has no effect + +set @@session.long_query_time=1; +set @@global.long_query_time=300; +truncate mysql.slow_log; +create event ev_log_general on schedule at now() on completion not preserve +do select 'events_logs_test' as inside_event, sleep(1.5); + +Nothing should be logged + +select user_host, db, sql_text from mysql.slow_log +where sql_text like 'select \'events_logs_test\'%'; +user_host db sql_text +set @@global.long_query_time=1; +truncate mysql.slow_log; +create event ev_log_general on schedule at now() on completion not preserve +do select 'events_logs_test' as inside_event, sleep(1.5); + +Event sub-statement should be logged. + +select user_host, db, sql_text from mysql.slow_log +where sql_text like 'select \'events_logs_test\'%'; +user_host db sql_text +USER_HOST events_test select 'events_logs_test' as inside_event, sleep(1.5) +drop database events_test; +set global event_scheduler=off; +set @@global.long_query_time=@save_long_query_time; +set @@session.long_query_time=@save_long_query_time; diff --git a/mysql-test/suite/events/events_logs_tests.test b/mysql-test/suite/events/events_logs_tests.test new file mode 100644 index 00000000..f7445352 --- /dev/null +++ b/mysql-test/suite/events/events_logs_tests.test @@ -0,0 +1,93 @@ +# Can't test with embedded server that doesn't support grants +-- source include/not_embedded.inc +-- source include/no_view_protocol.inc + +set @save_long_query_time=@@long_query_time; +--disable_warnings +drop database if exists events_test; +--enable_warnings +create database if not exists events_test; +use events_test; +--echo +--echo We use procedure here because its statements won't be +--echo logged into the general log. If we had used normal select +--echo that are logged in different ways depending on whether the +--echo test suite is run in normal mode or with --ps-protocol +--echo +delimiter |; +create procedure select_general_log() +begin + select user_host, argument from mysql.general_log + where argument like '%events_logs_test%' AND + (command_type = 'Query' OR command_type = 'Execute'); +end| +delimiter ;| +--echo +--echo Check that general query log works, but sub-statements +--echo of the stored procedure do not leave traces in it. +--echo +truncate mysql.general_log; +# Logging format in ps protocol is slightly different +--disable_ps_protocol +select 'events_logs_tests' as outside_event; +--enable_ps_protocol +--replace_column 1 USER_HOST +call select_general_log(); +--echo +--echo Check that unlike sub-statements of stored procedures, +--echo sub-statements of events are present in the general log. +--echo +set global event_scheduler=on; +truncate mysql.general_log; +create event ev_log_general on schedule at now() on completion not preserve do select 'events_logs_test' as inside_event; +--let $wait_condition=select count(*)=0 from information_schema.events where event_name='ev_log_general' +--source include/wait_condition.inc +--replace_column 1 USER_HOST +call select_general_log(); + +--echo +--echo Check slow query log +--echo +--echo Ensure that slow logging is on +show variables like 'slow_query_log'; +--echo +--echo Demonstrate that session value has no effect +--echo +set @@session.long_query_time=1; +set @@global.long_query_time=300; +truncate mysql.slow_log; +create event ev_log_general on schedule at now() on completion not preserve + do select 'events_logs_test' as inside_event, sleep(1.5); +--let $wait_condition=select count(*)=0 from information_schema.events where event_name='ev_log_general' +--source include/wait_condition.inc +--echo +--echo Nothing should be logged +--echo +--replace_column 1 USER_HOST +select user_host, db, sql_text from mysql.slow_log + where sql_text like 'select \'events_logs_test\'%'; +set @@global.long_query_time=1; +truncate mysql.slow_log; +create event ev_log_general on schedule at now() on completion not preserve + do select 'events_logs_test' as inside_event, sleep(1.5); +--let $wait_condition=select count(*)=0 from information_schema.events where event_name='ev_log_general' +--source include/wait_condition.inc +--echo +--echo Event sub-statement should be logged. +--echo +--replace_column 1 USER_HOST +select user_host, db, sql_text from mysql.slow_log + where sql_text like 'select \'events_logs_test\'%'; + +drop database events_test; +set global event_scheduler=off; +set @@global.long_query_time=@save_long_query_time; +set @@session.long_query_time=@save_long_query_time; + +# +# Safety +# +let $wait_condition= + select count(*) = 0 from information_schema.processlist + where db='events_test' and command = 'Connect' and user=current_user(); +--source include/wait_condition.inc diff --git a/mysql-test/suite/events/events_microsec.result b/mysql-test/suite/events/events_microsec.result new file mode 100644 index 00000000..2a9f3587 --- /dev/null +++ b/mysql-test/suite/events/events_microsec.result @@ -0,0 +1,13 @@ +create database if not exists events_test; +use events_test; +CREATE EVENT micro_test ON SCHEDULE EVERY 100 MICROSECOND DO SELECT 1; +ERROR 42000: This version of MariaDB doesn't yet support 'MICROSECOND' +CREATE EVENT micro_test ON SCHEDULE EVERY 100 DAY_MICROSECOND DO SELECT 1; +ERROR 42000: This version of MariaDB doesn't yet support 'MICROSECOND' +CREATE EVENT micro_test ON SCHEDULE EVERY 100 HOUR_MICROSECOND DO SELECT 1; +ERROR 42000: This version of MariaDB doesn't yet support 'MICROSECOND' +CREATE EVENT micro_test ON SCHEDULE EVERY 100 MINUTE_MICROSECOND DO SELECT 1; +ERROR 42000: This version of MariaDB doesn't yet support 'MICROSECOND' +CREATE EVENT micro_test ON SCHEDULE EVERY 100 SECOND_MICROSECOND DO SELECT 1; +ERROR 42000: This version of MariaDB doesn't yet support 'MICROSECOND' +drop database events_test; diff --git a/mysql-test/suite/events/events_microsec.test b/mysql-test/suite/events/events_microsec.test new file mode 100644 index 00000000..3e9abf6e --- /dev/null +++ b/mysql-test/suite/events/events_microsec.test @@ -0,0 +1,21 @@ +# Can't test with embedded server that doesn't support grants +-- source include/not_embedded.inc + +--disable_warnings +create database if not exists events_test; +--enable_warnings + +use events_test; + +--error ER_NOT_SUPPORTED_YET +CREATE EVENT micro_test ON SCHEDULE EVERY 100 MICROSECOND DO SELECT 1; +--error ER_NOT_SUPPORTED_YET +CREATE EVENT micro_test ON SCHEDULE EVERY 100 DAY_MICROSECOND DO SELECT 1; +--error ER_NOT_SUPPORTED_YET +CREATE EVENT micro_test ON SCHEDULE EVERY 100 HOUR_MICROSECOND DO SELECT 1; +--error ER_NOT_SUPPORTED_YET +CREATE EVENT micro_test ON SCHEDULE EVERY 100 MINUTE_MICROSECOND DO SELECT 1; +--error ER_NOT_SUPPORTED_YET +CREATE EVENT micro_test ON SCHEDULE EVERY 100 SECOND_MICROSECOND DO SELECT 1; + +drop database events_test; diff --git a/mysql-test/suite/events/events_restart-master.opt b/mysql-test/suite/events/events_restart-master.opt new file mode 100644 index 00000000..f93413a6 --- /dev/null +++ b/mysql-test/suite/events/events_restart-master.opt @@ -0,0 +1 @@ +--event-scheduler diff --git a/mysql-test/suite/events/events_restart.result b/mysql-test/suite/events/events_restart.result new file mode 100644 index 00000000..6099414e --- /dev/null +++ b/mysql-test/suite/events/events_restart.result @@ -0,0 +1,145 @@ +call mtr.add_suppression('Column count of mysql.event is wrong. Expected .*, found .*\. The table is probably corrupted'); +call mtr.add_suppression('Event Scheduler: An error occurred when initializing system tables. Disabling the Event Scheduler'); +set global event_scheduler=off; +create database events_test; +use events_test; +create table execution_log(name char(10)); +create event abc1 on schedule every 1 second do +insert into execution_log value('abc1'); +Warnings: +Warning 1105 Event scheduler is switched off, use SET GLOBAL event_scheduler=ON to enable it. +create event abc2 on schedule every 1 second do +insert into execution_log value('abc2'); +Warnings: +Warning 1105 Event scheduler is switched off, use SET GLOBAL event_scheduler=ON to enable it. +create event abc3 on schedule every 1 second do +insert into execution_log value('abc3'); +Warnings: +Warning 1105 Event scheduler is switched off, use SET GLOBAL event_scheduler=ON to enable it. +create table event_like like mysql.event; +insert into event_like select * from mysql.event; +alter table mysql.event +change column body body longtext character set utf8 collate utf8_bin; +# Now we restart the server +call mtr.add_suppression("Incorrect definition of table mysql.event:.*"); +# restart +use events_test; +select @@event_scheduler; +@@event_scheduler +OFF +show events; +ERROR HY000: Cannot proceed, because event scheduler is disabled +select event_name from information_schema.events; +ERROR HY000: Cannot proceed, because event scheduler is disabled +show create event intact_check; +ERROR HY000: Cannot proceed, because event scheduler is disabled +drop event no_such_event; +ERROR HY000: Cannot proceed, because event scheduler is disabled +create event intact_check_1 on schedule every 5 hour do select 5; +ERROR HY000: Cannot proceed, because event scheduler is disabled +alter event intact_check_1 on schedule every 8 hour do select 8; +ERROR HY000: Cannot proceed, because event scheduler is disabled +alter event intact_check_1 rename to intact_check_2; +ERROR HY000: Cannot proceed, because event scheduler is disabled +drop event intact_check_1; +ERROR HY000: Cannot proceed, because event scheduler is disabled +drop event intact_check_2; +ERROR HY000: Cannot proceed, because event scheduler is disabled +drop event intact_check; +ERROR HY000: Cannot proceed, because event scheduler is disabled +set global event_scheduler=on; +ERROR HY000: Event Scheduler: An error occurred when initializing system tables. Disabling the Event Scheduler. +set global event_scheduler=off; +ERROR HY000: Event Scheduler: An error occurred when initializing system tables. Disabling the Event Scheduler. +show variables like 'event_scheduler'; +Variable_name Value +event_scheduler OFF +# Make sure that we still can create and drop databases, +# and no warnings are produced. +drop database if exists mysqltest_database_not_exists; +Warnings: +Note 1008 Can't drop database 'mysqltest_database_not_exists'; database doesn't exist +create database mysqltest_db1; +drop database mysqltest_db1; +Warnings: +Error 1545 Failed to open mysql.event +# Restore the original mysql.event table +drop table mysql.event; +rename table event_like to mysql.event; +# check that we can now enable events without restart +set global event_scheduler=original; +Warnings: +Note 1408 Event Scheduler: Loaded 3 events +select @@global.event_scheduler; +@@global.event_scheduler +ON +set global event_scheduler=on; +select @@global.event_scheduler; +@@global.event_scheduler +ON +show events; +Db Name Definer Time zone Type Execute at Interval value Interval field Starts Ends Status Originator character_set_client collation_connection Database Collation +events_test abc1 root@localhost SYSTEM RECURRING # 1 SECOND # # ENABLED 1 latin1 latin1_swedish_ci latin1_swedish_ci +events_test abc2 root@localhost SYSTEM RECURRING # 1 SECOND # # ENABLED 1 latin1 latin1_swedish_ci latin1_swedish_ci +events_test abc3 root@localhost SYSTEM RECURRING # 1 SECOND # # ENABLED 1 latin1 latin1_swedish_ci latin1_swedish_ci +# Now let's restart the server again +# restart +use events_test; +select @@event_scheduler; +@@event_scheduler +ON +drop table execution_log; +drop database events_test; +# +# Test for bug#11748899 -- EVENT SET TO DISABLED AND ON COMPLETION +# NOT PRESERVE IS DELETED AT SERVER +# +SELECT @@event_scheduler; +@@event_scheduler +ON +USE test; +DROP EVENT IF EXISTS e1; +CREATE EVENT e1 ON SCHEDULE EVERY 1 SECOND DISABLE DO SELECT 1; +SHOW EVENTS; +Db Name Definer Time zone Type Execute at Interval value Interval field Starts Ends Status Originator character_set_client collation_connection Database Collation +test e1 root@localhost SYSTEM RECURRING # 1 SECOND # # DISABLED 1 latin1 latin1_swedish_ci latin1_swedish_ci +# Now we restart the server +# restart +USE test; +SELECT @@event_scheduler; +@@event_scheduler +ON +SHOW EVENTS; +Db Name Definer Time zone Type Execute at Interval value Interval field Starts Ends Status Originator character_set_client collation_connection Database Collation +test e1 root@localhost SYSTEM RECURRING # 1 SECOND # # DISABLED 1 latin1 latin1_swedish_ci latin1_swedish_ci +DROP EVENT e1; +# end test for bug#11748899 +# +# Test for MDEV-21758 Events switched randomly to SLAVESIDE_DISABLED +# +create event ev on schedule every 1 minute do set @a= 1; +select name, originator, status from mysql.event; +name originator status +ev 1 ENABLED +# +# Restarting server with server_id=100 +# +# restart: --server-id=100 +select @@global.server_id; +@@global.server_id +100 +select name, originator, status from mysql.event; +name originator status +ev 1 ENABLED +set global server_id= 1; +# +# Restarting server with the original server_id=1 +# +# restart +select @@global.server_id; +@@global.server_id +1 +select name, originator, status from mysql.event; +name originator status +ev 1 ENABLED +drop event ev; diff --git a/mysql-test/suite/events/events_restart.test b/mysql-test/suite/events/events_restart.test new file mode 100644 index 00000000..e1db3d0c --- /dev/null +++ b/mysql-test/suite/events/events_restart.test @@ -0,0 +1,172 @@ +# Can't test with embedded server that doesn't support grants +-- source include/not_embedded.inc +call mtr.add_suppression('Column count of mysql.event is wrong. Expected .*, found .*\. The table is probably corrupted'); +call mtr.add_suppression('Event Scheduler: An error occurred when initializing system tables. Disabling the Event Scheduler'); + +let $collation_server=`select @@collation_server`; +# +# Test that when the server is restarted, it checks mysql.event table, +# and disables the scheduler if it's not up to date. +# + +# Switch off the scheduler for now. +set global event_scheduler=off; + +create database events_test; +use events_test; +create table execution_log(name char(10)); + +create event abc1 on schedule every 1 second do + insert into execution_log value('abc1'); +create event abc2 on schedule every 1 second do + insert into execution_log value('abc2'); +create event abc3 on schedule every 1 second do + insert into execution_log value('abc3'); +# +# There are various conditions when a server would regard mysql.event +# table as damaged: +# - the table has more column than specified in the compiled in value, but +# the version of the server which created the table is the same +# - the column count in the table is less than the compiled in value +# - some column has an incompatible type specification (for what is an +# incompatible type specification please refer to the comments in the source +# +# Unfortunately, in order to test a condition, we need to restart the +# server. Therefore, here we test only one simple case: changing the data +# type of the 'body' field to blob. +# +# First, let's do a backup to not depend on actual definition of mysql.event +create table event_like like mysql.event; +insert into event_like select * from mysql.event; +# Now let's alter the table and restart the server +alter table mysql.event + change column body body longtext character set utf8 collate utf8_bin; +--echo # Now we restart the server + +call mtr.add_suppression("Incorrect definition of table mysql.event:.*"); +--source include/restart_mysqld.inc + +use events_test; +# Event scheduler should be disabled: the system tables are damaged +select @@event_scheduler; +# Try various Event Scheduler operation and check the output. +--error ER_EVENTS_DB_ERROR +show events; +--error ER_EVENTS_DB_ERROR +select event_name from information_schema.events; +--error ER_EVENTS_DB_ERROR +show create event intact_check; +--error ER_EVENTS_DB_ERROR +drop event no_such_event; +--error ER_EVENTS_DB_ERROR +create event intact_check_1 on schedule every 5 hour do select 5; +--error ER_EVENTS_DB_ERROR +alter event intact_check_1 on schedule every 8 hour do select 8; +--error ER_EVENTS_DB_ERROR +alter event intact_check_1 rename to intact_check_2; +--error ER_EVENTS_DB_ERROR +drop event intact_check_1; +--error ER_EVENTS_DB_ERROR +drop event intact_check_2; +--error ER_EVENTS_DB_ERROR +drop event intact_check; +--error ER_STARTUP +set global event_scheduler=on; +--error ER_STARTUP +set global event_scheduler=off; +show variables like 'event_scheduler'; +--echo # Make sure that we still can create and drop databases, +--echo # and no warnings are produced. +drop database if exists mysqltest_database_not_exists; +create database mysqltest_db1; +drop database mysqltest_db1; +--echo # Restore the original mysql.event table +drop table mysql.event; +rename table event_like to mysql.event; + +--echo # check that we can now enable events without restart +set global event_scheduler=original; +select @@global.event_scheduler; +set global event_scheduler=on; +select @@global.event_scheduler; +--sorted_result +--replace_column 6 # 9 # 10 # +--replace_result $collation_server latin1_swedish_ci +show events; + +--echo # Now let's restart the server again + +--source include/restart_mysqld.inc + +# We need this file primarily to make sure that the scheduler is restarted +# and enabled after we have restored mysql.event table. +# This is the final step of the "cleanup". +# +# Make sure also that events are executed OK after restart, just in case. +use events_test; +# Make sure the scheduler was started successfully +select @@event_scheduler; +let $wait_condition=select count(distinct name)=3 from execution_log; +--source include/wait_condition.inc +drop table execution_log; +# Will drop all events +drop database events_test; + +let $wait_condition= + select count(*) = 0 from information_schema.processlist + where db='events_test' and command = 'Connect' and user=current_user(); +--source include/wait_condition.inc + +--echo # +--echo # Test for bug#11748899 -- EVENT SET TO DISABLED AND ON COMPLETION +--echo # NOT PRESERVE IS DELETED AT SERVER +--echo # +SELECT @@event_scheduler; +USE test; +--disable_warnings +DROP EVENT IF EXISTS e1; +--enable_warnings +CREATE EVENT e1 ON SCHEDULE EVERY 1 SECOND DISABLE DO SELECT 1; +--replace_column 6 # 9 # 10 # +SHOW EVENTS; + +--echo # Now we restart the server +--source include/restart_mysqld.inc +USE test; +SELECT @@event_scheduler; +--replace_column 6 # 9 # 10 # +SHOW EVENTS; +DROP EVENT e1; + +--echo # end test for bug#11748899 + +--echo # +--echo # Test for MDEV-21758 Events switched randomly to SLAVESIDE_DISABLED +--echo # + +create event ev on schedule every 1 minute do set @a= 1; +select name, originator, status from mysql.event; + +--let $server_id= `SELECT @@global.server_id` + +--echo # +--echo # Restarting server with server_id=100 +--echo # +--let $restart_parameters= --server-id=100 +--source include/restart_mysqld.inc + +select @@global.server_id; +select name, originator, status from mysql.event; +--eval set global server_id= $server_id + +--echo # +--echo # Restarting server with the original server_id=$server_id +--echo # +--let $restart_parameters= +--source include/restart_mysqld.inc + +select @@global.server_id; +select name, originator, status from mysql.event; + +# Cleanup +drop event ev; diff --git a/mysql-test/suite/events/events_scheduling.result b/mysql-test/suite/events/events_scheduling.result new file mode 100644 index 00000000..1739c607 --- /dev/null +++ b/mysql-test/suite/events/events_scheduling.result @@ -0,0 +1,106 @@ +call mtr.add_suppression("Event Scheduler: .* Table 'events_test.t1' doesn't exist"); +CREATE DATABASE IF NOT EXISTS events_test; +USE events_test; +SET @event_scheduler=@@global.event_scheduler; +SET GLOBAL event_scheduler=OFF; +Try again to make sure it's allowed +SET GLOBAL event_scheduler=OFF; +SHOW VARIABLES LIKE 'event_scheduler'; +Variable_name Value +event_scheduler OFF +SET GLOBAL event_scheduler=1; +SHOW VARIABLES LIKE 'event_scheduler'; +Variable_name Value +event_scheduler ON +SET GLOBAL event_scheduler=0; +SHOW VARIABLES LIKE 'event_scheduler'; +Variable_name Value +event_scheduler OFF +SET GLOBAL event_scheduler=ON; +Try again to make sure it's allowed +SET GLOBAL event_scheduler=ON; +SHOW VARIABLES LIKE 'event_scheduler'; +Variable_name Value +event_scheduler ON +SET GLOBAL event_scheduler=DISABLED; +ERROR 42000: Variable 'event_scheduler' can't be set to the value of 'DISABLED' +SHOW VARIABLES LIKE 'event_scheduler'; +Variable_name Value +event_scheduler ON +SET GLOBAL event_scheduler=-1; +ERROR 42000: Variable 'event_scheduler' can't be set to the value of '-1' +SHOW VARIABLES LIKE 'event_scheduler'; +Variable_name Value +event_scheduler ON +SET GLOBAL event_scheduler=2; +ERROR 42000: Variable 'event_scheduler' can't be set to the value of '2' +SHOW VARIABLES LIKE 'event_scheduler'; +Variable_name Value +event_scheduler ON +SET GLOBAL event_scheduler=5; +ERROR 42000: Variable 'event_scheduler' can't be set to the value of '5' +SHOW VARIABLES LIKE 'event_scheduler'; +Variable_name Value +event_scheduler ON +CREATE TABLE table_1(a int); +CREATE TABLE table_2(a int); +CREATE TABLE table_3(a int); +CREATE TABLE table_4(a int); +SET GLOBAL event_scheduler=ON; +CREATE EVENT event_1 ON SCHEDULE EVERY 2 SECOND +DO +INSERT INTO table_1 VALUES (1); +CREATE EVENT event_2 ON SCHEDULE EVERY 1 SECOND +ENDS NOW() + INTERVAL 6 SECOND +ON COMPLETION PRESERVE +DO +INSERT INTO table_2 VALUES (1); +CREATE EVENT event_3 ON SCHEDULE EVERY 2 SECOND ENDS NOW() + INTERVAL 1 SECOND +ON COMPLETION NOT PRESERVE +DO +INSERT INTO table_3 VALUES (1); +CREATE EVENT event_4 ON SCHEDULE EVERY 1 SECOND ENDS NOW() + INTERVAL 1 SECOND +ON COMPLETION PRESERVE +DO +INSERT INTO table_4 VALUES (1); +SELECT IF(TIME_TO_SEC(TIMEDIFF(ENDS,STARTS))=6, 'OK', 'ERROR') +FROM INFORMATION_SCHEMA.EVENTS +WHERE EVENT_SCHEMA=DATABASE() AND EVENT_NAME='event_2'; +IF(TIME_TO_SEC(TIMEDIFF(ENDS,STARTS))=6, 'OK', 'ERROR') +OK +"Already dropped because ended. Therefore an error." +DROP EVENT event_3; +ERROR HY000: Unknown event 'event_3' +DROP EVENT event_1; +"Should be preserved" +SELECT EVENT_NAME, STATUS FROM INFORMATION_SCHEMA.EVENTS ORDER BY EVENT_NAME; +EVENT_NAME STATUS +event_2 DISABLED +event_4 DISABLED +DROP EVENT event_2; +DROP EVENT event_4; +DROP TABLE table_1; +DROP TABLE table_2; +DROP TABLE table_3; +DROP TABLE table_4; + +Bug #50087 Interval arithmetic for Event_queue_element is not portable. + +CREATE TABLE t1(a int); +CREATE EVENT e1 ON SCHEDULE EVERY 1 MONTH +STARTS NOW() - INTERVAL 1 MONTH +ENDS NOW() + INTERVAL 2 MONTH +ON COMPLETION PRESERVE +DO +INSERT INTO t1 VALUES (1); +CREATE EVENT e2 ON SCHEDULE EVERY 1 MONTH +STARTS NOW() +ENDS NOW() + INTERVAL 11 MONTH +ON COMPLETION PRESERVE +DO +INSERT INTO t1 VALUES (1); +DROP TABLE t1; +DROP EVENT e1; +DROP EVENT e2; +DROP DATABASE events_test; +SET GLOBAL event_scheduler=@event_scheduler; diff --git a/mysql-test/suite/events/events_scheduling.test b/mysql-test/suite/events/events_scheduling.test new file mode 100644 index 00000000..fee6274e --- /dev/null +++ b/mysql-test/suite/events/events_scheduling.test @@ -0,0 +1,150 @@ +# Can't test with embedded server that doesn't support events +-- source include/not_embedded.inc + +call mtr.add_suppression("Event Scheduler: .* Table 'events_test.t1' doesn't exist"); + +CREATE DATABASE IF NOT EXISTS events_test; +USE events_test; + +SET @event_scheduler=@@global.event_scheduler; +SET GLOBAL event_scheduler=OFF; +--echo Try again to make sure it's allowed +SET GLOBAL event_scheduler=OFF; +SHOW VARIABLES LIKE 'event_scheduler'; +SET GLOBAL event_scheduler=1; +SHOW VARIABLES LIKE 'event_scheduler'; +SET GLOBAL event_scheduler=0; +SHOW VARIABLES LIKE 'event_scheduler'; +SET GLOBAL event_scheduler=ON; +--echo Try again to make sure it's allowed +SET GLOBAL event_scheduler=ON; +SHOW VARIABLES LIKE 'event_scheduler'; +--error ER_WRONG_VALUE_FOR_VAR +SET GLOBAL event_scheduler=DISABLED; +SHOW VARIABLES LIKE 'event_scheduler'; +--error ER_WRONG_VALUE_FOR_VAR +SET GLOBAL event_scheduler=-1; +SHOW VARIABLES LIKE 'event_scheduler'; +--error ER_WRONG_VALUE_FOR_VAR +SET GLOBAL event_scheduler=2; +SHOW VARIABLES LIKE 'event_scheduler'; +--error ER_WRONG_VALUE_FOR_VAR +SET GLOBAL event_scheduler=5; +SHOW VARIABLES LIKE 'event_scheduler'; + +CREATE TABLE table_1(a int); +CREATE TABLE table_2(a int); +CREATE TABLE table_3(a int); +CREATE TABLE table_4(a int); + +SET GLOBAL event_scheduler=ON; +# We need to have 2 to make it safe with valgrind. This is probably because +# of when we calculate the timestamp value +CREATE EVENT event_1 ON SCHEDULE EVERY 2 SECOND +DO + INSERT INTO table_1 VALUES (1); + +CREATE EVENT event_2 ON SCHEDULE EVERY 1 SECOND +ENDS NOW() + INTERVAL 6 SECOND +ON COMPLETION PRESERVE +DO + INSERT INTO table_2 VALUES (1); + +CREATE EVENT event_3 ON SCHEDULE EVERY 2 SECOND ENDS NOW() + INTERVAL 1 SECOND +ON COMPLETION NOT PRESERVE +DO + INSERT INTO table_3 VALUES (1); + +CREATE EVENT event_4 ON SCHEDULE EVERY 1 SECOND ENDS NOW() + INTERVAL 1 SECOND +ON COMPLETION PRESERVE +DO + INSERT INTO table_4 VALUES (1); + +# Let event_1 insert at least 4 records into the table +let $wait_condition=select count(*) >= 4 from table_1; +--source include/wait_condition.inc +# Minimum of passed time is 6 seconds assuming +# - event executions starts immediate after creation +# - 4 times event_1 means an insert at ect, ect+2, ect+4, ect+6 +# ect = event creation time + +# Let event_2 reach the end of its execution interval +let $wait_condition=select count(*) = 0 from information_schema.events +where event_name='event_2' and status='enabled'; +--source include/wait_condition.inc +# Minimum of passed time is 6 seconds. +# See wait_condition for event_1 above and ENDS condition for event_2. + +# Let event_3, which is ON COMPLETION NOT PRESERVE execute and drop itself +let $wait_condition=select count(*) = 0 from information_schema.events +where event_name='event_3'; +--source include/wait_condition.inc + +# Let event_4 reach the end of its execution interval +let $wait_condition=select count(*) = 0 from information_schema.events +where event_name='event_4' and status='enabled'; +--source include/wait_condition.inc + +# +# On a busy system the scheduler may skip execution of events, +# we can't reliably expect that the data in a table to be modified +# by an event will be exact. Thus we do not SELECT from the tables +# in this test. See also +# Bug#39854 events_scheduling fails sporadically on pushbuild +# + +SELECT IF(TIME_TO_SEC(TIMEDIFF(ENDS,STARTS))=6, 'OK', 'ERROR') +FROM INFORMATION_SCHEMA.EVENTS +WHERE EVENT_SCHEMA=DATABASE() AND EVENT_NAME='event_2'; + +--echo "Already dropped because ended. Therefore an error." +--error ER_EVENT_DOES_NOT_EXIST +DROP EVENT event_3; + +DROP EVENT event_1; +--echo "Should be preserved" +SELECT EVENT_NAME, STATUS FROM INFORMATION_SCHEMA.EVENTS ORDER BY EVENT_NAME; +DROP EVENT event_2; +DROP EVENT event_4; +DROP TABLE table_1; +DROP TABLE table_2; +DROP TABLE table_3; +DROP TABLE table_4; + +-- echo +-- echo Bug #50087 Interval arithmetic for Event_queue_element is not portable. +-- echo + +CREATE TABLE t1(a int); + +CREATE EVENT e1 ON SCHEDULE EVERY 1 MONTH +STARTS NOW() - INTERVAL 1 MONTH +ENDS NOW() + INTERVAL 2 MONTH +ON COMPLETION PRESERVE +DO + INSERT INTO t1 VALUES (1); + +CREATE EVENT e2 ON SCHEDULE EVERY 1 MONTH +STARTS NOW() +ENDS NOW() + INTERVAL 11 MONTH +ON COMPLETION PRESERVE +DO + INSERT INTO t1 VALUES (1); + +DROP TABLE t1; +DROP EVENT e1; +DROP EVENT e2; + + +DROP DATABASE events_test; +SET GLOBAL event_scheduler=@event_scheduler; + +# +# End of tests +# + +let $wait_condition= + select count(*) = 0 from information_schema.processlist + where db='events_test' and command = 'Connect' and user=current_user(); +--source include/wait_condition.inc + diff --git a/mysql-test/suite/events/events_skip_grant_tables.opt b/mysql-test/suite/events/events_skip_grant_tables.opt new file mode 100644 index 00000000..3cf61728 --- /dev/null +++ b/mysql-test/suite/events/events_skip_grant_tables.opt @@ -0,0 +1 @@ +--event-scheduler=ON --skip-grant-tables diff --git a/mysql-test/suite/events/events_skip_grant_tables.result b/mysql-test/suite/events/events_skip_grant_tables.result new file mode 100644 index 00000000..beaeaa50 --- /dev/null +++ b/mysql-test/suite/events/events_skip_grant_tables.result @@ -0,0 +1,12 @@ +call mtr.add_suppression("Event Scheduler will not function when starting with --skip-grant-tables"); +# +# MDEV-29827 Misleading error messages about event scheduler when using --skip-grant-tables +# +FOUND 1 /Event Scheduler will not function when starting with --skip-grant-tables/ in mysqld.1.err +CREATE EVENT test ON SCHEDULE AT CURRENT_TIMESTAMP DO DO NULL; +ERROR HY000: The MariaDB server is running with the --skip-grant-tables option so it cannot execute this statement +select (@@global.event_scheduler='DISABLED') as expect_1; +expect_1 +1 +set global event_scheduler=1; +ERROR HY000: The MariaDB server is running with the --skip-grant-tables option so it cannot execute this statement diff --git a/mysql-test/suite/events/events_skip_grant_tables.test b/mysql-test/suite/events/events_skip_grant_tables.test new file mode 100644 index 00000000..843ead2b --- /dev/null +++ b/mysql-test/suite/events/events_skip_grant_tables.test @@ -0,0 +1,26 @@ +# Can't test with embedded server that doesn't support grants +-- source include/not_embedded.inc +call mtr.add_suppression("Event Scheduler will not function when starting with --skip-grant-tables"); + +--echo # +--echo # MDEV-29827 Misleading error messages about event scheduler when using --skip-grant-tables +--echo # + +# Verify that if server is started with +# --event-scheduler=ON --skip-grant-tables, we get an error +# with a distinct explanation that the latter disables the former. + +let SEARCH_FILE=$MYSQLTEST_VARDIR/log/mysqld.1.err; +let SEARCH_PATTERN=Event Scheduler will not function when starting with --skip-grant-tables; +source include/search_pattern_in_file.inc; + +--error ER_OPTION_PREVENTS_STATEMENT +CREATE EVENT test ON SCHEDULE AT CURRENT_TIMESTAMP DO DO NULL; + +# Although --event-scheduler=ON was specified (see -master.opt), it should +# have been changed to 'DISABLED' at startup. +select (@@global.event_scheduler='DISABLED') as expect_1; + +# Verify that we cannot (re)enable event scheduler +--error ER_OPTION_PREVENTS_STATEMENT +set global event_scheduler=1; diff --git a/mysql-test/suite/events/events_slowlog.result b/mysql-test/suite/events/events_slowlog.result new file mode 100644 index 00000000..be0a1e78 --- /dev/null +++ b/mysql-test/suite/events/events_slowlog.result @@ -0,0 +1,13 @@ +set @event_scheduler_save= @@global.event_scheduler; +set @slow_query_log_save= @@global.slow_query_log; +set global event_scheduler= on; +set global slow_query_log= on; +set global long_query_time=0.2; +create table t1 (i int); +insert into t1 values (0); +create event ev on schedule at CURRENT_TIMESTAMP + INTERVAL 1 second do update t1 set i=1+sleep(0.5); +FOUND 1 /update t1 set i=1/ in mysqld-slow.log +drop table t1; +set global event_scheduler= @event_scheduler_save; +set global slow_query_log= @slow_query_log_save; +set global long_query_time= @@session.long_query_time; diff --git a/mysql-test/suite/events/events_slowlog.test b/mysql-test/suite/events/events_slowlog.test new file mode 100644 index 00000000..9679714d --- /dev/null +++ b/mysql-test/suite/events/events_slowlog.test @@ -0,0 +1,28 @@ +--source include/not_embedded.inc +# +# MDEV-11552 Queries executed by event scheduler are written to slow log incorrectly or not written at all +# +set @event_scheduler_save= @@global.event_scheduler; +set @slow_query_log_save= @@global.slow_query_log; + +set global event_scheduler= on; +set global slow_query_log= on; +set global long_query_time=0.2; + +create table t1 (i int); +insert into t1 values (0); +create event ev on schedule at CURRENT_TIMESTAMP + INTERVAL 1 second do update t1 set i=1+sleep(0.5); + +--let wait_condition= select i from t1 where i > 0 +--source include/wait_condition.inc + +--let SEARCH_FILE = `SELECT @@slow_query_log_file` +--let SEARCH_PATTERN= update t1 set i=1 +--let SEARCH_RANGE= -1000 +--source include/search_pattern_in_file.inc + +drop table t1; + +set global event_scheduler= @event_scheduler_save; +set global slow_query_log= @slow_query_log_save; +set global long_query_time= @@session.long_query_time; diff --git a/mysql-test/suite/events/events_stress.result b/mysql-test/suite/events/events_stress.result new file mode 100644 index 00000000..1a91ff0f --- /dev/null +++ b/mysql-test/suite/events/events_stress.result @@ -0,0 +1,78 @@ +CREATE DATABASE IF NOT EXISTS events_test; +CREATE DATABASE events_conn1_test2; +CREATE TABLE events_test.fill_it1(test_name varchar(20), occur datetime); +CREATE TABLE events_test.fill_it2(test_name varchar(20), occur datetime); +CREATE TABLE events_test.fill_it3(test_name varchar(20), occur datetime); +CREATE USER event_user2@localhost; +CREATE DATABASE events_conn2_db; +GRANT ALL ON *.* TO event_user2@localhost; +CREATE USER event_user3@localhost; +CREATE DATABASE events_conn3_db; +GRANT ALL ON *.* TO event_user3@localhost; +connect conn2,localhost,event_user2,,events_conn2_db; +"In the second connection we create some events which won't be dropped till the end" +connect conn3,localhost,event_user3,,events_conn3_db; +"In the second connection we create some events which won't be dropped till the end" +connection default; +USE events_conn1_test2; +CREATE EVENT ev_drop1 ON SCHEDULE EVERY 10 MINUTE DISABLE DO SELECT 1; +CREATE EVENT ev_drop2 ON SCHEDULE EVERY 10 MINUTE DISABLE DO SELECT 1; +CREATE EVENT ev_drop3 ON SCHEDULE EVERY 10 MINUTE DISABLE DO SELECT 1; +USE events_test; +SELECT COUNT(*) FROM INFORMATION_SCHEMA.EVENTS; +COUNT(*) +103 +SELECT COUNT(*) FROM INFORMATION_SCHEMA.EVENTS WHERE EVENT_SCHEMA='events_conn1_test2'; +COUNT(*) +3 +DROP DATABASE events_conn1_test2; +SELECT COUNT(*) FROM INFORMATION_SCHEMA.EVENTS WHERE EVENT_SCHEMA='events_conn1_test2'; +COUNT(*) +0 +"Now testing stability - dropping db -> events while they are running" +CREATE DATABASE events_conn1_test2; +USE events_conn1_test2; +SELECT COUNT(*) FROM INFORMATION_SCHEMA.EVENTS WHERE EVENT_SCHEMA='events_conn1_test2'; +COUNT(*) +50 +SET @old_event_scheduler=@@event_scheduler; +SET GLOBAL event_scheduler=on; +DROP DATABASE events_conn1_test2; +SET GLOBAL event_scheduler=off; +SELECT COUNT(*) FROM INFORMATION_SCHEMA.EVENTS WHERE EVENT_SCHEMA='events_conn1_test2'; +COUNT(*) +0 +CREATE DATABASE events_conn1_test3; +USE events_conn1_test3; +SET GLOBAL event_scheduler=on; +SELECT COUNT(*) FROM INFORMATION_SCHEMA.EVENTS WHERE EVENT_SCHEMA='events_conn1_test3'; +COUNT(*) +50 +CREATE DATABASE events_conn1_test4; +USE events_conn1_test4; +CREATE DATABASE events_conn1_test2; +USE events_conn1_test2; +SELECT COUNT(*) FROM INFORMATION_SCHEMA.EVENTS WHERE EVENT_SCHEMA='events_conn1_test2'; +COUNT(*) +50 +connection conn2; +DROP DATABASE events_conn2_db; +connection conn3; +DROP DATABASE events_conn3_db; +connection default; +DROP DATABASE events_conn1_test2; +DROP DATABASE events_conn1_test3; +SET GLOBAL event_scheduler=off; +DROP DATABASE events_conn1_test4; +SET GLOBAL event_scheduler=on; +connection conn2; +disconnect conn2; +connection conn3; +disconnect conn3; +connection default; +USE events_test; +DROP TABLE fill_it1; +DROP TABLE fill_it2; +DROP TABLE fill_it3; +DROP DATABASE events_test; +SET GLOBAL event_scheduler=@old_event_scheduler; diff --git a/mysql-test/suite/events/events_stress.test b/mysql-test/suite/events/events_stress.test new file mode 100644 index 00000000..92b084a3 --- /dev/null +++ b/mysql-test/suite/events/events_stress.test @@ -0,0 +1,145 @@ +# Can't test with embedded server that doesn't support grants +--source include/not_embedded.inc +--source include/big_test.inc + +CREATE DATABASE IF NOT EXISTS events_test; +# +# DROP DATABASE test start (bug #16406) +# +CREATE DATABASE events_conn1_test2; +# BUG#20676: MySQL in debug mode has a limit of 100 waiters +# (in mysys/thr_lock.c), so use three different tables to insert into. +CREATE TABLE events_test.fill_it1(test_name varchar(20), occur datetime); +CREATE TABLE events_test.fill_it2(test_name varchar(20), occur datetime); +CREATE TABLE events_test.fill_it3(test_name varchar(20), occur datetime); +CREATE USER event_user2@localhost; +CREATE DATABASE events_conn2_db; +GRANT ALL ON *.* TO event_user2@localhost; +CREATE USER event_user3@localhost; +CREATE DATABASE events_conn3_db; +GRANT ALL ON *.* TO event_user3@localhost; +connect (conn2,localhost,event_user2,,events_conn2_db); +--echo "In the second connection we create some events which won't be dropped till the end" +--disable_query_log +--disable_warnings +let $1= 50; +while ($1) +{ + eval CREATE EVENT conn2_ev$1 ON SCHEDULE EVERY 1 SECOND DO INSERT INTO events_test.fill_it1 VALUES("conn2_ev$1", NOW()); + dec $1; +} +--enable_query_log +connect (conn3,localhost,event_user3,,events_conn3_db); +--echo "In the second connection we create some events which won't be dropped till the end" +--disable_query_log +let $1= 50; +while ($1) +{ + eval CREATE EVENT conn3_ev$1 ON SCHEDULE EVERY 1 SECOND DO INSERT INTO events_test.fill_it1 VALUES("conn3_ev$1", NOW()); + dec $1; +} +--enable_query_log +connection default; +USE events_conn1_test2; +CREATE EVENT ev_drop1 ON SCHEDULE EVERY 10 MINUTE DISABLE DO SELECT 1; +CREATE EVENT ev_drop2 ON SCHEDULE EVERY 10 MINUTE DISABLE DO SELECT 1; +CREATE EVENT ev_drop3 ON SCHEDULE EVERY 10 MINUTE DISABLE DO SELECT 1; +USE events_test; +--disable_service_connection +SELECT COUNT(*) FROM INFORMATION_SCHEMA.EVENTS; +SELECT COUNT(*) FROM INFORMATION_SCHEMA.EVENTS WHERE EVENT_SCHEMA='events_conn1_test2'; +DROP DATABASE events_conn1_test2; +SELECT COUNT(*) FROM INFORMATION_SCHEMA.EVENTS WHERE EVENT_SCHEMA='events_conn1_test2'; +--enable_service_connection + +--echo "Now testing stability - dropping db -> events while they are running" +CREATE DATABASE events_conn1_test2; +USE events_conn1_test2; +--disable_query_log +let $1= 50; +while ($1) +{ + eval CREATE EVENT conn1_round1_ev$1 ON SCHEDULE EVERY 1 SECOND DO INSERT INTO events_test.fill_it2 VALUES("conn1_round1_ev$1", NOW()); + dec $1; +} +--enable_query_log +SELECT COUNT(*) FROM INFORMATION_SCHEMA.EVENTS WHERE EVENT_SCHEMA='events_conn1_test2'; +SET @old_event_scheduler=@@event_scheduler; +SET GLOBAL event_scheduler=on; +--sleep 2.5 +DROP DATABASE events_conn1_test2; + +SET GLOBAL event_scheduler=off; +SELECT COUNT(*) FROM INFORMATION_SCHEMA.EVENTS WHERE EVENT_SCHEMA='events_conn1_test2'; +CREATE DATABASE events_conn1_test3; +USE events_conn1_test3; +--disable_query_log +let $1= 50; +while ($1) +{ + eval CREATE EVENT conn1_round2_ev$1 ON SCHEDULE EVERY 1 SECOND DO INSERT INTO events_test.fill_it2 VALUES("conn1_round2_ev$1", NOW()); + dec $1; +} +--enable_query_log +SET GLOBAL event_scheduler=on; +SELECT COUNT(*) FROM INFORMATION_SCHEMA.EVENTS WHERE EVENT_SCHEMA='events_conn1_test3'; +CREATE DATABASE events_conn1_test4; +USE events_conn1_test4; +--disable_query_log +let $1= 50; +while ($1) +{ + eval CREATE EVENT conn1_round3_ev$1 ON SCHEDULE EVERY 1 SECOND DO INSERT INTO events_test.fill_it3 VALUES("conn1_round3_ev$1", NOW()); + dec $1; +} +--enable_query_log + +CREATE DATABASE events_conn1_test2; +USE events_conn1_test2; +--disable_query_log +let $1= 50; +while ($1) +{ + eval CREATE EVENT ev_round4_drop$1 ON SCHEDULE EVERY 1 SECOND DO INSERT INTO events_test.fill_it3 VALUES("conn1_round4_ev$1", NOW()); + dec $1; +} +--enable_query_log +SELECT COUNT(*) FROM INFORMATION_SCHEMA.EVENTS WHERE EVENT_SCHEMA='events_conn1_test2'; +--sleep 2.5 +connection conn2; +--send +DROP DATABASE events_conn2_db; +connection conn3; +--send +DROP DATABASE events_conn3_db; +connection default; +# --send +DROP DATABASE events_conn1_test2; +DROP DATABASE events_conn1_test3; +SET GLOBAL event_scheduler=off; +DROP DATABASE events_conn1_test4; +SET GLOBAL event_scheduler=on; +connection conn2; +reap; +disconnect conn2; +connection conn3; +reap; +disconnect conn3; +connection default; +USE events_test; +DROP TABLE fill_it1; +DROP TABLE fill_it2; +DROP TABLE fill_it3; +--disable_query_log +DROP USER event_user2@localhost; +DROP USER event_user3@localhost; +--enable_query_log +# +# DROP DATABASE test end (bug #16406) +# + +DROP DATABASE events_test; + +# Cleanup +SET GLOBAL event_scheduler=@old_event_scheduler; +--source include/check_events_off.inc diff --git a/mysql-test/suite/events/events_time_zone.result b/mysql-test/suite/events/events_time_zone.result new file mode 100644 index 00000000..155a9d15 --- /dev/null +++ b/mysql-test/suite/events/events_time_zone.result @@ -0,0 +1,152 @@ +DROP DATABASE IF EXISTS mysqltest_db1; +CREATE DATABASE mysqltest_db1; +USE mysqltest_db1; +SET GLOBAL EVENT_SCHEDULER= OFF; +SET @save_time_zone= @@TIME_ZONE; +CREATE TABLE t_step (step INT); +INSERT INTO t_step VALUES (@step); +CREATE FUNCTION round_to_step(i INT, n INT) RETURNS INT +BEGIN +DECLARE step INT; +SELECT * INTO step FROM t_step; +# We add 0.1 as a protection from inexact division. +RETURN FLOOR((i % (step * n) + 0.1) / step); +END// +SET @step3= @step * 3; +SET @step6= @step * 6; +SET @unix_time= UNIX_TIMESTAMP() - 1; +SET @unix_time= @unix_time - @unix_time % @step6; +INSERT INTO mysql.time_zone VALUES (NULL, 'N'); +SET @tzid= LAST_INSERT_ID(); +INSERT INTO mysql.time_zone_transition_type +VALUES (@tzid, 0, 0, 0, 'b16420_0'); +INSERT INTO mysql.time_zone_transition_type +VALUES (@tzid, 1, @step3 - @step, 1, 'b16420_1'); +INSERT INTO mysql.time_zone_name VALUES ('<TZ_NAME_1>', @tzid); +CREATE TABLE t1 (count INT, unix_time INT, local_time INT, comment CHAR(80)); +CREATE TABLE t2 (count INT); +INSERT INTO t2 VALUES (1); +CREATE FUNCTION f1(comment CHAR(80)) RETURNS INT +BEGIN +DECLARE orig_tz CHAR(64); +DECLARE unix_time INT; +DECLARE local_now DATETIME; +DECLARE utc_now DATETIME; +DECLARE local_time INT; +SET unix_time= UNIX_TIMESTAMP(); +SET local_now= FROM_UNIXTIME(unix_time); +SET orig_tz= @@TIME_ZONE; +SET TIME_ZONE = '+00:00'; +SET utc_now= FROM_UNIXTIME(unix_time); +SET TIME_ZONE= orig_tz; +SET local_time = unix_time + TIMESTAMPDIFF(SECOND, utc_now, local_now); +SET unix_time= round_to_step(unix_time, 6); +SET local_time= round_to_step(local_time, 6); +INSERT INTO t1 VALUES ((SELECT count FROM t2), +unix_time, local_time, comment); +RETURN 0; +END// +SET TIME_ZONE= '+00:00'; +CREATE EVENT e1 ON SCHEDULE EVERY @step SECOND +STARTS FROM_UNIXTIME(@unix_time) DO SELECT f1("<e1>"); +SET TIME_ZONE= '<TZ_NAME_1>'; +CREATE EVENT e2 ON SCHEDULE EVERY @step SECOND +STARTS FROM_UNIXTIME(@unix_time) DO SELECT f1("<e2>"); +SET GLOBAL EVENT_SCHEDULER= ON; +SELECT SLEEP(@step / 2); +SLEEP(@step / 2) +0 +SET GLOBAL EVENT_SCHEDULER= OFF; +SELECT * FROM t1 ORDER BY count, comment; +count unix_time local_time comment +1 1 1 <e1> +1 1 3 <e2> +1 1 3 e2 should be executed +2 2 2 <e1> +2 2 4 <e2> +2 2 4 e2 should be executed +3 3 3 <e1> +3 3 3 Second pass after backward -2 step shift, e2 should not be executed +4 4 4 <e1> +4 4 4 Second pass after backward -2 step shift, e2 should not be executed +5 5 5 <e1> +5 5 5 <e2> +5 5 5 e2 should be executed +6 0 0 <e1> +6 0 2 <e2> +6 0 2 Forward +2 step shift, local 0, 1 are skipped, e2 should be executed +7 1 1 <e1> +7 1 3 <e2> +7 1 3 e2 should be executed +SET TIME_ZONE= @save_time_zone; +DROP EVENT e2; +DROP EVENT e1; +DROP FUNCTION f1; +DROP TABLE t1, t2; +DELETE FROM mysql.time_zone_name WHERE time_zone_id = @tzid; +DELETE FROM mysql.time_zone_transition_type WHERE time_zone_id = @tzid; +DELETE FROM mysql.time_zone_transition WHERE time_zone_id = @tzid; +DELETE FROM mysql.time_zone WHERE time_zone_id = @tzid; +ALTER TABLE mysql.time_zone AUTO_INCREMENT = 6; +SET TIME_ZONE= '+00:00'; +CREATE TABLE t1 (event CHAR(2), dt DATE, offset INT); +INSERT INTO mysql.time_zone VALUES (NULL, 'N'); +SET @tzid= LAST_INSERT_ID(); +SET @now= UNIX_TIMESTAMP(); +SET @offset_month_01= UNIX_TIMESTAMP('2030-01-31 12:00:00') - @now; +SET @offset_month_02= UNIX_TIMESTAMP('2030-02-28 12:00:00') - @now - 5*@step; +SET @offset_month_03= UNIX_TIMESTAMP('2030-03-31 12:00:00') - @now - 5*@step; +SET @offset_month_04= UNIX_TIMESTAMP('2030-04-30 12:00:00') - @now - 13*@step; +INSERT INTO mysql.time_zone_transition_type +VALUES (@tzid, 0, @offset_month_01, 0, 'b16420_0'); +INSERT INTO mysql.time_zone_transition_type +VALUES (@tzid, 1, @offset_month_02, 1, 'b16420_1'); +INSERT INTO mysql.time_zone_transition_type +VALUES (@tzid, 2, @offset_month_03, 1, 'b16420_2'); +INSERT INTO mysql.time_zone_transition_type +VALUES (@tzid, 3, @offset_month_04, 1, 'b16420_3'); +INSERT INTO mysql.time_zone_transition +VALUES (@tzid, @now, 0); +INSERT INTO mysql.time_zone_transition +VALUES (@tzid, @now + 3 * @step, 1); +INSERT INTO mysql.time_zone_transition +VALUES (@tzid, @now + 7 * @step, 2); +INSERT INTO mysql.time_zone_transition +VALUES (@tzid, @now + 12 * @step, 3); +INSERT INTO mysql.time_zone_name VALUES ('<TZ_NAME_2>', @tzid); +SET TIME_ZONE= '<TZ_NAME_2>'; +SET GLOBAL EVENT_SCHEDULER= ON; +SET GLOBAL EVENT_SCHEDULER= OFF; +Below we should see the following: +- On Jan 31 only e2 is executed, because we started later than +e1 should have been executed. Offset of e2 is 0 because of +the late start, not 1. +- The next execution is on Feb 28 (last day of Feb). Both events +are executed in their times, offsets are -1 and 1. +- The next time is Mar 31. Because the time of event +execution was skipped over, events are executed right away, +offsets are 2 and 2. +- The next time is Apr 30. Events are again executed in their +appointed times, offsets are -1 and 1. +SELECT * FROM t1 ORDER BY dt, event; +event dt offset +e2 2030-01-31 0 +e1 2030-02-28 -1 +e2 2030-02-28 1 +e1 2030-03-31 2 +e2 2030-03-31 2 +e1 2030-04-30 -1 +e2 2030-04-30 1 +DROP EVENT e2; +DROP EVENT e1; +DROP TABLE t1; +SET TIME_ZONE= @save_time_zone; +DELETE FROM mysql.time_zone_name WHERE time_zone_id = @tzid; +DELETE FROM mysql.time_zone_transition_type WHERE time_zone_id = @tzid; +DELETE FROM mysql.time_zone_transition WHERE time_zone_id = @tzid; +DELETE FROM mysql.time_zone WHERE time_zone_id = @tzid; +ALTER TABLE mysql.time_zone AUTO_INCREMENT = 6; +DROP FUNCTION round_to_step; +DROP TABLE t_step; +DROP DATABASE mysqltest_db1; +End of 5.1 tests. diff --git a/mysql-test/suite/events/events_time_zone.test b/mysql-test/suite/events/events_time_zone.test new file mode 100644 index 00000000..5291eb96 --- /dev/null +++ b/mysql-test/suite/events/events_time_zone.test @@ -0,0 +1,336 @@ +# 1. This test case is sensitive to execution timing. You may control +# this sensitivity by the parameter below. Small values will result +# in fast but more unstable execution, large values will improve +# stability at the cost of speed. Basically, N is a number of seconds +# to wait for operation to complete. Should be positive. Test runs +# about 25*N seconds (it sleeps most of the time, so CPU speed is not +# relevant). +let $N = 6; +# +# 2. Some subtests +# - create a new time zone +# - run some statements +# - delete the new time zone. +# But the time zone name used gets somewhere cached and it cannot be +# "reused" later in the same or another session for a new time zone. +# Experiments (2008-11 MySQL 5.1) showed that none of the available +# RESET/FLUSH commands removes these entries. +# 2008-11 MySQL 5.1 Bug#39979 main.events_time_zone does not clean up +# second bad effect +# Therefore we compute unique and unusual timezone names to minimize +# the likelihood that a later test uses the same name. +# +# 3. The subtests mentioned in 2. cause that the AUTO_INCREMENT value +# within "SHOW CREATE TABLE mysql.timezone" differ from the initial one. +# (Bug#39979 main.events_time_zone does not clean up) +# Therefore we reset this value after each of these subtests. +# +# Note(mleich): +# There is a significant likelihood that future improvements of the server +# cause that the solutions for the issues mentioned in 2. and 3. will no +# more work. +# A mysql-test-run.pl feature which allows to enforce +# 1. Server shutdown (-> Problem mentioned in 2. disappears) +# 2. Reset all data to initial state (-> Problem mentioned in 3. disappears) +# 3. Server start +# after a tests would be a perfect replacement. +# + +# Can't test with embedded server that doesn't support grants +-- source include/not_embedded.inc + +--source include/big_test.inc + + +--disable_warnings +DROP DATABASE IF EXISTS mysqltest_db1; +--enable_warnings + +CREATE DATABASE mysqltest_db1; + +let $old_db= `SELECT DATABASE()`; +USE mysqltest_db1; + +SET GLOBAL EVENT_SCHEDULER= OFF; +SET @save_time_zone= @@TIME_ZONE; + + +# +# BUG#16420: Events: timestamps become UTC +# BUG#26429: SHOW CREATE EVENT is incorrect for an event that +# STARTS NOW() +# BUG#26431: Impossible to re-create an event from backup if its +# STARTS clause is in the past +# WL#3698: Events: execution in local time zone +# + +#---------------------------------------------------------------------- + +# Create rounding function. + +# Disable query log to hide actual value of $N. +--disable_query_log +eval SET @step= $N; +--enable_query_log + +# Since we are working in a separate database, we may use any names we +# like. +CREATE TABLE t_step (step INT); +INSERT INTO t_step VALUES (@step); + +# We can't use @variables in function, because it will be called from +# the event thread, and 'eval' doesn't work for multi-statements, so +# we can't interpolate $variables either, hence we fetch the step +# value from the table. +delimiter //; +CREATE FUNCTION round_to_step(i INT, n INT) RETURNS INT +BEGIN + DECLARE step INT; + + SELECT * INTO step FROM t_step; + + # We add 0.1 as a protection from inexact division. + RETURN FLOOR((i % (step * n) + 0.1) / step); +END// +delimiter ;// + + +# Test time computations wrt Daylight Saving Time shifts. We also +# test here that the event operates in its time zone (see what NOW() +# returns). +# + +# Create a fake time zone with time transitions every 3*$N second. + +SET @step3= @step * 3; +SET @step6= @step * 6; + +SET @unix_time= UNIX_TIMESTAMP() - 1; +SET @unix_time= @unix_time - @unix_time % @step6; + +INSERT INTO mysql.time_zone VALUES (NULL, 'N'); +SET @tzid= LAST_INSERT_ID(); +INSERT INTO mysql.time_zone_transition_type + VALUES (@tzid, 0, 0, 0, 'b16420_0'); +INSERT INTO mysql.time_zone_transition_type + VALUES (@tzid, 1, @step3 - @step, 1, 'b16420_1'); + +let $transition_unix_time= `SELECT @unix_time`; +let $count= 30; +--disable_query_log +begin; +while ($count) +{ + eval INSERT INTO mysql.time_zone_transition + VALUES (@tzid, $transition_unix_time, + $transition_unix_time % @step6 = 0); + let $transition_unix_time= `SELECT $transition_unix_time + @step3`; + dec $count; +} +commit; +--enable_query_log +let $tz_name = `SELECT CONCAT('b16420_a',UNIX_TIMESTAMP())`; +--replace_result $tz_name <TZ_NAME_1> +eval INSERT INTO mysql.time_zone_name VALUES ('$tz_name', @tzid); + +CREATE TABLE t1 (count INT, unix_time INT, local_time INT, comment CHAR(80)); +CREATE TABLE t2 (count INT); +INSERT INTO t2 VALUES (1); + +delimiter //; +CREATE FUNCTION f1(comment CHAR(80)) RETURNS INT +BEGIN + DECLARE orig_tz CHAR(64); + DECLARE unix_time INT; + DECLARE local_now DATETIME; + DECLARE utc_now DATETIME; + DECLARE local_time INT; + + SET unix_time= UNIX_TIMESTAMP(); + SET local_now= FROM_UNIXTIME(unix_time); + SET orig_tz= @@TIME_ZONE; + SET TIME_ZONE = '+00:00'; + SET utc_now= FROM_UNIXTIME(unix_time); + SET TIME_ZONE= orig_tz; + SET local_time = unix_time + TIMESTAMPDIFF(SECOND, utc_now, local_now); + + SET unix_time= round_to_step(unix_time, 6); + SET local_time= round_to_step(local_time, 6); + + INSERT INTO t1 VALUES ((SELECT count FROM t2), + unix_time, local_time, comment); + RETURN 0; +END// +delimiter ;// + +SET TIME_ZONE= '+00:00'; +CREATE EVENT e1 ON SCHEDULE EVERY @step SECOND + STARTS FROM_UNIXTIME(@unix_time) DO SELECT f1("<e1>"); + +--replace_result $tz_name <TZ_NAME_1> +eval SET TIME_ZONE= '$tz_name'; +CREATE EVENT e2 ON SCHEDULE EVERY @step SECOND + STARTS FROM_UNIXTIME(@unix_time) DO SELECT f1("<e2>"); + +# We want to start at the beginning of the DST cycle, so we wait +# untill current time divides by @step6. +let $wait_timeout= `SELECT @step6*2 + 1`; +let $wait_condition= SELECT UNIX_TIMESTAMP() % @step6 = @step6 - 1; +--source include/wait_condition.inc +# The second wait is needed because after the first wait we may end up +# on the ending edge of a second. Second wait will bring us to the +# beginning edge. +let $wait_timeout= `SELECT @step*2 + 1`; +let $wait_condition= SELECT UNIX_TIMESTAMP() % @step6 = 0; +--source include/wait_condition.inc + +# Note that after the scheduler is enabled, the event will be +# scheduled only for the next second. +SET GLOBAL EVENT_SCHEDULER= ON; + +# We want to run after the events are executed. +SELECT SLEEP(@step / 2); + +let $count= 7; +--disable_query_log +--disable_result_log +while ($count) +{ + SELECT SLEEP(@step); + + eval SELECT CASE $count + WHEN 5 THEN f1(CONCAT("Second pass after backward -2 step shift,", + " e2 should not be executed")) + WHEN 4 THEN f1(CONCAT("Second pass after backward -2 step shift,", + " e2 should not be executed")) + WHEN 2 THEN f1(CONCAT("Forward +2 step shift, local 0, 1 are skipped,", + " e2 should be executed")) + ELSE f1("e2 should be executed") + END; + UPDATE t2 SET count= count + 1; + + dec $count; +} +--enable_result_log +--enable_query_log + +SET GLOBAL EVENT_SCHEDULER= OFF; + +SELECT * FROM t1 ORDER BY count, comment; + +SET TIME_ZONE= @save_time_zone; + +DROP EVENT e2; +DROP EVENT e1; +DROP FUNCTION f1; +DROP TABLE t1, t2; + +DELETE FROM mysql.time_zone_name WHERE time_zone_id = @tzid; +DELETE FROM mysql.time_zone_transition_type WHERE time_zone_id = @tzid; +DELETE FROM mysql.time_zone_transition WHERE time_zone_id = @tzid; +DELETE FROM mysql.time_zone WHERE time_zone_id = @tzid; +let $time_zone_auto_inc = `SELECT MAX(Time_zone_id) + 1 FROM mysql.time_zone`; +eval ALTER TABLE mysql.time_zone AUTO_INCREMENT = $time_zone_auto_inc; + +#---------------------------------------------------------------------- + +# Test MONTH interval. +# + +SET TIME_ZONE= '+00:00'; + +CREATE TABLE t1 (event CHAR(2), dt DATE, offset INT); + +INSERT INTO mysql.time_zone VALUES (NULL, 'N'); +SET @tzid= LAST_INSERT_ID(); + +SET @now= UNIX_TIMESTAMP(); +SET @offset_month_01= UNIX_TIMESTAMP('2030-01-31 12:00:00') - @now; +SET @offset_month_02= UNIX_TIMESTAMP('2030-02-28 12:00:00') - @now - 5*@step; +SET @offset_month_03= UNIX_TIMESTAMP('2030-03-31 12:00:00') - @now - 5*@step; +SET @offset_month_04= UNIX_TIMESTAMP('2030-04-30 12:00:00') - @now - 13*@step; + +INSERT INTO mysql.time_zone_transition_type + VALUES (@tzid, 0, @offset_month_01, 0, 'b16420_0'); +INSERT INTO mysql.time_zone_transition_type + VALUES (@tzid, 1, @offset_month_02, 1, 'b16420_1'); +INSERT INTO mysql.time_zone_transition_type + VALUES (@tzid, 2, @offset_month_03, 1, 'b16420_2'); +INSERT INTO mysql.time_zone_transition_type + VALUES (@tzid, 3, @offset_month_04, 1, 'b16420_3'); +INSERT INTO mysql.time_zone_transition + VALUES (@tzid, @now, 0); +INSERT INTO mysql.time_zone_transition + VALUES (@tzid, @now + 3 * @step, 1); +INSERT INTO mysql.time_zone_transition + VALUES (@tzid, @now + 7 * @step, 2); +INSERT INTO mysql.time_zone_transition + VALUES (@tzid, @now + 12 * @step, 3); +let $tz_name = `SELECT CONCAT('b16420_b',UNIX_TIMESTAMP())`; +--replace_result $tz_name <TZ_NAME_2> +eval INSERT INTO mysql.time_zone_name VALUES ('$tz_name', @tzid); + +--replace_result $tz_name <TZ_NAME_2> +eval SET TIME_ZONE= '$tz_name'; + +SET GLOBAL EVENT_SCHEDULER= ON; + +let $now= `SELECT @now`; +--disable_query_log +eval CREATE EVENT e1 ON SCHEDULE EVERY 1 MONTH + STARTS FROM_UNIXTIME($now - @step) DO + INSERT INTO t1 VALUES + ("e1", NOW(), round_to_step(UNIX_TIMESTAMP() - $now, 4) - 1); +eval CREATE EVENT e2 ON SCHEDULE EVERY 1 MONTH + STARTS FROM_UNIXTIME($now + @step) DO + INSERT INTO t1 VALUES + ("e2", NOW(), round_to_step(UNIX_TIMESTAMP() - $now, 4) - 1); +--enable_query_log + +let $wait_timeout= `SELECT 16 * @step`; +let $wait_condition= SELECT COUNT(*) = 7 FROM t1; +--source include/wait_condition.inc + +SET GLOBAL EVENT_SCHEDULER= OFF; + +--echo Below we should see the following: +--echo - On Jan 31 only e2 is executed, because we started later than +--echo e1 should have been executed. Offset of e2 is 0 because of +--echo the late start, not 1. +--echo - The next execution is on Feb 28 (last day of Feb). Both events +--echo are executed in their times, offsets are -1 and 1. +--echo - The next time is Mar 31. Because the time of event +--echo execution was skipped over, events are executed right away, +--echo offsets are 2 and 2. +--echo - The next time is Apr 30. Events are again executed in their +--echo appointed times, offsets are -1 and 1. +SELECT * FROM t1 ORDER BY dt, event; + +DROP EVENT e2; +DROP EVENT e1; +DROP TABLE t1; + +SET TIME_ZONE= @save_time_zone; + +DELETE FROM mysql.time_zone_name WHERE time_zone_id = @tzid; +DELETE FROM mysql.time_zone_transition_type WHERE time_zone_id = @tzid; +DELETE FROM mysql.time_zone_transition WHERE time_zone_id = @tzid; +DELETE FROM mysql.time_zone WHERE time_zone_id = @tzid; +let $time_zone_auto_inc = `SELECT MAX(Time_zone_id) + 1 FROM mysql.time_zone`; +eval ALTER TABLE mysql.time_zone AUTO_INCREMENT = $time_zone_auto_inc; + +DROP FUNCTION round_to_step; +DROP TABLE t_step; + + +DROP DATABASE mysqltest_db1; +--disable_query_log +eval USE $old_db; + +--enable_query_log +let $wait_condition= + SELECT COUNT(*) = 0 FROM information_schema.processlist + WHERE db='mysqltest_db1' AND command = 'Connect' AND user=current_user(); +--source include/wait_condition.inc + +--echo End of 5.1 tests. diff --git a/mysql-test/suite/events/events_trans.result b/mysql-test/suite/events/events_trans.result new file mode 100644 index 00000000..a7b268fe --- /dev/null +++ b/mysql-test/suite/events/events_trans.result @@ -0,0 +1,148 @@ +set sql_mode=""; +drop database if exists events_test; +drop database if exists mysqltest_no_such_database; +create database events_test; +use events_test; + +Test that Events DDL issue an implicit COMMIT + + +set autocommit=off; +select @@autocommit; +@@autocommit +0 +create table t1 (a varchar(255)) engine=innodb; +begin work; +insert into t1 (a) values ("OK: create event"); +create event e1 on schedule every 1 day do select 1; +Warnings: +Warning 1105 Event scheduler is switched off, use SET GLOBAL event_scheduler=ON to enable it. +rollback work; +select * from t1; +a +OK: create event +delete from t1; +commit work; +begin work; +insert into t1 (a) values ("OK: alter event"); +alter event e1 on schedule every 2 day do select 2; +rollback work; +select * from t1; +a +OK: alter event +delete from t1; +commit work; +begin work; +insert into t1 (a) values ("OK: alter event rename"); +alter event e1 rename to e2; +rollback work; +select * from t1; +a +OK: alter event rename +delete from t1; +commit work; +begin work; +insert into t1 (a) values ("OK: drop event"); +drop event e2; +rollback work; +select * from t1; +a +OK: drop event +delete from t1; +commit work; +begin work; +insert into t1 (a) values ("OK: drop event if exists"); +drop event if exists e2; +Warnings: +Note 1305 Event e2 does not exist +rollback work; +select * from t1; +a +OK: drop event if exists +delete from t1; +commit work; +create event e1 on schedule every 1 day do select 1; +Warnings: +Warning 1105 Event scheduler is switched off, use SET GLOBAL event_scheduler=ON to enable it. +begin work; +insert into t1 (a) values ("OK: create event if not exists"); +create event if not exists e1 on schedule every 2 day do select 2; +Warnings: +Note 1537 Event 'e1' already exists +Warning 1105 Event scheduler is switched off, use SET GLOBAL event_scheduler=ON to enable it. +rollback work; +select * from t1; +a +OK: create event if not exists +delete from t1; +commit work; + +Now check various error conditions: make sure we issue an +implicit commit anyway + +begin work; +insert into t1 (a) values ("OK: create event: event already exists"); +create event e1 on schedule every 2 day do select 2; +ERROR HY000: Event 'e1' already exists +rollback work; +select * from t1; +a +OK: create event: event already exists +delete from t1; +commit work; +begin work; +insert into t1 (a) values ("OK: alter event rename: rename to same name"); +alter event e1 rename to e1; +ERROR HY000: Same old and new event name +rollback work; +select * from t1; +a +OK: alter event rename: rename to same name +delete from t1; +commit work; +create event e2 on schedule every 3 day do select 3; +Warnings: +Warning 1105 Event scheduler is switched off, use SET GLOBAL event_scheduler=ON to enable it. +begin work; +insert into t1 (a) values ("OK: alter event rename: destination exists"); +alter event e2 rename to e1; +ERROR HY000: Event 'e1' already exists +rollback work; +select * from t1; +a +OK: alter event rename: destination exists +delete from t1; +commit work; +begin work; +insert into t1 (a) values ("OK: create event: database does not exist"); +create event mysqltest_no_such_database.e1 on schedule every 1 day do select 1; +ERROR 42000: Unknown database 'mysqltest_no_such_database' +rollback work; +select * from t1; +a +OK: create event: database does not exist +delete from t1; +commit work; +drop database events_test; +# +# Bug#54105 assert in MDL_context::release_locks_stored_before +# +USE test; +DROP TABLE IF EXISTS t1, t2; +DROP EVENT IF EXISTS e1; +CREATE TABLE t1 (a INT) ENGINE=InnoDB; +CREATE TABLE t2 (a INT); +CREATE EVENT e1 ON SCHEDULE EVERY 1 DAY DO SELECT 1; +Warnings: +Warning 1105 Event scheduler is switched off, use SET GLOBAL event_scheduler=ON to enable it. +START TRANSACTION; +INSERT INTO t1 VALUES (1); +SAVEPOINT A; +SHOW CREATE EVENT e1; +Event sql_mode time_zone Create Event character_set_client collation_connection Database Collation +e1 SYSTEM CREATE DEFINER=`root`@`localhost` EVENT `e1` ON SCHEDULE EVERY 1 DAY STARTS '#' ON COMPLETION NOT PRESERVE ENABLE DO SELECT 1 latin1 latin1_swedish_ci latin1_swedish_ci +SELECT * FROM t2; +a +ROLLBACK WORK TO SAVEPOINT A; +DROP TABLE t1, t2; +DROP EVENT e1; diff --git a/mysql-test/suite/events/events_trans.test b/mysql-test/suite/events/events_trans.test new file mode 100644 index 00000000..cc83fa6e --- /dev/null +++ b/mysql-test/suite/events/events_trans.test @@ -0,0 +1,152 @@ +# +# Tests that require transactions +# +-- source include/have_innodb.inc +-- source include/not_embedded.inc +-- source include/no_view_protocol.inc + +set sql_mode=""; + +--disable_warnings +drop database if exists events_test; +drop database if exists mysqltest_no_such_database; +--enable_warnings +create database events_test; +use events_test; + +--echo +--echo Test that Events DDL issue an implicit COMMIT +--echo +--echo +set autocommit=off; +# Sanity check +select @@autocommit; +create table t1 (a varchar(255)) engine=innodb; +# Basic: check that successful Events DDL commits pending transaction +begin work; +insert into t1 (a) values ("OK: create event"); +create event e1 on schedule every 1 day do select 1; +rollback work; +select * from t1; +delete from t1; +commit work; +# +begin work; +insert into t1 (a) values ("OK: alter event"); +alter event e1 on schedule every 2 day do select 2; +rollback work; +select * from t1; +delete from t1; +commit work; +# +begin work; +insert into t1 (a) values ("OK: alter event rename"); +alter event e1 rename to e2; +rollback work; +select * from t1; +delete from t1; +commit work; +# +begin work; +insert into t1 (a) values ("OK: drop event"); +drop event e2; +rollback work; +select * from t1; +delete from t1; +commit work; +# +begin work; +insert into t1 (a) values ("OK: drop event if exists"); +drop event if exists e2; +rollback work; +select * from t1; +delete from t1; +commit work; +# +create event e1 on schedule every 1 day do select 1; +begin work; +insert into t1 (a) values ("OK: create event if not exists"); +create event if not exists e1 on schedule every 2 day do select 2; +rollback work; +select * from t1; +delete from t1; +commit work; +--echo +--echo Now check various error conditions: make sure we issue an +--echo implicit commit anyway +--echo +# +begin work; +insert into t1 (a) values ("OK: create event: event already exists"); +--error ER_EVENT_ALREADY_EXISTS +create event e1 on schedule every 2 day do select 2; +rollback work; +select * from t1; +delete from t1; +commit work; +# +begin work; +insert into t1 (a) values ("OK: alter event rename: rename to same name"); +--error ER_EVENT_SAME_NAME +alter event e1 rename to e1; +rollback work; +select * from t1; +delete from t1; +commit work; +# +create event e2 on schedule every 3 day do select 3; +begin work; +insert into t1 (a) values ("OK: alter event rename: destination exists"); +--error ER_EVENT_ALREADY_EXISTS +alter event e2 rename to e1; +rollback work; +select * from t1; +delete from t1; +commit work; +# +begin work; +insert into t1 (a) values ("OK: create event: database does not exist"); +--error ER_BAD_DB_ERROR +create event mysqltest_no_such_database.e1 on schedule every 1 day do select 1; +rollback work; +select * from t1; +delete from t1; +commit work; + +# +# Cleanup +# + +let $wait_condition= + select count(*) = 0 from information_schema.processlist + where db='events_test' and command = 'Connect' and user=current_user(); +--source include/wait_condition.inc + +drop database events_test; + + +--echo # +--echo # Bug#54105 assert in MDL_context::release_locks_stored_before +--echo # + +USE test; + +--disable_warnings +DROP TABLE IF EXISTS t1, t2; +DROP EVENT IF EXISTS e1; +--enable_warnings + +CREATE TABLE t1 (a INT) ENGINE=InnoDB; +CREATE TABLE t2 (a INT); +CREATE EVENT e1 ON SCHEDULE EVERY 1 DAY DO SELECT 1; + +START TRANSACTION; +INSERT INTO t1 VALUES (1); +SAVEPOINT A; +--replace_regex /STARTS '[^']+'/STARTS '#'/ +SHOW CREATE EVENT e1; +SELECT * FROM t2; +ROLLBACK WORK TO SAVEPOINT A; + +DROP TABLE t1, t2; +DROP EVENT e1; diff --git a/mysql-test/suite/events/events_trans_notembedded.result b/mysql-test/suite/events/events_trans_notembedded.result new file mode 100644 index 00000000..324e28dd --- /dev/null +++ b/mysql-test/suite/events/events_trans_notembedded.result @@ -0,0 +1,49 @@ +drop database if exists events_test; +drop database if exists mysqltest_db2; +create database events_test; +use events_test; +create user mysqltest_user1@localhost; +grant create, insert, select, delete on mysqltest_db2.* +to mysqltest_user1@localhost; +create database mysqltest_db2; +connect conn1,localhost,mysqltest_user1,,mysqltest_db2; +set autocommit=off; +select @@autocommit; +@@autocommit +0 +create table t1 (a varchar(255)) engine=innodb; +begin work; +insert into t1 (a) values ("OK: create event: insufficient privileges"); +create event e1 on schedule every 1 day do select 1; +ERROR 42000: Access denied for user 'mysqltest_user1'@'localhost' to database 'mysqltest_db2' +rollback work; +select * from t1; +a +OK: create event: insufficient privileges +delete from t1; +commit work; +begin work; +insert into t1 (a) values ("OK: alter event: insufficient privileges"); +alter event e1 on schedule every 1 day do select 1; +ERROR 42000: Access denied for user 'mysqltest_user1'@'localhost' to database 'mysqltest_db2' +rollback work; +select * from t1; +a +OK: alter event: insufficient privileges +delete from t1; +commit work; +begin work; +insert into t1 (a) values ("OK: drop event: insufficient privileges"); +drop event e1; +ERROR 42000: Access denied for user 'mysqltest_user1'@'localhost' to database 'mysqltest_db2' +rollback work; +select * from t1; +a +OK: drop event: insufficient privileges +delete from t1; +commit work; +disconnect conn1; +connection default; +drop user mysqltest_user1@localhost; +drop database mysqltest_db2; +drop database events_test; diff --git a/mysql-test/suite/events/events_trans_notembedded.test b/mysql-test/suite/events/events_trans_notembedded.test new file mode 100644 index 00000000..3cf823bf --- /dev/null +++ b/mysql-test/suite/events/events_trans_notembedded.test @@ -0,0 +1,68 @@ +# +# Tests that require transactions +# +-- source include/not_embedded.inc +-- source include/have_innodb.inc +--disable_warnings +drop database if exists events_test; +drop database if exists mysqltest_db2; +--enable_warnings +create database events_test; +use events_test; + +# +# Privilege checks +# +create user mysqltest_user1@localhost; +grant create, insert, select, delete on mysqltest_db2.* + to mysqltest_user1@localhost; +create database mysqltest_db2; +connect (conn1,localhost,mysqltest_user1,,mysqltest_db2); +set autocommit=off; +# Sanity check +select @@autocommit; +create table t1 (a varchar(255)) engine=innodb; +# Not enough privileges to CREATE EVENT +begin work; +insert into t1 (a) values ("OK: create event: insufficient privileges"); +--error ER_DBACCESS_DENIED_ERROR +create event e1 on schedule every 1 day do select 1; +rollback work; +select * from t1; +delete from t1; +commit work; +# Not enough privileges to ALTER EVENT +begin work; +insert into t1 (a) values ("OK: alter event: insufficient privileges"); +--error ER_DBACCESS_DENIED_ERROR +alter event e1 on schedule every 1 day do select 1; +rollback work; +select * from t1; +delete from t1; +commit work; +# Not enough privileges to DROP EVENT +begin work; +insert into t1 (a) values ("OK: drop event: insufficient privileges"); +--error ER_DBACCESS_DENIED_ERROR +drop event e1; +rollback work; +select * from t1; +delete from t1; +commit work; +# Cleanup +disconnect conn1; +--source include/wait_until_disconnected.inc +connection default; +drop user mysqltest_user1@localhost; +drop database mysqltest_db2; + +# +# Cleanup +# +let $wait_condition= + select count(*) = 0 from information_schema.processlist + where db='events_test' and command = 'Connect' and user=current_user(); +--source include/wait_condition.inc + +drop database events_test; + |