What is MySQL's wait_timeout, net_read_timeout and net_write_timeout variable?

Question

I am doing bulk inserting and getting error as Mysql2::Error: Lost connection to MySQL server during query:

I searched for this error on the Internet and most of the blogs/articles asking to increase net_read_timeout value.

I searched on the Internet about net_read_timeout but not getting any article/blog which describes it in easy to understandable language. On MySQL website net_read_timeout is describe as "The number of seconds to wait for more data from a connection before aborting the read". I am totally confused with this statement and not getting it.

I also want to know about net_write_timeout and wait_timeout variable.

Answer 1

MySQL uses different timeout variables for various stages.

• When connection is established it uses connection_timeout
• When it waits for the next query it uses wait_timeout
• When it doesn't receive the query in the specific time it uses net_read_timeout and net_write_timeout
• And so on...

Usually net_read_timeout shouldn't be a problem but when you have some network trouble, especially when communicating with the server this timeout could be raised because instead of a single packet for the query, that you sent to the Database, MySQL waits for the entire query to be read but, due to the network problem, it doesn't receive the rest of the query. MySQL doesn't allow client to talk the server until the query result is fetched completely.

You cannot properly change those two variable, which are sessions variable after all.

Also from the MySQL Doc you can read

net_read_timeout:

The number of seconds to wait for more data from a connection before aborting the read. When the server is reading from the client, net_read_timeout is the timeout value controlling when to abort. When the server is writing to the client, net_write_timeout is the timeout value controlling when to abort. See also slave_net_timeout.

net_write_timeout:

The number of seconds to wait for a block to be written to a connection before aborting the write. See also net_read_timeout.

You can check the defaults variable within MySQL itself using

> mysql show variables like '%timeout';

Answer 2

I understood about wait_timeout settings. mysql default wait_timeout is 28800 seconds which 8 hours. now to understand how wait_timeout works execute following sql statement.

set wait_timeout = 10;

After executing above statement if mysql server has not received any sql statement withing 10 seconds then it will automatically close the connection.

To test it wait for 10 seconds and then execute any sql query it will give you error like "mysql closed connection during query execution"

will update my answer for net_read_timeout and net_write_timeout shortly.