bk commit into 5.0 tree (patg:1.2031) BUG#13146

[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.2031 05/10/10 23:59:18 patg@stripped +3 -0
  BUG #13146
  
  Added flag in quote_names (in mysqldump) to deal properly with mysqldump
  setting SQL_MODE to normal when dummping triggers.

  mysql-test/t/mysqldump.test
    1.69 05/10/10 23:58:57 patg@stripped +38 -0
    BUG #13146 - New test to test bug 13146

  mysql-test/r/mysqldump.result
    1.74 05/10/10 23:58:57 patg@stripped +89 -0
    BUG #13146 new test results

  client/mysqldump.c
    1.204 05/10/10 23:58:56 patg@stripped +48 -23
    BUG# 13146
    
    Added dumping triggers flag to quote_name so when dumping triggers, we 
    revert to "`" type quote since using " quote even in --compatible=ansi
    doesn't work.
    Also made sure all calls to quote_name use this flag.

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

--- 1.203/client/mysqldump.c	2005-09-27 18:40:42 +02:00
+++ 1.204/client/mysqldump.c	2005-10-10 23:58:56 +02:00
@@ -413,7 +413,8 @@
 static int init_dumping(char *);
 static int dump_databases(char **);
 static int dump_all_databases();
-static char *quote_name(const char *name, char *buff, my_bool force);
+static char *quote_name(const char *name, char *buff, my_bool force,
+                        my_bool dumping_triggers);
 char check_if_ignore_table(const char *table_name, char *table_type);
 static char *primary_key_fields(const char *table_name);
 static my_bool get_view_structure(char *table, char* db);
@@ -947,11 +948,32 @@
 } /* test_if_special_chars */
 
 
+/*
+  quote_name(name, buff, force, dumping_triggers)
+
+  Quotes char string, taking into account compatible mode 
+
+  Args
+
+  name                 Unquoted string containing that which will be quoted
+  buff                 The buffer that contains the quoted value, also returned
+  force                Flag to make it ignore 'test_if_special_chars' 
+  dumping_triggers     Flag to make it resort to '`' chars since dumping of 
+                       triggers sets sql mode to normal temporary, and ansi
+                       quotes will not work
+
+  Returns
 
-static char *quote_name(const char *name, char *buff, my_bool force)
+  buff                 quoted string
+
+*/
+static char *quote_name(const char *name, char *buff, my_bool force,
+                        my_bool dumping_triggers)
 {
   char *to= buff;
-  char qtype= (opt_compatible_mode & MASK_ANSI_QUOTES) ? '\"' : '`';
+  /* dumping_triggers flag added per bug 13146 by Patrick Galbraith */
+  char qtype= ((opt_compatible_mode & MASK_ANSI_QUOTES) && !dumping_triggers)
?
+    '\"' : '`';
 
   if (!force && !opt_quoted && !test_if_special_chars(name))
     return (char*) name;
@@ -1220,7 +1242,7 @@
       {
         DBUG_PRINT("info", ("retrieving CREATE %s for %s", routine_type[i],
                             name_buff));
-        routine_name=quote_name(routine_list_row[1], name_buff, 0);
+        routine_name=quote_name(routine_list_row[1], name_buff, 0, 0);
         my_snprintf(query_buff, sizeof(query_buff), "SHOW CREATE %s %s",
                     routine_type[i], routine_name);
 
@@ -1328,8 +1350,8 @@
     strmov(query_buff+len,
            "/*!40102 ,SQL_MODE=concat(@@sql_mode, _utf8
',NO_KEY_OPTIONS,NO_TABLE_OPTIONS,NO_FIELD_OPTIONS') */");
 
-  result_table=     quote_name(table, table_buff, 1);
-  opt_quoted_table= quote_name(table, table_buff2, 0);
+  result_table=     quote_name(table, table_buff, 1, 0);
+  opt_quoted_table= quote_name(table, table_buff2, 0, 0);
 
   if (opt_order_by_primary)
     order_by = primary_key_fields(result_table);
@@ -1480,7 +1502,7 @@
       init=1;
       if (opt_complete_insert && !(*ignore_flag & IGNORE_DATA))
         dynstr_append(&insert_pat,
-                      quote_name(row[SHOW_FIELDNAME], name_buff, 0));
+                      quote_name(row[SHOW_FIELDNAME], name_buff, 0, 0));
     }
     num_fields= (uint) mysql_num_rows(tableRes);
     mysql_free_result(tableRes);
