Error code 1005 (errno: 121) upon create table while restoring MySQL database from a dump

Jonathan asked:

I have a linux prod machine and a Win7 64bit dev machine. My workflow includes dumping the production MySQL database on the linux machine and restoring it in my local MySQL database on the windows machine (using SQLyog). This worked fine for a long time.

Following some trouble, I formatted and reinstalled my windows dev machine. Since then I’m unable to restore the db on it. I keep receiving the following error:

Query:
CREATE TABLE `auth_group` (
  `id` int(11) NOT NULL auto_increment,
  `name` varchar(80) collate utf8_unicode_ci NOT NULL,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

Error occured at:2010-06-26 17:16:14
Line no.:30
Error Code: 1005 - Can't create table 'ap_site.auth_group' (errno: 121)

Notice that this is the first create table statement in the sql dump file.

This error occurs both on MySQL Community Server 5.1.41 and 5.1.48 and with SQLyog Community 8.0.4 and 8.5.1. I really don’t know what’s different in my configuration from before the reinstall and now and why does it have this effect.

Restoring from sql dump is something I need to keep on doing, so I need a permanent fix and not a tailored workaround.

My answer:


The error you received means:

MySQL error code 121: Duplicate key on write or update

Your key/constraint names must be unique across the entire database.

To resolve the issue, choose a different name for the key which has the same name as an existing key, or rename the existing key.


View the full question and answer on Server Fault.

Creative Commons License
This work is licensed under a Creative Commons Attribution-ShareAlike 3.0 Unported License.