MySQL 5.5 foreign key constraint fails if foreign key exists

Question

Just install MySQL 5.5 on mac os x 10.6 and I have a strange problem in many tables. The following is an example. Insertion of a row is not performed with a foreign key constraint when it should not. The foreign key that it refers to exists. Any ideas?

mysql> show create table Language; +----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Language | CREATE TABLE `Language` ( `Id` int(11) NOT NULL AUTO_INCREMENT, `Code` varchar(2) NOT NULL, `Name` varchar(63) CHARACTER SET utf8 DEFAULT NULL, `Variant` varchar(63) CHARACTER SET utf8 DEFAULT NULL, `Country_Id` int(11) DEFAULT NULL, PRIMARY KEY (`Id`), UNIQUE KEY `Code` (`Code`,`Country_Id`,`Variant`), KEY `FKA3ACF7789C1796EB` (`Country_Id`), CONSTRAINT `FKA3ACF7789C1796EB` FOREIGN KEY (`Country_Id`) REFERENCES `Country` (`Id`) ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1 | +----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> show create table Language_Phrases; +------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Language_Phrases | CREATE TABLE `Language_Phrases` ( `Language_Id` int(11) NOT NULL, `Phrase` varchar(255) DEFAULT NULL, `Label` varchar(255) NOT NULL, PRIMARY KEY (`Language_Id`,`Label`), KEY `FK8B4876F3AEC1DBE9` (`Language_Id`), CONSTRAINT `FK8B4876F3AEC1DBE9` FOREIGN KEY (`Language_Id`) REFERENCES `Language` (`Id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 | +------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> select * from Language; +----+------+----------+---------+------------+ | Id | Code | Name | Variant | Country_Id | +----+------+----------+---------+------------+ | 1 | en | English | | 235 | | 2 | ro | Romanian | | 181 | +----+------+----------+---------+------------+ 2 rows in set (0.00 sec) mysql> select * from Language_Phrases; Empty set (0.00 sec) mysql> INSERT INTO Language_Phrases (Language_Id, Label, Phrase) VALUES (1, 'exampleLabel', 'Some phrase'); ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`dev`.`language_phrases`, CONSTRAINT `FK8B4876F3AEC1DBE9` FOREIGN KEY (`Language_Id`) REFERENCES `Language` (`Id`)) mysql> 

UPDATE After you deleted and recreated the database several times, I did show engine innodb status after a failed insert above and got this unexpected result. Parent language table not found! It seems very strange ... any ideas?

 ------------------------ LATEST FOREIGN KEY ERROR ------------------------ 110406 9:55:49 Transaction: TRANSACTION CA3B, ACTIVE 0 sec, OS thread id 4494462976 inserting mysql tables in use 1, locked 1 1 lock struct(s), heap size 376, 0 row lock(s) MySQL thread id 25, query id 50720 localhost root update INSERT INTO Language_Phrases (Language_Id, Label, Phrase) VALUES (1, 'exampleLabel', 'Some phrase') Foreign key constraint fails for table `dev`.`language_phrases`: , CONSTRAINT `FK8B4876F3AEC1DBE9` FOREIGN KEY (`Language_Id`) REFERENCES `Language` (`Id`) Trying to add to index `PRIMARY` tuple: DATA TUPLE: 5 fields; 0: len 4; hex 80000001; asc ;; 1: len 17; hex 747970654d69736d617463682e79656172; asc exampleLabel;; 2: len 6; hex 00000000ca3b; asc ;;; 3: len 7; hex 00000000000000; asc ;; 4: len 21; hex 59656172206d7573742062652061206e756d626572; asc Some phrase;; But the parent table `dev`.`Language` or its .ibd file does not currently exist! 

UPDATE 2 . Turns out this is just a massive bug in MySQL. Apparently, the latest versions of MySQL do not work fully under Mac OS X 10.6 (perhaps earlier versions too?). Downgrade to 5.5.8 seems to work. Extremely amazing.

Answer 1

This seems to be a bug introduced with MySQL 5.5.9 on Mac OS X: http://bugs.mysql.com/bug.php?id=60309

It is marked as fixed in 5.5.13 (released May 31) and is listed in the release notes: http://dev.mysql.com/doc/refman/5.5/en/news-5-5-13.html

Alternatively, there is a workaround indicated in the error report that I checked in 5.5.10 and reproduced below:

 [20 Mar 11:29] Harald Neiss

 I also received a new MBP and reinstalled MySQL (mysql-5.5.10-osx10.6-x86_64).  Finally i
 came across the same problem as described above.  So here is the query result and what I
 did to solve it.

 mysql> show variables like 'lower%';
 + ------------------------ + ------- +
 |  Variable_name |  Value |
 + ------------------------ + ------- +
 |  lower_case_file_system |  ON |
 |  lower_case_table_names |  2 |
 + ------------------------ + ------- +
 2 rows in set (0.00 sec)

 Dropped database, created the file /etc/my.cnf with the following content:

 [mysqld]
 lower_case_table_names = 1

 Restarted the MySQL daemon and repeated the query:

 mysql> show variables like 'lower%';
 + ------------------------ + ------- +
 |  Variable_name |  Value |
 + ------------------------ + ------- +
 |  lower_case_file_system |  ON |
 |  lower_case_table_names |  1 |
 + ------------------------ + ------- +
 2 rows in set (0.00 sec)

 I recreated the tables and everything works fine.

Answer 2

No wonder IMHO. I have found many errors in MySQL. For example, executing queries with sentences such as "WHERE some_tinyint_column = 0" will not produce any data if necessary, but rewriting the sentence as "WHERE (NOT some_tinyint_column = 1)" produces results. After some research, I found that it was a bug that should have been fixed, but in the release that I used, the bug was still there.

Conclusion: when something is completely meaningless in MySQL, I usually find it safe to consider it a mistake and start exploring the information on these lines.

Answer 3

check the attributes of the numeric type Language_Phrases (Language_Id) and Language ( Id )

both must be either UNSIGNED ZEROFILL or SIGNED

Answer 4

* mysql> INSERT INTO Language_Phrases (Language_Id, Label, Phrase) VALUES (1, 'exampleLabel', 'Some phrase'); ERROR 1452 (23000): Unable to add or update child row: ... *

You are trying to insert 1 as Language_Id, but the Language table has the AUTO_INCREMENT = 3 property. In this case you should use 3 or higher.

Answer 5

Today I had the same error. In my case, I used a script to recreate several tables with all their records.

In fact, I realized that the "Engine" type is different from my tables: one of them is MyISAM, and the second (FK link) is InnoDB. I changed all my tables in InnoDB and now everything is working fine.

This script will generate a script update file ( Link )

 mysql -u DB_USER -pDB_PASSWORD --default-character-set=utf8 DATABASE_NAME -e "SELECT CONCAT('ALTER TABLE ', table_name, ' ENGINE=InnoDB;') AS sql_statements FROM information_schema.tables AS tb WHERE table_schema = database() AND ENGINE = 'MyISAM' AND TABLE_TYPE = 'BASE TABLE' ORDER BY table_name DESC;" > ./alter_InnoDb.sql 

You must delete the first line in the line "alter_InnoDb.sql" containing the text "sql_statements".

After that, you can execute the script in your database to fix this error:

 mysql -u DB_USER -pDB_PASSWORD --default-character-set=utf8 DATABASE_NAME < ./ alter_InnoDb.sql