What will happen to running event if i turn off global event_scheduler to off

Question

Suppose I have a event in mysql named event_data and that calls a stored proceduce data_sp. event_data is triggered and data_sp is currently running. Now I am doing SET GLOBAL event_scheduler = OFF; - basically turning off global event scheduler.

What will happen to current execution of data_sp? Will it be stopped or keep running? If it will be stopped then will data be committed?

Answer 1

As the following proof of concept, the stored procedure will keep running until the end of the activity normally:

mysql> SELECT VERSION();
+-----------------+
| VERSION()       |
+-----------------+
| 5.5.22-0ubuntu1 |
+-----------------+
1 row in set (0.00 sec)

mysql> SET @@GLOBAL.event_scheduler := ON;
Query OK, 0 rows affected (0.00 sec)

mysql> DROP PROCEDURE IF EXISTS `data_sp`;
Query OK, 0 rows affected (0.00 sec)

mysql> DROP EVENT IF EXISTS `event_data`;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> DROP TABLE IF EXISTS `event_test`;
Query OK, 0 rows affected (0.00 sec)

mysql> DELIMITER //

mysql> CREATE TABLE `event_test`(
    ->     `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    ->     `event_scheduler_on` CHAR(3),
    ->     `current_datetime` DATETIME
    -> )//
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE EVENT IF NOT EXISTS `event_data`
    ->     ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 3 SECOND
    ->     DO CALL `data_sp`(10)//
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE PROCEDURE `data_sp`(`max_iteration` TINYINT)
    -> BEGIN
    ->     DECLARE `iteration`, `event_scheduler_off` TINYINT DEFAULT 0;
    ->     SET `event_scheduler_off` := 3;
    ->     WHILE `iteration` < `max_iteration` DO
    ->         IF `iteration` > `event_scheduler_off` THEN
    ->             SET @@GLOBAL.event_scheduler := OFF;
    ->         END IF;
    -> 
    ->         INSERT INTO `event_test` (
    ->             `event_scheduler_on`, `current_datetime`
    ->         ) VALUES (
    ->             @@GLOBAL.event_scheduler, NOW()
    ->         );
    -> 
    ->         SET `iteration` := `iteration` + 1;
    ->         DO SLEEP(1);
    ->     END WHILE;
    -> END//
Query OK, 0 rows affected (0.00 sec)

mysql> DELIMITER ;

mysql> SELECT @@GLOBAL.event_scheduler;
+--------------------------+
| @@GLOBAL.event_scheduler |
+--------------------------+
| OFF                      |
+--------------------------+
1 row in set (0.00 sec)

mysql> SELECT
    ->     `id`,
    ->     `event_scheduler_on`,
    ->     `current_datetime`
    -> FROM
    ->     `event_test`;
+----+--------------------+---------------------+
| id | event_scheduler_on | current_datetime    |
+----+--------------------+---------------------+
|  1 | ON                 | 2010-01-01 04:00:00 |
|  2 | ON                 | 2010-01-01 04:00:01 |
|  3 | ON                 | 2010-01-01 04:00:02 |
|  4 | ON                 | 2010-01-01 04:00:03 |
|  5 | OFF                | 2010-01-01 04:00:04 |
|  6 | OFF                | 2010-01-01 04:00:05 |
|  7 | OFF                | 2010-01-01 04:00:06 |
|  8 | OFF                | 2010-01-01 04:00:07 |
|  9 | OFF                | 2010-01-01 04:00:08 |
| 10 | OFF                | 2010-01-01 04:00:09 |
+----+--------------------+---------------------+
10 rows in set (0.00 sec)