How to create an event that runs every 24 hours?

Question

I need this to be run every 24 hours:

delete tags from tags left join tagowners on tags.id=tagowners.tagId
where tagowners.tagId is null;

Answer 1

Execute this first in SQl Query Browser

SET GLOBAL event_scheduler = ON;

then execute this one. It will trigger every 24 hours at 12:00 AM

CREATE EVENT event1
  ON SCHEDULE EVERY '1' DAY
  STARTS '2013-01-21 00:00:00'    
DO 
delete tags from tags left join tagowners on tags.id=tagowners.tagId
    where tagowners.tagId is null;

Answer 2

CREATE EVENT cleartags
    ON SCHEDULE EVERY 24 HOUR
    DO 
      delete tags from tags left join tagowners on tags.id=tagowners.tagId
    where tagowners.tagId is null;

Answer 3

Running on a Linux system? Use cron. I don't think MySQL has built-in functionality to accomplish this.

Answer 4

If you're on a linux server you can create a cronjob, a scheduled task, to execute the php script through the php executable. Creating a cron task is easy, execute 'crontab -e' through the shell, then add your command to the bottom of the file.

Example cron entries taken from http://mkaz.com/ref/unix_cron.html

#Run command at 7:00am each weekday [mon-fri]
00 07 * * 1-5 mail_pager.script 'Wake Up'

#Run command on 1st of each month, at 5:30pm
30 17 1 * * pay_rent.script

#Run command at 8:00am,10:00am and 2:00pm every day
00 8,10,14 * * * do_something.script

#Run command every 5 minutes during market hours
*/5 6-13 * * mon-fri get_stock_quote.script

#Run command every 3-hours while awake
0 7-23/3 * * * drink_water.script 

If you wanted to execute a php script once a day...

0 0 * * * /path/to/php.exe myscript.php

Remember, you're executing the script through the CLI, so the $_GET/$_POST/$_SERVER super globals won't exist (you can get around this by using wget).

If you're on windows, you can use windows task scheduler to accomplish the same.

Answer 5

With cron:

mysql -uUSER -pPWD -hDB-HOSTNAME/IPADDRESS -e "delete tags from tags left join tagowners on tags.id=tagowners.tagId where tagowners.tagId is null;"