event_scheduler
This page is part of MariaDB's Documentation.
The parent of this page is: System Variables for MariaDB Enterprise Server
Topics on this page:
Overview
Enable the event scheduler. Possible values are ON, OFF, and DISABLED (keep the event scheduler completely deactivated, it cannot be activated run-time)
USAGE
The event_scheduler
system variable can have its default value configured via SkySQL Configuration Manager.
The event_scheduler
system variable can be set in a configuration file:
[mariadb]
event_scheduler=ON
The event_scheduler
system variable can also be set dynamically at runtime by executing SET GLOBAL:
SET GLOBAL event_scheduler=ON;
When the event_scheduler
system variable is set dynamically at runtime, its value will be reset the next time the server restarts. To make the value persist on restart, set it in a configuration file too.
DETAILS
The event_scheduler
system variable controls the status of the event scheduler.
If the
event_scheduler
system variable isOFF
, then the event scheduler's thread will not exist, and no events will be executed.If the
event_scheduler
system variable isON
, then the event scheduler's thread will exist, and events will be executed as scheduled.If the
event_scheduler
system variable isDISABLED
, then the event scheduler's thread will not exist, no events will be executed, and enabling the event scheduler will require the service to be restarted.If the
event_scheduler
system variable is dynamically set to the special valueORIGINAL
, then its value will revert to the value from the last service restart.
SYNONYMS
SCHEMA
PARAMETERS
Command-line | --event_scheduler[={OFF| |
Configuration file | Supported |
Dynamic | Yes |
Scope | Global |
Data Type | ENUM (OFF, ON, DISABLED, ORIGINAL) |
Product Default Value | OFF |
SKYSQL
Cloud | SkySQL Topology | ES Ver | Service Default |
---|---|---|---|
AWS | Multi-Node Analytics | 10.6 | OFF (choices: ON, OFF) |
Single Node Analytics | 10.6 | OFF (choices: ON, OFF) | |
Replicated Transactions | 10.4 | OFF (choices: ON, OFF) | |
10.5 | OFF (choices: ON, OFF) | ||
10.6 | OFF (choices: ON, OFF) | ||
Single Node Transactions | 10.4 | OFF (choices: ON, OFF) | |
10.5 | OFF (choices: ON, OFF) | ||
10.6 | OFF (choices: ON, OFF) | ||
GCP | Multi-Node Analytics | 10.6 | OFF (choices: ON, OFF) |
Single Node Analytics | 10.6 | OFF (choices: ON, OFF) | |
Replicated Transactions | 10.4 | OFF (choices: ON, OFF) | |
10.5 | OFF (choices: ON, OFF) | ||
10.6 | OFF (choices: ON, OFF) | ||
Single Node Transactions | 10.4 | OFF (choices: ON, OFF) | |
10.5 | OFF (choices: ON, OFF) | ||
10.6 | OFF (choices: ON, OFF) |
Cloud | SkySQL Topology | ES Ver | Service Default |
---|---|---|---|
AWS | ColumnStore Data Warehouse | 10.6 | OFF (choices: OFF, ON) |
Enterprise Server With Replica(s) | 10.4 | OFF (choices: OFF, ON) | |
10.5 | OFF (choices: OFF, ON) | ||
10.6 | OFF (choices: OFF, ON) | ||
Enterprise Server Single Node | 10.4 | OFF (choices: OFF, ON) | |
10.5 | OFF (choices: OFF, ON) | ||
10.6 | OFF (choices: OFF, ON) | ||
GCP | ColumnStore Data Warehouse | 10.6 | OFF (choices: OFF, ON) |
Enterprise Server With Replica(s) | 10.4 | OFF (choices: OFF, ON) | |
10.5 | OFF (choices: OFF, ON) | ||
10.6 | OFF (choices: OFF, ON) | ||
Enterprise Server Single Node | 10.4 | OFF (choices: OFF, ON) | |
10.5 | OFF (choices: OFF, ON) | ||
10.6 | OFF (choices: OFF, ON) |
See SkySQL details in MariaDB SkySQL previous release
See SkySQL details in MariaDB SkySQL new release
PRIVILEGES
The event_scheduler
system variable requires the SUPER
privilege to dynamically set its value at runtime.
EXAMPLES
Event Scheduler Thread
If the event_scheduler
system variable is OFF
, then the event scheduler's thread will not exist:
SHOW GLOBAL VARIABLES LIKE 'event_scheduler';
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| event_scheduler | OFF |
+-----------------+-------+
SELECT ID, STATE FROM information_schema.PROCESSLIST WHERE USER='event_scheduler';
Empty set (0.001 sec)
If the event_scheduler
system variable is ON
, then the event scheduler's thread will exist:
SET GLOBAL event_scheduler=ON;
SHOW GLOBAL VARIABLES LIKE 'event_scheduler';
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| event_scheduler | ON |
+-----------------+-------+
SELECT ID, STATE FROM information_schema.PROCESSLIST WHERE USER='event_scheduler';
+----+------------------------+
| ID | STATE |
+----+------------------------+
| 59 | Waiting on empty queue |
+----+------------------------+
Creating Events
Events can be created when the event_scheduler
system variable is ON
, and the event will be executed according to its specified schedule:
SHOW GLOBAL VARIABLES LIKE 'event_scheduler';
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| event_scheduler | ON |
+-----------------+-------+
CREATE OR REPLACE TABLE test.test_event_history (
id INT AUTO_INCREMENT PRIMARY KEY,
event_ts DATETIME
);
CREATE OR REPLACE EVENT test.test_event
ON SCHEDULE
EVERY 1 HOUR
STARTS CURRENT_TIMESTAMP
DO
INSERT INTO test.test_event_history (event_ts)
VALUES (NOW());
SELECT * FROM test.test_event_history;
+----+---------------------+
| id | event_ts |
+----+---------------------+
| 1 | 2020-02-26 01:38:48 |
+----+---------------------+
Receiving Warnings when Off
When the event_scheduler
system variable is OFF
, most event-related operations will raise a warning with the ER_
SHOW GLOBAL VARIABLES LIKE 'event_scheduler';
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| event_scheduler | OFF |
+-----------------+-------+
CREATE OR REPLACE EVENT test.test_event
ON SCHEDULE
EVERY 1 HOUR
STARTS CURRENT_TIMESTAMP
DO
INSERT INTO test.test_event_history (event_ts)
VALUES (NOW());
SHOW WARNINGS;
+---------+------+----------------------------------------------------------------------------------+
| Level | Code | Message |
+---------+------+----------------------------------------------------------------------------------+
| Warning | 1105 | Event scheduler is switched off, use SET GLOBAL event_scheduler=ON to enable it. |
+---------+------+----------------------------------------------------------------------------------+
Receiving Errors when Disabled
When the event_scheduler
system variable is DISABLED
, most event-related operations will raise an error with the ER_
SHOW GLOBAL VARIABLES LIKE 'event_scheduler';
+-----------------+----------+
| Variable_name | Value |
+-----------------+----------+
| event_scheduler | DISABLED |
+-----------------+----------+
CREATE OR REPLACE EVENT test.test_event
ON SCHEDULE
EVERY 1 HOUR
STARTS CURRENT_TIMESTAMP
DO
INSERT INTO test.test_event_history (event_ts)
VALUES (NOW());
ERROR 1577 (HY000): Cannot proceed, because event scheduler is disabled
Reverting to Original Startup Value
If the event_scheduler
system variable is dynamically set to the special value ORIGINAL
, then its value will revert to the value from the last service restart. This may be useful if you believe that its value was unintentionally changed.
Let's say that the MariaDB service was restarted:
$ sudo systemctl restart mariadb
Then the following demonstrates how the special value ORIGINAL
can be used to revert any changes to the event_scheduler
system variable:
SHOW GLOBAL VARIABLES LIKE 'event_scheduler';
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| event_scheduler | OFF |
+-----------------+-------+
SET GLOBAL event_scheduler=ON;
SHOW GLOBAL VARIABLES LIKE 'event_scheduler';
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| event_scheduler | ON |
+-----------------+-------+
SET GLOBAL event_scheduler=ORIGINAL;
SHOW GLOBAL VARIABLES LIKE 'event_scheduler';
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| event_scheduler | OFF |
+-----------------+-------+
Dynamically Resetting Global Value to Default
The global value of the event_scheduler
system variable can be dynamically reset to the default value by setting it to DEFAULT
:
SET GLOBAL event_scheduler=ON;
SHOW GLOBAL VARIABLES LIKE 'event_scheduler';
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| event_scheduler | ON |
+-----------------+-------+
SET GLOBAL event_scheduler=DEFAULT;
SHOW GLOBAL VARIABLES LIKE 'event_scheduler';
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| event_scheduler | OFF |
+-----------------+-------+
Invalid Value for Variable
If a user tries to set the event_scheduler
system variable to an invalid value, then the operation will fail with the ER_
SET GLOBAL event_scheduler=-1;
ERROR 1231 (42000): Variable 'event_scheduler' can't be set to the value of '-1'
Invalid Type for Variable
If a user tries to set the event_scheduler
system variable to an invalid argument, then the operation will fail with the ER_
SET GLOBAL event_scheduler='';
ERROR 1232 (42000): Incorrect argument type to variable 'event_scheduler'
SET GLOBAL event_scheduler='infinity';
ERROR 1232 (42000): Incorrect argument type to variable 'event_scheduler'
ERROR HANDLING
FEATURE INTERACTION
RESPONSES
DIAGNOSIS
ISO 9075:2016
CHANGE HISTORY
Release Series | History |
---|---|
11.4 Enterprise |
|
10.6 Enterprise |
|
10.6 Community |
|
10.5 Enterprise |
|
10.5 Community |
|
10.4 Enterprise |
|
10.4 Community |
|
10.3 Enterprise |
|
10.3 Community |
|
10.2 Enterprise |
|
10.2 Community |
|
Release Series | History |
---|---|
11.4 Enterprise |
|
10.6 Enterprise |
|
10.5 Enterprise |
|
10.4 Enterprise |
|