MySQL 5.5 foreign key constraint fails when foreig

问题:

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.

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

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

回答3:

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

both should be either UNSIGNED ZEROFILL or SIGNED

回答4:

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

回答5:

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