MySQL re-query error?

Question

Background

I have a PayPal IPN handler in PHP` (5.2) that processes transactions and stores the date in a MySQL database. The site has a low activity.

Sometimes a MySQL query fails. (Very rare) I register everything, and if the PayPal message was not processed, I can just look in the log file and send the PayPal message.

Ideas / Challenges

But I was wondering if I can make the process more reliable and instead the handler will try the mysql query again before giving up.

I probably want to wait a bit - not to score a few request attempts right after each other.

I was hoping to find some patterns for this - finding things like "php mysql query retry" without having much success with this.

Questions

• Are there any good guides for this?

• Existing libraries?

• Is a simple loop and sleep between each request okay or can it have unwanted side effects? Too dear?

Answer 1

Good transaction security code in the php world is not the norm, but there are many examples if you look at java or .net code examples.

The best database code I've seen puts queries into a function and retries about 3 times before giving up and registering / queuing up. Hope this takes care of your problem.

Best practice would be to use innodb tables and put all your critical sql statements in a transaction that is either committed or canceled. Thus, your data is in a consistent state, even if one particular request fails. (note that this can also complicate locking issues if you are not careful).

You do not want to sleep long with the survey, as this may hang your users browser. They need an immediate response to confirm that the purchase has passed. Putting a job in a queue with a cron job or server daemon is another way, but it does not give the user immediate feedback and is probably unnecessary for this purpose.

If immediate attempts do not work, you have another problem with basic blocking, the reason for the failure is that you need to train.