MySQL 5.1同步到5.5卡库问题一则

今天在MySQL 5.1同步到5.5时遇到一则卡库问题，在从库上show slave status G，报错如下：Error "BIGINT UNSIGNED value is out of range in "（group.mg_group_var.grp_status_cnt + -（38））"" on query. Default database: "group". Query: "update mg_group_var set grp_status_cnt=grp_status_cnt+（-38） where mgid = "302412" and grp_status_cnt>0"为何在主库上能执行，在从库上就会失败呢？

追查原因

在从库上，show create table 查看表结构：*************************** 1. row ***************************Table: mg_group_varCreate Table: CREATE TABLE mg_group_var （mgid bigint（20） NOT NULL,grp_status_cnt int（11） unsigned NOT NULL DEFAULT "0",grp_member_cnt int（11） unsigned NOT NULL DEFAULT "0",grp_apply_cnt int（6） DEFAULT NULL, 发现grp_status_cnt 列为无符号的整数类型。查询一下grp_status_cnt的值：mysql> select grp_status_cnt from mg_group_varwheremgid = "302412";+----------------+| grp_status_cnt |+----------------+| 27 |+----------------+27-38后得到的是负数。
官方手册中有一段话：对于无符号数，如果相减得到负数，mysql会自动转化为最大的正数。（Subtraction between integer values, where one is of type UNSIGNED, produces an unsigned result by default. If the result would otherwise have been negative, it becomes the maximum integer value）<！--more-->

验证一下

在5.1上执行如下语句：mysql> SELECT CAST（0 AS UNSIGNED） - 1;+-------------------------+| CAST（0 AS UNSIGNED） - 1 |+-------------------------+|18446744073709551615 |+-------------------------+但是在5.5上执行的时候就会报错：mysql> SELECT CAST（0 AS UNSIGNED） - 1;ERROR 1690 （22003）: BIGINT UNSIGNED value is out of range in "（）cast（0 as unsigned） - 1）"按照官方文档的解释，在5.1上，当相减得到负数，马上就被转化为18446744073709551615，而它超出了无符号整数的范围，所以被转化为最大的int类型的无符号数4294967295（但是在5.5上，这个转化报错，因此导致同步中断）。（When an out-of-range value is assigned to an integer column, MySQL stores the value representing the corresponding endpoint of the column data type range. If you store 256 into a TINYINT or TINYINT UNSIGNED column, MySQL stores 127 or 255, respectively. ）回到生产数据库，在主库上，执行：mysql> select grp_status_cnt from mg_group_var wheregrp_status_cnt > 300000000 limit 3;+----------------+| grp_status_cnt |+----------------+| 4294967295 || 4294967295 || 4294967272 |+----------------+可以发现这种溢出的情况很多，它们其实都是从负数转化来的，这些数据从应用的角度来说，都是错误的。

解决方法

解决从库卡库问题的办法很多，常用的有如下几种：

1.跳过指定的错误代码

重启mysql，设置slave_skip_errors=1069。这需要重启数据库，代价太大，所以不推荐。
用watch -n与mysql命令相结合，每秒跳过一个命令点:

watch -n 1 "mysql -uroot -prootpassword -S /tmp/mysql4389.sock -e "set global sql_slave_skip_counter=1;start slave;" "但是watch的时间间隔最小只能到1s，因此只适合跳过少数卡库的命令点。如果从库卡库累积的命令点非常多，此法就不可取了。

用pt-slave-restart工具跳过命令点。这也是推荐的跳过命令点的方法：
- 可以指定按照错误代码匹配，例如1062，1069等；
- 可以指定按照错误文本进行正则匹配；
- 智能的调整从库同步状态检查的时间间隔，因此如果发现连续的卡库问题，1s内可以跳过非常多的sql语句。这些特点对于快速恢复服务是非常重要的。

2.跳命令点始终只能解决一时的问题，根本上还是应修改代码逻辑，确保不出现无符号减法的溢出。

3.对DBA来说，采用行格式复制，从根本上可以保证主从数据的一致性。毕竟跳过卡库问题是简单的，但是后续的数据修复过程却是复杂的。

参考资料

http://dev.mysql.com/doc/refman/5.1/en/numeric-types.html
http://dev.mysql.com/doc/refman/5.1/en/out-of-range-and-overflow.html--------------------------------------分割线 --------------------------------------Ubuntu 14.04下安装MySQL http://www.linuxidc.com/Linux/2014-05/102366.htm《MySQL权威指南（原书第2版）》清晰中文扫描版 PDF http://www.linuxidc.com/Linux/2014-03/98821.htmUbuntu 14.04 LTS 安装 LNMP NginxPHP5 （PHP-FPM）MySQL http://www.linuxidc.com/Linux/2014-05/102351.htmUbuntu 14.04下搭建MySQL主从服务器 http://www.linuxidc.com/Linux/2014-05/101599.htmUbuntu 12.04 LTS 构建高可用分布式 MySQL 集群 http://www.linuxidc.com/Linux/2013-11/93019.htmUbuntu 12.04下源代码安装MySQL5.6以及Python-MySQLdb http://www.linuxidc.com/Linux/2013-08/89270.htmMySQL-5.5.38通用二进制安装 http://www.linuxidc.com/Linux/2014-07/104509.htm--------------------------------------分割线 --------------------------------------本文永久更新链接地址