Bug #5859 DROP TABLE does not drop triggers
Submitted: 2 Oct 2004 15:21 Modified: 4 Apr 2005 23:56
Reporter: Dean Ellis Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:5.0.2 (1.1629) OS:
Assigned to: Dmitry Lenev CPU Architecture:Any

[2 Oct 2004 15:21] Dean Ellis
Description:
DROP TABLE does not drop triggers defined for the table.

How to repeat:
USE test;
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 ( a int, b int );
CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW SET new.a=new.b;
INSERT INTO t1 VALUES (1,2);
DROP TABLE t1;
CREATE TABLE t1 ( a int );
INSERT INTO t1 VALUES (1);
DROP TABLE t1;

Suggested fix:
Drop trigger definitions when table is dropped.
[27 Oct 2004 15:59] Paul Dubois
DROP DATABASE also fails to drop triggers, which causes
DROP DATABASE itself to fail with this error:

ERROR 1010 (HY000): Error dropping database
(can't rmdir './test/', errno: 17)

I suspect this is because sql_db.cc does not include ".TRG" in
its list of known extensions:

const char *known_exts[]=
{".ISM",".ISD",".ISM",".MRG",".MYI",".MYD",".db", ".ibd", NullS};

(By the way, it's also odd that ".ISM" is listed twice here? That's
true all the way back through 3.23...)
[3 Apr 2005 14:48] Dmitry Lenev
Fixed in 5.0.4
[4 Apr 2005 23:56] Paul Dubois
Noted in 5.0.4 changelog.