event_scheduler

This page is part of MariaDB's Documentation.

The parent of this page is: System Variables for MariaDB Enterprise Server

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 is OFF, then the event scheduler's thread will not exist, and no events will be executed.
If the event_scheduler system variable is ON, then the event scheduler's thread will exist, and events will be executed as scheduled.
If the event_scheduler system variable is DISABLED, 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 value ORIGINAL, then its value will revert to the value from the last service restart.

SYNONYMS

SCHEMA

PARAMETERS

Command-line	--event_scheduler[={OFF\|ON\|DISABLED\|ORIGINAL}]
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_UNKNOWN_ERROR error code:

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_EVENTS_DB_ERROR error code:

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_WRONG_VALUE_FOR_VAR error code:

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_WRONG_TYPE_FOR_VAR error code:

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
10.6 Enterprise	Present starting in MariaDB Enterprise Server 10.6.4-1.
10.6 Community	Present starting in MariaDB Community Server 10.6.0.
10.5 Enterprise	Present starting in MariaDB Enterprise Server 10.5.3-1.
10.5 Community	Present starting in MariaDB Community Server 10.5.0.
10.4 Enterprise	Present starting in MariaDB Enterprise Server 10.4.6-1.
10.4 Community	Present starting in MariaDB Community Server 10.4.0.
10.3 Enterprise	Present starting in MariaDB Enterprise Server 10.3.16-1.
10.3 Community	Present starting in MariaDB Community Server 10.3.0.
10.2 Enterprise	Present starting in MariaDB Enterprise Server 10.2.25-1.
10.2 Community	Present starting in MariaDB Community Server 10.2.0.

Release Series	History
10.6 Enterprise	Present starting in MariaDB Enterprise Server 10.6.4-1.
10.5 Enterprise	Present starting in MariaDB Enterprise Server 10.5.3-1.
10.4 Enterprise	Present starting in MariaDB Enterprise Server 10.4.6-1.