bk commit into 5.0 tree (msvensson:1.1877) BUG#10431

[msvensson]

Below is the list of changes that have just been committed into a local
5.0 repository of msvensson. When msvensson 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.1877 05/07/15 12:32:26 msvensson@neptunus.(none) +4 -0
  Bug#10431: mysqldump does not dump triggers of the table
   - Add dump of triggers, disabled by default
   - Add test cases 

  mysql-test/t/mysqldump.test
    1.52 05/07/15 12:32:11 msvensson@neptunus.(none) +50 -0
    test for dumping triggers

  mysql-test/r/mysqldump.result
    1.57 05/07/15 12:32:11 msvensson@neptunus.(none) +226 -0
    Test dumping triggers

  client/mysqldump.c
    1.186 05/07/15 12:32:11 msvensson@neptunus.(none) +41 -1
    Add functionality for dumping triggers
    To dump triggers is on by default but can be disabled with --skip-triggers

  client/client_priv.h
    1.38 05/07/15 12:32:11 msvensson@neptunus.(none) +2 -1
    Add OPT_DUMP_TRIGGERS

# 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:	msvensson
# Host:	neptunus.(none)
# Root:	/home/msvensson/mysql/bug10431

--- 1.185/client/mysqldump.c	2005-07-03 13:17:45 +02:00
+++ 1.186/client/mysqldump.c	2005-07-15 12:32:11 +02:00
@@ -86,7 +86,8 @@
 		opt_delete_master_logs=0, tty_password=0,
 		opt_single_transaction=0, opt_comments= 0, opt_compact= 0,
 		opt_hex_blob=0, opt_order_by_primary=0, opt_ignore=0,
-                opt_complete_insert= 0, opt_drop_database= 0;
+                opt_complete_insert= 0, opt_drop_database= 0,
+                opt_dump_triggers= 0;
 static ulong opt_max_allowed_packet, opt_net_buffer_length;
 static MYSQL mysql_connection,*sock=0;
 static my_bool insert_pat_inited=0;
@@ -371,6 +372,9 @@
    (gptr*) &path, (gptr*) &path, 0, GET_STR, REQUIRED_ARG, 0, 0, 0, 0, 0, 0},
   {"tables", OPT_TABLES, "Overrides option --databases (-B).",
    0, 0, 0, GET_NO_ARG, NO_ARG, 0, 0, 0, 0, 0, 0},
+  {"triggers", OPT_DUMP_TRIGGERS, "Dump triggers for each table",
+   (gptr*) &opt_dump_triggers, (gptr*) &opt_dump_triggers, 0, GET_BOOL,
+   NO_ARG, 0, 0, 0, 0, 0, 0},
 #ifndef DONT_ALLOW_USER_CHANGE
   {"user", 'u', "User for login if not current user.",
    (gptr*) &current_user, (gptr*) &current_user, 0, GET_STR, REQUIRED_ARG,
@@ -1315,6 +1319,41 @@
       fprintf(sql_file, "%s;\n", row[1]);
       check_io(sql_file);
       mysql_free_result(tableRes);
+
+      /* Start of dump triggers */
+      if (opt_dump_triggers &&
+          mysql_get_server_version(sock) >= 50009 &&
+          !opt_compatible_mode)
+      {
+        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, "\nDELIMITER //\n");
+        while ((row=mysql_fetch_row(tableRes)))
+        {
+          fprintf(sql_file, "CREATE TRIGGER %s %s %s ON %s\n"
+                  "FOR EACH ROW%s//\n\n",
+                  quote_name(row[0], name_buff, 0),
+                  row[4],
+                  row[1],
+                  result_table,
+                  row[3]);
+        }
+        if (mysql_num_rows(tableRes))
+          fprintf(sql_file, "DELIMITER ;");
+        mysql_free_result(tableRes);
+      }
+      /* End of dump triggers */
+
     }
     my_snprintf(query_buff, sizeof(query_buff), "show fields from %s",
 		result_table);
@@ -1591,6 +1630,7 @@
       check_io(sql_file);
     }
   }
