Re: new mysqldump features (patch)

[Tim Bunce]

On Wed, Aug 20, 2003 at 10:45:16PM -0700, John David Duncan wrote:
> Hi,
> 
> In the attached patch I have implemented two new options in mysqldump.
> The -R (--rename-tables)  option loads all tables using temporary names
> and then renames them while holding a lock.  This can be useful when it is
> necessary to minimize lock time.  The output looks like this:
> 
> sh% mysqldump --rename-tables mydb zip city
> 
>   CREATE TABLE _new_zip ...
>   CREATE TABLE _new_city ...
>   INSERT ...
> 
>   LOCK TABLES zip city WRITE;
>   DROP TABLE zip;
>   ALTER TABLE _new_zip RENAME zip;
>   DROP TABLE city;
>   ALTER TABLE _new_city RENAME city;
>   UNLOCK TABLES;

This seems like a good idea, but I'd recommend a a few simple changes:

1. Need to be able to specify the format of the temp table names to
	be sure name clashes can be avoided (a user may have tables
	that begin with underscore, for example).

2. "rename-tables" isn't very self-explanatory. I'd suggest
	--load-atomic which describes what it's doing rather than how.
	
3. Put all the new tables in place at once (fast), then delete the
	old (slow). This can be done using the RENAME command:
	RENAME	zip TO _zip_old;
	RENAME _zip TO zip;
   Although RENAME is atomic and one could rename all the tables
   (thus avoiding the need to lock tables) I'm not sure how RENAME
   behaves if some of the tables don't exist.

Tim.

