Bug #54139 cannot view structure of invalid view in some cases
Submitted: 1 Jun 2010 12:16 Modified: 10 Jan 2013 14:32
Reporter: Shane Bester (Platinum Quality Contributor) Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server: Views Severity:S3 (Non-critical)
Version:>=5.1.40, 5.1.47, 5.5.4 OS:Any
Assigned to: CPU Architecture:Any
Tags: regression
Triage: Triaged: D3 (Medium)

[1 Jun 2010 12:16] Shane Bester
Description:
if a view's underlying columns don't exist anymore, then 'show create view' fails. however, if underlying tables don't exist, we are able to see the view structure, and therefore fix the problem.  this is inconsistent.

testcase output:
<cut>
mysql> alter table t1 change a b int;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create view v1;
ERROR 1356 (HY000): View 'test.v1' references invalid table(s) or c

given the above, it is impossible to know why the view is failing,
since we cannot see the structure.  however:

mysql> drop table t1;
Query OK, 0 rows affected (0.00 sec)

mysql> show create view v1;
+------+-----------------------------------------------------------
| View | Create View
+------+-----------------------------------------------------------
| v1   | CREATE ALGORITHM=UNDEFINED DEFINER=``@`` SQL SECURITY DEFI
+------+-----------------------------------------------------------
</cut>

this is a regression (probably introduced with fix for bug #35996), since 5.0.91 and <=5.1.39 lets us see the view in both cases.

How to repeat:
drop table if exists t1,v1;                   
create table t1(a int)engine=myisam;          
create or replace view v1 as select a from t1;
alter table t1 change a b int;
show create view v1;#fails!
drop table t1;
show create view v1;#works!
[4 Jul 2011 19:24] Shane Bester
bug #61718 is a duplicate
[4 Jul 2011 19:25] Shane Bester
workaround: open the .frm file in an editor, or somehow use LOAD_FILE() sql function to read it..
[4 Jul 2011 19:41] Peter Laursen
@Shane .. yes .. mine is a duplicate of yours (only my report has observations about SELECT FROM I_S that this one/yours has not).  It has been known now for +1 year without a fix. Totally ridiculous!
[4 Oct 2012 8:50] Shane Bester
there is a patch on the duplicate:
Bug 12762393 - CANNOT SHOW CREATE VIEW IF UNDERLYING TABELS ARE ALTERED
[10 Jan 2013 14:32] Erlend Dahl
Fixed as a duplicate of bug#61718