Description:
Bug has been reported, and figures as closed (to be solved on next version); however, I've found an easy way to deal with it, and, since I'm happy with MySQL, I would like to share it with the community
How to repeat:
--
-- Create schema view_issue_demo
--
CREATE DATABASE /*!32312 IF NOT EXISTS*/ view_issue_demo;
USE view_issue_demo;
--
-- Table structure for table `view_issue_demo`.`table_1`
--
DROP TABLE IF EXISTS `table_1`;
CREATE TABLE `table_1` (
`some_id` int(10) unsigned NOT NULL auto_increment,
`dummy_data` char(16) NOT NULL default '',
PRIMARY KEY (`some_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
--
-- Dumping data for table `view_issue_demo`.`table_1`
--
/*!40000 ALTER TABLE `table_1` DISABLE KEYS */;
INSERT INTO `table_1` (`some_id`,`dummy_data`) VALUES
(1,'first dummy row'),
(2,'second dummy row');
/*!40000 ALTER TABLE `table_1` ENABLE KEYS */;
--
-- View structure for view `view_issue_demo`.`second_view`
--
DROP VIEW IF EXISTS `second_view`;
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `second_view` AS select `z_first_view`.`some_id` AS `some_id`,`z_first_view`.`dummy_data` AS `dummy_data` from `z_first_view`;
--
-- View structure for view `view_issue_demo`.`z_first_view`
--
DROP VIEW IF EXISTS `z_first_view`;
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `z_first_view` AS select `table_1`.`some_id` AS `some_id`,`table_1`.`dummy_data` AS `dummy_data` from `table_1`;
Suggested fix:
When defining views, use the following prefixes:
000-<yourViewNameHere>: When view depends only on tables.
001-<yourViewNameHere>: When view depends on other views.
002-<yourViewNameHere>: When view depends on views named as 1-...
And so on...
Childish, but works.
Why does it work? Because BackUp stores views in alphabetical order, so "strong entities" are stored first, and when you restore "weak entities", the objects theese depend on are yet available.
You may have problems with views that depends on views (001-) if dependant's name is "smaller" (ordered first) than objects name they depend on, however it's easier to find. Find out variations!
