Description:
I have a view which references a function. Dumping database with "dump stored routines" checked produces a file with create view instruction before create function.
Importing dump fails with "error 1035 (42000) : function XXX doesn't exist".
-- MySQL dump 10.13 Distrib 5.5.17, for Win64 (x86)
How to repeat:
Sample database dump is below (a table with a PK and a data column of type integer, a function that returns modulo an integer, a view which relies on table and function to show PK and modulo) , importing it with command :
D:\>"C:\Program Files\MySQL\MySQL Server 5.5\bin\mysql" -B -uroot -proot -hlocalhost -Dtest -P3307 0<d:\temp\test_dump.sql
fails :
ERROR 1305 (42000) at line 70: FUNCTION test.test_function does not exist
CREATE DATABASE IF NOT EXISTS `test` /*!40100 DEFAULT CHARACTER SET latin1 */;
USE `test`;
-- MySQL dump 10.13 Distrib 5.5.17, for Win64 (x86)
--
-- Host: localhost Database: test
-- ------------------------------------------------------
-- Server version 5.5.17-log
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
--
-- Table structure for table `test_table`
--
DROP TABLE IF EXISTS `test_table`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `test_table` (
`id` int(11) NOT NULL,
`data` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `test_table`
--
LOCK TABLES `test_table` WRITE;
/*!40000 ALTER TABLE `test_table` DISABLE KEYS */;
INSERT INTO `test_table` VALUES (1,2),(2,3),(3,5);
/*!40000 ALTER TABLE `test_table` ENABLE KEYS */;
UNLOCK TABLES;
--
-- Temporary table structure for view `test_view`
--
DROP TABLE IF EXISTS `test_view`;
/*!50001 DROP VIEW IF EXISTS `test_view`*/;
SET @saved_cs_client = @@character_set_client;
SET character_set_client = utf8;
/*!50001 CREATE TABLE `test_view` (
`id` int(11),
`modulo` int(11)
) ENGINE=MyISAM */;
SET character_set_client = @saved_cs_client;
--
-- Final view structure for view `test_view`
--
/*!50001 DROP TABLE IF EXISTS `test_view`*/;
/*!50001 DROP VIEW IF EXISTS `test_view`*/;
/*!50001 SET @saved_cs_client = @@character_set_client */;
/*!50001 SET @saved_cs_results = @@character_set_results */;
/*!50001 SET @saved_col_connection = @@collation_connection */;
/*!50001 SET character_set_client = utf8 */;
/*!50001 SET character_set_results = utf8 */;
/*!50001 SET collation_connection = utf8_general_ci */;
/*!50001 CREATE ALGORITHM=UNDEFINED */
/*!50013 DEFINER=`root`@`localhost` SQL SECURITY DEFINER */
/*!50001 VIEW `test_view` AS select `t`.`id` AS `id`,`test_function`(`t`.`data`) AS `modulo` from `test_table` `t` */;
/*!50001 SET character_set_client = @saved_cs_client */;
/*!50001 SET character_set_results = @saved_cs_results */;
/*!50001 SET collation_connection = @saved_col_connection */;
--
-- Dumping events for database 'test'
--
--
-- Dumping routines for database 'test'
--
/*!50003 DROP FUNCTION IF EXISTS `test_function` */;
/*!50003 SET @saved_cs_client = @@character_set_client */ ;
/*!50003 SET @saved_cs_results = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client = utf8 */ ;
/*!50003 SET character_set_results = utf8 */ ;
/*!50003 SET collation_connection = utf8_general_ci */ ;
/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
/*!50003 SET sql_mode = 'STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ ;
DELIMITER ;;
/*!50003 CREATE*/ /*!50020 DEFINER=`root`@`localhost`*/ /*!50003 FUNCTION `test_function`(someValue int) RETURNS int(11)
NO SQL
BEGIN
RETURN someValue % 2;
END */;;
DELIMITER ;
/*!50003 SET sql_mode = @saved_sql_mode */ ;
/*!50003 SET character_set_client = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection = @saved_col_connection */ ;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
-- Dump completed on 2012-10-24 9:14:53
Suggested fix:
Dump tables, then check views and procedures/functions dependance to know in which order to dump, because it seems to be less simple that just "dump procedures/functions before views".
Description: I have a view which references a function. Dumping database with "dump stored routines" checked produces a file with create view instruction before create function. Importing dump fails with "error 1035 (42000) : function XXX doesn't exist". -- MySQL dump 10.13 Distrib 5.5.17, for Win64 (x86) How to repeat: Sample database dump is below (a table with a PK and a data column of type integer, a function that returns modulo an integer, a view which relies on table and function to show PK and modulo) , importing it with command : D:\>"C:\Program Files\MySQL\MySQL Server 5.5\bin\mysql" -B -uroot -proot -hlocalhost -Dtest -P3307 0<d:\temp\test_dump.sql fails : ERROR 1305 (42000) at line 70: FUNCTION test.test_function does not exist CREATE DATABASE IF NOT EXISTS `test` /*!40100 DEFAULT CHARACTER SET latin1 */; USE `test`; -- MySQL dump 10.13 Distrib 5.5.17, for Win64 (x86) -- -- Host: localhost Database: test -- ------------------------------------------------------ -- Server version 5.5.17-log /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; /*!40101 SET NAMES utf8 */; /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */; /*!40103 SET TIME_ZONE='+00:00' */; /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */; /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */; -- -- Table structure for table `test_table` -- DROP TABLE IF EXISTS `test_table`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `test_table` ( `id` int(11) NOT NULL, `data` int(11) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `test_table` -- LOCK TABLES `test_table` WRITE; /*!40000 ALTER TABLE `test_table` DISABLE KEYS */; INSERT INTO `test_table` VALUES (1,2),(2,3),(3,5); /*!40000 ALTER TABLE `test_table` ENABLE KEYS */; UNLOCK TABLES; -- -- Temporary table structure for view `test_view` -- DROP TABLE IF EXISTS `test_view`; /*!50001 DROP VIEW IF EXISTS `test_view`*/; SET @saved_cs_client = @@character_set_client; SET character_set_client = utf8; /*!50001 CREATE TABLE `test_view` ( `id` int(11), `modulo` int(11) ) ENGINE=MyISAM */; SET character_set_client = @saved_cs_client; -- -- Final view structure for view `test_view` -- /*!50001 DROP TABLE IF EXISTS `test_view`*/; /*!50001 DROP VIEW IF EXISTS `test_view`*/; /*!50001 SET @saved_cs_client = @@character_set_client */; /*!50001 SET @saved_cs_results = @@character_set_results */; /*!50001 SET @saved_col_connection = @@collation_connection */; /*!50001 SET character_set_client = utf8 */; /*!50001 SET character_set_results = utf8 */; /*!50001 SET collation_connection = utf8_general_ci */; /*!50001 CREATE ALGORITHM=UNDEFINED */ /*!50013 DEFINER=`root`@`localhost` SQL SECURITY DEFINER */ /*!50001 VIEW `test_view` AS select `t`.`id` AS `id`,`test_function`(`t`.`data`) AS `modulo` from `test_table` `t` */; /*!50001 SET character_set_client = @saved_cs_client */; /*!50001 SET character_set_results = @saved_cs_results */; /*!50001 SET collation_connection = @saved_col_connection */; -- -- Dumping events for database 'test' -- -- -- Dumping routines for database 'test' -- /*!50003 DROP FUNCTION IF EXISTS `test_function` */; /*!50003 SET @saved_cs_client = @@character_set_client */ ; /*!50003 SET @saved_cs_results = @@character_set_results */ ; /*!50003 SET @saved_col_connection = @@collation_connection */ ; /*!50003 SET character_set_client = utf8 */ ; /*!50003 SET character_set_results = utf8 */ ; /*!50003 SET collation_connection = utf8_general_ci */ ; /*!50003 SET @saved_sql_mode = @@sql_mode */ ; /*!50003 SET sql_mode = 'STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ ; DELIMITER ;; /*!50003 CREATE*/ /*!50020 DEFINER=`root`@`localhost`*/ /*!50003 FUNCTION `test_function`(someValue int) RETURNS int(11) NO SQL BEGIN RETURN someValue % 2; END */;; DELIMITER ; /*!50003 SET sql_mode = @saved_sql_mode */ ; /*!50003 SET character_set_client = @saved_cs_client */ ; /*!50003 SET character_set_results = @saved_cs_results */ ; /*!50003 SET collation_connection = @saved_col_connection */ ; /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */; /*!40101 SET SQL_MODE=@OLD_SQL_MODE */; /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */; /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */; /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */; /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */; /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */; /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */; -- Dump completed on 2012-10-24 9:14:53 Suggested fix: Dump tables, then check views and procedures/functions dependance to know in which order to dump, because it seems to be less simple that just "dump procedures/functions before views".