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:
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

[15 Dec 2006 17:12] Eric Brunson
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
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.
[15 Dec 2006 18:18] Valeriy Kravchuk
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.
[15 Dec 2006 20:32] Eric Brunson
Sorry, didn't find the previous bug in a search.