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

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

1. In the application menu, select  Edit > Preferences > SQL Editor.
2. Look for the  MySQL Session section and increase the  DBMS connection read time out value.
3. Save the settings, quite MySQL Workbench and reopen the connection.

Navicat

How to edit Navicat preferences:

1. Control-click on a connection item and select  Connection Properties > Edit Connection.
2. 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寶典今日頭條號地址： ........................................................................................................................ ● 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 ● 文章內容來源於小麥苗的學習筆記，部分整理自網路，若有侵權或不當之處還請諒解 ● 版權所有，歡迎分享本文，轉載請保留出處 ........................................................................................................................ ● 小麥苗的微店： ● 小麥苗出版的資料庫類叢書： http://blog.itpub.net/26736162/viewspace-2142121/ ● 小麥苗OCP、OCM、高可用網路班： http://blog.itpub.net/26736162/viewspace-2148098/ ● 小麥苗騰訊課堂主頁： https://lhr.ke.qq.com/ ........................................................................................................................ 使用 微 信客戶端掃描下面的二維碼來關注小麥苗的微 信公眾號（ DB寶）及QQ群（DBA寶典）、新增小麥苗微 信， 學習最實用的資料庫技術。 ........................................................................................................................