Bug #71750 Make DROP TABLE report the table is a view if user has rights to view

• Submitted: 17 Feb 2014 12:11
• Modified: 17 Jun 2014 12:24
• Reporter: Simon Mudd (OCA)
• Status: Verified
• Category: MySQL Server: DDL
• Severity: S4 (Feature request)
• Version: 5.6.14
• OS: Any
• CPU Architecture: Any
• Tags: drop table, Views

[17 Feb 2014 12:11] Simon Mudd

Description:
MySQL can sometimes show view names as tables.

This can lead one to try to drop such a table and get an error which is confusing because you are not told the table you are trying to drop is actually a view. Improving the error message would reduce confusion in these cases.

How to repeat:
root@myserver [db]> show tables like 'mytable';
+------------------------+
| Tables_in_db (mytable) |
+------------------------+
| mytable                |
+------------------------+
1 row in set (0.00 sec)

root@myserver [db]> drop table if exists mytable;
Query OK, 0 rows affected, 1 warning (0.00 sec)

root@myserver [db]> show warnings;
+-------+------+----------------------------+
| Level | Code | Message                    |
+-------+------+----------------------------+
| Note  | 1051 | Unknown table 'db.mytable' |
+-------+------+----------------------------+
1 row in set (0.00 sec)

Suggested fix:
This is confusing.

If the table is a view, AND the user has rights to see that view, then change the error message to something more descriptive like:

Table 'db.mytable' is actually a VIEW. Please use the DROP VIEW command to remove it.

This is a simple change yet one which many of us have bumped into.

[17 Jun 2014 12:24] MySQL Verification Team

Hello Simon,

Thank you for the feature request!

Thanks,
Umesh

[17 Jun 2014 12:25] MySQL Verification Team

// 5.6.17/19 and 5.7.5

mysql> create table t1(id int not null auto_increment primary key)engine=innodb;
Query OK, 0 rows affected (0.05 sec)

mysql> insert into t1 values();
Query OK, 1 row affected (0.00 sec)

mysql> insert into t1 values();
Query OK, 1 row affected (0.00 sec)

mysql> select * from t1;
+----+
| id |
+----+
|  1 |
|  2 |
+----+
2 rows in set (0.00 sec)

mysql> create view t2 as select * from t1;
Query OK, 0 rows affected (0.01 sec)

mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| docnode_tree   |
| t1             |
| t2             |
+----------------+
3 rows in set (0.00 sec)

mysql> drop table if exists t2;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> show warnings;
+-------+------+-------------------------+
| Level | Code | Message                 |
+-------+------+-------------------------+
| Note  | 1051 | Unknown table 'test.t2' |
+-------+------+-------------------------+
1 row in set (0.00 sec)

mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| docnode_tree   |
| t1             |
| t2             |
+----------------+
3 rows in set (0.00 sec)