note: mySQL replication 複製的問題

mysql 5.5 的 slave 出現錯誤 (master 是 5.0 ) 的錯誤：

1690
Error 'BIGINT UNSIGNED value is out of range in '(`test`.`t9`.`rr` - 1)'' on query. Default database: 'test'. Query: 'update t9 set rr=rr-1 where id=1'

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.0.188
                  Master_User: copy
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 2600
               Relay_Log_File: centos_190-relay-bin.000002
                Relay_Log_Pos: 1049
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: No
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 1690
                   Last_Error: Error 'BIGINT UNSIGNED value is out of range in '(`test`.`t9`.`rr` - 1)'' on query. Default database: 'test'. Query: 'update t9 set rr=rr-1 where id=1'
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 903
              Relay_Log_Space: 2907
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 1690
               Last_SQL_Error: Error 'BIGINT UNSIGNED value is out of range in '(`test`.`t9`.`rr` - 1)'' on query. Default database: 'test'. Query: 'update t9 set rr=rr-1 where id=1'
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 1
1 row in set (0.00 sec)

但奇怪的是
5.0 版本的 master server 或是 5.0 slave 都很正常的執行該 update 命令

mysql 5.5 與 5.1(或之前)
以這樣的 table 來說

CREATE TABLE `tt` (
  `id` int(4) unsigned NOT NULL auto_increment,
  `rr` int(4) unsigned default '0',
  `txt` char(20) default '',
  PRIMARY KEY  (`id`),
  KEY `idx` (`rr`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

mysql 5.1	mysql 5.5
mysql> select * from tt; +----+------+------+ | id | rr   | txt  | +----+------+------+ |  1 |    0 | aa   | |  2 |    2 | bb   | +----+------+------+ 2 rows in set (0.00 sec)	mysql> select * from tt; +----+------+------+ | id | rr   | txt  | +----+------+------+ |  1 |    0 | aa   | |  2 |    2 | bb   | +----+------+------+ 2 rows in set (0.00 sec)
mysql> update tt set rr=rr-1 where id=1; Query OK, 1 row affected, 1 warning (0.00 sec) Rows matched: 1  Changed: 1  Warnings: 1 mysql> show warnings\G *************************** 1. row ***************************   Level: Warning    Code: 1264 Message: Out of range value adjusted for column 'rr' at row 1 1 row in set (0.00 sec) mysql> select * from tt; +----+------------+------+ | id | rr         | txt  | +----+------------+------+ |  1 | 4294967295 | aa   | |  2 |          2 | bb   | +----+------------+------+ 2 rows in set (0.00 sec)	mysql> update tt set rr=rr-1 where id=1; ERROR 1690 (22003): BIGINT UNSIGNED value is out of range in '(`test`.`tt`.`rr` - 1)' mysql> select * from tt; +----+------+------+ | id | rr   | txt  | +----+------+------+ |  1 |    0 | aa   | |  2 |    2 | bb   | +----+------+------+ 2 rows in set (0.00 sec)
mysql> update tt set rr=-1 where id=1; Query OK, 1 row affected, 1 warning (0.00 sec) Rows matched: 1  Changed: 1  Warnings: 1 mysql> show warnings\G *************************** 1. row ***************************   Level: Warning    Code: 1264 Message: Out of range value adjusted for column 'rr' at row 1 1 row in set (0.00 sec) mysql> select * from tt; +----+------+------+ | id | rr   | txt  | +----+------+------+ |  1 |    0 | aa   | |  2 |    2 | bb   | +----+------+------+ 2 rows in set (0.00 sec)	mysql> update tt set rr=-1 where id=1; Query OK, 0 rows affected, 1 warning (0.00 sec) Rows matched: 1  Changed: 0  Warnings: 1 mysql> show warnings\G *************************** 1. row ***************************   Level: Warning    Code: 1264 Message: Out of range value adjusted for column 'rr' at row 1 1 row in set (0.00 sec) mysql> select * from tt; +----+------+------+ | id | rr   | txt  | +----+------+------+ |  1 |    0 | aa   | |  2 |    2 | bb   | +----+------+------+ 2 rows in set (0.00 sec)
mysql> update tt set rr=4299999999 where id=1; Query OK, 1 row affected, 1 warning (0.00 sec) Rows matched: 1  Changed: 1  Warnings: 1 mysql> show warnings\G *************************** 1. row ***************************   Level: Warning    Code: 1264 Message: Out of range value adjusted for column 'rr' at row 1 1 row in set (0.00 sec) mysql> select * from tt; +----+------------+------+ | id | rr         | txt  | +----+------------+------+ |  1 | 4294967295 | aa   | |  2 |          2 | bb   | +----+------------+------+ 2 rows in set (0.00 sec)	mysql> update tt set rr=4299999999 where id=1; Query OK, 1 row affected, 1 warning (0.00 sec) Rows matched: 1  Changed: 1  Warnings: 1 mysql> show warnings\G *************************** 1. row ***************************   Level: Warning    Code: 1264 Message: Out of range value adjusted for column 'rr' at row 1 1 row in set (0.00 sec) mysql> select * from tt; +----+------------+------+ | id | rr         | txt  | +----+------------+------+ |  1 | 4294967295 | aa   | |  2 |          2 | bb   | +----+------------+------+ 2 rows in set (0.00 sec)

參考
mysql 的手冊 11.2.6. Out-of-Range and Overflow Handling

http://dev.mysql.com/doc/refman/5.5/en/server-sql-mode.html#sqlmode_no_unsigned_subtraction

http://nikolaynaychov.blogspot.tw/2012/08/how-to-fix-mysql-error-bigint-unsigned.html