mysql gone away pdo_使用PDO出现General error: 2006 MySQL server has gone away问题

You (or the db administrator) has killed the running thread with

a KILL statement

or a

You tried to run a query after closing the connection to the

server. This indicates a logic error in the application that should

be corrected.

A client application running on a different host does not have the

necessary privileges to connect to the MySQL server from that

host.

You got a timeout from the TCP/IP connection on the client side.

This may happen if you have been using the

commands:

You have encountered a timeout on the server side and the automatic

reconnection in the client is disabled

(the reconnect flag

in the MYSQL structure

is equal to 0).

You are using a Windows client and the server had dropped the

connection (probably because wait_timeout expired)

before the command was issued.

The problem on Windows is that in some cases MySQL does not get an

error from the OS when writing to the TCP/IP connection to the

server, but instead gets the error when trying to read the answer

from the connection.

Prior to MySQL 5.0.19, even if the reconnect flag

in the MYSQL structure

is equal to 1, MySQL does not automatically reconnect and re-issue

the query as it doesn't know if the server did get the original

query or not.

The solution to this is to either do a mysql_ping() on

the connection if there has been a long time since the last query

(this is what Connector/ODBC does) or setwait_timeout on

the mysqld server

so high that it in practice never times out.

第一点说到是被管理员杀死了线程。测试环境跑了那么长时间从来没有遇到过这种情况，不会有人主动kill某条sql，可以排除。

第二点说关闭被关闭之后还试图去查询mysql，可能是逻辑错误。如果是逻辑错误，那在首次跑的时候就会产生错误日志，这点也可以排除在外。

第三点说没有权限连接到mysql。这点也不可能，因为应用连接mysql好长一段时间了。

第四、第五、第六点都是提到客户端和服务端连接超时导致服务端断开连接。使用mysql命令 show VARIABLES like

'%timeout%'；返回如下信息

wait_timeout是28800, 也就是8个小时。比对一下错误日志的时间，有可能是这个问题。

查一下mysql wait_timeout，对其解释是:

参数含义：服务器关闭非交互连接之前等待活动的秒数。

在线程启动时，根据全局wait_timeout值或全局interactive_timeout值初始化会话wait_timeout值，取决于客户端类型(由mysql_real_connect()的连接选项CLIENT_INTERACTIVE定义)。

是否可以理解为客户端打开一个连接之后，在wait_timeout这段时间内如果没有活动，就会算超时？

做个实验看下能否重现此错误：

设置wait_timeout为5秒：set global wait_timeout=5;

然后重新跑cron脚本，等待5秒以后，出现了一样的错误，好，问题原因找到了，那该如何去解决？

一种方法是设置wait_timeout时间为很长，但是这样不靠谱，线上的mysql配置都是统一的，不能随便更改。而且即使设置再长，也会有可能超时，我们的业务需求是需要不停的跑此脚本。

那能不能在超时的时候，客户端自动重新建立一个mysql连接？

查看mysql连接类pdo，里面有一段捕获异常的代码：

try

{

$sth =

$pdo->prepare($sql);

$sth->execute($params);

} catch(Exception $e)

{

}

没错，日志的异常捕获就在这。试试能不能通过捕捉gone away的错误代码，然后再对其重连接处理。

使用$e->getCode()，返回此错误是HY000,在异常捕获的时候判断此错误编号，然后关闭连接重新new一个pdo。代码如下：

try

{

$sth =

$pdo->prepare($sql);

$sth->execute($params);

} catch(Exception $e)

{

if ($e->getCode() ==

'HY000') { // 捕获wait_timeout异常

self::close();//关闭连接

$pdo =

self::getPdo();//重新实例化一个mysql连接对象

}

}

改完之后，重新跑一下代码，没有出现错误日志，通过。