events_scheduling.result 2.17 KB
Newer Older
1 2 3 4 5 6
CREATE DATABASE IF NOT EXISTS events_test;
USE events_test;
CREATE TABLE table_1(a int);
CREATE TABLE table_2(a int);
CREATE TABLE table_3(a int);
CREATE TABLE table_4(a int);
unknown's avatar
unknown committed
7
CREATE TABLE T19170(s1 TIMESTAMP);
8
SET GLOBAL event_scheduler=1;
unknown's avatar
unknown committed
9
CREATE EVENT E19170 ON SCHEDULE EVERY 1 SECOND DO INSERT INTO T19170 VALUES(CURRENT_TIMESTAMP);
10 11 12 13 14 15 16
CREATE EVENT two_sec ON SCHEDULE EVERY 2 SECOND DO INSERT INTO table_1 VALUES(1);
CREATE EVENT start_n_end
ON SCHEDULE EVERY 1 SECOND
ENDS NOW() + INTERVAL 6 SECOND
ON COMPLETION PRESERVE
DO INSERT INTO table_2 VALUES(1);
CREATE EVENT only_one_time ON SCHEDULE EVERY 2 SECOND ENDS NOW() + INTERVAL 1 SECOND DO INSERT INTO table_3 VALUES(1);
17
CREATE EVENT two_time ON SCHEDULE EVERY 1 SECOND ENDS NOW() + INTERVAL 1 SECOND ON COMPLETION PRESERVE DO INSERT INTO table_4 VALUES(1);
18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33
SELECT IF(SUM(a) >= 4, 'OK', 'ERROR') FROM table_1;
IF(SUM(a) >= 4, 'OK', 'ERROR')
OK
SELECT IF(SUM(a) >= 5, 'OK', 'ERROR') FROM table_2;
IF(SUM(a) >= 5, 'OK', 'ERROR')
OK
SELECT IF(SUM(a) >  0, 'OK', 'ERROR') FROM table_3;
IF(SUM(a) >  0, 'OK', 'ERROR')
OK
SELECT IF(SUM(a) >  0, 'OK', 'ERROR') FROM table_4;
IF(SUM(a) >  0, 'OK', 'ERROR')
OK
DROP EVENT two_sec;
SELECT IF(TIME_TO_SEC(TIMEDIFF(ENDS,STARTS))=6, 'OK', 'ERROR') FROM INFORMATION_SCHEMA.EVENTS WHERE EVENT_SCHEMA=DATABASE() AND EVENT_NAME='start_n_end' AND ENDS IS NOT NULL;
IF(TIME_TO_SEC(TIMEDIFF(ENDS,STARTS))=6, 'OK', 'ERROR')
OK
unknown's avatar
unknown committed
34 35
SELECT IF(LAST_EXECUTED-ENDS < 3, 'OK', 'ERROR') FROM INFORMATION_SCHEMA.EVENTS WHERE EVENT_SCHEMA=DATABASE() AND EVENT_NAME='start_n_end' AND ENDS IS NOT NULL;
IF(LAST_EXECUTED-ENDS < 3, 'OK', 'ERROR')
36 37 38 39 40
OK
DROP EVENT start_n_end;
"Already dropped because ended. Therefore an error."
DROP EVENT only_one_time;
ERROR HY000: Unknown event 'only_one_time'
41 42 43 44 45
"Should be preserved"
SELECT EVENT_NAME, STATUS FROM INFORMATION_SCHEMA.EVENTS;
EVENT_NAME	STATUS
E19170	ENABLED
two_time	DISABLED
46 47 48 49 50
DROP EVENT two_time;
DROP TABLE table_1;
DROP TABLE table_2;
DROP TABLE table_3;
DROP TABLE table_4;
unknown's avatar
unknown committed
51 52 53 54
"Checking for multiple executions in one second, should not happen -> 0 as result"
SELECT COUNT(*) FROM (SELECT s1, COUNT(*) AS cnt FROM T19170 GROUP BY s1) AS tmp WHERE tmp.cnt > 1;
COUNT(*)
0
55
DROP DATABASE events_test;