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
• Status: Verified
• Category: MySQL Server: Views
• Severity: S4 (Feature request)
• Version: 5.0.12/BK source
• OS: Linux (Linux Fedora Core 2)
• 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>