MySQL Bugs: #64751: MySQL 5.5+ cannot replicate some statements that are valid in 5.1

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

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.

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.

If you enable NO_UNSIGNED_SUBTRACTION, "+ -1" fails, but "- 1" works.

^^ That is using 5.6.4-m7

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>

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.

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

fix for unsigned addition

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

fix for replication thread to obey unsigned sql_mode setting

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

attached two patches that resolve this issue and allow us to continue testing replicating from a 5.1 master

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).

Same issue as bug 16285, which was closed as Not a Bug?