MySQL Forums
Forum List  »  InnoDB

FK problem: ibd file does not currently exist
Posted by: Richard Gundersen
Date: April 26, 2005 12:30PM


I've got a strange foreign key constraint problem in that when I try to do an insert into a table with some foreign keys, even though those keys reference valid fields in valid tables, I get the "cannot add or update...." [1216] error message.

I ran SHOW INNODB STATUS and got the following:

050426 19:16:38 Transaction:
TRANSACTION 0 17920, ACTIVE 0 sec, OS thread id 3584 inserting, thread declared inside InnoDB 500
mysql tables in use 1, locked 1
3 lock struct(s), heap size 320, undo log entries 1
MySQL thread id 1, query id 5 localhost ODBC update
insert into resource_attribute_definition_assoc (resource_id,attribute_definition_id) VALUES (3,1)
Foreign key constraint fails for table `bdb_3829306995174123066/resource_attribute_definition_assoc`:
CONSTRAINT `resource_attribute_definition_assoc_ibfk_2` FOREIGN KEY (`attribute_definition_id`) REFERENCES `attribute_definition` (`id`)
Trying to add to index `attribute_definition_index` tuple:
DATA TUPLE: 2 fields;
0: len 3; hex 800001; asc ;; 1: len 3; hex 800003; asc ;;

But the parent table `bdb_3829306995174123066/attribute_definition`
or its .ibd file does not currently exist!

The table in question is defined with this SQL:

CREATE TABLE resource_attribute_definition_assoc (
resource_id mediumint(9) NOT NULL,
attribute_definition_id mediumint(9) NOT NULL,
KEY resource_index (resource_id),
KEY attribute_definition_index (attribute_definition_id),
PRIMARY KEY (resource_id, attribute_definition_id),
FOREIGN KEY (`resource_id`) REFERENCES `resource` (`id`),
FOREIGN KEY (`attribute_definition_id`) REFERENCES `attribute_definition` (`id`))

Please could someone tell me what this means? Are my indexes broken? I couldn't find any .ibd files under the <mysqlroot>/data directory. I tried a repair on all tables using Administrator with no success.

I've seen the error go away before by recreating the database, which may be fine while I'm in development mode, but when I move to production, I want to understand this problem so I can prevent it happening again.

Really appreciate any help you can give me


Options: ReplyQuote

Written By
FK problem: ibd file does not currently exist
April 26, 2005 12:30PM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.