当外键存在时，MySQL 5.5外键约束失败 [英] MySQL 5.5 foreign key constraint fails when foreign key exists
本文介绍了当外键存在时，MySQL 5.5外键约束失败的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
刚刚安装MySQL 5.5在mac os x 10.6和我有一个奇怪的问题在许多表。下面是一个例子。在不应该使用外键约束时，插入行将失败。它引用的外键确实存在。任何想法？
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 ：删除并重新创建数据库几次，我做了一个显示引擎innodb状态后失败插入上面并得到这个令人惊讶的结果。未找到父语言表！这看起来很奇怪...任何想法？
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 ：只是MySQL中的大量错误。显然，最新版本的MySQL不能完全在mac os X 10.6（也许早期版本？）。降级到5.5.8似乎工作。非常令人惊讶。
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.
推荐答案
这似乎是一个错误，自从MySQL 5.5.9在Mac OS X：
http://bugs.mysql.com/bug.php?id=60309
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
在5.5.13（5月31日发布）中被标记为固定，并在发行说明中提到：
http://dev.mysql.com/doc/refman/5.5/en/news-5-5-13.html
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
或者，我已在5.5.10验证并在下面转载的错误报告中列出了一个解决方法：
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.
这篇关于当外键存在时，MySQL 5.5外键约束失败的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
