Mysql LOAD DATA INFILE - input data unexpectedly truncated

Question

I'm trying to load data into a table (obviously?). My table looks like this:

CREATE TABLE IF NOT EXISTS `condensed` (
  `id` bigint(20) NOT NULL,
  `src` enum('C_X','C_AH','C_AO','C_B','H_X','H_AH','H_AO','H_B') NOT NULL,
  `hash` int(11) default NULL,
  `ihash` int(11) default NULL,
  `last_updated` datetime default NULL,
  PRIMARY KEY  (`id`,`src`),
  UNIQUE KEY `covering` (`id`,`src`,`hash`)
) ENGINE=MyISAM DEFAULT CHARSET=ascii;

I've got data files with look like this:

320115816,'C_X',692983698,854142703,20120216220954
320124536,'C_X',588472049,1059436251,20100527232845
320120196,'C_X',452117509,855369958,20101118105505
...

But when I load it using

LOAD DATA INFILE '/path/to/data.csv' 
IGNORE
INTO TABLE `condensed` 
(id, src, hash, ihash, last_updated);

it only loads the first two columns (hash, ihash and last_updated are null).

320115816,'C_X',NULL,NULL,NULL
320124536,'C_X',NULL,NULL,NULL
320120196,'C_X',NULL,NULL,NULL
...

I do get a lot of warnings (presumably because mysql is discarding the 3 columns from the input set and assigning defaults)

Query OK, 20 rows affected, 100 warnings (0.00 sec)
Records: 20  Deleted: 0  Skipped: 0  Warnings: 100

(I intend to load several milion records - not just 20)

I get the same problem using mysqlimport.

Omitting an explicit field list from the LOAD DATA statement (same fields and order in database as in files) resulted in the same outcome.

MySQL version is 5.0.22, there are no non-printable characters in the input file.

Help!

Adding NOT NULL constraints to the three mising columns just resulted in them being populated with 0s — symcbean, Jul 10 '12 at 12:14
Tried `lines terminated by '\n' starting by ''` - didn't help. — symcbean, Jul 10 '12 at 12:20
look this post: http://stackoverflow.com/questions/2675323/mysql-load-null-values-from-csv-data and this: http://stackoverflow.com/questions/7587660/filter-null-or-empty-input-using-load-data-infile-in-mysql — jcho360, Jul 10 '12 at 12:26
@jcho360: thanks - but these are both about explicitly handling nulls in the origin dataset and ensuring they are replicated into the table - that's not my problem. — symcbean, Jul 10 '12 at 13:14
In the meantime I've written an awk script to covert the file into INSERT multi statements - but it's rather slow compared load data infile :( — symcbean, Jul 10 '12 at 13:20

score 1 · Answer 1 · answered Jul 12 '12 at 12:39

1

Just add some improvement to Thilo's answer if you are using Windows.

LOAD DATA INFILE '/path/to/data.csv' IGNORE 
INTO TABLE `condensed` 
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '\''
LINES TERMINATED BY '\r\n' --Windows right line terminator
(id, src, hash, ihash, last_updated)

It worked for me. It solved all my truncating problems on Windows. Also have a look at this : http://forums.mysql.com/read.php?79,76131,76871

answered Jul 12 '12 at 12:39

SuwSuw

11
1

I'm not using NSWindows - but I did check before posting the original question - there are no carriage retrurns in the file :( – symcbean Jul 13 '12 at 08:19
But I'll check the FIELDS TERMINATED BY – symcbean Jul 13 '12 at 08:20

score 1 · Accepted Answer · answered Apr 23 '13 at 08:43

1

Never managed to resolve this. I ended up writing a wee php script to map the data into the db.

answered Apr 23 '13 at 08:43

symcbean

47,736
6
59
94

score 1 · Answer 3 · answered Dec 02 '15 at 22:37

1

It's worth noting that, if the field MySQL is complaining about happens to be the final one in the table, there's a chance that you need to fix the FIELDS TERMINATED BY. On Windows I had to tell it \n instead of \r\n.

answered Dec 02 '15 at 22:37

Alkanshel

4,198
1
35
54

score 1 · Answer 4 · answered Jun 26 '17 at 13:48

I was having similar problems with a CSV file created on an IBM mainframe that was moved to a Windows file server before being loaded. I was getting a truncation warning on all rows except the last. Mainframe file looked okay. Adding '\r\n' cleared the problem.

score 0 · Answer 5 · answered Jul 10 '12 at 12:37

0

I think the quotes around only the enum field are confusing the import. Try this:

LOAD DATA INFILE '/path/to/data.csv' IGNORE 
INTO TABLE `condensed` 
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '\''
(id, src, hash, ihash, last_updated)

answered Jul 10 '12 at 12:37

Thilo

17,565
5
68
84

Nope - tried loading it without the quotes around the enum and it had no affect. Explicitly adding `fields terminated by '\''` resulted in the hash and ihash columns populating, the enum was null (despite the non-null constraint) and the timestamp was null. – symcbean Jul 10 '12 at 13:16
Curious - it worked for me with your table definition and data. MySQL 5.5.15. – Thilo Jul 10 '12 at 13:26
Take a look at your warnings (show warnings limit 10); you'll probably be seeing what I saw: " Data truncated for column 'id'". That indicates it's reading past the commas for the first field and then the data is too long. So it's definitely a matter of explicitly specifying the field separators and enclosures. – Thilo Jul 10 '12 at 13:31
If you don't get it to work, your best bet might be to enclose ALL fields with single quotes in your data. – Thilo Jul 10 '12 at 13:32
There is a similar (*NOT* same) logged as 18335 in the MySQL bug list - I suspect it is MySQL at fault here rather than me. Mesing around with quotes is not helping. – symcbean Jul 10 '12 at 16:03

Mysql LOAD DATA INFILE - input data unexpectedly truncated

5 Answers5