MySQL Bugs: #54980: Comment to view will cause update forever

Bug #54980	Comment to view will cause update forever
Submitted:	3 Jul 2010 14:04	Modified:	6 Jul 2010 5:49
Reporter:	Martin Pirringer	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Workbench: Modeling	Severity:	S3 (Non-critical)
Version:	5.2.25	OS:	Windows (7)
Assigned to:		CPU Architecture:	Any
Tags:	comments, Views

Description:
Adding a comment to a view will cause it to sync always

How to repeat:
Create a view with comment

--
-- This is the comment to my view
--

Create ..... <view statement>

Suggested fix:
develop a way that views can have a comment

As far as I can see "update forever"  happens to any view. See Bug #54981, for example. Can you provide any model where view without comments does not cause "update forever"?

You can stop any view without a comment from syncinc/updating forever.

1.) Create a view
2.) sync with the database
3.) sync again only this time select "update model"

This will put the view in the format WB likes and does not update it again but it will destroy any formatting the view will look something like this

CREATE ALGORITHM=UNDEFINED DEFINER=`admin`@`%` SQL SECURITY DEFINER VIEW `custlist` AS select `customer`.`TERMINATED` AS `TERMINATED`,`customer`.`CUSTNO` AS `custno`,`customer`.`INACTIVE` AS `inactive`,`customer`.`COMPANY` AS `company`,`customer`.`ADDRESS1` AS `address1`,`customer`.`ADDRESS2` AS `address2`,`customer`.`CITY` AS `city`,`customer`.`STATE` AS `state`,`customer`.`ZIP` AS `zip`,`customer`.`COUNTRY` AS `country`,`customer`.`PHONE` AS `phone`,`customer`.`FAXNO` ASz `faxno`,sum(`b`.`BALANCE`) AS `balance` from (`customer` USE INDEX (PRIMARY) USE INDEX (`company`) join `invoice master` `b` USE INDEX (`custno`) USE INDEX (`balance`) on((`customer`.`CUSTNO` = `b`.`CUSTNO`))) group by `b`.`CUSTNO`

Please note that it will be in one line.

As long as you do not touch the view it will not sync/update again. If you as much as add a linefeed or a space it will update again. 

Suggestion: This seems the way it comes back from the mysql server. So the update sync/routine could take the view stored in WB and convert it into above format before making the decision to sync or not sync so

remove all comments, remove all line breaks, double spaces and add the aliases and quotes. It might make it necessary for mysql to add a field in the views table to store comments too. Another workaround maybe would be for WB to create its own Database next to the information schema to store features not provided by the information schema so there could be a table "viewextensions" that links to the primary key in the information schema and then has maybe a text field for "formatted view" and "view comment". This also could provide reference too and aid in other areas for features that WB wants to support and MYSQL does not like for example custom defined data types to avoid destroying custom defined datatypes when updating the model from the schema.

Thank you for the detailed explanation. I was able to repeat this on Mac based on it, but, essentially, it is a duplicate of bug #52004, IMHO. Do you agree?

Don't know what Bug 52004 is so it is possible - if it is a duplication - then I apologize.

Duplicate of/related to bug #52004.