please check my event code

[spiderwell]

hi all,

 

i wrote an event for a client's database that is supposed to run once a day, but having checked the database again, it doesnt seem to be occuring,

essentially what it does is clear out a row's info and reset it to a 'for sale status' from 'on hold' if its been on hold for more than 2 days.

what it does is working fine, its when it does it that doesn't seem to be working correctly

could anyone confirm for me if i have correctly structured the part that makes it run once a day every day.

 

many thanks

 

CREATE EVENT `checkonhold`
ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 DAY
  ON COMPLETION PRESERVE 
DO 

UPDATE `tbl_sales` 
SET 
`Sales_Status` = NULL, 
`Sales_Hold_Date` = NULL, 
`Sales_PrintRequired` = NULL, 
`Sales_Date` = NULL, 
`Sales_User_ID` = NULL, 
`Sales_GalleryDelivery_Date` = NULL, 
`Sales_RetailPrice` = NULL, 
`Sales_Currency` = NULL, 
`Sales_EuroRetailPrice` = NULL, 
`Sales_ArtistPercentage` = NULL,
`Sales_GalleryPercentage` = NULL, 
`Sales_CommissionOther` = NULL, 
`Sales_ClientName` = NULL, 
`Sales_GalleryDiscount` = NULL, 
`Sales_Billed` = NULL 
WHERE `Sales_Status` = 'Hold'
AND `Sales_Hold_Date` < date_sub( 
CURRENT_TIMESTAMP , INTERVAL 2 
DAY ) 
AND `Sales_User_ID` <> 2

 

also would there be any logs on mysql to show it is run daily, so i can further check this

[gizmola]

Try

 

ON SCHEDULE EVERY 1 DAY START NOW()

 

Did you turn the event scheduler on?

 

SET GLOBAL event_scheduler = ON

 

In doing a show processlist; you should see it. Otherwise the events will never fire.

 

Assuming everything is working, you can do this:

 

SELECT LAST_EXECUTED FROM INFORMATION_SCHEMA.EVENTS;

 

If null, the event hasn't run, otherwise you should get a timestamp.

[spiderwell]

yeah it came up NULL, and dont have permission to change the global, will be contacting the hosts right now

 

thanks for your help

[spiderwell]

will mark is solved once I have heard back and been able to check