Lost connection to MySQL server 或者 MySQL server has gone away解决

本文主要是介绍Lost connection to MySQL server 或者 MySQL server has gone away解决，对大家解决编程问题具有一定的参考价值，需要的程序猿们随着小编来一起学习吧！

前话

错误原因

Mysql has gone away 或者 Lost connection to MySQL server 或者 Error while sending QUERY packet

1. MySQL 服务宕了
2. mysql连接超时 show global variables like ‘%timeout’;
3. mysql请求链接进程被主动kill show global status like ‘com_kill’;
4. Your SQL statement was too large. show global variables like ‘max_allowed_packet’;

制造错误

造一个表

DROP TABLE IF EXISTS `nt_vm_message_idempotent`;
CREATE TABLE `nt_vm_message_idempotent`  (
  `uid` mediumint(15) NOT NULL DEFAULT 0,
  `message_id` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '' COMMENT '消息ID',
  `message_content` varchar(2000) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '' COMMENT '消息内容',
  `product_status` tinyint(1) UNSIGNED NOT NULL DEFAULT 0 COMMENT '是否生产成功到mq',
  `consume_status` tinyint(1) NOT NULL COMMENT '是否消费成功',
  `create_time` int(10) UNSIGNED NOT NULL DEFAULT 0,
  `update_time` int(10) UNSIGNED NOT NULL DEFAULT 0,
  PRIMARY KEY (`message_id`) USING BTREE,
  UNIQUE INDEX `unique_message_id`(`message_id`) USING BTREE,
  INDEX `uid`(`uid`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

然后造一个程序

public function actionInsert(){

try{
            //设置你的MYSQL的wait_timeout = 30，interactive_timeout也要设置，否则不生效 为了测试
            Yii::app()->db->createCommand('SET SESSION wait_timeout = 30;SET SESSION interactive_timeout = 30;')->execute();
            $result = Yii::app()->db->createCommand("show global variables like '%timeout'")->queryAll();
            print_r($result);
            $sql = "insert into  nt_vm_message_idempotent ( `uid` , `message_id` , `message_content` , `product_status`,`consume_status` , `create_time`, `update_time`) values 
                    ('98468', '100', 'asdfsda', 1,0,1,2)";
            Yii::app()->db->createCommand($sql)->execute();
            sleep(31);
            $sql = "UPDATE nt_vm_message_idempotent SET consume_status=1,update_time=1623412774 WHERE message_id = '100'";
            //再加上@符号抑制错误 要不下面捕捉不到
            @Yii::app()->db->createCommand($sql)->execute();
}catch (Exception $e){
            echo $e->getMessage();
            if(strpos($e->getMessage(),'UPDATE nt_vm_message_idempotent SET') !== false){
                //yii重连数据库 需要解决这个问题需要打开注释
                //Yii::app()->db->setActive(false);
                $sql = "UPDATE nt_vm_message_idempotent SET consume_status=1,update_time=1623412774 WHERE message_id = '100'";
                @Yii::app()->db->createCommand($sql)->execute();
            }

}

执行的时候就会抛异常：

exception 'CDbException' with message 'CDbCommand failed to execute the SQL statement: SQLSTATE[HY000]: General error: 2006 MySQL server has gone away. The SQL statement executed was: INSERT INTO `t_biubiu` (`value`) VALUES (:yp0). Bound with :yp0='insert8'' in E:\phpWorks\framework\db\CDbCommand.php:362

sleep有效果了。

大部分情况就是超时导致的，尤其是脚本执行时间太长

解决办法

这篇关于Lost connection to MySQL server 或者 MySQL server has gone away解决的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！