Bug #52792 mysqldump in XML mode does not dump routines!!
Submitted: 13 Apr 2010 19:27 Modified: 27 Jan 2012 14:42
Reporter: Roland Bouman Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: mysqldump Command-line Client Severity:S2 (Serious)
Version:5.1, 5.6.99 OS:Any
Assigned to: Nirbhay Choubey CPU Architecture:Any

[13 Apr 2010 19:27] Roland Bouman
Description:
mysqldump with -X and -R does not dump routines! 

this is a serious problem - i could be under the impression I am backing up my routines whereas I am not.

How to repeat:
mysqldump -uroot -pmysql -P3351 -X --no-data --hex-blob --create-options  -R --databases sakila > sakila-xml.txt

look in the output file for rewards_report. It is not there.
Compare it to this dump:

mysqldump -uroot -pmysql -P3351 --no-data --hex-blob --create-options  -R --databases sakila > sakila-sql.txt

Suggested fix:
add support for stored routines. while you're at it, check if triggers and events don't suffer from the same problem.
[13 Apr 2010 20:29] Sveta Smirnova
Thank you for the report.

Verified as described. Triggers and events are also affected.
[14 Apr 2010 16:40] Roland Bouman
Thanks Sveta for verifying. I found a similar issue with VIEWs - you can use the same sakila command line to reproduce. For now I consider the issue related to this bug. Please let me know if this needs a separate bug report and I'll add it.
[14 Apr 2010 16:41] Roland Bouman
Foreign keys are also not dumped in XML output. Please let me know if I should create a separate issue for that.
[14 Apr 2010 18:34] Sveta Smirnova
Roland, thank you for the feedback.

I think separate bug for new limitations is good to have.

Btw I can not confirm bug with view:

<table_structure name="v1">
                <field Field="1" Type="int(1)" Null="NO" Key="" Default="0" Extra="" />
                <options Name="v1" Comment="VIEW" />
</table_structure>

Regarding foreign keys problem confirmed. There is only key information and no info this is foreign key in the output:

...
                <key Table="i2" Non_unique="1" Key_name="f2" Seq_in_index="1" Column_name="f2" Collation="A" Cardinality="0" Null="YES" Index_type="BTREE" Comment="" Index_comment="" />

For create table i2(f1 int not null primary key, f2 int, foreign key(f2) references i1(f1)) engine=innodb;
[14 Apr 2010 21:11] Roland Bouman
Hi sveta, 

in the example you quote regarding the views, where is the view code? surely I can't recreate the view based on only the list of columns?
[14 Apr 2010 21:20] Sveta Smirnova
Roland,

thank you for the correction. Problem with VIEWs exists as well.
[27 Jan 2012 14:42] Jon Stephens
Thank you for your bug report. This issue has been committed to our source repository of that product and will be incorporated into the next release.

If necessary, you can access the source repository and build the latest available version, including the bug fix. More information about accessing the source trees is available at

    http://dev.mysql.com/doc/en/installing-source.html
[27 Jan 2012 14:44] Jon Stephens
Fixed in 5.1+. Documented in the 5.1.62, 5.5.21, and 5.6.5 changelogs as follows:

      When used with the --xml option, mysqldump --routines failed to dump any
      stored routines, triggers, or events.

Also noted issue and fix in 5.1/5.5/5.6 Manual's description of mysqldump options.

Closed.