MySQL 错误：错误 1005：无法创建表（错误号：121）
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12623651/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
ERROR: Error 1005: Can't create table (errno: 121)
提问by user1703514
I have troubles with forward engineeringmy MySQL database into WAMP server..
I was going to post an image of the schema but as this is my first post I can't.
forward engineering我的 MySQL 数据库进入 WAMP 服务器时遇到了问题。
Below is the executed script..
下面是执行的脚本..
use aquaticstar;
SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';
-- -----------------------------------------------------
-- Table `Students`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `Students` ;
CREATE TABLE IF NOT EXISTS `Students` (
`id` VARCHAR(10) NOT NULL ,
`studentName` VARCHAR(45) NOT NULL ,
`gender` CHAR NOT NULL ,
`birthDate` DATETIME NOT NULL ,
`mNo` VARCHAR(10) NOT NULL ,
`contactName` VARCHAR(45) NOT NULL ,
`contactEmail` VARCHAR(45) NOT NULL ,
`contactPhone` INT(10) NOT NULL ,
`startDate` DATETIME NOT NULL ,
`remarks` VARCHAR(200) NULL ,
PRIMARY KEY (`id`) )
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `Waiting List`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `Waiting List` ;
CREATE TABLE IF NOT EXISTS `Waiting List` (
`wait_id` VARCHAR(5) NOT NULL ,
`name` VARCHAR(45) NULL ,
`contactName` VARCHAR(45) NULL ,
`contactPhone` INT(10) NULL ,
`contactEmail` VARCHAR(45) NULL ,
`status` CHAR NULL ,
`remarks` VARCHAR(200) NULL ,
PRIMARY KEY (`wait_id`) )
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `Schedule`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `Schedule` ;
CREATE TABLE IF NOT EXISTS `Schedule` (
`lesson_id` VARCHAR(10) NOT NULL ,
`day` VARCHAR(3) NOT NULL ,
`branch` VARCHAR(30) NOT NULL ,
`level` VARCHAR(30) NOT NULL ,
`time` TIME NOT NULL ,
`ae` VARCHAR(45) NOT NULL ,
PRIMARY KEY (`lesson_id`) )
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `Link`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `Link` ;
CREATE TABLE IF NOT EXISTS `Link` (
`link_id` VARCHAR(10) NOT NULL ,
`id` VARCHAR(10) NOT NULL ,
`lesson_id` VARCHAR(10) NOT NULL ,
PRIMARY KEY (`link_id`) ,
INDEX `id_idx` (`id` ASC) ,
INDEX `lesson_id_idx` (`lesson_id` ASC) ,
CONSTRAINT `id`
FOREIGN KEY (`id` )
REFERENCES `Students` (`id` )
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `lesson_id`
FOREIGN KEY (`lesson_id` )
REFERENCES `Schedule` (`lesson_id` )
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `Attendance`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `Attendance` ;
CREATE TABLE IF NOT EXISTS `Attendance` (
`date` DATETIME NOT NULL ,
`attendance` VARCHAR(5) NOT NULL ,
`link_id` VARCHAR(10) NOT NULL ,
INDEX `link_id_idx` (`link_id` ASC) ,
CONSTRAINT `link_id`
FOREIGN KEY (`link_id` )
REFERENCES `Link` (`link_id` )
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;
SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
-- -----------------------------------------------------
-- Data for table `Students`
-- -----------------------------------------------------
START TRANSACTION;
INSERT INTO `Students` (`id`, `studentName`, `gender`, `birthDate`, `mNo`, `contactName`, `contactEmail`, `contactPhone`, `startDate`, `remarks`) VALUES ('s001', 'Sam Khew', 'm', '12/12/1991', 'nm', 'May Khew', '[email protected]', 0198829387, '12/07/2011', NULL);
INSERT INTO `Students` (`id`, `studentName`, `gender`, `birthDate`, `mNo`, `contactName`, `contactEmail`, `contactPhone`, `startDate`, `remarks`) VALUES ('s002', 'Joe Biden', 'm', '13/03/2003', 'nm', 'Layla Biden', '[email protected]', 0199283763, '14/05/2011', NULL);
INSERT INTO `Students` (`id`, `studentName`, `gender`, `birthDate`, `mNo`, `contactName`, `contactEmail`, `contactPhone`, `startDate`, `remarks`) VALUES ('s003', 'Bob Builder', 'm', '14/02/2002', 'LK920K', 'Mama Builder', '[email protected]', 0167728376, '29/02/2012', NULL);
INSERT INTO `Students` (`id`, `studentName`, `gender`, `birthDate`, `mNo`, `contactName`, `contactEmail`, `contactPhone`, `startDate`, `remarks`) VALUES ('s004', 'Kenny Koh', 'm', '18/02/1999', 'MM992', 'Lisa Koh', '[email protected]', 0123160231, '19/01/2012', NULL);
INSERT INTO `Students` (`id`, `studentName`, `gender`, `birthDate`, `mNo`, `contactName`, `contactEmail`, `contactPhone`, `startDate`, `remarks`) VALUES ('s005', 'Jane Doe', 'f', '29/09/1999', 'nm', 'Hymanie Doe', '[email protected]', 0127736254, '02/03/2012', NULL);
INSERT INTO `Students` (`id`, `studentName`, `gender`, `birthDate`, `mNo`, `contactName`, `contactEmail`, `contactPhone`, `startDate`, `remarks`) VALUES ('s006', 'Lola Lai', 'f', '02/05/2004', 'nm', 'Mark Lai', '[email protected]', 0198827365, '11/09/2011', NULL);
COMMIT;
-- -----------------------------------------------------
-- Data for table `Schedule`
-- -----------------------------------------------------
START TRANSACTION;
INSERT INTO `Schedule` (`lesson_id`, `day`, `branch`, `level`, `time`, `ae`) VALUES ('sat1_s4', 'Sat', 'Sunway', 'basic', '4pm', 'Aini');
INSERT INTO `Schedule` (`lesson_id`, `day`, `branch`, `level`, `time`, `ae`) VALUES ('sat1_s5', 'Sat', 'Sunway', 'basic', '5pm', 'Aini');
INSERT INTO `Schedule` (`lesson_id`, `day`, `branch`, `level`, `time`, `ae`) VALUES ('sat1_s6', 'Sat', 'Sunway', 'basic', '6pm', 'Aini');
INSERT INTO `Schedule` (`lesson_id`, `day`, `branch`, `level`, `time`, `ae`) VALUES ('sat2_s4', 'Sat', 'Sunway', 'advance', '4pm', 'Nina');
INSERT INTO `Schedule` (`lesson_id`, `day`, `branch`, `level`, `time`, `ae`) VALUES ('sat2_s5', 'Sat', 'Sunway', 'advance', '5pm', 'Nina');
INSERT INTO `Schedule` (`lesson_id`, `day`, `branch`, `level`, `time`, `ae`) VALUES ('sat3_s6', 'Sat', 'Sunway', 'pre-comp', '6pm', 'Marcus');
COMMIT;
-- -----------------------------------------------------
-- Data for table `Link`
-- -----------------------------------------------------
START TRANSACTION;
INSERT INTO `Link` (`link_id`, `id`, `lesson_id`) VALUES ('L001', 's001', 'sat1_s4');
INSERT INTO `Link` (`link_id`, `id`, `lesson_id`) VALUES ('L002', 's002', 'sat1_s5');
INSERT INTO `Link` (`link_id`, `id`, `lesson_id`) VALUES ('L003', 's003', 'sat1_s6');
INSERT INTO `Link` (`link_id`, `id`, `lesson_id`) VALUES ('L004', 's004', 'sat2_s4');
INSERT INTO `Link` (`link_id`, `id`, `lesson_id`) VALUES ('L005', 's005', 'sat1_s5');
COMMIT;
-- -----------------------------------------------------
-- Data for table `Attendance`
-- -----------------------------------------------------
START TRANSACTION;
INSERT INTO `Attendance` (`date`, `attendance`, `link_id`) VALUES ('26/9/2012', '1', NULL);
COMMIT;
But then I get this error:
但是后来我收到了这个错误：
Executing SQL script in server
ERROR: Error 1005: Can't create table 'aquaticstar.link' (errno: 121)
I can't figure out why. Can anyone help me?
我不明白为什么。谁能帮我？
回答by Dorvalla
I searched quickly for you, and it brought me here. I quote:
我很快就找到了你，它把我带到了这里。我引用：
You will get this message if you're trying to add a constraint with a name that's already used somewhere else
如果您尝试使用已在其他地方使用的名称添加约束，您将收到此消息
To check constraints use the following SQL query:
要检查约束，请使用以下 SQL 查询：
SELECT
constraint_name,
table_name
FROM
information_schema.table_constraints
WHERE
constraint_type = 'FOREIGN KEY'
AND table_schema = DATABASE()
ORDER BY
constraint_name;
Look for more information there, or try to see where the error occurs. Looks like a problem with a foreign key to me.
在那里查找更多信息，或尝试查看错误发生的位置。对我来说看起来像是外键的问题。
回答by Chriki
Foreign Key Constraint Names Have to be Unique Within a Database
外键约束名称在数据库中必须是唯一的
Both @Dorvalla's answerand this blog postmentioned above pointed me into the right direction to fix the problem for myself; quoting from the latter:
无论@ Dorvalla的回答和这个博客帖子上面所指出我提到到正确的方向来解决这个问题的呢; 引用后者：
If the table you're trying to create includes a foreign key constraint, and you've provided your own name for that constraint, remember that it must be unique within the database.
如果您尝试创建的表包含外键约束，并且您为该约束提供了自己的名称，请记住它在数据库中必须是唯一的。
I wasn't aware of that. I have changed my foreign key constraint names according to the following schema which appears to be used by Ruby on Rails applications, too:
我不知道。我已经根据以下似乎被 Ruby on Rails 应用程序使用的模式更改了我的外键约束名称：
<TABLE_NAME>_<FOREIGN_KEY_COLUMN_NAME>_fk
For the OP's table this would be Link_lession_id_fk, for example.
例如，对于 OP 的表，这将是Link_lession_id_fk。
回答by Marc
You can login to mysql and type
您可以登录到mysql并键入
mysql> SHOW INNODB STATUS\G
You will have all the output and you should have a better idea of what the error is.
您将拥有所有输出，并且您应该更好地了解错误是什么。
回答by giuseppe
If you have a foreign key definition in some table and the name of the foreign key is used elsewhere as another foreign key you will have this error.
如果您在某个表中有外键定义，并且外键的名称在其他地方用作另一个外键，则会出现此错误。
回答by Patrick Brown
I faced this error (errno 121) but it was caused by mysql-created intermediate tables that had been orphaned, preventing me from altering a table even though no such constraint name existed across any of my tables. At some point, my MySQL had crashed or failed to cleanup an intermediate table (table name starting with a #sql-) which ended up presenting me with an error such as: Can't create table '#sql-' (errno 121) when trying to run an ALTER TABLE with certain constraint names.
我遇到了这个错误 (errno 121)，但它是由 mysql 创建的中间表引起的，这些表已经被孤立，即使我的任何表中都不存在这样的约束名称，我也无法更改表。在某些时候，我的 MySQL 崩溃或无法清理中间表（表名以 #sql- 开头），最终向我展示了一个错误，例如：无法创建表 '#sql-'（错误号 121）尝试使用某些约束名称运行 ALTER TABLE 时。
According to the docs at http://dev.mysql.com/doc/refman/5.7/en/innodb-troubleshooting-datadict.html, you can search for these orphan tables with:
根据http://dev.mysql.com/doc/refman/5.7/en/innodb-troubleshooting-datadict.html上的文档，您可以使用以下命令搜索这些孤立表：
SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES WHERE NAME LIKE '%#sql%';
The version I was working with was 5.1, but the above command only works on versions >= 5.6 (manual is incorrect about it working for 5.5 or earlier, because INNODB_SYS_TABLES does not exist in such versions). I was able to find the orphaned temporary table (which did not match the one named in the message) by searching my mysql data directory in command line:
我使用的版本是 5.1，但上面的命令仅适用于 >= 5.6 的版本（手册不正确，它适用于 5.5 或更早版本，因为此类版本中不存在 INNODB_SYS_TABLES）。通过在命令行中搜索我的 mysql 数据目录，我能够找到孤立的临时表（与消息中命名的表不匹配）：
find . -iname '#*'
After discovering the filename, such as #sql-9ad_15.frm, I was able to drop that orphaned table in MySQL:
发现文件名后，例如#sql-9ad_15.frm，我能够在 MySQL 中删除该孤立表：
USE myschema;
DROP TABLE `#mysql50##sql-9ad_15`;
After doing so, I was then able to successfully run my ALTER TABLE.
这样做之后，我就能够成功运行我的 ALTER TABLE。
For completeness, as per the MySQL documentation linked, "the #mysql50# prefix tells MySQL to ignore file name safe encoding introduced in MySQL 5.1."
为了完整起见，根据链接的 MySQL 文档，“#mysql50# 前缀告诉 MySQL 忽略 MySQL 5.1 中引入的文件名安全编码。”
回答by itsraja
If you want to fix quickly, Forward Engineer again and check "Generate DROP SCHEMA" option and proceed.
如果您想快速修复，请再次转发工程师并选中“Generate DROP SCHEMA”选项并继续。
I assume the database doesn't contain data, so dropping it won't affect.
我假设数据库不包含数据，所以删除它不会影响。
回答by Roozbeh G
Something I noticed was that I had "other_database" and "Other_Database" in my databases. That caused this problem as I actually had same reference in other database which caused this mysterious error!
我注意到我的数据库中有“other_database”和“Other_Database”。这导致了这个问题，因为我实际上在其他数据库中有相同的引用，导致了这个神秘的错误！
回答by phpWebStudio
mysql> SHOW ENGINE INNODB STATUS;
But in my case only this way could help:
1. Make backup of current DB
2. Drop DB (not all tables, but DB)
3. Create DB (check that you still have previleges)
4. Restore DB from backup
但在我的情况下，只有这种方式可以帮助：
1. 备份当前的 DB
2. 删除 DB（不是所有表，而是 DB）
3. 创建 DB（检查您是否仍然拥有特权）
4. 从备份中恢复 DB
