PHP MySql Query Timeout

[tweenerz]

This is one of those "gray area" php/mysql questions ...

I wrote a php class that is basically a mysql wrapper. It detects for the availability of the mysql server and for connection timeouts. If the connection fails, it will write the query (unless it was a select) to a log file to be run at a later date manually when the server becomes available again.

The problem is that, when a query locks a table, the connection doesn't fail, so the query just sits in the queue and the queue will become extremely large. I need to find a way to timeout a query, not just the connection.

To make a long question short, how can I force a timeout on a query so as to avoid losing queries that are stuck in the queue in the event of a server reboot?

I assume I can use socket connections, but I am not sure how feasible or reliable that is.

Any help is greatly appreciated.

 

[litton1]

I can think of a few solutions to this problem but somebody else may have a better one.

1) improve the database speed by getting some old stuff out of there (if there is any).
2) Improve the hardware on the database server, then the queries can run faster.
3) Is the database server dedicated to that one task? If not then you could make it dedicated.
4) you could have a separate programme running that will actually manage and run the queries such as a C program or VB (whatever rocks your boat) instead of the php script, the script could then just write each query to a different file and the external program runs constantly scanning for changes in the directory
5) a bit extreme but you could have a redundant server that helps out when things get busy

maybe somebody has a better idea?


A problem with no solution is a problem viewed from the wrong angle