Execute stored procedure every 1 day MySQL

Question

I have to empty one of my tables from my database everyday at a certain hour (lets say 00:00:00). I have this in the procedure:

    CREATE DEFINER=`root`@`%` PROCEDURE `delete_day`()
    BEGIN
       TRUNCATE TABLE qmsos.module_queuemanagement_queue;
    END

I tried making an event that calls and repeats this every day like this:

CREATE 
    EVENT `delete_queue_daily`
    ON SCHEDULE EVERY 1 DAY STARTS '2017-05-12 00:00:00' 
    ON COMPLETION PRESERVE
    DO CALL delete_day();

But it doesn't work. I tried executing it at an specific hour for only one time and it worked, but not if I want to run it every day.

Answer 1

I can't reproduce the problem.

In the basic test the stored procedure is executed every 10 seconds:

mysql> SELECT VERSION();
+-----------+
| VERSION() |
+-----------+
| 5.7.18    |
+-----------+
1 row in set (0.00 sec)

mysql> SET @@GLOBAL.event_scheduler = ON;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW VARIABLES LIKE 'event_scheduler';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| event_scheduler | ON    |
+-----------------+-------+
1 row in set (0.00 sec)

mysql> USE `qmsos`;
Database changed

mysql> DROP EVENT IF EXISTS `delete_queue_daily`;
Query OK, 0 rows affected (0.00 sec)

mysql> DROP PROCEDURE IF EXISTS `delete_day`;
Query OK, 0 rows affected (0.00 sec)

mysql> DROP TABLE IF EXISTS `module_queuemanagement_queue`,
    ->                      `audit_log`;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE IF NOT EXISTS `module_queuemanagement_queue` (
    ->   `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO `module_queuemanagement_queue`
    ->   (`id`)
    -> VALUES
    ->   (NULL), (NULL), (NULL), (NULL), (NULL); 
Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> CREATE TABLE IF NOT EXISTS `audit_log` (
    ->   `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    ->   `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    -> );
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE EVENT `delete_queue_daily`
    ->   ON SCHEDULE EVERY 10 SECOND STARTS '2017-05-12 00:00:00' 
    ->   ON COMPLETION PRESERVE
    ->   DO CALL `delete_day`;
Query OK, 0 rows affected (0.00 sec)

mysql> DELIMITER //

mysql> CREATE PROCEDURE `delete_day`()
    -> BEGIN
    ->   INSERT INTO `audit_log` (`id`) VALUES (NULL);
    ->   TRUNCATE TABLE `module_queuemanagement_queue`;
    -> END//
Query OK, 0 rows affected (0.00 sec)

mysql> DELIMITER ;

mysql> SHOW EVENTS\G
*************************** 1. row ***************************
                  Db: qmsos
                Name: delete_queue_daily
             Definer: user@127.0.1.1
           Time zone: SYSTEM
                Type: RECURRING
          Execute at: NULL
      Interval value: 10
      Interval field: SECOND
              Starts: 2017-05-12 00:00:00
                Ends: NULL
              Status: ENABLED
          Originator: 0
character_set_client: utf8mb4
collation_connection: utf8mb4_general_ci
  Database Collation: utf8mb4_general_ci
1 row in set (0.00 sec)

mysql> SELECT `id`, `created_at`
    -> FROM `audit_log`;
Empty set (0.00 sec)

mysql> SELECT `id`
    -> FROM `module_queuemanagement_queue`;
+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
|  4 |
|  5 |
+----+
5 rows in set (0.00 sec)

mysql> SELECT `id`, `created_at` FROM `audit_log`;
+----+---------------------+
| id | created_at          |
+----+---------------------+
|  1 | 2017-05-12 00:00:10 |
+----+---------------------+
1 row in set (0.00 sec)

mysql> SELECT `id` FROM `module_queuemanagement_queue`;
Empty set (0.00 sec)

Answer 2

Your event definition looks ok. Please validate that event_scheduler is enabled by executing:

SHOW VARIABLES LIKE 'event_scheduler';

If it's off, then:

SET GLOBAL event_scheduler = ON;

The global event_scheduler system variable determines whether the Event Scheduler is enabled and running on the server. It has one of these 3 values, which affect event scheduling as described here:

• OFF: The Event Scheduler is stopped. The event scheduler thread does not run, is not shown in the output of SHOW PROCESSLIST, and no scheduled events are executed. OFF is the default value for event_scheduler.

  When the Event Scheduler is stopped (event_scheduler is OFF), it can be started by setting the value of event_scheduler to ON. (See next item.)

• ON: The Event Scheduler is started; the event scheduler thread runs and executes all scheduled events.

  When the Event Scheduler is ON, the event scheduler thread is listed in the output of SHOW PROCESSLIST as a daemon process

• DISABLED: This value renders the Event Scheduler nonoperational. When the Event Scheduler is DISABLED, the event scheduler thread does not run (and so does not appear in the output of SHOW PROCESSLIST). In addition, the Event Scheduler state cannot be changed at runtime.

UPDATE

To debug try to simplify your expression, and check that the scheduler thread is running by executing:

SHOW FULL PROCESSLIST;

For ie:

CREATE 
EVENT `delete_queue_daily_debug`
ON SCHEDULE EVERY 1 DAY 
STARTS TIMESTAMP(NOW() + INTERVAL 1 MINUTE) 
DO CALL delete_day();