mysql5.1 数据库同步_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_var

Create 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_var where mgid = '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)

验证一下

在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 where

grp_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来说，采用行格式复制，从根本上可以保证主从数据的一致性。毕竟跳过卡库问题是简单的，但是后续的数据修复过程却是复杂的。

参考资料

--------------------------------------分割线 --------------------------------------

Ubuntu 14.04 LTS 安装 LNMP Nginx\PHP5 (PHP-FPM)\MySQL http://www.linuxidc.com/Linux/2014-05/102351.htm

--------------------------------------分割线 --------------------------------------