Mysql: what is mysql wait_timeout, net_read_timeout and net_write_timeout variable?

Question

I do a bulk insert and get an error like Mysql2::Error: Lost connection to MySQL server during query:

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

I searched the web about net_read_timeout but did not get any article / blog that describes this in an understandable language. on mysql, net_read_timeout is described as "The number of seconds to wait for more data from a connection before aborting the read" . I am completely confused by this expression and do not get it.

I also want to learn about the variable net_write_timeout and wait_timeout.

Thanks,

Answer 1

MySQL uses different temporary variables for different stages.

• When a connection is established, it uses connection_timeout
• When it waits for the next request, it uses wait_timeout
• When he does not receive the request at a specific time, he uses net_read_timeout and net_write_timeout
• And so on...

Normally net_read_timeout should not be a problem, but when you have network problems, especially when communicating with the server, this timeout may be raised because instead of a single packet for the query that you sent to the database, MySQL expects the entire query to be read, but due to a network problem it will not receive the rest of the request. MySQL does not allow the client to talk to the server until the query result has been completely retrieved.

You cannot correctly change these two variables, which in turn are session variables.

Also from MySQL Doc you can read

net_read_timeout :

The number of seconds to wait for more data from the connection before interrupting reads. When the server reads from the client, net_read_timeout is the timeout value that controls the moment of interruption. When the server writes to the client, net_write_timeout is the timeout value that controls when to interrupt. See Also slave_net_timeout.

net_write_timeout :

The number of seconds to wait for a block to write to the connection before the recording is interrupted. See also net_read_timeout.

You can check the default variable in MySQL itself using

> mysql show variables like '%timeout';

Answer 2

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

set wait_timeout = 10;

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

To test it, wait 10 seconds, and then execute any SQL query that will give you an error, for example, "closed mysql connection during query execution"

will update my answer for net_read_timeout and net_write_timeout soon.

Answer 3

This happens when you use an unbuffered connection to the mysql database, and after executing your query, you are not using the data. client python connects to non-buffering SSDictCursor

 connection = pymysql.connect(host='localhost', user='xxxx', password='xxxx', db='employees', charset='utf8mb4', cursorclass=pymysql.cursors.SSDictCursor) sql = " select * from employees" cursor = connection.cursor() cursor.execute(sql) cursor.fetchone() 

do nothing, and your connection will be disconnected or if you do not extract all the data within 60 seconds (net_write_timeout), your connection will be disconnected.
quote from the document: When the server writes to the client, net_write_timeout is a timeout value that determines when to abort

2019-08-14T15: 20: 26.465498Z 28440 Request for selection * from employees

2019-08-14T15: 21: 26.584634Z 28440 [Note] Connection to database 28440 was interrupted: "employees", user: "xxxx", host: "localhost" (timed out while writing communication packets)