> The -U (--with-use-db) option assures that "USE db_name;" is always in the
> output; this is otherwise not true unless --databases or --all-databases
> is used.
> 
> - JD
> *** mysqldump.c	Fri Jul 18 07:57:46 2003
> --- new-mysqldump.c	Wed Aug 20 22:00:15 2003
> ***************
> *** 77,84 ****
>                   opt_delayed=0,create_options=0,opt_quoted=0,opt_databases=0,
>   	        opt_alldbs=0,opt_create_db=0,opt_first_slave=0,
>                   opt_autocommit=0,opt_master_data,opt_disable_keys=0,opt_xml=0,
> ! 	        opt_delete_master_logs=0, tty_password=0,
> ! 		opt_single_transaction=0;
>   static MYSQL  mysql_connection,*sock=0;
>   static char  insert_pat[12 * 1024],*opt_password=0,*current_user=0,
>                *current_host=0,*path=0,*fields_terminated=0,
> --- 77,84 ----
>                   opt_delayed=0,create_options=0,opt_quoted=0,opt_databases=0,
>   	        opt_alldbs=0,opt_create_db=0,opt_first_slave=0,
>                   opt_autocommit=0,opt_master_data,opt_disable_keys=0,opt_xml=0,
> ! 	        opt_delete_master_logs=0, tty_password=0,opt_rename_tables=0,
> ! 		opt_single_transaction=0,opt_with_use_db=0;
>   static MYSQL  mysql_connection,*sock=0;
>   static char  insert_pat[12 * 1024],*opt_password=0,*current_user=0,
>                *current_host=0,*path=0,*fields_terminated=0,
> ***************
> *** 212,217 ****
> --- 212,219 ----
>     {"result-file", 'r',
>      "Direct output to a given file. This option should be used in MSDOS, because it
> prevents new line '\\n' from being converted to '\\r\\n' (carriage return + line feed).",
>      0, 0, 0, GET_STR, REQUIRED_ARG, 0, 0, 0, 0, 0, 0},
> +   {"rename-tables", 'R',"Create tables using temporary names and then rename them
> while holding a lock",
> +    (gptr*) &opt_rename_tables, (gptr*) &opt_rename_tables, 0, GET_BOOL,
> NO_ARG, 0, 0, 0, 0, 0, 0},
>     {"socket", 'S', "Socket file to use for connection.",
>      (gptr*) &opt_mysql_unix_port, (gptr*) &opt_mysql_unix_port, 0, GET_STR,
>      REQUIRED_ARG, 0, 0, 0, 0, 0, 0},
> ***************
> *** 226,231 ****
> --- 228,235 ----
>      (gptr*) &current_user, (gptr*) &current_user, 0, GET_STR, REQUIRED_ARG,
>      0, 0, 0, 0, 0, 0},
>   #endif
> +   {"with-use-db", 'U',"Always include 'USE db_name;' in the output",
> +    (gptr*) &opt_with_use_db, (gptr*) &opt_with_use_db, 0, GET_BOOL, NO_ARG,
> 0, 0, 0, 0, 0, 0},
>     {"verbose", 'v', "Print info about the various stages.",
>      (gptr*) &verbose, (gptr*) &verbose, 0, GET_BOOL, NO_ARG, 0, 0, 0, 0, 0,
> 0},
>     {"version",'V', "Output version information and exit.", 0, 0, 0,
> ***************
> *** 254,265 ****
> --- 258,276 ----
>   			int string_value);
>   static int dump_selected_tables(char *db, char **table_names, int tables);
>   static int dump_all_tables_in_db(char *db);
> + 
>   static int init_dumping(char *);
>   static int dump_databases(char **);
>   static int dump_all_databases();
>   static char *quote_name(char *name, char *buff);
>   static void print_quoted_xml(FILE *output, char *fname, char *str, uint len);
>   
> + static char *rename_and_quote(char *name, char *buff);
> + static int drop_and_rename(char *table);
> + static int rename_tables(char *db, char **table_names, int tables);
> + static int rename_all_in_db(char *database);
> + 
> + 
>   static void print_version(void)
>   {
>     printf("%s  Ver %s Distrib %s, for %s (%s)\n",my_progname,DUMP_VERSION,
> ***************
> *** 555,560 ****
> --- 566,587 ----
>   } /* quote_name */
>   
>   
> + static char *rename_and_quote(char *name, char *buff)
> + {
> +   char *end;
> +   int y;
> + 
> +   end = buff;
> +   y = (opt_quoted || test_if_special_chars(name));
> +   if(y) *end++ = QUOTE_CHAR;
> +   end=strmov(end,"_new_");
> +   end=strnmov(end,name,NAME_LEN-4);
> +   if(y) *end++ = QUOTE_CHAR;
> +   *end =  0;
> +   return buff;
> + } /* rename_and_quote */
> + 
> + 
>   /*
>   ** getStructure -- retrievs database structure, prints out corresponding
>   **       CREATE statement and fills out insert_pat.
> ***************
> *** 566,574 ****
>     MYSQL_ROW  row;
>     my_bool    init=0;
>     uint       numFields;
> !   char	     *strpos, *table_name;
>     const char *delayed;
> !   char	     name_buff[NAME_LEN+3],table_buff[NAME_LEN+3];
>     FILE       *sql_file = md_result_file;
>     DBUG_ENTER("getTableStructure");
>   
> --- 593,601 ----
>     MYSQL_ROW  row;
>     my_bool    init=0;
>     uint       numFields;
> !   char	     *strpos, *table_name, *target_table;
>     const char *delayed;
> !   char	     name_buff[NAME_LEN+3],table_buff[NAME_LEN+3],target_buff[NAME_LEN+3];
>     FILE       *sql_file = md_result_file;
>     DBUG_ENTER("getTableStructure");
>   
> ***************
> *** 579,584 ****
> --- 606,614 ----
>   
>     sprintf(insert_pat,"SET OPTION SQL_QUOTE_SHOW_CREATE=%d", (opt_quoted ||
> opt_keywords));
>     table_name=quote_name(table,table_buff);
> +   if(opt_rename_tables) 
> +     target_table=rename_and_quote(table,target_buff);
> +   else target_table=table_name;
>     if (!mysql_query(sock,insert_pat))
>     {
>       /* using SHOW CREATE statement */
> ***************
> *** 610,624 ****
>           write_header(sql_file, db);
>         }
>         if (!opt_xml)
> ! 	fprintf(sql_file, "\n--\n-- Table structure for table '%s'\n--\n\n",
> ! 		table);
>         if (opt_drop)
> !         fprintf(sql_file, "DROP TABLE IF EXISTS %s;\n",table_name);
>   
>         tableRes=mysql_store_result(sock);
>         row=mysql_fetch_row(tableRes);
> !       if (!opt_xml)
> ! 	fprintf(sql_file, "%s;\n", row[1]);
>         mysql_free_result(tableRes);
>       }
>       sprintf(insert_pat,"show fields from %s",table_name);
> --- 640,666 ----
>           write_header(sql_file, db);
>         }
>         if (!opt_xml)
> ! 	    fprintf(sql_file, "\n--\n-- Table structure for table '%s'\n--\n\n",table);
>         if (opt_drop)
> !         fprintf(sql_file, "DROP TABLE IF EXISTS %s;\n",target_table);
>   
>         tableRes=mysql_store_result(sock);
>         row=mysql_fetch_row(tableRes);
> !       if(opt_rename_tables) {
> !         char *s = row[1];
> !         if(!(bcmp(row[1],"CREATE TABLE ",13))) {
> !           fprintf(sql_file,"CREATE TABLE %s ",target_table);
> !           for(s+=13 ; *s++ != ' ' ; );
> !           fprintf(sql_file, "%s;\n", s);
> !         }
> !         else { 
> !           fprintf(sql_file,"-- Failed to use renamed table\n\n");
> !           target_table=table_name;
> !           opt_rename_tables=0;
> !         }
> !       }  
> !       else if (!opt_xml)
> ! 	    fprintf(sql_file, "%s;\n", row[1]);
>         mysql_free_result(tableRes);
>       }
>       sprintf(insert_pat,"show fields from %s",table_name);
> ***************
> *** 633,642 ****
>       }
>   
>       if (cFlag)
> !       sprintf(insert_pat, "INSERT %sINTO %s (", delayed, table_name);
>       else
>       {
> !       sprintf(insert_pat, "INSERT %sINTO %s VALUES ", delayed, table_name);
>         if (!extended_insert)
>           strcat(insert_pat,"(");
>       }
> --- 675,684 ----
>       }
>   
>       if (cFlag)
> !       sprintf(insert_pat, "INSERT %sINTO %s (", delayed, target_table);
>       else
>       {
> !       sprintf(insert_pat, "INSERT %sINTO %s VALUES ", delayed, target_table);
>         if (!extended_insert)
>           strcat(insert_pat,"(");
>       }
> ***************
> *** 690,703 ****
>   	fprintf(sql_file, "\n--\n-- Table structure for table '%s'\n--\n\n",
>   		table);
>         if (opt_drop)
> !         fprintf(sql_file, "DROP TABLE IF EXISTS %s;\n",table_name);
> !       fprintf(sql_file, "CREATE TABLE %s (\n", table_name);
>       }
>       if (cFlag)
> !       sprintf(insert_pat, "INSERT %sINTO %s (", delayed, table_name);
>       else
>       {
> !       sprintf(insert_pat, "INSERT %sINTO %s VALUES ", delayed, table_name);
>         if (!extended_insert)
>           strcat(insert_pat,"(");
>       }
> --- 732,745 ----
>   	fprintf(sql_file, "\n--\n-- Table structure for table '%s'\n--\n\n",
>   		table);
>         if (opt_drop)
> !         fprintf(sql_file, "DROP TABLE IF EXISTS %s;\n",target_table);
> !       fprintf(sql_file, "CREATE TABLE %s (\n", target_table);
>       }
>       if (cFlag)
> !       sprintf(insert_pat, "INSERT %sINTO %s (", delayed, target_table);
>       else
>       {
> !       sprintf(insert_pat, "INSERT %sINTO %s VALUES ", delayed, target_table);
>         if (!extended_insert)
>           strcat(insert_pat,"(");
>       }
> ***************
> *** 719,725 ****
>         if (!tFlag)
>         {
>           if (opt_keywords)
> ! 	  fprintf(sql_file, "  %s.%s %s", table_name,
>   	  quote_name(row[SHOW_FIELDNAME],name_buff), row[SHOW_TYPE]);
>           else
>   	  fprintf(sql_file, "  %s %s", quote_name(row[SHOW_FIELDNAME],
> --- 761,767 ----
>         if (!tFlag)
>         {
>           if (opt_keywords)
> ! 	  fprintf(sql_file, "  %s.%s %s", target_table,
>   	  quote_name(row[SHOW_FIELDNAME],name_buff), row[SHOW_TYPE]);
>           else
>   	  fprintf(sql_file, "  %s %s", quote_name(row[SHOW_FIELDNAME],
> ***************
> *** 901,907 ****
>   */
>   static void dumpTable(uint numFields, char *table)
>   {
> !   char query[QUERY_LENGTH], *end, buff[256],table_buff[NAME_LEN+3];
>     MYSQL_RES	*res;
>     MYSQL_FIELD	*field;
>     MYSQL_ROW	row;
> --- 943,950 ----
>   */
>   static void dumpTable(uint numFields, char *table)
>   {
> !   char query[QUERY_LENGTH], *end, *target_table;
> !   char buff[256],table_buff[NAME_LEN+3],target_buff[NAME_LEN+3];  
>     MYSQL_RES	*res;
>     MYSQL_FIELD	*field;
>     MYSQL_ROW	row;
> ***************
> *** 909,914 ****
> --- 952,960 ----
>   
>     if (verbose)
>       fprintf(stderr, "-- Sending SELECT query...\n");
> +   if(opt_rename_tables) 
> +     target_table=rename_and_quote(table,target_buff);
> +   else target_table=quote_name(table,table_buff);
>     if (path)
>     {
>       char filename[FN_REFLEN], tmp_path[FN_REFLEN];
> ***************
> *** 985,995 ****
>       }
>   
>       if (opt_disable_keys)
> !       fprintf(md_result_file,"/*!40000 ALTER TABLE %s DISABLE KEYS */;\n",
> ! 	      quote_name(table, table_buff));
>       if (opt_lock)
> !       fprintf(md_result_file,"LOCK TABLES %s WRITE;\n",
> ! 	      quote_name(table,table_buff));
>   
>       total_length=net_buffer_length;		/* Force row break */
>       row_break=0;
> --- 1031,1039 ----
>       }
>   
>       if (opt_disable_keys)
> !       fprintf(md_result_file,"/*!40000 ALTER TABLE %s DISABLE KEYS
> */;\n",target_table);
>       if (opt_lock)
> !       fprintf(md_result_file,"LOCK TABLES %s WRITE;\n",target_table);
>   
>       total_length=net_buffer_length;		/* Force row break */
>       row_break=0;
> ***************
> *** 1152,1162 ****
>         safe_exit(EX_CONSCHECK);
>         return;
>       }
>       if (opt_lock)
>         fputs("UNLOCK TABLES;\n", md_result_file);
>       if (opt_disable_keys)
>         fprintf(md_result_file,"/*!40000 ALTER TABLE %s ENABLE KEYS */;\n",
> ! 	      quote_name(table,table_buff));
>       if (opt_autocommit)
>         fprintf(md_result_file, "commit;\n");
>       mysql_free_result(res);
> --- 1196,1208 ----
>         safe_exit(EX_CONSCHECK);
>         return;
>       }
> + 
> + 
>       if (opt_lock)
>         fputs("UNLOCK TABLES;\n", md_result_file);
>       if (opt_disable_keys)
>         fprintf(md_result_file,"/*!40000 ALTER TABLE %s ENABLE KEYS */;\n",
> ! 	      target_table);
>       if (opt_autocommit)
>         fprintf(md_result_file, "commit;\n");
>       mysql_free_result(res);
> ***************
> *** 1253,1262 ****
>     }
>     if (!path && !opt_xml)
>     {
> !     if (opt_databases || opt_alldbs)
>       {
>         fprintf(md_result_file,"\n--\n-- Current Database: %s\n--\n", database);
> !       if (!opt_create_db)
>   	fprintf(md_result_file,"\nCREATE DATABASE /*!32312 IF NOT EXISTS*/ %s;\n",
>   		database);
>         fprintf(md_result_file,"\nUSE %s;\n", database);
> --- 1299,1308 ----
>     }
>     if (!path && !opt_xml)
>     {
> !     if (opt_databases || opt_alldbs || opt_with_use_db)
>       {
>         fprintf(md_result_file,"\n--\n-- Current Database: %s\n--\n", database);
> !       if ((opt_databases || opt_alldbs) && !opt_create_db)
>   	fprintf(md_result_file,"\nCREATE DATABASE /*!32312 IF NOT EXISTS*/ %s;\n",
>   		database);
>         fprintf(md_result_file,"\nUSE %s;\n", database);
> ***************
> *** 1299,1305 ****
>         DBerror(sock, "when doing refresh");
>              /* We shall continue here, if --force was given */
>     }
> !   while ((table = getTableName(0)))
>     {
>       numrows = getTableStructure(table, database);
>       if (!dFlag && numrows > 0)
> --- 1345,1351 ----
>         DBerror(sock, "when doing refresh");
>              /* We shall continue here, if --force was given */
>     }
> !   while ((table = getTableName(1)))
>     {
>       numrows = getTableStructure(table, database);
>       if (!dFlag && numrows > 0)
> ***************
> *** 1309,1314 ****
> --- 1355,1362 ----
>       fprintf(md_result_file, "</database>\n");
>     if (lock_tables)
>       mysql_query(sock,"UNLOCK_TABLES");
> +   if (opt_rename_tables) 
> +     rename_all_in_db(database);
>     return 0;
>   } /* dump_all_tables_in_db */
>   
> ***************
> *** 1317,1330 ****
>   static int dump_selected_tables(char *db, char **table_names, int tables)
>   {
>     uint numrows;
> !   char table_buff[NAME_LEN+3];
>   
>     if (init_dumping(db))
>       return 1;
>     if (lock_tables)
>     {
>       DYNAMIC_STRING query;
> -     int i;
>   
>       init_dynamic_string(&query, "LOCK TABLES ", 256, 1024);
>       for (i=0 ; i < tables ; i++)
> --- 1365,1379 ----
>   static int dump_selected_tables(char *db, char **table_names, int tables)
>   {
>     uint numrows;
> !   char *target_table;
> !   char table_buff[NAME_LEN+3], target_buff[NAME_LEN+3];
> !   int i;
>   
>     if (init_dumping(db))
>       return 1;
>     if (lock_tables)
>     {
>       DYNAMIC_STRING query;
>   
>       init_dynamic_string(&query, "LOCK TABLES ", 256, 1024);
>       for (i=0 ; i < tables ; i++)
> ***************
> *** 1345,1362 ****
>     }
>     if (opt_xml)
>       fprintf(md_result_file, "<database name=\"%s\">\n", db);
> !   for (; tables > 0 ; tables-- , table_names++)
>     {
> !     numrows = getTableStructure(*table_names, db);
>       if (!dFlag && numrows > 0)
> !       dumpTable(numrows, *table_names);
>     }
>     if (opt_xml)
>       fprintf(md_result_file, "</database>\n");
>     if (lock_tables)
>       mysql_query(sock,"UNLOCK_TABLES");
>     return 0;
>   } /* dump_selected_tables */
>   
>   
>   /* Print a value with a prefix on file */
> --- 1394,1462 ----
>     }
>     if (opt_xml)
>       fprintf(md_result_file, "<database name=\"%s\">\n", db);
> !   for (i = 0; i < tables ; i++)
>     {
> !     numrows = getTableStructure(table_names[i], db);
>       if (!dFlag && numrows > 0)
> !       dumpTable(numrows, table_names[i]);
>     }
>     if (opt_xml)
>       fprintf(md_result_file, "</database>\n");
>     if (lock_tables)
>       mysql_query(sock,"UNLOCK_TABLES");
> +   if (opt_rename_tables) 
> +     rename_tables(db,table_names,tables);
>     return 0;
>   } /* dump_selected_tables */
> + 
> + 
> + static int drop_and_rename(char *table) 
> + {
> +   char *renamed_table, *real_table;
> +   char table_buff[NAME_LEN+3],target_buff[NAME_LEN+3];
> + 
> +   real_table=quote_name(table,table_buff);
> +   renamed_table=rename_and_quote(table,target_buff);
> +   fprintf(md_result_file,"DROP TABLE %s;\n",real_table);
> +   fprintf(md_result_file,"ALTER TABLE %s RENAME %s;\n",
> +     renamed_table,real_table);
> + } /* drop_and_rename */
> + 
> + 
> + static int rename_all_in_db(char *database) 
> + {
> +   int i;
> +   char *table;
> +   char buff[NAME_LEN+3];
> +   
> +   fprintf(md_result_file,"\nLOCK TABLES ");
> +   while ((table = getTableName(1)))
> +     fprintf(md_result_file,"%s ",quote_name(table,buff));
> +   fprintf(md_result_file,"WRITE;\n");
> +   
> +   while ((table = getTableName(1))) {
> +     drop_and_rename(table);
> +   }
> +   fprintf(md_result_file,"UNLOCK TABLES;\n"); 
> + } /* rename_all_in_db */
> + 
> + 
> + static int rename_tables(char *db, char **table_names, int tables) 
> + {
> +   int i;
> +   char *table;
> +   char buff[NAME_LEN+3];
> + 
> +   fprintf(md_result_file,"\nLOCK TABLES ");
> +   for (i=0 ; i < tables ; i++) 
> +     fprintf(md_result_file,"%s ",
> +             quote_name(table_names[i],buff));
> +   fprintf(md_result_file,"WRITE;\n");
> + 
> +   for (i=0 ; i < tables ; i++) 
> +     drop_and_rename(table_names[i]);
> +   fprintf(md_result_file,"UNLOCK TABLES;\n"); 
> + } /* rename_tables */
>   
>   
>   /* Print a value with a prefix on file */

> -- 
> MySQL Internals Mailing List
> For list archives: http://lists.mysql.com/internals
> To unsubscribe:    http://lists.mysql.com/internals?unsub=1