Why do our queries get stuck on the state "Writing to net" in MySql?

Question

We have a lot of queries

select * from tbl_message

that get stuck on the state "Writing to net". The table has 98k rows.

The thing is... we aren't even executing any query like that from our application, so I guess the question is:

• What might be generating the query?
• ...and why does it get stuck on the state "writing to net"

I feel stupid asking this question, but I'm 99,99% sure that our application is not executing a query like that to our database... we are however executing a couple of querys to that table using WHERE statement:

 SELECT Count(*) as StrCount FROM tbl_message WHERE m_to=1960412 AND m_restid=948

 SELECT Count(m_id) AS NrUnreadMail FROM tbl_message WHERE m_to=2019422 AND m_restid=440 AND m_read=1

 SELECT * FROM tbl_message WHERE m_to=2036390 AND m_restid=994 ORDER BY m_id DESC

I have searched our application several times for select * from tbl_message but haven't found anything... But still our query-log on our mysql server is full of Select * from tbl_message queries

Answer 1

Since applications don't magically generate queries as they like, I think that it's rather likely that there's a misstake somewhere in your application that's causing this. Here's a few suggestions that you can use to track it down. I'm guessing that your using PHP, since your using MySQL, so I'll use that for my examples.

Try adding comments in front of all your queries in the application, like this:

$sqlSelect  = "/* file.php, class::method() */";
$sqlSelect .= "SELECT * FROM foo ";
$sqlSelect .= "WHERE criteria";

The comment will show up in your query log. If you're using some kind database api wrapper, you could potentially add these messages automatically:

function query($sql)
{
    $backtrace = debug_backtrace();
    // The function that executed the query
    $prev = $backtrace[1];
    $newSql = sprintf("/* %s */ ", $prev["function"]);
    $newSql .= $sql;

    mysql_query($newSql) or handle_error();
}

In case you're not using a wrapper, but rather executing the queries directly, you could use the runkit extension and the function runkit_function_rename to rename mysql_query (or whatever you're using) and intercept the queries.

Answer 2

There are (at least) two data retrieval modes for mysql. With the c api you either call mysql_store_result() or mysql_use_result().
mysql_store_result() returns when all result data is transferred from the MySQL server to your process' memory, i.e. no data has to be transferred for further calls to mysql_fetch_row().

However, by using mysql_use_result() each record has to be fetched individually if and when mysql_fetch_row() is called. If your application does some computing that takes longer than the time period specified in net_write_timeout between two calls to mysql_fetch_row() the MySQL server considers your connection to be timed out.

Answer 3

Temporarily enable the query log by putting

log=

into your my.cnf file, restart mysql and watch the query log for those mystery queries (you don't have to give the log a name, it'll assume one from the host value).