On 2/21/2005 4:21 AM Heikki Tuuri wrote:
John,
please print a detailed description of the latest foreign key error with
SHOW INNODB STATUS
and post it here.
Best regards,
Heikki Tuuri
Innobase Oy
Foreign keys, transactions, and row level locking for MySQL
InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up
MyISAM tables
http://www.innodb.com/order.php
Order MySQL Network from http://www.mysql.com/network/
Thank you! I won't bother to post the results because the command showed
me the problem. The problem appears to be that the parser doesn't skip
whitespace after separators as I assumed it did. The result is that my
key was "Administrator", but it was looking for " Administrator".
Removing all whitespace allowed it to work correctly.
To me, this parsing is either a bug or a "feature" that should be
documented in red letters. But it isn't a InnoDB issue.
Thanks again for your help by pointing out this very useful command.
- Original Message - From: "John Swartzentruber"
<[EMAIL PROTECTED]>
Newsgroups: mailing.database.myodbc
Sent: Sunday, February 20, 2005 11:54 PM
Subject: Problems with LOAD DATA INFILE
I am new to SQL and to MySQL, but am working with it on a project for a
graduate Database system course. I'm running MySQL 4.1.10 under Windows
2000. I am trying to load a table from a text file. The table in
question has a foreign key. The table that it references contains data.
The problem is that when I load the data using LOAD DATA LOCAL INFILE, I
get this error:
ERROR 1216 (23000): Cannot add or update a child row: a foreign key
constraint fails
That seems clear enough except that the foreign key constraint should
*not* fail. I've verified that the value exists in the other table.
What is really strange is that when I tried inserting the data directly
using "INSERT INTO", it works. In this particular case, I only need to
load a handful of records, so using INSERT INTO is an option, but I
would really like to figure out what isn't working.
Here is my table definition:
create table Subscriber (
UserIDint auto_increment,
Name varchar(50) not null,
Password varchar(8) not null,
EmailAddress varchar(50),
SGroupNamevarchar(50),
primary key (UserId),
foreign key (SGroupName) references AccessGroup(GroupName) on update
cascade
) ENGINE=INNODB;
Here is how I am attempting to load it:
LOAD DATA LOCAL INFILE 'c:/Documents and Settings/john/My Documents/Grad
School/Project/LoadData/Subscriber.csv'
REPLACE
INTO TABLE Subscriber
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
ESCAPED BY '\\'
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES
(Name, Password, EmailAddress, SGroupName);
Here are the first two lines of my data file:
Name, Password, EmailAddress, SGroupName
"John Swartzentruber", "8490JTTT", "[EMAIL PROTECTED]", Administrator
And here is what *does* work:
insert into Subscriber
(Name, Password, EmailAddress, SGroupName)
values ("John Swartzentruber", "8490JTTT", "[EMAIL PROTECTED]",
"Administrator");
Can anyone see what my problem is? I really appreciate any assistance
you can provide. I hope this is the appropriate group for newbie
questions.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