@@ -1560,7 +1582,7 @@
       init=1;
       if (opt_complete_insert && !(*ignore_flag & IGNORE_DATA))
         dynstr_append(&insert_pat,
-                      quote_name(row[SHOW_FIELDNAME], name_buff, 0));
+                      quote_name(row[SHOW_FIELDNAME], name_buff, 0, 0));
       if (!tFlag)
       {
 	if (opt_xml)
@@ -1571,11 +1593,11 @@
 
         if (opt_keywords)
 	  fprintf(sql_file, "  %s.%s %s", result_table,
-		  quote_name(row[SHOW_FIELDNAME],name_buff, 0),
+		  quote_name(row[SHOW_FIELDNAME],name_buff, 0, 0),
 		  row[SHOW_TYPE]);
         else
 	  fprintf(sql_file, "  %s %s", quote_name(row[SHOW_FIELDNAME],
-						  name_buff, 0),
+						  name_buff, 0, 0),
 		  row[SHOW_TYPE]);
         if (row[SHOW_DEFAULT])
         {
@@ -1648,16 +1670,16 @@
 	    putc(')', sql_file);
 	  if (atoi(row[1]))       /* Test if duplicate key */
 	    /* Duplicate allowed */
-	    fprintf(sql_file, ",\n  KEY %s (",quote_name(row[2],name_buff,0));
+	    fprintf(sql_file, ",\n  KEY %s (",quote_name(row[2],name_buff,0, 0));
 	  else if (keynr == primary_key)
 	    fputs(",\n  PRIMARY KEY (",sql_file); /* First UNIQUE is primary */
 	  else
 	    fprintf(sql_file, ",\n  UNIQUE %s (",quote_name(row[2],name_buff,
-							    0));
+							    0, 0));
         }
         else
 	  putc(',', sql_file);
-        fputs(quote_name(row[4], name_buff, 0), sql_file);
+        fputs(quote_name(row[4], name_buff, 0, 0), sql_file);
         if (row[7])
 	  fprintf(sql_file, " (%s)",row[7]);      /* Sub key */
 	check_io(sql_file);
@@ -1756,7 +1778,7 @@
 
   DBUG_ENTER("dump_triggers_for_table");
   DBUG_PRINT("enter", ("db: %s, table: %s", db, table));
-  result_table=     quote_name(table, table_buff, 1);
+  result_table=     quote_name(table, table_buff, 1, 1);
 
   my_snprintf(query_buff, sizeof(query_buff),
               "SHOW TRIGGERS LIKE %s",
@@ -1774,10 +1796,13 @@
 DELIMITER //;\n");
   while ((row=mysql_fetch_row(result)))
   {
+    /*
+      make sure to pass dumping_triggers flag as 1 (last arg on quote_name)
+    */
     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 */
+            quote_name(row[0], name_buff, 0, 1), /* Trigger */
             row[4], /* Timing */
             row[1], /* Event */
             result_table,
@@ -1924,8 +1949,8 @@
     DBUG_VOID_RETURN;
   }
 
-  result_table= quote_name(table,table_buff, 1);
-  opt_quoted_table= quote_name(table, table_buff2, 0);
+  result_table= quote_name(table,table_buff, 1, 0);
+  opt_quoted_table= quote_name(table, table_buff2, 0, 0);
 
 
   if (verbose)
@@ -2431,7 +2456,7 @@
 	length of table name * 2 (if name contains quotes), 2 quotes and 0
       */
       char quoted_database_buf[64*2+3];
-      char *qdatabase= quote_name(database,quoted_database_buf,opt_quoted);
+      char *qdatabase= quote_name(database,quoted_database_buf,opt_quoted, 0);
       if (opt_comments)
       {
 	fprintf(md_result_file,"\n--\n-- Current Database: %s\n--\n", qdatabase);
@@ -2512,7 +2537,7 @@
     init_dynamic_string(&query, "LOCK TABLES ", 256, 1024);
     for (numrows= 0 ; (table= getTableName(1)) ; numrows++)
     {
-      dynstr_append(&query, quote_name(table, table_buff, 1));
+      dynstr_append(&query, quote_name(table, table_buff, 1, 0));
       dynstr_append(&query, " READ /*!32311 LOCAL */,");
     }
     if (numrows && mysql_real_query(sock, query.str, query.length-1))
@@ -2584,7 +2609,7 @@
     init_dynamic_string(&query, "LOCK TABLES ", 256, 1024);
     for (numrows= 0 ; (table= getTableName(1)); numrows++)
     {
-      dynstr_append(&query, quote_name(table, table_buff, 1));
+      dynstr_append(&query, quote_name(table, table_buff, 1, 0));
       dynstr_append(&query, " READ /*!32311 LOCAL */,");
     }
     if (numrows && mysql_real_query(sock, query.str, query.length-1))
@@ -2693,7 +2718,7 @@
       if (lock_tables)
       {
         dynstr_append(&lock_tables_query,
-                      quote_name(new_table_name, table_buff, 1));
+                      quote_name(new_table_name, table_buff, 1, 0));
         dynstr_append(&lock_tables_query, " READ /*!32311 LOCAL */,");
       }
 
@@ -3135,8 +3160,8 @@
 	  (opt_quoted || opt_keywords));
 #endif
 
-  result_table=     quote_name(table, table_buff, 1);
-  opt_quoted_table= quote_name(table, table_buff2, 0);
+  result_table=     quote_name(table, table_buff, 1, 0);
+  opt_quoted_table= quote_name(table, table_buff2, 0, 0);
 
   sprintf(buff,"show create table %s", result_table);
   if (mysql_query(sock, buff))

