| Bug #11551 | Asymmetric + undocumented behaviour of DROP VIEW and DROP TABLE | ||
|---|---|---|---|
| Submitted: | 24 Jun 2005 18:17 | Modified: | 2 Aug 2006 19:13 |
| Reporter: | Matthias Leich | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Views | Severity: | S3 (Non-critical) |
| Version: | 5.0 | OS: | |
| Assigned to: | Georgi Kodinov | CPU Architecture: | Any |
[6 Jul 2006 19:32]
Trudy Pelzer
Re-verified using 5.1.12-beta-debug. This is a bug because -- since views are just a form of table -- their behaviour should be no different from the behaviour exhibited by tables. Sometimes dropping multiple views does work, though. Consider the following. mysql> select @@version; +-------------------+ | @@version | +-------------------+ | 5.1.12-beta-debug | +-------------------+ mysql> drop table if exists t1,t2; mysql> create table t1 (col1 int); mysql> drop table t1,t2; ERROR 1051 (42S02): Unknown table 't2' mysql> show tables; Empty set (0.00 sec) mysql> create table t1 (col1 int); mysql> drop table t2,t1; ERROR 1051 (42S02): Unknown table 't2' mysql> show tables; Empty set (0.01 sec) Note the difference between the two examples: In the first case, the existing table (t1) comes first in the list of tables to drop. In the second case, the non-existent table (t2) comes first in the list. But, in both cases, table t1 is dropped and MySQL returns an error indicating that table t2 couldn't be dropped because it doesn't exist. This is the correct behaviour. Here's what happens with views: mysql> drop table if exists t1,t2; mysql> drop view if exists vt1,vt2; mysql> create table t1 (col1 int); mysql> create view vt1 as select * from t1; mysql> drop view vt1,vt2; ERROR 1051 (42S02): Unknown table 'tp.vt2' mysql> show tables; +--------------+ | Tables_in_tp | +--------------+ | t1 | +--------------+ 1 row in set (0.00 sec) -- This is the correct behaviour; matching what is done for tables. Although view vt2 doesn't exist, the DROP correctly drops vt1 and returns an error indicating that view vt2 can't be dropped because it doesn't exist. However, when I turn the DROP arguments around: mysql> create view vt1 as select * from t1; mysql> drop view vt2,vt1; ERROR 1051 (42S02): Unknown table 'tp.vt2' mysql> show tables; +--------------+ | Tables_in_tp | +--------------+ | t1 | | vt1 | +--------------+ 2 rows in set (0.00 sec) -- This shows the incorrect behaviour; it doesn't match the equivalent example for tables. That is, when the first argument to DROP VIEW is the view that doesn't exist, MySQL does correctly return an error indicating that view vt2 can't be dropped because it doesn't exist -- but does not drop view vt1, as it should. Suggested fix: Make DROP VIEW behave the same as DROP TABLE. When DROP contains multiple arguments, one or more of which do not exist, DROP the views that do exist and return ERROR 1051 (42S02): Unknown table ... for the non-existent views.
[28 Jul 2006 12: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/commits/9704
[31 Jul 2006 17:56]
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/commits/9844 ChangeSet@1.2222, 2006-07-31 20:56:06+03:00, gkodinov@macbook.gmz +3 -0 Bug #11551: Asymmetric + undocumented behaviour of DROP VIEW and DROP TABLE made DROP VIEW to continue on error and report an aggregated error at its end. This makes it similar to DROP TABLE.
[2 Aug 2006 18:46]
Evgeny Potemkin
Fixed in 5.0.25, 5.1.12
[2 Aug 2006 19:13]
Paul DuBois
Noted in 5.0.25, 5.1.12 changelogs.
Description: Let's start with an example to show the strange observations: DROP TABLE IF EXISTS t1, t2; DROP VIEW IF EXISTS v1, v2; CREATE TABLE t1 (f1 BIGINT); CREATE VIEW v1 AS SELECT * FROM t1; DROP VIEW v2, v1; ERROR 42S02: Unknown table 'test.v2' SELECT * FROM v1; f1 That means if the drop of the first view (v2) fails, the following view (v1) will be not dropped. DROP TABLE t2, t1; ERROR 42S02: Unknown table 't2' SELECT * FROM t1; ERROR 42S02: Table 'test.t1' doesn't exist That means if the drop of the first table (t2) fails, the following table (t1) will be dropped. BTW: The manual says DROP TABLE gets an implicit COMMIT. So we have here a DROP table, which earns an error message for the first and a funny additional COMMIT for the second table. I assume that is confusing for customers and they do not expect this behaviour. 1. It is not documented, what will happen, if the drop of one of several views or tables fails. 2. There is an ugly asymmetry between the DROP VIEW and DROP TABLE behaviour, when the drop of one object fails. SET sql_mode = 'traditional'; DROP TABLE IF EXISTS t1, t2; DROP VIEW IF EXISTS v1, v2; CREATE TABLE t1 (f1 BIGINT); DROP TABLE t2, t1; ERROR 42S02: Unknown table 't2' SELECT * FROM t1; ERROR 42S02: Table 'test.t1' doesn't exist We have here the same behaviour like above and this is very questionable, because we are now in "traditional" mode. The behaviour on tables violates the all or nothing principle, which is usual valid for single statements in "traditional" SQL systems. The perfect behaviour would be, that nothing is dropped if the action for one element fails. I am not able to decide if most of the things above are bugs or not, because the manual does not contain a clear and detailed description of the intended behaviour. Only the missing documentation of this behaviour is a clear bug. My environment: - Intel PC with Linux(SuSE 9.3) - MySQL compiled from source Version 5.0 ChangeSet@1.1972, 2005-06-21 How to repeat: Please execute the statements above.