MySQL Bugs: #32521: MySQL fails to create table where keynames matches to the constraint names.

Bug #32521	MySQL fails to create table where keynames matches to the constraint names.
Submitted:	20 Nov 2007 13:51	Modified:	25 Sep 2008 9:52
Reporter:	Anton Myshkovsky	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server: DDL	Severity:	S3 (Non-critical)
Version:	5.0.46	OS:	Windows
Assigned to:		CPU Architecture:	Any

Description:
The following DDL statement is produced by the 'mysqldump' utility:

DROP TABLE IF EXISTS `a`;
CREATE TABLE `a` (
  `f0` int(11) NOT NULL,
  `f1` int(11) default NULL,
  `f2` int(11) default NULL,
  PRIMARY KEY  (`f0`),
  KEY `a_on_b` (`f1`),
  KEY `a_on_c` (`f2`),
  CONSTRAINT `a_on_b` FOREIGN KEY (`F1`) REFERENCES `B` (`F1`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT `a_on_c` FOREIGN KEY (`F2`) REFERENCES `C` (`F1`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE=innodb DEFAULT CHARSET=latin1;

This statement fails with the following error:
"Duplicate key name 'a_on_b'"

How to repeat:
DROP TABLE IF EXISTS `a`;
CREATE TABLE `a` (
  `f0` int(11) NOT NULL,
  `f1` int(11) default NULL,
  `f2` int(11) default NULL,
  PRIMARY KEY  (`f0`),
  KEY `a_on_b` (`f1`),
  KEY `a_on_c` (`f2`),
  CONSTRAINT `a_on_b` FOREIGN KEY (`F1`) REFERENCES `B` (`F1`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT `a_on_c` FOREIGN KEY (`F2`) REFERENCES `C` (`F1`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE=innodb DEFAULT CHARSET=latin1;

The original DDL statement:

create table a(
f0 integer not null,
f1 integer,
f2 integer,
primary key (f0),
constraint a_on_b foreign key (f1) references b(f1),
constraint a_on_c foreign key (f2) references c(f1)) engine=innodb;

The DDL statement generated by mysqldump:

CREATE TABLE `a` (
  `f0` int(11) NOT NULL,
  `f1` int(11) default NULL,
  `f2` int(11) default NULL,
  PRIMARY KEY  (`f0`),
  KEY `a_on_b` (`f1`),
  KEY `a_on_c` (`f2`),
  CONSTRAINT `a_on_b` FOREIGN KEY (`f1`) REFERENCES `b` (`f1`),
  CONSTRAINT `a_on_c` FOREIGN KEY (`f2`) REFERENCES `c` (`f1`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Thank you for the report.

I can not repeat described error in my environment. Please upgrade to current version 5.0.67, try with it and if problem still exists provide repeatable test case.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

probably related to bug #39932