1# Can't test with embedded server that doesn't support events 2-- source include/not_embedded.inc 3 4CREATE DATABASE IF NOT EXISTS events_test; 5USE events_test; 6 7SET @event_scheduler=@@global.event_scheduler; 8SET GLOBAL event_scheduler=OFF; 9--echo Try again to make sure it's allowed 10SET GLOBAL event_scheduler=OFF; 11SHOW VARIABLES LIKE 'event_scheduler'; 12SET GLOBAL event_scheduler=1; 13SHOW VARIABLES LIKE 'event_scheduler'; 14SET GLOBAL event_scheduler=0; 15SHOW VARIABLES LIKE 'event_scheduler'; 16SET GLOBAL event_scheduler=ON; 17--echo Try again to make sure it's allowed 18SET GLOBAL event_scheduler=ON; 19SHOW VARIABLES LIKE 'event_scheduler'; 20--error ER_WRONG_VALUE_FOR_VAR 21SET GLOBAL event_scheduler=DISABLED; 22SHOW VARIABLES LIKE 'event_scheduler'; 23--error ER_WRONG_VALUE_FOR_VAR 24SET GLOBAL event_scheduler=-1; 25SHOW VARIABLES LIKE 'event_scheduler'; 26--error ER_WRONG_VALUE_FOR_VAR 27SET GLOBAL event_scheduler=2; 28SHOW VARIABLES LIKE 'event_scheduler'; 29--error ER_WRONG_VALUE_FOR_VAR 30SET GLOBAL event_scheduler=5; 31SHOW VARIABLES LIKE 'event_scheduler'; 32 33CREATE TABLE table_1(a int); 34CREATE TABLE table_2(a int); 35CREATE TABLE table_3(a int); 36CREATE TABLE table_4(a int); 37 38SET GLOBAL event_scheduler=ON; 39# We need to have 2 to make it safe with valgrind. This is probably because 40# of when we calculate the timestamp value 41CREATE EVENT event_1 ON SCHEDULE EVERY 2 SECOND 42DO 43 INSERT INTO table_1 VALUES (1); 44 45CREATE EVENT event_2 ON SCHEDULE EVERY 1 SECOND 46ENDS NOW() + INTERVAL 6 SECOND 47ON COMPLETION PRESERVE 48DO 49 INSERT INTO table_2 VALUES (1); 50 51CREATE EVENT event_3 ON SCHEDULE EVERY 2 SECOND ENDS NOW() + INTERVAL 1 SECOND 52ON COMPLETION NOT PRESERVE 53DO 54 INSERT INTO table_3 VALUES (1); 55 56CREATE EVENT event_4 ON SCHEDULE EVERY 1 SECOND ENDS NOW() + INTERVAL 1 SECOND 57ON COMPLETION PRESERVE 58DO 59 INSERT INTO table_4 VALUES (1); 60 61# Let event_1 insert at least 4 records into the table 62let $wait_condition=select count(*) >= 4 from table_1; 63--source include/wait_condition.inc 64# Minimum of passed time is 6 seconds assuming 65# - event executions starts immediate after creation 66# - 4 times event_1 means an insert at ect, ect+2, ect+4, ect+6 67# ect = event creation time 68 69# Let event_2 reach the end of its execution interval 70let $wait_condition=select count(*) = 0 from information_schema.events 71where event_name='event_2' and status='enabled'; 72--source include/wait_condition.inc 73# Minimum of passed time is 6 seconds. 74# See wait_condition for event_1 above and ENDS condition for event_2. 75 76# Let event_3, which is ON COMPLETION NOT PRESERVE execute and drop itself 77let $wait_condition=select count(*) = 0 from information_schema.events 78where event_name='event_3'; 79--source include/wait_condition.inc 80 81# Let event_4 reach the end of its execution interval 82let $wait_condition=select count(*) = 0 from information_schema.events 83where event_name='event_4' and status='enabled'; 84--source include/wait_condition.inc 85 86# 87# On a busy system the scheduler may skip execution of events, 88# we can't reliably expect that the data in a table to be modified 89# by an event will be exact. Thus we do not SELECT from the tables 90# in this test. See also 91# Bug#39854 events_scheduling fails sporadically on pushbuild 92# 93 94SELECT IF(TIME_TO_SEC(TIMEDIFF(ENDS,STARTS))=6, 'OK', 'ERROR') 95FROM INFORMATION_SCHEMA.EVENTS 96WHERE EVENT_SCHEMA=DATABASE() AND EVENT_NAME='event_2'; 97 98--echo "Already dropped because ended. Therefore an error." 99--error ER_EVENT_DOES_NOT_EXIST 100DROP EVENT event_3; 101 102DROP EVENT event_1; 103--echo "Should be preserved" 104SELECT EVENT_NAME, STATUS FROM INFORMATION_SCHEMA.EVENTS ORDER BY EVENT_NAME; 105DROP EVENT event_2; 106DROP EVENT event_4; 107DROP TABLE table_1; 108DROP TABLE table_2; 109DROP TABLE table_3; 110DROP TABLE table_4; 111 112-- echo 113-- echo Bug #50087 Interval arithmetic for Event_queue_element is not portable. 114-- echo 115 116CREATE TABLE t1(a int); 117 118CREATE EVENT e1 ON SCHEDULE EVERY 1 MONTH 119STARTS NOW() - INTERVAL 1 MONTH 120ENDS NOW() + INTERVAL 2 MONTH 121ON COMPLETION PRESERVE 122DO 123 INSERT INTO t1 VALUES (1); 124 125CREATE EVENT e2 ON SCHEDULE EVERY 1 MONTH 126STARTS NOW() 127ENDS NOW() + INTERVAL 11 MONTH 128ON COMPLETION PRESERVE 129DO 130 INSERT INTO t1 VALUES (1); 131 132DROP TABLE t1; 133DROP EVENT e1; 134DROP EVENT e2; 135 136 137DROP DATABASE events_test; 138SET GLOBAL event_scheduler=@event_scheduler; 139 140# 141# End of tests 142# 143 144let $wait_condition= 145 select count(*) = 0 from information_schema.processlist 146 where db='events_test' and command = 'Connect' and user=current_user(); 147--source include/wait_condition.inc 148 149