随意吧: mysql server has gone away 触发的字符集问题

故障前奏：

监控数据库由于磁盘空间不够，无法保留较长时间的数据，为此升级 percona-5.5.23 为 percona-5.6.17，

并支持 tokudb 引擎，将旧表的数据转换为该引擎以节省大量磁盘空间。

下午升级＋切换，此过程还算顺利，因为长连接，重启应用相关业务后，都切换到新机器上。晚上爆出来的

故障反映告警失败，根本原因为其中一个表字符集乱码导致。收到问题后，查原因：

1. 对比两边的字符集设置，表字符集设置，确定一致：

mysql> show variables like '%char%';

+--------------------------+-------------------------------------+

| Variable_name | Value |

+--------------------------+-------------------------------------+

| character_set_client | latin1 |

| character_set_connection | latin1 |

| character_set_database | latin1 |

| character_set_filesystem | binary |

| character_set_results | latin1 |

| character_set_server | latin1 |

| character_set_system | utf8 |

| character_sets_dir | /usr/share/percona-server/charsets/ |

+--------------------------+-------------------------------------+

mysql> show create table t_idc_flow_cfg;

------------------------------------------------------------

CREATE TABLE `t_idc_flow_cfg` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`name` varchar(256) NOT NULL COMMENT 'idc接口名',

`is_alarm` int(2) NOT NULL DEFAULT '0' COMMENT '1: alarm, 0: no_alarm',

`low` int(11) NOT NULL DEFAULT '0' COMMENT '流量低位阀值',

`high` int(11) NOT NULL DEFAULT '0' COMMENT '流量低高位阀值',

