Unanswered: MySQL query: Query hangs, disappears from processlist
I'm suddenly getting this peculiar problem which I'm not sure how to debug.
After entering a particular query in Mysql client (takes around 20 minutes usually); if I check `SHOW PROCESSLIST` in another console, I can see the query there as:
6070 | admin | IP:PORT | DATABASENAME | Query | 570 | Sending data | INSERT INTO ... JOIN...
However, the query refuses to complete or throw out any errors at all even after hours and hours of waiting. Eventually it completely disappears from `SHOW PROCESSLIST`. So basically MySQL doesn't think it's running, but it STILL hasn't thrown a single error or completed in the original console. Then if I press `ctrl-c` to abort the query, I see this:
^CCtrl-C -- sending "KILL QUERY 6061" to server ...
Ctrl-C -- query aborted.
BUT the console still hangs and I don't get a prompt to do anything else.
Any idea what might be going on here? I'm completely lost. The query just refuses to complete OR fail, and MySQL doesn't even know it's running after a while.
The server in in an Amazon RDS instance, so there's all the standard RDS monitors.
Here's a few more things from my investigation:
1. The Query actually seems to complete in the expected time 20-30 mins(I will still run a few more tests and verify). The problem seems to be that the client is not informed of this fact, hence keeps waiting.
2. After exactly 2 hours of waiting, client gets a "2013 Lost connection to MySQL server during query"
3. The Number of 'connections' drops some time after the query is issued (In the latest attempt, after just 5-6 minutes of query). So I suppose the connection is lost, and hence the Client doesn't know whether the query has completed or not.