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

Bug #1169019 reported by Fernando Ipar
6
This bug affects 1 person
Affects Status Importance Assigned to Milestone
Percona Toolkit moved to https://jira.percona.com/projects/PT
Confirmed
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
Revision history for this message
Shahriyar Rzayev (rzayev-sehriyar) wrote :

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

To post a comment you must log in.
This report contains Public information  
Everyone can see this information.

Other bug subscribers

Remote bug watches

Bug watches keep track of this bug in other bug trackers.