How to schedule MySQL Query using MySQL event in phpMyAdmin?

To set Event scheduler in Databse, your MySQL server needs to have event scheduler enabled AND have EVENT privileges for the database user.

Go to PhpMyAdmin then run Below SQL query which will tell you about your scheduler status.

SELECT @@event_scheduler;

To enable event scheduler if it’s disabled try calling SET GLOBAL event_scheduler := 1; You need the SUPER privilege for this to succeed.

Above Query will make your scheduler status ON.

Once you have invoked your scheduler you can see it in the process list. By the following command:

SHOW PROCESSLIST;

Now we can proceed and create our EVENT. Here is how I created Event for the solution of my problem.

I want to delete all records from tx_candle_domain_model_candle table after every Three days whose crdate (created date which is in TIME format in MySQL ) more than Three days than Current day.

Here is how I created Event for the solution of my problem.

CREATE EVENT delete_candle_data

ON SCHEDULE EVERY 3 DAY

DO

DELETE FROM tx_candle_domain_model_candle where crdate < UNIX_TIMESTAMP(timestampadd(day, -3, now())) ORDER BY crdate DESC;

You can refer official documentation of MySQL to create events. There are various parameters that can be used to create an EVENT for your need.

Now you have created an event and if you want to see your events in future just fire a simple command:

SHOW EVENTS;

It will list all the events with details about each event.

There is also one Events tab is created in that Database at top. Where you can see your event list and status also.

  

To update your event MySQl offers you ALTER command. You can update the working of event by manipulating the SQL query, you can also change the schedule or event running time.

In my example I am changing my Event (delete_candle_data) run time. It will run the event once – one hour after the ALTER command is fired.

ALTER EVENT delete_candle_data

ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 HOUR

Further if you want to DROP the created event in future, you can simply use the command:

DROP EVENT delete_candle_data;

I hope you have understood MySQL events and how to work with them. You can also use MySQL events with PHP to schedule things like publishing blog post in  your website etc. I highly recommend you to once go through the official documentation. Share the simple tutorial in order to help others.

Of course you can also create cron job for this purpose. Simply create cron file by putting DELETE query command and put it on server and set time for that url. That will also work.

Have a great Day Ahead 🙂