ERROR: Error 1005: Can't create table (errno: 121)

Mysql

Mysql Problem Overview

I have troubles with forward engineering my 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.

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', 'Jackie 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?

Mysql Solutions

Solution 1 - Mysql

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:

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.

Solution 2 - Mysql

Foreign Key Constraint Names Have to be Unique Within a Database

Both @Dorvalla’s answer and this blog post mentioned above pointed me into the right direction to fix the problem for myself; quoting from the latter:

> 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:

<TABLE_NAME>_<FOREIGN_KEY_COLUMN_NAME>_fk

For the OP’s table this would be Link_lession_id_fk, for example.

Solution 3 - Mysql

You can login to mysql and type

mysql> SHOW INNODB STATUS\G

You will have all the output and you should have a better idea of what the error is.

Solution 4 - Mysql

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.

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:

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:

find . -iname '#*'

After discovering the filename, such as #sql-9ad_15.frm, I was able to drop that orphaned table in MySQL:

USE myschema;
DROP TABLE `#mysql50##sql-9ad_15`;

After doing so, I was then able to successfully run my 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."

Solution 5 - Mysql

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.

Solution 6 - Mysql

If you want to fix quickly, Forward Engineer again and check "Generate DROP SCHEMA" option and proceed.

I assume the database doesn't contain data, so dropping it won't affect.

Solution 7 - Mysql

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!

Solution 8 - Mysql

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
Categories
Recommended Mysql Solutions

Previous Solution:

How to get a string between two characters?

JavaString

Next Solution:

How do you specifically order ggplot2 x axis instead of alphabetical order?

RGgplot2R Faq

Attributions

All content for this solution is sourced from the original question on Stackoverflow.

The content on this page is licensed under the Attribution-ShareAlike 4.0 International (CC BY-SA 4.0) license.

Content TypeOriginal AuthorOriginal Content on Stackoverflow
Questionuser1703514View Question on Stackoverflow
Solution 1 - MysqlDorvallaView Answer on Stackoverflow
Solution 2 - MysqlChrikiView Answer on Stackoverflow
Solution 3 - MysqlMarcView Answer on Stackoverflow
Solution 4 - MysqlPatrick BrownView Answer on Stackoverflow
Solution 5 - MysqlgdmView Answer on Stackoverflow
Solution 6 - MysqlitsrajaView Answer on Stackoverflow
Solution 7 - MysqlRoozbeh GView Answer on Stackoverflow
Solution 8 - MysqlphpWebStudioView Answer on Stackoverflow