Using MySQL Events in Practice

For those who actively use MySQL, it is no secret that since version 5.1, MySQL supports events. If you need to execute queries or separate procedures on a schedule, and there was no time for laziness to switch from launching the console to the MySQL built-in functionality , welcome to cat.

First, let us remind ourselves what events are in MySQL and how to prepare them?
First of all, events provide cross-platform, as they do not require any external applications. Inside the event, you can run SQL commands or just call pre-written procedures.
In our case, we will be engaged in archiving a rapidly swelling table in which user requests are logged.

Turn on the scheduler

The event variable is controlled by the event_scheduler global variable . For MySQL later than 5.1.11, it can take one of 3 values:
OFF (it can also be 0 ): The scheduler is stopped. The scheduler thread does not execute and is not shown in the output of SHOW PROCESSLIST. No scheduled events are being executed. OFF is the default value for event_scheduler.
ON (may also be 1 ): The scheduler is running. The scheduler thread executes itself and executes all scheduled events. The event scheduler thread is listed in the output of SHOW PROCESSLIST as a background DISABLED process
: value makes the scheduler inactive. The scheduler thread is not running and is not displayed in the output of SHOW PROCESSLIST.

We are interested in the enabled state, so you need to register in the config

event_scheduler=1

or run the command

SET GLOBAL event_scheduler=ON;

Create an event

I liked it better just to call the procedure inside the event. It is also possible because it was already created and launched by the application itself;) So, let's create a procedure:

CREATE DEFINER = 'root'@'localhost' PROCEDURE `new_proc`()
    NOT DETERMINISTIC
    CONTAINS SQL
    SQL SECURITY DEFINER
    COMMENT ''
BEGIN
DECLARE tbl_tmp,tbl_logarch VARCHAR(50);
-- tbl_log будет названием архивируемой таблицы
-- мы хотим получить название архивной таблицы tbl_log_<дата>_<время>
SET tbl_logarch=DATE_FORMAT(CURRENT_TIMESTAMP, '%Y%m%d_%H%i');
SET tbl_tmp=CONCAT("tbl_log_", tbl_logarch);
-- формируем SQL запрос на создание архивной таблицы;
SET @archive_query:=CONCAT("CREATE TABLE ", tbl_tmp, " ENGINE=ARCHIVE AS (SELECT * FROM tbl_log)");
-- выполняем подготовленный запрос
PREPARE archive_query FROM @archive_query;
EXECUTE archive_query;
DEALLOCATE PREPARE archive_query;
-- удаляем данные из основной таблицы, в моем случае без всяких условий
DELETE FROM tbl_log;
END;

Now run the procedure and see if it works

call new_proc();

If everything is in order, then continue. Create an event directly. The simplified syntax looks like this: or I needed to archive 1 time per week, so my DDL looks like this:
CREATE EVENT event_name ON SCHEDULE AT {DATE AND TIME} DO {SQL COMMAND};


CREATE EVENT event_name ON SCHEDULE EVERY {X} {SECOND|MINUTE|HOUR|DAY|MONTH|YEAR|WEEK} DO {SQL COMMAND};


CREATE EVENT `new_event`
  ON SCHEDULE EVERY 1 WEEK STARTS CURRENT_TIMESTAMP
  ON COMPLETION NOT PRESERVE
  ENABLE
  COMMENT ''  DO
call new_proc();

We observe the result

Having played with time, I was convinced that the created event does exactly what I expected. Another step towards automation has been taken. As a bonus, several additional material on the topic has been studied.

PS Just in case, I draw attention to the fact that I tried to present only the practical part. Therefore, I deliberately omitted the description of the extended syntax, user privileges, restrictions depending on the versions of MySQL. If you, dear habrachelove, did not manage to create and use the event according to the above instructions, you can always write about it in habrakoment (I will try to answer) or find a solution on other useful resources.

The writing process used materials:
dev.mysql.com/doc/refman/5.1/en/events.html
www.rldp.ru/mysql/mysqlpro/events.htm

UPD: They say that you can organize the interaction of the event planner and the operating system using the federated table and MySQL Proxy. I have not tried it myself. Who is in the subject, write pliz in comments.