MySQL Bugs: #70277: last argument of LOAD DATA ... SET ... statement repeated twice in binlog

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	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Replication	Severity:	S3 (Non-critical)
Version:	5.5.33	OS:	Linux
Assigned to:		CPU Architecture:	Any

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 .

MTR test to repeat the bug

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

Hello Vlad,

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

Thanks,
Umesh

// 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')

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.

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.