MySQL 5.5 foreign key constraint fails when foreign key exists

Question

Just installed MySQL 5.5 on mac os x 10.6 and am having a strange issue on many tables. Below is an example. Inserting a row fails with a foreign key constraint when it shouldn't. The foreign key it references does exist. 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 dropping and recreating the database several times, I did a show engine innodb status after the failing insert above and got this surprising result. The parent Language table is not found! This 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: It turns out this is simply a massive bug in MySQL. Apparently the latest versions of MySQL do not work fully under mac os X 10.6 (maybe earlier versions too?). Downgrading to 5.5.8 seems to work. Extremely surprising.

Campbellite

Is there a link to where this bug is mentioned? I'm having the same issue (mysql5.5 and osx10.6) and would like to read more on it. – Amphibian

I don't have the links handy, we found them in MySQL forums and there was a bug report as well. – Campbellite

Had this exact same problem, thanks for posting this guys. This has been driving my crazy for the past few days. – Afterheat

@GeorgeVelez - were you using the latest MySQL? – Campbellite

Answer 1

This does appear to be a bug introduced since 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 mentioned in the release notes: http://dev.mysql.com/doc/refman/5.5/en/news-5-5-13.html

Alternatively, there is a workaround listed in the bug report that I've verified on 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.

Amparoampelopsis

Thanks! I'll check out the latest version of mysql! – Campbellite

This problem cropped up for me again after upgrading to OS X Lion. The workaround I referenced above did not seem to work this time, though, so I was forced to upgrade MySQL. Version 5.5.14 installed via homebrew works a treat! – Amparoampelopsis

Answer 2

Not surprising IMHO. I have found numerous bugs in MySQL. For example running queries with where clauses such as "WHERE some_tinyint_column = 0" would produce no data when it should, but rewriting the clause as "WHERE (NOT some_tinyint_column = 1)" produces results. After some research I found that it was a bug that was supposed to have been fixed, but in the release I was using, the bug was still there.

Conclusion: when something makes absolutely no sense in MySQL, I usually find it safe to assume it's a bug and start researching for info along those lines.

Psycholinguistics

Answer 3

I was also facing the same issue and did not find any solution which solves my issue. So this issue can occur due to many reasons. I am just trying to put as many of these reasons and solutions at one place and also putting the fix which helped me to resolve this. I hope this will help someone in future.

1) Provided by Penfold - case sensitivity of table names
2) Engine mismatch in Parent and Child Table
3) Charset mismatch in Parent and Child Table
4) Parent(Id) and Child(Patent_Id : both must have exactly same data types (Also Signed/Unsigned)
5) InnoDB lost table but table exists - Please find solution here

SQL Query (show engine InnoDB status) gives this error (its .ibd file does not currently exist!) Blockquote

6) Few older versions of mysql has this bug.Bug#60196, Bug#60309
7) This works for me (if everything above seems fine, you probably needs this)- Dropping the foreign key from Child Table and Add Constraint again. If it also fails then drop parent table and create it again, before this you need to drop foreign key from all the Child Tables. This one is last possible thing.
Further Read :
https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_lower_case_table_names

Clog

Answer 4

check the Numeric Type Attributes of Language_Phrases (Language_Id) and Language (Id)

both should be either UNSIGNED ZEROFILL or SIGNED

Piker

how do I check or set whether they are signed or unsigned? – Campbellite

they are both int(11) according to describe – Campbellite

go to phpmyadmin and check for both..may be some space in attribute name?? – Piker

they're the same, it turns out this is a massive bug. MySQL latest versions are broken under mac os X 10.6. – Campbellite

Answer 5

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

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

Poultryman

I gave the output of Language, which includes 2 rows (that's the reason auto_increment=3) – Campbellite

Try to recreate the FK; there is the same error - forums.mysql.com/read.php?135,407929,407929#msg-407929 – Poultryman

dropping and recreating the foreign key doesn't fix the error. But interesting thread. It appears the last 3 versions of MySQL are quite broken, at least on Mac OS X 10.6. Downgrading to MySQL 5.5.8 worked for us as well as that thread... – Campbellite

Answer 6

I had the same error today. In my case, I've used a script to recreate a few tables with all their records.

In fact, I've realised that "Engine" type was different between my tables: one was MyISAM and the second one (the reference of the FK) was InnoDB. I've altered all my tables to InnoDB and now everything works fine.

This script will generate an update script file (Reference)

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 remove the first line in "alter_InnoDb.sql", line containing the text "sql_statements".

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

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

Comedic