+
   if (opt_complete_insert)
   {
     dynstr_append_mem(&insert_pat, ") VALUES ", 9);

--- 1.37/client/client_priv.h	2005-05-26 12:58:50 +02:00
+++ 1.38/client/client_priv.h	2005-07-15 12:32:11 +02:00
@@ -49,5 +49,6 @@
 #ifdef HAVE_NDBCLUSTER_DB
   OPT_NDBCLUSTER, OPT_NDB_CONNECTSTRING,
 #endif
-  OPT_IGNORE_TABLE,OPT_INSERT_IGNORE,OPT_SHOW_WARNINGS,OPT_DROP_DATABASE
+  OPT_IGNORE_TABLE,OPT_INSERT_IGNORE,OPT_SHOW_WARNINGS,OPT_DROP_DATABASE,
+  OPT_DUMP_TRIGGERS
 };

--- 1.56/mysql-test/r/mysqldump.result	2005-06-30 20:44:26 +02:00
+++ 1.57/mysql-test/r/mysqldump.result	2005-07-15 12:32:11 +02:00
@@ -1673,3 +1673,229 @@
 3	6	three
 drop view v1, v2, v3;
 drop table t1;
+CREATE TABLE t1 (a int, b bigint default NULL);
+CREATE TABLE t2 (a int);
+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|
+create trigger trg2 before update on t1 for each row begin
+if old.a % 2 = 0 then set new.b := 12; end if;
+end|
+create trigger trg3 after update on t1 for each row
+begin
+if new.a = -1 then
+set @fired:= "Yes";
+end if;
+end|
+create trigger trg4 before insert on t2 for each row
+begin
+if new.a > 10 then
+set @fired:= "No";
+end if;
+end|
+show triggers like "t1";
+Trigger	Event	Table	Statement	Timing	Created
+trg1	INSERT	t1	
+begin
+if new.a > 10 then
+set new.a := 10;
+set new.a := 11;
+end if;
+end	BEFORE	0000-00-00 00:00:00
+trg2	UPDATE	t1	 begin
+if old.a % 2 = 0 then set new.b := 12; end if;
+end	BEFORE	0000-00-00 00:00:00
+trg3	UPDATE	t1	
+begin
+if new.a = -1 then
+set @fired:= "Yes";
+end if;
+end	AFTER	0000-00-00 00:00:00
+INSERT INTO t1 (a) VALUES (1),(2),(3),(22);
+update t1 set a = 4 where a=3;
+
+/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
+/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
+/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
+/*!40101 SET NAMES utf8 */;
+/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
+/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
+/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
+/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
+
+CREATE DATABASE /*!32312 IF NOT EXISTS*/ `test` /*!40100 DEFAULT CHARACTER SET latin1 */;
+
+USE `test`;
+DROP TABLE IF EXISTS `t1`;
+CREATE TABLE `t1` (
+  `a` int(11) default NULL,
+  `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 */;
+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 */;
+
+/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
+/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
+/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
+/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
+/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
+/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
+/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
+
+
+/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
+/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
+/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
+/*!40101 SET NAMES utf8 */;
+/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
+/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
+/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
+/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
+
+CREATE DATABASE /*!32312 IF NOT EXISTS*/ `test` /*!40100 DEFAULT CHARACTER SET latin1 */;
+
+USE `test`;
+DROP TABLE IF EXISTS `t1`;
+CREATE TABLE `t1` (
+  `a` int(11) default NULL,
+  `b` bigint(20) default NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1;
+
+DELIMITER //
+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//
+
+CREATE TRIGGER `trg2` BEFORE UPDATE ON `t1`
+FOR EACH ROW begin
+if old.a % 2 = 0 then set new.b := 12; end if;
+end//
+
+CREATE TRIGGER `trg3` AFTER UPDATE ON `t1`
+FOR EACH ROW
+begin
+if new.a = -1 then
+set @fired:= "Yes";
+end if;
+end//
+
+DELIMITER ;
+
+/*!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`;
+CREATE TABLE `t2` (
+  `a` int(11) default NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1;
+
+DELIMITER //
+CREATE TRIGGER `trg4` BEFORE INSERT ON `t2`
+FOR EACH ROW
+begin
+if new.a > 10 then
+set @fired:= "No";
+end if;
+end//
+
+DELIMITER ;
+
+/*!40000 ALTER TABLE `t2` DISABLE KEYS */;
+LOCK TABLES `t2` WRITE;
+UNLOCK TABLES;
+/*!40000 ALTER TABLE `t2` ENABLE KEYS */;
+
+/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
+/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
+/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
+/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
+/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
+/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
+/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
+
+/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
+/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
+/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO,ANSI' */;
+/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
+
+CREATE DATABASE /*!32312 IF NOT EXISTS*/ "test" /*!40100 DEFAULT CHARACTER SET latin1 */;
+
+USE "test";
+DROP TABLE IF EXISTS "t1";
+CREATE TABLE "t1" (
+  "a" int(11) default NULL,
+  "b" bigint(20) default NULL
+);
+
+
+/*!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";
+CREATE TABLE "t2" (
+  "a" int(11) default NULL
+);
+
+
+/*!40000 ALTER TABLE "t2" DISABLE KEYS */;
+LOCK TABLES "t2" WRITE;
+UNLOCK TABLES;
+/*!40000 ALTER TABLE "t2" ENABLE KEYS */;
+
+/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
+/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
+/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
+/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
+
+drop table t1;
+show tables;
+Tables_in_test
+t1
+t2
+show triggers like "t1";
+Trigger	Event	Table	Statement	Timing	Created
+trg1	INSERT	t1	
+begin
+if new.a > 10 then
+set new.a := 10;
+set new.a := 11;
+end if;
+end	BEFORE	0000-00-00 00:00:00
+trg2	UPDATE	t1	 begin
+if old.a % 2 = 0 then set new.b := 12; end if;
+end	BEFORE	0000-00-00 00:00:00
+trg3	UPDATE	t1	
+begin
+if new.a = -1 then
+set @fired:= "Yes";
+end if;
+end	AFTER	0000-00-00 00:00:00
+DROP TABLE t1, t2;

--- 1.51/mysql-test/t/mysqldump.test	2005-06-30 20:44:26 +02:00
+++ 1.52/mysql-test/t/mysqldump.test	2005-07-15 12:32:11 +02:00
@@ -710,3 +710,53 @@
 
 drop view v1, v2, v3;
 drop table t1;
+#
+# Test for dumping triggers
+#
+
+CREATE TABLE t1 (a int, b bigint default NULL);
+CREATE TABLE t2 (a int);
+delimiter |;
+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|
+create trigger trg2 before update on t1 for each row begin
+  if old.a % 2 = 0 then set new.b := 12; end if;
+end|
+create trigger trg3 after update on t1 for each row
+begin
+  if new.a = -1 then
+    set @fired:= "Yes";
+  end if;
+end|
+create trigger trg4 before insert on t2 for each row
+begin
+  if new.a > 10 then
+    set @fired:= "No";
+  end if;
+end|
+delimiter ;|
+--replace_column 6 '0000-00-00 00:00:00'
+show triggers like "t1";
+INSERT INTO t1 (a) VALUES (1),(2),(3),(22);
+update t1 set a = 4 where a=3;
+# Triggers should not be dumped by default
+--exec $MYSQL_DUMP --skip-comments --databases test
+# Dumping triggers
+--exec $MYSQL_DUMP --skip-comments --databases --triggers test
+# Don't dump triggers if compatible mode
+--exec $MYSQL_DUMP --skip-comments --databases --triggers --compatible=ansi test
+# Dump and reload...
+--exec $MYSQL_DUMP --skip-comments --databases --triggers test > var/tmp/mysqldump.sql
+drop table t1;
+--exec $MYSQL test < var/tmp/mysqldump.sql
+# Check that tables have been reloaded
+show tables;
+--replace_column 6 '0000-00-00 00:00:00'
+show triggers like "t1";
+DROP TABLE t1, t2;
+