diff options
Diffstat (limited to 'mysql-test/r')
| -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 |
8 files changed, 726 insertions, 213 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; |