MySQL Bugs: #60389: MySQL WorkBench - Data Dump

Bug #60389	MySQL WorkBench - Data Dump - Export to Disk
Submitted:	8 Mar 2011 13:26	Modified:	10 Oct 2011 4:18
Reporter:	Ben Whittard	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Command-line Clients	Severity:	S2 (Serious)
Version:	5.2.34	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	Backup, Data Dump, export, workbench

Description:
Backups (or Exports) created by Workbench do not work when imported if you have a View that utilizes a Function. This is because in the .sql files created, Views are created before Functions, causing an error on import.

How to repeat:
Create a Function in MySQL.
Create a View in MySQL that uses the function.
In Workbench go to Administrator and Data Dump tab.
Export to Disk (either to dump project folder or self-contained file - both have the same issue)
Check Dump Views and Dump Stored Procedures
Start Export

Use the Export to Import from Disk
Import should fail.

Suggested fix:
Move Functions to be created before Views in the generated SQL when taking a dump, similar to how MySQL Administrator works.

Could you please try 5.2.32. Thanks in advance.

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".

Problem also occurs in version 5.2.34CE.  Please see http://bugs.mysql.com/bug.php?id=62153 for more details.

Hello.
Thanks a lot for your report.
Ben or Dave Power can you please share with us a DB dump where you can reproduce this?
Thanks.

I've done some more investigation on the nature of the problem.   The sequence of code generated in respect to the routines in the export is correct.  However, it appears there is a problem with the generation of the view statement if it contains a complex view statement with several subqueries.

Output in the dump statement:

/*!50001 DROP TABLE IF EXISTS `xt overall event attendence monthly`*/;
/*!50001 DROP VIEW IF EXISTS `xt overall event attendence monthly`*/;
/*!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 `xt overall event attendence monthly` AS select 'Volunteers' AS `Category`,concat('V',cast(`ve`.`Volunteer_ID` as char charset utf8)) AS `Named_ID`,`div`.`ID` AS `Division_ID`,`div`.`Name` AS `Division`,`pgm`.`ID` AS `Programme_ID`,`pgm`.`Name` AS `Programme`,`pjt`.`ID` AS `Project_ID`,`pjt`.`Name` AS `Project`,cast((`e`.`Start` + interval (1 - dayofmonth(`e`.`Start`)) day) as date) AS `Period`,count(`e`.`Duration`) AS `Events` from ((((`volunteers_attend_events` `ve` join `event` `e` on((`ve`.`Event_ID` = `e`.`ID`))) join `project` `pjt` on((`e`.`Project` = `pjt`.`ID`))) join `programme` `pgm` on((`pjt`.`Programme_id` = `pgm`.`ID`))) join `my divisions` `div` on((`pgm`.`Division` = `div`.`ID`))) group by 1,2,3,4,5,6,7,8,9 union select 'Players' AS `Classification`,concat('P',cast(`psp`.`Player_ID` as char charset utf8)) AS `concat('P',cast(PSP.Player_ID as char))`,`div`.`ID` AS `Division_ID`,`div`.`Name` AS `Division`,`pgm`.`ID` AS `Programme_ID`,`pgm`.`Name` AS `Programme`,`pjt`.`ID` AS `Project_ID`,`pjt`.`Name` AS `Project`,cast((`e`.`Start` + interval (1 - dayofmonth(`e`.`Start`)) day) as date) AS `Period`,count(`e`.`Duration`) AS `Events` from (((((`players_participate_in_events` `pe` join `players signup to projects` `psp` on((`pe`.`Player signup_ID` = `psp`.`ID`))) join `event` `e` on((`pe`.`Event_ID` = `e`.`ID`))) join `project` `pjt` on((`e`.`Project` = `pjt`.`ID`))) join `programme` `pgm` on((`pjt`.`Programme_id` = `pgm`.`ID`))) join `my divisions` `div` on((`pgm`.`Division` = `div`.`ID`))) group by 1,2,3,4,5,6,7,8,9 union select `p`.`Category` AS `Category`,`p`.`Named_ID` AS `Named_ID`,`p`.`Division_ID` AS `Division_ID`,`p`.`Division` AS `Division`,`p`.`Programme_ID` AS `Programme_ID`,`p`.`Programme` AS `Programme`,`p`.`Project_ID` AS `Project_ID`,`p`.`Project` AS `Project`,((curdate() + interval (1 - dayofmonth(curdate())) day) + interval -(`sequence nos`.`No`) month) AS `Adddate(AddDate(curdate(),1-day(curdate())), interval -``No`` Month)`,NULL AS `NULL` from (`xt overall projects` `p` join `sequence nos`) where (`sequence nos`.`No` < 26) */;
/*!50001 SET character_set_client      = @saved_cs_client */;
/*!50001 SET character_set_results     = @saved_cs_results */;
/*!50001 SET collation_connection      = @saved_col_connection */;

Orginal view definition:

CREATE  OR REPLACE VIEW `One in a Million`.`XT Overall Event Effort Monthly` AS
    select  'Volunteers' Category,
            concat('V',cast(ve.Volunteer_ID as char)) Named_ID,
            div.ID Division_ID,
            div.Name Division,
            pgm.ID Programme_ID,
            pgm.Name Programme,
            pjt.ID Project_ID,
            pjt.Name 'Project',
            date(AddDate(e.start,1-day(e.start))) `Period`,
            sum(hour(e.`duration`)+minute(e.`duration`)/60) `Events`
    from `Volunteers_attend_events` ve
                join Event E on ve.Event_ID=E.ID
                join `Project` Pjt on E.Project=Pjt.ID
                join Programme Pgm on Pjt.Programme_id=Pgm.ID
                join `My divisions` `div` on Pgm.Division=div.ID
    group by 1,2,3,4,5,6,7,8,9
union
    select  'Players' Classification,
            concat('P',cast(PSP.Player_ID as char)),
            div.ID Division_ID,
            div.Name Division,
            pgm.ID Programme_ID,
            pgm.Name Programme,
            pjt.ID Project_ID,
            pjt.Name 'Project',
            date(AddDate(e.start,1-day(e.start))) `Period`,
            sum(hour(e.`duration`)+minute(e.`duration`)/60) `Events`
    from `Players_participate_in_Events` PE join `Players signup to Projects` PSP on PE.`Player Signup_ID`=PSP.ID
                join Event E on PE.Event_ID=E.ID
                join `Project` Pjt on E.Project=Pjt.ID
                join Programme Pgm on Pjt.Programme_id=Pgm.ID
                join `My divisions` `div` on Pgm.Division=div.ID
    group by 1,2,3,4,5,6,7,8,9
union
    select p.*,Adddate(AddDate(curdate(),1-day(curdate())), interval -`No` Month),null from `XT Overall Projects` p,`Sequence Nos` where `No`<26;

The import routine fails when attempting to read the column "Adddate(AddDate..." in the last subquery.

It is possible to work around this problem by giving an explicit name for the column.  BUT WHY is the export routine attempting to give names to each column when generating the second and subsequent sub-query???

Thanks a lot for your report.

Does a "show create view <yourview>" show the expected view definition or the modified one?

The dump is created by mysqldump, so I'm reassigning it.