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)
MySQL event not working
Moderator: General Moderators
Re: MySQL event not working
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.
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
Thanks, but that wasn't an answer to my question.
Anyone else?
Anyone else?
Re: MySQL event not working
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.
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
I do, but you asked for someone else to help you.
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?
Code: Select all
invoice.invoice_time > (NOW() - interval 2 HOUR)As for running automatically, are you using cron or do you have this code running someplace in the normal code for your site?
- Christopher
- Site Administrator
- Posts: 13596
- Joined: Wed Aug 25, 2004 7:54 pm
- Location: New York, NY, US
Re: MySQL event not working
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:
Better would be something like:
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)Code: Select all
UPDATE invoice SET invoice.status='Error' WHERE invoice.commit_invoice = '0' AND invoice.invoice_time>DATE_SUB(NOW(), INTERVAL 2 HOUR)(#10850)
Re: MySQL event not working
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.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?
Re: MySQL event not working
Since five point one? Wow... I'm already imagining the things you could do with it.Weirdan wrote: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.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?