解决SQLSTATE[HY000]: General error: 2006 MySQL server has gone away问题的方法

环境:PHP7 MySQL5.7 PDO连接

场景

我有一个脚本需要记录执行时长,开始时写入记录,结束时更新时间。中间无DB交互。

问题

当MySQL连接量上来时,常发生脚本结束时更新时间不成功的问题。报错如:ERROR 2006 (HY000): MySQL server has gone away。

分析

1:开始时写入记录没问题,排除DB的连接。
2:另一个脚本在执行期间有读表操作,没有出现这个问题。

排查

1:首先排查是不是MySQL连接数达到上限,通过granfana查看数据报表后发现,日常连接数峰值在500左右,并没有达到配置所显示的最大连接数,查看MySQL连接数的命令

查看允许连接数:show variables like ‘max_connections’;//注意权限问题,尽量用root,
查看当前连接数:show processlist;//用户只能看到自己的连接,root可以看到所有用户的连接
查看峰值连接数:show global status like ‘Max_used_connections’;

2:排查超时时间的设置,是否超过了我们所设定的超时时间。通过命令查看,超时时间wati_timeout设置为默认值8小时,我们的20分钟远远没有达到

show variables like ‘%timeout%’;
ps:interactive_timeout是交互模式的超时设置,不在本文讨论之列

相关概念:MySQL连接管理器:连接管理器的作用是管理和维持所有MySQL客户端的请求连接,当我们向MySQL发起请求时,连接管理器会负责创建连接并校验用户的权限。对于已经建立的连接,如果没有太久没有发送请求,连接管理器会自动断开连接,我们可以通过设置变量wait_timeout决定多久断开不活跃的连接。

3:如果使用的云服务器,安全组可能也会有限制,关键词:阿里云MySQL及Redis灵异断连现象:安全组静默丢包解决方法。阿里云会断开长时间闲置的TCP连接。我排除了这个原因,因为在测试环境是自建服务器,没有做连接时间限制。
4:查看被MySQL主动kill掉的连接数,看下是不是被其他的程序误杀了

show global status like ‘Com_kill’;

5:根据分析推测,很有可能是长时间没有数据交互请求,被MySQL当作垃圾连接回收了。

确认:

1:show processlist;查看当前的MySQL连接,发现虽然超过20分钟没有请求的连接被回收了,但是还是会显示在连接列表,说明wait_timeout 8小时生效,连接不会被杀掉,但是因为负载,连接器还是将连接标记为断开
2:根据另一个脚本来推断,长时间没有请求是主要问题之一

解决方案1

发送心跳包,每隔5分钟发送一个空包,如’select 1’给MySQL,来激活这条连接的状态,说明这个连接正在被使用,不要回收
解决方案1的优势和缺点:
优势是代价小,占用了一个连接,不会由于到时候申请不到连接而出现一致性问题。
缺点是连接数占用了,而且属于闲置状态,造成了资源的浪费,而且如果有其它的业务需求连接,申请不到连接,也会有影响。

解决方案2

开始时写入记录,写完记录把连接close掉(如mysql_close()或者是PDO的$resource=null;),下次再申请一个新的连接用来更新数据。
解决方案2的优势和缺点:
优势是资源得到了释放,如果其他资源需要连接,这个连接可以让出去,让MySQL连接器去回收这个连接。缺点是如果申请的时候连接数满了,日志就记录的不完整。

综合考虑后我选择了方案2,当然这个根据自己的业务需求来定。

使用方案2遇到的问题

先说PDO连接的关闭

根据官方文档,如$resource = new \PDO();只需要将$resource置为null即可($resource=null);
但是PDO有个特性(其他连接方式没有测),就是当资源被占用时,无法关闭连接。

举例:
$resource = new \PDO();
 $b = $resource;
 $resource=null;
这样则无法关闭连接,因为连接资源被$b占用。
类似如$result = $resouce->query($sql);这个$result也会占用一个连接资源。
这个可以通过debug扩展来排查

debug_zval_dump($resource);//输出:object(PDO)#83 (0) refcount(5){} 这里,refcount(num)代表被引用的次数。要把这些引用的地方都置为null才能真正的断开连接。
debug_backtrace() //输出文件名,类名,方法名,属性名,可以打印程序走过来的路由来排查引用的位置

这里基本遇到死角,因为引用的地方太多了,各个容器,私有属性,很难统一关闭连接。

解决方法:这里我们可以先把这个主资源给释放了,$resouce=null;,然后重新new \PDO(),来生成一个新的数据库连接,这时候旧的连接就会自动回收。(不再关心被引用的对象,只要主连接被回收,就没问题PS:如果将引用连接置为null是不行的)

举例:

<?php
//对象存储
class A{
    public $connection = null;
    public function __construct(){
                //这里省略配置项,具体查看PHP手册
        $dsn="$dbms:host=$host;port=$port;dbname=$dbName";
        try {
            $this->connection = new PDO($dsn, $user, $pass); //初始化一个PDO对象
            echo "connect Success<br/>";
        } catch (PDOException $e) {
            die ("Error!: " . $e->getMessage() . "<br/>");
        }
    }
}
$b = new A();//B连接
sleep(5);//留时间在MySQL观察连接状态
$c = $b->connection;//引用赋值,C连接
$b->connection = null;//关闭PDO连接(因为C的引用,这里只是做了标记,没有真正回收连接)
echo '新对象';
$d = new A();//创建D连接,同时,由于B连接身上有标记,将B连接替换掉,复制出的C连接也被一并替换(如果没有标记,就是2条连接,不会发生替换)
sleep(5);//留时间在MySQL观察连接状态
我们同步在MySQL查看show processlist;能清楚的看到b连接被d连接更新掉了。问题解决。

enjoy it!0人点赞PHP

打赏作者

《解决SQLSTATE[HY000]: General error: 2006 MySQL server has gone away问题的方法》上有2条评论

发表评论

邮箱地址不会被公开。