Bug #39495 Incorrect result of "show create table" with constraints
Submitted: 17 Sep 2008 11:07 Modified: 29 Sep 2008 8:43
Reporter: Olag Ulga Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: DDL Severity:S3 (Non-critical)
Version:5.0.45, 4.1, 5.0, 5.1, 6.0 bzr OS:Any
Assigned to: CPU Architecture:Any
Tags: constraint, SHOW CREATE TABLE
Triage: Triaged: D2 (Serious)

[17 Sep 2008 11:07] Olag Ulga
Description:
The "show create table" statement does not return an equivalent create-statement with constraints.

The original create statement contains:
CONSTRAINT `CS_FK_childs_ParentId` FOREIGN KEY `IX_FK_childs_ParentId` 

what means, that the constraint is based on the index IX_FK_childs_ParentId

the returned statement of "show create table childs" contains:

CONSTRAINT `CS_FK_childs_ParentId` FOREIGN KEY (`ParentId`) REFERENCES `parents` (`ParentId`) ON DELETE NO ACTION ON UPDATE NO ACTION

that means that a constraint is created for the column ParentId what is correct, but it does not define the index to be used

in the case described in how to repeat this is a problem cause there are two indexes on the childs.ParentId column:

 KEY `IX_FK_childs_ParentId` (`ParentId`),
 KEY `IX ParentId` (`ParentId`),

How to repeat:
CREATE DATABASE `test` /*!40100 DEFAULT CHARACTER SET latin1 COLLATE latin1_general_ci */;

CREATE TABLE `parents` (
  `ParentId` int(10) unsigned NOT NULL auto_increment,
  `ParentText` varchar(45) collate latin1_general_ci NOT NULL,
  PRIMARY KEY  (`ParentId`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;

CREATE TABLE `childs` (
  `ChildId` int(10) unsigned NOT NULL auto_increment,
  `ParentId` int(10) unsigned NOT NULL,
  `ChildText` varchar(45) collate latin1_general_ci NOT NULL,
  PRIMARY KEY  USING BTREE (`ChildId`),
  KEY `IX_FK_childs_ParentId` (`ParentId`),
  KEY `IX ParentId` (`ParentId`),
  CONSTRAINT `CS_FK_childs_ParentId` FOREIGN KEY `IX_FK_childs_ParentId` (`ParentId`) REFERENCES `parents` (`ParentId`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;

show create table childs; /*returns the incorrect statement*/

Suggested fix:
show create table has to analyse which index is used for the constraint and should return an equivalent statement.
[17 Sep 2008 21:37] Sveta Smirnova
Thank you for the report.

Verified as described: output of SHOW CREATE does not show custom name of a FOREIGN KEY
[29 Sep 2008 8:43] Olag Ulga
There are many backup tools which use the "show create table" statement to save the table layout. All these backups are not able to restore the db's original state!

In my opinion the severity should be raised to S2.