Scheduled and manage the Event job Service in MySQL or MariaDB

Check any event is scheduled in MySQL or MariaDB

select * from information_schema.events;

Check Event service is enabled or disabled:

show variables like '%event_scheduler%'
----------------------
Variable_name  |Value|
---------------+-----+
event_scheduler|OFF  |

-- if the event_scheduler service is working then you will find an entry in processlist otherwise no entry

Select * from information_schema.processlist where USER = 'event_scheduler';
--------------------------------------------------------------
ID|USER           |HOST           |DB           |COMMAND|TIME|
--+---------------+---------------+-------------+-------+----+
16|event_scheduler|localhost      |             |Daemon |   3|

Enable or Disable Event Service in MySQL or MariaDB:

-- Enable Event Service in MySQL or MariaDB:
SET GLOBAL event_scheduler=1

OR

SET GLOBAL event_scheduler=ON


-- Disable Event Service in MySQL or MariaDB:

SET GLOBAL event_scheduler=0

OR

SET GLOBAL event_scheduler=OFF

Syntax of MySQL or MariaDB Event Schedule

CREATE [OR REPLACE]

    [DEFINER = { user | CURRENT_USER | role | CURRENT_ROLE }]

    EVENT

    [IF NOT EXISTS]

    event_name   

    ON SCHEDULE schedule

    [ON COMPLETION [NOT] PRESERVE]

    [ENABLE | DISABLE | DISABLE ON SLAVE]

    [COMMENT 'comment']

    DO sql_statement;

 

schedule:

    AT timestamp [+ INTERVAL interval] ...

  | EVERY interval

    [STARTS timestamp [+ INTERVAL interval] ...]

    [ENDS timestamp [+ INTERVAL interval] ...]

interval:

    quantity {YEAR | QUARTER | MONTH | DAY | HOUR | MINUTE |

              WEEK | SECOND | YEAR_MONTH | DAY_HOUR | DAY_MINUTE |

              DAY_SECOND | HOUR_MINUTE | HOUR_SECOND | MINUTE_SECOND}

Example:

For Event Execute at specific time and executed once.

CREATE EVENT Myeventatspecifiedtime

ON SCHEDULE AT '2022-01-01 15:00:00.000'

DO

BEGIN

truncate table test.test;

insert into test.test values('Truncated');

END

For Event Quaterly scheduled and start on specified date ‘1st jan 2022’ but no enddate defined:

CREATE EVENT myeventQuaterlyscheduled

ON SCHEDULE EVERY 1 QUARTER STARTS '2022-01-01 21:00:00.000'

DO

BEGIN

truncate table test.test;

insert into test.test values('Truncated');

END

Scheduled Event at every hour :Scheduled Event at every hour:

CREATE EVENT Myeventeveryhour

ON SCHEDULE EVERY 1 HOUR STARTS '2022-01-01 21:00:00.000' ENDS '2022-01-31 21:00:00.000'

DO

BEGIN

truncate table test.test;

insert into test.test values('Truncated');

END

Scheduled Event which run once after 1 hour:

CREATE EVENT myeventafter1hrs

   ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 HOUR

   DO

   truncate table test.test;

Check the scheduled event details:

select event_name,event_type,execute_at,status from information_schema.events

event_name      |event_type|execute_at             |status |

----------------+----------+-----------------------+-------+

myeventafter1hrs|ONE TIME  |2022-03-09 22:59:50.000|ENABLED|

Drop Event:

DROP EVENT eventname;

Show Event command:

SHOW CREATE EVENT myeventafter1hrs \G


MariaDB [classicmodels]> show create event myeventafter1hrs \G

*************************** 1. row ***************************

               Event: myeventafter1hrs

            sql_mode: IGNORE_SPACE, STRICT_TRANS_TABLES, ERROR_FOR_DIVISION_BY_ZERO, NO_AUTO_CREATE_USER, NO_ENGINE_SUBSTITUTION

           time_zone: SYSTEM

        Create Event: CREATE DEFINER=`root`@`localhost` EVENT `myeventafter1hrs` ON SCHEDULE AT '2022-03-09 22:59:50' ON COMPLETION NOT PRESERVE ENABLE DO truncate table test.test

character_set_client: utf8mb4

collation_connection: utf8mb4_unicode_ci

  Database Collation: latin1_swedish_ci

1 row in set (0.000 sec)