My site has a video gallery in which only videos 2 weeks old or less should be displayed. Previously I was running a simple update query that ran whenever ANY user came to this page as follows:
$unixtime = time();
$query = mysqli_query($cxn, "UPDATE vids SET status = 'inactive' WHERE status = 'active' AND 1209600 < ($unixtime - uploadtime)");
I recently became aware of events and stored procedures, and figured this would save many queries by simply running this query as an event once every 24 hours at a specific time (and removing the query above from the page where this query is currently found). I'm using phpMyAdmin v.4.1.8 (shared hosting) and MySQL 5.1.73 (event support seems to have existed since MySQL 5.1.6) and cannot get it to work! Here's my query (since the query does not return anything, I considered this an "event", though I know a "procedure" also doesn't need to return anything):
CREATE DEFINER=`mysite`@`localhost`
EVENT `videodeactivate`
ON SCHEDULE EVERY 1 DAY
STARTS '2015-02-21 10:45:00'
ON COMPLETION PRESERVE ENABLE
DO
UPDATE myschema.vids SET status = 'inactive'
WHERE status = 'active'
AND 1209600 < (UNIX_TIMESTAMP() - uploadtime);
(Note: 1209600 seconds = 2 weeks)
The update is not occurring, although the event has definitely been scheduled (I see it as a little red-outline clock right above "Tables" for this database, and when I click the clock the procedure is labeled as ENABLED). I have edited the event to remove "myschema" (so UPDATE vids SET....) and have even removed the AND clause. No errors are being thrown, but the event doesn't seem to run.
Why is this event, which definitely appears to be "scheduled" in phpMyAdmin as far as I can tell, not running? If all else fails I can manually run a query to deactivate these 2+ week old videos, but I would like to see this event work.
