Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2014
    Posts
    4

    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:

    Code:
        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:

    Code:
        ^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.

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    have you looked at the mySQL log files?
    ..specifically the slow query log , but wexamine all to see if there is a diagnostic message as to what is going on
    http://www.pontikis.net/blog/how-and...ble-mysql-logs

    at 20 minutes my suspicion us you are tripping a rogue query timeout. what are you using to moinitor you MySQL servers?
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Aug 2014
    Posts
    4
    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.

    Code:
    mysql> SHOW VARIABLES LIKE '%_timeout'
            -> ;
        +-----------------------------+----------+
        | Variable_name               | Value    |
        +-----------------------------+----------+
        | connect_timeout             | 10       |
        | delayed_insert_timeout      | 300      |
        | innodb_flush_log_at_timeout | 1        |
        | innodb_lock_wait_timeout    | 50       |
        | innodb_rollback_on_timeout  | OFF      |
        | interactive_timeout         | 28800    |
        | lock_wait_timeout           | 31536000 |
        | net_read_timeout            | 300      |
        | net_write_timeout           | 60       |
        | rpl_stop_slave_timeout      | 31536000 |
        | slave_net_timeout           | 3600     |
        | wait_timeout                | 28800    |
        +-----------------------------+----------+

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •