summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat
-rw-r--r--client/mysqldump.c103
-rw-r--r--mysql-test/r/mysqldump.result77
-rw-r--r--mysql-test/t/mysqldump.test35
3 files changed, 157 insertions, 58 deletions
diff --git a/client/mysqldump.c b/client/mysqldump.c
index 7c81f6909c1..57a58c1417f 100644
--- a/client/mysqldump.c
+++ b/client/mysqldump.c
@@ -1330,41 +1330,6 @@ static uint get_table_structure(char *table, char *db)
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 @@ continue_xml:
} /* 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 @@ static int dump_all_tables_in_db(char *database)
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 @@ static int dump_selected_tables(char *db, char **table_names, int tables)
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 */
diff --git a/mysql-test/r/mysqldump.result b/mysql-test/r/mysqldump.result
index 917724580cf..6a4d847c5b6 100644
--- a/mysql-test/r/mysqldump.result
+++ b/mysql-test/r/mysqldump.result
@@ -1738,61 +1738,61 @@ CREATE TABLE `t1` (
`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 @@ set @fired:= "No";
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;
diff --git a/mysql-test/t/mysqldump.test b/mysql-test/t/mysqldump.test
index 27bea937dcf..58d76d6401d 100644
--- a/mysql-test/t/mysqldump.test
+++ b/mysql-test/t/mysqldump.test
@@ -761,3 +761,38 @@ show tables;
--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;
View Lorry Controller Status