PRIMARY KEY (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=123 DEFAULT CHARSET=utf8 |

2. 检查相关中文的字符集类型：

mysql> select * from t_idc_flow_cfg limit 10;

+----+---------+----------+-----+-------+

+----+---------+----------+-----+-------+

| 37 | ?????? | 1 | 100 | 1000 |

| 38 | ?????? | 1 | 50 | 490 |

| 39 | ?????? | 0 | 300 | 8500 |

| 40 | ?????? | 1 | 50 | 3000 |

| 41 | ???? | 1 | 100 | 10000 |

| 42 | ???? | 1 | 300 | 8500 |

| 43 | ??????? | 1 | 300 | 10000 |

| 44 | ???? | 1 | 300 | 10000 |

| 45 | ??87?? | 1 | 500 | 10000 |

| 46 | ???? | 1 | 500 | 9500 |

+----+---------+----------+-----+-------+

10 rows in set (0.01 sec)

mysql> set names utf8;

Query OK, 0 rows affected (0.00 sec)

mysql> select * from t_idc_flow_cfg limit 10;

+----+-----------------------+----------+-----+-------+

+----+-----------------------+----------+-----+-------+

| 37 | 上海南汇电信 | 1 | 100 | 1000 |

| 38 | 上海南汇联通 | 1 | 50 | 490 |

| 39 | 太原联通联通 | 0 | 300 | 8500 |

| 40 | 北京赛尔赛尔 | 1 | 50 | 3000 |

| 41 | 韶关电信 | 1 | 100 | 10000 |

| 42 | 滨州联通 | 1 | 300 | 8500 |

| 43 | 济南担山屯联通 | 1 | 300 | 10000 |

| 44 | 青岛联通 | 1 | 300 | 10000 |

| 45 | 宁波87电信 | 1 | 500 | 10000 |

| 46 | 汕尾电信 | 1 | 500 | 9500 |

+----+-----------------------+----------+-----+-------+

10 rows in set (0.00 sec)

mysql> select * from t_idc_flow_cfg ;

+-----+------------------------------------------------------+----------+-----+-------+

+-----+------------------------------------------------------+----------+-----+-------+

| 37 | 上海南汇电信 | 1 | 100 | 1000 |

| 38 | 上海南汇联通 | 1 | 50 | 490 |

| 39 | 太原联通联通 | 0 | 300 | 8500 |

| 40 | 北京赛尔赛尔 | 1 | 50 | 3000 |

| 41 | 韶关电信 | 1 | 100 | 10000 |

| 42 | 滨州联通 | 1 | 300 | 8500 |

＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊

切换后新 insert 的数据

| 82 | ä¸Šæµ·å—æ±‡ç”µä¿¡ | 0 | 0 | 0 |

| 83 | ä¸Šæµ·å—æ±‡è”é€š | 0 | 0 | 0 |

| 84 | å¤ªåŽŸè”é€šè”é€š | 0 | 0 | 0 |

| 85 | åŒ—äº¬èµ›å°”èµ›å°” | 0 | 0 | 0 |

| 86 | æ»¨å·žè”é€š | 0 | 0 | 0 |

| 122 | å…†ç»´2è”é€š | 0 | 0 | 0 |

+-----+------------------------------------------------------+----------+-----+-------+

82 rows in set (0.00 sec)

mysql> set lames latin1;

mysql> select * from t_idc_flow_cfg ;

+-----+------------------------------------------------------+----------+-----+-------+

+-----+------------------------------------------------------+----------+-----+-------+

| 37 | ?????? | 1 | 100 | 1000 |

| 38 | ?????? | 1 | 50 | 490 |

| 39 | ?????? | 0 | 300 | 8500 |

| 40 | ???? | 1 | 50 | 3000 |

| 41 | ???? | 1 | 100 | 10000 |

| 42 | ???????? | 1 | 300 | 8500 |

＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊

切换后新 insert 的数据

| 82 | 上海南汇电信 | 0 | 0 | 0 |

| 83 | 上海南汇联通 | 0 | 0 | 0 |

| 84 | 太原联通联通 | 0 | 0 | 0 |

| 85 | 北京赛尔赛 | 0 | 0 | 0 |

| 86 | 韶关电信 | 0 | 0 | 0 |

| 122 | 滨州联通 | 0 | 0 | 0 |

+-----+------------------------------------------------------+----------+-----+-------+

82 rows in set (0.00 sec)

3. 通过上面的数据检查，数据入表的逻辑，确定 select 旧数据发现是？号，由于字符集不一致无法匹配，所以 insert

新记录。初步来看，问题的原因是旧的数据记录类型是 utf8，新增的记录类型是 latin1 。so ，和晓杰沟通，确定程序

是否设置字符集变量为 utf8，刚开始在 latin1 中不断的绕，两个多小时的纠结还是回到 utf8 上。

在操作 mysql 终端过程中可以看到有以下提示：

ERROR 2006 (HY000): MySQL server has gone away

看一下具体的值，单位为秒

mysql> show variables like '%wait_timeout%';

+-----------------------------------------+----------+

| Variable_name | Value |

+-----------------------------------------+----------+

| wait_timeout | 100 |

+-----------------------------------------+----------+

1 rows in set (0.00 sec)

因为没有涉及到字符集相关，所以忽略了这个带来的影响。

程序那边反馈说没有问题，会不会是系统环境变量导致的？由于跑程序的机器没有变更，系统两边的确实不一样，

一个是 cn_utf8, 新数据库系统变量是 en_us。虽然不一样，但程序取出来的数据仍然是在旧的机器上显示，

所以这个影响排除。晓杰不知采取何方法临时解决了改问题，但没查明真相。

4. 在 cbt 指引下，问题可能在程序中，看程序的代码，从逻辑中大概分析出程序有全局的 set name 操作，中间过程涉

及到 auto_reconnect，但 auto_reconnet 没有定义字符集。如此，可能是由于中间过程 mysql server has gone away 引起。

现场复现过程大致如下：

1）. 新增一个账号，供测试连接使用，

2）. 将程序使用的表改为备份表，让程序在 insert 前 sleep 60 秒

3）. 数据库在服务端 kill 掉该进程

4）. 查看新入库的数据（数据从utf8 变成 latin1），为什么变成 latin1 呢？因为 mysql server 默认的字符集是 latin1。

为了简化问题描述，我们看一个简单的示例：

未设定mysql 登录后的字符集：

mysql> select * from tmp2;

+----+--------+----------+-----+------+

+----+--------+----------+-----+------+

| 37 | ?????? | 1 | 100 | 1000 |

| 38 | ?????? | 1 | 50 | 490 |

+----+--------+----------+-----+------+

2 rows in set (0.00 sec)

设置为 utf8 ：

mysql> set names utf8;

Query OK, 0 rows affected (0.16 sec)

mysql> select * from tmp2;

+----+--------------------+----------+-----+------+

+----+--------------------+----------+-----+------+

| 37 | 上海南汇电信 | 1 | 100 | 1000 |

| 38 | 上海南汇联通 | 1 | 50 | 490 |

+----+--------------------+----------+-----+------+

2 rows in set (0.00 sec)

以上说明 name 字段的字符集是 utf8。

测试，gone away 前后看看这两条记录的显示：

mysql> set names utf8;select * from tmp2;select sleep(110);select * from tmp2;

Query OK, 0 rows affected (0.13 sec)

+----+--------------------+----------+-----+------+

+----+--------------------+----------+-----+------+

| 37 | 上海南汇电信 | 1 | 100 | 1000 | 这里正常使用 utf8 显示

| 38 | 上海南汇联通 | 1 | 50 | 490 |

+----+--------------------+----------+-----+------+

2 rows in set (0.00 sec)

ERROR 2013 (HY000): Lost connection to MySQL server during query kill 操作触发 gone way

ERROR 2006 (HY000): MySQL server has gone away

No connection. Trying to reconnect...

Connection id: 129399

Current database: monitor

+----+--------+----------+-----+------+

+----+--------+----------+-----+------+

| 37 | ?????? | 1 | 100 | 1000 | 这里 set names utf8 变量丢失，导致显示？号了

| 38 | ?????? | 1 | 50 | 490 |

+----+--------+----------+-----+------+

2 rows in set (0.14 sec)

mysql>

中间的 MySQL server has gone away 是通过 show processlist 得出的 id， kill 掉手动触发：

+--------+----------+-------------+---------+---------+------+------------+----------------------+-----+------+

16 rows in set (0.00 sec)

mysql> kill 129388;

Query OK, 0 rows affected (0.00 sec)

我们再来看 Perl 代码中的连接：

sub ConnectDb(){

print "now connect mysql[DataBaseServer:$db]...\n";

$dsn = "dbi:mysql:$db:$host:$port";

$dbh = DBI->connect($dsn, $username, $passwd, { RaiseError => 0, AutoCommit => 1, PrintError => 1, mysql_auto_reconnect => 1} );

if($dbh){

print "connect mysql sucess[DataBaseServer:$db]...\n";

}

else{

print "connect mysql failed[DataBaseServer:$db]...\n";

exit(1);

}

$dbh->do("SET NAMES 'utf8'");

}

程序不是一直处于连接运行状态，连接后，mysql server has gone away，触发 mysql_auto_reconnect 后，不会触发

$dbh->do ("set names 'utf8'") 操作，因此会出现上面我测试的结果。在 DBI->connect 中增加参数 mysql_enable_utf8 => 1

可以解决这个问题，或者设置更长时间的 wait_timeout ，或者更改数据库会话默认的字符集。

5. 总结：规范是避免 bug 存在的根本所在。

随意吧

2014年10月23日星期四

mysql server has gone away 触发的字符集问题

没有评论:

发表评论

我的简介