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)
• Status: Duplicate
• Category: MySQL Server: Views
• Severity: S3 (Non-critical)
• Version: >=5.1.40, 5.1.47, 5.5.4
• OS: Any
• CPU Architecture: Any
• Tags: regression

[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] MySQL Verification Team

bug #61718 is a duplicate

[4 Jul 2011 19:25] MySQL Verification Team

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] MySQL Verification Team

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