MySQL Bugs: #14985: Removing a Table "referenced" by a View is allowed

Bug #14985	Removing a Table "referenced" by a View is allowed
Submitted:	16 Nov 2005 13:07	Modified:	13 Mar 2014 8:10
Reporter:	Alexandre Gomes	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Views	Severity:	S3 (Non-critical)
Version:	5.0.17 BK	OS:	Linux (Linux)
Assigned to:		CPU Architecture:	Any

Description:
If we remove a table that is referenced by one or more Views mysql says nothing at all.

How to repeat:
Create a table and a view that uses it. Drop the table.

Suggested fix:
Shouldn't be possible to drop the table without droping all the related views. Unless the use knows what he is doing, in that case a "force" option should be there and a warning should be shown.

Thank you for the bug report.

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.0.17-debug

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> create table tb4 (id int);
Query OK, 0 rows affected (0.04 sec)

mysql> insert into tb4 set id = 1;
Query OK, 1 row affected (0.00 sec)

mysql> create view tbv4 as select * from tb4;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from tbv4;
+------+
| id   |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

mysql> drop table tb4;
Query OK, 0 rows affected (0.01 sec)

mysql> select * from tbv4;
ERROR 1356 (HY000): View 'test.tbv4' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
mysql>

Sorry, we will not fix this bug in 5.0.

This is a known limitation of MySQL views, which we plan to lift in future:

You can use DROP TABLE or ALTER TABLE to drop or alter a table that is used in a view definition (which invalidates the view) and no warning results from the drop or alter operation. An error occurs later when the view is used.

For more information, plese refer to
http://dev.mysql.com/doc/refman/5.0/en/view-restrictions.html

The current limitation is documented at:

http://dev.mysql.com/doc/refman/5.0/en/create-view.html

"Any table or view referred to in the definition must exist. However, after a view has been created, it is possible to drop a table or view that the definition refers to. To check a view definition for problems of this kind, use the CHECK TABLE statement."

This is documented behavior. I'm not aware of any plans to fix it.

This behaviour is still around as of 5.7. Setting to "verified" as we are discontinuing the use of "to be fixed later".