Bug #10927 mysqldump: Can't reload dump with view that consist of other view
Submitted: 27 May 2005 19:52 Modified: 14 Jul 2005 18:26
Reporter: David Hammink Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:5.0.4 OS:Linux (Fedora Core 3)
Assigned to: Magnus Blåudd CPU Architecture:Any

[27 May 2005 19:52] David Hammink
Description:
mysqldump does not handle views that consist of other views properly.
It does it alphabetically but if a view is like

AView
SELECT *
FROM bVIEW
WHERE date=CURDATE()

it reports the error that bView does not exists because it is load later

How to repeat:
Create two Views
one of a real table

CREATE ALGORITHM=UNDEFINED VIEW 'DBNAME'.'vB' AS 
SELECT *
FROM tbl

then a view ON that VIEW
CREATE ALGORITHM=UNDEFINED VIEW 'DBNAME'.'vA' AS 
SELECT *
FROM vB LIMIT 1;

 do a MysqlDump to another database and it will produce an error 
It will try to load vA first but that refers to vB and the Create does not allow that..

Suggested fix:
a call for CREate view that allows wrong view temporarily
(or a tool in mysqldump that fishes out the related views
I think I will script such a thing somewhere in the near future (if time permits)
[28 May 2005 1:48] Miguel Solorzano
Verified with BK source 5.0.7:

CREATE DATABASE dbname;
USE dbname;
CREATE TABLE tbl (col1 char(15));
INSERT INTO tbl VALUES ("data 1"), ("data 2");
CREATE ALGORITHM=UNDEFINED VIEW vb AS SELECT * FROM tbl;
CREATE ALGORITHM=UNDEFINED VIEW va AS SELECT * FROM vb LIMIT 1;

The dump looks like:

--
-- View structure for view `va`
--

DROP VIEW IF EXISTS `va`;
CREATE ALGORITHM=UNDEFINED VIEW `dbname`.`va` AS select `vb`.`col1` AS `col1` from `dbname`.`vb` limit 1;

--
-- View structure for view `vb`
--

DROP VIEW IF EXISTS `vb`;
CREATE ALGORITHM=UNDEFINED VIEW `dbname`.`vb` AS select `dbname`.`tbl`.`col1` AS `col1` from `dbname`.`tbl`;
[16 Jun 2005 15:08] Bugs System
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/26062
[30 Jun 2005 18:46] Magnus Blåudd
Pushed to 5.0.9
[14 Jul 2005 18:26] Paul Dubois
Noted in 5.0.9 changelog.