Insert into a foreign key field looks like it should match.....

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

Comments

A Man Full of Leprosy

In the Gospel reading for Mass today, LK 5:12-16 we read Now there was a man full of leprosy in one of the towns where he was; and when he saw Jesus, he fell prostrate, pleaded with him, and said, “Lord, if you wish, you can make me clean.” LK 5:12 The weather this morning forced me into the gym and onto the treadmill. So as I was runningandpraying on the treadmill I kept coming back to this first verse of today's Gospel. This verse I think addresses 2 profound yet prevalent misconceptions about Christianity. On one hand, there is a notion that Christians believe they are perfect and don't sin and don't need to be made clean. But that is just not true. We are all like this leper, full of sin that is eating away our life. That doesn't mean we cannot approach our Lord. Rather, it means that we must approach Him because He is the one who can make us clean. On the other hand, a lot of people have the idea that Christians think they merely need to say "Jesus i...

2013 BeeBumble 10k: Race Report

This has quickly become one of my favorite races of the year and I am not alone because there was a record 575 participants across the different events this year. I like the small town atmosphere, I like the course that is an out and back on a country road, and the timing makes it a perfect tune up race for the fall season. After my performance here the last two years I am also really beginning to appreciate it for the confidence booster it is and it's ability to add a breath of fresh air to the end of the training cycle for the 2013 Indianapolis Monumental Marathon. For this race I added an early morning shake out run to get my body primed for running. This was just a short and easy 20 minute run very first thing out of bed. Nearly all of my running is first thing in the morning but I've never done it as a shakeout for a race. I liked it and think I'll keep it. The last 5k I ran I didn't do a warm up and I learned my lesson because it cost me a very slow fi...

Running Goals or Running Goal; What is it That I Want?

The other day I got all caught up in setting running goals for myself for the rest of the year. After the goals were set, I found myself starting to worry about how I was going to reach them. Wondering if there would be time to sufficiently prepare for them all. My goals were to run a 5k in under 20 minutes, to run a half marathon in 1:25 minutes and qualify for and run in the Boston marathon in 2014. At first, I thought that setting and meeting these goals would make me a more serious runner. In fact, I thought that to be the "serious" runner I want to be that I needed these goals. However, while I was running the other morning I realized that first of all, I was thinking about how to shift my training to meet my 5k goal. Then I began thinking about the right approach to a 1:25 half while training to qualify for Boston in a full marathon a month later. That is when I realized that the ancillary goals were starting to consume my focus and quite possibly i...

Praying and Running

Search This Blog