| 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: | |
| 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 | CPU Architecture: | Any |
| Tags: | signness, user var replication | ||
[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.
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