Bug #64651 mysqldump xml fails to quote string literals
Submitted: 14 Mar 2012 19:01 Modified: 14 Mar 2012 19:14
Reporter: Jeffrey Paul Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: mysqldump Command-line Client Severity:S2 (Serious)
Version:5.1.61, 5.5.21 OS:Any
Assigned to: CPU Architecture:Any
Tags: mysqldump, XML

[14 Mar 2012 19:01] Jeffrey Paul
Description:
The "Default" attribute of a <field> tag in the <table_structure> of an XML-format mysqldump does not correctly quote string literals within the attribute value.

Example:

<table_structure name="tableName">
    [snip]
    <field Field="col1" Type="timestamp" Null="NO" Key="" Default="CURRENT_TIMESTAMP" Extra="on update CURRENT_TIMESTAMP" Comment="" />
    <field Field="col2" Type="timestamp" Null="NO" Key="" Default="0000-00-00 00:00:00" Extra="" Comment="" />
    [snip]
</table_structure>

In a traditional mysqldump, these columns appear like this:

CREATE TABLE `tableName` (
  [snip]
  `col1` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  `col2` timestamp NOT NULL default '0000-00-00 00:00:00',
  [snip]

It seems that in the xml-format mysqldump, the <field> should appear as follows (note the entity &apos; for a single quote on each side of the default value inside of the double quotes of the attribute value:

    <field Field="field2" Type="timestamp" Null="NO" Key="" Default="&apos;0000-00-00 00:00:00&apos;" Extra="" Comment="" />

This prevents a user who has relied on an xml-format mysqldump from being able to accurately reconstruct their database without human review of the data (or ugly edge-case handling to detect strings versus function names)!

How to repeat:
Create a table with a timestamp column with a default of CURRENT_TIMESTAMP, and another timestamp column with a default of '0000-00-00 00:00:00'.

Perform an xml-format mysqldump (mysqldump --xml).

There is no way to discern from the resultant xml dumpfile whether or not the default value (in the attribute) should be single-quoted (e.g. in the case of 0000-00-00 00:00:00) or not (e.g. in the case of CURRENT_TIMESTAMP).  This poses problems for any utility attempting to re-create tables based on the contents of an xml-format mysqldump.

Suggested fix:
Place entity-escaped single quotes inside the "Default" attribute value within the <field> tags when they are specifying string literals, such as '0000-00-00 00:00:00'.

<field ... Default="0000-00-00 00:00:00"... />

becomes

<field ... Default="&apos;0000-00-00 00:00:00&apos;"... />
[14 Mar 2012 19:14] Valeriy Kravchuk
Verified with 5.1.61 also:

macbook-pro:5.1 openxs$ bin/mysqldump --xml -uroot test tableName
<?xml version="1.0"?>
<mysqldump xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<database name="test">
	<table_structure name="tableName">
		<field Field="col1" Type="timestamp" Null="NO" Key="" Default="CURRENT_TIMESTAMP" Extra="on update CURRENT_TIMESTAMP" Comment="" />
		<field Field="col2" Type="timestamp" Null="NO" Key="" Default="0000-00-00 00:00:00" Extra="" Comment="" />
		<options Name="tableName" Engine="MyISAM" Version="10" Row_format="Fixed" Rows="0" Avg_row_length="0" Data_length="0" Max_data_length="2533274790395903" Index_length="1024" Data_free="0" Create_time="2012-03-14 19:12:18" Update_time="2012-03-14 19:12:18" Collation="latin1_swedish_ci" Create_options="" Comment="" />
	</table_structure>
	<table_data name="tableName">
	</table_data>
</database>
</mysqldump>