MySQL Bugs: #66821: Include referenced table name for Foreign Key in mysqldump XML output

Bug #66821	Include referenced table name for Foreign Key in mysqldump XML output
Submitted:	14 Sep 2012 14:44	Modified:	3 Feb 2013 17:43
Reporter:	Danny Kopping	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: mysqldump Command-line Client	Severity:	S4 (Feature request)
Version:	5.5.27	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	fk, mysqldump, XML

Description:
When mysqldump is run without the -X flag to produce XML output, it produces something like this:

CREATE TABLE `UserInformation` (
  `id` int(20) NOT NULL AUTO_INCREMENT,
   ...
  `userId` int(20) unsigned NOT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_UserInformation_User1` (`userId`),
  CONSTRAINT `fk_UserInformation_User1` FOREIGN KEY (`userId`) REFERENCES `User` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=27 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

Notice the "REFERENCES `User`" statement in the output.

However, when the -X flag is provided, the referenced table in the Foreign Key is not included in the output:
<key Table="UserInformation" Non_unique="1" Key_name="fk_UserInformation_User1" Seq_in_index="1" Column_name="userId" Collation="A" Cardinality="2" Null="" Index_type="BTREE" Comment="" />

How to repeat:
Run mysqldump -X -d -u root dbName --tables UserInformation and view output

I'd call this a bug actually (incomplete information returned), not just a feature request:

mysql> create table t1(id int primary key, c1 int);
Query OK, 0 rows affected (0.31 sec)

mysql> create table t2(id int primary key, c1 int, foreign key(c1) references t1
(id));
Query OK, 0 rows affected (0.67 sec)

mysql> show create table t2\G
*************************** 1. row ***************************
       Table: t2
Create Table: CREATE TABLE `t2` (
  `id` int(11) NOT NULL,
  `c1` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `c1` (`c1`),
  CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`c1`) REFERENCES `t1` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.06 sec)

mysql> exit
Bye

C:\Program Files\MySQL\MySQL Server 5.5\bin>mysqldump -X -d -uroot -proot -P3312
 test --tables t2
<?xml version="1.0"?>
<mysqldump xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<database name="test">
        <table_structure name="t2">
                <field Field="id" Type="int(11)" Null="NO" Key="PRI" Extra="" Co
mment="" />
                <field Field="c1" Type="int(11)" Null="YES" Key="MUL" Extra="" C
omment="" />
                <key Table="t2" Non_unique="0" Key_name="PRIMARY" Seq_in_index="
1" Column_name="id" Collation="A" Cardinality="0" Null="" Index_type="BTREE" Com
ment="" Index_comment="" />
                <key Table="t2" Non_unique="1" Key_name="c1" Seq_in_index="1" Co
lumn_name="c1" Collation="A" Cardinality="0" Null="YES" Index_type="BTREE" Comme
nt="" Index_comment="" />
                <options Name="t2" Engine="InnoDB" Version="10" Row_format="Comp
act" Rows="0" Avg_row_length="0" Data_length="16384" Max_data_length="0" Index_l
ength="16384" Data_free="9437184" Create_time="2012-09-17 06:57:50" Collation="u
tf8_general_ci" Create_options="" Comment="" />
        </table_structure>
</database>
</mysqldump>

Foreign key related information is obviously missing.

The reason it's missing is that the XML representation comes from the output of "SHOW KEYS FROM 'table'", which is:

mysql> show keys from UserInformation\G
*************************** 1. row ***************************
        Table: userinformation
   Non_unique: 0
     Key_name: PRIMARY
 Seq_in_index: 1
  Column_name: id
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
*************************** 2. row ***************************
        Table: userinformation
   Non_unique: 1
     Key_name: fk_UserInformation_User1
 Seq_in_index: 1
  Column_name: userId
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
2 rows in set (0.00 sec)

However, since the required information is available from information_schema, this is a valid feature request.

You need to explicitly ask for the INFORMATION_SCHEMA database in this case:
mysqldump --xml --skip-lock-tables --databases mydatabase INFORMATION_SCHEMA

Note: I've used this for introspection, but I haven't tried to rebuild a schema with it. I don't know if it's possible to integrate this data into a rebuild out of the box, and at any rate would certainly require some work if you were rebuilding a schema on a server with multiple databases.

from mysqldump page at http://dev.mysql.com/doc/refman/5.5/en/mysqldump.html

"mysqldump does not dump the INFORMATION_SCHEMA database by default. mysqldump dumps INFORMATION_SCHEMA only if you name it explicitly on the command line, although currently you must also use the --skip-lock-tables option. Before MySQL 5.5 mysqldump silently ignores INFORMATION_SCHEMA even if you name it explicitly on the command line."