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). 8let $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 46DROP DATABASE IF EXISTS mysqltest_db1; 47--enable_warnings 48 49CREATE DATABASE mysqltest_db1; 50 51let $old_db= `SELECT DATABASE()`; 52USE mysqltest_db1; 53 54SET GLOBAL EVENT_SCHEDULER= OFF; 55SET @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 73eval 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. 78CREATE TABLE t_step (step INT); 79INSERT 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. 85delimiter //; 86CREATE FUNCTION round_to_step(i INT, n INT) RETURNS INT 87BEGIN 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); 94END// 95delimiter ;// 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 105SET @step3= @step * 3; 106SET @step6= @step * 6; 107 108SET @unix_time= UNIX_TIMESTAMP() - 1; 109SET @unix_time= @unix_time - @unix_time % @step6; 110 111INSERT INTO mysql.time_zone VALUES (NULL, 'N'); 112SET @tzid= LAST_INSERT_ID(); 113INSERT INTO mysql.time_zone_transition_type 114 VALUES (@tzid, 0, 0, 0, 'b16420_0'); 115INSERT INTO mysql.time_zone_transition_type 116 VALUES (@tzid, 1, @step3 - @step, 1, 'b16420_1'); 117 118let $transition_unix_time= `SELECT @unix_time`; 119let $count= 30; 120--disable_query_log 121begin; 122while ($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} 130commit; 131--enable_query_log 132let $tz_name = `SELECT CONCAT('b16420_a',UNIX_TIMESTAMP())`; 133--replace_result $tz_name <TZ_NAME_1> 134eval INSERT INTO mysql.time_zone_name VALUES ('$tz_name', @tzid); 135 136CREATE TABLE t1 (count INT, unix_time INT, local_time INT, comment CHAR(80)); 137CREATE TABLE t2 (count INT); 138INSERT INTO t2 VALUES (1); 139 140delimiter //; 141CREATE FUNCTION f1(comment CHAR(80)) RETURNS INT 142BEGIN 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; 163END// 164delimiter ;// 165 166SET TIME_ZONE= '+00:00'; 167CREATE 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> 171eval SET TIME_ZONE= '$tz_name'; 172CREATE 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. 177let $wait_timeout= `SELECT @step6*2 + 1`; 178let $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. 183let $wait_timeout= `SELECT @step*2 + 1`; 184let $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. 189SET GLOBAL EVENT_SCHEDULER= ON; 190 191# We want to run after the events are executed. 192SELECT SLEEP(@step / 2); 193 194let $count= 7; 195--disable_query_log 196--disable_result_log 197while ($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 217SET GLOBAL EVENT_SCHEDULER= OFF; 218 219SELECT * FROM t1 ORDER BY count, comment; 220 221SET TIME_ZONE= @save_time_zone; 222 223DROP EVENT e2; 224DROP EVENT e1; 225DROP FUNCTION f1; 226DROP TABLE t1, t2; 227 228DELETE FROM mysql.time_zone_name WHERE time_zone_id = @tzid; 229DELETE FROM mysql.time_zone_transition_type WHERE time_zone_id = @tzid; 230DELETE FROM mysql.time_zone_transition WHERE time_zone_id = @tzid; 231DELETE FROM mysql.time_zone WHERE time_zone_id = @tzid; 232let $time_zone_auto_inc = `SELECT MAX(Time_zone_id) + 1 FROM mysql.time_zone`; 233eval ALTER TABLE mysql.time_zone AUTO_INCREMENT = $time_zone_auto_inc; 234 235#---------------------------------------------------------------------- 236 237# Test MONTH interval. 238# 239 240SET TIME_ZONE= '+00:00'; 241 242CREATE TABLE t1 (event CHAR(2), dt DATE, offset INT); 243 244INSERT INTO mysql.time_zone VALUES (NULL, 'N'); 245SET @tzid= LAST_INSERT_ID(); 246 247SET @now= UNIX_TIMESTAMP(); 248SET @offset_month_01= UNIX_TIMESTAMP('2030-01-31 12:00:00') - @now; 249SET @offset_month_02= UNIX_TIMESTAMP('2030-02-28 12:00:00') - @now - 5*@step; 250SET @offset_month_03= UNIX_TIMESTAMP('2030-03-31 12:00:00') - @now - 5*@step; 251SET @offset_month_04= UNIX_TIMESTAMP('2030-04-30 12:00:00') - @now - 13*@step; 252 253INSERT INTO mysql.time_zone_transition_type 254 VALUES (@tzid, 0, @offset_month_01, 0, 'b16420_0'); 255INSERT INTO mysql.time_zone_transition_type 256 VALUES (@tzid, 1, @offset_month_02, 1, 'b16420_1'); 257INSERT INTO mysql.time_zone_transition_type 258 VALUES (@tzid, 2, @offset_month_03, 1, 'b16420_2'); 259INSERT INTO mysql.time_zone_transition_type 260 VALUES (@tzid, 3, @offset_month_04, 1, 'b16420_3'); 261INSERT INTO mysql.time_zone_transition 262 VALUES (@tzid, @now, 0); 263INSERT INTO mysql.time_zone_transition 264 VALUES (@tzid, @now + 3 * @step, 1); 265INSERT INTO mysql.time_zone_transition 266 VALUES (@tzid, @now + 7 * @step, 2); 267INSERT INTO mysql.time_zone_transition 268 VALUES (@tzid, @now + 12 * @step, 3); 269let $tz_name = `SELECT CONCAT('b16420_b',UNIX_TIMESTAMP())`; 270--replace_result $tz_name <TZ_NAME_2> 271eval INSERT INTO mysql.time_zone_name VALUES ('$tz_name', @tzid); 272 273--replace_result $tz_name <TZ_NAME_2> 274eval SET TIME_ZONE= '$tz_name'; 275 276SET GLOBAL EVENT_SCHEDULER= ON; 277 278let $now= `SELECT @now`; 279--disable_query_log 280eval 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); 284eval 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 290let $wait_timeout= `SELECT 16 * @step`; 291let $wait_condition= SELECT COUNT(*) = 7 FROM t1; 292--source include/wait_condition.inc 293 294SET 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. 307SELECT * FROM t1 ORDER BY dt, event; 308 309DROP EVENT e2; 310DROP EVENT e1; 311DROP TABLE t1; 312 313SET TIME_ZONE= @save_time_zone; 314 315DELETE FROM mysql.time_zone_name WHERE time_zone_id = @tzid; 316DELETE FROM mysql.time_zone_transition_type WHERE time_zone_id = @tzid; 317DELETE FROM mysql.time_zone_transition WHERE time_zone_id = @tzid; 318DELETE FROM mysql.time_zone WHERE time_zone_id = @tzid; 319let $time_zone_auto_inc = `SELECT MAX(Time_zone_id) + 1 FROM mysql.time_zone`; 320eval ALTER TABLE mysql.time_zone AUTO_INCREMENT = $time_zone_auto_inc; 321 322DROP FUNCTION round_to_step; 323DROP TABLE t_step; 324 325 326DROP DATABASE mysqltest_db1; 327--disable_query_log 328eval USE $old_db; 329 330--enable_query_log 331let $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. 337