Mysql – ERROR 1005 (HY000): Can’t create table ‘phact_greenland.cache’ (errno: 121)

innodbMySQL

I get an error when I try to create a table:

ERROR 1005 (HY000): Can't create table 'phact_greenland.cache' (errno: 121)

CREATE TABLE IF NOT EXISTS `cache` 
(   
    `cid` varchar(255) NOT NULL DEFAULT '' 
        COMMENT 'Primary Key: Unique cache ID.'
    , `data` longblob 
        COMMENT 'A collection of data to cache.' 
    , `expire` int(11) NOT NULL DEFAULT '0' 
        COMMENT 'A Unix timestamp indicating when the cache entry 
                should expire, or 0 for never.'  
    , `created` int(11) NOT NULL DEFAULT '0' 
        COMMENT 'A Unix timestamp indicating when the cache entry was created.'  
    , `serialized` smallint(6) NOT NULL DEFAULT '0' 
        COMMENT 'A flag to indicate whether content is serialized (1) or not (0).' 
    , PRIMARY KEY (`cid`) 
    , KEY `expire` (`expire`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 
    COMMENT='Generic cache table for caching things not separated out...';

How do i solve it ?

Best Answer

You get Error 121 when you try to create a table that contains a constraint with the same name as a constraint defined elsewhere in your database. You can avoid this issue by naming the constraints yourself in the creation script. More information can be found below:

InnoDb Error Codes

http://dev.mysql.com/doc/refman/5.5/en/innodb-error-codes.html

Create Table Statement

http://dev.mysql.com/doc/refman/5.5/en/create-table.html

I hope this helps you.

Related Solutions

Mysql – Unexplained InnoDB timeouts

I know this is really late, but you really need to capture the output of SHOW ENGINE INNODB STATUS; during that query to see why it's waiting.

If it happens a lot during a specific time, it would be easy to just grab that output every x seconds and hope you capture it (or perhaps artificially generate the load).

Mysql – ERROR: Error 1005: Can’t create table ‘progetto.museo’ (errno: 121)

There is no need to name your constraints, but if you do, then that is the name that must be unique within each schema. The error is telling you that You already have constraints with the same name elsewhere.

To see where, you can SHOW ENGINE INNODB STATUS;

------------------------
LATEST FOREIGN KEY ERROR
------------------------
130129 19:45:00 Error in foreign key constraint creation for table `test`.`baz`.
A foreign key constraint of name `test`.`foo_id`
already exists.

But in this case, we don't need to look that up, since it's apparent from the script that you're reusing the same constraint names in multple table definitions.

If you remove lines like this...

CONSTRAINT `Comune`

... but leave the rest of your table definition intact...

  FOREIGN KEY (`Comune` )
  REFERENCES `PROGETTO`.`PAESE` (`Comune` )
  ON DELETE NO ACTION
  ON UPDATE CASCADE)

... you should not have a problem. You will find that InnoDB will generate names for your constraints, like PAESE_ibfk_1, PAESE_ibfk_2, etc.

Or, you can continue to declare the names of your constraints, remembering that whatever comes after the keyword CONSTRAINT has to be unique within each schema.

"If the CONSTRAINT symbol clause is given, the symbol value must be unique in the database. If the clause is not given, InnoDB creates the name automatically."

http://dev.mysql.com/doc/refman/5.5/en/innodb-foreign-key-constraints.html

Best Answer

Related Solutions

Mysql – Unexplained InnoDB timeouts

Mysql – ERROR: Error 1005: Can’t create table ‘progetto.museo’ (errno: 121)

Related Question