Bug #16614 SHOW CREATE TABLE does not show constraints for InnoDB tables

• Submitted: 18 Jan 2006 19:15
• Modified: 7 Jul 2006 0:32
• Reporter: Mark Matthews
• Status: Closed
• Category: MySQL Server: Information schema
• Severity: S2 (Serious)
• Version: 5.1.5
• OS: Linux (Linux)
• Assigned to: Alexey Botchkov
• CPU Architecture: Any

[18 Jan 2006 19:15] Mark Matthews

Description:
The output of "SHOW CREATE TABLE" does not show constraints added to InnoDB tables.

mysql> create table `app tab` ( C1 int(11) NULL, INDEX NEWINX (C1), INDEX NEWINX2 (C1)) ENGINE = InnoDB;
Query OK, 0 rows affected (0.01 sec)

mysql> ALTER TABLE `app tab` ADD CONSTRAINT APPFK FOREIGN KEY (C1) REFERENCES `app tab` (C1);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table `app tab`;
+---------+--------------------------------------------------------------------------------------------------------------------------------------------+
| Table   | Create Table                                                                                                                               |
+---------+--------------------------------------------------------------------------------------------------------------------------------------------+
| app tab | CREATE TABLE `app tab` (
  `C1` int(11) default NULL,
  KEY `NEWINX` (`C1`),
  KEY `NEWINX2` (`C1`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+---------+--------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

mysql> 

This probably breaks "mysqldump" as well, since I tested it, and the foreign key constraints just aren't in the dump at all.

How to repeat:
mysql> create table `app tab` ( C1 int(11) NULL, INDEX NEWINX (C1), INDEX NEWINX2 (C1)) ENGINE = InnoDB;
Query OK, 0 rows affected (0.01 sec)

mysql> ALTER TABLE `app tab` ADD CONSTRAINT APPFK FOREIGN KEY (C1) REFERENCES `app tab` (C1);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table `app tab`;
+---------+--------------------------------------------------------------------------------------------------------------------------------------------+
| Table   | Create Table                                                                                                                               |
+---------+--------------------------------------------------------------------------------------------------------------------------------------------+
| app tab | CREATE TABLE `app tab` (
  `C1` int(11) default NULL,
  KEY `NEWINX` (`C1`),
  KEY `NEWINX2` (`C1`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+---------+--------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

mysql>

[18 Jan 2006 20:30] MySQL Verification Team

mysql> show create table `app tab`\G
*************************** 1. row ***************************
       Table: app tab
Create Table: CREATE TABLE `app tab` (
  `C1` int(11) default NULL,
  KEY `NEWINX` (`C1`),
  KEY `NEWINX2` (`C1`),
  CONSTRAINT `APPFK` FOREIGN KEY (`C1`) REFERENCES `app tab` (`C1`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> select version();
+--------------+
| version()    |
+--------------+
| 5.0.19-debug |
+--------------+
1 row in set (0.00 sec)

mysql> show create table `app tab`\G
*************************** 1. row ***************************
       Table: app tab
Create Table: CREATE TABLE `app tab` (
  `C1` int(11) default NULL,
  KEY `NEWINX` (`C1`),
  KEY `NEWINX2` (`C1`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.01 sec)

mysql> select version();
+-------------------+
| version()         |
+-------------------+
| 5.1.6-alpha-debug |
+-------------------+
1 row in set (0.01 sec)

[2 Jun 2006 12:48] Bugs System

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/7206

[2 Jun 2006 13:12] Alexander Barkov

Test results display encoded table name in REFERENCES:

+app tab	CREATE TABLE `app tab` (
+  `C1` int(11) DEFAULT NULL,
+  KEY `NEWINX` (`C1`),
+  KEY `NEWINX2` (`C1`),
+  CONSTRAINT `APPFK` FOREIGN KEY (`C1`) REFERENCES `app@0020tab` (`C1`)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1
+drop table `app tab`;

It should be `app tab` instead of `app@0020tab`

Setting back to "in progress".

[7 Jul 2006 0:32] Paul DuBois

Noted in 5.1.12 changelog.

SHOW CREATE TABLE now shows constraints for InnoDB tables.