Edit File: events_time_zone.result
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.
Back to File Manager