MySQL Bugs: #10713: mysqldump includes database in create view and referenced tables

Bug #10713	mysqldump includes database in create view and referenced tables
Submitted:	18 May 2005 15:16	Modified:	24 Oct 2005 10:01
Reporter:	Anders Karlsson	Email Updates:
Status:	Won't fix	Impact on me:	None
Category:	MySQL Server: mysqldump Command-line Client	Severity:	S3 (Non-critical)
Version:	5.0	OS:
Assigned to:	Magnus Blåudd	CPU Architecture:	Any

Description:
Mysqldump will genenerate a CREATE VIEW statement that includes the original database name for the view, as well as for the referenced tables / views. This is the same output as "SHOW CREATE VIEW", so I guess that is where the real trouble lies. Anyway, this makes using mysqldump as a tool for moving data and schemas from one db to another rather difficult.

How to repeat:
mysql test
...
mysql> create table a(c1 int);
Query OK, 0 rows affected (0.07 sec)

mysql> create view b as select * from a;
Query OK, 0 rows affected (0.00 sec)

mysql> exit
Bye
$ mysqldump test
...
[snip]
--
-- View structure for view `b`
--

DROP VIEW IF EXISTS `b`;
CREATE ALGORITHM=UNDEFINED VIEW `test`.`b` AS select `test`.`a`.`c1` AS `c1` from `test`.`a`;
[snip]
...
As can be seen, the VIEW is explicitly created to exist in database test, referencing table a in database test.

Suggested fix:
Remove the database name in the VIEW name, and the output of SHOW CREATE TABLE when a VIEW is referenced. As a view may reference tables outside the current database, removing the referenced table name name is possibly not so obvious, but I suggest it be removed from the output if the referenced table exists in the same database as the one which is currently exported. This should cover many more cases than the current implementation.

Thank you for the bug report.

Sent my patch to Patrik and Andrey. Keeping it open until sorted out.

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/29159

Note, the patch refers to bug#1213 by mistake. Should be this bug.

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/29721

Pushed to 5.0.13 and 5.1

When looking at the bug description saying  "this makes using mysqldump as a tool for moving data and schemas from one db to another rather difficult." - ie. dump a table and restore it to another name. 

Unfortunately mysqldump  is not the tool to use for this, it's purpose is to make backups of databases and tables. See http://dev.mysql.com/doc/refman/5.0/en/mysqldump.html