外键存在时 MySQL 5.5 外键约束失败
刚刚在 mac os x 10.6 上安装了 MySQL 5.5，并且在许多表上遇到了一个奇怪的问题.下面是一个例子.在不应该插入行时，由于外键约束而失败.它引用的外键确实存在.有什么想法吗?
mysql>显示创建表语言；+----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+|表 |创建表 |+----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+|语言 |创建表`语言`(`Id` int(11) NOT NULL AUTO_INCREMENT,`代码` varchar(2) NOT NULL,`Name` varchar(63) CHARACTER SET utf8 DEFAULT NULL,`Variant` varchar(63) 字符集 utf8 默认为 NULL，`Country_Id` int(11) 默认为空，主键(`Id`)，唯一键`代码`(`代码`，`Country_Id`，`Variant`)，KEY `FKA3ACF7789C1796EB`(`Country_Id`)，约束`FKA3ACF7789C1796EB`外键(`Country_Id`)参考`Country`(`Id`)) 引擎=InnoDB AUTO_INCREMENT=3 默认字符集=latin1 |+----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+一组中的 1 行(0.00 秒)mysql>显示创建表 Language_Phrases;+------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+|表 |创建表 |+------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+|语言_短语 |创建表`Language_Phrases`(`Language_Id` int(11) NOT NULL,`Phrase` varchar(255) 默认为空，`标签` varchar(255) NOT NULL,主键(`Language_Id`，`Label`)，KEY `FK8B4876F3AEC1DBE9`(`Language_Id`)，约束`FK8B4876F3AEC1DBE9`外键(`Language_Id`)参考`Language`(`Id`)) 引擎=InnoDB 默认字符集=latin1 |+------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+一组中的 1 行(0.00 秒)mysql>从语言中选择 *；+----+------+----------+---------+------------+|身份证 |代码 |姓名 |变体 |Country_Id |+----+------+----------+---------+------------+|1 |zh |英语 ||235 ||2 |罗 |罗马尼亚语 ||181 |+----+------+----------+---------+------------+2 行(0.00 秒)mysql>从 Language_Phrases 中选择 *；空集(0.00 秒)mysql>INSERT INTO Language_Phrases (Language_Id, Label, Phrase) VALUES (1, 'exampleLabel', 'Some phrase');错误 1452 (23000):无法添加或更新子行:外键约束失败 (`dev`.`language_phrases`, CONSTRAINT `FK8B4876F3AEC1DBE9` FOREIGN KEY (`Language_Id`) REFERENCES`Language` (`Id`))mysql>更新:在多次删除并重新创建数据库后，我在上面插入失败后做了 show engine innodb status 并得到了这个令人惊讶的结果.未找到父语言表！这看起来很奇怪......有什么想法吗?
----------最新的外键错误----------------------110406 9:55:49 交易:事务 CA3B，活动 0 秒，操作系统线程 id 4494462976 正在插入mysql 表正在使用 1，锁定 11 个锁结构，堆大小 376，0 行锁MySQL 线程 id 25，查询 id 50720 localhost 根更新INSERT INTO Language_Phrases (Language_Id, Label, Phrase) VALUES (1, 'exampleLabel', 'Some phrase')表`dev`的外键约束失败.`language_phrases`:,约束`FK8B4876F3AEC1DBE9`外键(`Language_Id`)参考`Language`(`Id`)尝试添加到索引 `PRIMARY` 元组:数据元组:5个字段；0:长度 4；十六进制 80000001;升序;;1:长度 17；十六进制 747970654d69736d617463682e79656172；asc 示例标签；；2:长度 6；十六进制 00000000ca3b;升序;;;3:长度 7；十六进制 00000000000000;升序;;4:长度 21；十六进制 59656172206d7573742062652061206e756d626572；asc 一些短语;;但是父表`dev`.`Language`或其 .ibd 文件当前不存在！UPDATE 2:事实证明，这只是 MySQL 中的一个大量错误.显然，最新版本的 MySQL 在 mac os X 10.6 下不能完全运行(也许更早的版本也是如此?).降级到 5.5.8 似乎可行.非常令人惊讶.
解决方案这似乎是自 Mac OS X 上的 MySQL 5.5.9 以来引入的一个错误:http://bugs.mysql.com/bug.php?id=60309
在 5.5.13(5 月 31 日发布)中标记为已修复，并在发行说明中提到:http://dev.mysql.com/doc/refman/5.5/zh/news-5-5-13.html
或者，我在 5.5.10 验证并在下面复制的错误报告中列出了一种解决方法:
<上一页>[3 月 20 日 11:29] 哈拉尔德·奈斯我还收到了新的 MBP 并重新安装了 MySQL (mysql-5.5.10-osx10.6-x86_64).最后我遇到了与上述相同的问题.所以这是查询结果和我确实解决了它.mysql> 显示像'lower%'这样的变量；+------------------------+--------+|变量名 |价值 |+------------------------+--------+|小写文件系统 |开 ||小写表名 |2 |+------------------------+--------+2 行(0.00 秒)删除数据库，创建文件/etc/my.cnf，内容如下:[mysqld]lower_case_table_names=1重新启动 MySQL 守护程序并重复查询:mysql> 显示像'lower%'这样的变量；+------------------------+--------+|变量名 |价值 |+------------------------+--------+|小写文件系统 |开 ||小写表名 |1 |+------------------------+--------+2 行(0.00 秒)我重新创建了表格，一切正常.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.
解决方案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.
相关文章