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

The problem was that local times specified by the user in AT, STARTS
and ENDS of CREATE EVENT/ALTER EVENT statement were converted to UTC,
and the original time zone was forgotten.  This way, event scheduler
couldn't honor Daylight Saving Time shifts, and times shown to the
user were also in UTC.  Additionally, CREATE EVENT didn't allow times
in the past, thus preventing straightforward event restoration from
old backups.

This patch reworks event scheduler time computations, performing them
in the time zone associated with the event.  Also it allows times to
be in the past.

The patch adds time_zone column to mysql.event table.

NOTE: The patch is almost final, but the bug#9953 should be pushed
first.


client/mysqldump.c:
  Before every CREATE EVENT, output its time zone.
mysql-test/include/wait_condition.inc:
  Add optional $wait_timeout parameter.
mysql-test/lib/init_db.sql:
  Add time_zone column.
mysql-test/r/events.result:
  Update result.
mysql-test/r/events_bugs.result:
  Update result.
mysql-test/r/events_grant.result:
  Update result.
mysql-test/r/events_restart_phase1.result:
  Update result.
mysql-test/r/events_scheduling.result:
  Update result.
mysql-test/r/mysqldump.result:
  Update result.
mysql-test/r/ps.result:
  Update result.
mysql-test/r/system_mysql_db.result:
  Update result.
mysql-test/t/events.test:
  Remove STARTS from the result, as it depends on current time.
mysql-test/t/events_bugs.test:
  Time in the past is no longer an error.
mysql-test/t/events_restart_phase1.test:
  Fill new column 'time_zone' in mysql.event.
mysql-test/t/events_scheduling.test:
  Cleanup: disable event scheduler.
scripts/mysql_create_system_tables.sh:
  Add new column 'time_zone' to mysql.event.
scripts/mysql_fix_privilege_tables.sql:
  Add new column 'time_zone' to mysql.event.
sql/event_data_objects.cc:
  The essence of the change is the following:
   - for internal times use my_time_t instead of TIME.  Assignment and
     comparison is done now on plain numbers.
   - in init_execute_at(), init_starts(), init_ends() convert given time
     to number of seconds since Epoch (aka Unix time, in UTC).
   - handle time_zone field loading and storing.
   - in get_next_time(), Unix time is converted back to event time zone,
     interval is added, and the result is converted to UTC again.
   - fix Event_timed::get_create_event() to report STARTS and ENDS.
   - before executing the event body we set thread time zone to the
     event time zone.
sql/event_data_objects.h:
  Add time_zone member to Event_basic class.
  
  Store internal times in my_time_t (number of seconds since Epoch),
  rather than in broken down TIME structure.
sql/event_db_repository.cc:
  Add time_zone column handling.
  
  Give a warning and do not create an event if its execution time is in
  the past, and ON COMPLETION NOT PRESERVE is set, because such an event
  should be dropped by that time.  Also, do not allow ALTER EVENT to
  set execution time in the past when ON COMPLETION NOT PRESERVE is set.
sql/event_db_repository.h:
  Add enum member for new time zone column.
sql/event_queue.cc:
  Replace handling of broken down times with simple handling of
  my_time_t.
sql/event_queue.h:
  Store internal times in my_time_t (number of seconds since Epoch),
  rather than in broken down TIME structure.
sql/event_scheduler.cc:
  Add TODO comment.
sql/events.cc:
  Send time_zone column for SHOW CREATE EVENT.
sql/share/errmsg.txt:
  Update error message, and add two more errors.
sql/sql_show.cc:
  Add TIME_ZONE column to the output of SHOW EVENTS.
mysql-test/r/events_time_zone.result:
  BitKeeper file /home/tomash/src/mysql_ab/mysql-5.1-wl3698/mysql-test/r/events_time_zone.result
mysql-test/t/events_time_zone.test:
  BitKeeper file /home/tomash/src/mysql_ab/mysql-5.1-wl3698/mysql-test/t/events_time_zone.test

16 files changed, 896 insertions, 93 deletions

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.