Slave related problem with UNSIGNED subtraction

  • Filter
  • Time
  • Show
Clear All
new posts

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

    Any ideas?


  • #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