1

I am getting an error when I try to load data into my MySQL (MariaDB actually) table using the load data command:

load data local infile '/tmp/my_data.tsv' 
into table my_schema.my_table
fields terminated by '\t' 
optionally enclosed by '"'
escaped by '\\'
lines terminated by '\n';

Trying the solutions given here and here did not work.

Community
  • 1
  • 1
ostrokach
  • 17,993
  • 11
  • 78
  • 90
  • Why downvote? I wasted almost an hour of my time trying to figure this out. And I was contemplating deleting the table because other posts said you get this error if your table is corrupt. – ostrokach May 31 '15 at 05:48

1 Answers1

0

For some reason, MySQL gives this obscure error message when the data you are loading breaks a foreign key constraint (described in more detail here).

I managed to get the correct error message by executing the load data command directly from the command line. i.e. running:

mysql -u username -p --local-infile --execute= \
"load data local infile '/tmp/my_data.tsv' into table my_schema.my_table \
fields terminated by '\t' optionally enclosed by '\"' escaped by '\\\\';"

gave the error message:

Cannot add or update a child row: a foreign key constraint fails 
(`my_schema`.`my_table`, CONSTRAINT `other_table` FOREIGN KEY
(`id`) REFERENCES `other_table` (`id`))

After removing rows that violated the foreign key constraint, I was able to import my data.

ostrokach
  • 17,993
  • 11
  • 78
  • 90