Bug #49562 SBR out of sync when using numeric data types + user variable
Submitted: 9 Dec 2009 14:30 Modified: 4 Aug 2010 10:36
Reporter: Matthias Leich Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Replication Severity:S3 (Non-critical)
Version:5.1,mysql-5.1-rep+3,mysql-6.0-codebase-b OS:Any
Assigned to: Luis Soares
Tags: signness, user var replication
Triage: Triaged: D2 (Serious)

[9 Dec 2009 14:30] Matthias Leich
Description:
My script:
----------
--source include/master-slave.inc
--disable_abort_on_error
SET AUTOCOMMIT=1;
CREATE TABLE t1 (
 f1 DOUBLE,
 f2 TINYINT,
 primary key (f2)) ENGINE = MyISAM;
let $my_value= 15596528459537448960;
eval SET @aux = $my_value;
eval INSERT INTO t1 ( f1 , f2 ) VALUES ( $my_value , 0 );
     INSERT INTO t1 ( f1 , f2 ) VALUES ( @aux      , 1 );
SELECT f1 , f2 FROM t1;
--sync_slave_with_master
connection slave;
SELECT f1 , f2 FROM t1;

The result with binlog-format statement or mixed
------------------------------------------------
stop slave;
drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9;
reset master;
reset slave;
drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9;
start slave;
SET AUTOCOMMIT=1;
CREATE TABLE t1 (
f1 DOUBLE,
f2 TINYINT,
primary key (f2)) ENGINE = MyISAM;
SET @aux = 15596528459537448960;
INSERT INTO t1 ( f1 , f2 ) VALUES ( 15596528459537448960 , 0 );
INSERT INTO t1 ( f1 , f2 ) VALUES ( @aux      , 1 );
SELECT f1 , f2 FROM t1;
f1	f2
1.55965284595374e+19	0
1.55965284595374e+19	1
SELECT f1 , f2 FROM t1;
f1	f2
1.55965284595374e+19	0
-2.8502156141721e+18	1  <=================

Observations:
-------------
1. binlog_format ROW does not show this bug
2. DOUBLE/FLOAT SIGNED/UNSIGNED show all this bug

Affected releases:
------------------
   mysql-5.1-bugteam revno: 3200 2009-11-13
   mysql-5.1-rep+3   revno: 3124 2009-12-02
   mysql-6.0-codebase-bugfixing revno: 3755 2009-12-04

My environment:
---------------
- MySQL compiled from source
  ./BUILD/compile-pentium64-debug-max
- Linux OpenSuSE 11.0 (64 Bit)
- Intel Core2Duo

How to repeat:
See above
[9 Dec 2009 16:54] Matthias Leich
The binlog on master side contains a:
   master-bin.000001 574 User var 1 618 @`aux`=-2850215614172102656
which IMHO explains why we get such
wrong result.
[14 Dec 2009 12:22] Matthias Leich
The reported effect seems to be present in more numeric
data types.
Example from mysql-6.0-codebase-bugfixing:
------------------------------------------
SET AUTOCOMMIT=1;
CREATE TABLE t1 (
f1 INTEGER,
f2 TINYINT,
primary key (f2)) ENGINE = MyISAM;
SET @aux = 15596528459537448960;
INSERT INTO t1 ( f1 , f2 ) VALUES ( 15596528459537448960 , 0 );
Warnings:
Warning	1264	Out of range value for column 'f1' at row 1
INSERT INTO t1 ( f1 , f2 ) VALUES ( @aux      , 1 );
Warnings:
Warning	1264	Out of range value for column 'f1' at row 1
SELECT f1 , f2 FROM t1;
f1	f2
2147483647	0
2147483647	1
SELECT f1 , f2 FROM t1;
f1	f2
2147483647	0
-2147483648	1
[18 Dec 2009 1:25] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/94874

3125 Luis Soares	2009-12-18
      BUG#49562: SBR out of sync when using numeric data types + user variable
      
      The User_var_log_event was not serializing the unsigned flag. This
      would cause the slave to always assume signed values.
      
      We fix this by extending the User_var_log_event to also contain
      information on the unsigned_flag, meaning that it gets into the 
      binlog as well, therefore the slave will get this information as
      well.
