Bug #13818 SHOW CREATE VIEW / TABLE and information_schema.views fail for invalid view
Submitted: 6 Oct 2005 20:02 Modified: 22 Nov 2005 17:27
Reporter: Kolbe Kegel Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:5.0.14 OS:Linux (Linux)
Assigned to: Sergei Glukhov CPU Architecture:Any

[6 Oct 2005 20:02] Kolbe Kegel 
Description:
SHOW CREATE VIEW fails with an error when the definition of the view is invalid:

ERROR 1356 (HY000): View 'test.view1' references invalid table(s) or column(s) or function(s)

The information cannot be retrieved from information_schema.views either.

This is undesirable, since it makes it impossible to review the view definition to determine which invalid table(s) or column(s) or function(s) are referenced in the view definition. This essentially renders the view "lost", since nothing can be done to retrieve its original definition.

How to repeat:
CREATE TABLE table1 (col1 int, col2 int);
CREATE VIEW view1 AS SELECT col2 FROM table1;
ALTER TABLE table1 DROP col2;
SHOW CREATE VIEW view1;
SELECT * FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME='view1';
SHOW WARNINGS;

Suggested fix:
Permit SHOW CREATE VIEW, SHOW CREATE TABLE, and retrieval of metadata from information_schema even for invalid views. A warning should be issued in all cases if the view is invalid.
[25 Oct 2005 8:15] Bugs System 
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/31434
[2 Nov 2005 9:42] Bugs System 
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/31788
[11 Nov 2005 9:37] Bugs System 
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/32172
[11 Nov 2005 9:41] Sergei Glukhov 
Fixed in 5.0.17
[22 Nov 2005 17:27] Paul DuBois 
Noted in 5.0.17 changelog.