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:
None 
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
Description:
DROP TRIGGER needs an IF EXISTS

How to repeat:
DROP TRIGGER needs an IF EXISTS

Suggested fix:
DROP TRIGGER needs an IF EXISTS
[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.