mysql - 如果我将全局 event_scheduler 关闭，运行事件会发生什么

Question

假设我在 mysql 中有一个名为 event_data 的事件，它调用一个存储过程 data_sp。 event_data 被触发，data_sp 当前正在运行。现在我在做SET GLOBAL event_scheduler = OFF; - 基本上关闭全局事件调度程序。

data_sp 的当前执行会发生什么？它会停止还是继续运行？如果它会被停止，那么数据会被提交吗？

Answer 1

作为以下概念证明，存储过程将一直运行到事件结束为止:

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)