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

php中文网

发布时间：2016-06-07 16:43:06

1083人浏览过

来源于php中文网

原创

今天在MySQL 5.1同步到5.5时遇到一则卡库问题，在从库上show slave status \G，报错如下：

今天在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，设置=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，因此只适合跳过少数卡库的命令点。如果从库卡库累积的命令点非常多，此法就不可取了。