MySQL - ERROR 1005 (HY000): Can't create table './MyDB/#sql-e4a_c715.frm' (errno: 121)

The following statement

ALTER TABLE testone.lgn_permission ADD CONSTRAINT fk_lgn_permission_r FOREIGN KEY ( role_fk ) REFERENCES testone.lgn_role( id ) ON DELETE NO ACTION ON UPDATE NO ACTION;

was causing this error:

ERROR 1005 (HY000): Can't create table './MyDB/#sql-e4a_c715.frm' (errno: 121)


Below, it's shown part of the script responsible for the error:


CREATE TABLE testone.lgn_role (
    id                   BIGINT  NOT NULL  AUTO_INCREMENT,
    title                VARCHAR(100)  NOT NULL  ,
    code                 INT UNSIGNED NOT NULL  ,
    profile_fk           BIGINT    ,
    CONSTRAINT pk_lgn_role PRIMARY KEY ( id )
 );

CREATE INDEX idx_lgn_role ON testone.lgn_role ( profile_fk );

CREATE TABLE testone.lgn_permission (
    role_fk              BIGINT  NOT NULL  ,
    login_fk             BIGINT  NOT NULL  ,
    resource_fk          BIGINT  NOT NULL 
    -- CONSTRAINT pk_lgn_permission UNIQUE ( login_fk, role_fk )
 ) ENGINE=InnoDB ;

ALTER TABLE testone.lgn_permission ADD CONSTRAINT fk_lgn_permission_role FOREIGN KEY ( role_fk ) REFERENCES testone.lgn_role( id ) ON DELETE NO ACTION ON UPDATE NO ACTION;

ALTER TABLE testone.lgn_permission ADD CONSTRAINT fk_lgn_permission_login FOREIGN KEY ( login_fk ) REFERENCES testone.lgn_login( id ) ON DELETE NO ACTION ON UPDATE NO ACTION;

Solution

One table was declared as InnoDB, the other not.


CREATE TABLE testone.lgn_role (
...
);


CREATE TABLE testone.lgn_permission (
...
 ) ENGINE=InnoDB ;


Note:

Every time you get such error (1005), check your table declarations.
Something wrong you'll certainly find there.
May be difficult because if the database has a huge script there will be much stuff to be checked.

A good strategy is to create a check list.
For instance:
1. Check all table constraints first.
2. Check key types, if they all have the appropriate type.
etc.

 There are also some useful commands:

1. Checking innoDB status

mysql> Show innoDB status;


On the example above, the result of this command shows that there is a pendant issue.

------------------------
LATEST FOREIGN KEY ERROR
------------------------
130705 18:41:47 Error in foreign key constraint of table testone/#sql-d65_ca:
 FOREIGN KEY ( role_fk ) REFERENCES testone.lgn_role( id ) ON DELETE NO ACTION ON UPDATE NO ACTION:
Cannot resolve table name close to:
( id ) ON DELETE NO ACTION ON UPDATE NO ACTION
------------



2. Checking Schema Informations

Useful to find out the existent constraints, etc.

mysql>USE information_schema;
mysql>desc KEY_COLUMN_USAGE;


For instance:

select * from key_column_usage where constraint_name='fk_lgn_permission_resource';