MySQL errorno 121

Question

I'm getting this error in MySQL create. I'm doing:

CREATE TABLE `blogReply` (

    `Id`      INT(24)      NOT NULL AUTO_INCREMENT COMMENT 'Primary Key of This Table',
    `blogId`  INT(24)      NOT NULL COMMENT 'Blog where this reply was posted',
    `userId`  INT(24)      NULL COMMENT 'User the blog was posted by',
    `name`    VARCHAR(100) NULL DEFAULT 'Unknown' COMMENT 'The Name of the user that the reply was posted by',
    `email`   VARCHAR(100) NULL DEFAULT 'Unknown' COMMENT 'The Email of the user that the reply was posted by',
    `http`    VARCHAR(300) NULL DEFAULT 'Unknown' COMMENT 'The Webaddress of the user that the reply was posted by',
    `message` TEXT         NOT NULL COMMENT 'text of the blog',
    `votes`   INT(10)      DEFAULT 0 COMMENT 'Rating of the Blog',
    `ratedBy` TEXT         COMMENT 'People who have already Voted on this blog',
    `dateReg` BIGINT       NOT NULL COMMENT 'Date the User was Registered',

    PRIMARY KEY (`Id`),

    CONSTRAINT `FK_userId` FOREIGN KEY(`userId`)
        REFERENCES `user` (`Id`)
        ON DELETE SET NULL
        ON UPDATE CASCADE,

    CONSTRAINT `FK_blogId` FOREIGN KEY(`blogId`)
        REFERENCES `blog` (`Id`)
        ON DELETE CASCADE
        ON UPDATE CASCADE

) ENGINE = InnoDB;

Any Ideas? The Error States: Can't create table './xxxxxxxx/blogReply.frm' (errno: 121)

Absolutism

Answer 1

Check that all your constraints are really spelled out correctly, also check that there's not any other tables that uses the constraint names FK_userId or FK_blogId

Mulberry

Let me test that theory cause i think im naming FK_userId before but in a different Table – Absolutism

Nice, didn't know that foreign keys need to have unique names through tables. Thanks :) – Sadesadella

Today I got this error for the first time using mySQL for years, now. It really was a coincidence that the names always matched. – Applied

Answer 2

Error 121 is a foreign key constraint issue. The first thing to check is that your foreign key definitions are ok (all the tables and field names are correct, etc.).

You can try disabling foreign key checks before creating the table as well, like this:

SET FOREIGN_KEY_CHECKS = 0;

That has the downside of throwing errors later when you re-enable your key checks (set it to 1), however, if this is the case, then it means you have some invalid records somewhere that are interfering with the creation of the foreign key.

However, this issue can also occur if you've been manually moving database files around, such as physically renaming the data/your_database_name directory. InnoDB can't correlate physical changes like that to the tablespace, so it mucks with the internals.

If this is what you did, the solution that works best is to move your old database back to where it was, take a dump or export of it, and do a DROP DATABASE on it before re-importing.

Grouse

No im not physically moving thisgs around.. I just made an install file and re run that with a fresh copy to create all the tables – Absolutism

Answer 3

please check that your foreign key which you are creating is same in all aspects such as datatype with the referred table column . Each foreign key name should be unique for those tables in which it is created , it should not be used in anyother tables. for the above problem the Foreign key name "FK_userId" should not be used in any other table .

Chole

Answer 4

In I've this problem in mysql 5.5 but works fine in mysql 5.6. The problem was because the constraint name looks to be unique but if this is a long name and is truncated the become non unique, for example :

long_constraint_name_1, long_constraint_name_2 may become long_constraint_name_

Odont