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

青旅半醒 2022-08-18 02:21 75阅读 0赞

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

[slave_skip_errors]: http://dev.mysql.com/doc/refman/5.5/en/replication-options-slave.html#option_mysqld_slave-skip-errors