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
Triage: Needs Triage: D2 (Serious)

[23 Jul 2011 16:58] A Ramos
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.
[24 Jul 2011 10:00] Valeriy Kravchuk
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
[24 Aug 2011 23:00] Bugs System
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".
[25 Sep 2011 23:00] Bugs System
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".
[8 Oct 2012 22:13] David Oberst
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.
[19 Dec 2012 20:35] Sveta Smirnova
David,

thank you for the feedback. Verified as described.

Test case will be attached soon.
[19 Dec 2012 20:35] Sveta Smirnova
test case for MTR

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