mysql：mysql 的 wait_timeout、net_read_timeout 和 net_write_timeout 变量是什么？

mysql: what is mysql's wait_timeout,net_read_timeout and net_write_timeout variable?

提问by user2274074

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

我正在做批量插入并收到错误 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_timeoutvalue.

我在互联网和大多数要求增加net_read_timeout价值的博客/文章上搜索了这个错误。

I searched on the internet about net_read_timeoutbut 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.

我在互联网上搜索了有关net_read_timeout但没有得到任何以易于理解的语言描述它的文章/博客。在 mysql 网站上 net_read_timeout 被描述为"The number of seconds to wait for more data from a connection before aborting the read". 我完全对这个声明感到困惑并且没有得到它。

I also want to know about net_write_timeout and wait_timeout variable.

我也想知道 net_write_timeout 和 wait_timeout 变量。

Thanks,

谢谢，

回答by Claudio Ludovico Panetta

MySQL uses different timeout variables for various stages.

MySQL 在各个阶段使用不同的超时变量。

• 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_timeoutand net_write_timeout
• And so on...

• 建立连接后，它使用 connection_timeout
• 当它等待下一个查询时，它使用 wait_timeout
• 当它在它使用的特定时间没有收到查询net_read_timeout并且net_write_timeout
• 等等...

Usually net_read_timeoutshouldn'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.

通常net_read_timeout应该不是问题，但是当您遇到一些网络问题时，尤其是在与服务器通信时，可能会引发此超时，因为您发送到数据库的不是查询的单个数据包，而是 MySQL 等待整个查询被读取，但由于网络问题，它不会收到查询的其余部分。在完全获取查询结果之前，MySQL 不允许客户端与服务器对话。

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

您无法正确更改这两个变量，毕竟它们是会话变量。

Also from the MySQL Doc you can read

也可以从 MySQL Doc 中阅读

net_read_timeout:

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_read_timeout 是控制何时中止的超时值。当服务器写入客户端时，net_write_timeout 是控制何时中止的超时值。另请参阅 slave_net_timeout。

net_write_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.

在中止写入之前等待将块写入连接的秒数。另请参阅 net_read_timeout。

You can check the defaults variable within MySQL itself using

您可以使用以下命令检查 MySQL 本身中的默认变量

> mysql show variables like '%timeout';

> mysql show variables like '%timeout';

回答by user2274074

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.

我了解 wait_timeout 设置。mysql 默认 wait_timeout 是 28800 秒，即 8 小时。现在要了解 wait_timeout 的工作原理，请执行以下 sql 语句。

set wait_timeout = 10;

设置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.

执行上述语句后，如果 mysql 服务器在 10 秒内没有收到任何 sql 语句，它将自动关闭连接。

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"

要测试它等待 10 秒，然后执行任何 sql 查询，它会给你类似“查询执行期间 mysql 关闭连接”的错误

will update my answer for net_read_timeout and net_write_timeout shortly.

将很快更新我对 net_read_timeout 和 net_write_timeout 的回答。

回答by mao andy

This is happening when you are using the non-buffering connection to the mysql database, and after executing your query, you are not consuming the data. client python connect with non-buffering SSDictCursor

当您使用到 mysql 数据库的非缓冲连接时会发生这种情况，并且在执行查询后，您没有使用数据。客户端 python 与非缓冲 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()

doing nothing and your connection will be timed out or if not fetching all data within 60 seconds(net_write_timeout), your connection will be aborted.
quote from doc: When the server is writing to the client, net_write_timeout is the timeout value controlling when to abort

什么都不做，您的连接将超时，或者如果没有在 60 秒（net_write_timeout）内获取所有数据，您的连接将被中止。
来自 doc 的引用：当服务器写入客户端时，net_write_timeout 是控制何时中止的超时值

2019-08-14T15:20:26.465498Z 28440 Query select * from employees

2019-08-14T15:20:26.465498Z 28440 查询 select * from 员工

2019-08-14T15:21:26.584634Z 28440 [Note] Aborted connection 28440 to db: 'employees' user: 'xxxx' host: 'localhost' (Got timeout writing communication packets)

2019-08-14T15:21:26.584634Z 28440 [注意] 中止连接 28440 到 db: 'employees' user: 'xxxx' host: 'localhost' （写入通信数据包超时）