Hi Monty.
Saw the documentation was updated, but now I'm even more puzzled. This is the
test I come up with. A bit long but if covers all situations I'm looking at.
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (
id INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
a VARCHAR(20),
b DATETIME
);
DELIMITER //
CREATE OR REPLACE PROCEDURE abc1 (IN x VARCHAR(10))
BEGIN
SET @n=SECOND(NOW());
INSERT INTO t1 (a,b) VALUES (CONCAT(x,' start: ',@n),NOW());
SELECT x,SLEEP(10);
INSERT INTO t1 (a,b) VALUES (CONCAT(x,' end: ',@n),NOW());
END;
//
CREATE OR REPLACE PROCEDURE abc2 (IN x VARCHAR(10))
BEGIN
SET @n=SECOND(NOW());
INSERT INTO t1 (a,b) VALUES (CONCAT(x,' start: ',@n),NOW());
SELECT x,SLEEP(10);
INSERT INTO t1 (a,b) VALUES (CONCAT(x,' end: ',@n),NOW());
END;
//
DROP EVENT IF EXISTS ev1;
CREATE EVENT ev1
ON SCHEDULE EVERY 2 SECOND STARTS '2023-08-14 00:00:00'
DO CALL abc1('ev1')
//
DROP EVENT IF EXISTS ev2;
CREATE EVENT ev2
ON SCHEDULE EVERY 2 SECOND STARTS '2023-08-14 00:00:01'
DO CALL abc1('ev2')
//
DROP EVENT IF EXISTS ev3;
CREATE EVENT ev3
ON SCHEDULE EVERY 2 SECOND STARTS '2023-08-14 00:00:00'
DO CALL abc1('ev3')
//
DROP EVENT IF EXISTS ev4;
CREATE EVENT ev4
ON SCHEDULE EVERY 2 SECOND STARTS '2023-08-14 00:00:01'
DO CALL abc2('ev4')
//
DELIMITER ;
select sleep(20);
show processlist;
SELECT * FROM t1;
DROP EVENT IF EXISTS ev4;
DROP EVENT IF EXISTS ev3;
DROP EVENT IF EXISTS ev2;
DROP EVENT IF EXISTS ev1;
So we got two procedures and 4 events.
- ev1 and ev3 is triggered every 2 seconds (on even seconds) and calls
procedure abc1 which takes 10 seconds to run.
- ev2 is triggered every 2 seconds (on odd seconds) and calls same procedure
abc1.
- ev4 is triggered every 2 seconds (on odd seconds) and calls another procedure
abc2 which also takes 10 seconds to run.
The procedures logs to table when ended and started with the calling event name
and an identifier (the seconds).
So I got long running events trying to overlap each other and two procedures
just in case events would behave differently in this case.
And everything seems to work as expected.
Processlist shows the different overlapping processes running (showing just
relavant info). And there are same events running at same time, because they
got called every 2 seconds.
Id Time Info
28358 9 SELECT NAME_CONST('x',_utf8mb4'ev4' COLLATE
'utf8mb4_unicode_520_ci'),SLEEP(10)
28359 9 SELECT NAME_CONST('x',_utf8mb4'ev2' COLLATE
'utf8mb4_unicode_520_ci'),SLEEP(10)
28360 8 SELECT NAME_CONST('x',_utf8mb4'ev3' COLLATE
'utf8mb4_unicode_520_ci'),SLEEP(10)
28361 8 SELECT NAME_CONST('x',_utf8mb4'ev1' COLLATE
'utf8mb4_unicode_520_ci'),SLEEP(10)
28362 7 SELECT NAME_CONST('x',_utf8mb4'ev2' COLLATE
'utf8mb4_unicode_520_ci'),SLEEP(10)
28363 7 SELECT NAME_CONST('x',_utf8mb4'ev4' COLLATE
'utf8mb4_unicode_520_ci'),SLEEP(10)
28364 6 SELECT NAME_CONST('x',_utf8mb4'ev1' COLLATE
'utf8mb4_unicode_520_ci'),SLEEP(10)
28365 6 SELECT NAME_CONST('x',_utf8mb4'ev3' COLLATE
'utf8mb4_unicode_520_ci'),SLEEP(10)
28366 5 SELECT NAME_CONST('x',_utf8mb4'ev4' COLLATE
'utf8mb4_unicode_520_ci'),SLEEP(10)
28367 5 SELECT NAME_CONST('x',_utf8mb4'ev2' COLLATE
'utf8mb4_unicode_520_ci'),SLEEP(10)
28368 4 SELECT NAME_CONST('x',_utf8mb4'ev3' COLLATE
'utf8mb4_unicode_520_ci'),SLEEP(10)
28369 4 SELECT NAME_CONST('x',_utf8mb4'ev1' COLLATE
'utf8mb4_unicode_520_ci'),SLEEP(10)
28370 3 SELECT NAME_CONST('x',_utf8mb4'ev2' COLLATE
'utf8mb4_unicode_520_ci'),SLEEP(10)
28371 3 SELECT NAME_CONST('x',_utf8mb4'ev4' COLLATE
'utf8mb4_unicode_520_ci'),SLEEP(10)
28372 2 SELECT NAME_CONST('x',_utf8mb4'ev1' COLLATE
'utf8mb4_unicode_520_ci'),SLEEP(10)
28373 2 SELECT NAME_CONST('x',_utf8mb4'ev3' COLLATE
'utf8mb4_unicode_520_ci'),SLEEP(10)
28374 1 SELECT NAME_CONST('x',_utf8mb4'ev4' COLLATE
'utf8mb4_unicode_520_ci'),SLEEP(10)
28375 1 SELECT NAME_CONST('x',_utf8mb4'ev2' COLLATE
'utf8mb4_unicode_520_ci'),SLEEP(10)
28376 0 SELECT NAME_CONST('x',_utf8mb4'ev3' COLLATE
'utf8mb4_unicode_520_ci'),SLEEP(10)
28377 0 SELECT NAME_CONST('x',_utf8mb4'ev1' COLLATE
'utf8mb4_unicode_520_ci'),SLEEP(10)
And when checking table t1, we can see events starting at same time. So line
(id) 1 is event3 starting at seconds :50, and it ends 10 seconds later as id 23.
id a b
1 ev3 start: 50 2023-08-17 23:29:50
2 ev1 start: 50 2023-08-17 23:29:50
3 ev2 start: 51 2023-08-17 23:29:51
4 ev4 start: 51 2023-08-17 23:29:51
5 ev3 start: 52 2023-08-17 23:29:52
6 ev1 start: 52 2023-08-17 23:29:52
7 ev4 start: 53 2023-08-17 23:29:53
8 ev2 start: 53 2023-08-17 23:29:53
9 ev1 start: 54 2023-08-17 23:29:54
10 ev3 start: 54 2023-08-17 23:29:54
11 ev4 start: 55 2023-08-17 23:29:55
12 ev2 start: 55 2023-08-17 23:29:55
13 ev1 start: 56 2023-08-17 23:29:56
14 ev3 start: 56 2023-08-17 23:29:56
15 ev4 start: 57 2023-08-17 23:29:57
16 ev2 start: 57 2023-08-17 23:29:57
17 ev3 start: 58 2023-08-17 23:29:58
18 ev1 start: 58 2023-08-17 23:29:58
19 ev2 start: 59 2023-08-17 23:29:59
20 ev4 start: 59 2023-08-17 23:29:59
21 ev3 start: 0 2023-08-17 23:30:00
22 ev1 start: 0 2023-08-17 23:30:00
23 ev1 end: 50 2023-08-17 23:30:00
24 ev3 end: 50 2023-08-17 23:30:00
25 ev2 end: 51 2023-08-17 23:30:01
26 ev4 end: 51 2023-08-17 23:30:01
27 ev2 start: 1 2023-08-17 23:30:01
28 ev4 start: 1 2023-08-17 23:30:01
29 ev1 start: 2 2023-08-17 23:30:02
30 ev3 start: 2 2023-08-17 23:30:02
31 ev1 end: 52 2023-08-17 23:30:02
So multiple events at same time overlapping each other seems to be working or
do I test this somehow wrong?
-- Kaj
_______________________________________________
discuss mailing list -- [email protected]
To unsubscribe send an email to [email protected]
