解決MySQL server has gone away錯(cuò)誤的方案

PHP中MySQL server has gone away問題

一.背景

之前在Codeigniter里面寫過類似console命令行的腳本. 腳本里存在sleep語句時(shí)間比較久， 導(dǎo)致出現(xiàn)一個(gè)現(xiàn)象就是sleep之前的SQL都是操作成功的，但是sleep之后，再執(zhí)行SQL操作竟然報(bào)錯(cuò): MySQL server has gone away. 也就是mysql的這個(gè)連接失效. 后來分析才知道, MySQL中存在2個(gè)重要的配置參數(shù):

interactive_timeout
wait_timeout

這2個(gè)參數(shù)的單位都是秒(s). 默認(rèn)是8小時(shí)(28800). interactive_timeout從單詞上看指的是交互超時(shí)時(shí)間. mysql的連接方式一般分為2種, 一種稱之為"交互式", 一種稱為"非交互式". 一般常見的使用mysql -u root xxx之類的或者主從復(fù)制的連接為"交互式連接", 使用如Java的JDBC、PHP的PDO驅(qū)動(dòng)連接的方式一般是"非交互式連接". 然而interactive_timeout如果未修改的情況下，這個(gè)值是一直不會(huì)變的,但是wait_timeout在不同連接方式下，值是不一樣的.

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

最終起作用的只是wait_timeout的值.這配置項(xiàng)限定了處于sleep狀態(tài)(通過 show processlist查看當(dāng)前連接數(shù)情況)的連接,如果這個(gè)連接sleep休眠時(shí)間超過wait_timeout的值，則這個(gè)連接被斷掉或者說被清理掉.

二.wait_timeout分析

1.首先查看mysql.conf配置

首先我們配置了interactive_timeout=10 wait_timeout=5, 此時(shí)通過mysql客戶端(交互式連接)查看這2個(gè)配置項(xiàng)的值: show variables like ‘%timeout%';

2.交互式連接

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

那我們?cè)賮砜纯碢HP連接MySQL(非交互式連接)，執(zhí)行相同的語句,得到什么結(jié)構(gòu):

3.非交互式連接

此時(shí)wait_timeout是我們?cè)瓉碓趍ysql.cnf配置的值了.

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

三.gone away原因分析

結(jié)合上面的情況，我們就知道了。 一開始某些SQL執(zhí)行成功，但是后面的SQL執(zhí)行失敗報(bào)錯(cuò)gone away，大部分原因就是這個(gè)連接被閑置超過了wait_timeout，mysql服務(wù)器單方面斷掉了這個(gè)連接。但是客戶端代碼，還是在用這個(gè)連接變量，以為連接還是ok的(其實(shí)mysql server端已經(jīng)斷開了,只是我們以為這個(gè)連接還有效)，去執(zhí)行SQL必然報(bào)錯(cuò).

那么我們?cè)趺唇鉀Q這個(gè)情況呢?

1.可以適當(dāng)調(diào)整wait_timeout的值， 調(diào)大一點(diǎn)，這樣不容易觸發(fā)這個(gè)gone away情況.但是弊端就是，sleep的長(zhǎng)連接不被清理，資源白白浪費(fèi)了.

2.通過try-cach如果拋出gone way msyql的連接問題， 先把之前的db調(diào)用close().在重新獲取db連接open,然后再執(zhí)行之前的代碼. 不過代碼看起來感覺很蛋疼.偽代碼:

$db = db();

try {
   fun1$db); // 一開始執(zhí)行成功
   sleep(3600*10) // 假設(shè)sleep了10個(gè)小時(shí)
   fun2($db);   // 10小時(shí)之后 由于連接已經(jīng)被mysql干掉 導(dǎo)致報(bào)錯(cuò) gone away
}catch(Exception $e) {
  // 報(bào)錯(cuò)后 我們把無效的連接close 在 open新的連接 
  $db->close();
  $db->open();
  // 再拿到新的連接執(zhí)行
  // fun2($db)  
}

3.如果是使用類似swoole或者easyswoole框架， 建議使用mysql pool連接池的形式.并且一般連接池都有關(guān)于心跳檢查ping、連接存活檢測(cè)間隔時(shí)間設(shè)置、最大閑置連接數(shù)等等設(shè)置， 只要配置一次就好了。 例如可以配置測(cè)活連接間隔時(shí)間短一點(diǎn)，來保證連接不會(huì)被msyql服務(wù)器干掉.例如 easyswoole配置:

例如之前我設(shè)置wait_timeout=10, 但是如果我沒修改這個(gè)easyswoole的mysql連接池測(cè)活間隔時(shí)間變小， 同樣會(huì)出現(xiàn)gone way的情況. 第一次訪問接口成功返回SQL執(zhí)行結(jié)果，但是超過10s以后我再次訪問接口，報(bào)錯(cuò)mysql has gone away。修改setIntervalCheckTime()之后，就不會(huì)出現(xiàn)這個(gè)問題了. 我們通過mysql的show processlist;查看連接數(shù)情況:

這些都是easyswoole幫我們維護(hù)的連接數(shù). 當(dāng)sleep超過3秒時(shí)， 由于檢查時(shí)間是3秒存活， 連接池幫我們保活檢查， sleep的時(shí)間又從0開始計(jì)算.

下面是其他網(wǎng)友的補(bǔ)充

進(jìn)入MySQL

cmd

mysql -u用戶名 -p密碼

在我們使用mysql導(dǎo)入大文件sql時(shí)可能會(huì)報(bào)MySQL server has gone away錯(cuò)誤，該問題是max_allowed_packet配置的默認(rèn)值設(shè)置太小，只需要相應(yīng)調(diào)大該項(xiàng)的值之后再次導(dǎo)入便能成功。該項(xiàng)的作用是限制mysql服務(wù)端接收到的包的大小，因此如果導(dǎo)入的文件過大則可能會(huì)超過該項(xiàng)設(shè)置的值從而導(dǎo)致導(dǎo)入不成功！下面我們來看一下如何查看以及設(shè)置該項(xiàng)的值。

查看 max_allowed_packet 的值

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

可以看到默認(rèn)情況下該項(xiàng)的大小只有4M，接下來將該值設(shè)置成150M(1024*1024*150)

set global max_allowed_packet=157286400;

此時(shí)再查看大小

show global variables like 'max_allowed_packet';

通過調(diào)大該值，一般來說再次導(dǎo)入數(shù)據(jù)量大的sql應(yīng)該就能成功了，如果任然報(bào)錯(cuò)，則繼續(xù)再調(diào)大一些就行，請(qǐng)注意通過在命令行中進(jìn)行設(shè)置只對(duì)當(dāng)前有效，重啟mysql服務(wù)之后則恢復(fù)默認(rèn)值，但可以通過修改配置文件（可以在配置文件my.cnf中添加max_allowed_packet=150M即可）來達(dá)到永久有效的目的，可其實(shí)我們并不是經(jīng)常有這種大量數(shù)據(jù)的導(dǎo)入操作，所以個(gè)人覺得通過命令行使得當(dāng)前配置生效即可，沒有必要修改配置文件。

以上就是本次介紹的全部相關(guān)知識(shí)點(diǎn)內(nèi)容，希望腳本之家整理的內(nèi)容能夠幫助到你。