MySQL Bugs: #25099: Views, user defined routines improperly restored from mysqldump into diff schema

Bug #25099	Views, user defined routines improperly restored from mysqldump into diff schema
Submitted:	15 Dec 2006 18:12	Modified:	15 Dec 2006 21:32
Reporter:	Eric Brunson
Status:	Duplicate
Category:	Server: Backup	Severity:	S3 (Non-critical)
Version:	5.0.22	OS:	FreeBSD (FreeBSD, Linux, Solaris)
Assigned to:		Target Version:
Tags:	Views, routines, mysqldump

Description:
We have a production schema which we would like to duplicate under another schema name.
The schema defines functions that we use in views, so we specify the '--routines' flag
and mysql dumps the functions also. However, in the view definitions the schema name is
prepended to the function name so when imported under a different schema name, the views
still reference the functions in the original schema. 

How to repeat:
In the mysql shell:
create database d;
use d;
create table t ( n int );
delimiter //
CREATE function f( n int ) returns int
BEGIN
return n*n;
END //
delimiter ;
create view v as select n, f(n) from t;
create database i;

From the OS:
mysqldump --routines --password=$password --opt d | mysql --password=$password i

From the mysql shell:
use i;
show create table v;

mysql> *************************** 1. row ***************************
View: v
Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`ebrunson`@`localhost` SQL SECURITY
DEFINER VIEW `v` AS select `t`.`n` AS `n`,`d`.`f`(`t`.`n`) AS `f(n)` from `t`

You see the function still references the original schema.

Suggested fix:
I'm not sure.  It's obvious that you want to be explicit with the function name in the
view definition.

Please do not submit the same bug more than once. An existing bug report, bug #23491,
already describes this very problem. Even if you feel that your issue is somewhat
different, the resolution is likely to be the same. Because of this, we hope you add your
comments to the original bug instead.

Thank you for your interest in MySQL.

Sorry, didn't find the previous bug in a search.