Search code examples
javamysqlsqlserver-side

Execute MySQL statement in the background automatically

I want to delete records from MySQL table which were not updated for longer than 3 minutes. How can I set the timer in the background to manage it without being invoked by events or methods in java? Is that possible?

DELETE FROM bus WHERE created_at < (NOW() - INTERVAL 5 MINUTE)
Solution

  • As @abhishek-ghosh correctly pointed out, you can use CREATE EVENT.

    CREATE EVENT event_bus_delete
   EVERY 5 MINUTE
   DO 
      DELETE FROM bus WHERE created_at < (NOW() - INTERVAL 5 MINUTE);

    Event support was added in MySQL 5.1.6. However MySQL Event Scheduler is not running by default and needs to be enabled in order for events to work.

    See this StackOverflow answer or How to Configure MySQL Event Scheduler article on how to enable MySQL Event Scheduler and make sure it's running.

    You can ensure the scheduler starts when MySQL is launched with the command-line option --event-scheduler=ON or setting event_scheduler=ON in your MySQL configuration file (my.cnf or my.ini on Windows).

    Alternatively, you can start the scheduler from the MySQL command line:

    SET GLOBAL event_scheduler = ON;