bk commit into 5.0 tree (patg:1.1936) BUG#12597

[Patrick Galbraith]

Below is the list of changes that have just been committed into a local
5.0 repository of patg. When patg does a push these changes will
be propagated to the main repository and, within 24 hours after the
push, to the public repository.
For information on how to access the public repository
see http://dev.mysql.com/doc/mysql/en/installing-source-tree.html

ChangeSet
  1.1936 05/09/09 00:03:07 patg@stripped +3 -0
  BUG #12597 This is a complete patch (2nd) containing fixes per Serg's review

  mysql-test/t/mysqldump.test
    1.56 05/09/09 00:02:52 patg@stripped +35 -0
    BUG #12597
    - New tests to test bug #12597

  mysql-test/r/mysqldump.result
    1.62 05/09/09 00:02:51 patg@stripped +54 -23
    Tests to test BUG #12597

  client/mysqldump.c
    1.196 05/09/09 00:02:51 patg@stripped +68 -35
    BUG #12597
    - Dump triggers after data dump of each table
    - created dump_triggers_for_table function
    - added space between "MODE" and "*/"

# This is a BitKeeper patch.  What follows are the unified diffs for the
# set of deltas contained in the patch.  The rest of the patch, the part
# that BitKeeper cares about, is below these diffs.
# User:	patg
# Host:	radha.local
# Root:	/Users/patg/mysql-build/mysql-5.0.bug12597

--- 1.195/client/mysqldump.c	2005-08-26 14:45:19 +02:00
+++ 1.196/client/mysqldump.c	2005-09-09 00:02:51 +02:00
@@ -1330,41 +1330,6 @@
       fprintf(sql_file, "%s;\n", row[1]);
       check_io(sql_file);
       mysql_free_result(tableRes);
-      if (opt_dump_triggers &&
-          mysql_get_server_version(sock) >= 50009)
-      {
-        my_snprintf(query_buff, sizeof(query_buff),
-                    "SHOW TRIGGERS LIKE %s",
-                    quote_for_like(table, name_buff));
-
-
-        if (mysql_query_with_error_report(sock, &tableRes, query_buff))
-        {
-          if (path)
-            my_fclose(sql_file, MYF(MY_WME));
-          safe_exit(EX_MYSQLERR);
-          DBUG_RETURN(0);
-        }
-        if (mysql_num_rows(tableRes))
-          fprintf(sql_file, "\n/*!50003 SET @OLD_SQL_MODE=@@SQL_MODE*/;\n\
-DELIMITER //;\n");
-        while ((row=mysql_fetch_row(tableRes)))
-        {
-          fprintf(sql_file, "/*!50003 SET SESSION SQL_MODE=\"%s\"*/ //\n\
-/*!50003 CREATE TRIGGER %s %s %s ON %s FOR EACH ROW%s*/ //\n\n",
-                  row[6], /* sql_mode */
-                  quote_name(row[0], name_buff, 0), /* Trigger */
-                  row[4], /* Timing */
-                  row[1], /* Event */
-                  result_table,
-                  row[3] /* Statement */);
-        }
-        if (mysql_num_rows(tableRes))
-          fprintf(sql_file,
-                  "DELIMITER ;//\n\
-/*!50003 SET SESSION SQL_MODE=@OLD_SQL_MODE*/;");
-        mysql_free_result(tableRes);
-      }
     }
     my_snprintf(query_buff, sizeof(query_buff), "show fields from %s",
 		result_table);
@@ -1657,6 +1622,68 @@
 } /* get_table_structure */
 
 
