Page 1 of 1

MySQL event not working

Posted: Wed Nov 13, 2013 10:16 am
by rhecker
I am trying to create an event in MySQL that runs once per hour and will delete records that have a timestamp of at least an hour older than the current time minus two hours; however, the script I have written does not accomplish this. The idea is to purge invoice transactions that have not completed within a reasonable amount of time.

With the following script, if I set the interval to 1 hour, it will sometimes delete records too soon, and if I set the interval to 2 hours, it simply never deletes the record.


DELETE FROM invoice WHERE invoice.commit_invoice = '0' AND invoice.invoice_time > (NOW() - interval 2 HOUR)

Re: MySQL event not working

Posted: Wed Nov 13, 2013 1:28 pm
by requinix
You very rarely should ever actually delete data from a database. Especially something important like an invoice - completed or not, that's good information to keep around.
If you don't want them showing up somewhere then modify whatever query is used so that it doesn't include "old" (however you define that) incomplete invoices.

Re: MySQL event not working

Posted: Wed Nov 13, 2013 6:28 pm
by rhecker
Thanks, but that wasn't an answer to my question.

Anyone else?

Re: MySQL event not working

Posted: Mon Nov 25, 2013 9:56 pm
by rhecker
No one here knows how to write a simple MySQL event script?

I understand the value of retaining data and simply changing a field value to something like "deleted". It doesn't change my fundamental question.

Re: MySQL event not working

Posted: Tue Nov 26, 2013 12:24 am
by requinix
I do, but you asked for someone else to help you.

Code: Select all

invoice.invoice_time > (NOW() - interval 2 HOUR)
That will delete things where the invoice_time is after two hours ago. It should be < as in "where the invoice_time is before/older than two hours ago".

As for running automatically, are you using cron or do you have this code running someplace in the normal code for your site?

Re: MySQL event not working

Posted: Tue Nov 26, 2013 12:09 pm
by Christopher
I agree with requinix, you should not delete records like these. That you are deleting them indicates there is probably a design problem with your code.

That said, it should be:

Code: Select all

DELETE FROM invoice WHERE invoice.commit_invoice = '0' AND invoice.invoice_time>DATE_SUB(NOW(), INTERVAL 2 HOUR)
Better would be something like:

Code: Select all

UPDATE invoice SET invoice.status='Error' WHERE invoice.commit_invoice = '0' AND invoice.invoice_time>DATE_SUB(NOW(), INTERVAL 2 HOUR)

Re: MySQL event not working

Posted: Tue Nov 26, 2013 3:42 pm
by Weirdan
requinix wrote: As for running automatically, are you using cron or do you have this code running someplace in the normal code for your site?
Recent MySQL versions include their own event scheduling mechanism: http://dev.mysql.com/doc/refman/5.1/en/events.html . This is what OP refers to.

Re: MySQL event not working

Posted: Tue Nov 26, 2013 5:59 pm
by requinix
Weirdan wrote:
requinix wrote: As for running automatically, are you using cron or do you have this code running someplace in the normal code for your site?
Recent MySQL versions include their own event scheduling mechanism: http://dev.mysql.com/doc/refman/5.1/en/events.html . This is what OP refers to.
Since five point one? Wow... I'm already imagining the things you could do with it.