An introduction to creating and managing scheduled events, which are named database objects containing SQL statements to be executed by the Event Scheduler.
Events are named database objects containing SQL statements that are to be executed at a later stage, either once off, or at regular intervals.
They function very similarly to the Windows Task Scheduler or Unix cron jobs.
Creating, modifying or deleting events requires the EVENT privilege.
Events are created with the CREATE EVENT statement.
Events are only executed if the event scheduler is running. This is determined by the value of the system variable, which needs to be set to On for the event scheduler to be running.
You can check if the Event scheduler is running with:
If the event scheduler is not running and event_scheduler has been set to OFF, use:
to activate it. If event_scheduler has been set to Disabled, you cannot change the value at runtime. Changing the value of the event_scheduler variable requires the SUPER privilege.
Since , setting the system variable will also try to reload the if it was not properly loaded at startup.
A list of current events can be obtained with the statement. This only shows the event name and interval - the full event details, including the SQL, can be seen by querying the , or with .
If an event is currently being executed, it can be seen by querying the , or with the statement.
An event can be changed with the statement.
Events are dropped with the statement. Events are also automatically dropped once they have run for the final time according to their schedule, unless the ON COMPLETION PRESERVE clause has been specified.
This page is licensed: CC BY-SA / Gnu FDL
CREATE EVENT test_event
ON SCHEDULE EVERY 1 MINUTE DO
UPDATE test.t1 SET a = a + 1;SHOW PROCESSLIST;
+----+-----------------+-----------+------+---------+------+-----------------------------+------------------+----------+
| Id | User | Host | db | Command | Time | State | Info | Progress |
+----+-----------------+-----------+------+---------+------+-----------------------------+------------------+----------+
| 40 | root | localhost | test | Sleep | 4687 | | NULL | 0.000 |
| 41 | root | localhost | test | Query | 0 | init | SHOW PROCESSLIST | 0.000 |
| 42 | event_scheduler | localhost | NULL | Daemon | 30 | Waiting for next activation | NULL | 0.000 |
+----+-----------------+-----------+------+---------+------+-----------------------------+------------------+----------+SET GLOBAL event_scheduler = ON;SHOW EVENTS\G;
*************************** 1. row ***************************
Db: test
Name: test_event
Definer: root@localhost
Time zone: SYSTEM
Type: RECURRING
Execute at: NULL
Interval value: 1
Interval field: MINUTE
Starts: 2013-05-20 13:46:56
Ends: NULL
Status: ENABLED
Originator: 1
character_set_client: utf8
collation_connection: utf8_general_ci
Database Collation: latin1_swedish_ciSHOW CREATE EVENT test_event\G
*************************** 1. row ***************************
Event: test_event
sql_mode:
time_zone: SYSTEM
Create Event: CREATE DEFINER=`root`@`localhost` EVENT `test_event` ON SCHEDULE EVERY 1 MINUTE STARTS '2013-05-20 13:46:56' ON COMPLETION NOT PRESERVE ENABLE DO UPDATE test.t1 SET a = a + 1
character_set_client: utf8
collation_connection: utf8_general_ci
Database Collation: latin1_swedish_ciALTER EVENT test_event ON SCHEDULE EVERY '2:3' DAY_HOUR;DROP EVENT test_event;
Query OK, 0 rows affected (0.00 sec)Utilize the Event Scheduler in MariaDB Server to automate tasks. Learn how to create, manage, and schedule events to execute SQL statements at specified intervals or times.
A list of restrictions associated with the Event Scheduler, including the inability to return result sets and specific date range limitations.
The following restrictions apply to Events:
All of the restrictions listed in Stored Routine Limitations.
Events cannot return a result set.
Event names are case insensitive, so it's not possible to define two events in the same database if their case insensitive names will match. This restriction has applied since MariaDB/MySQL 5.1.8. If you are upgrading from an older version of MySQL, and have events that could clash, these events need to be renamed before the upgrade.
Events do not support dates beyond the maximum that can be represented in the Unix epoch (2038-01-19).
Events cannot be created, dropped or altered by another stored program, trigger or event.
Events cannot create, drop or alter stored programs or triggers
Event timings cannot be strictly predicted. The intervals MONTH, YEAR_MONTH, QUARTER, and YEAR are all resolved in months. All others are resolved in seconds. A delay of up to two seconds is possible in extreme cases, and events scheduled to run at the same second cannot be executed in a given order. The LAST_EXECUTED column in the table will however always be accurate to within a second.
A new connection is used for each execution of statements within the body of an event, so the session counts for such as Com_delete and Com_select will not reflect these.
Because the Event Scheduler depends on grant tables for its functionality, it is automatically disabled when the server is running with .
This page is licensed: CC BY-SA / Gnu FDL
Learn how to modify the characteristics of an existing event, such as its schedule, body, or enabled status, without dropping and recreating it.
Modifies one or more characteristics of an existing event.
The ALTER EVENT statement is used to change one or more of the characteristics of an existing without the need to drop and recreate it. The syntax for each of the DEFINER, ON SCHEDULE, ON COMPLETION,COMMENT, ENABLE / DISABLE, and DO clauses is exactly the same as when used with .
This statement requires the privilege. When a user executes a successful ALTER EVENT statement, that user becomes the definer for the affected event.
(In MySQL 5.1.11 and earlier, an event could be altered only by its definer, or by a user having the privilege.)
ALTER EVENT works only with an existing event:
This page is licensed: GPLv2, originally from
ALTER
[DEFINER = { user | CURRENT_USER }]
EVENT event_name
[ON SCHEDULE schedule]
[ON COMPLETION [NOT] PRESERVE]
[RENAME TO new_event_name]
[ENABLE | DISABLE | DISABLE ON SLAVE]
[COMMENT 'comment']
[DO sql_statement]ALTER EVENT no_such_event ON SCHEDULE EVERY '2:3' DAY_HOUR;
ERROR 1539 (HY000): Unknown event 'no_such_event'ALTER EVENT myevent
ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 2 HOUR
DO
UPDATE myschema.mytable SET mycol = mycol + 1;