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