Bug #70277 last argument of LOAD DATA ... SET ... statement repeated twice in binlog
Submitted: 9 Sep 2013 10:54 Modified: 21 Oct 2013 15:56
Reporter: Vlad Lesin (OCA) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Replication Severity:S3 (Non-critical)
Version:5.5.33 OS:Linux
Assigned to:

[9 Sep 2013 10:54] Vlad Lesin
Description:
If LOAD DATA ... SET ... is used the last argument of SET is repeated twice in replication binlog.

How to repeat:
I used MTR test to repeat the bug. See this comment https://bugs.launchpad.net/percona-server/+bug/1208371/comments/5 .
[9 Sep 2013 10:56] Vlad Lesin
MTR test to repeat the bug

Attachment: bug-16753869.tar.bz2 (application/x-bzip, text), 1.38 KiB.

[9 Sep 2013 11:48] Umesh Shastry
Hello Vlad,

Thank you for the bug report. 
Verified as described on recent 5.5.33 version.

Thanks,
Umesh
[9 Sep 2013 11:50] Umesh Shastry
// How to repeat

// Dummy load file
// perl load1.pls > /tmp/load.data

# more load1.pl 
#!/usr/bin/perl

for($i=1; $i <= 100; $i++) {
  print "$i,$i abc123456789A123456789B123456789C123456789D123456789E123456789F123456789G123456789H123456789I\n";
}

// Test case

$ more t/70265.test 

FLUSH LOGS;
CREATE TABLE t1 (a INT PRIMARY KEY, b VARCHAR(1000));
LOAD DATA INFILE '/tmp/load.data' INTO TABLE t1
  FIELDS TERMINATED BY ','
    (@a, @b) SET a = @a, b = CONCAT(@b, '| 123456789');
FLUSH LOGS;

// Running mtr test case 
// ./mysql-test-run.pl --mysqld=--secure-file-priv=/tmp --mysqld=--log_bin 70265

// binlog contents

SET TIMESTAMP=1378726960/*!*/;
LOAD DATA LOCAL INFILE '/tmp/SQL_LOAD_MB-1-3' INTO TABLE `t1` FIELDS TERMINATED BY ',' ENCLOSED BY '' ESCAPED BY '\\' LINES TERMINATED BY '\n' (@`a`, @`b`) SET `a`= @a, b = CONCAT(@b, '| 123456789'), `b`= CONCAT(@b, '| 123456789')
[21 Oct 2013 15:56] Jon Stephens
Fixed in 5.5+. Documented in the 5.5.35, 5.6.15, and 5.7.3 changelogs, as follows:

        The final argument in the SET clause of a LOAD DATA ... SET
        statement was repeated in the binary log.

Closed.
[4 Dec 2013 9:31] Laurynas Biveinis
5.5$ bzr log -r 4509
------------------------------------------------------------
revno: 4509
committer: Sujatha Sivakumar <sujatha.sivakumar@oracle.com>
branch nick: Bug17429677_mysql-5.5
timestamp: Wed 2013-10-16 11:49:00 +0530
message:
  Bug#17429677:LAST ARGUMENT OF LOAD DATA ...SET ...STATEMENT
  REPEATED TWICE IN BINLOG
  
  Problem:
  =======
  If LOAD DATA ... SET ... is used the last argument of SET is
  repeated twice in replication binlog.
  
  Analysis:
  ========
  LOAD DATA statements are reconstructed once again before
  they are written to the binary log. When SET clauses are
  specified as part of LOAD DATA statement, these SET clause
  user command strings need to be stored in order to rebuild
  the original user command. During parsing each column and
  the value in the SET command are stored in two differenet
  lists. All the values are stored in a string list.
  
  When SET expression has more than one value as shown in the
  following example:
  SET a = @a, b = CONCAT(@b, '| 123456789');
  
  Parser extracts values in the following manner i.e Item name
  , value string, actual length of the value of the item with
  in the string.
  
  Item a:
  Value for a:"= @a, b = CONCAT(@b, '| 123456789')
  str_length = 4
  Item b:
  Value for b:"= CONCAT(@b, '| 123456789')
  str_length = 27
  
  During reconstructing the LOAD DATA command the above
  strings are retrived as it is and appended to the LOAD DATA
  statement. Hence it becomes as shown below.
  
  SET `a`= @a, b = CONCAT(@b, '| 123456789'),
  `b`= CONCAT(@b, '| 123456789')
  
  Fix:
  ===
  During reconstruction of SET command, retrieve exact item
  value string rather than reading the entire string.