diff options
Diffstat (limited to 'mysql-test/main/events_time_zone.result')
| -rw-r--r-- | mysql-test/main/events_time_zone.result | 152 |
1 files changed, 152 insertions, 0 deletions
diff --git a/mysql-test/main/events_time_zone.result b/mysql-test/main/events_time_zone.result new file mode 100644 index 00000000000..155a9d155af --- /dev/null +++ b/mysql-test/main/events_time_zone.result @@ -0,0 +1,152 @@ +DROP DATABASE IF EXISTS mysqltest_db1; +CREATE DATABASE mysqltest_db1; +USE mysqltest_db1; +SET GLOBAL EVENT_SCHEDULER= OFF; +SET @save_time_zone= @@TIME_ZONE; +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_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'); +INSERT INTO mysql.time_zone_name VALUES ('<TZ_NAME_1>', @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= '<TZ_NAME_1>'; +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; +ALTER TABLE mysql.time_zone AUTO_INCREMENT = 6; +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 ('<TZ_NAME_2>', @tzid); +SET TIME_ZONE= '<TZ_NAME_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; +ALTER TABLE mysql.time_zone AUTO_INCREMENT = 6; +DROP FUNCTION round_to_step; +DROP TABLE t_step; +DROP DATABASE mysqltest_db1; +End of 5.1 tests. |