bk commit into 5.0 tree (patg:1.1883)

[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.1883 05/07/22 04:11:45 patg@stripped +3 -0
  Applied and tested this patch successfully (bug 10431)  to a clean 
  tree on brian's amd 64 box (which was already approved). 
  
  [patg@zim mysql-5.0]$ bk changes
  ChangeSet@stripped, 2005-07-21 18:43:55+05:00, gluh@stripped
    merge fix
  
  Was the changeset level I applied this patch to.
  
  Master shutdown finished
  Slave shutdown finished
  All 326 tests were successful.
  
  [patg@zim mysql-5.0]$ 

  mysql-test/t/mysqldump.test
    1.52 05/07/22 04:11:21 patg@stripped +45 -0
    fresh application of Magnus Svensson's patch for bug 10431

  mysql-test/r/mysqldump.result
    1.58 05/07/22 04:11:20 patg@stripped +172 -0
    Fresh application of Magnus Svensson's patch for bug 10431

  client/mysqldump.c
    1.191 05/07/22 04:11:20 patg@stripped +36 -1
    This is a fresh application of Magnus Svensson's patch for bug 10431

# 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.msvensson

--- 1.190/client/mysqldump.c	2005-07-21 07:29:52 +02:00
+++ 1.191/client/mysqldump.c	2005-07-22 04:11:20 +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", '/0', "Dump triggers for each dumped table",
+     (gptr*) &opt_dump_triggers, (gptr*) &opt_dump_triggers, 0, GET_BOOL,
+     NO_ARG, 1, 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,37 @@
       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, "\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);
+      }
     }
     my_snprintf(query_buff, sizeof(query_buff), "show fields from %s",
 		result_table);

--- 1.57/mysql-test/r/mysqldump.result	2005-07-06 03:23:51 +02:00
+++ 1.58/mysql-test/r/mysqldump.result	2005-07-22 04:11:20 +02:00
@@ -1673,3 +1673,175 @@
 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;
+
+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 */;
+
+
+/*!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 */;
+
+drop table t1;
+show tables;
+Tables_in_test
+t1
+t2
+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-22 04:11:21 +02:00
@@ -710,3 +710,48 @@
 
 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 be dumped by default
+--exec $MYSQL_DUMP --skip-comments --databases test
+# Skip dumping triggers
+--exec $MYSQL_DUMP --skip-comments --databases --skip-triggers test
+# Dump and reload...
+--exec $MYSQL_DUMP --skip-comments --databases test > var/tmp/mysqldump.sql
+drop table t1;
+--exec $MYSQL test < var/tmp/mysqldump.sql
+# Check that tables have been reloaded
+show tables;
+DROP TABLE t1, t2;