Debian Codegraph @

mysql-test/suite/events/events_time_zone.result

raw
1 DROP DATABASE IF EXISTS mysqltest_db1;
2 CREATE DATABASE mysqltest_db1;
3 USE mysqltest_db1;
4 SET GLOBAL EVENT_SCHEDULER= OFF;
5 SET @save_time_zone= @@TIME_ZONE;
6 CREATE TABLE t_step (step INT);
7 INSERT INTO t_step VALUES (@step);
8 CREATE FUNCTION round_to_step(i INT, n INT) RETURNS INT
9 BEGIN
10 DECLARE step INT;
11 SELECT * INTO step FROM t_step;
12 # We add 0.1 as a protection from inexact division.
13 RETURN FLOOR((i % (step * n) + 0.1) / step);
14 END//
15 SET @step3= @step * 3;
16 SET @step6= @step * 6;
17 SET @unix_time= UNIX_TIMESTAMP() - 1;
18 SET @unix_time= @unix_time - @unix_time % @step6;
19 INSERT INTO mysql.time_zone VALUES (NULL, 'N');
20 SET @tzid= LAST_INSERT_ID();
21 INSERT INTO mysql.time_zone_transition_type
22 VALUES (@tzid, 0, 0, 0, 'b16420_0');
23 INSERT INTO mysql.time_zone_transition_type
24 VALUES (@tzid, 1, @step3 - @step, 1, 'b16420_1');
25 INSERT INTO mysql.time_zone_name VALUES ('<TZ_NAME_1>', @tzid);
26 CREATE TABLE t1 (count INT, unix_time INT, local_time INT, comment CHAR(80));
27 CREATE TABLE t2 (count INT);
28 INSERT INTO t2 VALUES (1);
29 CREATE FUNCTION f1(comment CHAR(80)) RETURNS INT
30 BEGIN
31 DECLARE orig_tz CHAR(64);
32 DECLARE unix_time INT;
33 DECLARE local_now DATETIME;
34 DECLARE utc_now DATETIME;
35 DECLARE local_time INT;
36 SET unix_time= UNIX_TIMESTAMP();
37 SET local_now= FROM_UNIXTIME(unix_time);
38 SET orig_tz= @@TIME_ZONE;
39 SET TIME_ZONE = '+00:00';
40 SET utc_now= FROM_UNIXTIME(unix_time);
41 SET TIME_ZONE= orig_tz;
42 SET local_time = unix_time + TIMESTAMPDIFF(SECOND, utc_now, local_now);
43 SET unix_time= round_to_step(unix_time, 6);
44 SET local_time= round_to_step(local_time, 6);
45 INSERT INTO t1 VALUES ((SELECT count FROM t2),
46 unix_time, local_time, comment);
47 RETURN 0;
48 END//
49 SET TIME_ZONE= '+00:00';
50 CREATE EVENT e1 ON SCHEDULE EVERY @step SECOND
51 STARTS FROM_UNIXTIME(@unix_time) DO SELECT f1("<e1>");
52 SET TIME_ZONE= '<TZ_NAME_1>';
53 CREATE EVENT e2 ON SCHEDULE EVERY @step SECOND
54 STARTS FROM_UNIXTIME(@unix_time) DO SELECT f1("<e2>");
55 SET GLOBAL EVENT_SCHEDULER= ON;
56 SELECT SLEEP(@step / 2);
57 SLEEP(@step / 2)
58 0
59 SET GLOBAL EVENT_SCHEDULER= OFF;
60 SELECT * FROM t1 ORDER BY count, comment;
61 count unix_time local_time comment
62 1 1 1 <e1>
63 1 1 3 <e2>
64 1 1 3 e2 should be executed
65 2 2 2 <e1>
66 2 2 4 <e2>
67 2 2 4 e2 should be executed
68 3 3 3 <e1>
69 3 3 3 Second pass after backward -2 step shift, e2 should not be executed
70 4 4 4 <e1>
71 4 4 4 Second pass after backward -2 step shift, e2 should not be executed
72 5 5 5 <e1>
73 5 5 5 <e2>
74 5 5 5 e2 should be executed
75 6 0 0 <e1>
76 6 0 2 <e2>
77 6 0 2 Forward +2 step shift, local 0, 1 are skipped, e2 should be executed
78 7 1 1 <e1>
79 7 1 3 <e2>
80 7 1 3 e2 should be executed
81 SET TIME_ZONE= @save_time_zone;
82 DROP EVENT e2;
83 DROP EVENT e1;
84 DROP FUNCTION f1;
85 DROP TABLE t1, t2;
86 DELETE FROM mysql.time_zone_name WHERE time_zone_id = @tzid;
87 DELETE FROM mysql.time_zone_transition_type WHERE time_zone_id = @tzid;
88 DELETE FROM mysql.time_zone_transition WHERE time_zone_id = @tzid;
89 DELETE FROM mysql.time_zone WHERE time_zone_id = @tzid;
90 ALTER TABLE mysql.time_zone AUTO_INCREMENT = 6;
91 SET TIME_ZONE= '+00:00';
92 CREATE TABLE t1 (event CHAR(2), dt DATE, offset INT);
93 INSERT INTO mysql.time_zone VALUES (NULL, 'N');
94 SET @tzid= LAST_INSERT_ID();
95 SET @now= UNIX_TIMESTAMP();
96 SET @offset_month_01= UNIX_TIMESTAMP('2030-01-31 12:00:00') - @now;
97 SET @offset_month_02= UNIX_TIMESTAMP('2030-02-28 12:00:00') - @now - 5*@step;
98 SET @offset_month_03= UNIX_TIMESTAMP('2030-03-31 12:00:00') - @now - 5*@step;
99 SET @offset_month_04= UNIX_TIMESTAMP('2030-04-30 12:00:00') - @now - 13*@step;
100 INSERT INTO mysql.time_zone_transition_type
101 VALUES (@tzid, 0, @offset_month_01, 0, 'b16420_0');
102 INSERT INTO mysql.time_zone_transition_type
103 VALUES (@tzid, 1, @offset_month_02, 1, 'b16420_1');
104 INSERT INTO mysql.time_zone_transition_type
105 VALUES (@tzid, 2, @offset_month_03, 1, 'b16420_2');
106 INSERT INTO mysql.time_zone_transition_type
107 VALUES (@tzid, 3, @offset_month_04, 1, 'b16420_3');
108 INSERT INTO mysql.time_zone_transition
109 VALUES (@tzid, @now, 0);
110 INSERT INTO mysql.time_zone_transition
111 VALUES (@tzid, @now + 3 * @step, 1);
112 INSERT INTO mysql.time_zone_transition
113 VALUES (@tzid, @now + 7 * @step, 2);
114 INSERT INTO mysql.time_zone_transition
115 VALUES (@tzid, @now + 12 * @step, 3);
116 INSERT INTO mysql.time_zone_name VALUES ('<TZ_NAME_2>', @tzid);
117 SET TIME_ZONE= '<TZ_NAME_2>';
118 SET GLOBAL EVENT_SCHEDULER= ON;
119 SET GLOBAL EVENT_SCHEDULER= OFF;
120 Below we should see the following:
121 - On Jan 31 only e2 is executed, because we started later than
122 e1 should have been executed. Offset of e2 is 0 because of
123 the late start, not 1.
124 - The next execution is on Feb 28 (last day of Feb). Both events
125 are executed in their times, offsets are -1 and 1.
126 - The next time is Mar 31. Because the time of event
127 execution was skipped over, events are executed right away,
128 offsets are 2 and 2.
129 - The next time is Apr 30. Events are again executed in their
130 appointed times, offsets are -1 and 1.
131 SELECT * FROM t1 ORDER BY dt, event;
132 event dt offset
133 e2 2030-01-31 0
134 e1 2030-02-28 -1
135 e2 2030-02-28 1
136 e1 2030-03-31 2
137 e2 2030-03-31 2
138 e1 2030-04-30 -1
139 e2 2030-04-30 1
140 DROP EVENT e2;
141 DROP EVENT e1;
142 DROP TABLE t1;
143 SET TIME_ZONE= @save_time_zone;
144 DELETE FROM mysql.time_zone_name WHERE time_zone_id = @tzid;
145 DELETE FROM mysql.time_zone_transition_type WHERE time_zone_id = @tzid;
146 DELETE FROM mysql.time_zone_transition WHERE time_zone_id = @tzid;
147 DELETE FROM mysql.time_zone WHERE time_zone_id = @tzid;
148 ALTER TABLE mysql.time_zone AUTO_INCREMENT = 6;
149 DROP FUNCTION round_to_step;
150 DROP TABLE t_step;
151 DROP DATABASE mysqltest_db1;
152 End of 5.1 tests.