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
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
20
# Therefore we compute unique and unusual timezone names to minimize
21
# the likelihood that a later test uses the same name.
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.
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
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)
36
# after a tests would be a perfect replacement.
39
--source include/big_test.inc
43
DROP DATABASE IF EXISTS mysqltest_db1;
46
CREATE DATABASE mysqltest_db1;
48
let $old_db= `SELECT DATABASE()`;
51
SET GLOBAL EVENT_SCHEDULER= OFF;
52
SET @save_time_zone= @@TIME_ZONE;
56
# BUG#16420: Events: timestamps become UTC
57
# BUG#26429: SHOW CREATE EVENT is incorrect for an event that
59
# BUG#26431: Impossible to re-create an event from backup if its
60
# STARTS clause is in the past
61
# WL#3698: Events: execution in local time zone
64
#----------------------------------------------------------------------
66
# Create rounding function.
68
# Disable query log to hide actual value of $N.
73
# Since we are working in a separate database, we may use any names we
75
CREATE TABLE t_step (step INT);
76
INSERT INTO t_step VALUES (@step);
78
# We can't use @variables in function, because it will be called from
79
# the event thread, and 'eval' doesn't work for multi-statements, so
80
# we can't interpolate $variables either, hence we fetch the step
81
# value from the table.
83
CREATE FUNCTION round_to_step(i INT, n INT) RETURNS INT
87
SELECT * INTO step FROM t_step;
89
# We add 0.1 as a protection from inexact division.
90
RETURN FLOOR((i % (step * n) + 0.1) / step);
95
# Test time computations wrt Daylight Saving Time shifts. We also
96
# test here that the event operates in its time zone (see what NOW()
100
# Create a fake time zone with time transitions every 3*$N second.
102
SET @step3= @step * 3;
103
SET @step6= @step * 6;
105
SET @unix_time= UNIX_TIMESTAMP() - 1;
106
SET @unix_time= @unix_time - @unix_time % @step6;
108
INSERT INTO mysql.time_zone VALUES (NULL, 'N');
109
SET @tzid= LAST_INSERT_ID();
110
INSERT INTO mysql.time_zone_transition_type
111
VALUES (@tzid, 0, 0, 0, 'b16420_0');
112
INSERT INTO mysql.time_zone_transition_type
113
VALUES (@tzid, 1, @step3 - @step, 1, 'b16420_1');
115
let $transition_unix_time= `SELECT @unix_time`;
120
eval INSERT INTO mysql.time_zone_transition
121
VALUES (@tzid, $transition_unix_time,
122
$transition_unix_time % @step6 = 0);
123
let $transition_unix_time= `SELECT $transition_unix_time + @step3`;
127
let $tz_name = `SELECT CONCAT('b16420_a',UNIX_TIMESTAMP())`;
128
--replace_result $tz_name <TZ_NAME_1>
129
eval INSERT INTO mysql.time_zone_name VALUES ('$tz_name', @tzid);
131
CREATE TABLE t1 (count INT, unix_time INT, local_time INT, comment CHAR(80));
132
CREATE TABLE t2 (count INT);
133
INSERT INTO t2 VALUES (1);
136
CREATE FUNCTION f1(comment CHAR(80)) RETURNS INT
138
DECLARE orig_tz CHAR(64);
139
DECLARE unix_time INT;
140
DECLARE local_now DATETIME;
141
DECLARE utc_now DATETIME;
142
DECLARE local_time INT;
144
SET unix_time= UNIX_TIMESTAMP();
145
SET local_now= FROM_UNIXTIME(unix_time);
146
SET orig_tz= @@TIME_ZONE;
147
SET TIME_ZONE = '+00:00';
148
SET utc_now= FROM_UNIXTIME(unix_time);
149
SET TIME_ZONE= orig_tz;
150
SET local_time = unix_time + TIMESTAMPDIFF(SECOND, utc_now, local_now);
152
SET unix_time= round_to_step(unix_time, 6);
153
SET local_time= round_to_step(local_time, 6);
155
INSERT INTO t1 VALUES ((SELECT count FROM t2),
156
unix_time, local_time, comment);
161
SET TIME_ZONE= '+00:00';
162
CREATE EVENT e1 ON SCHEDULE EVERY @step SECOND
163
STARTS FROM_UNIXTIME(@unix_time) DO SELECT f1("<e1>");
165
--replace_result $tz_name <TZ_NAME_1>
166
eval SET TIME_ZONE= '$tz_name';
167
CREATE EVENT e2 ON SCHEDULE EVERY @step SECOND
168
STARTS FROM_UNIXTIME(@unix_time) DO SELECT f1("<e2>");
170
# We want to start at the beginning of the DST cycle, so we wait
171
# untill current time divides by @step6.
172
let $wait_timeout= `SELECT @step6 + 1`;
173
let $wait_condition= SELECT UNIX_TIMESTAMP() % @step6 = @step6 - 1;
174
--source include/wait_condition.inc
175
# The second wait is needed because after the first wait we may end up
176
# on the ending edge of a second. Second wait will bring us to the
178
let $wait_timeout= `SELECT @step + 1`;
179
let $wait_condition= SELECT UNIX_TIMESTAMP() % @step6 = 0;
180
--source include/wait_condition.inc
182
# Note that after the scheduler is enabled, the event will be
183
# scheduled only for the next second.
184
SET GLOBAL EVENT_SCHEDULER= ON;
186
# We want to run after the events are executed.
187
SELECT SLEEP(@step / 2);
196
eval SELECT CASE $count
197
WHEN 5 THEN f1(CONCAT("Second pass after backward -2 step shift,",
198
" e2 should not be executed"))
199
WHEN 4 THEN f1(CONCAT("Second pass after backward -2 step shift,",
200
" e2 should not be executed"))
201
WHEN 2 THEN f1(CONCAT("Forward +2 step shift, local 0, 1 are skipped,",
202
" e2 should be executed"))
203
ELSE f1("e2 should be executed")
205
UPDATE t2 SET count= count + 1;
212
SET GLOBAL EVENT_SCHEDULER= OFF;
214
SELECT * FROM t1 ORDER BY count, comment;
216
SET TIME_ZONE= @save_time_zone;
223
DELETE FROM mysql.time_zone_name WHERE time_zone_id = @tzid;
224
DELETE FROM mysql.time_zone_transition_type WHERE time_zone_id = @tzid;
225
DELETE FROM mysql.time_zone_transition WHERE time_zone_id = @tzid;
226
DELETE FROM mysql.time_zone WHERE time_zone_id = @tzid;
227
let $time_zone_auto_inc = `SELECT MAX(Time_zone_id) + 1 FROM mysql.time_zone`;
228
eval ALTER TABLE mysql.time_zone AUTO_INCREMENT = $time_zone_auto_inc;
230
#----------------------------------------------------------------------
232
# Test MONTH interval.
235
SET TIME_ZONE= '+00:00';
237
CREATE TABLE t1 (event CHAR(2), dt DATE, offset INT);
239
INSERT INTO mysql.time_zone VALUES (NULL, 'N');
240
SET @tzid= LAST_INSERT_ID();
242
SET @now= UNIX_TIMESTAMP();
243
SET @offset_month_01= UNIX_TIMESTAMP('2030-01-31 12:00:00') - @now;
244
SET @offset_month_02= UNIX_TIMESTAMP('2030-02-28 12:00:00') - @now - 5*@step;
245
SET @offset_month_03= UNIX_TIMESTAMP('2030-03-31 12:00:00') - @now - 5*@step;
246
SET @offset_month_04= UNIX_TIMESTAMP('2030-04-30 12:00:00') - @now - 13*@step;
248
INSERT INTO mysql.time_zone_transition_type
249
VALUES (@tzid, 0, @offset_month_01, 0, 'b16420_0');
250
INSERT INTO mysql.time_zone_transition_type
251
VALUES (@tzid, 1, @offset_month_02, 1, 'b16420_1');
252
INSERT INTO mysql.time_zone_transition_type
253
VALUES (@tzid, 2, @offset_month_03, 1, 'b16420_2');
254
INSERT INTO mysql.time_zone_transition_type
255
VALUES (@tzid, 3, @offset_month_04, 1, 'b16420_3');
256
INSERT INTO mysql.time_zone_transition
257
VALUES (@tzid, @now, 0);
258
INSERT INTO mysql.time_zone_transition
259
VALUES (@tzid, @now + 3 * @step, 1);
260
INSERT INTO mysql.time_zone_transition
261
VALUES (@tzid, @now + 7 * @step, 2);
262
INSERT INTO mysql.time_zone_transition
263
VALUES (@tzid, @now + 12 * @step, 3);
264
let $tz_name = `SELECT CONCAT('b16420_b',UNIX_TIMESTAMP())`;
265
--replace_result $tz_name <TZ_NAME_2>
266
eval INSERT INTO mysql.time_zone_name VALUES ('$tz_name', @tzid);
268
--replace_result $tz_name <TZ_NAME_2>
269
eval SET TIME_ZONE= '$tz_name';
271
SET GLOBAL EVENT_SCHEDULER= ON;
273
let $now= `SELECT @now`;
275
eval CREATE EVENT e1 ON SCHEDULE EVERY 1 MONTH
276
STARTS FROM_UNIXTIME($now - @step) DO
277
INSERT INTO t1 VALUES
278
("e1", NOW(), round_to_step(UNIX_TIMESTAMP() - $now, 4) - 1);
279
eval CREATE EVENT e2 ON SCHEDULE EVERY 1 MONTH
280
STARTS FROM_UNIXTIME($now + @step) DO
281
INSERT INTO t1 VALUES
282
("e2", NOW(), round_to_step(UNIX_TIMESTAMP() - $now, 4) - 1);
285
let $wait_timeout= `SELECT 16 * @step`;
286
let $wait_condition= SELECT COUNT(*) = 7 FROM t1;
287
--source include/wait_condition.inc
289
SET GLOBAL EVENT_SCHEDULER= OFF;
291
--echo Below we should see the following:
292
--echo - On Jan 31 only e2 is executed, because we started later than
293
--echo e1 should have been executed. Offset of e2 is 0 because of
294
--echo the late start, not 1.
295
--echo - The next execution is on Feb 28 (last day of Feb). Both events
296
--echo are executed in their times, offsets are -1 and 1.
297
--echo - The next time is Mar 31. Because the time of event
298
--echo execution was skipped over, events are executed right away,
299
--echo offsets are 2 and 2.
300
--echo - The next time is Apr 30. Events are again executed in their
301
--echo appointed times, offsets are -1 and 1.
302
SELECT * FROM t1 ORDER BY dt, event;
308
SET TIME_ZONE= @save_time_zone;
310
DELETE FROM mysql.time_zone_name WHERE time_zone_id = @tzid;
311
DELETE FROM mysql.time_zone_transition_type WHERE time_zone_id = @tzid;
312
DELETE FROM mysql.time_zone_transition WHERE time_zone_id = @tzid;
313
DELETE FROM mysql.time_zone WHERE time_zone_id = @tzid;
314
let $time_zone_auto_inc = `SELECT MAX(Time_zone_id) + 1 FROM mysql.time_zone`;
315
eval ALTER TABLE mysql.time_zone AUTO_INCREMENT = $time_zone_auto_inc;
317
DROP FUNCTION round_to_step;
321
DROP DATABASE mysqltest_db1;
327
SELECT COUNT(*) = 0 FROM information_schema.processlist
328
WHERE db='mysqltest_db1' AND command = 'Connect' AND user=current_user();
329
--source include/wait_condition.inc
331
--echo End of 5.1 tests.