Bug #33937 mysqldump --routines --add-drop-database can lose stored routines on reload
Submitted: 19 Jan 2008 1:57 Modified: 6 Apr 2008 10:08
Reporter: Daniel Grace Email Updates:
Status: No Feedback Impact on me:
None 
Category:MySQL Server: mysqldump Command-line Client Severity:S2 (Serious)
Version: 5.0.45-5-log OS:Linux (Debian lenny/sid, using debian packages)
Assigned to: CPU Architecture:Any

[19 Jan 2008 1:57] Daniel Grace
Description:
When running the following:

mysqldump  --add-locks --all-databases --comments --disable-keys --extended-insert --flush-privileges --lock-all-tables --master-data --quick --routines --triggers --tz-utc 

No CREATE PROCEDURE statements are dumped despite the presence of --routines.  This is presumably correct behavior, since they are already defined in mysql.proc, which is included in the resultant dump, and defining them again would cause errors on reload from already being defined.

However, if --add-drop-database is specified in addition to the above options, any databases dumped after the 'mysql' database will lose their stored routines, as the DROP DATABASE will drop that database's procedures that were defined previously by mysql.proc being recreated.  This may only happen if the database did in fact previously exist, I did not test that far.

Since mysqldump dumps databases in alphabetical order, this does not occur with database names that lexically sort before "mysql", but will with databases that sort after it.  Database "blah" will keep its routines, whereas "test" will lose  its routines.

How to repeat:
see description

Suggested fix:
Dump the mysql database at the very end of the dump, instead of where it happens to fall in the list of databases.  This ensures that all of the routine data is restored.

As an alternative, dump routines guarded with DROP PROCEDURE ... IF EXISTS.  However, this causes the same issues with preserving timestamps that are noted at http://dev.mysql.com/doc/refman/5.0/en/mysqldump.html#option_mysqldump_routines for any routines dumped after the mysql database is dumped.
[26 Jan 2008 12:28] Sveta Smirnova
Thank you for the report.

I can not repeat described behavior with test data. Please provide dump of database I can repeat problem with.
[27 Feb 2008 0:00] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".
[6 Mar 2008 10:08] Susanne Ebrecht
We still need the dump.

Also it would be nice, when you try to test with newer MySQL version (5.0.51a) and let us know if the bahaviour still occurs.
[6 Apr 2008 23:00] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".