mysql报错ERROR 2013 (HY000): Lost connection to MySQL server during query
MySQL官方提供的参考方案 http://dev.mysql.com/doc/refman/5.7/en/error-lost-connection.html
B.5.2.3 Lost connection to MySQL server
通常这是网络连接问题引起的，如果这种情况频繁发生，你应该检查网络情况。如果错误信息包含"during query"，这很可能是下述情况引起的：
1、查询中大量数据被发送，由于数据传输时间不够导致，可以增加net_read_timeout的值。
2、初次连接时，连接时间设定太少，可以增加connect_timeout的值改善。
3、有些少见的情况可以show global status like 'aborted_connets'，这个全局变量在每一次服务器终止时会增加1，查看"reading authorization packet"获取错误信息。
4、BLOB值太大的问题，调整配置文件max_allowed_packet。
就更新大量的数据来说，可以进行两个方面的设置：将系统变量net_read_timeout设置得大一点，再将配置文件中的max_allowed_packet设置大一点。但是问题依然没有得到解决。可能的原因是这条查询语句真的太大了，而且我读的是机械硬盘，所以设置无法满足。
最终我将这条语句进行了拆分，将500W+的数据拆分成6次更新，问题得到了解决，而且每次更新的时间并不久，这从侧面说明了出现上述问题应该是数据传输量方面的问题。
https://dev.mysql.com/doc/refman/5.7/en/error-lost-connection.html
B.4.2.3 Lost connection to MySQL server
There are three likely causes for this error message.
Usually it indicates network connectivity trouble and you should check the condition of your network if this error occurs frequently. If the error message includes “ during query,” this is probably the case you are experiencing.
Sometimes the
“
during query” form happens when millions of rows are being sent as part of one or more queries. If you know that this is happening, you should try increasing
net_read_timeout from its default of 30 seconds to 60 seconds or longer, sufficient for the data transfer to complete.
More rarely, it can happen when the client is attempting the initial connection to the server. In this case, if your
connect_timeout value is set to only a few seconds, you may be able to resolve the problem by increasing it to ten seconds, perhaps more if you have a very long distance or slow connection. You can determine whether you are experiencing this more uncommon cause by using
SHOW GLOBAL STATUS LIKE 'Aborted_connects'. It will increase by one for each initial connection attempt that the server aborts. You may see
“
reading authorization packet” as part of the error message; if so, that also suggests that this is the solution that you need.
If the cause is none of those just described, you may be experiencing a problem with
BLOB values that are larger than
max_allowed_packet, which can cause this error with some clients. Sometime you may see an
ER_NET_PACKET_TOO_LARGE error, and that confirms that you need to increase
max_allowed_packet.
How to fix Error Code 2013 Lost connection to MySQL server
If you spend time running lots of MySQL queries, you might come across the
Error Code: 2013. Lost connection to MySQL server during query. This article offers some suggestions on how to avoid or fix the problem.
Why this happens
This error appears when the connection between your MySQL client and database server times out. Essentially, it took too long for the query to return data so the connection gets dropped.
Most of my work involves content migrations. These projects usually involve running complex MySQL queries that take a long time to complete. I’ve found the WordPress wp_postmeta table especially troublesome because a site with tens of thousands of posts can easily have several hundred thousand postmeta entries. Joins of large datasets from these types of tables can be especially intensive.
Avoid the problem by refining your queries
In many cases, you can avoid the problem entirely by refining your SQL queries. For example, instead of joining all the contents of two very large tables, try filtering out the records you don’t need. Where possible, try reducing the number of joins in a single query. This should have the added benefit of making your query easier to read. For my purposes, I’ve found that denormalizing content into working tables can improve the read performance. This avoids time-outs.
Re-writing the queries isn’t always option so you can try the following server-side and client-side workarounds.
Server-side solution
If you’re an administrator for your MySQL server, try changing some values. The MySQL
documentation suggests increasing the
net_read_timeout or
connect_timeout values on the server.
Client-side solution
You can increase your MySQL client’s timeout values if you don’t have administrator access to the MySQL server.
MySQL Workbench
You can edit the SQL Editor preferences in MySQL Workbench:
- In the application menu, select Edit > Preferences > SQL Editor.
- Look for the MySQL Session section and increase the DBMS connection read time out value.
- Save the settings, quite MySQL Workbench and reopen the connection.
Navicat
How to edit Navicat preferences:
- Control-click on a connection item and select Connection Properties > Edit Connection.
- Select the Advanced tab and increase the Socket Timeout value.
Command line
On the command line, use the
connect_timeout variable.
Python script
If you’re running a query from a Python script, use the connection argument:
con.query('SET GLOBAL connect_timeout=6000')
修改wait_timeout 的值
更改方法，需同时更改如下2个参数（单位--秒）：
wait_timeout=86400
interactive_timeout=86400
在默认情况下，如果未发生任何事，8小时后服务器将关闭连接。也可以在启动mysqld时，通过设置wait_timeout变量更改时间限制。
interactive_timeout：
服务器关闭交互式连接前等待活动的秒数。交互式客户端定义为在mysql_real_connect()中使用CLIENT_INTERACTIVE选项的客户端。又见wait_timeout。
wait_timeout：
服务器关闭非交互连接之前等待活动的秒数。
我碰到的问题是报错：ERROR 2013 (HY000): Lost connection to MySQL server during query，我的环境为：
1、MySQL在docker中安装，我在docker所在的服务器linux上连接mysql，
无论用EIP还是docker分配的IP，10分钟后不会出现问题，在docker容器里边肯定不会出现问题
2、如果我用我的笔记本电脑通过eip连接docker中的mysql，那么10分钟后就会自动断开，相关参数已经设置过了，感觉不是这些参数的问题：
set global connect_timeout=6000; set global net_read_timeout=6000; set global wait_timeout=2880000; set global interactive_timeout=2880000; set global max_allowed_packet = 500M; connect_timeout=6000 net_read_timeout=6000 wait_timeout=2880000 interactive_timeout=2880000 SELECT @@connect_timeout,@@net_read_timeout,@@wait_timeout,@@interactive_timeout;
笔记本电脑：
直接服务器：
解决：
/etc/sysctl.conf net.ipv4.tcp_keepalive_intvl = 20 net.ipv4.tcp_keepalive_probes = 3 net.ipv4.tcp_keepalive_time = 30000 sysctl -p --服务器端 /etc/ssh/sshd_config ClientAliveInterval 60 ClientAliveCountMax 1000 TCPKeepAlive yes systemctl restart sshd -- 客户端 #linux在/etc/ssh目录下 #windows在C:\\Users\\chinasoft_lhrxxt/.ssh/config 或 C:\ProgramData\ssh\ssh_config /etc/ssh/ssh_config ServerAliveInterval 60 ssh root@121.36.78.6 -o StrictHostKeyChecking=no -o TCPKeepAlive=yes -o serveraliveinterval=60 ssh root@121.36.78.6 -o ServerAliveInterval=60
About Me
|
........................................................................................................................ ● 本文作者：小麦苗，部分内容整理自网络，若有侵权请联系小麦苗删除 ● 本文在itpub、博客园、CSDN和个人微 信公众号（ DB宝）上有同步更新 ● 本文itpub地址： http://blog.itpub.net/26736162 ● 本文博客园地址： http://www.cnblogs.com/lhrbest ● 本文CSDN地址： https://blog.csdn.net/lihuarongaini ● 本文pdf版、个人简介及小麦苗云盘地址： http://blog.itpub.net/26736162/viewspace-1624453/ ● 数据库笔试面试题库及解答： http://blog.itpub.net/26736162/viewspace-2134706/ ● DBA宝典今日头条号地址： http://www.toutiao.com/c/user/6401772890/#mid=1564638659405826 ........................................................................................................................ ● QQ群号： 230161599 、618766405 ● 微 信群：可加我微 信，我拉大家进群，非诚勿扰 ● 联系我请加QQ好友 （ 646634621 ），注明添加缘由 ● 于 2020-04-01 06:00 ~ 2020-04-30 24:00 在西安完成 ● 最新修改时间：2020-04-01 06:00 ~ 2020-04-30 24:00 ● 文章内容来源于小麦苗的学习笔记，部分整理自网络，若有侵权或不当之处还请谅解 ● 版权所有，欢迎分享本文，转载请保留出处 ........................................................................................................................ ● 小麦苗的微店： https://weidian.com/s/793741433?wfr=c&ifr=shopdetail ● 小麦苗出版的数据库类丛书： http://blog.itpub.net/26736162/viewspace-2142121/ ● 小麦苗OCP、OCM、高可用网络班： http://blog.itpub.net/26736162/viewspace-2148098/ ● 小麦苗腾讯课堂主页： https://lhr.ke.qq.com/ ........................................................................................................................ 使用 微 信客户端扫描下面的二维码来关注小麦苗的微 信公众号（ DB宝）及QQ群（DBA宝典）、添加小麦苗微 信， 学习最实用的数据库技术。
........................................................................................................................ |
 |
|