1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
|
# Can't test with embedded server that doesn't support grants
-- source include/not_embedded.inc
CREATE DATABASE IF NOT EXISTS events_test;
USE events_test;
SET GLOBAL event_scheduler=OFF;
--echo Try agian to make sure it's allowed
SET GLOBAL event_scheduler=OFF;
SHOW VARIABLES LIKE 'event_scheduler';
SET GLOBAL event_scheduler=1;
SHOW VARIABLES LIKE 'event_scheduler';
SET GLOBAL event_scheduler=0;
SHOW VARIABLES LIKE 'event_scheduler';
SET GLOBAL event_scheduler=ON;
--echo Try again to make sure it's allowed
SET GLOBAL event_scheduler=ON;
SHOW VARIABLES LIKE 'event_scheduler';
--error ER_WRONG_VALUE_FOR_VAR
SET GLOBAL event_scheduler=DISABLED;
SHOW VARIABLES LIKE 'event_scheduler';
--error ER_WRONG_VALUE_FOR_VAR
SET GLOBAL event_scheduler=-1;
SHOW VARIABLES LIKE 'event_scheduler';
--error ER_WRONG_VALUE_FOR_VAR
SET GLOBAL event_scheduler=2;
SHOW VARIABLES LIKE 'event_scheduler';
--error ER_WRONG_VALUE_FOR_VAR
SET GLOBAL event_scheduler=5;
SHOW VARIABLES LIKE 'event_scheduler';
CREATE TABLE table_1(a int);
CREATE TABLE table_2(a int);
CREATE TABLE table_3(a int);
CREATE TABLE table_4(a int);
SET GLOBAL event_scheduler=ON;
# We need to have 2 to make it safe with valgrind. This is probably because
# of when we calculate the timestamp value
CREATE EVENT event_1 ON SCHEDULE EVERY 2 SECOND
DO
INSERT INTO table_1 VALUES (1);
CREATE EVENT event_2 ON SCHEDULE EVERY 1 SECOND
ENDS NOW() + INTERVAL 6 SECOND
ON COMPLETION PRESERVE
DO
INSERT INTO table_2 VALUES (1);
CREATE EVENT event_3 ON SCHEDULE EVERY 2 SECOND ENDS NOW() + INTERVAL 1 SECOND
ON COMPLETION NOT PRESERVE
DO
INSERT INTO table_3 VALUES (1);
CREATE EVENT event_4 ON SCHEDULE EVERY 1 SECOND ENDS NOW() + INTERVAL 1 SECOND
ON COMPLETION PRESERVE
DO
INSERT INTO table_4 VALUES (1);
# Let event_1 insert at least 4 records into the table
let $wait_condition=select count(*) >= 4 from table_1;
--source include/wait_condition.inc
# Let event_2 reach the end of its execution interval
let $wait_condition=select count(*) = 0 from information_schema.events
where event_name='event_2' and status='enabled';
--source include/wait_condition.inc
# Let event_3, which is ON COMPLETION NOT PRESERVE execute and drop itself
let $wait_condition=select count(*) = 0 from information_schema.events
where event_name='event_3';
--source include/wait_condition.inc
# Let event_4 reach the end of its execution interval
let $wait_condition=select count(*) = 0 from information_schema.events
where event_name='event_4' and status='enabled';
--source include/wait_condition.inc
# check the data
SELECT IF(SUM(a) >= 4, 'OK', 'ERROR') FROM table_1;
SELECT IF(SUM(a) >= 5, 'OK', 'ERROR') FROM table_2;
SELECT IF(SUM(a) >= 1, 'OK', 'ERROR') FROM table_3;
SELECT IF(SUM(a) >= 1, 'OK', 'ERROR') FROM table_4;
SELECT IF(TIME_TO_SEC(TIMEDIFF(ENDS,STARTS))=6, 'OK', 'ERROR')
FROM INFORMATION_SCHEMA.EVENTS
WHERE EVENT_SCHEMA=DATABASE() AND EVENT_NAME='event_2';
SELECT IF(LAST_EXECUTED-ENDS < 3, 'OK', 'ERROR')
FROM INFORMATION_SCHEMA.EVENTS
WHERE EVENT_SCHEMA=DATABASE() AND EVENT_NAME='event_2';
--echo "Already dropped because ended. Therefore an error."
--error ER_EVENT_DOES_NOT_EXIST
DROP EVENT event_3;
DROP EVENT event_1;
--echo "Should be preserved"
SELECT EVENT_NAME, STATUS FROM INFORMATION_SCHEMA.EVENTS ORDER BY EVENT_NAME;
DROP EVENT event_2;
DROP EVENT event_4;
DROP TABLE table_1;
DROP TABLE table_2;
DROP TABLE table_3;
DROP TABLE table_4;
DROP DATABASE events_test;
SET GLOBAL event_scheduler=OFF;
#
# End of tests
#
let $wait_condition=
select count(*) = 0 from information_schema.processlist
where db='events_test' and command = 'Connect' and user=current_user();
--source include/wait_condition.inc
|
