MySQL Bugs: #66502: Unable to alter view that references invalid columns/tables

Bug #66502	Unable to alter view that references invalid columns/tables
Submitted:	22 Aug 2012 15:55	Modified:	15 Jan 2013 5:48
Reporter:	Bob Dankert	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Workbench: SQL Editor	Severity:	S2 (Serious)
Version:	5.2.42	OS:	Windows
Assigned to:		CPU Architecture:	Any
Tags:	invalid column, invalid table, reference, VIEW

Description:
If you have a view with an invalid table or column in the definition, you are not able to alter or edit this view.  This is problematic.  This means if you alter a table used in your view, you can not go in to fix the view so it is correct.  Currently I must get the view definition from the information_schema, drop the view, and re-create the view.

How to repeat:
Create a view, then modify the table referenced by the view (change a column name, etc).

Suggested fix:
Allow the view to be modified so it can be fixed/corrected.

Thank you for the bug report.

Hi I can't repeat this, can you give an example/description of how to repeat?
I tried dropping a table that is referenced from a view and when I try to expand the view from the
schema tree it will indeed show as broken. But I can call ALTER TABLE on it just fine.

Here are the steps I just went through to repeat the problem:

- Create a basic table using the UI in workbench (I created a table called test with two columns:  id(int primary) and testcol(varchar)
- create a basic view referencing the above table, using the UI.  I did something like this:  create view new_view select testcol from test;
- Alter the table test using the WB UI to rename the testcol column.  I changed it from testcol to testcol2.
- Right click on the View and try to alter it.  I get an error message box stating: 
"Error getting DDL for object.
View 'database.new_view' referenced invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them"

Because of this error message, I am unable to alter the view using the Workbench UI.  

Hopefully this clears up the issue.

I just experienced this same problem this morning using Workbench 5.2.44.  My default is INNODB and I have INNODB_STRICT_MODE set.  I don't know if that might make a difference.  Recovery in my case was to re-add the deleted column and then alter the view followed by deleting the column again.

Fixed as of the upcoming MySQL Workbench 5.2.46, and here's the changelog entry:

Changes to a table that a view referenced would cause an error while
attempting to edit the view. This fix allows MySQL Workbench to work around
the underlying bug, which is MySQL bug #61718.

Thank you for the bug report.