diff options
29 files changed, 1381 insertions, 381 deletions
diff --git a/client/mysqldump.c b/client/mysqldump.c index 5f2749eef77..c86a2fc385f 100644 --- a/client/mysqldump.c +++ b/client/mysqldump.c @@ -1431,6 +1431,8 @@ static uint dump_events_for_db(char *db) strcpy(delimiter, ";"); if (mysql_num_rows(event_list_res) > 0) { + fprintf(sql_file, "/*!50106 SET @save_time_zone= @@TIME_ZONE */ ;\n"); + while ((event_list_row= mysql_fetch_row(event_list_res)) != NULL) { event_name= quote_name(event_list_row[1], name_buff, 0); @@ -1447,13 +1449,13 @@ static uint dump_events_for_db(char *db) if the user has EXECUTE privilege he can see event names, but not the event body! */ - if (strlen(row[2]) != 0) + if (strlen(row[3]) != 0) { if (opt_drop) fprintf(sql_file, "/*!50106 DROP EVENT IF EXISTS %s */%s\n", event_name, delimiter); - delimit_test= create_delimiter(row[2], delimiter, sizeof(delimiter)); + delimit_test= create_delimiter(row[3], delimiter, sizeof(delimiter)); if (delimit_test == NULL) { fprintf(stderr, "%s: Warning: Can't dump event '%s'\n", event_name, my_progname); @@ -1461,11 +1463,15 @@ static uint dump_events_for_db(char *db) } fprintf(sql_file, "DELIMITER %s\n", delimiter); - fprintf(sql_file, "/*!50106 %s */ %s\n", row[2], delimiter); + fprintf(sql_file, "/*!50106 SET TIME_ZONE= '%s' */ %s\n", + row[2], delimiter); + fprintf(sql_file, "/*!50106 %s */ %s\n", row[3], delimiter); } } /* end of event printing */ } /* end of list of events */ fprintf(sql_file, "DELIMITER ;\n"); + fprintf(sql_file, "/*!50106 SET TIME_ZONE= @save_time_zone */ ;\n"); + mysql_free_result(event_res); } mysql_free_result(event_list_res); diff --git a/mysql-test/include/wait_condition.inc b/mysql-test/include/wait_condition.inc index cd80b58d44e..2e6bd276aac 100644 --- a/mysql-test/include/wait_condition.inc +++ b/mysql-test/include/wait_condition.inc @@ -11,13 +11,28 @@ # SELECT c = 3 FROM t; # --source include/wait_condition.inc # +# OR +# +# let $wait_timeout= 60; # Override default 30 seconds with 60. +# let $wait_condition= +# SELECT c = 3 FROM t; +# --source include/wait_condition.inc +# # EXAMPLE -# events_bugs.test +# events_bugs.test, events_time_zone.test # --disable_query_log let $wait_counter= 300; +if ($wait_timeout) +{ + let $wait_counter= `SELECT $wait_timeout * 10`; +} +# Reset $wait_timeout so that its value won't be used on subsequent +# calls, and default will be used instead. +let $wait_timeout= 0; + while ($wait_counter) { let $success= `$wait_condition`; diff --git a/mysql-test/lib/init_db.sql b/mysql-test/lib/init_db.sql index c6c0f1d81dd..e689db94e9f 100644 --- a/mysql-test/lib/init_db.sql +++ b/mysql-test/lib/init_db.sql @@ -645,6 +645,7 @@ CREATE TABLE event ( 'HIGH_NOT_PRECEDENCE' ) DEFAULT '' NOT NULL, 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'; diff --git a/mysql-test/r/events.result b/mysql-test/r/events.result index af864c57efa..cab5762012c 100644 --- a/mysql-test/r/events.result +++ b/mysql-test/r/events.result @@ -118,81 +118,81 @@ drop table t_event3; set names utf8; CREATE EVENT root6 ON SCHEDULE EVERY '10:20' MINUTE_SECOND ON COMPLETION PRESERVE ENABLE COMMENT 'some comment' DO select 1; SHOW CREATE EVENT root6; -Event sql_mode Create Event -root6 CREATE EVENT `root6` ON SCHEDULE EVERY '10:20' MINUTE_SECOND ON COMPLETION PRESERVE ENABLE COMMENT 'some comment' DO select 1 +Event sql_mode time_zone Create Event +root6 SYSTEM CREATE EVENT `root6` ON SCHEDULE EVERY '10:20' MINUTE_SECOND STARTS '#' ON COMPLETION PRESERVE ENABLE COMMENT 'some comment' DO select 1 create event root7 on schedule every 2 year do select 1; SHOW CREATE EVENT root7; -Event sql_mode Create Event -root7 CREATE EVENT `root7` ON SCHEDULE EVERY 2 YEAR ON COMPLETION NOT PRESERVE ENABLE DO select 1 +Event sql_mode time_zone Create Event +root7 SYSTEM CREATE EVENT `root7` ON SCHEDULE EVERY 2 YEAR STARTS '#' ON COMPLETION NOT PRESERVE ENABLE DO select 1 create event root8 on schedule every '2:5' year_month do select 1; SHOW CREATE EVENT root8; -Event sql_mode Create Event -root8 CREATE EVENT `root8` ON SCHEDULE EVERY '2-5' YEAR_MONTH ON COMPLETION NOT PRESERVE ENABLE DO select 1 +Event sql_mode time_zone Create Event +root8 SYSTEM CREATE EVENT `root8` ON SCHEDULE EVERY '2-5' YEAR_MONTH STARTS '#' ON COMPLETION NOT PRESERVE ENABLE DO select 1 create event root8_1 on schedule every '2:15' year_month do select 1; SHOW CREATE EVENT root8_1; -Event sql_mode Create Event -root8_1 CREATE EVENT `root8_1` ON SCHEDULE EVERY '3-3' YEAR_MONTH ON COMPLETION NOT PRESERVE ENABLE DO select 1 +Event sql_mode time_zone Create Event +root8_1 SYSTEM CREATE EVENT `root8_1` ON SCHEDULE EVERY '3-3' YEAR_MONTH STARTS '#' ON COMPLETION NOT PRESERVE ENABLE DO select 1 create event root9 on schedule every 2 week ON COMPLETION PRESERVE DISABLE COMMENT 'коментар на кирилица' do select 1; SHOW CREATE EVENT root9; -Event sql_mode Create Event -root9 CREATE EVENT `root9` ON SCHEDULE EVERY 2 WEEK ON COMPLETION PRESERVE DISABLE COMMENT 'коментар на кирилица' DO select 1 +Event sql_mode time_zone Create Event +root9 SYSTEM CREATE EVENT `root9` ON SCHEDULE EVERY 2 WEEK STARTS '#' ON COMPLETION PRESERVE DISABLE COMMENT 'коментар на кирилица' DO select 1 create event root10 on schedule every '20:5' day_hour do select 1; SHOW CREATE EVENT root10; -Event sql_mode Create Event -root10 CREATE EVENT `root10` ON SCHEDULE EVERY '20 5' DAY_HOUR ON COMPLETION NOT PRESERVE ENABLE DO select 1 +Event sql_mode time_zone Create Event +root10 SYSTEM CREATE EVENT `root10` ON SCHEDULE EVERY '20 5' DAY_HOUR STARTS '#' ON COMPLETION NOT PRESERVE ENABLE DO select 1 create event root11 on schedule every '20:25' day_hour do select 1; SHOW CREATE EVENT root11; -Event sql_mode Create Event -root11 CREATE EVENT `root11` ON SCHEDULE EVERY '21 1' DAY_HOUR ON COMPLETION NOT PRESERVE ENABLE DO select 1 +Event sql_mode time_zone Create Event +root11 SYSTEM CREATE EVENT `root11` ON SCHEDULE EVERY '21 1' DAY_HOUR STARTS '#' ON COMPLETION NOT PRESERVE ENABLE DO select 1 create event root12 on schedule every '20:25' hour_minute do select 1; SHOW CREATE EVENT root12; -Event sql_mode Create Event -root12 CREATE EVENT `root12` ON SCHEDULE EVERY '20:25' HOUR_MINUTE ON COMPLETION NOT PRESERVE ENABLE DO select 1 +Event sql_mode time_zone Create Event +root12 SYSTEM CREATE EVENT `root12` ON SCHEDULE EVERY '20:25' HOUR_MINUTE STARTS '#' ON COMPLETION NOT PRESERVE ENABLE DO select 1 create event root13 on schedule every '25:25' hour_minute do select 1; SHOW CREATE EVENT root13; -Event sql_mode Create Event -root13 CREATE EVENT `root13` ON SCHEDULE EVERY '25:25' HOUR_MINUTE ON COMPLETION NOT PRESERVE ENABLE DO select 1 +Event sql_mode time_zone Create Event +root13 SYSTEM CREATE EVENT `root13` ON SCHEDULE EVERY '25:25' HOUR_MINUTE STARTS '#' ON COMPLETION NOT PRESERVE ENABLE DO select 1 create event root13_1 on schedule every '11:65' hour_minute do select 1; SHOW CREATE EVENT root13_1; -Event sql_mode Create Event -root13_1 CREATE EVENT `root13_1` ON SCHEDULE EVERY '12:5' HOUR_MINUTE ON COMPLETION NOT PRESERVE ENABLE DO select 1 +Event sql_mode time_zone Create Event +root13_1 SYSTEM CREATE EVENT `root13_1` ON SCHEDULE EVERY '12:5' HOUR_MINUTE STARTS '#' ON COMPLETION NOT PRESERVE ENABLE DO select 1 create event root14 on schedule every '35:35' minute_second do select 1; SHOW CREATE EVENT root14; -Event sql_mode Create Event -root14 CREATE EVENT `root14` ON SCHEDULE EVERY '35:35' MINUTE_SECOND ON COMPLETION NOT PRESERVE ENABLE DO select 1 +Event sql_mode time_zone Create Event +root14 SYSTEM CREATE EVENT `root14` ON SCHEDULE EVERY '35:35' MINUTE_SECOND STARTS '#' ON COMPLETION NOT PRESERVE ENABLE DO select 1 create event root15 on schedule every '35:66' minute_second do select 1; SHOW CREATE EVENT root15; -Event sql_mode Create Event -root15 CREATE EVENT `root15` ON SCHEDULE EVERY '36:6' MINUTE_SECOND ON COMPLETION NOT PRESERVE ENABLE DO select 1 +Event sql_mode time_zone Create Event +root15 SYSTEM CREATE EVENT `root15` ON SCHEDULE EVERY '36:6' MINUTE_SECOND STARTS '#' ON COMPLETION NOT PRESERVE ENABLE DO select 1 create event root16 on schedule every '35:56' day_minute do select 1; SHOW CREATE EVENT root16; -Event sql_mode Create Event -root16 CREATE EVENT `root16` ON SCHEDULE EVERY '1 11:56' DAY_MINUTE ON COMPLETION NOT PRESERVE ENABLE DO select 1 +Event sql_mode time_zone Create Event +root16 SYSTEM CREATE EVENT `root16` ON SCHEDULE EVERY '1 11:56' DAY_MINUTE STARTS '#' ON COMPLETION NOT PRESERVE ENABLE DO select 1 create event root17 on schedule every '35:12:45' day_minute do select 1; SHOW CREATE EVENT root17; -Event sql_mode Create Event -root17 CREATE EVENT `root17` ON SCHEDULE EVERY '35 12:45' DAY_MINUTE ON COMPLETION NOT PRESERVE ENABLE DO select 1 +Event sql_mode time_zone Create Event +root17 SYSTEM CREATE EVENT `root17` ON SCHEDULE EVERY '35 12:45' DAY_MINUTE STARTS '#' ON COMPLETION NOT PRESERVE ENABLE DO select 1 create event root17_1 on schedule every '35:25:65' day_minute do select 1; SHOW CREATE EVENT root17_1; -Event sql_mode Create Event -root17_1 CREATE EVENT `root17_1` ON SCHEDULE EVERY '36 2:5' DAY_MINUTE ON COMPLETION NOT PRESERVE ENABLE DO select 1 +Event sql_mode time_zone Create Event +root17_1 SYSTEM CREATE EVENT `root17_1` ON SCHEDULE EVERY '36 2:5' DAY_MINUTE STARTS '#' ON COMPLETION NOT PRESERVE ENABLE DO select 1 create event root18 on schedule every '35:12:45' hour_second do select 1; SHOW CREATE EVENT root18; -Event sql_mode Create Event -root18 CREATE EVENT `root18` ON SCHEDULE EVERY '35:12:45' HOUR_SECOND ON COMPLETION NOT PRESERVE ENABLE DO select 1 +Event sql_mode time_zone Create Event +root18 SYSTEM CREATE EVENT `root18` ON SCHEDULE EVERY '35:12:45' HOUR_SECOND STARTS '#' ON COMPLETION NOT PRESERVE ENABLE DO select 1 create event root19 on schedule every '15:59:85' hour_second do select 1; SHOW CREATE EVENT root19; -Event sql_mode Create Event -root19 CREATE EVENT `root19` ON SCHEDULE EVERY '16:0:25' HOUR_SECOND ON COMPLETION NOT PRESERVE ENABLE DO select 1 +Event sql_mode time_zone Create Event +root19 SYSTEM CREATE EVENT `root19` ON SCHEDULE EVERY '16:0:25' HOUR_SECOND STARTS '#' ON COMPLETION NOT PRESERVE ENABLE DO select 1 create event root20 on schedule every '50:20:12:45' day_second do select 1; SHOW CREATE EVENT root20; -Event sql_mode Create Event -root20 CREATE EVENT `root20` ON SCHEDULE EVERY '50 20:12:45' DAY_SECOND ON COMPLETION NOT PRESERVE ENABLE DO select 1 +Event sql_mode time_zone Create Event +root20 SYSTEM CREATE EVENT `root20` ON SCHEDULE EVERY '50 20:12:45' DAY_SECOND STARTS '#' ON COMPLETION NOT PRESERVE ENABLE DO select 1 set names cp1251; create event ðóóò21 on schedule every '50:23:59:95' day_second COMMENT 'òîâà å 1251 êîìåíòàð' do select 1; SHOW CREATE EVENT ðóóò21; -Event sql_mode Create Event -ðóóò21 CREATE EVENT `ðóóò21` ON SCHEDULE EVERY '51 0:0:35' DAY_SECOND ON COMPLETION NOT PRESERVE ENABLE COMMENT 'òîâà å 1251 êîìåíòàð' DO select 1 +Event sql_mode time_zone Create Event +ðóóò21 SYSTEM CREATE EVENT `ðóóò21` ON SCHEDULE EVERY '51 0:0:35' DAY_SECOND STARTS '#' ON COMPLETION NOT PRESERVE ENABLE COMMENT 'òîâà å 1251 êîìåíòàð' DO select 1 insert into mysql.event (db, name, body, definer, interval_value, interval_field) values (database(), "root22", "select 1", user(), 100, "SECOND_MICROSECOND"); show create event root22; ERROR 42000: This version of MySQL doesn't yet support 'MICROSECOND' @@ -225,18 +225,18 @@ drop event ðóóò21; set names latin1; CREATE EVENT intact_check ON SCHEDULE EVERY 10 HOUR DO SELECT "nothing"; SHOW EVENTS; -Db Name Definer Type Execute at Interval value Interval field Starts Ends Status -events_test intact_check root@localhost RECURRING NULL 10 HOUR # # ENABLED +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 16, found 17. Table probably corrupted +ERROR HY000: Column count of mysql.event is wrong. Expected 17, found 18. Table 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 16, found 17. Table probably corrupted +ERROR HY000: Column count of mysql.event is wrong. Expected 17, found 18. Table probably corrupted ALTER TABLE mysql.event DROP dummy2; SHOW EVENTS; -Db Name Definer Type Execute at Interval value Interval field Starts Ends Status -events_test intact_check root@localhost RECURRING NULL 10 HOUR # # ENABLED +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 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 ''; @@ -262,6 +262,7 @@ event CREATE TABLE `event` ( `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; @@ -269,8 +270,8 @@ ERROR HY000: Cannot load from mysql.event. Table probably corrupted. See error l ALTER TABLE mysql.event MODIFY db char(64) character set utf8 collate utf8_bin default ''; "This should work" SHOW EVENTS; -Db Name Definer Type Execute at Interval value Interval field Starts Ends Status -events_test intact_check root@localhost RECURRING NULL 10 HOUR # # ENABLED +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. Table probably corrupted. See error log. @@ -279,14 +280,14 @@ SELECT event_name FROM INFORMATION_SCHEMA.EVENTS; ERROR HY000: Cannot load from mysql.event. Table probably corrupted. See error log. ALTER TABLE mysql.event DROP comment, DROP starts; SELECT event_name FROM INFORMATION_SCHEMA.EVENTS; -ERROR HY000: Column count of mysql.event is wrong. Expected 16, found 14. Table probably corrupted +ERROR HY000: Column count of mysql.event is wrong. Expected 17, found 15. Table probably corrupted DROP TABLE mysql.event; CREATE TABLE mysql.event like event_like; INSERT INTO mysql.event SELECT * FROM event_like; DROP TABLE event_like; SHOW EVENTS; -Db Name Definer Type Execute at Interval value Interval field Starts Ends Status -events_test intact_check root@localhost RECURRING NULL 10 HOUR # # ENABLED +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; @@ -399,5 +400,5 @@ ERROR 42000: Incorrect database name 'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa SHOW EVENTS FROM ``; ERROR 42000: Incorrect database name '' SHOW EVENTS FROM `events\\test`; -Db Name Definer Type Execute at Interval value Interval field Starts Ends Status +Db Name Definer Time zone Type Execute at Interval value Interval field Starts Ends Status drop database events_test; diff --git a/mysql-test/r/events_bugs.result b/mysql-test/r/events_bugs.result index a7f0594588d..1a34f098b12 100644 --- a/mysql-test/r/events_bugs.result +++ b/mysql-test/r/events_bugs.result @@ -25,9 +25,12 @@ ERROR HY000: Incorrect STARTS value: '99990101000000' create event e_55 on schedule every 10 minute ends 99990101000000 do drop table t; ERROR HY000: ENDS is either invalid or before STARTS create event e_55 on schedule at 10000101000000 do drop table t; -ERROR HY000: Activation (AT) time is in the past +ERROR HY000: Incorrect AT value: '10000101000000' create event e_55 on schedule at 20000101000000 do drop table t; -ERROR HY000: Activation (AT) time is in the past +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 create event e_55 on schedule at 20200101000000 starts 10000101000000 do drop table t; ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'starts 10000101000000 do drop table t' at line 1 create event e_55 on schedule at 20200101000000 ends 10000101000000 do drop table t; diff --git a/mysql-test/r/events_grant.result b/mysql-test/r/events_grant.result index a28c30a9345..8bcf40b5167 100644 --- a/mysql-test/r/events_grant.result +++ b/mysql-test/r/events_grant.result @@ -2,8 +2,8 @@ CREATE DATABASE IF NOT EXISTS events_test; use events_test; CREATE EVENT one_event ON SCHEDULE EVERY 10 SECOND DO SELECT 123; SHOW EVENTS; -Db Name Definer Type Execute at Interval value Interval field Starts Ends Status -events_test one_event root@localhost RECURRING NULL 10 SECOND # # ENABLED +Db Name Definer Time zone Type Execute at Interval value Interval field Starts Ends Status +events_test one_event root@localhost SYSTEM RECURRING NULL 10 # # NULL ENABLED SELECT EVENT_CATALOG, EVENT_SCHEMA, EVENT_NAME, DEFINER, EVENT_BODY, EVENT_DEFINITION, EVENT_TYPE, EXECUTE_AT, INTERVAL_VALUE, INTERVAL_FIELD, STATUS,ON_COMPLETION, EVENT_COMMENT FROM INFORMATION_SCHEMA.EVENTS ORDER BY EVENT_SCHEMA, EVENT_NAME; EVENT_CATALOG EVENT_SCHEMA EVENT_NAME DEFINER EVENT_BODY EVENT_DEFINITION EVENT_TYPE EXECUTE_AT INTERVAL_VALUE INTERVAL_FIELD STATUS ON_COMPLETION EVENT_COMMENT NULL events_test one_event root@localhost SQL SELECT 123 RECURRING NULL 10 SECOND ENABLED NOT PRESERVE @@ -29,8 +29,8 @@ ERROR 42000: Access denied for user 'ev_test'@'localhost' to database 'events_te USE events_test; "We should see one event"; SHOW EVENTS; -Db Name Definer Type Execute at Interval value Interval field Starts Ends Status -events_test one_event root@localhost RECURRING NULL 10 SECOND # # ENABLED +Db Name Definer Time zone Type Execute at Interval value Interval field Starts Ends Status +events_test one_event root@localhost SYSTEM RECURRING NULL 10 # # NULL ENABLED SELECT CONCAT("Let's create some new events from the name of ", USER()); CONCAT("Let's create some new events from the name of ", USER()) Let's create some new events from the name of ev_test@localhost @@ -40,18 +40,18 @@ CREATE EVENT two_event ON SCHEDULE EVERY 20 SECOND ON COMPLETION NOT PRESERVE CO CREATE EVENT three_event ON SCHEDULE EVERY 20 SECOND ON COMPLETION PRESERVE COMMENT "three event" DO SELECT 123; "Now we should see 3 events:"; SHOW EVENTS; -Db Name Definer Type Execute at Interval value Interval field Starts Ends Status -events_test one_event root@localhost RECURRING NULL 10 SECOND # # ENABLED -events_test three_event ev_test@localhost RECURRING NULL 20 SECOND # # ENABLED -events_test two_event ev_test@localhost RECURRING NULL 20 SECOND # # ENABLED +Db Name Definer Time zone Type Execute at Interval value Interval field Starts Ends Status +events_test one_event root@localhost SYSTEM RECURRING NULL 10 # # NULL ENABLED +events_test three_event ev_test@localhost SYSTEM RECURRING NULL 20 # # NULL ENABLED +events_test two_event ev_test@localhost SYSTEM RECURRING NULL 20 # # NULL ENABLED "This should show us only 2 events:"; SHOW EVENTS LIKE 't%event'; -Db Name Definer Type Execute at Interval value Interval field Starts Ends Status -events_test three_event ev_test@localhost RECURRING NULL 20 SECOND # # ENABLED -events_test two_event ev_test@localhost RECURRING NULL 20 SECOND # # ENABLED +Db Name Definer Time zone Type Execute at Interval value Interval field Starts Ends Status +events_test three_event ev_test@localhost SYSTEM RECURRING NULL 20 # # NULL ENABLED +events_test two_event ev_test@localhost SYSTEM RECURRING NULL 20 # # NULL ENABLED "This should show us no events:"; SHOW EVENTS FROM test LIKE '%'; -Db Name Definer Type Execute at Interval value Interval field Starts Ends Status +Db Name Definer Time zone Type Execute at Interval value Interval field Starts Ends Status GRANT EVENT ON events_test2.* TO ev_test@localhost; USE events_test2; CREATE EVENT four_event ON SCHEDULE EVERY 20 SECOND DO SELECT 42; diff --git a/mysql-test/r/events_restart_phase1.result b/mysql-test/r/events_restart_phase1.result index a7a46fa0ab1..0c032884dc4 100644 --- a/mysql-test/r/events_restart_phase1.result +++ b/mysql-test/r/events_restart_phase1.result @@ -7,6 +7,6 @@ create event abc2 on schedule every 1 second do insert into execution_log value( 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'); -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'); +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'); "Now we restart the server" diff --git a/mysql-test/r/events_scheduling.result b/mysql-test/r/events_scheduling.result index 180c2e4883d..d885dc3a048 100644 --- a/mysql-test/r/events_scheduling.result +++ b/mysql-test/r/events_scheduling.result @@ -83,3 +83,4 @@ DROP TABLE table_2; DROP TABLE table_3; DROP TABLE table_4; DROP DATABASE events_test; +SET GLOBAL event_scheduler=OFF; diff --git a/mysql-test/r/events_time_zone.result b/mysql-test/r/events_time_zone.result new file mode 100644 index 00000000000..3d5ff794848 --- /dev/null +++ b/mysql-test/r/events_time_zone.result @@ -0,0 +1,291 @@ +DROP DATABASE IF EXISTS mysqltest_db1; +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 +BEGIN +DECLARE step INT; +SELECT * INTO step FROM t_step; +# We add 0.1 as a protection from inexact division. +RETURN FLOOR((i % (step * n) + 0.1) / step); +END// +SET @step3= @step * 3; +SET @step6= @step * 6; +SET @unix_time= @unix_time - @unix_time % @step6; +INSERT INTO mysql.time_zone VALUES (NULL, 'N'); +SET @tzid= LAST_INSERT_ID(); +INSERT INTO mysql.time_zone_transition_type +VALUES (@tzid, 0, 0, 0, 'b16420_0'); +INSERT INTO mysql.time_zone_transition_type +VALUES (@tzid, 1, @step3 - @step, 1, 'b16420_1'); +INSERT INTO mysql.time_zone_name VALUES ('bug16420', @tzid); +CREATE TABLE t1 (count INT, unix_time INT, local_time INT, comment CHAR(80)); +CREATE TABLE t2 (count INT); +INSERT INTO t2 VALUES (1); +CREATE FUNCTION f1(comment CHAR(80)) RETURNS INT +BEGIN +DECLARE orig_tz CHAR(64); +DECLARE unix_time INT; +DECLARE local_now DATETIME; +DECLARE utc_now DATETIME; +DECLARE local_time INT; +SET unix_time= UNIX_TIMESTAMP(); +SET local_now= FROM_UNIXTIME(unix_time); +SET orig_tz= @@TIME_ZONE; +SET TIME_ZONE = '+00:00'; +SET utc_now= FROM_UNIXTIME(unix_time); +SET TIME_ZONE= orig_tz; +SET local_time = unix_time + TIMESTAMPDIFF(SECOND, utc_now, local_now); +SET unix_time= round_to_step(unix_time, 6); +SET local_time= round_to_step(local_time, 6); +INSERT INTO t1 VALUES ((SELECT count FROM t2), +unix_time, local_time, comment); +RETURN 0; +END// +SET TIME_ZONE= '+00:00'; +CREATE EVENT e1 ON SCHEDULE EVERY @step SECOND +STARTS FROM_UNIXTIME(@unix_time) DO SELECT f1("<e1>"); +SET TIME_ZONE= 'bug16420'; +CREATE EVENT e2 ON SCHEDULE EVERY @step SECOND +STARTS FROM_UNIXTIME(@unix_time) DO SELECT f1("<e2>"); +SET GLOBAL EVENT_SCHEDULER= ON; +SELECT SLEEP(@step / 2); +SLEEP(@step / 2) +0 +SET GLOBAL EVENT_SCHEDULER= OFF; +SELECT * FROM t1 ORDER BY count, comment; +count unix_time local_time comment +1 1 1 <e1> +1 1 3 <e2> +1 1 3 e2 should be executed +2 2 2 <e1> +2 2 4 <e2> +2 2 4 e2 should be executed +3 3 3 <e1> +3 3 3 Second pass after backward -2 step shift, e2 should not be executed +4 4 4 <e1> +4 4 4 Second pass after backward -2 step shift, e2 should not be executed +5 5 5 <e1> +5 5 5 <e2> +5 5 5 e2 should be executed +6 0 0 <e1> +6 0 2 <e2> +6 0 2 Forward +2 step shift, local 0, 1 are skipped, e2 should be executed +7 1 1 <e1> +7 1 3 <e2> +7 1 3 e2 should be executed +SET TIME_ZONE= @save_time_zone; +DROP EVENT e2; +DROP EVENT e1; +DROP FUNCTION f1; +DROP TABLE t1, t2; +DELETE FROM mysql.time_zone_name WHERE time_zone_id = @tzid; +DELETE FROM mysql.time_zone_transition_type WHERE time_zone_id = @tzid; +DELETE FROM mysql.time_zone_transition WHERE time_zone_id = @tzid; +DELETE FROM mysql.time_zone WHERE time_zone_id = @tzid; +SET TIME_ZONE= '+00:00'; +CREATE TABLE t1 (event CHAR(2), dt DATE, offset INT); +INSERT INTO mysql.time_zone VALUES (NULL, 'N'); +SET @tzid= LAST_INSERT_ID(); +SET @now= UNIX_TIMESTAMP(); +SET @offset_month_01= UNIX_TIMESTAMP('2030-01-31 12:00:00') - @now; +SET @offset_month_02= UNIX_TIMESTAMP('2030-02-28 12:00:00') - @now - 5*@step; +SET @offset_month_03= UNIX_TIMESTAMP('2030-03-31 12:00:00') - @now - 5*@step; +SET @offset_month_04= UNIX_TIMESTAMP('2030-04-30 12:00:00') - @now - 13*@step; +INSERT INTO mysql.time_zone_transition_type +VALUES (@tzid, 0, @offset_month_01, 0, 'b16420_0'); +INSERT INTO mysql.time_zone_transition_type +VALUES (@tzid, 1, @offset_month_02, 1, 'b16420_1'); +INSERT INTO mysql.time_zone_transition_type +VALUES (@tzid, 2, @offset_month_03, 1, 'b16420_2'); +INSERT INTO mysql.time_zone_transition_type +VALUES (@tzid, 3, @offset_month_04, 1, 'b16420_3'); +INSERT INTO mysql.time_zone_transition +VALUES (@tzid, @now, 0); +INSERT INTO mysql.time_zone_transition +VALUES (@tzid, @now + 3 * @step, 1); +INSERT INTO mysql.time_zone_transition +VALUES (@tzid, @now + 7 * @step, 2); +INSERT INTO mysql.time_zone_transition +VALUES (@tzid, @now + 12 * @step, 3); +INSERT INTO mysql.time_zone_name VALUES ('bug16420_2', @tzid); +SET TIME_ZONE= 'bug16420_2'; +SET GLOBAL EVENT_SCHEDULER= ON; +SET GLOBAL EVENT_SCHEDULER= OFF; +Below we should see the following: +- On Jan 31 only e2 is executed, because we started later than +e1 should have been executed. Offset of e2 is 0 because of +the late start, not 1. +- The next execution is on Feb 28 (last day of Feb). Both events +are executed in their times, offsets are -1 and 1. +- The next time is Mar 31. Because the time of event +execution was skipped over, events are executed right away, +offsets are 2 and 2. +- The next time is Apr 30. Events are again executed in their +appointed times, offsets are -1 and 1. +SELECT * FROM t1 ORDER BY dt, event; +event dt offset +e2 2030-01-31 0 +e1 2030-02-28 -1 +e2 2030-02-28 1 +e1 2030-03-31 2 +e2 2030-03-31 2 +e1 2030-04-30 -1 +e2 2030-04-30 1 +DROP EVENT e2; +DROP EVENT e1; +DROP TABLE t1; +SET TIME_ZONE= @save_time_zone; +DELETE FROM mysql.time_zone_name WHERE time_zone_id = @tzid; +DELETE FROM mysql.time_zone_transition_type WHERE time_zone_id = @tzid; +DELETE FROM mysql.time_zone_transition WHERE time_zone_id = @tzid; +DELETE FROM mysql.time_zone WHERE time_zone_id = @tzid; +DROP FUNCTION round_to_step; +DROP TABLE t_step; +DROP DATABASE mysqltest_db1; +End of 5.1 tests. diff --git a/mysql-test/r/mysqldump.result b/mysql-test/r/mysqldump.result index 0190094f0ba..da90ff2cf6b 100644 --- a/mysql-test/r/mysqldump.result +++ b/mysql-test/r/mysqldump.result @@ -3440,35 +3440,35 @@ use first; set time_zone = 'UTC'; create event ee1 on schedule at '2035-12-31 20:01:23' do set @a=5; show events; -Db Name Definer Type Execute at Interval value Interval field Starts Ends Status -first ee1 root@localhost ONE TIME 2035-12-31 20:01:23 NULL NULL NULL NULL ENABLED +Db Name Definer Time zone Type Execute at Interval value Interval field Starts Ends Status +first ee1 root@localhost UTC ONE TIME 2035-12-31 20:01:23 NULL NULL NULL NULL ENABLED show create event ee1; -Event sql_mode Create Event -ee1 CREATE EVENT `ee1` ON SCHEDULE AT '2035-12-31 20:01:23' ON COMPLETION NOT PRESERVE ENABLE DO set @a=5 +Event sql_mode time_zone Create Event +ee1 UTC CREATE EVENT `ee1` ON SCHEDULE AT '2035-12-31 20:01:23' ON COMPLETION NOT PRESERVE ENABLE DO set @a=5 drop database first; create database second; use second; show events; -Db Name Definer Type Execute at Interval value Interval field Starts Ends Status -second ee1 root@localhost ONE TIME 2035-12-31 20:01:23 NULL NULL NULL NULL ENABLED +Db Name Definer Time zone Type Execute at Interval value Interval field Starts Ends Status +second ee1 root@localhost UTC ONE TIME 2035-12-31 20:01:23 NULL NULL NULL NULL ENABLED show create event ee1; -Event sql_mode Create Event -ee1 NO_AUTO_VALUE_ON_ZERO CREATE EVENT `ee1` ON SCHEDULE AT '2035-12-31 20:01:23' ON COMPLETION NOT PRESERVE ENABLE DO set @a=5 +Event sql_mode time_zone Create Event +ee1 NO_AUTO_VALUE_ON_ZERO UTC CREATE EVENT `ee1` ON SCHEDULE AT '2035-12-31 20:01:23' ON COMPLETION NOT PRESERVE ENABLE DO set @a=5 create event ee2 on schedule at '2018-12-31 21:01:23' do set @a=5; create event ee3 on schedule at '2030-12-31 22:01:23' do set @a=5; show events; -Db Name Definer Type Execute at Interval value Interval field Starts Ends Status -second ee1 root@localhost ONE TIME 2035-12-31 20:01:23 NULL NULL NULL NULL ENABLED -second ee2 root@localhost ONE TIME 2018-12-31 21:01:23 NULL NULL NULL NULL ENABLED -second ee3 root@localhost ONE TIME 2030-12-31 22:01:23 NULL NULL NULL NULL ENABLED +Db Name Definer Time zone Type Execute at Interval value Interval field Starts Ends Status +second ee1 root@localhost UTC ONE TIME 2035-12-31 20:01:23 NULL NULL NULL NULL ENABLED +second ee2 root@localhost UTC ONE TIME 2018-12-31 21:01:23 NULL NULL NULL NULL ENABLED +second ee3 root@localhost UTC ONE TIME 2030-12-31 22:01:23 NULL NULL NULL NULL ENABLED drop database second; create database third; use third; show events; -Db Name Definer Type Execute at Interval value Interval field Starts Ends Status -third ee1 root@localhost ONE TIME 2035-12-31 20:01:23 NULL NULL NULL NULL ENABLED -third ee2 root@localhost ONE TIME 2018-12-31 21:01:23 NULL NULL NULL NULL ENABLED -third ee3 root@localhost ONE TIME 2030-12-31 22:01:23 NULL NULL NULL NULL ENABLED +Db Name Definer Time zone Type Execute at Interval value Interval field Starts Ends Status +third ee1 root@localhost UTC ONE TIME 2035-12-31 20:01:23 NULL NULL NULL NULL ENABLED +third ee2 root@localhost UTC ONE TIME 2018-12-31 21:01:23 NULL NULL NULL NULL ENABLED +third ee3 root@localhost UTC ONE TIME 2030-12-31 22:01:23 NULL NULL NULL NULL ENABLED drop database third; set time_zone = 'SYSTEM'; use test; diff --git a/mysql-test/r/ps.result b/mysql-test/r/ps.result index 7790de570b0..c4e44945ec4 100644 --- a/mysql-test/r/ps.result +++ b/mysql-test/r/ps.result @@ -1968,11 +1968,11 @@ prepare abc from "show master logs"; deallocate prepare abc; create procedure proc_1() show events; call proc_1(); -Db Name Definer Type Execute at Interval value Interval field Starts Ends Status +Db Name Definer Time zone Type Execute at Interval value Interval field Starts Ends Status call proc_1(); -Db Name Definer Type Execute at Interval value Interval field Starts Ends Status +Db Name Definer Time zone Type Execute at Interval value Interval field Starts Ends Status call proc_1(); -Db Name Definer Type Execute at Interval value Interval field Starts Ends Status +Db Name Definer Time zone Type Execute at Interval value Interval field Starts Ends Status drop procedure proc_1; create function func_1() returns int begin show events; return 1; end| ERROR 0A000: Not allowed to return a result set from a function @@ -1982,11 +1982,11 @@ drop function func_1; ERROR 42000: FUNCTION test.func_1 does not exist prepare abc from "show events"; execute abc; -Db Name Definer Type Execute at Interval value Interval field Starts Ends Status +Db Name Definer Time zone Type Execute at Interval value Interval field Starts Ends Status execute abc; -Db Name Definer Type Execute at Interval value Interval field Starts Ends Status +Db Name Definer Time zone Type Execute at Interval value Interval field Starts Ends Status execute abc; -Db Name Definer Type Execute at Interval value Interval field Starts Ends Status +Db Name Definer Time zone Type Execute at Interval value Interval field Starts Ends Status deallocate prepare abc; drop procedure if exists a; create procedure a() select 42; diff --git a/mysql-test/r/system_mysql_db.result b/mysql-test/r/system_mysql_db.result index c93fbfba6e2..29223cd3061 100644 --- a/mysql-test/r/system_mysql_db.result +++ b/mysql-test/r/system_mysql_db.result @@ -224,6 +224,7 @@ event CREATE TABLE `event` ( `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' show create table general_log; diff --git a/mysql-test/t/events.test b/mysql-test/t/events.test index 6eb514fc13c..24f4b4eccab 100644 --- a/mysql-test/t/events.test +++ b/mysql-test/t/events.test @@ -122,43 +122,62 @@ set names utf8; # SHOW CREATE EVENT test begin # CREATE EVENT root6 ON SCHEDULE EVERY '10:20' MINUTE_SECOND ON COMPLETION PRESERVE ENABLE COMMENT 'some comment' DO select 1; +--replace_regex /STARTS '[^']+'/STARTS '#'/ SHOW CREATE EVENT root6; create event root7 on schedule every 2 year do select 1; +--replace_regex /STARTS '[^']+'/STARTS '#'/ SHOW CREATE EVENT root7; create event root8 on schedule every '2:5' year_month do select 1; +--replace_regex /STARTS '[^']+'/STARTS '#'/ SHOW CREATE EVENT root8; create event root8_1 on schedule every '2:15' year_month do select 1; +--replace_regex /STARTS '[^']+'/STARTS '#'/ SHOW CREATE EVENT root8_1; create event root9 on schedule every 2 week ON COMPLETION PRESERVE DISABLE COMMENT 'коментар на кирилица' do select 1; +--replace_regex /STARTS '[^']+'/STARTS '#'/ SHOW CREATE EVENT root9; create event root10 on schedule every '20:5' day_hour do select 1; +--replace_regex /STARTS '[^']+'/STARTS '#'/ SHOW CREATE EVENT root10; create event root11 on schedule every '20:25' day_hour do select 1; +--replace_regex /STARTS '[^']+'/STARTS '#'/ SHOW CREATE EVENT root11; create event root12 on schedule every '20:25' hour_minute do select 1; +--replace_regex /STARTS '[^']+'/STARTS '#'/ SHOW CREATE EVENT root12; create event root13 on schedule every '25:25' hour_minute do select 1; +--replace_regex /STARTS '[^']+'/STARTS '#'/ SHOW CREATE EVENT root13; create event root13_1 on schedule every '11:65' hour_minute do select 1; +--replace_regex /STARTS '[^']+'/STARTS '#'/ SHOW CREATE EVENT root13_1; create event root14 on schedule every '35:35' minute_second do select 1; +--replace_regex /STARTS '[^']+'/STARTS '#'/ SHOW CREATE EVENT root14; create event root15 on schedule every '35:66' minute_second do select 1; +--replace_regex /STARTS '[^']+'/STARTS '#'/ SHOW CREATE EVENT root15; create event root16 on schedule every '35:56' day_minute do select 1; +--replace_regex /STARTS '[^']+'/STARTS '#'/ SHOW CREATE EVENT root16; create event root17 on schedule every '35:12:45' day_minute do select 1; +--replace_regex /STARTS '[^']+'/STARTS '#'/ SHOW CREATE EVENT root17; create event root17_1 on schedule every '35:25:65' day_minute do select 1; +--replace_regex /STARTS '[^']+'/STARTS '#'/ SHOW CREATE EVENT root17_1; create event root18 on schedule every '35:12:45' hour_second do select 1; +--replace_regex /STARTS '[^']+'/STARTS '#'/ SHOW CREATE EVENT root18; create event root19 on schedule every '15:59:85' hour_second do select 1; +--replace_regex /STARTS '[^']+'/STARTS '#'/ SHOW CREATE EVENT root19; create event root20 on schedule every '50:20:12:45' day_second do select 1; +--replace_regex /STARTS '[^']+'/STARTS '#'/ SHOW CREATE EVENT root20; set names cp1251; create event ðóóò21 on schedule every '50:23:59:95' day_second COMMENT 'òîâà å 1251 êîìåíòàð' do select 1; +--replace_regex /STARTS '[^']+'/STARTS '#'/ SHOW CREATE EVENT ðóóò21; insert into mysql.event (db, name, body, definer, interval_value, interval_field) values (database(), "root22", "select 1", user(), 100, "SECOND_MICROSECOND"); --error ER_NOT_SUPPORTED_YET diff --git a/mysql-test/t/events_bugs.test b/mysql-test/t/events_bugs.test index 0790999d720..c1016f1752b 100644 --- a/mysql-test/t/events_bugs.test +++ b/mysql-test/t/events_bugs.test @@ -45,10 +45,17 @@ create event e_55 on schedule at 99990101000000 do drop table t; create event e_55 on schedule every 10 hour starts 99990101000000 do drop table t; --error ER_EVENT_ENDS_BEFORE_STARTS create event e_55 on schedule every 10 minute ends 99990101000000 do drop table t; ---error ER_EVENT_EXEC_TIME_IN_THE_PAST +--error ER_WRONG_VALUE create event e_55 on schedule at 10000101000000 do drop table t; ---error ER_EVENT_EXEC_TIME_IN_THE_PAST + +# For the purpose of backup we allow times in the past. Here, no +# error will be given, but the event won't be created. One may think +# of that as if the event was created, then it turned out it's in the +# past, so it was dropped because of implicit ON COMPLETION NOT +# PRESERVE. create event e_55 on schedule at 20000101000000 do drop table t; +show events; + --error ER_PARSE_ERROR create event e_55 on schedule at 20200101000000 starts 10000101000000 do drop table t; --error ER_PARSE_ERROR diff --git a/mysql-test/t/events_restart_phase1.test b/mysql-test/t/events_restart_phase1.test index 92783ddaef7..0a84f6c4966 100644 --- a/mysql-test/t/events_restart_phase1.test +++ b/mysql-test/t/events_restart_phase1.test @@ -14,6 +14,6 @@ create event abc3 on schedule every 1 second do insert into execution_log value( --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'); -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'); +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'); --echo "Now we restart the server" diff --git a/mysql-test/t/events_scheduling.test b/mysql-test/t/events_scheduling.test index 0002cf9f29f..e3b55685e65 100644 --- a/mysql-test/t/events_scheduling.test +++ b/mysql-test/t/events_scheduling.test @@ -63,3 +63,4 @@ DROP TABLE table_2; DROP TABLE table_3; DROP TABLE table_4; DROP DATABASE events_test; +SET GLOBAL event_scheduler=OFF; diff --git a/mysql-test/t/events_time_zone.test b/mysql-test/t/events_time_zone.test new file mode 100644 index 00000000000..fff84c7a995 --- /dev/null +++ b/mysql-test/t/events_time_zone.test @@ -0,0 +1,463 @@ +# This test case is sensitive to execution timing. You may control +# this sensitivity by the parameter below. Small values will result +# in fast but more unstable execution, large values will improve +# stability at the cost of speed. Basically, N is a number of seconds +# to wait for operation to complete. Should be positive. Test runs +# about 25*N seconds (it sleeps most of the time, so CPU speed is not +# relevant). +let $N = 5; + +--source include/big_test.inc + + +--disable_warnings +DROP DATABASE IF EXISTS mysqltest_db1; +--enable_warnings + +CREATE DATABASE mysqltest_db1; + +let $old_db= `SELECT DATABASE()`; +USE mysqltest_db1; + +SET GLOBAL EVENT_SCHEDULER= OFF; + + +# +# 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 +# + +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. + +# Disable query log to hide actual value of $N. +--disable_query_log +eval SET @step= $N; +--enable_query_log + +# Since we are working in a separate database, we may use any names we +# like. +CREATE TABLE t_step (step INT); +INSERT INTO t_step VALUES (@step); + +# We can't use @variables in function, because it will be called from +# the event thread, and 'eval' doesn't work for multi-statements, so +# we can't interpolate $variables either, hence we fetch the step +# value from the table. +delimiter //; +CREATE FUNCTION round_to_step(i INT, n INT) RETURNS INT +BEGIN + DECLARE step INT; + + SELECT * INTO step FROM t_step; + + # We add 0.1 as a protection from inexact division. + RETURN FLOOR((i % (step * n) + 0.1) / step); +END// +delimiter ;// + + +# Test time computations wrt Daylight Saving Time shifts. We also +# test here that the event operates in its time zone (see what NOW() +# returns). +# + +# Create a fake time zone with time transitions every 3*$N second. + +SET @step3= @step * 3; +SET @step6= @step * 6; + +# Disable query log to hide current time. +--disable_query_log +SET @unix_time= UNIX_TIMESTAMP() - 1; +--enable_query_log + +SET @unix_time= @unix_time - @unix_time % @step6; + +INSERT INTO mysql.time_zone VALUES (NULL, 'N'); +SET @tzid= LAST_INSERT_ID(); +INSERT INTO mysql.time_zone_transition_type + VALUES (@tzid, 0, 0, 0, 'b16420_0'); +INSERT INTO mysql.time_zone_transition_type + VALUES (@tzid, 1, @step3 - @step, 1, 'b16420_1'); + +let $transition_unix_time= `SELECT @unix_time`; +let $count= 30; +--disable_query_log +while ($count) +{ + eval INSERT INTO mysql.time_zone_transition + VALUES (@tzid, $transition_unix_time, + $transition_unix_time % @step6 = 0); + let $transition_unix_time= `SELECT $transition_unix_time + @step3`; + dec $count; +} +--enable_query_log +INSERT INTO mysql.time_zone_name VALUES ('bug16420', @tzid); + +CREATE TABLE t1 (count INT, unix_time INT, local_time INT, comment CHAR(80)); +CREATE TABLE t2 (count INT); +INSERT INTO t2 VALUES (1); + +delimiter //; +CREATE FUNCTION f1(comment CHAR(80)) RETURNS INT +BEGIN + DECLARE orig_tz CHAR(64); + DECLARE unix_time INT; + DECLARE local_now DATETIME; + DECLARE utc_now DATETIME; + DECLARE local_time INT; + + SET unix_time= UNIX_TIMESTAMP(); + SET local_now= FROM_UNIXTIME(unix_time); + SET orig_tz= @@TIME_ZONE; + SET TIME_ZONE = '+00:00'; + SET utc_now= FROM_UNIXTIME(unix_time); + SET TIME_ZONE= orig_tz; + SET local_time = unix_time + TIMESTAMPDIFF(SECOND, utc_now, local_now); + + SET unix_time= round_to_step(unix_time, 6); + SET local_time= round_to_step(local_time, 6); + + INSERT INTO t1 VALUES ((SELECT count FROM t2), + unix_time, local_time, comment); + RETURN 0; +END// +delimiter ;// + +SET TIME_ZONE= '+00:00'; +CREATE EVENT e1 ON SCHEDULE EVERY @step SECOND + STARTS FROM_UNIXTIME(@unix_time) DO SELECT f1("<e1>"); + +SET TIME_ZONE= 'bug16420'; +CREATE EVENT e2 ON SCHEDULE EVERY @step SECOND + STARTS FROM_UNIXTIME(@unix_time) DO SELECT f1("<e2>"); + +# We want to start at the beginning of the DST cycle, so we wait +# untill current time divides by @step6. +let $wait_timeout= `SELECT @step6 + 1`; +let $wait_condition= SELECT UNIX_TIMESTAMP() % @step6 = @step6 - 1; +--source include/wait_condition.inc +# The second wait is needed because after the first wait we may end up +# on the ending edge of a second. Second wait will bring us to the +# beginning edge. +let $wait_timeout= `SELECT @step + 1`; +let $wait_condition= SELECT UNIX_TIMESTAMP() % @step6 = 0; +--source include/wait_condition.inc + +# Note that after the scheduler is enabled, the event will be +# scheduled only for the next second. +SET GLOBAL EVENT_SCHEDULER= ON; + +# We want to run after the events are executed. +SELECT SLEEP(@step / 2); + +let $count= 7; +--disable_query_log +--disable_result_log +while ($count) +{ + SELECT SLEEP(@step); + + eval SELECT CASE $count + WHEN 5 THEN f1(CONCAT("Second pass after backward -2 step shift,", + " e2 should not be executed")) + WHEN 4 THEN f1(CONCAT("Second pass after backward -2 step shift,", + " e2 should not be executed")) + WHEN 2 THEN f1(CONCAT("Forward +2 step shift, local 0, 1 are skipped,", + " e2 should be executed")) + ELSE f1("e2 should be executed") + END; + UPDATE t2 SET count= count + 1; + + dec $count; +} +--enable_result_log +--enable_query_log + +SET GLOBAL EVENT_SCHEDULER= OFF; + +SELECT * FROM t1 ORDER BY count, comment; + +SET TIME_ZONE= @save_time_zone; + +DROP EVENT e2; +DROP EVENT e1; +DROP FUNCTION f1; +DROP TABLE t1, t2; + +DELETE FROM mysql.time_zone_name WHERE time_zone_id = @tzid; +DELETE FROM mysql.time_zone_transition_type WHERE time_zone_id = @tzid; +DELETE FROM mysql.time_zone_transition WHERE time_zone_id = @tzid; +DELETE FROM mysql.time_zone WHERE time_zone_id = @tzid; + +#---------------------------------------------------------------------- + +# Test MONTH interval. +# + +SET TIME_ZONE= '+00:00'; + +CREATE TABLE t1 (event CHAR(2), dt DATE, offset INT); + +INSERT INTO mysql.time_zone VALUES (NULL, 'N'); +SET @tzid= LAST_INSERT_ID(); + +SET @now= UNIX_TIMESTAMP(); +SET @offset_month_01= UNIX_TIMESTAMP('2030-01-31 12:00:00') - @now; +SET @offset_month_02= UNIX_TIMESTAMP('2030-02-28 12:00:00') - @now - 5*@step; +SET @offset_month_03= UNIX_TIMESTAMP('2030-03-31 12:00:00') - @now - 5*@step; +SET @offset_month_04= UNIX_TIMESTAMP('2030-04-30 12:00:00') - @now - 13*@step; + +INSERT INTO mysql.time_zone_transition_type + VALUES (@tzid, 0, @offset_month_01, 0, 'b16420_0'); +INSERT INTO mysql.time_zone_transition_type + VALUES (@tzid, 1, @offset_month_02, 1, 'b16420_1'); +INSERT INTO mysql.time_zone_transition_type + VALUES (@tzid, 2, @offset_month_03, 1, 'b16420_2'); +INSERT INTO mysql.time_zone_transition_type + VALUES (@tzid, 3, @offset_month_04, 1, 'b16420_3'); +INSERT INTO mysql.time_zone_transition + VALUES (@tzid, @now, 0); +INSERT INTO mysql.time_zone_transition + VALUES (@tzid, @now + 3 * @step, 1); +INSERT INTO mysql.time_zone_transition + VALUES (@tzid, @now + 7 * @step, 2); +INSERT INTO mysql.time_zone_transition + VALUES (@tzid, @now + 12 * @step, 3); +# We have to user a new time zone name, because 'bug16420' has been +# cached already. +INSERT INTO mysql.time_zone_name VALUES ('bug16420_2', @tzid); + +SET TIME_ZONE= 'bug16420_2'; + +SET GLOBAL EVENT_SCHEDULER= ON; + +let $now= `SELECT @now`; +--disable_query_log +eval CREATE EVENT e1 ON SCHEDULE EVERY 1 MONTH + STARTS FROM_UNIXTIME($now - @step) DO + INSERT INTO t1 VALUES + ("e1", NOW(), round_to_step(UNIX_TIMESTAMP() - $now, 4) - 1); +eval CREATE EVENT e2 ON SCHEDULE EVERY 1 MONTH + STARTS FROM_UNIXTIME($now + @step) DO + INSERT INTO t1 VALUES + ("e2", NOW(), round_to_step(UNIX_TIMESTAMP() - $now, 4) - 1); +--enable_query_log + +let $wait_timeout= `SELECT 16 * @step`; +let $wait_condition= SELECT COUNT(*) = 7 FROM t1; +--source include/wait_condition.inc + +SET GLOBAL EVENT_SCHEDULER= OFF; + +--echo Below we should see the following: +--echo - On Jan 31 only e2 is executed, because we started later than +--echo e1 should have been executed. Offset of e2 is 0 because of +--echo the late start, not 1. +--echo - The next execution is on Feb 28 (last day of Feb). Both events +--echo are executed in their times, offsets are -1 and 1. +--echo - The next time is Mar 31. Because the time of event +--echo execution was skipped over, events are executed right away, +--echo offsets are 2 and 2. +--echo - The next time is Apr 30. Events are again executed in their +--echo appointed times, offsets are -1 and 1. +SELECT * FROM t1 ORDER BY dt, event; + +DROP EVENT e2; +DROP EVENT e1; +DROP TABLE t1; + +SET TIME_ZONE= @save_time_zone; + +DELETE FROM mysql.time_zone_name WHERE time_zone_id = @tzid; +DELETE FROM mysql.time_zone_transition_type WHERE time_zone_id = @tzid; +DELETE FROM mysql.time_zone_transition WHERE time_zone_id = @tzid; +DELETE FROM mysql.time_zone WHERE time_zone_id = @tzid; + +DROP FUNCTION round_to_step; +DROP TABLE t_step; + + +DROP DATABASE mysqltest_db1; +--disable_query_log +eval USE $old_db; +--enable_query_log + + +--echo End of 5.1 tests. diff --git a/scripts/mysql_create_system_tables.sh b/scripts/mysql_create_system_tables.sh index cc86def1a5c..808f4adc222 100644 --- a/scripts/mysql_create_system_tables.sh +++ b/scripts/mysql_create_system_tables.sh @@ -867,6 +867,7 @@ then c_ev="$c_ev 'HIGH_NOT_PRECEDENCE'" c_ev="$c_ev ) DEFAULT '' NOT NULL," c_ev="$c_ev comment char(64) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL default ''," + c_ev="$c_ev time_zone char(64) CHARACTER SET latin1 NOT NULL DEFAULT 'SYSTEM'," c_ev="$c_ev PRIMARY KEY (db, name)" c_ev="$c_ev ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT 'Events';" fi diff --git a/scripts/mysql_fix_privilege_tables.sql b/scripts/mysql_fix_privilege_tables.sql index 9cdea507493..9a966c653f2 100644 --- a/scripts/mysql_fix_privilege_tables.sql +++ b/scripts/mysql_fix_privilege_tables.sql @@ -683,6 +683,7 @@ CREATE TABLE event ( 'HIGH_NOT_PRECEDENCE' ) DEFAULT '' NOT NULL, 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'; @@ -734,10 +735,12 @@ ALTER TABLE event ADD sql_mode 'NO_AUTO_CREATE_USER', 'HIGH_NOT_PRECEDENCE' ) DEFAULT '' NOT NULL AFTER on_completion; - UPDATE user SET Event_priv=Super_priv WHERE @hadEventPriv = 0; ALTER TABLE event MODIFY name char(64) CHARACTER SET utf8 NOT NULL default ''; +ALTER TABLE event ADD COLUMN time_zone char(64) CHARACTER SET latin1 + NOT NULL DEFAULT 'SYSTEM' AFTER comment; + # # TRIGGER privilege # diff --git a/sql/event_data_objects.cc b/sql/event_data_objects.cc index dad8aeb2e20..36f20c34200 100644 --- a/sql/event_data_objects.cc +++ b/sql/event_data_objects.cc @@ -101,7 +101,7 @@ Event_parse_data::new_instance(THD *thd) */ Event_parse_data::Event_parse_data() - :on_completion(ON_COMPLETION_DROP), status(ENABLED), + :on_completion(ON_COMPLETION_DROP), status(ENABLED), do_not_create(FALSE), item_starts(NULL), item_ends(NULL), item_execute_at(NULL), starts_null(TRUE), ends_null(TRUE), execute_at_null(TRUE), item_expression(NULL), expression(0) @@ -109,9 +109,7 @@ Event_parse_data::Event_parse_data() DBUG_ENTER("Event_parse_data::Event_parse_data"); /* Actually in the parser STARTS is always set */ - set_zero_time(&starts, MYSQL_TIMESTAMP_DATETIME); - set_zero_time(&ends, MYSQL_TIMESTAMP_DATETIME); - set_zero_time(&execute_at, MYSQL_TIMESTAMP_DATETIME); + starts= ends= execute_at= 0; body.str= comment.str= NULL; body.length= comment.length= 0; @@ -224,6 +222,55 @@ Event_parse_data::init_body(THD *thd) /* + This function is called on CREATE EVENT or ALTER EVENT. When either + ENDS or AT is in the past, we are trying to create an event that + will never be executed. If it has ON COMPLETION NOT PRESERVE + (default), then it would normally be dropped already, so on CREATE + EVENT we give a warning, and do not create anyting. On ALTER EVENT + we give a error, and do not change the event. + + If the event has ON COMPLETION PRESERVE, then we see if the event is + created or altered to the ENABLED (default) state. If so, then we + give a warning, and change the state to DISABLED. + + Otherwise it is a valid event in ON COMPLETION PRESERVE DISABLE + state. +*/ + +void +Event_parse_data::check_if_in_the_past(THD *thd, my_time_t ltime_utc) +{ + if (ltime_utc >= (my_time_t) thd->query_start()) + return; + + if (on_completion == ON_COMPLETION_DROP) + { + switch (thd->lex->sql_command) { + case SQLCOM_CREATE_EVENT: + push_warning(thd, MYSQL_ERROR::WARN_LEVEL_NOTE, + ER_EVENT_CANNOT_CREATE_IN_THE_PAST, + ER(ER_EVENT_CANNOT_CREATE_IN_THE_PAST)); + break; + case SQLCOM_ALTER_EVENT: + my_error(ER_EVENT_CANNOT_ALTER_IN_THE_PAST, MYF(0)); + break; + default: + DBUG_ASSERT(0); + } + + do_not_create= TRUE; + } + else if (status == ENABLED) + { + status= DISABLED; + push_warning(thd, MYSQL_ERROR::WARN_LEVEL_NOTE, + ER_EVENT_EXEC_TIME_IN_THE_PAST, + ER(ER_EVENT_EXEC_TIME_IN_THE_PAST)); + } +} + + +/* Sets time for execution for one-time event. SYNOPSIS @@ -240,8 +287,7 @@ Event_parse_data::init_execute_at(THD *thd) { my_bool not_used; TIME ltime; - my_time_t t; - TIME time_tmp; + my_time_t ltime_utc; DBUG_ENTER("Event_parse_data::init_execute_at"); @@ -256,35 +302,20 @@ Event_parse_data::init_execute_at(THD *thd) (starts_null && ends_null))); DBUG_ASSERT(starts_null && ends_null); - /* let's check whether time is in the past */ - thd->variables.time_zone->gmt_sec_to_TIME(&time_tmp, - (my_time_t) thd->query_start()); - if ((not_used= item_execute_at->get_date(<ime, TIME_NO_ZERO_DATE))) goto wrong_value; - if (TIME_to_ulonglong_datetime(<ime) < - TIME_to_ulonglong_datetime(&time_tmp)) - { - my_error(ER_EVENT_EXEC_TIME_IN_THE_PAST, MYF(0)); - DBUG_RETURN(ER_WRONG_VALUE); - } - - /* - This may result in a 1970-01-01 date if ltime is > 2037-xx-xx. - CONVERT_TZ has similar problem. - mysql_priv.h currently lists - #define TIMESTAMP_MAX_YEAR 2038 (see TIME_to_timestamp()) - */ - my_tz_UTC->gmt_sec_to_TIME(<ime,t=TIME_to_timestamp(thd,<ime,¬_used)); - if (!t) + ltime_utc= TIME_to_timestamp(thd,<ime,¬_used); + if (!ltime_utc) { DBUG_PRINT("error", ("Execute AT after year 2037")); goto wrong_value; } + check_if_in_the_past(thd, ltime_utc); + execute_at_null= FALSE; - execute_at= ltime; + execute_at= ltime_utc; DBUG_RETURN(0); wrong_value: @@ -424,8 +455,8 @@ int Event_parse_data::init_starts(THD *thd) { my_bool not_used; - TIME ltime, time_tmp; - my_time_t t; + TIME ltime; + my_time_t ltime_utc; DBUG_ENTER("Event_parse_data::init_starts"); if (!item_starts) @@ -437,36 +468,15 @@ Event_parse_data::init_starts(THD *thd) if ((not_used= item_starts->get_date(<ime, TIME_NO_ZERO_DATE))) goto wrong_value; - /* - Let's check whether time is in the past. - Note: This call is not post year 2038 safe. That's because - thd->query_start() is of time_t, while gmt_sec_to_TIME() - wants my_time_t. In the case time_t is larger than my_time_t - an overflow might happen and events subsystem will not work as - expected. - */ - thd->variables.time_zone->gmt_sec_to_TIME(&time_tmp, - (my_time_t) thd->query_start()); - - DBUG_PRINT("info",("now: %ld starts: %ld", - (long) TIME_to_ulonglong_datetime(&time_tmp), - (long) TIME_to_ulonglong_datetime(<ime))); - if (TIME_to_ulonglong_datetime(<ime) < - TIME_to_ulonglong_datetime(&time_tmp)) + ltime_utc= TIME_to_timestamp(thd, <ime, ¬_used); + if (!ltime_utc) goto wrong_value; - /* - Again, after 2038 this code won't work. As - mysql_priv.h currently lists - #define TIMESTAMP_MAX_YEAR 2038 (see TIME_to_timestamp()) - */ - my_tz_UTC->gmt_sec_to_TIME(<ime,t=TIME_to_timestamp(thd, <ime, - ¬_used)); - if (!t) - goto wrong_value; + DBUG_PRINT("info",("now: %ld starts: %ld", + (long) thd->query_start(), (long) ltime_utc)); - starts= ltime; starts_null= FALSE; + starts= ltime_utc; DBUG_RETURN(0); wrong_value: @@ -498,9 +508,9 @@ wrong_value: int Event_parse_data::init_ends(THD *thd) { - TIME ltime, ltime_now; my_bool not_used; - my_time_t t; + TIME ltime; + my_time_t ltime_utc; DBUG_ENTER("Event_parse_data::init_ends"); if (!item_ends) @@ -513,34 +523,19 @@ Event_parse_data::init_ends(THD *thd) if ((not_used= item_ends->get_date(<ime, TIME_NO_ZERO_DATE))) goto error_bad_params; - /* - Again, after 2038 this code won't work. As - mysql_priv.h currently lists - #define TIMESTAMP_MAX_YEAR 2038 (see TIME_to_timestamp()) - */ - DBUG_PRINT("info", ("get the UTC time")); - my_tz_UTC->gmt_sec_to_TIME(<ime,t=TIME_to_timestamp(thd, <ime, - ¬_used)); - if (!t) + ltime_utc= TIME_to_timestamp(thd, <ime, ¬_used); + if (!ltime_utc) goto error_bad_params; /* Check whether ends is after starts */ DBUG_PRINT("info", ("ENDS after STARTS?")); - if (!starts_null && my_time_compare(&starts, <ime) != -1) + if (!starts_null && starts >= ltime_utc) goto error_bad_params; - /* - The parser forces starts to be provided but one day STARTS could be - set before NOW() and in this case the following check should be done. - Check whether ENDS is not in the past. - */ - DBUG_PRINT("info", ("ENDS after NOW?")); - my_tz_UTC->gmt_sec_to_TIME(<ime_now, thd->query_start()); - if (my_time_compare(<ime_now, <ime) == 1) - goto error_bad_params; + check_if_in_the_past(thd, ltime_utc); - ends= ltime; ends_null= FALSE; + ends= ltime_utc; DBUG_RETURN(0); error_bad_params: @@ -656,6 +651,7 @@ Event_basic::Event_basic() init_alloc_root(&mem_root, 256, 512); dbname.str= name.str= NULL; dbname.length= name.length= 0; + time_zone= NULL; DBUG_VOID_RETURN; } @@ -716,6 +712,16 @@ Event_basic::load_string_fields(Field **fields, ...) } +bool +Event_basic::load_time_zone(THD *thd, const LEX_STRING tz_name) +{ + String str(tz_name.str, &my_charset_latin1); + time_zone= my_tz_find(thd, &str); + + return (time_zone == NULL); +} + + /* Constructor @@ -730,10 +736,7 @@ Event_queue_element::Event_queue_element(): { DBUG_ENTER("Event_queue_element::Event_queue_element"); - set_zero_time(&starts, MYSQL_TIMESTAMP_DATETIME); - set_zero_time(&ends, MYSQL_TIMESTAMP_DATETIME); - set_zero_time(&execute_at, MYSQL_TIMESTAMP_DATETIME); - set_zero_time(&last_executed, MYSQL_TIMESTAMP_DATETIME); + starts= ends= execute_at= last_executed= 0; starts_null= ends_null= execute_at_null= TRUE; DBUG_VOID_RETURN; @@ -833,7 +836,7 @@ Event_timed::init() Loads an event's body from a row from mysql.event SYNOPSIS - Event_job_data::load_from_row(MEM_ROOT *mem_root, TABLE *table) + Event_job_data::load_from_row(THD *thd, TABLE *table) RETURN VALUE 0 OK @@ -846,7 +849,7 @@ Event_timed::init() */ int -Event_job_data::load_from_row(TABLE *table) +Event_job_data::load_from_row(THD *thd, TABLE *table) { char *ptr; uint len; @@ -858,9 +861,12 @@ Event_job_data::load_from_row(TABLE *table) if (table->s->fields != ET_FIELD_COUNT) goto error; + LEX_STRING tz_name; load_string_fields(table->field, ET_FIELD_DB, &dbname, ET_FIELD_NAME, &name, ET_FIELD_BODY, &body, ET_FIELD_DEFINER, &definer, - ET_FIELD_COUNT); + ET_FIELD_TIME_ZONE, &tz_name, ET_FIELD_COUNT); + if (load_time_zone(thd, tz_name)) + goto error; ptr= strchr(definer.str, '@'); @@ -887,7 +893,7 @@ error: Loads an event from a row from mysql.event SYNOPSIS - Event_queue_element::load_from_row(MEM_ROOT *mem_root, TABLE *table) + Event_queue_element::load_from_row(THD *thd, TABLE *table) RETURN VALUE 0 OK @@ -900,10 +906,10 @@ error: */ int -Event_queue_element::load_from_row(TABLE *table) +Event_queue_element::load_from_row(THD *thd, TABLE *table) { char *ptr; - bool res1, res2; + TIME time; DBUG_ENTER("Event_queue_element::load_from_row"); @@ -913,29 +919,44 @@ Event_queue_element::load_from_row(TABLE *table) if (table->s->fields != ET_FIELD_COUNT) goto error; + LEX_STRING tz_name; load_string_fields(table->field, ET_FIELD_DB, &dbname, ET_FIELD_NAME, &name, - ET_FIELD_DEFINER, &definer, ET_FIELD_COUNT); + ET_FIELD_DEFINER, &definer, + ET_FIELD_TIME_ZONE, &tz_name, ET_FIELD_COUNT); + if (load_time_zone(thd, tz_name)) + goto error; starts_null= table->field[ET_FIELD_STARTS]->is_null(); - res1= table->field[ET_FIELD_STARTS]->get_date(&starts, TIME_NO_ZERO_DATE); + if (!starts_null) + { + table->field[ET_FIELD_STARTS]->get_date(&time, TIME_NO_ZERO_DATE); + starts= sec_since_epoch_TIME(&time); + } ends_null= table->field[ET_FIELD_ENDS]->is_null(); - res2= table->field[ET_FIELD_ENDS]->get_date(&ends, TIME_NO_ZERO_DATE); + if (!ends_null) + { + table->field[ET_FIELD_ENDS]->get_date(&time, TIME_NO_ZERO_DATE); + ends= sec_since_epoch_TIME(&time); + } if (!table->field[ET_FIELD_INTERVAL_EXPR]->is_null()) expression= table->field[ET_FIELD_INTERVAL_EXPR]->val_int(); else expression= 0; /* - If res1 and res2 are TRUE then both fields are empty. + If neigher STARTS and ENDS is set, then both fields are empty. Hence, if ET_FIELD_EXECUTE_AT is empty there is an error. */ execute_at_null= table->field[ET_FIELD_EXECUTE_AT]->is_null(); DBUG_ASSERT(!(starts_null && ends_null && !expression && execute_at_null)); - if (!expression && - table->field[ET_FIELD_EXECUTE_AT]->get_date(&execute_at, - TIME_NO_ZERO_DATE)) - goto error; + if (!expression && !execute_at_null) + { + if (table->field[ET_FIELD_EXECUTE_AT]->get_date(&time, + TIME_NO_ZERO_DATE)) + goto error; + execute_at= sec_since_epoch_TIME(&time); + } /* We load the interval type from disk as string and then map it to @@ -962,11 +983,14 @@ Event_queue_element::load_from_row(TABLE *table) interval= (interval_type) i; } - table->field[ET_FIELD_LAST_EXECUTED]->get_date(&last_executed, - TIME_NO_ZERO_DATE); + if (!table->field[ET_FIELD_LAST_EXECUTED]->is_null()) + { + table->field[ET_FIELD_LAST_EXECUTED]->get_date(&time, + TIME_NO_ZERO_DATE); + last_executed= sec_since_epoch_TIME(&time); + } last_executed_changed= FALSE; - if ((ptr= get_field(&mem_root, table->field[ET_FIELD_STATUS])) == NullS) goto error; @@ -992,7 +1016,7 @@ error: Loads an event from a row from mysql.event SYNOPSIS - Event_timed::load_from_row(MEM_ROOT *mem_root, TABLE *table) + Event_timed::load_from_row(THD *thd, TABLE *table) RETURN VALUE 0 OK @@ -1005,14 +1029,14 @@ error: */ int -Event_timed::load_from_row(TABLE *table) +Event_timed::load_from_row(THD *thd, TABLE *table) { char *ptr; uint len; DBUG_ENTER("Event_timed::load_from_row"); - if (Event_queue_element::load_from_row(table)) + if (Event_queue_element::load_from_row(thd, table)) goto error; load_string_fields(table->field, ET_FIELD_BODY, &body, ET_FIELD_COUNT); @@ -1048,11 +1072,30 @@ error: /* - Computes the sum of a timestamp plus interval. Presumed is that at least one - previous execution has occured. + add_interval() adds a specified interval to time 'ltime' in time + zone 'time_zone', and returns the result converted to the number of + seconds since epoch (aka Unix time; in UTC time zone). Zero result + means an error. +*/ +static +my_time_t +add_interval(TIME *ltime, const Time_zone *time_zone, + interval_type scale, INTERVAL interval) +{ + if (date_add_interval(ltime, scale, interval)) + return 0; + + my_bool not_used; + return time_zone->TIME_to_gmt_sec(ltime, ¬_used); +} + + +/* + Computes the sum of a timestamp plus interval. SYNOPSIS - get_next_time(TIME *start, int interval_value, interval_type interval) + get_next_time() + time_zone event time zone next the sum start add interval_value to this time time_now current time @@ -1069,26 +1112,19 @@ error: seconds as resolution for computation. 2) In all other cases - MONTH, QUARTER, YEAR we use MONTH as resolution and PERIOD_DIFF()'s implementation - 3) We get the difference between time_now and `start`, then divide it - by the months, respectively seconds and round up. Then we multiply - monts/seconds by the rounded value and add it to `start` -> we get - the next execution time. */ static -bool get_next_time(TIME *next, TIME *start, TIME *time_now, TIME *last_exec, +bool get_next_time(const Time_zone *time_zone, my_time_t *next, + my_time_t start, my_time_t time_now, int i_value, interval_type i_type) { - bool ret; - INTERVAL interval; - TIME tmp; - longlong months=0, seconds=0; DBUG_ENTER("get_next_time"); - DBUG_PRINT("enter", ("start: %lu now: %lu", - (long) TIME_to_ulonglong_datetime(start), - (long) TIME_to_ulonglong_datetime(time_now))); + DBUG_PRINT("enter", ("start: %lu now: %lu", (long) start, (long) time_now)); - bzero(&interval, sizeof(interval)); + DBUG_ASSERT(start <= time_now); + + longlong months=0, seconds=0; switch (i_type) { case INTERVAL_YEAR: @@ -1135,84 +1171,151 @@ bool get_next_time(TIME *next, TIME *start, TIME *time_now, TIME *last_exec, DBUG_ASSERT(0); } DBUG_PRINT("info", ("seconds: %ld months: %ld", (long) seconds, (long) months)); + + TIME local_start; + TIME local_now; + + /* Convert times from UTC to local. */ + { + time_zone->gmt_sec_to_TIME(&local_start, start); + time_zone->gmt_sec_to_TIME(&local_now, time_now); + } + + INTERVAL interval; + bzero(&interval, sizeof(interval)); + my_time_t next_time= 0; + if (seconds) { longlong seconds_diff; long microsec_diff; + bool negative= calc_time_diff(&local_now, &local_start, 1, + &seconds_diff, µsec_diff); + if (!negative) + { + /* + The formula below returns the interval that, when added to + local_start, will always give the time in the future. + */ + interval.second= seconds_diff - seconds_diff % seconds + seconds; + next_time= add_interval(&local_start, time_zone, + INTERVAL_SECOND, interval); + if (next_time == 0) + goto done; + } - if (calc_time_diff(time_now, start, 1, &seconds_diff, µsec_diff)) + if (next_time <= time_now) { - DBUG_PRINT("error", ("negative difference")); - DBUG_ASSERT(0); + /* + If 'negative' is true above, then 'next_time == 0', and + 'next_time <= time_now' is also true. If negative is false, + then next_time was set, but perhaps to the value that is less + then time_now. See below for elaboration. + */ + DBUG_ASSERT(negative || next_time > 0); + + /* + If local_now < local_start, i.e. STARTS time is in the future + according to the local time (it always in the past according + to UTC---this is a prerequisite of this function), then + STARTS is almost always in the past according to the local + time too. However, in the time zone that has backward + Daylight Saving Time shift, the following may happen: suppose + we have a backward DST shift at certain date after 2:59:59, + i.e. local time goes 1:59:59, 2:00:00, ... , 2:59:59, (shift + here) 2:00:00 (again), ... , 2:59:59 (again), 3:00:00, ... . + Now suppose the time has passed the first 2:59:59, has been + shifted backward, and now is (the second) 2:20:00. The user + does CREATE EVENT with STARTS 'current-date 2:40:00'. Local + time 2:40:00 from create statement is treated by time + functions as the first such time, so according to UTC it comes + before the second 2:20:00. But according to local time it is + obviously in the future, so we end up in this branch. + + Since we are in the second pass through 2:00:00--2:59:59, and + any local time form this interval is treated by system + functions as the time from the first pass, we have to find the + time for the next execution that is past the DST-affected + interval (past the second 2:59:59 for our example, + i.e. starting from 3:00:00). We do this in the loop until the + local time is mapped onto future UTC time. 'start' time is in + the past, so we may use 'do { } while' here, and add the first + interval right away. + + Alternatively, it could be that local_now >= local_start. Now + for the example above imagine we do CREATE EVENT with STARTS + 'current-date 2:10:00'. Local start 2:10 is in the past (now + is local 2:20), so we add an interval, and get next execution + time, say, 2:40. It is in the future according to local time, + but, again, since we are in the second pass through + 2:00:00--2:59:59, 2:40 will be converted into UTC time in the + past. So we will end up in this branch again, and may add + intervals in a 'do { } while' loop. + + Note that for any given event we may end up here only if event + next execution time will map to the time interval that is + passed twice, and only if the server was started during the + second pass, or the event is being created during the second + pass. After that, we never will get here (unless we again + start the server during the second pass). In other words, + such a condition is extremely rare. + */ + interval.second= seconds; + do + { + next_time= add_interval(&local_start, time_zone, + INTERVAL_SECOND, interval); + if (next_time == 0) + goto done; + } + while (next_time <= time_now); } - uint multiplier= (uint) (seconds_diff / seconds); - /* - Increase the multiplier is the modulus is not zero to make round up. - Or if time_now==start then we should not execute the same - event two times for the same time - get the next exec if the modulus is not - */ - DBUG_PRINT("info", ("multiplier: %d", multiplier)); - if (seconds_diff % seconds || (!seconds_diff && last_exec->year) || - TIME_to_ulonglong_datetime(time_now) == - TIME_to_ulonglong_datetime(last_exec)) - ++multiplier; - interval.second= seconds * multiplier; - DBUG_PRINT("info", ("multiplier: %lu interval.second: %lu", (ulong) multiplier, - (ulong) interval.second)); - tmp= *start; - if (!(ret= date_add_interval(&tmp, INTERVAL_SECOND, interval))) - *next= tmp; } else { - /* PRESUMED is that at least one execution took already place */ - int diff_months= (time_now->year - start->year)*12 + - (time_now->month - start->month); - /* - Note: If diff_months is 0 that means we are in the same month as the - last execution which is also the first execution. - */ + long diff_months= (long) (local_now.year - local_start.year)*12 + + (local_now.month - local_start.month); /* - First we try with the smaller if not then + 1, because if we try with - directly with +1 we will be after the current date but it could be that - we will be 1 month ahead, so 2 steps are necessary. + Unlike for seconds above, the formula below returns the interval + that, when added to the local_start, will give the time in the + past, or somewhere in the current month. We are interested in + the latter case, to see if this time has already passed, or is + yet to come this month. + + Note that the time is guaranteed to be in the past unless + (diff_months % months == 0), but no good optimization is + possible here, because (diff_months % months == 0) is what will + happen most of the time, as get_next_time() will be called right + after the execution of the event. We could pass last_executed + time to this function, and see if the execution has already + happened this month, but for that we will have to convert + last_executed from seconds since epoch to local broken-down + time, and this will greatly reduce the effect of the + optimization. So instead we keep the code simple and clean. */ - interval.month= (ulong) ((diff_months / months)*months); - /* - Check if the same month as last_exec (always set - prerequisite) - An event happens at most once per month so there is no way to - schedule it two times for the current month. This saves us from two - calls to date_add_interval() if the event was just executed. But if - the scheduler is started and there was at least 1 scheduled date - skipped this one does not help and two calls to date_add_interval() - will be done, which is a bit more expensive but compared to the - rareness of the case is neglectable. - */ - if (time_now->year == last_exec->year && - time_now->month == last_exec->month) - interval.month+= (ulong) months; - - tmp= *start; - if ((ret= date_add_interval(&tmp, INTERVAL_MONTH, interval))) + interval.month= diff_months - diff_months % months; + next_time= add_interval(&local_start, time_zone, + INTERVAL_MONTH, interval); + if (next_time == 0) goto done; - /* If `tmp` is still before time_now just add one more time the interval */ - if (my_time_compare(&tmp, time_now) == -1) - { - interval.month+= (ulong) months; - tmp= *start; - if ((ret= date_add_interval(&tmp, INTERVAL_MONTH, interval))) + if (next_time <= time_now) + { + interval.month= months; + next_time= add_interval(&local_start, time_zone, + INTERVAL_MONTH, interval); + if (next_time == 0) goto done; } - *next= tmp; - /* assert on that the next is after now */ - DBUG_ASSERT(1==my_time_compare(next, time_now)); } + DBUG_ASSERT(time_now < next_time); + + *next= next_time; + done: - DBUG_PRINT("info", ("next: %lu", (long) TIME_to_ulonglong_datetime(next))); - DBUG_RETURN(ret); + DBUG_PRINT("info", ("next_time: %ld", (long) next_time)); + DBUG_RETURN(next_time == 0); } @@ -1227,20 +1330,17 @@ done: TRUE Error NOTES - The time is set in execute_at, if no more executions the latter is set to - 0000-00-00. + The time is set in execute_at, if no more executions the latter is + set to 0. */ bool Event_queue_element::compute_next_execution_time() { - TIME time_now; - int tmp; + my_time_t time_now; DBUG_ENTER("Event_queue_element::compute_next_execution_time"); DBUG_PRINT("enter", ("starts: %lu ends: %lu last_executed: %lu this: 0x%lx", - (long) TIME_to_ulonglong_datetime(&starts), - (long) TIME_to_ulonglong_datetime(&ends), - (long) TIME_to_ulonglong_datetime(&last_executed), + (long) starts, (long) ends, (long) last_executed, (long) this)); if (status == Event_queue_element::DISABLED) @@ -1253,7 +1353,7 @@ Event_queue_element::compute_next_execution_time() if (!expression) { /* Let's check whether it was executed */ - if (last_executed.year) + if (last_executed) { DBUG_PRINT("info",("One-time event %s.%s of was already executed", dbname.str, name.str)); @@ -1266,17 +1366,16 @@ Event_queue_element::compute_next_execution_time() goto ret; } - my_tz_UTC->gmt_sec_to_TIME(&time_now, current_thd->query_start()); + time_now= current_thd->query_start(); - DBUG_PRINT("info",("NOW: [%lu]", - (ulong) TIME_to_ulonglong_datetime(&time_now))); + DBUG_PRINT("info",("NOW: [%lu]", (ulong) time_now)); /* if time_now is after ends don't execute anymore */ - if (!ends_null && (tmp= my_time_compare(&ends, &time_now)) == -1) + if (!ends_null && ends < time_now) { DBUG_PRINT("info", ("NOW after ENDS, don't execute anymore")); /* time_now is after ends. don't execute anymore */ - set_zero_time(&execute_at, MYSQL_TIMESTAMP_DATETIME); + execute_at= 0; execute_at_null= TRUE; if (on_completion == Event_queue_element::ON_COMPLETION_DROP) dropped= TRUE; @@ -1293,12 +1392,11 @@ Event_queue_element::compute_next_execution_time() Let's check whether time_now is before starts. If so schedule for starts. */ - if (!starts_null && (tmp= my_time_compare(&time_now, &starts)) < 1) + if (!starts_null && time_now <= starts) { - if (tmp == 0 && my_time_compare(&starts, &last_executed) == 0) + if (time_now == starts && starts == last_executed) { /* - time_now = starts = last_executed do nothing or we will schedule for second time execution at starts. */ } @@ -1324,26 +1422,25 @@ Event_queue_element::compute_next_execution_time() If not set then schedule for now. */ DBUG_PRINT("info", ("Both STARTS & ENDS are set")); - if (!last_executed.year) + if (!last_executed) { DBUG_PRINT("info", ("Not executed so far.")); } { - TIME next_exec; + my_time_t next_exec; - if (get_next_time(&next_exec, &starts, &time_now, - last_executed.year? &last_executed:&starts, + if (get_next_time(time_zone, &next_exec, starts, time_now, (int) expression, interval)) goto err; /* There was previous execution */ - if (my_time_compare(&ends, &next_exec) == -1) + if (ends < next_exec) { DBUG_PRINT("info", ("Next execution of %s after ENDS. Stop executing.", name.str)); /* Next execution after ends. No more executions */ - set_zero_time(&execute_at, MYSQL_TIMESTAMP_DATETIME); + execute_at= 0; execute_at_null= TRUE; if (on_completion == Event_queue_element::ON_COMPLETION_DROP) dropped= TRUE; @@ -1352,8 +1449,7 @@ Event_queue_element::compute_next_execution_time() } else { - DBUG_PRINT("info",("Next[%lu]", - (ulong) TIME_to_ulonglong_datetime(&next_exec))); + DBUG_PRINT("info",("Next[%lu]", (ulong) next_exec)); execute_at= next_exec; execute_at_null= FALSE; } @@ -1368,15 +1464,14 @@ Event_queue_element::compute_next_execution_time() Both starts and m_ends are not set, so we schedule for the next based on last_executed. */ - if (last_executed.year) + if (last_executed) { - TIME next_exec; - if (get_next_time(&next_exec, &starts, &time_now, &last_executed, + my_time_t next_exec; + if (get_next_time(time_zone, &next_exec, starts, time_now, (int) expression, interval)) goto err; execute_at= next_exec; - DBUG_PRINT("info",("Next[%lu]", - (ulong) TIME_to_ulonglong_datetime(&next_exec))); + DBUG_PRINT("info",("Next[%lu]", (ulong) next_exec)); } else { @@ -1398,20 +1493,18 @@ Event_queue_element::compute_next_execution_time() Hence schedule for starts + m_expression in case last_executed is not set, otherwise to last_executed + m_expression */ - if (!last_executed.year) + if (!last_executed) { DBUG_PRINT("info", ("Not executed so far.")); } { - TIME next_exec; - if (get_next_time(&next_exec, &starts, &time_now, - last_executed.year? &last_executed:&starts, + my_time_t next_exec; + if (get_next_time(time_zone, &next_exec, starts, time_now, (int) expression, interval)) goto err; execute_at= next_exec; - DBUG_PRINT("info",("Next[%lu]", - (ulong) TIME_to_ulonglong_datetime(&next_exec))); + DBUG_PRINT("info",("Next[%lu]", (ulong) next_exec)); } execute_at_null= FALSE; } @@ -1426,20 +1519,20 @@ Event_queue_element::compute_next_execution_time() If last_executed is not set then schedule for now */ - if (!last_executed.year) + if (!last_executed) execute_at= time_now; else { - TIME next_exec; + my_time_t next_exec; - if (get_next_time(&next_exec, &starts, &time_now, &last_executed, + if (get_next_time(time_zone, &next_exec, starts, time_now, (int) expression, interval)) goto err; - if (my_time_compare(&ends, &next_exec) == -1) + if (ends < next_exec) { DBUG_PRINT("info", ("Next execution after ENDS. Stop executing.")); - set_zero_time(&execute_at, MYSQL_TIMESTAMP_DATETIME); + execute_at= 0; execute_at_null= TRUE; status= Event_queue_element::DISABLED; status_changed= TRUE; @@ -1448,8 +1541,7 @@ Event_queue_element::compute_next_execution_time() } else { - DBUG_PRINT("info", ("Next[%lu]", - (ulong) TIME_to_ulonglong_datetime(&next_exec))); + DBUG_PRINT("info", ("Next[%lu]", (ulong) next_exec)); execute_at= next_exec; execute_at_null= FALSE; } @@ -1458,8 +1550,7 @@ Event_queue_element::compute_next_execution_time() goto ret; } ret: - DBUG_PRINT("info", ("ret: 0 execute_at: %lu", - (long) TIME_to_ulonglong_datetime(&execute_at))); + DBUG_PRINT("info", ("ret: 0 execute_at: %lu", (long) execute_at)); DBUG_RETURN(FALSE); err: DBUG_PRINT("info", ("ret=1")); @@ -1479,12 +1570,9 @@ err: void Event_queue_element::mark_last_executed(THD *thd) { - TIME time_now; - thd->end_time(); - my_tz_UTC->gmt_sec_to_TIME(&time_now, (my_time_t) thd->query_start()); - last_executed= time_now; /* was execute_at */ + last_executed= thd->query_start(); last_executed_changed= TRUE; execution_count++; @@ -1538,8 +1626,11 @@ Event_queue_element::update_timing_fields(THD *thd) if (last_executed_changed) { + TIME time; + my_tz_UTC->gmt_sec_to_TIME(&time, last_executed); + fields[ET_FIELD_LAST_EXECUTED]->set_notnull(); - fields[ET_FIELD_LAST_EXECUTED]->store_time(&last_executed, + fields[ET_FIELD_LAST_EXECUTED]->store_time(&time, MYSQL_TIMESTAMP_DATETIME); last_executed_changed= FALSE; } @@ -1561,6 +1652,26 @@ done: } +static +void +append_datetime(String *buf, Time_zone *time_zone, my_time_t secs, + const char *name, uint len) +{ + char dtime_buff[20*2+32];/* +32 to make my_snprintf_{8bit|ucs2} happy */ + buf->append(STRING_WITH_LEN(" ")); + buf->append(name, len); + buf->append(STRING_WITH_LEN(" '")); + /* + Pass the buffer and the second param tells fills the buffer and + returns the number of chars to copy. + */ + TIME time; + time_zone->gmt_sec_to_TIME(&time, secs); + buf->append(dtime_buff, my_datetime_to_str(&time, dtime_buff)); + buf->append(STRING_WITH_LEN("'")); +} + + /* Get SHOW CREATE EVENT as string @@ -1600,17 +1711,17 @@ Event_timed::get_create_event(THD *thd, String *buf) buf->append(' '); LEX_STRING *ival= &interval_type_to_name[interval]; buf->append(ival->str, ival->length); + + if (!starts_null) + append_datetime(buf, time_zone, starts, STRING_WITH_LEN("STARTS")); + + if (!ends_null) + append_datetime(buf, time_zone, ends, STRING_WITH_LEN("ENDS")); } else { - char dtime_buff[20*2+32];/* +32 to make my_snprintf_{8bit|ucs2} happy */ - buf->append(STRING_WITH_LEN(" ON SCHEDULE AT '")); - /* - Pass the buffer and the second param tells fills the buffer and - returns the number of chars to copy. - */ - buf->append(dtime_buff, my_datetime_to_str(&execute_at, dtime_buff)); - buf->append(STRING_WITH_LEN("'")); + append_datetime(buf, time_zone, execute_at, + STRING_WITH_LEN("ON SCHEDULE AT")); } if (on_completion == Event_timed::ON_COMPLETION_DROP) @@ -1654,6 +1765,7 @@ int Event_job_data::get_fake_create_event(THD *thd, String *buf) { DBUG_ENTER("Event_job_data::get_create_event"); + /* FIXME: "EVERY 3337 HOUR" is asking for trouble. */ buf->append(STRING_WITH_LEN("CREATE EVENT anonymous ON SCHEDULE " "EVERY 3337 HOUR DO ")); buf->append(body.str, body.length); @@ -1705,6 +1817,9 @@ Event_job_data::execute(THD *thd) sphead->m_flags|= sp_head::LOG_SLOW_STATEMENTS; sphead->m_flags|= sp_head::LOG_GENERAL_LOG; + /* Execute the event in its time zone. */ + thd->variables.time_zone= time_zone; + ret= sphead->execute_procedure(thd, &empty_item_list); } else diff --git a/sql/event_data_objects.h b/sql/event_data_objects.h index 4346b0eb5b8..8e013e40400 100644 --- a/sql/event_data_objects.h +++ b/sql/event_data_objects.h @@ -58,15 +58,20 @@ public: LEX_STRING name; LEX_STRING definer;// combination of user and host + Time_zone *time_zone; + Event_basic(); virtual ~Event_basic(); virtual int - load_from_row(TABLE *table) = 0; + load_from_row(THD *thd, TABLE *table) = 0; protected: bool load_string_fields(Field **fields, ...); + + bool + load_time_zone(THD *thd, const LEX_STRING tz_name); }; @@ -92,11 +97,11 @@ public: enum enum_on_completion on_completion; enum enum_status status; - TIME last_executed; - TIME execute_at; - TIME starts; - TIME ends; + my_time_t last_executed; + my_time_t execute_at; + my_time_t starts; + my_time_t ends; my_bool starts_null; my_bool ends_null; my_bool execute_at_null; @@ -112,7 +117,7 @@ public: virtual ~Event_queue_element(); virtual int - load_from_row(TABLE *table); + load_from_row(THD *thd, TABLE *table); bool compute_next_execution_time(); @@ -168,7 +173,7 @@ public: init(); virtual int - load_from_row(TABLE *table); + load_from_row(THD *thd, TABLE *table); int get_create_event(THD *thd, String *buf); @@ -192,7 +197,7 @@ public: virtual ~Event_job_data(); virtual int - load_from_row(TABLE *table); + load_from_row(THD *thd, TABLE *table); int execute(THD *thd); @@ -224,6 +229,11 @@ public: }; enum enum_on_completion on_completion; enum enum_status status; + /* + do_not_create will be set if STARTS time is in the past and + on_completion == ON_COMPLETION_DROP. + */ + bool do_not_create; const uchar *body_begin; @@ -237,9 +247,9 @@ public: Item* item_ends; Item* item_execute_at; - TIME starts; - TIME ends; - TIME execute_at; + my_time_t starts; + my_time_t ends; + my_time_t execute_at; my_bool starts_null; my_bool ends_null; my_bool execute_at_null; @@ -284,6 +294,9 @@ private: void report_bad_value(const char *item_name, Item *bad_item); + void + check_if_in_the_past(THD *thd, my_time_t ltime_utc); + Event_parse_data(const Event_parse_data &); /* Prevent use of these */ void operator=(Event_parse_data &); }; diff --git a/sql/event_db_repository.cc b/sql/event_db_repository.cc index 940930ec4c6..6be7411af7b 100644 --- a/sql/event_db_repository.cc +++ b/sql/event_db_repository.cc @@ -118,6 +118,11 @@ const TABLE_FIELD_W_TYPE event_table_fields[ET_FIELD_COUNT] = { C_STRING_WITH_LEN("comment") }, { C_STRING_WITH_LEN("char(64)") }, { C_STRING_WITH_LEN("utf8") } + }, + { + { C_STRING_WITH_LEN("time_zone") }, + { C_STRING_WITH_LEN("char(64)") }, + { C_STRING_WITH_LEN("latin1") } } }; @@ -183,6 +188,14 @@ mysql_event_fill_row(THD *thd, TABLE *table, Event_parse_data *et, if (et->expression) { + const String *tz_name= thd->variables.time_zone->get_name(); + if (!is_update || !et->starts_null) + { + fields[ET_FIELD_TIME_ZONE]->set_notnull(); + fields[ET_FIELD_TIME_ZONE]->store(tz_name->ptr(), tz_name->length(), + tz_name->charset()); + } + fields[ET_FIELD_INTERVAL_EXPR]->set_notnull(); fields[ET_FIELD_INTERVAL_EXPR]->store((longlong)et->expression, TRUE); @@ -197,26 +210,40 @@ mysql_event_fill_row(THD *thd, TABLE *table, Event_parse_data *et, if (!et->starts_null) { + TIME time; + my_tz_UTC->gmt_sec_to_TIME(&time, et->starts); + fields[ET_FIELD_STARTS]->set_notnull(); - fields[ET_FIELD_STARTS]->store_time(&et->starts, MYSQL_TIMESTAMP_DATETIME); + fields[ET_FIELD_STARTS]->store_time(&time, MYSQL_TIMESTAMP_DATETIME); } if (!et->ends_null) { + TIME time; + my_tz_UTC->gmt_sec_to_TIME(&time, et->ends); + fields[ET_FIELD_ENDS]->set_notnull(); - fields[ET_FIELD_ENDS]->store_time(&et->ends, MYSQL_TIMESTAMP_DATETIME); + fields[ET_FIELD_ENDS]->store_time(&time, MYSQL_TIMESTAMP_DATETIME); } } - else if (et->execute_at.year) + else if (et->execute_at) { + const String *tz_name= thd->variables.time_zone->get_name(); + fields[ET_FIELD_TIME_ZONE]->set_notnull(); + fields[ET_FIELD_TIME_ZONE]->store(tz_name->ptr(), tz_name->length(), + tz_name->charset()); + fields[ET_FIELD_INTERVAL_EXPR]->set_null(); fields[ET_FIELD_TRANSIENT_INTERVAL]->set_null(); fields[ET_FIELD_STARTS]->set_null(); fields[ET_FIELD_ENDS]->set_null(); + TIME time; + my_tz_UTC->gmt_sec_to_TIME(&time, et->execute_at); + fields[ET_FIELD_EXECUTE_AT]->set_notnull(); fields[ET_FIELD_EXECUTE_AT]-> - store_time(&et->execute_at, MYSQL_TIMESTAMP_DATETIME); + store_time(&time, MYSQL_TIMESTAMP_DATETIME); } else { @@ -527,6 +554,8 @@ Event_db_repository::create_event(THD *thd, Event_parse_data *parse_data, if (check_parse_params(thd, parse_data)) goto err; + if (parse_data->do_not_create) + goto ok; DBUG_PRINT("info", ("open mysql.event for update")); if (open_event_table(thd, TL_WRITE, &table)) @@ -587,7 +616,7 @@ Event_db_repository::create_event(THD *thd, Event_parse_data *parse_data, goto err; } - if (!(parse_data->expression) && !(parse_data->execute_at.year)) + if (!(parse_data->expression) && !(parse_data->execute_at)) { DBUG_PRINT("error", ("neither expression nor execute_at are set!")); my_error(ER_EVENT_NEITHER_M_EXPR_NOR_M_AT, MYF(0)); @@ -664,7 +693,7 @@ Event_db_repository::update_event(THD *thd, Event_parse_data *parse_data, goto err; } - if (check_parse_params(thd, parse_data)) + if (check_parse_params(thd, parse_data) || parse_data->do_not_create) goto err; DBUG_PRINT("info", ("dbname: %s", parse_data->dbname.str)); @@ -964,7 +993,7 @@ Event_db_repository::load_named_event(THD *thd, LEX_STRING dbname, my_error(ER_EVENT_OPEN_TABLE_FAILED, MYF(0)); else if ((ret= find_named_event(thd, dbname, name, table))) my_error(ER_EVENT_DOES_NOT_EXIST, MYF(0), name.str); - else if ((ret= etn->load_from_row(table))) + else if ((ret= etn->load_from_row(thd, table))) my_error(ER_CANNOT_LOAD_FROM_TABLE, MYF(0), "event"); if (table) diff --git a/sql/event_db_repository.h b/sql/event_db_repository.h index 1457fb64e2e..4991e2d0aa2 100644 --- a/sql/event_db_repository.h +++ b/sql/event_db_repository.h @@ -35,6 +35,7 @@ enum enum_events_table_field ET_FIELD_ON_COMPLETION, ET_FIELD_SQL_MODE, ET_FIELD_COMMENT, + ET_FIELD_TIME_ZONE, ET_FIELD_COUNT /* a cool trick to count the number of fields :) */ }; diff --git a/sql/event_queue.cc b/sql/event_queue.cc index 296c30506f6..bcfe0a222f1 100644 --- a/sql/event_queue.cc +++ b/sql/event_queue.cc @@ -65,8 +65,13 @@ struct event_queue_param static int event_queue_element_compare_q(void *vptr, byte* a, byte *b) { - return my_time_compare(&((Event_queue_element *)a)->execute_at, - &((Event_queue_element *)b)->execute_at); + /* + Note that no overflow is possible here because both values are + non-negative, and subtraction is done in the signed my_time_t + type. + */ + return (((Event_queue_element *)a)->execute_at + - ((Event_queue_element *)b)->execute_at); } @@ -84,7 +89,7 @@ Event_queue::Event_queue() { mutex_last_unlocked_in_func= mutex_last_locked_in_func= mutex_last_attempted_lock_in_func= ""; - set_zero_time(&next_activation_at, MYSQL_TIMESTAMP_DATETIME); + next_activation_at= 0; } @@ -504,15 +509,11 @@ Event_queue::dbug_dump_queue(time_t now) DBUG_PRINT("info", ("exec_at: %lu starts: %lu ends: %lu execs_so_far: %u " "expr: %ld et.exec_at: %ld now: %ld " "(et.exec_at - now): %d if: %d", - (long) TIME_to_ulonglong_datetime(&et->execute_at), - (long) TIME_to_ulonglong_datetime(&et->starts), - (long) TIME_to_ulonglong_datetime(&et->ends), - et->execution_count, - (long) et->expression, - (long) (sec_since_epoch_TIME(&et->execute_at)), - (long) now, - (int) (sec_since_epoch_TIME(&et->execute_at) - now), - sec_since_epoch_TIME(&et->execute_at) <= now)); + (long) et->execute_at, (long) et->starts, + (long) et->ends, et->execution_count, + (long) et->expression, (long) et->execute_at, + (long) now, (int) (et->execute_at - now), + et->execute_at <= now)); } DBUG_VOID_RETURN; #endif @@ -541,7 +542,6 @@ Event_queue::get_top_for_execution_if_time(THD *thd, Event_queue_element_for_exec **event_name) { bool ret= FALSE; - struct timespec top_time; *event_name= NULL; DBUG_ENTER("Event_queue::get_top_for_execution_if_time"); @@ -560,7 +560,7 @@ Event_queue::get_top_for_execution_if_time(THD *thd, if (!queue.elements) { /* There are no events in the queue */ - set_zero_time(&next_activation_at, MYSQL_TIMESTAMP_DATETIME); + next_activation_at= 0; /* Wait on condition until signaled. Release LOCK_queue while waiting. */ cond_wait(thd, NULL, queue_empty_msg, SCHED_FUNC, __LINE__); @@ -572,16 +572,16 @@ Event_queue::get_top_for_execution_if_time(THD *thd, thd->end_time(); /* Get current time */ - time_t seconds_to_next_event= - sec_since_epoch_TIME(&top->execute_at) - thd->query_start(); next_activation_at= top->execute_at; - if (seconds_to_next_event > 0) + if (next_activation_at > thd->query_start()) { /* Not yet time for top event, wait on condition with time or until signaled. Release LOCK_queue while waiting. */ - set_timespec(top_time, seconds_to_next_event); + struct timespec top_time; + top_time.tv_sec= next_activation_at; + top_time.tv_nsec= 0; cond_wait(thd, &top_time, queue_wait_msg, SCHED_FUNC, __LINE__); continue; @@ -759,10 +759,11 @@ Event_queue::dump_internal_status() printf("Last lock attempt at: %s:%u\n", mutex_last_attempted_lock_in_func, mutex_last_attempted_lock_at_line); printf("WOC : %s\n", waiting_on_cond? "YES":"NO"); + + TIME time; + my_tz_UTC->gmt_sec_to_TIME(&time, next_activation_at); printf("Next activation : %04d-%02d-%02d %02d:%02d:%02d\n", - next_activation_at.year, next_activation_at.month, - next_activation_at.day, next_activation_at.hour, - next_activation_at.minute, next_activation_at.second); + time.year, time.month, time.day, time.hour, time.minute, time.second); DBUG_VOID_RETURN; } diff --git a/sql/event_queue.h b/sql/event_queue.h index a1237e1b52c..338a6c8f903 100644 --- a/sql/event_queue.h +++ b/sql/event_queue.h @@ -86,7 +86,7 @@ protected: /* The sorted queue with the Event_queue_element objects */ QUEUE queue; - TIME next_activation_at; + my_time_t next_activation_at; uint mutex_last_locked_at_line; uint mutex_last_unlocked_at_line; diff --git a/sql/event_scheduler.cc b/sql/event_scheduler.cc index 64bba756be9..3c8ecf6ca3a 100644 --- a/sql/event_scheduler.cc +++ b/sql/event_scheduler.cc @@ -574,6 +574,14 @@ Event_scheduler::execute_top(THD *thd, Event_queue_element_for_exec *event_name) DBUG_PRINT("info", ("Event %s@%s ready for start", event_name->dbname.str, event_name->name.str)); + /* + TODO: should use thread pool here, preferably with an upper limit + on number of threads: if too many events are scheduled for the + same time, starting all of them at once won't help them run truly + in parallel (because of the great amount of synchronization), so + we may as well execute them in sequence, keeping concurrency at a + reasonable level. + */ /* Major failure */ if ((res= pthread_create(&th, &connection_attrib, event_worker_thread, event_name))) diff --git a/sql/events.cc b/sql/events.cc index f73dc97e7c2..88014ee34af 100644 --- a/sql/events.cc +++ b/sql/events.cc @@ -326,7 +326,8 @@ Events::create_event(THD *thd, Event_parse_data *parse_data, bool if_not_exists) pthread_mutex_lock(&LOCK_event_metadata); /* On error conditions my_error() is called so no need to handle here */ - if (!(ret= db_repository->create_event(thd, parse_data, if_not_exists))) + if (!(ret= db_repository->create_event(thd, parse_data, if_not_exists)) && + !parse_data->do_not_create) { Event_queue_element *new_element; @@ -527,6 +528,10 @@ Events::show_create_event(THD *thd, LEX_STRING dbname, LEX_STRING name) field_list.push_back(new Item_empty_string("sql_mode", sql_mode_len)); + const String *tz_name= et->time_zone->get_name(); + field_list.push_back(new Item_empty_string("time_zone", + tz_name->length())); + field_list.push_back(new Item_empty_string("Create Event", show_str.length())); @@ -539,6 +544,8 @@ Events::show_create_event(THD *thd, LEX_STRING dbname, LEX_STRING name) protocol->store((char*) sql_mode_str, sql_mode_len, scs); + protocol->store((char*) tz_name->ptr(), tz_name->length(), scs); + protocol->store(show_str.c_ptr(), show_str.length(), scs); ret= protocol->write(); send_eof(thd); @@ -942,7 +949,7 @@ Events::load_events_from_db(THD *thd) } DBUG_PRINT("info", ("Loading event from row.")); - if ((ret= et->load_from_row(table))) + if ((ret= et->load_from_row(thd, table))) { sql_print_error("SCHEDULER: Error while loading from mysql.event. " "Table probably corrupted"); @@ -967,7 +974,7 @@ Events::load_events_from_db(THD *thd) Event_job_data temp_job_data; DBUG_PRINT("info", ("Event %s loaded from row. ", et->name.str)); - temp_job_data.load_from_row(table); + temp_job_data.load_from_row(thd, table); /* We load only on scheduler root just to check whether the body diff --git a/sql/share/errmsg.txt b/sql/share/errmsg.txt index c5bda4ac7a6..f7ffad697b0 100644 --- a/sql/share/errmsg.txt +++ b/sql/share/errmsg.txt @@ -5873,8 +5873,7 @@ ER_EVENT_ENDS_BEFORE_STARTS eng "ENDS is either invalid or before STARTS" ger "ENDS ist entweder ungültig oder liegt vor STARTS" ER_EVENT_EXEC_TIME_IN_THE_PAST - eng "Activation (AT) time is in the past" - ger "Aktivierungszeit (AT) liegt in der Vergangenheit" + eng "Event execution time is in the past. Event has been disabled" ER_EVENT_OPEN_TABLE_FAILED eng "Failed to open mysql.event" ger "Öffnen von mysql.event fehlgeschlagen" @@ -6052,3 +6051,7 @@ ER_DUP_ENTRY_WITH_KEY_NAME 23000 S1009 ER_BINLOG_PURGE_EMFILE eng "Too many files opened, please execute the command again" ger "Zu viele offene Dateien, bitte führen Sie den Befehl noch einmal aus" +ER_EVENT_CANNOT_CREATE_IN_THE_PAST + eng "Event execution time is in the past and ON COMPLETION NOT PRESERVE is set. Event has not been created" +ER_EVENT_CANNOT_ALTER_IN_THE_PAST + eng "Event execution time is in the past and ON COMPLETION NOT PRESERVE is set. Event has not been altered" diff --git a/sql/sql_show.cc b/sql/sql_show.cc index bdfb7512979..0c56abbdfc8 100644 --- a/sql/sql_show.cc +++ b/sql/sql_show.cc @@ -38,6 +38,7 @@ enum enum_i_s_events_fields ISE_EVENT_SCHEMA, ISE_EVENT_NAME, ISE_DEFINER, + ISE_TIME_ZONE, ISE_EVENT_BODY, ISE_EVENT_DEFINITION, ISE_EVENT_TYPE, @@ -4310,7 +4311,7 @@ copy_event_to_schema_table(THD *thd, TABLE *sch_table, TABLE *event_table) restore_record(sch_table, s->default_values); - if (et.load_from_row(event_table)) + if (et.load_from_row(thd, event_table)) { my_error(ER_CANNOT_LOAD_FROM_TABLE, MYF(0)); DBUG_RETURN(1); @@ -4337,6 +4338,9 @@ copy_event_to_schema_table(THD *thd, TABLE *sch_table, TABLE *event_table) store(et.name.str, et.name.length, scs); sch_table->field[ISE_DEFINER]-> store(et.definer.str, et.definer.length, scs); + const String *tz_name= et.time_zone->get_name(); + sch_table->field[ISE_TIME_ZONE]-> + store(tz_name->ptr(), tz_name->length(), scs); sch_table->field[ISE_EVENT_BODY]-> store(STRING_WITH_LEN("SQL"), scs); sch_table->field[ISE_EVENT_DEFINITION]-> @@ -4353,6 +4357,8 @@ copy_event_to_schema_table(THD *thd, TABLE *sch_table, TABLE *event_table) store((const char*)sql_mode_str, sql_mode_len, scs); } + int not_used=0; + if (et.expression) { String show_str; @@ -4372,15 +4378,17 @@ copy_event_to_schema_table(THD *thd, TABLE *sch_table, TABLE *event_table) sch_table->field[ISE_INTERVAL_FIELD]->store(ival->str, ival->length, scs); /* starts & ends . STARTS is always set - see sql_yacc.yy */ + et.time_zone->gmt_sec_to_TIME(&time, et.starts); sch_table->field[ISE_STARTS]->set_notnull(); sch_table->field[ISE_STARTS]-> - store_time(&et.starts, MYSQL_TIMESTAMP_DATETIME); + store_time(&time, MYSQL_TIMESTAMP_DATETIME); if (!et.ends_null) { + et.time_zone->gmt_sec_to_TIME(&time, et.ends); sch_table->field[ISE_ENDS]->set_notnull(); sch_table->field[ISE_ENDS]-> - store_time(&et.ends, MYSQL_TIMESTAMP_DATETIME); + store_time(&time, MYSQL_TIMESTAMP_DATETIME); } } else @@ -4388,9 +4396,10 @@ copy_event_to_schema_table(THD *thd, TABLE *sch_table, TABLE *event_table) /* type */ sch_table->field[ISE_EVENT_TYPE]->store(STRING_WITH_LEN("ONE TIME"), scs); + et.time_zone->gmt_sec_to_TIME(&time, et.execute_at); sch_table->field[ISE_EXECUTE_AT]->set_notnull(); sch_table->field[ISE_EXECUTE_AT]-> - store_time(&et.execute_at, MYSQL_TIMESTAMP_DATETIME); + store_time(&time, MYSQL_TIMESTAMP_DATETIME); } /* status */ @@ -4407,7 +4416,6 @@ copy_event_to_schema_table(THD *thd, TABLE *sch_table, TABLE *event_table) sch_table->field[ISE_ON_COMPLETION]-> store(STRING_WITH_LEN("PRESERVE"), scs); - int not_used=0; number_to_datetime(et.created, &time, 0, ¬_used); DBUG_ASSERT(not_used==0); sch_table->field[ISE_CREATED]->store_time(&time, MYSQL_TIMESTAMP_DATETIME); @@ -4417,11 +4425,12 @@ copy_event_to_schema_table(THD *thd, TABLE *sch_table, TABLE *event_table) sch_table->field[ISE_LAST_ALTERED]-> store_time(&time, MYSQL_TIMESTAMP_DATETIME); - if (et.last_executed.year) + if (et.last_executed) { + et.time_zone->gmt_sec_to_TIME(&time, et.last_executed); sch_table->field[ISE_LAST_EXECUTED]->set_notnull(); sch_table->field[ISE_LAST_EXECUTED]-> - store_time(&et.last_executed, MYSQL_TIMESTAMP_DATETIME); + store_time(&time, MYSQL_TIMESTAMP_DATETIME); } sch_table->field[ISE_EVENT_COMMENT]-> @@ -5427,6 +5436,7 @@ ST_FIELD_INFO events_fields_info[]= {"EVENT_SCHEMA", NAME_LEN, MYSQL_TYPE_STRING, 0, 0, "Db"}, {"EVENT_NAME", NAME_LEN, MYSQL_TYPE_STRING, 0, 0, "Name"}, {"DEFINER", 77, MYSQL_TYPE_STRING, 0, 0, "Definer"}, + {"TIME_ZONE", 64, MYSQL_TYPE_STRING, 0, 0, "Time zone"}, {"EVENT_BODY", 8, MYSQL_TYPE_STRING, 0, 0, 0}, {"EVENT_DEFINITION", 65535, MYSQL_TYPE_STRING, 0, 0, 0}, {"EVENT_TYPE", 9, MYSQL_TYPE_STRING, 0, 0, "Type"}, |