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

1 files changed, 463 insertions, 0 deletions

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.