Mysql – On MariaDB event scheduler doesn’t run

mariadbmysql-event

I'm using MariaDB 10.3.22 on debian buster; I've set up a query to cleanup some expired user session from a table; this query should run every 15 minutes; the query is correct (tested and working on another identical DB but based on MySQL) but the start event is not triggered.

What I checked so far:

Event scheduler is ON

MariaDB [(none)]> show processlist;
+-----+-----------------+-----------+------+---------+-------+-----------------------------+------------------+----------+
| Id  | User            | Host      | db   | Command | Time  | State                       | Info             | Progress |
+-----+-----------------+-----------+------+---------+-------+-----------------------------+------------------+----------+
|   1 | system user     |           | NULL | Daemon  |  NULL | InnoDB purge coordinator    | NULL             |    0.000 |
|   2 | system user     |           | NULL | Daemon  |  NULL | InnoDB purge worker         | NULL             |    0.000 |
|   3 | system user     |           | NULL | Daemon  |  NULL | InnoDB purge worker         | NULL             |    0.000 |
|   4 | system user     |           | NULL | Daemon  |  NULL | InnoDB purge worker         | NULL             |    0.000 |
|   5 | system user     |           | NULL | Daemon  |  NULL | InnoDB shutdown handler     | NULL             |    0.000 |
|  37 | event_scheduler | localhost | NULL | Daemon  | 13949 | Waiting for next activation | NULL             |    0.000 |
| 411 | root            | localhost | NULL | Query   |     0 | Init                        | show processlist |    0.000 |
+-----+-----------------+-----------+------+---------+-------+-----------------------------+------------------+----------+
7 rows in set (0.000 sec)

Event inserted and enabled

MariaDB [integrity]> show events\G;
*************************** 1. row ***************************
                  Db: integrity
                Name: DELETE_EXPIRED_SESSIONS
             Definer: phpmyadmin@localhost
           Time zone: SYSTEM
                Type: RECURRING
          Execute at: NULL
      Interval value: 15
      Interval field: MINUTE
              Starts: 2019-04-10 00:00:00
                Ends: NULL
              Status: ENABLED
          Originator: 1
character_set_client: utf8mb4
collation_connection: utf8mb4_unicode_ci
  Database Collation: utf8mb4_general_ci
1 row in set (0.001 sec)

Event query seems to be ok

MariaDB [integrity]> show create event delete_expired_sessions\G;
*************************** 1. row ***************************
               Event: DELETE_EXPIRED_SESSIONS
            sql_mode: STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
           time_zone: SYSTEM
        Create Event: CREATE DEFINER=`phpmyadmin`@`localhost` EVENT `DELETE_EXPIRED_SESSIONS` ON SCHEDULE EVERY 15 MINUTE STARTS '2019-04-10 00:00:00' ON COMPLETION NOT PRESERVE ENABLE DO DELETE FROM `core_sessions` WHERE `core_sessions`.`expiry` <= NOW()
character_set_client: utf8mb4
collation_connection: utf8mb4_unicode_ci
  Database Collation: utf8mb4_general_ci

Other things to check?

Thank you for any hint…

Best Answer

First rule of *nix admin is: Logs are your friends

Looking at logfile:

2020-05-15 14:41:00 1108 [ERROR] Event Scheduler: [phpmyadmin@localhost].[integrity.DELETE_EXPIRED_SESSIONS] execution failed, user no longer has EVENT privilege.
2020-05-15 14:41:00 1108 [ERROR] Event Scheduler: [phpmyadmin@localhost][integrity.DELETE_EXPIRED_SESSIONS] Access denied for user 'phpmyadmin@localhost' to database 'integrity'
2020-05-15 14:41:00 1108 [Note] Event Scheduler: [phpmyadmin@localhost].[integrity.DELETE_EXPIRED_SESSIONS] event execution failed.

The user defined at Definer: phpmyadmin@localhost MUST be an existing user and with all privileges to access your database...

Fixed that, everything worked fine.