| 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. |