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

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...

2015 Valpo Half Marathon Race Report

This was my big tune-up race for the Indianapolis Monumental Marathon. I always run a half-marathon at this point in the build up to the Monumental to get a final big fitness boost, a reality check on where I am at fitness-wise and, if all goes well, probably the most important aspect is the confidence boost that I get. I got one heck of a confidence boost yesterday, 10/25/2015, at the Valpohalf Half-marathon in Valparaiso IN. Valparaiso is about 2 hours from home which is kind of right there on the line of driving on race morning or staying in a hotel the night before. This time we decided to get up and drive. Valparaiso is on central time which puts it an hour behind us. Meaning the 8:30 AM start was really a 9:30 AM start for me. Making the decision to drive that much easier. I have been dealing with some issues on the top of my right foot, which is probably extensor tendinitis, for the last couple of weeks. I saw my soft-tissue guy last Friday. He worked on it some and got...

My AMDG Run: An Answered Prayer.

I have never been a person to pray for specific favors. It never felt right to me. My prayers generally are for strength, guidance, an acceptance of what comes my way. That is always how I "felt" right praying. To pray for a specific favor like a good grade, to do well in some competition and things like that just always felt a little wrong and to pray for physical healing was something I never would have considered. I am not saying this was any kind of selflessness. I am not sure exactly what it was. A fear of seeming childish or perhaps it was out of fear that my faith would be shaken if the prayer wasn't answered. Regardless, this is how I generally pray. And that is not to say that I don't or didn't think that God doesn't concretely answer prayers. I believe that He does. For instance I have always prayed when overwhelmed something like "God, I cannot do this all alone, please help me" and I will somehow, someway find my way through whatever it...

Praying and Running

Search This Blog