MySQL Bugs: #33937: mysqldump --routines --add-drop-database can lose stored routines on reload

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

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.

Thank you for the report.

I can not repeat described behavior with test data. Please provide dump of database I can repeat problem with.

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".

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.

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".