Bug #64751 MySQL 5.5+ cannot replicate some statements that are valid in 5.1
Submitted: 24 Mar 2012 0:11 Modified: 28 Mar 2012 21:12
Reporter: Chip Turner Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: DML Severity:S2 (Serious)
Version:5.5.20, 5.6.4, 5.6.5 OS:Linux (64 bit)
Assigned to: CPU Architecture:Any
Tags: Contribution

[24 Mar 2012 0:11] Chip Turner
Description:
MySQL is more strict than 5.1 when it comes to underflows operating on unsigned types.  5.1 silently allows underflows but, even setting NO_UNSIGNED_SUBTRACTION still results in SQL statements that aren't valid in 5.6.  

For our use case, we have queries that perform

... ON DUPLICATE KEY UPDATE `count` = GREATEST(0, `count` + -1)

`count` is declared unsigned.  In 5.1, the above does what you expect when count is 0.  In 5.6, the query raises an error and fails.  Another example of the same problem is in the repeat section.

I think the check is good to have, but an SQL_MODE option to control this would help migrations to 5.6 from installed 5.1 deployments.

How to repeat:

mysql> show variables like 'version'; select CAST(0 AS UNSIGNED)  - 1;
+---------------+--------------+
| Variable_name | Value        |
+---------------+--------------+
| version       | 5.6.4-m7-log |
+---------------+--------------+
1 row in set (0.00 sec)

ERROR 1690 (22003): BIGINT UNSIGNED value is out of range in '(cast(0 as unsigned) - 1)'

mysql> show variables like 'version'; select CAST(0 AS UNSIGNED)  - 1;
+---------------+---------------------------------+
| Variable_name | Value                           |
+---------------+---------------------------------+
| version       | 5.1.53_blackhole_nomemcache-log |
+---------------+---------------------------------+
1 row in set (0.00 sec)

+--------------------------+
| CAST(0 AS UNSIGNED)  - 1 |
+--------------------------+
|     18446744073709551615 |
+--------------------------+
1 row in set (0.00 sec)

Suggested fix:
Add an SQL_MODE that allows this behavior.
[24 Mar 2012 9:36] Valeriy Kravchuk
Same problem in 5.5:

mysql> create table tc(id int primary key, c int unsigned);Query OK, 0 rows affected (0.07 sec)

mysql> insert into tc values(1,0);Query OK, 1 row affected (0.00 sec)

mysql> select @@sql_mode;
+------------+
| @@sql_mode |
+------------+
|            |
+------------+
1 row in set (0.00 sec)

mysql> insert into tc(id) values(1) on duplicate key update c = greatest(0, c +  -1) ;
ERROR 1690 (22003): BIGINT UNSIGNED value is out of range in '(`test`.`tc`.`c` + -(1))'
mysql> set sql_mode='NO_UNSIGNED_SUBTRACTION';
Query OK, 0 rows affected (0.00 sec)

mysql> insert into tc(id) values(1) on duplicate key update c = greatest(0, c +  -1) ;
ERROR 1690 (22003): BIGINT UNSIGNED value is out of range in '(`test`.`tc`.`c` + -(1))'
mysql> show variables like 'version'; select CAST(0 AS UNSIGNED)  - 1;
+---------------+--------------+
| Variable_name | Value        |
+---------------+--------------+
| version       | 5.5.20-debug |
+---------------+--------------+
1 row in set (0.00 sec)

+--------------------------+
| CAST(0 AS UNSIGNED)  - 1 |
+--------------------------+
|                       -1 |
+--------------------------+
1 row in set (0.00 sec)

So, while NO_UNSIGNED_SUBTRACTION works in case of CAST (as described in the manual, http://dev.mysql.com/doc/refman/5.5/en/server-sql-mode.html#sqlmode_no_unsigned_subtractio..., it does not work in case of table and statement like yours.
[27 Mar 2012 0:50] MySQL Verification Team
If you enable NO_UNSIGNED_SUBTRACTION, "+ -1" fails, but "- 1" works.
[27 Mar 2012 0:55] MySQL Verification Team
^^ That is using 5.6.4-m7
[27 Mar 2012 1:59] MySQL Verification Team
Even for the simple CAST example:

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

mysql> SELECT CAST(0 AS UNSIGNED) -1 ;
+------------------------+
| CAST(0 AS UNSIGNED) -1 |
+------------------------+
|                     -1 |
+------------------------+
1 row in set (0.01 sec)

mysql> SELECT CAST(0 AS UNSIGNED) + -1 ;
ERROR 1690 (22003): BIGINT UNSIGNED value is out of range in '(cast(0 as unsigned) + -(1))'
mysql>
[27 Mar 2012 17:26] Chip Turner
I should have been more accurate in my original report; it does indeed look like subtraction works but adding a negative unconditionally fails, as others have reported.
[28 Mar 2012 1:33] Chip Turner
I've added support for + -1 and the cases in this bug work.  however, the replication thread still fails when one of these statements is encountered because, apparently, even with the global sql_mode set to be NO_UNSIGNED_SUBTRACTION, replication disregards it and only accepts the master's value.  this means replicating from 5.1 to 5.6 fails for these kinds of queries.

updated the synopsis to clarify this is also a replication issue
[28 Mar 2012 21:11] Chip Turner
fix for unsigned addition

Attachment: unsigned-addition-fix.patch (application/octet-stream, text), 919 bytes.

[28 Mar 2012 21:12] Chip Turner
fix for replication thread to obey unsigned sql_mode setting

Attachment: unsigned-replication.patch (application/octet-stream, text), 741 bytes.

[28 Mar 2012 21:12] Chip Turner
attached two patches that resolve this issue and allow us to continue testing replicating from a 5.1 master
[20 Feb 2013 2:39] Domas Mituzas
do note http://bugs.mysql.com/bug.php?id=68439 - apparently MySQL 5.1 will be corrupting data on some of these queries that 5.6 will reject outright (in both cases there should be a way to bound the value at 0).
[6 May 2014 16:28] Laurynas Biveinis
Same issue as bug 16285, which was closed as Not a Bug?