mysql5.5同步mysql5.1出现“BIGINT UNSIGNED value is out of range”解决方法

线上遇到的错误：
主库5.1版本执行下面类似的语句

mysql> update test set age=greatest(0,age-1) where id=1;
从库5.5版本复制出错：
mysql> update test set age=greatest(0,age-1) where id=1;

原因：
Mysql5.5下sql_mode为空时，两个数想减，当其中一个为unsigned时，如果结果为负数，则会出现error；sql_mode修改为NO_UNSIGNED_SUBTRACTION，则不会出现error，如下所示：

mysql> SET sql_mode = '';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT CAST(0 AS UNSIGNED) - 1;
ERROR 1690 (22003): BIGINT UNSIGNED value is out of range in '(cast(0 as unsigned) - 1)'
If the NO_UNSIGNED_SUBTRACTION SQL mode is enabled, the result is negative:

mysql> SET sql_mode = 'NO_UNSIGNED_SUBTRACTION';
mysql> SELECT CAST(0 AS UNSIGNED) - 1;
+-------------------------+
| CAST(0 AS UNSIGNED) - 1 |
+-------------------------+
| -1 |
+-------------------------+

目前从测试的结果看，sql_mode不会对replication的sql_thread产生影响（使用NO_ZERO_IN_DATE、STRICT_ALL_TABLES、NO_UNSIGNED_SUBTRACTION进行测试），5.1->5.5时，当遇到这个错误的时候，通过修改从库的sql_mode无法解决这个问题。

测试5.5->5.5，当主库的sql_mode设置成NO_UNSIGNED_SUBTRACTION时，主库可以插入，从库也不会报错。
从目前调查的结论看，这个应该是一个bug。

解决方法：
1， 主库和从库版本保持一致（主库升级或者从库降级）。如果主库升级的话，主库需要设置sql_mode=” NO_UNSIGNED_SUBTRACTION”。
2， 将原来的greatest函数修改为控制流函数if，建议使用这种方法。

mysql> update test set age=greatest(0,age-1) where id=1;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1 Changed: 0 Warnings: 0
mysql> update test set age=if(age>1,age-1,0) where id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

本文转自：