Slave related problem with UNSIGNED subtraction

I’ve one master and one slave A (both v5.1). Replication going well for long time.
Now I want to add another slave B (Percona v5.5). I set up it and almost all is OK. But sometimes on slave B I’ve error like this (and replication stops):

Last_SQL_Errno: 1690Last_SQL_Error: Error ‘BIGINT UNSIGNED value is out of range in ‘(myDB.u_email.bounced - 1)’’ on query. Default database: ‘’. Query: ‘UPDATE myDB.u_email SET bounced=bounced-1 WHERE email=‘any@email’’

u_email is MyISAM table.
The ‘bounced’ column type (on all servers) is SMALL INT UNSIGNED
Initial value is 0.

When I log in with mysql client and manually execute:

UPDATE myDB.u_email SET bounced=bounced-1 WHERE email='any@email’I have same error.So I do that:SET sql_mode=‘NO_UNSIGNED_SUBTRACTION’;Query OK, 0 rows affected (0.00 sec)UPDATE myDB.u_email SET bounced=bounced-1 WHERE email='any@email’Query OK, 1 row affected, 1 warning (0.00 sec)Rows matched: 2 Changed: 1 Warnings: 1mysql> show warnings; ±--------±-----±----------------------------------------- ------------+ | Level | Code | Message ±--------±-----±----------------------------------------- ------------+ | Warning | 1264 | Out of range value for column ‘bounced’ at row 1 ±--------±-----±----------------------------------------- ------------+1 row in set (0.00 sec)

After this operation executed on master and slave A (v5.1) value becomes: 65535 (the biggest allowed value in this column)
On slave B (v5.5) value of bounced = 0
This difference isn’t important for me, but I want to have uninterrupted replication.

I added to my.cnf (on slave):
init_slave=“SET sql_mode=‘NO_UNSIGNED_SUBTRACTION’”

In logs (on B slave) I see:

54223 Query SET sql_mode='NO_UNSIGNED_SUBTRACTION’54223 Query UPDATE myDB.u_email SET bounced=bounced-1 WHERE email=‘any@email’

But still I’ve same error (from slave SQL process).
I set:
slave_exec_mode = IDEMPOTENT
but without success either.

I think that problem is related to this: sqlmode_no_unsigned_subtraction

Any ideas?


Hi Tom,

Actually, its expected behavior in MySQL 5.5.5 and higher versions. As per the documentation, its incompatible change.

"Incompatible change: As of MySQL 5.5.5, all numeric operators and functions on integer, floating-point and DECIMAL values throw an “out of range” error (ER_DATA_OUT_OF_RANGE) rather than returning an incorrect value or NULL, when the result is out of the supported range for the corresponding data type. See Section 11.2.6, “Out-of-Range and Overflow Handling”.

Check below links. us-series.html flow.html

Looks like need to find some workaround for this. Still I’ll try to check it out and let you know if I’ll found something.

If anyone is still looking for a temporary work-around to get the SLAVE replication going again; adding the ‘1690’ error code to the ‘slave-skip-errors’ setting helps.

For details: lave.html#option_mysqld_slave-skip-errors