Bug #10322 The dump of database containing VIEW can't be dump to another database.
Submitted: 2 May 2005 20:37 Modified: 14 Sep 2005 13:41
Reporter: Disha Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server: Replication Severity:S2 (Serious)
Version:5.0.4.-beta-standard OS:Any (Any)
Assigned to: Bugs System CPU Architecture:Any

[2 May 2005 20:37] Disha
Description:
Create database say test3. Create view say v1 in database test3. If we go to dump this database  to another database the dump is not allowed.

How to repeat:
Execute the following on 'mysql' prompt:

   drop database if exists test3//
   drop database if exists test4//
   Create database test3 //
   Use test3 //
  Create table tb1(f1 int) //
  Insert into tb1 values (21) //
  Create view v1 as Select * from tb1 //
  Create database test4 //

Execute the following on Command prompt :
  mysqldump -u root test3 > mytest.sql
  mysql -u root test4 < mytest.sql

Actual Result : The execution on command prompt displays the following error message:
     ERROR 1050 (42S01) at line 47: Table 'v1' already exists

Expected Result : The database test4 should create successfully containing the   view v1.
[2 May 2005 21:26] Hartmut Holzgraefe
mysqldump prepends the database name to the "CREATE VIEW" statement but not
to the preceeding "DROP" statement:

DROP VIEW IF EXISTS `v1`;
CREATE ALGORITHM=UNDEFINED VIEW `test3`.`v1` AS select `test3`.`tb1`.`f1` AS `f1` from `test3`.`tb1`;

when restoring a dump into a different database any hardcoded references to the original database name are a bit confusiong to say at least ...
[19 Aug 2005 6:27] Patrick Galbraith
The calls mysqldump makes are this:

050819  8:20:25       2 Query       show tables
                      1 Query       LOCK TABLES `tbl` READ /*!32311 LOCAL */,`v1` READ /*!32311 LOCAL */
                      1 Query       SET OPTION SQL_QUOTE_SHOW_CREATE=1
                      1 Query       show create table `tbl`
                      1 Query       SHOW TRIGGERS LIKE 'tbl'
                      1 Query       show fields from `tbl`
                      1 Query       show table status like 'tbl'
                      1 Query       SELECT /*!40001 SQL_NO_CACHE */ * FROM `tbl`
                      1 Query       SET OPTION SQL_QUOTE_SHOW_CREATE=1
                      1 Query       show create table `v1`
                      1 Query       CREATE TEMPORARY TABLE `v1` SELECT * FROM `v1` WHERE 0
                      1 Query       SHOW CREATE TABLE `v1`
050819  8:19:07       1 Query       DROP TEMPORARY TABLE `v1`
                      1 Query       UNLOCK TABLES

The one call:

mysql> show create table `v1`;
+------+------------------------------------------------------------------------------------------------------+
| View | Create View                                                                                          |
+------+------------------------------------------------------------------------------------------------------+
| v1   | CREATE ALGORITHM=UNDEFINED VIEW `test3`.`v1` AS select `test3`.`tbl`.`f1` AS `f1` from `test3`.`tbl` |
+------+------------------------------------------------------------------------------------------------------+
1 row in set (0.09 sec)

Is the source of the db name being in the 'table' (view) definition. If there is a way to supress the db name and just display the view name, then that would solve this.
[6 Sep 2005 0:07] Patrick Galbraith
waiting on push of 10713, which will fix this issue.
[14 Sep 2005 13:41] Magnus BlÄudd
Fixed by push of bug#10713