MySQL Bugs: #10927: mysqldump: Can't reload dump with view that consist of other view

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

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)

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`;

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

Pushed to 5.0.9

Noted in 5.0.9 changelog.