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

Bug #9056	mysqldump ignores stored routines in database
Submitted:	8 Mar 2005 21:20	Modified:	19 Oct 2005 20:59
Reporter:	Paul DuBois
Status:	Closed
Category:	Server: Replication	Severity:	S3 (Non-critical)
Version:	5.0-bk	OS:
Assigned to:	Bugs System	Target Version:

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.