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

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

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

Comparisons to Hitler

Hitler was an evil man and you know that from the fruit of his work, the mass annihilation of ethnic groups. He was pure evil. He killed people simply for their Israelite heritage. That being the case, it's only natural in the modern American political landscape that from time to time you see various politicians likened to Hitler by their detractors. After his comments on general immigration and specifically Muslim immigrants and visitors, Donald Trump is the latest target of the "Hitler Attack"and his supporters are likened to Hitler's supporters. Don't get me wrong, I'm no Trump supporter. I think the guy is a joke and frankly, I wish he would sit down and shut up. However, to compare him to Hitler; that's wrong. It is as wrong to the Jewish people, the cripples, the infirm, and the rest of the "useless" people who suffered under the Nazi regime as it is to Trump himself. If you really feel the need to compare someone or something in Am...

Praying and Running

Search This Blog