Bug #9056 mysqldump ignores stored routines in database
Submitted: 8 Mar 2005 20:20 Modified: 19 Oct 2005 18:59
Reporter: Paul Dubois Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Replication Severity:S3 (Non-critical)
Version:5.0-bk OS:
Assigned to: Bugs System CPU Architecture:Any

[8 Mar 2005 20:20] Paul Dubois
Description:
A stored routine is associated with a database, like a table
or a view, but mysqldump db_name dumps only tables and
views and ignores stored routines.

Secondary problem: Suppose mysqldump is changed to dump
stored routines for mysqldump db_name. There will still be a
problem in specifying individual routines to dump. mysqldump
supports a --tables option to indicate that following arguments
are table names, but there is no corresponding argument for
routines.

How to repeat:
Create a stored routine in a database.

Run mysqldump db_name.

No routine in the output.
[24 Apr 2005 21:07] Guilhem Bichot
In discussion with Paul (Paul, please see my email to you).
[13 May 2005 20:34] Guilhem Bichot
Discussed with Monty:
I'll add a mysqldump option --routines-of-db
If 0, does not dump routines when dumping a database, dumps them only if you dump mysql.proc.
If 1, dumps routines belonging to the database when dumping a database, and skips dumping of mysql.proc (to avoid double dump which would lead to "duplicate key" when restoring from the output of "mysqldump --databases").
The default would be *0*, to not get too far from the implementation constraints which are that in fact routines are in mysql.proc, and to not change from the current behaviour.
[13 May 2005 20:35] Guilhem Bichot
Note that the "fix" will apply for stored procs and functions, but not for triggers for which there is BUG#10431.
[16 May 2005 21:09] Guilhem Bichot
When --routines-of-db==1, the routines should be dumped in the format of mysql.proc:
INSERT INTO mysql.proc etc etc with proper escaping of strings. *Maybe* this can be done by just somehow considering that after dumping each db, we add "mysql.proc" with a "--where=db=DBNAME" and then we can re-use some code of mysqldump.
[16 May 2005 22:39] Guilhem Bichot
After discussion with Serg. SELECTing/INSERTing mysql.proc is something not allowed to bare users generally.  But we want a bare user to be able to dump/reload his own routines. For this:
1) reading of the routines should be done not with "SELECT * FROM mysql.proc WHERE" but with SHOW CREATE PROCEDURE|FUNCTION and SHOW PROCEDURE|FUNCTION STATUS. Combining the output of the two we get the same info (same columns) as "SELECT * FROM mysql.proc". Currently there is a bug (which could be filed) that SHOW PROCEDURE|FUNCTION STATUS requires SELECT priv on mysql.proc, it should rather open mysql.proc and return at least the routines created by the current user.
2) the output format should, if this is a bare user (i.e. who does not have INSERT grant on mysql.proc), be CREATE PROCEDURE|FUNCTION and not "INSERT INTO mysql.proc". Problems with this approach CREATE PROCEDURE|FUNCTION does not re-create the routine as it was originally: the dates in mysql.proc are different; to get the same dates mysqldump should add some SET TIMESTAMP before the CREATE PROCEDURE|FUNCTION, it should also add SET SQL_MODE (to have the routine created with the same SQL_MODE). Last info is the definer: if the reloader is equal to the definer of the dumped routines, then definer will be set ok. If it's different (e.g. the dump was done by bare user and restore is done by DBA) then definer will be set wrong and DBA will have to fix it with UPDATE mysql.proc SET definer=...
To limit this, we decide that:
- a dump made with --routines-of-db=0 (the default) will not dump the routines, or will only if mysql.proc is in the list of tables to dump (no change to the code here)
- if the dump is made with --routines-of-db=1, it will be restorable easily only if the user 
So we say that a dump made by a bare user cannot be restored by a DBA.
And if the DBA wants to dump one db, he'll naturally use --routines-of-db=1 and will get problems at restore. And Serg finally added:
<serg> dump tables, dump procedures as CREATE PROCEDURE where definer=current_user, dump from mysql.proc as INSERT where definer<>current_user (but db=current_db) <serg> that'll work for everybody, assuming current_user of dump is the same as current_user of restore <serg> but the latter problem we can solve later, I think.
Another way would be to modify the privilege checking of SELECT * FROM mysql.proc and INSERT INTO mysql.proc... Conclusion: nothing decided yet, all solutions have problems.
[27 Aug 2005 18:56] Patrick Galbraith
I will work with Guilhem and Serg to find out where I should pick up from where this was left off.
[29 Aug 2005 21:29] Patrick Galbraith
I have emailed both Guilhem and Serg as what to do with this. As soon as I have word back from them, this will become my top task to handle.
[1 Sep 2005 2:03] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/29146
[2 Sep 2005 18:24] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/29248
[3 Sep 2005 23:35] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/29283
[8 Sep 2005 17:46] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/29520
[9 Sep 2005 23:23] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/29591
[9 Sep 2005 23:29] Patrick Galbraith
pushed patch
[7 Oct 2005 22:29] Patrick Galbraith
Fixed in 5.0.13
[19 Oct 2005 18:59] Paul Dubois
Noted in 5.0.13 changelog, and described in
the mysqldump section.