MYSQL errorno 121

Question

Im getting this error in Mysql Create: Im 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)

Answer 1

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.

Answer 2

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