pt-fifo-split does not work for blob/text columns with multiline data

[Fernando Ipar]

LOAD DATA INFILE used with pt-fifo-split will write bad data to the destination table, if the dump file has newlines in text columns.

Example:

mysql [localhost] {msandbox} (test) > select * from orig_sample\G
*************************** 1. row ***************************
instanceid: 1
   chunkid: 1
       tag: 1
val_binary: asd
dpo
pdd
*************************** 2. row ***************************
instanceid: 1
   chunkid: 2
       tag: 4
val_binary: asd
dpo
pdd
2 rows in set (0.00 sec)

mysql [localhost] {msandbox} (test) > show create table orig_sample\G
*************************** 1. row ***************************
       Table: orig_sample
Create Table: CREATE TABLE `orig_sample` (
  `instanceid` int(11) unsigned NOT NULL,
  `chunkid` smallint(6) unsigned NOT NULL,
  `tag` smallint(6) unsigned NOT NULL,
  `val_binary` longblob,
  PRIMARY KEY (`instanceid`,`tag`,`chunkid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql [localhost] {msandbox} (test) > select * from orig_sample into outfile './orig_sample';
Query OK, 2 rows affected (0.00 sec)

mysql [localhost] {msandbox} (test) > select * from sample;
Empty set (0.00 sec)

mysql [localhost] {msandbox} (test) > show create table sample\G
*************************** 1. row ***************************
       Table: sample
Create Table: CREATE TABLE `sample` (
  `instanceid` int(11) unsigned NOT NULL,
  `chunkid` smallint(6) unsigned NOT NULL,
  `tag` smallint(6) unsigned NOT NULL,
  `val_binary` longblob,
  PRIMARY KEY (`instanceid`,`tag`,`chunkid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

-- on a shell:
-- telecaster:msb_5_5_27 fernandoipar$ pt-fifo-split --lines 1 data/orig_sample

mysql [localhost] {msandbox} (test) > load data infile '/tmp/pt-fifo-split' into table sample;
Query OK, 1 row affected (0.00 sec)
Records: 1 Deleted: 0 Skipped: 0 Warnings: 0

mysql [localhost] {msandbox} (test) > load data infile '/tmp/pt-fifo-split' into table sample;
Query OK, 1 row affected, 4 warnings (0.00 sec)
Records: 1 Deleted: 0 Skipped: 0 Warnings: 4

mysql [localhost] {msandbox} (test) > load data infile '/tmp/pt-fifo-split' into table sample;
ERROR 1062 (23000): Duplicate entry '0-0-0' for key 'PRIMARY'

mysql [localhost] {msandbox} (test) > select * from sample\G
*************************** 1. row ***************************
instanceid: 0
   chunkid: 0
       tag: 0
val_binary: NULL
*************************** 2. row ***************************
instanceid: 1
   chunkid: 1
       tag: 1
val_binary: asd

2 rows in set (0.00 sec)

[Shahriyar Rzayev (rzayev-sehriyar)]

Percona now uses JIRA for bug reports so this bug report is migrated to: https://jira.percona.com/browse/PT-1097