You bring data into a referenced column through LOAD DATA LOCAL INFILE but when you try to insert data into the referencing column you get the "Cannot add or update a foreign key constraint fails...." error. You have looked at the referenced column and the referencing data again and again and you don't see the problem.
If you open up Query Browser and look at the referenced data you may find paragraph marks. This problem confounded me for quite some time. I understood that the paragraph marks were causing the problem but I didn't understand how they were getting there.
This is a classic case where "RTFM" applies. MySQL by default understands line termination by the return character "\r". The Windows OS by default writes line termination as a carriage return and a new line "\r\n". The extra \n is causing the problem.
How to deal with this is right in the MySQL manual. But I thought I would post it because I do and did "RTFM" and still missed it. So maybe I can save someone else the frustration. The solution is to simply use LINES TERMINATED BY... at the end of your load data statement like so
LOAD DATA LOCAL INFILE "/path/to/file" INTO TABLE tablename LINES TERMINATED BY '\r\n'.
This can also cause you trouble in reverse if you are importing referencing data with the wrong kind of line termination
If you open up Query Browser and look at the referenced data you may find paragraph marks. This problem confounded me for quite some time. I understood that the paragraph marks were causing the problem but I didn't understand how they were getting there.
This is a classic case where "RTFM" applies. MySQL by default understands line termination by the return character "\r". The Windows OS by default writes line termination as a carriage return and a new line "\r\n". The extra \n is causing the problem.
How to deal with this is right in the MySQL manual. But I thought I would post it because I do and did "RTFM" and still missed it. So maybe I can save someone else the frustration. The solution is to simply use LINES TERMINATED BY... at the end of your load data statement like so
LOAD DATA LOCAL INFILE "/path/to/file" INTO TABLE tablename LINES TERMINATED BY '\r\n'.
This can also cause you trouble in reverse if you are importing referencing data with the wrong kind of line termination
Comments