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 17:12	Modified:	15 Dec 2006 20:32
Reporter:	Eric Brunson	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: Backup	Severity:	S3 (Non-critical)
Version:	5.0.22	OS:	FreeBSD (FreeBSD, Linux, Solaris)
Assigned to:		CPU Architecture:	Any
Tags:	mysqldump, routines, Views

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.