In my previous post I mentioned you might need to increase net_write_timeout to avoid connection being aborted and now I think I should have better explained that.
MySQL uses a lot of different timeout variables at different stages. For example when connection is just being established connect_timeout is used. When server waits for another query to be sent to it wait_timeout
(or interactive_timeout for applications which specified they are interactive during connection). This value is rather large as it is rather typical for some applications to have long delays between queries.
If query is being read or result set is being sent back, much shorter net_read_timeout and net_write_timeout are used.
net_read_timeout rarely becomes the problem unless you have extremely poor network, but when you’re in trouble anyway, because in most cases query is generated and sent as single packet to the server and application can’t switch doing something else and leaving server with partial query received.
Result set however comes back in multiple pieces and if you’re using mysql_use_result you can do any work between fetches, which potentially could take a lot of time, so increasing net_write_timeout could be the good idea.
This for example typical cause of aborted connections while using Sphinx with large data sets and large buffers. While indexing Sphinx performs sorts and flushes buffers to the disk every so often which can take long enough to trigger default net_write_timeout on the server side.
You could ask why server does not do any flow control and can’t find out client just is just busy and it is not network issue - well this comes from simplicity of MySQL protocol which does not allow client to talk to the server until full result set is fetched. This is also the reason why you can’t stop fetching for ordinary statements and mysql_free_result will have to complete the fetch internally.
Is there any way to tell the server you need more time besides increasing net_write_timeout ? Not what I know of. You can’t use something like mysql_ping because connection is in the stage of getting the data. You can’t even fetch couple of rows every few seconds to show you’re fetching data because there is buffering happening inside MySQL client library so you never know when real network read will happen.
As net_write_timeout and net_read_timeout are session level variables you can simply change them per connections for connections which could have timeout issues otherwise without affecting the rest of the server, so it seems like fine solution.


MySQL net_write_timeout vs wait_timeout and protocol notes - Read More...