Bug #13382 Renaming a Table that is use in VIEWS leads to client problems
Submitted: 21 Sep 2005 15:16 Modified: 21 Sep 2005 15:50
Reporter: David Hammink Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Views Severity:S4 (Feature request)
Version:5.0.12/BK source OS:Linux (Linux Fedora Core 2)
Assigned to: CPU Architecture:Any

[21 Sep 2005 15:16] David Hammink
Description:
When one renames a table that is used in Views it can lead to erratic behaviour in clients.
the views in question are no longer valid.
Should the server forbid those renames. Or update the views ?
In case of deleting a table should the server delete the views cascadingly ?

How to repeat:
Create  a set of tables
Create a View on those tables
Rename one of the tables that are in the view
And in Information Schema you will see the errors in the views
[21 Sep 2005 15:50] MySQL Verification Team
miguel@hegel:~/dbs/5.0> bin/mysqladmin -uroot create gh
miguel@hegel:~/dbs/5.0> bin/mysql -uroot gh
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 5.0.14-rc-debug

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

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

mysql> create table tb2 (id2 int);
Query OK, 0 rows affected (0.03 sec)

mysql> create view tb_v as select * from tb1,tb2;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from information_schema.views\G
*************************** 1. row ***************************
  TABLE_CATALOG: NULL
   TABLE_SCHEMA: gh
     TABLE_NAME: tb_v
VIEW_DEFINITION: select `gh`.`tb1`.`id1` AS `id1`,`gh`.`tb2`.`id2` AS `id2` from `gh`.`tb1` join `gh`.`tb2`
   CHECK_OPTION: NONE
   IS_UPDATABLE: YES
        DEFINER: root@localhost
  SECURITY_TYPE: DEFINER
1 row in set (0.05 sec)

mysql> rename table tb2 to tb3;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from information_schema.views\G
Empty set, 1 warning (0.04 sec)

mysql> show warnings;
+---------+------+------------------------------------------------------------------------+
| Level   | Code | Message                                                                |
+---------+------+------------------------------------------------------------------------+
| Warning | 1356 | View 'gh.tb_v' references invalid table(s) or column(s) or function(s) |
+---------+------+------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql>