MySQL Bugs: #34325: --add-drop-trigger option for mysqldump

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

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.

Agree completely.  Working through DROP TRIGGER statements manually right now.

I too have to munge the dumps to add drop trigger.

Is there news about that ticket?

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"

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.

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.

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"

attaching patch

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

attaching patch

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

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.

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.

Pushed into mysql-next-mr-bugfixing.

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.

Pushed into mysql-next-mr (revid:alik@sun.com-20100723121929-90e9zemk3jkr2ocy) (version source revid:vasil.dimov@oracle.com-20100531152341-x2d4hma644icamh1) (pib:18)

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)

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)

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.

Is this making it into a particular version?  Any status on this?  It's 2 years old now.

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.