MySQL Bugs: #9056: mysqldump ignores stored routines in database

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

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.

In discussion with Paul (Paul, please see my email to you).

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.

Note that the "fix" will apply for stored procs and functions, but not for triggers for which there is BUG#10431.

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.

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.

I will work with Guilhem and Serg to find out where I should pick up from where this was left off.

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.

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

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

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

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

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

pushed patch

Fixed in 5.0.13

Noted in 5.0.13 changelog, and described in
the mysqldump section.