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
Category:	Client	Severity:	S3 (Non-critical)
Version:	5.0	OS:
Assigned to:	Magnus Blaudd	Target Version:

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

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

New FEATURES

This MySQL is very fast because there are features missing that
are familiar with Fire Bird DB (Inter Base)used with the "Flame Robin" 
console.
The Flame Robin is wonderfull. You can
- Backup a DB and restore a "copy" of it with another name (to the same host)
- You can copy selected QUERY result row contents as INSERT or UPDATE SQL-commands
- You can EDIT the cells (fields)on a QUERY result and just click COMMIT-button