diff options
Diffstat (limited to 'mysql-test')
| -rw-r--r-- | mysql-test/r/events.result | 404 | ||||
| -rw-r--r-- | mysql-test/r/events_bugs.result | 150 | ||||
| -rw-r--r-- | mysql-test/r/events_restart_phase1.result | 22 | ||||
| -rw-r--r-- | mysql-test/r/events_restart_phase2.result | 48 | ||||
| -rw-r--r-- | mysql-test/r/events_restart_phase3.result | 10 | ||||
| -rw-r--r-- | mysql-test/r/events_scheduling.result | 4 | ||||
| -rw-r--r-- | mysql-test/r/events_time_zone.result | 142 | ||||
| -rw-r--r-- | mysql-test/r/events_trans.result | 159 | ||||
| -rw-r--r-- | mysql-test/t/events.test | 374 | ||||
| -rw-r--r-- | mysql-test/t/events_bugs.test | 185 | ||||
| -rw-r--r-- | mysql-test/t/events_restart_phase1.test | 46 | ||||
| -rw-r--r-- | mysql-test/t/events_restart_phase2.test | 44 | ||||
| -rw-r--r-- | mysql-test/t/events_restart_phase3.test | 22 | ||||
| -rw-r--r-- | mysql-test/t/events_scheduling.test | 4 | ||||
| -rw-r--r-- | mysql-test/t/events_time_zone.test | 171 | ||||
| -rw-r--r-- | mysql-test/t/events_trans.test | 159 |
16 files changed, 1497 insertions, 447 deletions
diff --git a/mysql-test/r/events.result b/mysql-test/r/events.result index b0640af0b13..7125b13cb88 100644 --- a/mysql-test/r/events.result +++ b/mysql-test/r/events.result @@ -1,4 +1,8 @@ -create database if not exists events_test; +drop database if exists events_test; +drop database if exists db_x; +drop database if exists mysqltest_db2; +drop database if exists mysqltest_no_such_database; +create database events_test; use events_test; CREATE USER pauline@localhost; CREATE DATABASE db_x; @@ -223,72 +227,180 @@ 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"; SHOW EVENTS; Db Name Definer Time zone Type Execute at Interval value Interval field Starts Ends Status events_test intact_check root@localhost SYSTEM RECURRING NULL 10 # # NULL ENABLED -ALTER TABLE mysql.event ADD dummy INT FIRST; -SHOW EVENTS; -ERROR HY000: Column count of mysql.event is wrong. Expected 17, found 18. The table is probably corrupted -ALTER TABLE mysql.event DROP dummy, ADD dummy2 VARCHAR(64) FIRST; -SHOW EVENTS; -ERROR HY000: Column count of mysql.event is wrong. Expected 17, found 18. The table is probably corrupted -ALTER TABLE mysql.event DROP dummy2; +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 make sure everything +works as before + +ALTER TABLE mysql.event ADD dummy INT; SHOW EVENTS; Db Name Definer Time zone Type Execute at Interval value Interval field Starts Ends Status events_test intact_check root@localhost SYSTEM RECURRING NULL 10 # # NULL ENABLED +SELECT event_name FROM INFORMATION_SCHEMA.events; +event_name +intact_check +SHOW CREATE EVENT intact_check; +Event sql_mode time_zone Create Event +intact_check SYSTEM CREATE EVENT `intact_check` ON SCHEDULE EVERY 10 HOUR STARTS '#' ON COMPLETION NOT PRESERVE ENABLE DO SELECT "nothing" +DROP EVENT no_such_event; +ERROR HY000: Unknown event 'no_such_event' +CREATE EVENT intact_check_1 ON SCHEDULE EVERY 5 HOUR DO SELECT 5; +ALTER EVENT intact_check_1 ON SCHEDULE EVERY 8 HOUR DO SELECT 8; +ALTER EVENT intact_check_1 RENAME TO intact_check_2; +DROP EVENT intact_check_1; +ERROR HY000: Unknown event 'intact_check_1' +DROP EVENT intact_check_2; +DROP EVENT intact_check; +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; +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; +CREATE EVENT intact_check ON SCHEDULE EVERY 10 HOUR DO SELECT "nothing"; + +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: Cannot load from mysql.event. The table is probably corrupted +SELECT event_name FROM INFORMATION_SCHEMA.events; +ERROR HY000: Cannot load from mysql.event. The table is probably corrupted +SHOW CREATE EVENT intact_check; +ERROR HY000: Unknown event 'intact_check' +DROP EVENT no_such_event; +ERROR HY000: Unknown event 'no_such_event' +CREATE EVENT intact_check_1 ON SCHEDULE EVERY 5 HOUR DO SELECT 5; +ERROR HY000: Cannot load from mysql.event. The table is probably corrupted +ALTER EVENT intact_check_1 ON SCHEDULE EVERY 8 HOUR DO SELECT 8; +ERROR HY000: Unknown event 'intact_check_1' +ALTER EVENT intact_check_1 RENAME TO intact_check_2; +ERROR HY000: Unknown event 'intact_check_1' +DROP EVENT intact_check_1; +ERROR HY000: Unknown event 'intact_check_1' +DROP EVENT intact_check_2; +ERROR HY000: Unknown event 'intact_check_2' +DROP EVENT intact_check; +ERROR HY000: Unknown event 'intact_check' +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; +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"; +Back up the table, further changes are not reversible CREATE TABLE event_like LIKE mysql.event; INSERT INTO event_like SELECT * FROM mysql.event; -ALTER TABLE mysql.event MODIFY db char(64) character set cp1251 default ''; -SELECT event_name FROM INFORMATION_SCHEMA.EVENTS; -ERROR HY000: Cannot load from mysql.event. The table is probably corrupted. Please see the error log for details -ALTER TABLE mysql.event MODIFY db char(20) character set utf8 collate utf8_bin default ''; -SHOW CREATE TABLE mysql.event; -Table Create Table -event CREATE TABLE `event` ( - `db` char(20) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '', - `name` char(64) NOT NULL DEFAULT '', - `body` longblob NOT NULL, - `definer` char(77) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '', - `execute_at` datetime DEFAULT NULL, - `interval_value` int(11) DEFAULT NULL, - `interval_field` enum('YEAR','QUARTER','MONTH','DAY','HOUR','MINUTE','WEEK','SECOND','MICROSECOND','YEAR_MONTH','DAY_HOUR','DAY_MINUTE','DAY_SECOND','HOUR_MINUTE','HOUR_SECOND','MINUTE_SECOND','DAY_MICROSECOND','HOUR_MICROSECOND','MINUTE_MICROSECOND','SECOND_MICROSECOND') DEFAULT NULL, - `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, - `modified` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00', - `last_executed` datetime DEFAULT NULL, - `starts` datetime DEFAULT NULL, - `ends` datetime DEFAULT NULL, - `status` enum('ENABLED','DISABLED') NOT NULL DEFAULT 'ENABLED', - `on_completion` enum('DROP','PRESERVE') NOT NULL DEFAULT 'DROP', - `sql_mode` set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','NOT_USED','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBTRACTION','NO_DIR_IN_CREATE','POSTGRESQL','ORACLE','MSSQL','DB2','MAXDB','NO_KEY_OPTIONS','NO_TABLE_OPTIONS','NO_FIELD_OPTIONS','MYSQL323','MYSQL40','ANSI','NO_AUTO_VALUE_ON_ZERO','NO_BACKSLASH_ESCAPES','STRICT_TRANS_TABLES','STRICT_ALL_TABLES','NO_ZERO_IN_DATE','NO_ZERO_DATE','INVALID_DATES','ERROR_FOR_DIVISION_BY_ZERO','TRADITIONAL','NO_AUTO_CREATE_USER','HIGH_NOT_PRECEDENCE') NOT NULL DEFAULT '', - `comment` char(64) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '', - `time_zone` char(64) CHARACTER SET latin1 NOT NULL DEFAULT 'SYSTEM', - PRIMARY KEY (`db`,`name`) -) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Events' -SELECT event_name FROM INFORMATION_SCHEMA.EVENTS; -ERROR HY000: Cannot load from mysql.event. The table is probably corrupted. Please see the error log for details -ALTER TABLE mysql.event MODIFY db char(64) character set utf8 collate utf8_bin default ''; -"This should work" -SHOW EVENTS; -Db Name Definer Time zone Type Execute at Interval value Interval field Starts Ends Status -events_test intact_check root@localhost SYSTEM RECURRING NULL 10 # # NULL ENABLED -ALTER TABLE mysql.event MODIFY db char(64) character set cp1251 default ''; -SELECT event_name FROM INFORMATION_SCHEMA.EVENTS; -ERROR HY000: Cannot load from mysql.event. The table is probably corrupted. Please see the error log for details -ALTER TABLE mysql.event MODIFY db varchar(64) character set utf8 collate utf8_bin default ''; -SELECT event_name FROM INFORMATION_SCHEMA.EVENTS; -ERROR HY000: Cannot load from mysql.event. The table is probably corrupted. Please see the error log for details + +Drop some columns and try more checks. + + ALTER TABLE mysql.event DROP comment, DROP starts; +SHOW EVENTS; +ERROR HY000: Cannot load from mysql.event. The table is probably corrupted SELECT event_name FROM INFORMATION_SCHEMA.EVENTS; +ERROR HY000: Cannot load from mysql.event. The table is probably corrupted +SHOW CREATE EVENT intact_check; +ERROR HY000: Cannot load from mysql.event. The table is probably corrupted +DROP EVENT no_such_event; +ERROR HY000: Unknown event 'no_such_event' +CREATE EVENT intact_check_1 ON SCHEDULE EVERY 5 HOUR DO SELECT 5; ERROR HY000: Column count of mysql.event is wrong. Expected 17, found 15. The table is probably corrupted +ALTER EVENT intact_check_1 ON SCHEDULE EVERY 8 HOUR DO SELECT 8; +ERROR HY000: Unknown event 'intact_check_1' +ALTER EVENT intact_check_1 RENAME TO intact_check_2; +ERROR HY000: Unknown event 'intact_check_1' +DROP EVENT intact_check_1; +ERROR HY000: Unknown event 'intact_check_1' +DROP EVENT intact_check_2; +ERROR HY000: Unknown event 'intact_check_2' +DROP EVENT intact_check; +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; +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 +Error 1146 Table 'mysql.event' doesn't exist +CREATE DATABASE mysqltest_db2; +DROP DATABASE mysqltest_db2; +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; -INSERT INTO mysql.event SELECT * FROM event_like; DROP TABLE event_like; SHOW EVENTS; Db Name Definer Time zone Type Execute at Interval value Interval field Starts Ends Status -events_test intact_check root@localhost SYSTEM RECURRING NULL 10 # # NULL ENABLED -DROP EVENT intact_check; create event e_26 on schedule at '2017-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; db name body definer convert_tz(execute_at, 'UTC', 'SYSTEM') on_completion @@ -401,4 +513,196 @@ 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 + +LOCK TABLES mode. + +create table t1 (a int); +create event e1 on schedule every 10 hour do select 1; +lock table t1 read; +show create event e1; +Event sql_mode time_zone Create Event +e1 SYSTEM CREATE EVENT `e1` ON SCHEDULE EVERY 10 HOUR STARTS '#' ON COMPLETION NOT PRESERVE ENABLE DO select 1 +select event_name from information_schema.events; +event_name +e1 +create event e2 on schedule every 10 hour do select 1; +ERROR HY000: Table 'event' was not locked with LOCK TABLES +alter event e2 disable; +ERROR HY000: Table 'event' was not locked with LOCK TABLES +alter event e2 rename to e3; +ERROR HY000: Table 'event' was not locked with LOCK TABLES +drop event e2; +ERROR HY000: Table 'event' was not locked with LOCK TABLES +drop event e1; +ERROR HY000: Table 'event' was not locked with LOCK TABLES +unlock tables; +lock table t1 write; +show create event e1; +Event sql_mode time_zone Create Event +e1 SYSTEM CREATE EVENT `e1` ON SCHEDULE EVERY 10 HOUR STARTS '#' ON COMPLETION NOT PRESERVE ENABLE DO select 1 +select event_name from information_schema.events; +event_name +e1 +create event e2 on schedule every 10 hour do select 1; +ERROR HY000: Table 'event' was not locked with LOCK TABLES +alter event e2 disable; +ERROR HY000: Table 'event' was not locked with LOCK TABLES +alter event e2 rename to e3; +ERROR HY000: Table 'event' was not locked with LOCK TABLES +drop event e2; +ERROR HY000: Table 'event' was not locked with LOCK TABLES +drop event e1; +ERROR HY000: Table 'event' was not locked with LOCK TABLES +unlock tables; +lock table t1 read, mysql.event read; +show create event e1; +Event sql_mode time_zone Create Event +e1 SYSTEM CREATE EVENT `e1` ON SCHEDULE EVERY 10 HOUR STARTS '#' ON COMPLETION NOT PRESERVE ENABLE DO select 1 +select event_name from information_schema.events; +event_name +e1 +create event e2 on schedule every 10 hour do select 1; +ERROR HY000: Table 'event' was locked with a READ lock and can't be updated +alter event e2 disable; +ERROR HY000: Table 'event' was locked with a READ lock and can't be updated +alter event e2 rename to e3; +ERROR HY000: Table 'event' was locked with a READ lock and can't be updated +drop event e2; +ERROR HY000: Table 'event' was locked with a READ lock and can't be updated +drop event e1; +ERROR HY000: Table 'event' was locked with a READ lock and can't be updated +unlock tables; +lock table t1 write, mysql.event read; +show create event e1; +Event sql_mode time_zone Create Event +e1 SYSTEM CREATE EVENT `e1` ON SCHEDULE EVERY 10 HOUR STARTS '#' ON COMPLETION NOT PRESERVE ENABLE DO select 1 +select event_name from information_schema.events; +event_name +e1 +create event e2 on schedule every 10 hour do select 1; +ERROR HY000: Table 'event' was locked with a READ lock and can't be updated +alter event e2 disable; +ERROR HY000: Table 'event' was locked with a READ lock and can't be updated +alter event e2 rename to e3; +ERROR HY000: Table 'event' was locked with a READ lock and can't be updated +drop event e2; +ERROR HY000: Table 'event' was locked with a READ lock and can't be updated +drop event e1; +ERROR HY000: Table 'event' was locked with a READ lock and can't be updated +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 +e1 SYSTEM CREATE EVENT `e1` ON SCHEDULE EVERY 10 HOUR STARTS '#' ON COMPLETION NOT PRESERVE ENABLE DO select 1 +select event_name from information_schema.events; +event_name +e1 +create event e2 on schedule every 10 hour do select 1; +alter event e2 disable; +alter event e2 rename to e3; +drop event e3; +drop event e1; +unlock tables; +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; +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| drop database events_test; diff --git a/mysql-test/r/events_bugs.result b/mysql-test/r/events_bugs.result index 6835ecd7fde..b7690a57207 100644 --- a/mysql-test/r/events_bugs.result +++ b/mysql-test/r/events_bugs.result @@ -1,4 +1,7 @@ -create database if not exists events_test; +drop database if exists events_test; +drop database if exists mysqltest_db1; +drop database if exists mysqltest_db2; +create database events_test; use events_test; CREATE EVENT lower_case ON SCHEDULE EVERY 1 MINUTE DO SELECT 1; CREATE EVENT Lower_case ON SCHEDULE EVERY 2 MINUTE DO SELECT 2; @@ -381,4 +384,149 @@ ERROR 42000: Access denied; you need the SUPER privilege for this operation DROP EVENT e1; ERROR HY000: Unknown event 'e1' 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; +SHOW EVENTS; +Db Name Definer Time zone Type Execute at Interval value Interval field Starts Ends Status +events_test e1 root@localhost +00:00 RECURRING NULL 1 DAY 2005-12-31 23:58:59 NULL ENABLED +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 +events_test e1 root@localhost -01:00 RECURRING NULL 1 DAY 2000-01-01 00:00:00 NULL ENABLED +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 +events_test e1 root@localhost +02:00 ONE TIME 2000-01-02 00:00:00 NULL NULL NULL NULL DISABLED +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 +events_test e1 root@localhost -03:00 RECURRING NULL 1 DAY 2005-12-31 20:58:59 2030-01-03 00:00:00 DISABLED +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 +events_test e1 root@localhost -03:00 RECURRING NULL 1 DAY 2005-12-31 20:58:59 2030-01-03 00:00:00 ENABLED +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; +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; +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 +NULL 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 +NULL 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 +NULL 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 +SHOW EVENTS; +Db Name Definer Time zone Type Execute at Interval value Interval field Starts Ends Status +events_test e1 root@localhost +05:00 RECURRING NULL 1 DAY 2006-01-01 00:00:00 NULL ENABLED +events_test e2 root@localhost -05:00 RECURRING NULL 1 DAY 2006-01-01 00:00:00 NULL ENABLED +events_test e3 root@localhost +00:00 RECURRING NULL 1 DAY 2006-01-01 00:00:00 NULL ENABLED +SHOW CREATE EVENT e1; +Event sql_mode time_zone Create Event +e1 +05:00 CREATE EVENT `e1` ON SCHEDULE EVERY 1 DAY STARTS '2006-01-01 00:00:00' ON COMPLETION NOT PRESERVE ENABLE DO SELECT 1 +SHOW CREATE EVENT e2; +Event sql_mode time_zone Create Event +e2 -05:00 CREATE EVENT `e2` ON SCHEDULE EVERY 1 DAY STARTS '2006-01-01 00:00:00' ON COMPLETION NOT PRESERVE ENABLE DO SELECT 1 +SHOW CREATE EVENT e3; +Event sql_mode time_zone Create Event +e3 +00:00 CREATE EVENT `e3` ON SCHEDULE EVERY 1 DAY STARTS '2006-01-01 00:00:00' ON COMPLETION NOT PRESERVE ENABLE DO SELECT 1 +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. Event has not been altered +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. Event has not been altered +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 1584 Event execution time is in the past and ON COMPLETION NOT PRESERVE is set. Event has not been created +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 1584 Event execution time is in the past and ON COMPLETION NOT PRESERVE is set. Event has not been created +CREATE EVENT e4 ON SCHEDULE AT '1999-01-01 00:00:00' DO +SELECT 1; +Warnings: +Note 1584 Event execution time is in the past and ON COMPLETION NOT PRESERVE is set. Event has not been created +CREATE EVENT e4 ON SCHEDULE AT '1999-01-01 00:00:00' DISABLE +DO +SELECT 1; +Warnings: +Note 1584 Event execution time is in the past and ON COMPLETION NOT PRESERVE is set. Event has not been created +SHOW EVENTS; +Db Name Definer Time zone Type Execute at Interval value Interval field Starts Ends Status +events_test e1 root@localhost +05:00 RECURRING NULL 1 DAY 2006-01-01 00:00:00 NULL ENABLED +events_test e2 root@localhost -05:00 RECURRING NULL 1 DAY 2006-01-01 00:00:00 NULL ENABLED +events_test e3 root@localhost +00:00 RECURRING NULL 1 DAY 2006-01-01 00:00:00 NULL ENABLED +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 1533 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 1533 Event execution time is in the past. Event has been disabled +CREATE EVENT e5 ON SCHEDULE AT '1999-01-01 00:00:00' + ON COMPLETION PRESERVE +DO +SELECT 1; +Warnings: +Note 1533 Event execution time is in the past. Event has been disabled +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; +Db Name Definer Time zone Type Execute at Interval value Interval field Starts Ends Status +events_test e1 root@localhost +00:00 RECURRING NULL 1 HOUR 1999-01-01 00:00:00 1999-01-02 00:00:00 DISABLED +events_test e2 root@localhost +00:00 RECURRING NULL 1 HOUR 1999-01-01 00:00:00 NULL ENABLED +events_test e3 root@localhost +00:00 RECURRING NULL 1 HOUR 1999-01-01 00:00:00 1999-01-02 00:00:00 DISABLED +events_test e4 root@localhost +00:00 RECURRING NULL 1 HOUR 1999-01-01 00:00:00 1999-01-02 00:00:00 DISABLED +events_test e5 root@localhost +00:00 ONE TIME 1999-01-01 00:00:00 NULL NULL NULL NULL DISABLED +events_test e6 root@localhost +00:00 RECURRING NULL 1 HOUR 1999-01-01 00:00:00 NULL ENABLED +events_test e7 root@localhost +00:00 RECURRING NULL 1 HOUR 1999-01-01 00:00:00 1999-01-02 00:00:00 DISABLED +events_test e8 root@localhost +00:00 ONE TIME 1999-01-01 00:00:00 NULL NULL NULL NULL DISABLED +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; drop database events_test; diff --git a/mysql-test/r/events_restart_phase1.result b/mysql-test/r/events_restart_phase1.result index 0c032884dc4..7b1de62f2ef 100644 --- a/mysql-test/r/events_restart_phase1.result +++ b/mysql-test/r/events_restart_phase1.result @@ -1,12 +1,16 @@ -create database if not exists mysqltest_events_test; -use mysqltest_events_test; set global event_scheduler=off; +drop database if exists events_test; +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'); -select name from execution_log; -name -insert into mysql.event values ('db1','bad','select 42','root@localhost',NULL,1000,'MICROSECOND','2006-05-05 17:39:11','2006-05-05 17:39:20','2016-05-05 15:39:24','2016-05-05 15:39:11',NULL,'ENABLED','DROP','','comment1','SYSTEM'); -insert into mysql.event values ('db1','bad2','sect','root@localhost',NULL,1000,'SECOND','2006-05-05 17:39:11','2006-05-05 17:39:20','2016-05-05 15:39:24','2016-05-05 15:39:11',NULL,'ENABLED','DROP','','comment2','SYSTEM'); +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'); +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" diff --git a/mysql-test/r/events_restart_phase2.result b/mysql-test/r/events_restart_phase2.result index 703cb92324f..60ddf06bf23 100644 --- a/mysql-test/r/events_restart_phase2.result +++ b/mysql-test/r/events_restart_phase2.result @@ -1,6 +1,42 @@ -use mysqltest_events_test; -"Should get 0 rows because the queue aborted run -select distinct name from execution_log order by name; -name -delete from mysql.event where name like 'bad%'; -"Now restart the server again" +use events_test; +select @@event_scheduler; +@@event_scheduler +DISABLED +show events; +ERROR HY000: Cannot proceed because system tables used by Event Scheduler were found damaged at server start +select event_name from information_schema.events; +ERROR HY000: Cannot proceed because system tables used by Event Scheduler were found damaged at server start +show create event intact_check; +ERROR HY000: Cannot proceed because system tables used by Event Scheduler were found damaged at server start +drop event no_such_event; +ERROR HY000: Cannot proceed because system tables used by Event Scheduler were found damaged at server start +create event intact_check_1 on schedule every 5 hour do select 5; +ERROR HY000: Cannot proceed because system tables used by Event Scheduler were found damaged at server start +alter event intact_check_1 on schedule every 8 hour do select 8; +ERROR HY000: Cannot proceed because system tables used by Event Scheduler were found damaged at server start +alter event intact_check_1 rename to intact_check_2; +ERROR HY000: Cannot proceed because system tables used by Event Scheduler were found damaged at server start +drop event intact_check_1; +ERROR HY000: Cannot proceed because system tables used by Event Scheduler were found damaged at server start +drop event intact_check_2; +ERROR HY000: Cannot proceed because system tables used by Event Scheduler were found damaged at server start +drop event intact_check; +ERROR HY000: Cannot proceed because system tables used by Event Scheduler were found damaged at server start +set global event_scheduler=on; +ERROR HY000: Cannot proceed because system tables used by Event Scheduler were found damaged at server start +set global event_scheduler=off; +ERROR HY000: Cannot proceed because system tables used by Event Scheduler were found damaged at server start +show variables like 'event_scheduler'; +Variable_name Value +event_scheduler DISABLED +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; +Restore the original mysql.event table +drop table mysql.event; +rename table event_like to mysql.event; +Now let's restart the server again diff --git a/mysql-test/r/events_restart_phase3.result b/mysql-test/r/events_restart_phase3.result index e653d6a7c23..8ac00fdc70d 100644 --- a/mysql-test/r/events_restart_phase3.result +++ b/mysql-test/r/events_restart_phase3.result @@ -1,12 +1,12 @@ -use mysqltest_events_test; +use events_test; +select @@event_scheduler; +@@event_scheduler +ON "Should get 3 rows : abc1, abc2, abc3 select distinct name from execution_log order by name; name abc1 abc2 abc3 -drop event abc1; -drop event abc2; -drop event abc3; drop table execution_log; -drop database mysqltest_events_test; +drop database events_test; diff --git a/mysql-test/r/events_scheduling.result b/mysql-test/r/events_scheduling.result index d885dc3a048..b945b83093f 100644 --- a/mysql-test/r/events_scheduling.result +++ b/mysql-test/r/events_scheduling.result @@ -1,6 +1,8 @@ CREATE DATABASE IF NOT EXISTS events_test; USE events_test; SET GLOBAL event_scheduler=OFF; +Try agian to make sure it's allowed +SET GLOBAL event_scheduler=OFF; SHOW VARIABLES LIKE 'event_scheduler'; Variable_name Value event_scheduler OFF @@ -13,6 +15,8 @@ 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 diff --git a/mysql-test/r/events_time_zone.result b/mysql-test/r/events_time_zone.result index 3d5ff794848..b20aa445183 100644 --- a/mysql-test/r/events_time_zone.result +++ b/mysql-test/r/events_time_zone.result @@ -3,148 +3,6 @@ CREATE DATABASE mysqltest_db1; USE mysqltest_db1; 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; -SHOW EVENTS; -Db Name Definer Time zone Type Execute at Interval value Interval field Starts Ends Status -mysqltest_db1 e1 root@localhost +00:00 RECURRING NULL 1 DAY 2005-12-31 23:58:59 NULL ENABLED -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 -mysqltest_db1 e1 root@localhost -01:00 RECURRING NULL 1 DAY 2000-01-01 00:00:00 NULL ENABLED -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 -mysqltest_db1 e1 root@localhost +02:00 ONE TIME 2000-01-02 00:00:00 NULL NULL NULL NULL DISABLED -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 -mysqltest_db1 e1 root@localhost -03:00 RECURRING NULL 1 DAY 2005-12-31 20:58:59 2030-01-03 00:00:00 DISABLED -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 -mysqltest_db1 e1 root@localhost -03:00 RECURRING NULL 1 DAY 2005-12-31 20:58:59 2030-01-03 00:00:00 ENABLED -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; -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; -SELECT * FROM INFORMATION_SCHEMA.EVENTS; -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 -NULL mysqltest_db1 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 -NULL mysqltest_db1 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 -NULL mysqltest_db1 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 -SHOW EVENTS; -Db Name Definer Time zone Type Execute at Interval value Interval field Starts Ends Status -mysqltest_db1 e1 root@localhost +05:00 RECURRING NULL 1 DAY 2006-01-01 00:00:00 NULL ENABLED -mysqltest_db1 e2 root@localhost -05:00 RECURRING NULL 1 DAY 2006-01-01 00:00:00 NULL ENABLED -mysqltest_db1 e3 root@localhost +00:00 RECURRING NULL 1 DAY 2006-01-01 00:00:00 NULL ENABLED -SHOW CREATE EVENT e1; -Event sql_mode time_zone Create Event -e1 +05:00 CREATE EVENT `e1` ON SCHEDULE EVERY 1 DAY STARTS '2006-01-01 00:00:00' ON COMPLETION NOT PRESERVE ENABLE DO SELECT 1 -SHOW CREATE EVENT e2; -Event sql_mode time_zone Create Event -e2 -05:00 CREATE EVENT `e2` ON SCHEDULE EVERY 1 DAY STARTS '2006-01-01 00:00:00' ON COMPLETION NOT PRESERVE ENABLE DO SELECT 1 -SHOW CREATE EVENT e3; -Event sql_mode time_zone Create Event -e3 +00:00 CREATE EVENT `e3` ON SCHEDULE EVERY 1 DAY STARTS '2006-01-01 00:00:00' ON COMPLETION NOT PRESERVE ENABLE DO SELECT 1 -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. Event has not been altered -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. Event has not been altered -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 1584 Event execution time is in the past and ON COMPLETION NOT PRESERVE is set. Event has not been created -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 1584 Event execution time is in the past and ON COMPLETION NOT PRESERVE is set. Event has not been created -CREATE EVENT e4 ON SCHEDULE AT '1999-01-01 00:00:00' DO -SELECT 1; -Warnings: -Note 1584 Event execution time is in the past and ON COMPLETION NOT PRESERVE is set. Event has not been created -CREATE EVENT e4 ON SCHEDULE AT '1999-01-01 00:00:00' DISABLE -DO -SELECT 1; -Warnings: -Note 1584 Event execution time is in the past and ON COMPLETION NOT PRESERVE is set. Event has not been created -SHOW EVENTS; -Db Name Definer Time zone Type Execute at Interval value Interval field Starts Ends Status -mysqltest_db1 e1 root@localhost +05:00 RECURRING NULL 1 DAY 2006-01-01 00:00:00 NULL ENABLED -mysqltest_db1 e2 root@localhost -05:00 RECURRING NULL 1 DAY 2006-01-01 00:00:00 NULL ENABLED -mysqltest_db1 e3 root@localhost +00:00 RECURRING NULL 1 DAY 2006-01-01 00:00:00 NULL ENABLED -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 1533 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 1533 Event execution time is in the past. Event has been disabled -CREATE EVENT e5 ON SCHEDULE AT '1999-01-01 00:00:00' - ON COMPLETION PRESERVE -DO -SELECT 1; -Warnings: -Note 1533 Event execution time is in the past. Event has been disabled -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; -Db Name Definer Time zone Type Execute at Interval value Interval field Starts Ends Status -mysqltest_db1 e1 root@localhost +00:00 RECURRING NULL 1 HOUR 1999-01-01 00:00:00 1999-01-02 00:00:00 DISABLED -mysqltest_db1 e2 root@localhost +00:00 RECURRING NULL 1 HOUR 1999-01-01 00:00:00 NULL ENABLED -mysqltest_db1 e3 root@localhost +00:00 RECURRING NULL 1 HOUR 1999-01-01 00:00:00 1999-01-02 00:00:00 DISABLED -mysqltest_db1 e4 root@localhost +00:00 RECURRING NULL 1 HOUR 1999-01-01 00:00:00 1999-01-02 00:00:00 DISABLED -mysqltest_db1 e5 root@localhost +00:00 ONE TIME 1999-01-01 00:00:00 NULL NULL NULL NULL DISABLED -mysqltest_db1 e6 root@localhost +00:00 RECURRING NULL 1 HOUR 1999-01-01 00:00:00 NULL ENABLED -mysqltest_db1 e7 root@localhost +00:00 RECURRING NULL 1 HOUR 1999-01-01 00:00:00 1999-01-02 00:00:00 DISABLED -mysqltest_db1 e8 root@localhost +00:00 ONE TIME 1999-01-01 00:00:00 NULL NULL NULL NULL DISABLED -DROP EVENT e8; -DROP EVENT e7; -DROP EVENT e6; -DROP EVENT e5; -DROP EVENT e4; -DROP EVENT e3; -DROP EVENT e2; -DROP EVENT e1; CREATE TABLE t_step (step INT); INSERT INTO t_step VALUES (@step); CREATE FUNCTION round_to_step(i INT, n INT) RETURNS INT diff --git a/mysql-test/r/events_trans.result b/mysql-test/r/events_trans.result new file mode 100644 index 00000000000..145fb8be084 --- /dev/null +++ b/mysql-test/r/events_trans.result @@ -0,0 +1,159 @@ +drop database if exists events_test; +drop database if exists mysqltest_db2; +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; +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; +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 1526 Event 'e1' already exists +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; +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; +grant create, insert, select, delete on mysqltest_db2.* +to mysqltest_user1@localhost; +create database 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; +drop user mysqltest_user1@localhost; +drop database mysqltest_db2; +drop database events_test; diff --git a/mysql-test/t/events.test b/mysql-test/t/events.test index 39bc1063ace..c8054e49da4 100644 --- a/mysql-test/t/events.test +++ b/mysql-test/t/events.test @@ -1,7 +1,13 @@ # Can't test with embedded server that doesn't support grants -- source include/not_embedded.inc -create database if not exists events_test; +--disable_warnings +drop database if exists events_test; +drop database if exists db_x; +drop database if exists mysqltest_db2; +drop database if exists mysqltest_no_such_database; +--enable_warnings +create database events_test; use events_test; # @@ -215,62 +221,161 @@ set names latin1; # # -# mysql.event intact checking start +# 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. # -# There should be at least 1 second between the ALTERs or we can't catch the change of create_time!! +# +--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; -ALTER TABLE mysql.event ADD dummy INT FIRST; ---error ER_COL_COUNT_DOESNT_MATCH_CORRUPTED -SHOW EVENTS; -ALTER TABLE mysql.event DROP dummy, ADD dummy2 VARCHAR(64) FIRST; ---error ER_COL_COUNT_DOESNT_MATCH_CORRUPTED -SHOW EVENTS; -ALTER TABLE mysql.event DROP dummy2; +# +--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 make sure everything +--echo works as before +--echo +ALTER TABLE mysql.event ADD dummy INT; --replace_column 8 # 9 # SHOW EVENTS; +SELECT event_name FROM INFORMATION_SCHEMA.events; +--replace_regex /STARTS '[^']+'/STARTS '#'/ +SHOW CREATE EVENT intact_check; +--error ER_EVENT_DOES_NOT_EXIST +DROP EVENT no_such_event; +CREATE EVENT intact_check_1 ON SCHEDULE EVERY 5 HOUR DO SELECT 5; +ALTER EVENT intact_check_1 ON SCHEDULE EVERY 8 HOUR DO SELECT 8; +ALTER EVENT intact_check_1 RENAME TO intact_check_2; +--error ER_EVENT_DOES_NOT_EXIST +DROP EVENT intact_check_1; +DROP EVENT intact_check_2; +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; +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_CANNOT_LOAD_FROM_TABLE +SHOW EVENTS; +--error ER_CANNOT_LOAD_FROM_TABLE +SELECT event_name FROM INFORMATION_SCHEMA.events; +--error ER_EVENT_DOES_NOT_EXIST +SHOW CREATE EVENT intact_check; +--error ER_EVENT_DOES_NOT_EXIST +DROP EVENT no_such_event; +--error ER_CANNOT_LOAD_FROM_TABLE +CREATE EVENT intact_check_1 ON SCHEDULE EVERY 5 HOUR DO SELECT 5; +--error ER_EVENT_DOES_NOT_EXIST +ALTER EVENT intact_check_1 ON SCHEDULE EVERY 8 HOUR DO SELECT 8; +--error ER_EVENT_DOES_NOT_EXIST +ALTER EVENT intact_check_1 RENAME TO intact_check_2; +--error ER_EVENT_DOES_NOT_EXIST +DROP EVENT intact_check_1; +--error ER_EVENT_DOES_NOT_EXIST +DROP EVENT intact_check_2; +--error ER_EVENT_DOES_NOT_EXIST +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; -#sleep a bit or we won't catch the change of time ---sleep 1.1 -ALTER TABLE mysql.event MODIFY db char(64) character set cp1251 default ''; ---error ER_CANNOT_LOAD_FROM_TABLE -SELECT event_name FROM INFORMATION_SCHEMA.EVENTS; -ALTER TABLE mysql.event MODIFY db char(20) character set utf8 collate utf8_bin default ''; -#wait a bit or we won't see the difference because of seconds resolution ---sleep 1.1 -SHOW CREATE TABLE mysql.event; +--echo +--echo Drop some columns and try more checks. +--echo +--echo +ALTER TABLE mysql.event DROP comment, DROP starts; --error ER_CANNOT_LOAD_FROM_TABLE -SELECT event_name FROM INFORMATION_SCHEMA.EVENTS; ---sleep 1.1 -ALTER TABLE mysql.event MODIFY db char(64) character set utf8 collate utf8_bin default ''; ---sleep 1.1 ---echo "This should work" ---replace_column 8 # 9 # SHOW EVENTS; ---sleep 1.1 -ALTER TABLE mysql.event MODIFY db char(64) character set cp1251 default ''; --error ER_CANNOT_LOAD_FROM_TABLE SELECT event_name FROM INFORMATION_SCHEMA.EVENTS; ---sleep 1.1 -ALTER TABLE mysql.event MODIFY db varchar(64) character set utf8 collate utf8_bin default ''; ---sleep 1.1 --error ER_CANNOT_LOAD_FROM_TABLE -SELECT event_name FROM INFORMATION_SCHEMA.EVENTS; ---sleep 1.1 -ALTER TABLE mysql.event DROP comment, DROP starts; ---sleep 1.1 +SHOW CREATE EVENT intact_check; +--error ER_EVENT_DOES_NOT_EXIST +DROP EVENT no_such_event; --error ER_COL_COUNT_DOESNT_MATCH_CORRUPTED -SELECT event_name FROM INFORMATION_SCHEMA.EVENTS; +CREATE EVENT intact_check_1 ON SCHEDULE EVERY 5 HOUR DO SELECT 5; +--error ER_EVENT_DOES_NOT_EXIST +ALTER EVENT intact_check_1 ON SCHEDULE EVERY 8 HOUR DO SELECT 8; +--error ER_EVENT_DOES_NOT_EXIST +ALTER EVENT intact_check_1 RENAME TO intact_check_2; +--error ER_EVENT_DOES_NOT_EXIST +DROP EVENT intact_check_1; +--error ER_EVENT_DOES_NOT_EXIST +DROP EVENT intact_check_2; +# Should succeed +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; -INSERT INTO mysql.event SELECT * FROM event_like; DROP TABLE event_like; --replace_column 8 # 9 # SHOW EVENTS; -DROP EVENT intact_check; # # mysql.event intact checking end # @@ -424,5 +529,200 @@ SHOW EVENTS FROM aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa 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_TABLE_NOT_LOCKED +create event e2 on schedule every 10 hour do select 1; +--error ER_TABLE_NOT_LOCKED +alter event e2 disable; +--error ER_TABLE_NOT_LOCKED +alter event e2 rename to e3; +--error ER_TABLE_NOT_LOCKED +drop event e2; +--error ER_TABLE_NOT_LOCKED +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_TABLE_NOT_LOCKED +create event e2 on schedule every 10 hour do select 1; +--error ER_TABLE_NOT_LOCKED +alter event e2 disable; +--error ER_TABLE_NOT_LOCKED +alter event e2 rename to e3; +--error ER_TABLE_NOT_LOCKED +drop event e2; +--error ER_TABLE_NOT_LOCKED +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_TABLE_NOT_LOCKED_FOR_WRITE +create event e2 on schedule every 10 hour do select 1; +--error ER_TABLE_NOT_LOCKED_FOR_WRITE +alter event e2 disable; +--error ER_TABLE_NOT_LOCKED_FOR_WRITE +alter event e2 rename to e3; +--error ER_TABLE_NOT_LOCKED_FOR_WRITE +drop event e2; +--error ER_TABLE_NOT_LOCKED_FOR_WRITE +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_TABLE_NOT_LOCKED_FOR_WRITE +create event e2 on schedule every 10 hour do select 1; +--error ER_TABLE_NOT_LOCKED_FOR_WRITE +alter event e2 disable; +--error ER_TABLE_NOT_LOCKED_FOR_WRITE +alter event e2 rename to e3; +--error ER_TABLE_NOT_LOCKED_FOR_WRITE +drop event e2; +--error ER_TABLE_NOT_LOCKED_FOR_WRITE +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; +create event e2 on schedule every 10 hour do select 1; +alter event e2 disable; +alter event e2 rename to e3; +drop event e3; +drop event e1; +unlock tables; +--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 ;| drop database events_test; diff --git a/mysql-test/t/events_bugs.test b/mysql-test/t/events_bugs.test index 56c44dc3a9f..8343c6b4bc6 100644 --- a/mysql-test/t/events_bugs.test +++ b/mysql-test/t/events_bugs.test @@ -1,7 +1,12 @@ # Can't test with embedded server that doesn't support grants -- source include/not_embedded.inc -create database if not exists events_test; +--disable_warnings +drop database if exists events_test; +drop database if exists mysqltest_db1; +drop database if exists mysqltest_db2; +--enable_warnings +create database events_test; use events_test; # @@ -429,6 +434,184 @@ 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; +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; + +# # End of tests # drop database events_test; diff --git a/mysql-test/t/events_restart_phase1.test b/mysql-test/t/events_restart_phase1.test index 0a84f6c4966..6a94ef12222 100644 --- a/mysql-test/t/events_restart_phase1.test +++ b/mysql-test/t/events_restart_phase1.test @@ -1,19 +1,43 @@ # Can't test with embedded server that doesn't support grants -- source include/not_embedded.inc +# +# 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; --disable_warnings -create database if not exists mysqltest_events_test; +drop database if exists events_test; --enable_warnings - -use mysqltest_events_test; -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'); ---sleep 1.5 -select name from execution_log; -insert into mysql.event values ('db1','bad','select 42','root@localhost',NULL,1000,'MICROSECOND','2006-05-05 17:39:11','2006-05-05 17:39:20','2016-05-05 15:39:24','2016-05-05 15:39:11',NULL,'ENABLED','DROP','','comment1','SYSTEM'); -insert into mysql.event values ('db1','bad2','sect','root@localhost',NULL,1000,'SECOND','2006-05-05 17:39:11','2006-05-05 17:39:20','2016-05-05 15:39:24','2016-05-05 15:39:11',NULL,'ENABLED','DROP','','comment2','SYSTEM'); +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" diff --git a/mysql-test/t/events_restart_phase2.test b/mysql-test/t/events_restart_phase2.test index 845472377ba..c3f799634b3 100644 --- a/mysql-test/t/events_restart_phase2.test +++ b/mysql-test/t/events_restart_phase2.test @@ -1,9 +1,41 @@ # Can't test with embedded server that doesn't support grants -- source include/not_embedded.inc -use mysqltest_events_test; ---sleep 1.5 ---echo "Should get 0 rows because the queue aborted run -select distinct name from execution_log order by name; -delete from mysql.event where name like 'bad%'; ---echo "Now restart the server again" +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_EVENTS_DB_ERROR +set global event_scheduler=on; +--error ER_EVENTS_DB_ERROR +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 Now let's restart the server again diff --git a/mysql-test/t/events_restart_phase3.test b/mysql-test/t/events_restart_phase3.test index 76cd9d22752..1be40c72717 100644 --- a/mysql-test/t/events_restart_phase3.test +++ b/mysql-test/t/events_restart_phase3.test @@ -1,14 +1,18 @@ # Can't test with embedded server that doesn't support grants -- source include/not_embedded.inc - -use mysqltest_events_test; ---sleep 2 +# +# 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 --echo "Should get 3 rows : abc1, abc2, abc3 select distinct name from execution_log order by name; - -drop event abc1; -drop event abc2; -drop event abc3; drop table execution_log; - -drop database mysqltest_events_test; +# Will drop all events +drop database events_test; diff --git a/mysql-test/t/events_scheduling.test b/mysql-test/t/events_scheduling.test index e3b55685e65..ff9dfaad927 100644 --- a/mysql-test/t/events_scheduling.test +++ b/mysql-test/t/events_scheduling.test @@ -6,12 +6,16 @@ CREATE DATABASE IF NOT EXISTS events_test; USE events_test; SET GLOBAL event_scheduler=OFF; +--echo Try agian 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; diff --git a/mysql-test/t/events_time_zone.test b/mysql-test/t/events_time_zone.test index fff84c7a995..5f929e0b07a 100644 --- a/mysql-test/t/events_time_zone.test +++ b/mysql-test/t/events_time_zone.test @@ -20,6 +20,7 @@ let $old_db= `SELECT DATABASE()`; USE mysqltest_db1; SET GLOBAL EVENT_SCHEDULER= OFF; +SET @save_time_zone= @@TIME_ZONE; # @@ -31,176 +32,6 @@ SET GLOBAL EVENT_SCHEDULER= OFF; # WL#3698: Events: execution in local time zone # -SET @save_time_zone= @@TIME_ZONE; - -#---------------------------------------------------------------------- - -# We will use a separate connection because SET TIMESTAMP will stop -# the clock in that connection. - -connect (conn1, localhost, root, , mysqltest_db1); - -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; - - -# 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; - - -disconnect conn1; -connection default; - #---------------------------------------------------------------------- # Create rounding function. diff --git a/mysql-test/t/events_trans.test b/mysql-test/t/events_trans.test new file mode 100644 index 00000000000..41044f9975f --- /dev/null +++ b/mysql-test/t/events_trans.test @@ -0,0 +1,159 @@ +# +# Tests that require transactions +# +-- source include/have_innodb.inc +--disable_warnings +drop database if exists events_test; +drop database if exists mysqltest_db2; +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; +# +# Privilege checks +# +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; +connection default; +drop user mysqltest_user1@localhost; +drop database mysqltest_db2; + +# +# Cleanup +# +drop database events_test; + |