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