Create 'main' test directory and move 't' and 'r' there

1 files changed, 336 insertions, 0 deletions

diff --git a/mysql-test/main/events_time_zone.test b/mysql-test/main/events_time_zone.test
new file mode 100644
index 00000000000..5291eb96e6b
--- /dev/null
+++ b/mysql-test/main/events_time_zone.test
@@ -0,0 +1,336 @@
+# 1. 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 = 6;
+#
+# 2. Some subtests
+#    - create a new time zone
+#    - run some statements
+#    - delete the new time zone.
+#    But the time zone name used gets somewhere cached and it cannot be
+#    "reused" later in the same or another session for a new time zone.
+#    Experiments (2008-11 MySQL 5.1) showed that none of the available
+#    RESET/FLUSH commands removes these entries.
+#    2008-11 MySQL 5.1 Bug#39979 main.events_time_zone does not clean up
+#                      second bad effect
+#    Therefore we compute unique and unusual timezone names to minimize
+#    the likelihood that a later test uses the same name.
+#
+# 3. The subtests mentioned in 2. cause that the AUTO_INCREMENT value
+#    within "SHOW CREATE TABLE mysql.timezone" differ from the initial one.
+#    (Bug#39979 main.events_time_zone does not clean up)
+#    Therefore we reset this value after each of these subtests.
+#
+# Note(mleich):
+#    There is a significant likelihood that future improvements of the server
+#    cause that the solutions for the issues mentioned in 2. and 3. will no
+#    more work.
+#    A mysql-test-run.pl feature which allows to enforce
+#    1. Server shutdown (-> Problem mentioned in 2. disappears)
+#    2. Reset all data to initial state (-> Problem mentioned in 3. disappears)
+#    3. Server start
+#    after a tests would be a perfect replacement.
+#
+
+# Can't test with embedded server that doesn't support grants
+-- source include/not_embedded.inc
+
+--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;
+SET @save_time_zone= @@TIME_ZONE;
+
+
+#
+# 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
+#
+
+#----------------------------------------------------------------------
+
+# 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;
+
+SET @unix_time= UNIX_TIMESTAMP() - 1;
+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
+begin;
+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;
+}
+commit;
+--enable_query_log
+let $tz_name = `SELECT CONCAT('b16420_a',UNIX_TIMESTAMP())`;
+--replace_result $tz_name <TZ_NAME_1>
+eval INSERT INTO mysql.time_zone_name VALUES ('$tz_name', @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>");
+
+--replace_result $tz_name <TZ_NAME_1>
+eval SET TIME_ZONE= '$tz_name';
+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*2 + 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*2 + 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;
+let $time_zone_auto_inc = `SELECT MAX(Time_zone_id) + 1 FROM mysql.time_zone`;
+eval ALTER TABLE mysql.time_zone AUTO_INCREMENT = $time_zone_auto_inc;
+
+#----------------------------------------------------------------------
+
+# 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);
+let $tz_name = `SELECT CONCAT('b16420_b',UNIX_TIMESTAMP())`;
+--replace_result $tz_name <TZ_NAME_2>
+eval INSERT INTO mysql.time_zone_name VALUES ('$tz_name', @tzid);
+
+--replace_result $tz_name <TZ_NAME_2>
+eval SET TIME_ZONE= '$tz_name';
+
+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;
+let $time_zone_auto_inc = `SELECT MAX(Time_zone_id) + 1 FROM mysql.time_zone`;
+eval ALTER TABLE mysql.time_zone AUTO_INCREMENT = $time_zone_auto_inc;
+
+DROP FUNCTION round_to_step;
+DROP TABLE t_step;
+
+
+DROP DATABASE mysqltest_db1;
+--disable_query_log
+eval USE $old_db;
+
+--enable_query_log
+let $wait_condition=
+  SELECT COUNT(*) = 0 FROM information_schema.processlist
+  WHERE db='mysqltest_db1' AND command = 'Connect' AND user=current_user();
+--source include/wait_condition.inc
+
+--echo End of 5.1 tests.