Bug #34325 --add-drop-trigger option for mysqldump
Submitted: 5 Feb 2008 19:11 Modified: 9 Aug 2010 17:55
Reporter: Matthew Lord Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: mysqldump Command-line Client Severity:S4 (Feature request)
Version:all OS:Any
Assigned to: CPU Architecture:Any
Tags: Contribution, myqldump, triggers
Triage: Needs Triage: D4 (Minor)

[5 Feb 2008 19:11] Matthew Lord
Description:
It would be nice to have a --add-drop-trigger option for mysqldump so that you
could do things like mysqldump --triggers --no-data --no-create-info [--add-drop-trigger) so that you could get backups of triggers alone for later bulk
reloading etc.

How to repeat:
N/A

Suggested fix:
Add --add-drop-trigger which adds a DROP TRIGGER IF EXISTS line for each trigger
being dumped.
[14 May 2009 8:03] Steve Moore
Agree completely.  Working through DROP TRIGGER statements manually right now.
[15 Oct 2009 13:08] Rolf Martin-Hoster
I too have to munge the dumps to add drop trigger.
[7 Dec 2009 15:09] Seif Ben
Is there news about that ticket?
[8 Jun 2010 11:45] Hartmut Holzgraefe
Suggested fix:

=== modified file 'client/mysqldump.c'
--- client/mysqldump.c	2010-01-27 15:13:39 +0000
+++ client/mysqldump.c	2010-06-08 11:44:34 +0000
@@ -2754,6 +2754,9 @@
   if (opt_compact)
     fprintf(sql_file, "/*!50003 SET @OLD_SQL_MODE=@@SQL_MODE*/;\n");
 
+  if (opt_drop)
+    fprintf(sql_file, "/*!50003 DROP TRIGGER */ /*!50032 IF EXISTS */ /*!50003 %s */;\n", row[0]);
+
   fprintf(sql_file,
           "DELIMITER ;;\n"
           "/*!50003 SET SESSION SQL_MODE=\"%s\" */;;\n"
@@ -2828,6 +2831,9 @@
 
     switch_sql_mode(sql_file, ";", row[1]);
 
+    if (opt_drop)
+      fprintf(sql_file, "/*!50003 DROP TRIGGER */ /*!50032 IF EXISTS */ /*!50003 %s */;\n", row[0]);
+
     fprintf(sql_file,
             "DELIMITER ;;\n"
             "/*!50003 %s */;;\n"
[8 Jun 2010 11:49] Hartmut Holzgraefe
Above patch will actually make --add-drop-table (which is on by default) drop triggers, too. As this option already controls the dropping of e.g. procedures, functions and events extending it to drop triggers seems like the right thing to do.
[8 Jun 2010 11:49] Hartmut Holzgraefe
Above patch will actually make --add-drop-table (which is on by default) drop triggers, too. As this option already controls the dropping of e.g. procedures, functions and events extending it to drop triggers seems like the right thing to do.
[8 Jun 2010 11:54] Hartmut Holzgraefe
Ignore previous patch, it doesn't compile and it is probably
better to not have a DROP TRIGGER command at all for versions
prior to 5.0.32 that don't have DROP TRIGGER IF EXISTS ..

New patch:

=== modified file 'client/mysqldump.c'
--- client/mysqldump.c	2010-01-27 15:13:39 +0000
+++ client/mysqldump.c	2010-06-08 11:52:29 +0000
@@ -2754,6 +2754,9 @@
   if (opt_compact)
     fprintf(sql_file, "/*!50003 SET @OLD_SQL_MODE=@@SQL_MODE*/;\n");
 
+  if (opt_drop)
+    fprintf(sql_file, "/*!50032 DROP TRIGGER IF EXISTS %s */;\n", (*show_trigger_row)[0]);
+
   fprintf(sql_file,
           "DELIMITER ;;\n"
           "/*!50003 SET SESSION SQL_MODE=\"%s\" */;;\n"
@@ -2828,6 +2831,9 @@
 
     switch_sql_mode(sql_file, ";", row[1]);
 
+    if (opt_drop)
+      fprintf(sql_file, "/*!50032 DROP TRIGGER IF EXISTS %s */;\n", row[0]);
+
     fprintf(sql_file,
             "DELIMITER ;;\n"
             "/*!50003 %s */;;\n"
[8 Jun 2010 12:12] Hartmut Holzgraefe
attaching patch

Attachment: bug34325.patch (text/x-patch), 726 bytes.

[8 Jun 2010 12:16] Hartmut Holzgraefe
attaching patch

Attachment: bug34325.patch (text/x-patch), 726 bytes.

[9 Jun 2010 10:04] 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/110593

3052 Georgi Kodinov	2010-06-09
      Bug #34325 : --add-drop-trigger option for mysqldump
      
      Implemented the --add-drop-trigger option to prepend each 
      CREATE TRIGGER in the dump file with DROP TRIGGER.
      The option is off by default. Added a test case.
[1 Jul 2010 9:20] 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/112656

3052 Georgi Kodinov	2010-07-01
      Bug #34325 : --add-drop-trigger option for mysqldump
      
      Implemented the --add-drop-trigger option to prepend each 
      CREATE TRIGGER in the dump file with DROP TRIGGER.
      The option is off by default. Added a test case.
[5 Jul 2010 11:58] Georgi Kodinov
Pushed into mysql-next-mr-bugfixing.
[5 Jul 2010 13:05] 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/112888

3285 Georgi Kodinov	2010-07-05
      Bug #34325 : --add-drop-trigger option for mysqldump
            
      Implemented the --add-drop-trigger option to prepend each 
      CREATE TRIGGER in the dump file with DROP TRIGGER.
      The option is off by default. Added a test case.
[23 Jul 2010 12:32] Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20100723121929-90e9zemk3jkr2ocy) (version source revid:vasil.dimov@oracle.com-20100531152341-x2d4hma644icamh1) (pib:18)
[4 Aug 2010 8:06] Bugs System
Pushed into mysql-trunk 5.6.1-m4 (revid:alik@ibmvm-20100804080001-bny5271e65xo34ig) (version source revid:vasil.dimov@oracle.com-20100531152341-x2d4hma644icamh1) (merge vers: 5.5.5-m3) (pib:18)
[4 Aug 2010 8:22] Bugs System
Pushed into mysql-trunk 5.6.1-m4 (revid:alik@ibmvm-20100804081533-c1d3rbipo9e8rt1s) (version source revid:vasil.dimov@oracle.com-20100531152341-x2d4hma644icamh1) (merge vers: 5.5.5-m3) (pib:18)
[9 Aug 2010 17:55] Paul Dubois
Noted in 5.6.0 changelog.

mysqldump now has an --add-drop-trigger option which adds a DROP 
TRIGGER IF EXISTS statement before each dumped trigger definition.
[21 May 2012 22:55] Jared Griffith
Is this making it into a particular version?  Any status on this?  It's 2 years old now.
[22 May 2012 9:00] Georgi Kodinov
It's been present in the 5.6 development milestones since 5.6.0 : http://dev.mysql.com/doc/refman/5.6/en/news-5-6-0.html, search for the bug number.