| Bug #59018 | Invalid Create View script generated in Export Model | ||
|---|---|---|---|
| Submitted: | 18 Dec 2010 0:11 | Modified: | 14 Jan 2014 3:22 |
| Reporter: | Dave Power | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Workbench: Modeling | Severity: | S3 (Non-critical) |
| Version: | 5.2.30CE (rev 6790), 5.2.31 | OS: | Any (Windows, Mac OS X) |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | create view | ||
[18 Dec 2010 8:34]
Valeriy Kravchuk
Please, send .mwb file that demonstrates the problem.
[18 Dec 2010 9:28]
Dave Power
Workbench file demonstrating problem
Attachment: MySQL WorkBench Bug.mwb (, text), 167.83 KiB.
[18 Dec 2010 9:30]
Dave Power
Problem may be linked to the order of creating views. i.e. if you create a view A which includes a reference to view B.
[18 Dec 2010 16:14]
Valeriy Kravchuk
Verified using .mwb file uploaded with 5.2.31 on Mac OS X. This is what we get when trying to execute script generated: macbook-pro:5.5-sec openxs$ bin/mysql -uroot test < ~/Downloads/bug59018.sql ERROR 1054 (42S22) at line 59: Unknown column 'Given Name' in 'field list'
[18 Dec 2010 16:35]
Dave Power
The error message is the same as I found. The problem is definately linked to the order the SQL statements are generated. There is a work around by deleting the view object and re-creating it. i.e. If I recreate the `Player Enhanced` view object, it now appears at the end of the view definitions and hence after the `My Player` definition has been correctly defined. Hence, under normal circumstances the problem with creating a temporary table definition called `My Player` with a single field as this temporary definition is not used. I'll add a Public "WORK AROUND" statement to the problem.
[18 Dec 2010 16:43]
Dave Power
WORK AROUND 1. The problem is linked to the order you created the Views in the workbench. 2. As mitigating action, delete the dependent view definitions and replace them with new view definitions. This will change the order Workbench generates the create view statements in the export file, and hence whilst the initial "create table" statement will still be incorrect, it will have been replaced by the correct view definition by the time the dependent view statements being created. Well this work around worked for me! but if does not exactly resolve your problem hopefully it should provide a clue for how to mitigate the action should you experience a similar problem.
[14 Jan 2014 3:22]
Philip Olson
Fixed as of the upcoming MySQL Workbench 6.1.1 release, and here is the changelog entry: The temporary table created would only include one column ID rather than the full table when a view statement contained a subquery. This caused processing problems when attempting to use the exported scripts. A workaround was to recreate the view object by deleting the dependent view definitions and replacing them with new view definitions. Thank you for the bug report.
Description: The temporary table created only includes one column ID rather than the full table when a view statement contains a subsequery. This causes processing problems when attempting to use the exported scripts. How to repeat: The following view definition faults to be created correctly. CREATE VIEW `One in a Million`.`My Players` AS select * from player where id in ( select distinct p.id from player p,division d,DatabaseID L where p.`sponsoring division`=d.ID and d.leader=L.ID union select p.id from player p,`Division Administrators` da,DatabaseID L where p.`sponsoring division`=da.Division_ID and da.Volunteer_ID=L.ID union select `Kids Base_ID` from `Player interest in Programmes` p,Programme Pgm,DatabaseID L where p.Programmes_id=Pgm.ID and Pgm.Leader=L.ID union select p.Player_ID from `Players signup to Projects` p,Project Pj,DatabaseID L where p.Project_ID=Pj.ID and Pj.Leader=L.ID and (Pj.finish is null or Pj.finish > now()))