+/*
+
+  dump_triggers_for_table
+
+  Dumps the triggers given a table/db name. This should be called after
+  the tables have been dumped in case a trigger depends on the existence
+  of a table
+
+  INPUT
+    char * tablename and db name
+  RETURNS
+   0 Failure
+   1 Succes
+
+*/
+
+static void dump_triggers_for_table (char *table, char *db)
+{
+  MYSQL_RES  *result;
+  MYSQL_ROW  row;
+  char	     *result_table;
+  char	     name_buff[NAME_LEN+3], table_buff[NAME_LEN*2+3];
+  char       query_buff[512];
+  FILE       *sql_file = md_result_file;
+
+  DBUG_ENTER("dump_triggers_for_table");
+  DBUG_PRINT("enter", ("db: %s, table: %s", db, table));
+  result_table=     quote_name(table, table_buff, 1);
+
+  my_snprintf(query_buff, sizeof(query_buff),
+              "SHOW TRIGGERS LIKE %s",
+              quote_for_like(table, name_buff));
+
+  if (mysql_query_with_error_report(sock, &result, query_buff))
+  {
+    if (path)
+      my_fclose(sql_file, MYF(MY_WME));
+    safe_exit(EX_MYSQLERR);
+    DBUG_VOID_RETURN;
+  }
+  if (mysql_num_rows(result))
+    fprintf(sql_file, "\n/*!50003 SET @OLD_SQL_MODE=@@SQL_MODE*/;\n\
+DELIMITER //;\n");
+  while ((row=mysql_fetch_row(result)))
+  {
+    fprintf(sql_file, "/*!50003 SET SESSION SQL_MODE=\"%s\" */ //\n\
+/*!50003 CREATE TRIGGER %s %s %s ON %s FOR EACH ROW%s */ //\n\n",
+            row[6], /* sql_mode */
+            quote_name(row[0], name_buff, 0), /* Trigger */
+            row[4], /* Timing */
+            row[1], /* Event */
+            result_table,
+            row[3] /* Statement */);
+  }
+  if (mysql_num_rows(result))
+    fprintf(sql_file,
+            "DELIMITER ;//\n\
+/*!50003 SET SESSION SQL_MODE=@OLD_SQL_MODE */;");
+  mysql_free_result(result);
+  DBUG_VOID_RETURN;
+}
+
 static char *add_load_option(char *ptr,const char *object,
 			     const char *statement)
 {
@@ -2376,6 +2403,9 @@
       dump_table(numrows,table);
       my_free(order_by, MYF(MY_ALLOW_ZERO_PTR));
       order_by= 0;
+      if (opt_dump_triggers && ! opt_xml &&
+          mysql_get_server_version(sock) >= 50009)
+        dump_triggers_for_table(table, database);
     }
   }
   if (opt_xml)
@@ -2569,6 +2599,9 @@
     DBUG_PRINT("info",("Dumping table %s", table_name));
     numrows= get_table_structure(table_name, db);
     dump_table(numrows, table_name);
+    if (opt_dump_triggers &&
+        mysql_get_server_version(sock) >= 50009)
+      dump_triggers_for_table(table_name, db);
   }
 
   /* Dump each selected view */

--- 1.61/mysql-test/r/mysqldump.result	2005-07-30 08:24:47 +02:00
+++ 1.62/mysql-test/r/mysqldump.result	2005-09-09 00:02:51 +02:00
@@ -1738,61 +1738,61 @@
   `b` bigint(20) default NULL
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
 
+
+/*!40000 ALTER TABLE `t1` DISABLE KEYS */;
+LOCK TABLES `t1` WRITE;
+INSERT INTO `t1` VALUES (1,NULL),(2,NULL),(4,NULL),(11,NULL);
+UNLOCK TABLES;
+/*!40000 ALTER TABLE `t1` ENABLE KEYS */;
+
 /*!50003 SET @OLD_SQL_MODE=@@SQL_MODE*/;
 DELIMITER //;
-/*!50003 SET SESSION SQL_MODE=""*/ //
+/*!50003 SET SESSION SQL_MODE="" */ //
 /*!50003 CREATE TRIGGER `trg1` BEFORE INSERT ON `t1` FOR EACH ROW
 begin
 if new.a > 10 then
 set new.a := 10;
 set new.a := 11;
 end if;
-end*/ //
+end */ //
 
-/*!50003 SET SESSION SQL_MODE=""*/ //
+/*!50003 SET SESSION SQL_MODE="" */ //
 /*!50003 CREATE TRIGGER `trg2` BEFORE UPDATE ON `t1` FOR EACH ROW begin
 if old.a % 2 = 0 then set new.b := 12; end if;
-end*/ //
+end */ //
 
-/*!50003 SET SESSION
SQL_MODE="STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER"*/
//
+/*!50003 SET SESSION
SQL_MODE="STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER"
*/ //
 /*!50003 CREATE TRIGGER `trg3` AFTER UPDATE ON `t1` FOR EACH ROW
 begin
 if new.a = -1 then
 set @fired:= "Yes";
 end if;
-end*/ //
+end */ //
 
 DELIMITER ;//
