MySQL Query Restarting Every 60 Seconds?

Question

I have a strange problem. I'm running a MySQL query against a very large table from PHP. The query time is over a minute, but that's not my problem. It looks like PHP is resending the query every 66 seconds.

show processlist;
+--------+---------+-------------------+----------+---------+------+---------------+--------------------------------------------------------
| Id     | User    | Host              | db       | Command | Time | State         | Info                                                   
+--------+---------+-------------------+----------+---------+------+---------------+--------------------------------------------------------
| 150018 | root    | localhost         | amrs     | Query   |   32 | Sending data  | /*DEREK*/select ctlno, count(*) AS count from (omitted)

A few minutes later, I checked again:

+--------+---------+-------------------+----------+---------+------+---------------+--------------------------------------------------------
| Id     | User    | Host              | db       | Command | Time | State         | Info                                                   
+--------+---------+-------------------+----------+---------+------+---------------+--------------------------------------------------------
| 150018 | root    | localhost         | amrs     | Query   |  188 | Sending data  | /*DEREK*/select ctlno, count(*) AS count from (omitted)
| 150021 | root    | localhost         | amrs     | Query   |  122 | Sending data  | /*DEREK*/select ctlno, count(*) AS count from (omitted)
| 150023 | root    | localhost         | amrs     | Query   |   56 | Sending data  | /*DEREK*/select ctlno, count(*) AS count from (omitted)

I have not reloaded the page or anything. set_time_limit(0) is called near the beginning of the script. The annoying part is, the page seems to be linked to the most recently run one. So if I kill 150018, nothing bad happens, but if I kill 150023 before another one is spawned, the page comes up with a "Query execution interrupted" error. 150018 will eventually finish running on its own but it doesn't do any good because the script/page won't receive it.

Anyone have any ideas?

EDIT: show full processlist gives the following (with some lines removed for brevity and confidentiality):

+--------+---------+-------------------+----------+---------+-------+--------------+-----------------------------------------------------
| Id     | User    | Host              | db       | Command | Time  | State        | Info 
+--------+---------+-------------------+----------+---------+-------+--------------+-----------------------------------------------------
| 147385 | root    | localhost:44560   | amrs     | Sleep   | 14021 |              | NULL
| 150248 | root    | localhost         | NULL     | Query   |     0 | NULL         | show full processlist
| 150251 | root    | localhost         | amrs     | Query   |     1 | statistics   | /*DEREK*/select ctlno, count(*) AS count from (snip)
+--------+---------+-------------------+----------+---------+-------+--------------+-----------------------------------------------------

Answer 1

I have seen a similar problem a few times before, and your problems sound very similar to what I was experiencing working with a previous web site.

Are you going through a proxy server when going to the web site?

What was happening with my site, was that the queries from a particular set of users within the same company were being directed through their proxy server, which was deciding for itself that if no response was received within 60 seconds, then it would just issue the web request again, without informing the client browser that this was happening!

So, with certain long running queries taking over a minute to execute, I'd see the queries start to compound on each other, and I'd end up looking at a process-list with loads of identical queries running, each separated by almost exactly 60 seconds!

The resolution to this was to get the client to by-pass their proxy server for our site.

A second instance of this exact same problem was resolved when the client upgraded their proxy server to the latest version.

I'm sorry, but I can't remember what proxy server was in use in either case, as it was quite a few months ago, and I have slept since then :-/