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
10
--source include/big_test.inc
14
DROP DATABASE IF EXISTS mysqltest_db1;
17
CREATE DATABASE mysqltest_db1;
19
let $old_db= `SELECT DATABASE()`;
22
SET GLOBAL EVENT_SCHEDULER= OFF;
23
SET @save_time_zone= @@TIME_ZONE;
27
# BUG#16420: Events: timestamps become UTC
28
# BUG#26429: SHOW CREATE EVENT is incorrect for an event that
30
# BUG#26431: Impossible to re-create an event from backup if its
31
# STARTS clause is in the past
32
# WL#3698: Events: execution in local time zone
35
#----------------------------------------------------------------------
37
# Create rounding function.
39
# Disable query log to hide actual value of $N.
44
# Since we are working in a separate database, we may use any names we
46
CREATE TABLE t_step (step INT);
47
INSERT INTO t_step VALUES (@step);
49
# We can't use @variables in function, because it will be called from
50
# the event thread, and 'eval' doesn't work for multi-statements, so
51
# we can't interpolate $variables either, hence we fetch the step
52
# value from the table.
54
CREATE FUNCTION round_to_step(i INT, n INT) RETURNS INT
58
SELECT * INTO step FROM t_step;
60
# We add 0.1 as a protection from inexact division.
61
RETURN FLOOR((i % (step * n) + 0.1) / step);
66
# Test time computations wrt Daylight Saving Time shifts. We also
67
# test here that the event operates in its time zone (see what NOW()
71
# Create a fake time zone with time transitions every 3*$N second.
73
SET @step3= @step * 3;
74
SET @step6= @step * 6;
76
# Disable query log to hide current time.
78
SET @unix_time= UNIX_TIMESTAMP() - 1;
81
SET @unix_time= @unix_time - @unix_time % @step6;
83
INSERT INTO mysql.time_zone VALUES (NULL, 'N');
84
SET @tzid= LAST_INSERT_ID();
85
INSERT INTO mysql.time_zone_transition_type
86
VALUES (@tzid, 0, 0, 0, 'b16420_0');
87
INSERT INTO mysql.time_zone_transition_type
88
VALUES (@tzid, 1, @step3 - @step, 1, 'b16420_1');
90
let $transition_unix_time= `SELECT @unix_time`;
95
eval INSERT INTO mysql.time_zone_transition
96
VALUES (@tzid, $transition_unix_time,
97
$transition_unix_time % @step6 = 0);
98
let $transition_unix_time= `SELECT $transition_unix_time + @step3`;
102
INSERT INTO mysql.time_zone_name VALUES ('bug16420', @tzid);
104
CREATE TABLE t1 (count INT, unix_time INT, local_time INT, comment CHAR(80));
105
CREATE TABLE t2 (count INT);
106
INSERT INTO t2 VALUES (1);
109
CREATE FUNCTION f1(comment CHAR(80)) RETURNS INT
111
DECLARE orig_tz CHAR(64);
112
DECLARE unix_time INT;
113
DECLARE local_now DATETIME;
114
DECLARE utc_now DATETIME;
115
DECLARE local_time INT;
117
SET unix_time= UNIX_TIMESTAMP();
118
SET local_now= FROM_UNIXTIME(unix_time);
119
SET orig_tz= @@TIME_ZONE;
120
SET TIME_ZONE = '+00:00';
121
SET utc_now= FROM_UNIXTIME(unix_time);
122
SET TIME_ZONE= orig_tz;
123
SET local_time = unix_time + TIMESTAMPDIFF(SECOND, utc_now, local_now);
125
SET unix_time= round_to_step(unix_time, 6);
126
SET local_time= round_to_step(local_time, 6);
128
INSERT INTO t1 VALUES ((SELECT count FROM t2),
129
unix_time, local_time, comment);
134
SET TIME_ZONE= '+00:00';
135
CREATE EVENT e1 ON SCHEDULE EVERY @step SECOND
136
STARTS FROM_UNIXTIME(@unix_time) DO SELECT f1("<e1>");
138
SET TIME_ZONE= 'bug16420';
139
CREATE EVENT e2 ON SCHEDULE EVERY @step SECOND
140
STARTS FROM_UNIXTIME(@unix_time) DO SELECT f1("<e2>");
142
# We want to start at the beginning of the DST cycle, so we wait
143
# untill current time divides by @step6.
144
let $wait_timeout= `SELECT @step6 + 1`;
145
let $wait_condition= SELECT UNIX_TIMESTAMP() % @step6 = @step6 - 1;
146
--source include/wait_condition.inc
147
# The second wait is needed because after the first wait we may end up
148
# on the ending edge of a second. Second wait will bring us to the
150
let $wait_timeout= `SELECT @step + 1`;
151
let $wait_condition= SELECT UNIX_TIMESTAMP() % @step6 = 0;
152
--source include/wait_condition.inc
154
# Note that after the scheduler is enabled, the event will be
155
# scheduled only for the next second.
156
SET GLOBAL EVENT_SCHEDULER= ON;
158
# We want to run after the events are executed.
159
SELECT SLEEP(@step / 2);
168
eval SELECT CASE $count
169
WHEN 5 THEN f1(CONCAT("Second pass after backward -2 step shift,",
170
" e2 should not be executed"))
171
WHEN 4 THEN f1(CONCAT("Second pass after backward -2 step shift,",
172
" e2 should not be executed"))
173
WHEN 2 THEN f1(CONCAT("Forward +2 step shift, local 0, 1 are skipped,",
174
" e2 should be executed"))
175
ELSE f1("e2 should be executed")
177
UPDATE t2 SET count= count + 1;
184
SET GLOBAL EVENT_SCHEDULER= OFF;
186
SELECT * FROM t1 ORDER BY count, comment;
188
SET TIME_ZONE= @save_time_zone;
195
DELETE FROM mysql.time_zone_name WHERE time_zone_id = @tzid;
196
DELETE FROM mysql.time_zone_transition_type WHERE time_zone_id = @tzid;
197
DELETE FROM mysql.time_zone_transition WHERE time_zone_id = @tzid;
198
DELETE FROM mysql.time_zone WHERE time_zone_id = @tzid;
200
#----------------------------------------------------------------------
202
# Test MONTH interval.
205
SET TIME_ZONE= '+00:00';
207
CREATE TABLE t1 (event CHAR(2), dt DATE, offset INT);
209
INSERT INTO mysql.time_zone VALUES (NULL, 'N');
210
SET @tzid= LAST_INSERT_ID();
212
SET @now= UNIX_TIMESTAMP();
213
SET @offset_month_01= UNIX_TIMESTAMP('2030-01-31 12:00:00') - @now;
214
SET @offset_month_02= UNIX_TIMESTAMP('2030-02-28 12:00:00') - @now - 5*@step;
215
SET @offset_month_03= UNIX_TIMESTAMP('2030-03-31 12:00:00') - @now - 5*@step;
216
SET @offset_month_04= UNIX_TIMESTAMP('2030-04-30 12:00:00') - @now - 13*@step;
218
INSERT INTO mysql.time_zone_transition_type
219
VALUES (@tzid, 0, @offset_month_01, 0, 'b16420_0');
220
INSERT INTO mysql.time_zone_transition_type
221
VALUES (@tzid, 1, @offset_month_02, 1, 'b16420_1');
222
INSERT INTO mysql.time_zone_transition_type
223
VALUES (@tzid, 2, @offset_month_03, 1, 'b16420_2');
224
INSERT INTO mysql.time_zone_transition_type
225
VALUES (@tzid, 3, @offset_month_04, 1, 'b16420_3');
226
INSERT INTO mysql.time_zone_transition
227
VALUES (@tzid, @now, 0);
228
INSERT INTO mysql.time_zone_transition
229
VALUES (@tzid, @now + 3 * @step, 1);
230
INSERT INTO mysql.time_zone_transition
231
VALUES (@tzid, @now + 7 * @step, 2);
232
INSERT INTO mysql.time_zone_transition
233
VALUES (@tzid, @now + 12 * @step, 3);
234
# We have to user a new time zone name, because 'bug16420' has been
236
INSERT INTO mysql.time_zone_name VALUES ('bug16420_2', @tzid);
238
SET TIME_ZONE= 'bug16420_2';
240
SET GLOBAL EVENT_SCHEDULER= ON;
242
let $now= `SELECT @now`;
244
eval CREATE EVENT e1 ON SCHEDULE EVERY 1 MONTH
245
STARTS FROM_UNIXTIME($now - @step) DO
246
INSERT INTO t1 VALUES
247
("e1", NOW(), round_to_step(UNIX_TIMESTAMP() - $now, 4) - 1);
248
eval CREATE EVENT e2 ON SCHEDULE EVERY 1 MONTH
249
STARTS FROM_UNIXTIME($now + @step) DO
250
INSERT INTO t1 VALUES
251
("e2", NOW(), round_to_step(UNIX_TIMESTAMP() - $now, 4) - 1);
254
let $wait_timeout= `SELECT 16 * @step`;
255
let $wait_condition= SELECT COUNT(*) = 7 FROM t1;
256
--source include/wait_condition.inc
258
SET GLOBAL EVENT_SCHEDULER= OFF;
260
--echo Below we should see the following:
261
--echo - On Jan 31 only e2 is executed, because we started later than
262
--echo e1 should have been executed. Offset of e2 is 0 because of
263
--echo the late start, not 1.
264
--echo - The next execution is on Feb 28 (last day of Feb). Both events
265
--echo are executed in their times, offsets are -1 and 1.
266
--echo - The next time is Mar 31. Because the time of event
267
--echo execution was skipped over, events are executed right away,
268
--echo offsets are 2 and 2.
269
--echo - The next time is Apr 30. Events are again executed in their
270
--echo appointed times, offsets are -1 and 1.
271
SELECT * FROM t1 ORDER BY dt, event;
277
SET TIME_ZONE= @save_time_zone;
279
DELETE FROM mysql.time_zone_name WHERE time_zone_id = @tzid;
280
DELETE FROM mysql.time_zone_transition_type WHERE time_zone_id = @tzid;
281
DELETE FROM mysql.time_zone_transition WHERE time_zone_id = @tzid;
282
DELETE FROM mysql.time_zone WHERE time_zone_id = @tzid;
284
DROP FUNCTION round_to_step;
288
DROP DATABASE mysqltest_db1;
294
--echo End of 5.1 tests.
added
removed
mysql-test/t/events_time_zone.test
