Debian Codegraph @

mysql-test/suite/events/events_scheduling.test

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