[12 Jan 2010 16:17] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/96630

3131 Luis Soares	2010-01-12
      BUG#49562: SBR out of sync when using numeric data types + user variable
      
      The User_var_log_event was not serializing the unsigned flag. This
      would cause the slave to always assume signed values.
      
      We fix this by extending the User_var_log_event to also contain
      information on the unsigned_flag, meaning that it gets into the 
      binlog as well, therefore the slave will get this information as
      well. Events without information on unsigned flag (old events) are 
      treated as they were before (always signed: unsigned_flag= FALSE).
[12 Jan 2010 17:47] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/96653

3131 Luis Soares	2010-01-12
      BUG#49562: SBR out of sync when using numeric data types + user variable
      
      The User_var_log_event was not serializing the unsigned flag. This
      would cause the slave to always assume signed values.
      
      We fix this by extending the User_var_log_event to also contain
      information on the unsigned_flag, meaning that it gets into the 
      binlog as well, therefore the slave will get this information as
      well. Events without information on unsigned flag (old events) are 
      treated as they were before (always signed: unsigned_flag= FALSE).
[15 Jan 2010 14:02] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/97082

3131 Luis Soares	2010-01-15
      BUG#49562: SBR out of sync when using numeric data types + user variable
      
      The User_var_log_event was not serializing the unsigned flag. This
      would cause the slave to always assume signed values.
      
      We fix this by extending the User_var_log_event to also contain
      information on the unsigned_flag, meaning that it gets into the 
      binlog as well, therefore the slave will get this information as
      well. Events without information on unsigned flag (old events) are 
      treated as they were before (always signed: unsigned_flag= FALSE).
      
      The information on the unsigned_flag, is shipped in an extra byte
      appended to the end of the User_var_log_event and added by this
      patch. This extra byte holds values for general purpose
      User_var_log_event flags which are now packed in the binlog as
      well. One of these flags contains information about whether the
      value is signed or unsigned (currently this extra byte is only
      used to hold data on the unsigned flag, in the future we can use
      it to pack extra flags if there is the need to).
[15 Jan 2010 17:53] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/97145

3131 Luis Soares	2010-01-15
      BUG#49562: SBR out of sync when using numeric data types + user
      variable
      
      The User_var_log_event was not serializing the unsigned
      flag. This would cause the slave to always assume signed values.
      
      We fix this by extending the User_var_log_event to also contain
      information on the unsigned_flag, meaning that it gets into the
      binlog as well, therefore the slave will get this information as
      well. Events without information on unsigned flag (old events)
      are treated as they were before (always signed: unsigned_flag=
      FALSE).
      
      The information on the unsigned_flag, is shipped in an extra byte
      appended to the end of the User_var_log_event and added by this
      patch. This extra byte holds values for general purpose
      User_var_log_event flags which are now packed in the binlog as
      well. One of these flags contains information about whether the
      value is signed or unsigned (currently this extra byte is only
      used to hold data on the unsigned flag, in the future we can use
      it to pack extra flags if there is the need to).
[18 Jan 2010 9:11] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/97211

3132 Luis Soares	2010-01-18
      BUG#49562: SBR out of sync when using numeric data types + user
      variable
      
      Incremental commit: 
        1. Moved part of the test case to binlog suite.
        2. Removed cast set when writing the flags field.
[19 Jan 2010 23:16] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/97461

3133 Luis Soares	2010-01-19
      BUG#49562: SBR out of sync when using numeric data types + user
      variable
      
      Post-push fix: missing 'reset master' at the beginning of the 
      test would cause show binlog events to output events from 
      previous tests.
