Execute MySQL statement in the background automatically

Question

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)

Which dbms are you using? (This is not ANSI SQL, so you'll need product specific functionality, if it can be done.) — jarlh, May 06 '15 at 10:23
@jarlh: I am using mysql. can you give me an example for such product with specific functionality? — The Time, May 06 '15 at 10:30
Please visit this link : http://dev.mysql.com/doc/refman/5.1/en/create-event.html — Abhishek Ghosh, May 06 '15 at 10:33

DaveH · Answer 1 · 2015-05-06T11:22:40.067

0

Edit

Misread your question - the following describes how you can use Java to schedule the task rather than doing it independently of the Java layer

You can achieve this by using a combination of Timer and TimerTask. Together they form quite a simple scheduling facility. The Timer schedules TimerTasks that occur periodically.

There are a number of good tutorials on the web : for example

edited May 06 '15 at 11:22

answered May 06 '15 at 11:14

DaveH

7,187
5
32
53

I know but my problem how can I invoke it in the background without events or triggers in my class. In the example it is being invoked from the `main` method? – The Time May 06 '15 at 11:25
Well, the code has to be invoked from *somewhere*, but the Timer creates a separate thread of execution that just runs in the background of your application, leaving you application to respond to any other events that are initiated by the user. So, when you application starts up, create the Timer, schedule the TimerTask and forget about it. – DaveH May 06 '15 at 11:29

score 0 · Accepted Answer · edited May 23 '17 at 11:58

0

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;

edited May 23 '17 at 11:58

Community

1
1

answered May 06 '15 at 11:46

Gyrocode.com

57,606
14
150
185

How can I set the event? I tried like this `SET GLOBAL event_scheduler = ON;` and is not work? – The Time May 06 '15 at 15:35
What version of MySQL are you using? Do you get any console errors when running `SET GLOBAL event_scheduler = ON;`? Do you see `event_scheduler` user in the output of `SHOW PROCESSLIST;` command? – Gyrocode.com May 06 '15 at 17:03

Execute MySQL statement in the background automatically

2 Answers2