--- 1.73/mysql-test/r/mysqldump.result	2005-09-30 09:40:22 +02:00
+++ 1.74/mysql-test/r/mysqldump.result	2005-10-10 23:58:57 +02:00
@@ -2123,3 +2123,92 @@
 DROP PROCEDURE bug9056_proc1;
 DROP PROCEDURE bug9056_proc2;
 drop table t1;
+DROP TABLE IF EXISTS `t1 test`;
+CREATE TABLE `t1 test` (
+`a1` int(11) default NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1;
+DROP TABLE IF EXISTS `t2 test`;
+CREATE TABLE `t2 test` (
+`a2` int(11) default NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1;
+CREATE TRIGGER `test trig` BEFORE INSERT ON `t1 test` FOR EACH ROW BEGIN
+INSERT INTO `t2 test` SET a2 = NEW.a1; END  //
+INSERT INTO `t1 test` VALUES (1);
+INSERT INTO `t1 test` VALUES (2);
+INSERT INTO `t1 test` VALUES (3);
+SELECT * FROM `t2 test`;
+a2
+1
+2
+3
+-- MySQL dump 10.10
+--
+-- Host: localhost    Database: test
+-- ------------------------------------------------------
+-- Server version	5.0.15-rc-debug-log
+/*!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 */;
+
+--
+-- Table structure for table "t1 test"
+--
+
+DROP TABLE IF EXISTS "t1 test";
+CREATE TABLE "t1 test" (
+  "a1" int(11) default NULL
+);
+
+--
+-- Dumping data for table "t1 test"
+--
+
+
+/*!40000 ALTER TABLE "t1 test" DISABLE KEYS */;
+LOCK TABLES "t1 test" WRITE;
+INSERT INTO "t1 test" VALUES (1),(2),(3);
+UNLOCK TABLES;
+/*!40000 ALTER TABLE "t1 test" ENABLE KEYS */;
+
+/*!50003 SET @OLD_SQL_MODE=@@SQL_MODE*/;
+DELIMITER //;
+/*!50003 SET SESSION SQL_MODE="" */ //
+/*!50003 CREATE TRIGGER `test trig` BEFORE INSERT ON `t1 test` FOR EACH ROW BEGIN
+INSERT INTO `t2 test` SET a2 = NEW.a1; END */ //
+
+DELIMITER ;//
+/*!50003 SET SESSION SQL_MODE=@OLD_SQL_MODE */;
+--
+-- Table structure for table "t2 test"
+--
+
+DROP TABLE IF EXISTS "t2 test";
+CREATE TABLE "t2 test" (
+  "a2" int(11) default NULL
+);
+
+--
+-- Dumping data for table "t2 test"
+--
+
+
+/*!40000 ALTER TABLE "t2 test" DISABLE KEYS */;
+LOCK TABLES "t2 test" WRITE;
+INSERT INTO "t2 test" VALUES (1),(2),(3);
+UNLOCK TABLES;
+/*!40000 ALTER TABLE "t2 test" 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 */;
+
+SELECT * FROM `t2 test`;
+a2
+1
+2
+3
+DROP TRIGGER `test trig`;
+DROP TABLE IF EXISTS `t1 test`;
+DROP TABLE IF EXISTS `t2 test`;

--- 1.68/mysql-test/t/mysqldump.test	2005-09-30 22:37:50 +02:00
+++ 1.69/mysql-test/t/mysqldump.test	2005-10-10 23:58:57 +02:00
@@ -907,3 +907,41 @@
 DROP PROCEDURE bug9056_proc2;
 drop table t1;
 
+#
+# Test of fix to BUG 13146 
+#
+
+--disable_warnings
+DROP TABLE IF EXISTS `t1 test`;
+CREATE TABLE `t1 test` (
+  `a1` int(11) default NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1;
+
+DROP TABLE IF EXISTS `t2 test`;
+CREATE TABLE `t2 test` (
+  `a2` int(11) default NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1;
+--enable_warnings
+
+DELIMITER //;
+CREATE TRIGGER `test trig` BEFORE INSERT ON `t1 test` FOR EACH ROW BEGIN
+INSERT INTO `t2 test` SET a2 = NEW.a1; END  //
+DELIMITER ;//
+
+INSERT INTO `t1 test` VALUES (1);
+INSERT INTO `t1 test` VALUES (2);
+INSERT INTO `t1 test` VALUES (3);
+SELECT * FROM `t2 test`;
+# dump with compatible=ansi. Everything except triggers should be double 
+# quoted
+--exec $MYSQL_DUMP --compatible=ansi --triggers test
+--exec $MYSQL_DUMP --compatible=ansi --triggers test > var/tmp/bug13146.sql
+# test to see that everything, particulary the triggers, load correctly 
+--exec $MYSQL test < var/tmp/bug13146.sql
+SELECT * FROM `t2 test`;
+
+--disable_warnings
+DROP TRIGGER `test trig`;
+DROP TABLE IF EXISTS `t1 test`;
+DROP TABLE IF EXISTS `t2 test`;
+--enable_warnings