| Bug #23703 | DROP TRIGGER needs an IF EXISTS | ||
|---|---|---|---|
| Submitted: | 27 Oct 2006 1:20 | Modified: | 30 Nov 2006 19:33 |
| Reporter: | Hunter Peress | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server | Severity: | S3 (Non-critical) |
| Version: | 5.0, 5.1 | OS: | Linux (Linux) |
| Assigned to: | Marc ALFF | CPU Architecture: | Any |
[27 Oct 2006 1:20]
Hunter Peress
[27 Oct 2006 6:03]
Valeriy Kravchuk
Duplicate of bug #18161. Work in progress already.
[7 Nov 2006 0:34]
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/14916 ChangeSet@1.2341, 2006-11-06 17:34:04-07:00, malff@weblab.(none) +6 -0 Bug#23703 (DROP TRIGGER needs an IF EXISTS) This change set implements the DROP TRIGGER IF EXISTS functionality. This fix is considered a bug and not a feature, because without it, there is no known method to write a database creation script that can create a trigger without failing, when executed on a database that may or may not contain already a trigger of the same name. Implementing this functionality closes an orthogonality gap between triggers and stored procedures / stored functions (which do support the DROP IF EXISTS syntax). The patch itself is based on code contributed by the MySQL community, under the terms of the Contributor License Agreement (See Bug 18161).
[10 Nov 2006 12:29]
Konstantin Osipov
Sent a code review by email.
[13 Nov 2006 22:41]
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/15276 ChangeSet@1.2303, 2006-11-13 15:40:22-07:00, malff@weblab.(none) +6 -0 Bug#23703 (DROP TRIGGER needs an IF EXISTS) This change set implements the DROP TRIGGER IF EXISTS functionality. This fix is considered a bug and not a feature, because without it, there is no known method to write a database creation script that can create a trigger without failing, when executed on a database that may or may not contain already a trigger of the same name. Implementing this functionality closes an orthogonality gap between triggers and stored procedures / stored functions (which do support the DROP IF EXISTS syntax). In sql_trigger.cc, in mysql_create_or_drop_trigger, the code has been reordered to: - perform the tests that do not depend on the file system (access()), - get the locks (wait_if_global_read_lock, LOCK_open) - call access() - perform the operation - write to the binlog - unlock (LOCK_open, start_waiting_global_read_lock) This is to ensure that all the code that depends on the presence of the trigger file is executed in the same critical section, and prevents race conditions similar to the case fixed by Bug 14262 : - thread 1 executes DROP TRIGGER IF EXISTS, access() returns a failure - thread 2 executes CREATE TRIGGER - thread 2 logs CREATE TRIGGER - thread 1 logs DROP TRIGGER IF EXISTS The patch itself is based on code contributed by the MySQL community, under the terms of the Contributor License Agreement (See Bug 18161).
[17 Nov 2006 20:19]
Stephen Gornick
Sure would be nice if the same thing were done for DROP INDEX: http://bugs.mysql.com/bug.php?id=23543
[30 Nov 2006 1:37]
Konstantin Osipov
Pushed into 5.0.32 and 5.1.13
[30 Nov 2006 19:33]
Paul DuBois
Noted in DROP TRIGGER section and in 5.0.32, 5.1.14 changelogs.