-/*!50003 SET SESSION SQL_MODE=@OLD_SQL_MODE*/;
-
-/*!40000 ALTER TABLE `t1` DISABLE KEYS */;
-LOCK TABLES `t1` WRITE;
-INSERT INTO `t1` VALUES (1,NULL),(2,NULL),(4,NULL),(11,NULL);
-UNLOCK TABLES;
-/*!40000 ALTER TABLE `t1` ENABLE KEYS */;
-DROP TABLE IF EXISTS `t2`;
+/*!50003 SET SESSION SQL_MODE=@OLD_SQL_MODE */;DROP TABLE IF EXISTS `t2`;
 CREATE TABLE `t2` (
   `a` int(11) default NULL
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
 
+
+/*!40000 ALTER TABLE `t2` DISABLE KEYS */;
+LOCK TABLES `t2` WRITE;
+UNLOCK TABLES;
+/*!40000 ALTER TABLE `t2` ENABLE KEYS */;
+
 /*!50003 SET @OLD_SQL_MODE=@@SQL_MODE*/;
 DELIMITER //;
-/*!50003 SET SESSION
SQL_MODE="STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER"*/
//
+/*!50003 SET SESSION
SQL_MODE="STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER"
*/ //
 /*!50003 CREATE TRIGGER `trg4` BEFORE INSERT ON `t2` FOR EACH ROW
 begin
 if new.a > 10 then
 set @fired:= "No";
 end if;
-end*/ //
+end */ //
 
 DELIMITER ;//
-/*!50003 SET SESSION SQL_MODE=@OLD_SQL_MODE*/;
-
-/*!40000 ALTER TABLE `t2` DISABLE KEYS */;
-LOCK TABLES `t2` WRITE;
-UNLOCK TABLES;
-/*!40000 ALTER TABLE `t2` ENABLE KEYS */;
-
+/*!50003 SET SESSION SQL_MODE=@OLD_SQL_MODE */;
 /*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
 /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
 /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
@@ -1875,3 +1875,34 @@
 end if;
 end	BEFORE	#	STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER
 DROP TABLE t1, t2;
+DROP TABLE IF EXISTS `test1`;
+Warnings:
+Note	1051	Unknown table 'test1'
+CREATE TABLE `test1` (
+`a1` int(11) default NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1;
+DROP TABLE IF EXISTS `test2`;
+Warnings:
+Note	1051	Unknown table 'test2'
+CREATE TABLE `test2` (
+`a2` int(11) default NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1;
+CREATE TRIGGER `testref` BEFORE INSERT ON `test1` FOR EACH ROW BEGIN
+INSERT INTO test2 SET a2 = NEW.a1; END  //
+INSERT INTO `test1` VALUES (1);
+SELECT * FROM `test2`;
+a2
+1
+SHOW TRIGGERS;
+Trigger	Event	Table	Statement	Timing	Created	sql_mode
+testref	INSERT	test1	 BEGIN
+INSERT INTO test2 SET a2 = NEW.a1; END	BEFORE	NULL	
+SELECT * FROM `test1`;
+a1
+1
+SELECT * FROM `test2`;
+a2
+1
+DROP TRIGGER testref;
+DROP TABLE test1;
+DROP TABLE test2;

--- 1.55/mysql-test/t/mysqldump.test	2005-07-29 22:40:00 +02:00
+++ 1.56/mysql-test/t/mysqldump.test	2005-09-09 00:02:52 +02:00
@@ -762,3 +762,38 @@
 --replace_column 6 #
 show triggers;
 DROP TABLE t1, t2;
+
+# Test of fix to BUG 12597
+DROP TABLE IF EXISTS `test1`;
+CREATE TABLE `test1` (
+  `a1` int(11) default NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1;
+
+DROP TABLE IF EXISTS `test2`;
+CREATE TABLE `test2` (
+  `a2` int(11) default NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1;
+
+DELIMITER //;
+CREATE TRIGGER `testref` BEFORE INSERT ON `test1` FOR EACH ROW BEGIN
+INSERT INTO test2 SET a2 = NEW.a1; END  //
+DELIMITER ;//
+
+INSERT INTO `test1` VALUES (1);
+SELECT * FROM `test2`;
+
+# dump
+--exec $MYSQL_DUMP --skip-comments --databases test > var/tmp/mysqldump.sql
+
+#DROP TRIGGER testref;
+#DROP TABLE test1;
+#DROP TABLE test2;
+# restore
+--exec $MYSQL test < var/tmp/mysqldump.sql
+SHOW TRIGGERS;
+SELECT * FROM `test1`;
+SELECT * FROM `test2`;
+
+DROP TRIGGER testref;
+DROP TABLE test1;
+DROP TABLE test2;