MySQL Bugs: #61961: mysqldump has wrong order for views with functions

Bug #61961	mysqldump has wrong order for views with functions
Submitted:	23 Jul 2011 16:58	Modified:	19 Dec 2012 20:35
Reporter:	A Ramos	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: mysqldump Command-line Client	Severity:	S3 (Non-critical)
Version:	5.0.75, 5.0.97, 5.1.68, 5.5.30, 5.7.1	OS:	Any (IBM AIX, Linux)
Assigned to:		CPU Architecture:	Any
Tags:	mysqldump

Description:
the current order for mysqldump on database that has both views and functions is:

1. the dummy "tablified" version of the views,
2. the view definitions,
3. the functions.

This order is incorrect and will cause a dump that cannot be loaded, if the view depends on a function.

How to repeat:
create a function, then creates a view that calls the function.
then use mysqldump to create a backup.
then drop the database.
then try to reload from the backup.

Suggested fix:
Dumping in the following order would solve the problem:

1. the dummy "tablified" version of the views,
3. the functions,
2. the view definitions.

Please, check if the same problem still happens with a newer version, 5.0.92. This is what I see in current MySQL 5.1:

macbook-pro:5.1 openxs$ bin/mysqldump --routines -uroot dbf
-- MySQL dump 10.13  Distrib 5.1.59, for apple-darwin9.6.0 (i386)
--
-- Host: localhost    Database: dbf
-- ------------------------------------------------------
-- Server version	5.1.59-debug

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;

...

--
-- Temporary table structure for view `v1`
--

DROP TABLE IF EXISTS `v1`;
/*!50001 DROP VIEW IF EXISTS `v1`*/;
SET @saved_cs_client     = @@character_set_client;
SET character_set_client = utf8;
/*!50001 CREATE TABLE `v1` (
  `a` int(11)
) ENGINE=MyISAM */;
SET character_set_client = @saved_cs_client;

--
-- Dumping routines for database 'dbf'
--
/*!50003 DROP FUNCTION IF EXISTS `f1` */;
/*!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  = latin1 */ ;
/*!50003 SET character_set_results = latin1 */ ;
/*!50003 SET collation_connection  = latin1_swedish_ci */ ;
/*!50003 SET @saved_sql_mode       = @@sql_mode */ ;
/*!50003 SET sql_mode              = 'STRICT_ALL_TABLES' */ ;
DELIMITER ;;
/*!50003 CREATE*/ /*!50020 DEFINER=`root`@`localhost`*/ /*!50003 FUNCTION `f1`() RETURNS int(11)
return 1 */;;
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 */ ;

--
-- Final view structure for view `v1`
--

/*!50001 DROP TABLE IF EXISTS `v1`*/;
/*!50001 DROP VIEW IF EXISTS `v1`*/;
/*!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      = latin1 */;
/*!50001 SET character_set_results     = latin1 */;
/*!50001 SET collation_connection      = latin1_swedish_ci */;
/*!50001 CREATE ALGORITHM=UNDEFINED */
/*!50013 DEFINER=`root`@`localhost` SQL SECURITY DEFINER */
/*!50001 VIEW `v1` AS select `f1`() AS `a` */;
/*!50001 SET character_set_client      = @saved_cs_client */;
/*!50001 SET character_set_results     = @saved_cs_results */;
/*!50001 SET collation_connection      = @saved_col_connection */;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;

...

-- Dump completed on 2011-07-24 12:58:28

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

I'm seeing the same problematic export order as the original reporter.  So the order for "myView1", which calls "myFunction" is, the relevant lines in exported order are:

-- MySQL dump 10.13  Distrib 5.1.66, for apple-darwin10.3.0 (i386)
-- Temporary table structure for view `myView1`
-- Final view structure for view `myView2`
/*!50003 CREATE*/ /*!50020 DEFINER=`definername`@`%`*/ /*!50003 FUNCTION `myFunction`(`IN_status` int) RETURNS varchar(10) CHARSET utf8

And so reading these commands back in fails because myFunction hasn't been defined when the final definition of myView1 is encountered.

In my case, I'm explicitly specifying the views I want dumped, something like:

   mysqldump --routines dbname vw_myview1 myview2

If I just do a plain dump (mysqldump --routines dbname), it DOES seem to export correctly - the routines are exported between the temporary and final view sections.  I'm assuming that by specifying tables (or views) specifically, mysqldump is doing something slightly differently, which results in the specified views being exported before the routines.

Since the export still does all temporaries before starting on the finals, there's no reason it couldn't check for the --routines setting in between and dump them out there.  It is deeply irritating to have mysqldump export out something it can't read back in properly!  Hopefully this will actually get looked at, although I'm not holding my breath... :)

It would be interesting to know whether 5.5 or 5.6 handle this any better.

David,

thank you for the feedback. Verified as described.

Test case will be attached soon.

test case for MTR

Attachment: bug61961.test (application/octet-stream, text), 375 bytes.