[13 Feb 2010 8:37] Bugs System
Pushed into 6.0.14-alpha (revid:alik@sun.com-20100213083436-9pesg4h55w1mekxc) (version source revid:luis.soares@sun.com-20100209123439-1wao1hywtw0o6cej) (merge vers: 6.0.14-alpha) (pib:16)
[13 Feb 2010 8:39] Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20100213083327-cee4ao3jpg33eggv) (version source revid:luis.soares@sun.com-20100209121047-o78m90xmdr3llqra) (pib:16)
[13 Feb 2010 9:51] Jon Stephens
Documented bugfix in the 6.0.14 changelog as follows:

      Statement-based replication of user variables having numeric data 
      types did not always work correctly.

Set Need Merge, waiting for 5.1, 5.5 merges.
[24 Feb 2010 12:14] Luis Soares
See also: BUG#51426.
[6 Mar 2010 10:58] Bugs System
Pushed into 5.5.3-m3 (revid:alik@sun.com-20100306103849-hha31z2enhh7jwt3) (version source revid:vvaintroub@mysql.com-20100213160132-nx1vlocxuta76txh) (merge vers: 5.5.99-m3) (pib:16)
[8 Mar 2010 19:58] Jon Stephens
Also documented in the 5.5.3 changelog. Per mail discussion with Luís and Paul, this isn't expected to go into 5.1. Closed.
[16 Mar 2010 23:58] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/103520

2998 Luis Soares	2010-03-16
      In BUG#49562 we fixed the case where numeric user var events
      would not serialize the flag stating whether the value was signed
      or unsigned (unsigned_flag). This fixed the case that the slave
      would get an overflow while treating the unsigned values as
      signed.
            
      In this bug, we find that the unsigned_flag can sometimes change
      between the moment that the user value is recorded for binlogging
      purposes and the actual binlogging time. Since we take the
      unsigned_flag from the runtime variable data, at binlogging time,
      and the variable value is comes from the copy taken earlier in
      the execution, there may be inconsistency in the
      User_var_log_event between the variable value and its
      unsigned_flag.
            
      We fix this by also copying the unsigned_flag of the
      user_var_entry when its value is copied, for binlogging
      purposes. Later, at binlogging time, we use the copied
      unsigned_flag and not the one in the runtime user_var_entry
      instance.
[17 Mar 2010 0:06] Luis Soares
Please ignore commit http://lists.mysql.com/commits/103520
([17 Mar 0:58] Bugs System). This was intended for BUG#51426 
instead.
[24 Mar 2010 8:15] Bugs System
Pushed into 6.0.14-alpha (revid:alik@sun.com-20100324081249-yfwol7qtcek6dh7w) (version source revid:alik@sun.com-20100324081113-kc7x1iytnplww91u) (merge vers: 6.0.14-alpha) (pib:16)
[24 Mar 2010 8:16] Bugs System
Pushed into 5.5.4-m3 (revid:alik@sun.com-20100324081056-6bndv6f0nrvbblhp) (version source revid:alik@sun.com-20100324081056-6bndv6f0nrvbblhp) (merge vers: 5.5.4-m3) (pib:16)
[24 Mar 2010 8:18] Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20100324081159-5b8juv8ldiqwce8v) (version source revid:alik@sun.com-20100324081105-y72rautcea375zxm) (pib:16)
[15 Jun 2010 8:30] Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20100615080558-cw01bzdqr1bdmmec) (version source revid:mmakela@bk-internal.mysql.com-20100415070122-1nxji8ym4mao13ao) (pib:16)
[4 Aug 2010 8:06] Bugs System
Pushed into mysql-trunk 5.6.1-m4 (revid:alik@ibmvm-20100804080001-bny5271e65xo34ig) (version source revid:mmakela@bk-internal.mysql.com-20100415070122-1nxji8ym4mao13ao) (merge vers: 5.1.47) (pib:18)
[4 Aug 2010 8:22] Bugs System
Pushed into mysql-trunk 5.6.1-m4 (revid:alik@ibmvm-20100804081533-c1d3rbipo9e8rt1s) (version source revid:mmakela@bk-internal.mysql.com-20100415070122-1nxji8ym4mao13ao) (merge vers: 5.1.47) (pib:18)
[4 Aug 2010 10:36] Jon Stephens
Setting back to Closed state without further action, per earlier comments from myself and Luís.