Description:
Currently, MySQL requires that the objects references by view definitions exist when creating a view. MTK does not resolve the order of views properly and generates non-executeable SQL. mysqldump has the same problem, but solves it by generating stand-in tables which are later replaced by a view definition.
To resolve the problem, the stand-in table definitions must be added manually.
How to repeat:
create table basetable ( id integer );
create view first as select * from basetable;
create view second as select * from first;
in Oracle, migrate to MySQL.
Suggested fix:
Use the stand-in table technique that mysqldump uses for this:
--
-- Dumping data for table `basetable`
--
/*!40000 ALTER TABLE `basetable` DISABLE KEYS */;
LOCK TABLES `basetable` WRITE;
UNLOCK TABLES;
/*!40000 ALTER TABLE `basetable` ENABLE KEYS */;
--
-- Table structure for table `first`
--
DROP TABLE IF EXISTS `first`;
/*!50001 DROP VIEW IF EXISTS `first`*/;
/*!50001 DROP TABLE IF EXISTS `first`*/;
/*!50001 CREATE TABLE `first` (
`id` int(11)
) */;
--
-- Table structure for table `second`
--
DROP TABLE IF EXISTS `second`;
/*!50001 DROP VIEW IF EXISTS `second`*/;
/*!50001 DROP TABLE IF EXISTS `second`*/;
/*!50001 CREATE TABLE `second` (
`id` int(11)
) */;
--
-- View structure for view `first`
--
/*!50001 DROP TABLE IF EXISTS `first`*/;
/*!50001 DROP VIEW IF EXISTS `first`*/;
/*!50001 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `first` AS select `basetable`.`id` AS `id` from `basetable`*/;
--
-- View structure for view `second`
--
/*!50001 DROP TABLE IF EXISTS `second`*/;
/*!50001 DROP VIEW IF EXISTS `second`*/;
/*!50001 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `second` AS select `first`.`id` AS `id` from `first`*/;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
