1.版本
1)操作系统
cat /etc/issue
cat /etc/issue
CentOS release 6.6 (Final)
Kernel \r on an \m
cat /proc/version
cat /proc/version
Linux version 2.6.32-504.el6.x86_64 (mockbuild@c6b9.bsys.dev.centos.org) (gcc version 4.4.7 20120313 (Red Hat 4.4.7-11) (GCC) ) #1 SMP Wed Oct 15 04:27:16 UTC 2014
2)mysql数据库版本
mysql --version
mysql Ver 14.14 Distrib 5.1.73, for redhat-linux-gnu (x86_64) using readline 5.1
2.问题描述
2.1 发现问题
今天在个mysql库下建表,报如下错误:
ERROR 1005 (HY000): Can't create table 'log.test6' (errno: 121)
建表语句如下:
CREATE TABLE `test6` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`title` varchar(100) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
`content` text CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
`user_id` int(11) NOT NULL,
CONSTRAINT `FK_TEST_ID` FOREIGN KEY (`id`) REFERENCES `test3` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
3. 问题分析
3.1 直接从上面的报错,我们无法看出是什么原因导致建表语句报错。我们可以在操作系统下使用perror 查看121错误代码相关信息:
perror 121
OS error code 121: Remote I/O error
MySQL error code 121: Duplicate key on write or update
通过上面 perror 121输出的信息(Duplicate key),我们就大概能判断出问题原因了。应该是外键名(不区分大小写)重复导致的(因为我们指定索引名是可以重复的)。
3.2 下面我们来验证一下我们的判断
1)创建 test3表
CREATE TABLE test3 (
id int(11) DEFAULT NULL,
name varchar(20) DEFAULT '0',
KEY (id)) engine=innodb;
2)创建test4表(并创建外键约束)
CREATE TABLE `test4` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`title` varchar(100) NOT NULL,
`user_id` int(11) NOT NULL,
CONSTRAINT `FK_TEST_ID` FOREIGN KEY (`id`) REFERENCES `test3` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
3)创建test5表,并指定重名外键FK_TEST_ID
CREATE TABLE `test5` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`title` varchar(100) NOT NULL,
`user_id` int(11) NOT NULL,
CONSTRAINT `FK_TEST_ID` FOREIGN KEY (`id`) REFERENCES `test3` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
ERROR 1005 (HY000): Can't create table 'log.test5' (errno: 121)
4)先建表后添加外键
CREATE TABLE `test5` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`title` varchar(100) NOT NULL,
`user_id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
alter table test5 add constraint FK_TEST_ID foreign key(id) REFERENCES test3(id);
ERROR 1005 (HY000): Can't create table 'log.#sql-2a43_e91' (errno: 121)
上面是5.1.73上的报错,同样的场景发生在5.6时报错如下:
alter添加外键遭遇重名报错如下:
ERROR 1022 (23000): Can't write; duplicate key in table '#sql-1106_10'
create 建表时指定外键遭遇重名报错如下:
ERROR 1022 (23000): Can't write; duplicate key in table 'test6'
##可以看出5.6的报错比5.1的跟易读。上面的测试,验证了我们的判断。(注意仅适用于innodb表,并且并应用为外键的列必须创建索引)。
如果引用的外键列没有索引,创建外键时在5.1 和5.6 下会分别报如下错误:
5.1 报如下错误
ERROR 1005 (HY000): Can't create table 'test.test4' (errno: 150)
5.6 报如下错误
ERROR 1215 (HY000): Cannot add foreign key constraint
4. 解决方案
指定不同的外键名称(注意不区分大小写即abc同ABc被视为重复)