Debian Codegraph @

mysql-test/suite/events/events_time_zone.test

raw
1 # 1. This test case is sensitive to execution timing. You may control
2 # this sensitivity by the parameter below. Small values will result
3 # in fast but more unstable execution, large values will improve
4 # stability at the cost of speed. Basically, N is a number of seconds
5 # to wait for operation to complete. Should be positive. Test runs
6 # about 25*N seconds (it sleeps most of the time, so CPU speed is not
7 # relevant).
8 let $N = 6;
9 #
10 # 2. Some subtests
11 # - create a new time zone
12 # - run some statements
13 # - delete the new time zone.
14 # But the time zone name used gets somewhere cached and it cannot be
15 # "reused" later in the same or another session for a new time zone.
16 # Experiments (2008-11 MySQL 5.1) showed that none of the available
17 # RESET/FLUSH commands removes these entries.
18 # 2008-11 MySQL 5.1 Bug#39979 main.events_time_zone does not clean up
19 # second bad effect
20 # Therefore we compute unique and unusual timezone names to minimize
21 # the likelihood that a later test uses the same name.
22 #
23 # 3. The subtests mentioned in 2. cause that the AUTO_INCREMENT value
24 # within "SHOW CREATE TABLE mysql.timezone" differ from the initial one.
25 # (Bug#39979 main.events_time_zone does not clean up)
26 # Therefore we reset this value after each of these subtests.
27 #
28 # Note(mleich):
29 # There is a significant likelihood that future improvements of the server
30 # cause that the solutions for the issues mentioned in 2. and 3. will no
31 # more work.
32 # A mysql-test-run.pl feature which allows to enforce
33 # 1. Server shutdown (-> Problem mentioned in 2. disappears)
34 # 2. Reset all data to initial state (-> Problem mentioned in 3. disappears)
35 # 3. Server start
36 # after a tests would be a perfect replacement.
37 #
38
39 # Can't test with embedded server that doesn't support grants
40 -- source include/not_embedded.inc
41
42 --source include/big_test.inc
43
44
45 --disable_warnings
46 DROP DATABASE IF EXISTS mysqltest_db1;
47 --enable_warnings
48
49 CREATE DATABASE mysqltest_db1;
50
51 let $old_db= `SELECT DATABASE()`;
52 USE mysqltest_db1;
53
54 SET GLOBAL EVENT_SCHEDULER= OFF;
55 SET @save_time_zone= @@TIME_ZONE;
56
57
58 #
59 # BUG#16420: Events: timestamps become UTC
60 # BUG#26429: SHOW CREATE EVENT is incorrect for an event that
61 # STARTS NOW()
62 # BUG#26431: Impossible to re-create an event from backup if its
63 # STARTS clause is in the past
64 # WL#3698: Events: execution in local time zone
65 #
66
67 #----------------------------------------------------------------------
68
69 # Create rounding function.
70
71 # Disable query log to hide actual value of $N.
72 --disable_query_log
73 eval SET @step= $N;
74 --enable_query_log
75
76 # Since we are working in a separate database, we may use any names we
77 # like.
78 CREATE TABLE t_step (step INT);
79 INSERT INTO t_step VALUES (@step);
80
81 # We can't use @variables in function, because it will be called from
82 # the event thread, and 'eval' doesn't work for multi-statements, so
83 # we can't interpolate $variables either, hence we fetch the step
84 # value from the table.
85 delimiter //;
86 CREATE FUNCTION round_to_step(i INT, n INT) RETURNS INT
87 BEGIN
88 DECLARE step INT;
89
90 SELECT * INTO step FROM t_step;
91
92 # We add 0.1 as a protection from inexact division.
93 RETURN FLOOR((i % (step * n) + 0.1) / step);
94 END//
95 delimiter ;//
96
97
98 # Test time computations wrt Daylight Saving Time shifts. We also
99 # test here that the event operates in its time zone (see what NOW()
100 # returns).
101 #
102
103 # Create a fake time zone with time transitions every 3*$N second.
104
105 SET @step3= @step * 3;
106 SET @step6= @step * 6;
107
108 SET @unix_time= UNIX_TIMESTAMP() - 1;
109 SET @unix_time= @unix_time - @unix_time % @step6;
110
111 INSERT INTO mysql.time_zone VALUES (NULL, 'N');
112 SET @tzid= LAST_INSERT_ID();
113 INSERT INTO mysql.time_zone_transition_type
114 VALUES (@tzid, 0, 0, 0, 'b16420_0');
115 INSERT INTO mysql.time_zone_transition_type
116 VALUES (@tzid, 1, @step3 - @step, 1, 'b16420_1');
117
118 let $transition_unix_time= `SELECT @unix_time`;
119 let $count= 30;
120 --disable_query_log
121 begin;
122 while ($count)
123 {
124 eval INSERT INTO mysql.time_zone_transition
125 VALUES (@tzid, $transition_unix_time,
126 $transition_unix_time % @step6 = 0);
127 let $transition_unix_time= `SELECT $transition_unix_time + @step3`;
128 dec $count;
129 }
130 commit;
131 --enable_query_log
132 let $tz_name = `SELECT CONCAT('b16420_a',UNIX_TIMESTAMP())`;
133 --replace_result $tz_name <TZ_NAME_1>
134 eval INSERT INTO mysql.time_zone_name VALUES ('$tz_name', @tzid);
135
136 CREATE TABLE t1 (count INT, unix_time INT, local_time INT, comment CHAR(80));
137 CREATE TABLE t2 (count INT);
138 INSERT INTO t2 VALUES (1);
139
140 delimiter //;
141 CREATE FUNCTION f1(comment CHAR(80)) RETURNS INT
142 BEGIN
143 DECLARE orig_tz CHAR(64);
144 DECLARE unix_time INT;
145 DECLARE local_now DATETIME;
146 DECLARE utc_now DATETIME;
147 DECLARE local_time INT;
148
149 SET unix_time= UNIX_TIMESTAMP();
150 SET local_now= FROM_UNIXTIME(unix_time);
151 SET orig_tz= @@TIME_ZONE;
152 SET TIME_ZONE = '+00:00';
153 SET utc_now= FROM_UNIXTIME(unix_time);
154 SET TIME_ZONE= orig_tz;
155 SET local_time = unix_time + TIMESTAMPDIFF(SECOND, utc_now, local_now);
156
157 SET unix_time= round_to_step(unix_time, 6);
158 SET local_time= round_to_step(local_time, 6);
159
160 INSERT INTO t1 VALUES ((SELECT count FROM t2),
161 unix_time, local_time, comment);
162 RETURN 0;
163 END//
164 delimiter ;//
165
166 SET TIME_ZONE= '+00:00';
167 CREATE EVENT e1 ON SCHEDULE EVERY @step SECOND
168 STARTS FROM_UNIXTIME(@unix_time) DO SELECT f1("<e1>");
169
170 --replace_result $tz_name <TZ_NAME_1>
171 eval SET TIME_ZONE= '$tz_name';
172 CREATE EVENT e2 ON SCHEDULE EVERY @step SECOND
173 STARTS FROM_UNIXTIME(@unix_time) DO SELECT f1("<e2>");
174
175 # We want to start at the beginning of the DST cycle, so we wait
176 # untill current time divides by @step6.
177 let $wait_timeout= `SELECT @step6*2 + 1`;
178 let $wait_condition= SELECT UNIX_TIMESTAMP() % @step6 = @step6 - 1;
179 --source include/wait_condition.inc
180 # The second wait is needed because after the first wait we may end up
181 # on the ending edge of a second. Second wait will bring us to the
182 # beginning edge.
183 let $wait_timeout= `SELECT @step*2 + 1`;
184 let $wait_condition= SELECT UNIX_TIMESTAMP() % @step6 = 0;
185 --source include/wait_condition.inc
186
187 # Note that after the scheduler is enabled, the event will be
188 # scheduled only for the next second.
189 SET GLOBAL EVENT_SCHEDULER= ON;
190
191 # We want to run after the events are executed.
192 SELECT SLEEP(@step / 2);
193
194 let $count= 7;
195 --disable_query_log
196 --disable_result_log
197 while ($count)
198 {
199 SELECT SLEEP(@step);
200
201 eval SELECT CASE $count
202 WHEN 5 THEN f1(CONCAT("Second pass after backward -2 step shift,",
203 " e2 should not be executed"))
204 WHEN 4 THEN f1(CONCAT("Second pass after backward -2 step shift,",
205 " e2 should not be executed"))
206 WHEN 2 THEN f1(CONCAT("Forward +2 step shift, local 0, 1 are skipped,",
207 " e2 should be executed"))
208 ELSE f1("e2 should be executed")
209 END;
210 UPDATE t2 SET count= count + 1;
211
212 dec $count;
213 }
214 --enable_result_log
215 --enable_query_log
216
217 SET GLOBAL EVENT_SCHEDULER= OFF;
218
219 SELECT * FROM t1 ORDER BY count, comment;
220
221 SET TIME_ZONE= @save_time_zone;
222
223 DROP EVENT e2;
224 DROP EVENT e1;
225 DROP FUNCTION f1;
226 DROP TABLE t1, t2;
227
228 DELETE FROM mysql.time_zone_name WHERE time_zone_id = @tzid;
229 DELETE FROM mysql.time_zone_transition_type WHERE time_zone_id = @tzid;
230 DELETE FROM mysql.time_zone_transition WHERE time_zone_id = @tzid;
231 DELETE FROM mysql.time_zone WHERE time_zone_id = @tzid;
232 let $time_zone_auto_inc = `SELECT MAX(Time_zone_id) + 1 FROM mysql.time_zone`;
233 eval ALTER TABLE mysql.time_zone AUTO_INCREMENT = $time_zone_auto_inc;
234
235 #----------------------------------------------------------------------
236
237 # Test MONTH interval.
238 #
239
240 SET TIME_ZONE= '+00:00';
241
242 CREATE TABLE t1 (event CHAR(2), dt DATE, offset INT);
243
244 INSERT INTO mysql.time_zone VALUES (NULL, 'N');
245 SET @tzid= LAST_INSERT_ID();
246
247 SET @now= UNIX_TIMESTAMP();
248 SET @offset_month_01= UNIX_TIMESTAMP('2030-01-31 12:00:00') - @now;
249 SET @offset_month_02= UNIX_TIMESTAMP('2030-02-28 12:00:00') - @now - 5*@step;
250 SET @offset_month_03= UNIX_TIMESTAMP('2030-03-31 12:00:00') - @now - 5*@step;
251 SET @offset_month_04= UNIX_TIMESTAMP('2030-04-30 12:00:00') - @now - 13*@step;
252
253 INSERT INTO mysql.time_zone_transition_type
254 VALUES (@tzid, 0, @offset_month_01, 0, 'b16420_0');
255 INSERT INTO mysql.time_zone_transition_type
256 VALUES (@tzid, 1, @offset_month_02, 1, 'b16420_1');
257 INSERT INTO mysql.time_zone_transition_type
258 VALUES (@tzid, 2, @offset_month_03, 1, 'b16420_2');
259 INSERT INTO mysql.time_zone_transition_type
260 VALUES (@tzid, 3, @offset_month_04, 1, 'b16420_3');
261 INSERT INTO mysql.time_zone_transition
262 VALUES (@tzid, @now, 0);
263 INSERT INTO mysql.time_zone_transition
264 VALUES (@tzid, @now + 3 * @step, 1);
265 INSERT INTO mysql.time_zone_transition
266 VALUES (@tzid, @now + 7 * @step, 2);
267 INSERT INTO mysql.time_zone_transition
268 VALUES (@tzid, @now + 12 * @step, 3);
269 let $tz_name = `SELECT CONCAT('b16420_b',UNIX_TIMESTAMP())`;
270 --replace_result $tz_name <TZ_NAME_2>
271 eval INSERT INTO mysql.time_zone_name VALUES ('$tz_name', @tzid);
272
273 --replace_result $tz_name <TZ_NAME_2>
274 eval SET TIME_ZONE= '$tz_name';
275
276 SET GLOBAL EVENT_SCHEDULER= ON;
277
278 let $now= `SELECT @now`;
279 --disable_query_log
280 eval CREATE EVENT e1 ON SCHEDULE EVERY 1 MONTH
281 STARTS FROM_UNIXTIME($now - @step) DO
282 INSERT INTO t1 VALUES
283 ("e1", NOW(), round_to_step(UNIX_TIMESTAMP() - $now, 4) - 1);
284 eval CREATE EVENT e2 ON SCHEDULE EVERY 1 MONTH
285 STARTS FROM_UNIXTIME($now + @step) DO
286 INSERT INTO t1 VALUES
287 ("e2", NOW(), round_to_step(UNIX_TIMESTAMP() - $now, 4) - 1);
288 --enable_query_log
289
290 let $wait_timeout= `SELECT 16 * @step`;
291 let $wait_condition= SELECT COUNT(*) = 7 FROM t1;
292 --source include/wait_condition.inc
293
294 SET GLOBAL EVENT_SCHEDULER= OFF;
295
296 --echo Below we should see the following:
297 --echo - On Jan 31 only e2 is executed, because we started later than
298 --echo e1 should have been executed. Offset of e2 is 0 because of
299 --echo the late start, not 1.
300 --echo - The next execution is on Feb 28 (last day of Feb). Both events
301 --echo are executed in their times, offsets are -1 and 1.
302 --echo - The next time is Mar 31. Because the time of event
303 --echo execution was skipped over, events are executed right away,
304 --echo offsets are 2 and 2.
305 --echo - The next time is Apr 30. Events are again executed in their
306 --echo appointed times, offsets are -1 and 1.
307 SELECT * FROM t1 ORDER BY dt, event;
308
309 DROP EVENT e2;
310 DROP EVENT e1;
311 DROP TABLE t1;
312
313 SET TIME_ZONE= @save_time_zone;
314
315 DELETE FROM mysql.time_zone_name WHERE time_zone_id = @tzid;
316 DELETE FROM mysql.time_zone_transition_type WHERE time_zone_id = @tzid;
317 DELETE FROM mysql.time_zone_transition WHERE time_zone_id = @tzid;
318 DELETE FROM mysql.time_zone WHERE time_zone_id = @tzid;
319 let $time_zone_auto_inc = `SELECT MAX(Time_zone_id) + 1 FROM mysql.time_zone`;
320 eval ALTER TABLE mysql.time_zone AUTO_INCREMENT = $time_zone_auto_inc;
321
322 DROP FUNCTION round_to_step;
323 DROP TABLE t_step;
324
325
326 DROP DATABASE mysqltest_db1;
327 --disable_query_log
328 eval USE $old_db;
329
330 --enable_query_log
331 let $wait_condition=
332 SELECT COUNT(*) = 0 FROM information_schema.processlist
333 WHERE db='mysqltest_db1' AND command = 'Connect' AND user=current_user();
334 --source include/wait_condition.inc
335
336 --echo End of 5.1 tests.