Not the answer you need?
Register and ask your own question!

Slave related problem with UNSIGNED subtraction

liberolibero EntrantInactive User Role Participant
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='[email protected]''</pre>

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='[email protected]'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='[email protected]'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)</pre>

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='[email protected]'</pre>

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?



Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.