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

Reported by Fernando Ipar on 2013-04-15
6
This bug affects 1 person
Affects Status Importance Assigned to Milestone
Percona Toolkit
Undecided
Unassigned

Bug Description

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)

tags: added: pt-fifo-split
Changed in percona-toolkit:
status: New → Confirmed
To post a comment you must log in.
This report contains Public information  Edit
Everyone can see this information.

Other bug subscribers