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: [email protected]
           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)