XML dump of MySQL database don't include foreign key constraints. What can be the reason? sqldump(.sql) file list it

Question

I am accessing schema information of databases in XML format. I am taking dump of the database in XML format using the command below.

mysqldump --no-data --xml -u root -p bakerydb > bakerydb.xml

these dump don't list the foreign key constraints. Below part of the output is listed.

   <table_structure name="class_details">
        <field Field="id" Type="int(10)" Null="NO" Key="PRI" Extra="auto_increment" Comment="" />
        <field Field="course_id" Type="int(10)" Null="NO" Key="MUL" Extra="" Comment="" />
        <field Field="start_date" Type="date" Null="NO" Key="" Extra="" Comment="" />
        <field Field="end_date" Type="date" Null="NO" Key="" Extra="" Comment="" />
        <field Field="tutor_id" Type="int(10)" Null="NO" Key="MUL" Extra="" Comment="" />
        <key Table="class_details" Non_unique="0" Key_name="PRIMARY" Seq_in_index="1" Column_name="id" Collation="A" Cardinality="0" Null="" Index_type="BTREE" Comment="" Index_comment="" />
        <key Table="class_details" Non_unique="1" Key_name="fk_cources_offered_class_details" Seq_in_index="1" Column_name="course_id" Collation="A" Cardinality="0" Null="" Index_type="BTREE" Comment="" Index_comment="" />
        <key Table="class_details" Non_unique="1" Key_name="fk_employee_details_class_details" Seq_in_index="1" Column_name="tutor_id" Collation="A" Cardinality="0" Null="" Index_type="BTREE" Comment="" Index_comment="" />
        <options Name="class_details" Engine="InnoDB" Version="10" Row_format="Compact" Rows="0" Avg_row_length="0" Data_length="16384" Max_data_length="0" Index_length="32768" Data_free="7340032" Auto_increment="1" Create_time="2014-02-25 07:08:56" Collation="utf8_general_ci" Create_options="" Comment="" />
    </table_structure>

Same database lists the detailed foreign key constraint in sqldump. (.sql file). below that's mentioned.

DROP TABLE IF EXISTS `class_details`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `class_details` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `course_id` int(10) NOT NULL,
  `start_date` date NOT NULL,
  `end_date` date NOT NULL,
  `tutor_id` int(10) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_cources_offered_class_details` (`course_id`),
  KEY `fk_employee_details_class_details` (`tutor_id`),
  CONSTRAINT `fk_employee_details_class_details` FOREIGN KEY (`tutor_id`) REFERENCES `employee_details` (`emp_id`),
  CONSTRAINT `fk_courses_offered_class_details` FOREIGN KEY (`course_id`) REFERENCES `courses_offered` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Anyone know why they don't list parent table information? and the way, How we can get the details of foreign key constraints?

P.S. : storage engine is InnoDB.

Answer 1

This is a bug in mysqldump that Oracle has characterized as a "feature request."

http://bugs.mysql.com/bug.php?id=66821

In normal mode (not XML), mysqldump just uses the server's output from SHOW CREATE TABLE tablename.

if (!opt_xml ...
    ...
    my_snprintf(buff, sizeof(buff), "show create table %s", result_table);

When running in --xml mode, mysqldump uses the output from SHOW KEYS FROM 'tablename', which doesn't include the foreign key constraints.

my_snprintf(buff, sizeof(buff), "show keys from %s", result_table);

Since the two different operating modes of mysqldump fetch the data from the server in such different ways, so the addition of this information to an XML dump is not exactly a trivial change.

There is not a simple workaround, although as noted in the bug report, the information is available in information_schema and you can access this from mysqldump if you supply the --skip-lock-tables option (locking information_schema isn't allowed). You'll have to take the data you retrieve, parse and extract the relevant bits, and reformat it as needed before it would be all that useful.