解決MySQL server has gone away錯誤的解決方案

PHP中MySQL server has gone away問題

一.背景

 

之前在Codeigniter裡面寫過類似console命令列的指令碼. 指令碼里存在sleep語句時間比較久， 導致出現一個現象就是sleep之前的SQL都是操作成功的，但是sleep之後，再執行SQL操作竟然報錯: MySQL server has gone away. 也就是mysql的這個連線失效. 後來分析才知道, MySQL中存在2個重要的配置引數:

interactive_timeout
wait_timeout

這2個引數的單位都是秒(s). 預設是8小時(28800). interactive_timeout從單詞上看指的是互動超時時間. mysql的連線方式一般分為2種, 一種稱之為"互動式", 一種稱為"非互動式". 一般常見的使用mysql -u root xxx之類的或者主從複製的連線為"互動式連線", 使用如Java的JDBC、PHP的PDO驅動連線的方式一般是"非互動式連線". 然而interactive_timeout如果未修改的情況下，這個值是一直不會變的,但是wait_timeout在不同連線方式下，值是不一樣的.

wait_timeout在"互動式連線"下, 其值是interactive_timeout的值. 如果在"非互動式連線"情況下, 則wait_timeout的值是原來mysql.cnf中配置的原始值.

最終起作用的只是wait_timeout的值.這配置項限定了處於sleep狀態(通過 show processlist檢視當前連線數情況)的連線,如果這個連線sleep休眠時間超過wait_timeout的值，則這個連線被斷掉或者說被清理掉.

二.WAIT_TIMEOUT分析

 

1.首先檢視mysql.conf配置

首先我們配置了interactive_timeout=10 wait_timeout=5, 此時通過mysql客戶端(互動式連線)檢視這2個配置項的值: show variables like ‘%timeout%';

2.互動式連線

客戶端的結果: wait_timeout竟然不是我們msyql.conf配置的10s， 而是 5s.

那我們再來看看PHP連線MySQL(非互動式連線)，執行相同的語句,得到什麼結構:

3.非互動式連線

此時wait_timeout是我們原來在mysql.cnf配置的值了.

綜上所述: wait_timeout這個值，在不同的"連線模式"下面，拿到的值是不一樣的.

三.GONE AWAY原因分析

 

結合上面的情況，我們就知道了。 一開始某些SQL執行成功，但是後面的SQL執行失敗報錯gone away，大部分原因就是這個連線被閒置超過了wait_timeout，mysql伺服器單方面斷掉了這個連線。但是客戶端程式碼，還是在用這個連線變數，以為連線還是ok的(其實mysql server端已經斷開了,只是我們以為這個連線還有效)，去執行SQL必然報錯.

那麼我們怎麼解決這個情況呢?

1.可以適當調整wait_timeout的值， 調大一點，這樣不容易觸發這個gone away情況.但是弊端就是，sleep的長連線不被清理，資源白白浪費了.

2.通過try-cach如果丟擲gone way msyql的連線問題， 先把之前的db呼叫close().在重新獲取db連線open,然後再執行之前的程式碼. 不過程式碼看起來感覺很蛋疼.虛擬碼:

3.如果是使用類似swoole或者easyswoole框架， 建議使用mysql pool連線池的形式.並且一般連線池都有關於心跳檢查ping、連線存活檢測間隔時間設定、最大閒置連線數等等設定， 只要配置一次就好了。 例如可以配置測活連線間隔時間短一點，來保證連線不會被msyql伺服器幹掉.例如 easyswoole配置:

例如之前我設定wait_timeout=10, 但是如果我沒修改這個easyswoole的mysql連線池測活間隔時間變小， 同樣會出現gone way的情況. 第一次訪問介面成功返回SQL執行結果，但是超過10s以後我再次訪問介面，報錯mysql has gone away。修改setIntervalCheckTime()之後，就不會出現這個問題了. 我們通過mysql的show processlist;檢視連線數情況:

這些都是easyswoole幫我們維護的連線數. 當sleep超過3秒時， 由於檢查時間是3秒存活， 連線池幫我們保活檢查， sleep的時間又從0開始計算.

下面是其他網友的補充

 

進入MySQL

cmd

mysql -u使用者名稱 -p密碼

在我們使用mysql匯入大檔案sql時可能會報MySQL server has gone away錯誤，該問題是max_allowed_packet配置的預設值設定太小，只需要相應調大該項的值之後再次匯入便能成功。該項的作用是限制mysql服務端接收到的包的大小，因此如果匯入的檔案過大則可能會超過該項設定的值從而導致匯入不成功！下面我們來看一下如何檢視以及設定該項的值。

檢視 max_allowed_packet 的值

show global variables like 'max_allowed_packet';
+--------------------+---------+
| Variable_name | Value |
+--------------------+---------+
| max_allowed_packet | 4194304 |
+--------------------+---------+

可以看到預設情況下該項的大小隻有4M，接下來將該值設定成150M(1024*1024*150)

set global max_allowed_packet=157286400;

此時再檢視大小

show global variables like 'max_allowed_packet';

通過調大該值，一般來說再次匯入資料量大的sql應該就能成功了，如果任然報錯，則繼續再調大一些就行，請注意通過在命令列中進行設定只對當前有效，重啟mysql服務之後則恢復預設值，但可以通過修改配置檔案（可以在配置檔案my.cnf中新增max_allowed_packet=150M即可）來達到永久有效的目的，可其實我們並不是經常有這種大量資料的匯入操作，所以個人覺得通過命令列使得當前配置生效即可，沒有必要修改配置檔案。

香港伺服器