mysql错误码:2006-MySQL server has gone away详解

1.背景

代码测试过程中发现程序运行一段时间之后mysql数据库就报2006的错误码，程序重启之后恢复正常。

2.产生原因与解决方案一（mysql长连接）

2.1.mysql_ping与mysql长连接（此方案适用于解决代码开发中遇到的mysql错误码:2006—治标又治本）

重点参考有"//20190730 changlq add 修复mysql长连接长时间没有操作会被自动关闭的bug，错误信息：2006:MySQL server has gone away"的两块代码

#include<stdio.h>
#include<stdlib.h>
#include <string.h>
#include<mysql/mysql.h>
void free_conn(MYSQL *mysql){
	mysql_close(mysql);
	free(mysql);
}
int main(){
  /* gcc -o mysql_eg mysql_eg.c -L /usr/lib64/mysql -lmysqlclient */
  MYSQL *my_con=(MYSQL *)malloc(sizeof(MYSQL));
  my_con=mysql_init(my_con);
  if(!my_con){
    printf("mysql_init error\n");
    return -1;
  }
  my_con=mysql_real_connect(my_con,"db_ip","db_name","db_pass","db_name",3306,NULL,0);
  if(my_con){
    printf("connect success!\n");
    
    //20190730 changlq add 修复mysql长连接长时间没有操作会被自动关闭的bug，错误信息：2006:MySQL server has gone away
    char value = 1;
    int mo_flag=mysql_options(my_con, MYSQL_OPT_RECONNECT, &value);
    if(mo_flag!=0){
    	printf("mysql_options set fail\n");
    }
    
  }else{
  	printf("connect fail![errno:%d][error:%s][sqlstat:%s]\n",mysql_errno(my_con),mysql_error(my_con),mysql_sqlstate(my_con));
  }
  
  int i=0;
  char *alo_sql_str=(char *)malloc(2048);
  while(i++<10){
  	//20190730 changlq add 修复mysql长连接长时间没有操作会被自动关闭的bug，错误信息：2006:MySQL server has gone away
  	//20190730 changlq add 每次循环都监听一次数据库连接
	int mp_local_flag=mysql_ping(my_con);
	if(mp_local_flag!=0){
      printf("mysql_ping local operation fail");
    }
    
    /* 一次执行多条语句+读完整个resault集 */
    memset(alo_sql_str,0x00,2048);
    sprintf(alo_sql_str,"LOCK TABLES test_table WRITE;INSERT INTO test_table VALUES(10);UNLOCK TABLES");
    // 执行多条语句：功能打开-多语句执行-功能关闭
    mysql_set_server_option(my_con,MYSQL_OPTION_MULTI_STATEMENTS_ON);
    int res=mysql_query(my_con,alo_sql_str);
    mysql_set_server_option(my_con,MYSQL_OPTION_MULTI_STATEMENTS_OFF);
    if(res != 0){
      printf("insert fail,msg:[%s]", alo_sql_str );
      free(alo_sql_str);
      free_conn(my_con);
      return -1;
    }
    // 当使用执行多语句功能后，一定要读完整个resault集，否则会出现错误：erron:2014 error:Commands out of sync; you can't run this command now 
    MYSQL_RES    *my_res; //查询结果
    do
    {
        my_res = mysql_store_result( my_con );
        if(my_res) mysql_free_result(my_res);
    }while( !mysql_next_result(my_con) );  /* more my_con? -1 = no, >0 = error, 0 = yes (keep looping) */
  }
  free(alo_sql_str);
  free_conn(my_con);
  return 0;
}

2.2.调整数据库相关参数阈值（此方案适用于临时解决错误码:2006—治标不治本）

2.2.1 找到mysql配置文件/etc/my.cnf，加入以下代码
wait_timeout=288000 # 参数含义：服务器关闭交互式连接前等待活动的秒数。
interactive_timeout = 288000 # 参数含义：服务器关闭非交互连接之前等待活动的秒数。
相关参数详解
2.2.2 重启MySQL数据库
service mysql restart

3.产生原因与解决方案二（数据库max_allowed_packet参数限制）

3.1调整数据库参数max_allowed_packet阈值

3.1.1 临时调整max_allowed_packet阈值

mysql> show global variables like 'max_allowed_packet';
+--------------------+---------+
| Variable_name      | Value   |
+--------------------+---------+
| max_allowed_packet | 1048576 |
+--------------------+---------+
1 row in set (0.00 sec)
mysql> set global max_allowed_packet=1024*1024*16;

3.1.2 永久调整max_allowed_packet阈值
a. 查看现有阈值

[root@localhost:/root]#cat /etc/my.cnf | grep "max_allowed_packet"
max_allowed_packet = 1M

b. 修改阈值

[root@localhost:/root]#sed -i 's/max_allowed_packet = 1M/max_allowed_packet = 16M/g' /etc/my.cnf
[root@localhost:/root]#cat /etc/my.cnf | grep "max_allowed_packet"
max_allowed_packet = 16M

c. 重启MySQL数据库
service mysql restart