MySQL event not working

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
rhecker
Forum Contributor
Posts: 178
Joined: Fri Jul 11, 2008 5:49 pm

MySQL event not working

Post 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)
User avatar
requinix
Spammer :|
Posts: 6617
Joined: Wed Oct 15, 2008 2:35 am
Location: WA, USA

Re: MySQL event not working

Post 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.
rhecker
Forum Contributor
Posts: 178
Joined: Fri Jul 11, 2008 5:49 pm

Re: MySQL event not working

Post by rhecker »

Thanks, but that wasn't an answer to my question.

Anyone else?
rhecker
Forum Contributor
Posts: 178
Joined: Fri Jul 11, 2008 5:49 pm

Re: MySQL event not working

Post 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.
User avatar
requinix
Spammer :|
Posts: 6617
Joined: Wed Oct 15, 2008 2:35 am
Location: WA, USA

Re: MySQL event not working

Post 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?
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Re: MySQL event not working

Post 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)
(#10850)
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Re: MySQL event not working

Post 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.
User avatar
requinix
Spammer :|
Posts: 6617
Joined: Wed Oct 15, 2008 2:35 am
Location: WA, USA

Re: MySQL event not working

Post 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.
Post Reply