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

Question

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.

Answer 1

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.

Answer 2

After reading lots of threads about this error, mainly about a restriction that Foreign Keys should be uniquely named throughout the database, the following simple solution worked:

Drop the db, recreate it, and try again :)