Slave related problem with UNSIGNED subtraction

MySQL & MariaDB Other MySQL® Questions
1

Hello,
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: http://dev.mysql.com/doc/refman/5.5/en/server-sql-mode.html# sqlmode_no_unsigned_subtraction

Any ideas?

Regards
Tom

2

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.

http://dev.mysql.com/doc/refman/5.5/en/upgrading-from-previo us-series.html
http://dev.mysql.com/doc/refman/5.5/en/out-of-range-and-over 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.

3

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:
http://dev.mysql.com/doc/refman/5.5/en/replication-options-s lave.html#option_mysqld_slave-skip-errors