MySQL Bugs: #64601: Error when creating foreign keys

Bug #64601	Error when creating foreign keys
Submitted:	9 Mar 2012 16:24	Modified:	13 Sep 2012 19:41
Reporter:	Aur?lien Vairet	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Workbench: SQL Editor	Severity:	S1 (Critical)
Version:	5.2.38	OS:	Any (Microsoft Windows XP Professional Service Pack 3 (build 2600), Mac OS X)
Assigned to:		CPU Architecture:	Any
Tags:	WBBugReporter

Description:
When I go to "Foreign keys" tab to add a foreign key on a InnoDB table that references field on another InnoDB table, if I click apply the Sql Editor display the correct SQL statements. But when I click on "Apply" an error occur: Error 1005 Can't create table 'myschema.#sql-b00_1' (errno:121) 

How to repeat:
Create schema "myschema". Create InnoDB table "apartments" with id and name fields. Create InnoDB table "rooms" with id, apartment_id. Click apply to create table. Then go to "Foreign keys" tab to add a foreign key on apartment_id field references "apartments.id". Click apply. Sql Editor display the correct SQL statements. Click on "Apply" and then the error occur: Error 1005 Can't create table 'myschema.#sql-b00_1' (errno:121) [...] table "rooms" already exists

Thank you for the bug report. I've got this eventually:

ERROR 1005: Can't create table 'test.#sql-7267_11' (errno: 150)
SQL Statement:
ALTER TABLE `test`.`r` 
  ADD CONSTRAINT `fk`
  FOREIGN KEY (`a_id` )
  REFERENCES `test`.`a` (`id` , `id` )
  ON DELETE NO ACTION
  ON UPDATE NO ACTION
, DROP PRIMARY KEY 
, ADD PRIMARY KEY (`a_id`)

ERROR: Error when running failback script. Details follow.

ERROR 1050: Table 'r' already exists
SQL Statement:
CREATE TABLE `r` (
  `id` int(11) NOT NULL,
  `a_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk` (`a_id`),
  CONSTRAINT `fk` FOREIGN KEY (`a_id`) REFERENCES `a` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=latin1

with 5.2.38 on Mac OS X, while tried to add FK for a second time when I saw that no columns are selected for FK in GUI. 

So, looks like we have a bug in fallback script generated at least. No need to CREATE TABLE after whatever wrong ALTER that ended with error.

This error appears beacause in the generated script, mysql workbench uses the same name for the constraint and for the index related to this foreign key. The solution is to rename manualy in the script the connstraint or the index before submit.

This has been fixed. This bug report was documented with MySQL Bug #66285 (Oracle Bug #14486006).