Bug #1248 LOAD DATA FROM MASTER drops the slave's db unexpectedly
Submitted: 11 Sep 2003 7:25 Modified: 12 Sep 2003 6:27
Reporter: Guilhem Bichot Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Replication Severity:S3 (Non-critical)
Version:4.0 OS:Any (all)
Assigned to: Guilhem Bichot

[11 Sep 2003 7:25] Guilhem Bichot
Description:
LOAD DATA FROM MASTER reads the list of all dbs on the master. For each db, it tests if this matches replicate_*_db rules (* = "do" or "ignore"); if it does it drops the db on the slave and recreates it; then it reads the list of all tables in this db on the master. For each table it tests if this matches replicate_*_table rules; if it does it copies it to the slave.

How to repeat:
For example:
Have 2 databases (db1 and db2) on master and slave, db1 being not empty on slave.
Do LOAD DATA FROM MASTER on slave; see how db1 is now empty on slave.

Suggested fix:
It should work more simply:
1) for each db from the master, if it matches replicate_*_db rules (in particular if there are no such rules), don't drop it; just try to create it (a CREATE DATABASE IF NOT EXISTS) and go to step 2; dropping is stupid as the user may have tables in this db which exist only on the slave and which she/he wants to keep. 
2) for each table from this db, if it matches replicate_*_table rules (in particular if there are no such rules), drop it and copy it from the master.
[12 Sep 2003 6:17] Guilhem Bichot
Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html

Fixed in ChangeSet@1.1561.1.1, 2003-09-11 23:17:28+02:00, guilhem@mysql.com.
Here is a patch until 4.0.16 is released:

ChangeSet
  1.1562 03/09/11 23:17:28 guilhem@mysql.com +6 -0
  * Fix for BUG#1248: "LOAD DATA FROM MASTER drops the slave's db unexpectedly".
  Now LOAD DATA FROM MASTER does not drop the database, instead it only tries to
  create it, and drops/creates table-by-table.
  * replicate_wild_ignore_table='db1.%' is now considered as "ignore the 'db1'
  database as a whole", as it already works for CREATE DATABASE and DROP DATABASE.

  sql/sql_parse.cc
    1.339 03/09/11 23:17:22 guilhem@mysql.com +5 -2
    do not drop the table in LOAD TABLE FROM MASTER (this behaviour is already
    true; but changes in LOAD DATA FROM MASTER made the argument needed).

  sql/slave.h
    1.65 03/09/11 23:17:21 guilhem@mysql.com +2 -2
    new argument to drop the table in fetch_master_table

  sql/slave.cc
    1.251 03/09/11 23:17:21 guilhem@mysql.com +33 -12
    New argument to drop the table in create_table_from_dump() 
    (LOAD TABLE/DATA FROM MASTER are the only places where this function is used).
    This is needed because LOAD DATA FROM MASTER does not drop the database anymore.
    The behaviour when the table exists is unchanged: LOAD DATA silently replaces
    the table, LOAD TABLE gives error.

  sql/repl_failsafe.cc
    1.34 03/09/11 23:17:21 guilhem@mysql.com +9 -5
    * replicate_wild_ignore_table='db1.%' is now considered as "ignore the 'db1'
    database as a whole", as it already works for CREATE DATABASE and DROP DATABASE.
    * If a db matches replicate_*_db rules, we don't drop/recreate it because this
    could drop some tables in this db which could be slave-specific. Instead,
    we do a CREATE DATABASE IF EXISTS, and we will drop each table which has
    an equivalent on the master, table-by-table.

--- 1.250/sql/slave.cc  Mon Aug 25 16:20:10 2003
+++ 1.251/sql/slave.cc  Thu Sep 11 23:17:21 2003
@@ -72,7 +72,7 @@
                      void* thread_killed_arg);
 static int request_table_dump(MYSQL* mysql, const char* db, const char* table);
 static int create_table_from_dump(THD* thd, NET* net, const char* db,
-                                 const char* table_name);
+                                 const char* table_name, bool overwrite);
 static int check_master_version(MYSQL* mysql, MASTER_INFO* mi);
 
 
@@ -1033,12 +1033,22 @@
   return 0;
 }
 
+/*
+  Used by fetch_master_table (used by LOAD TABLE tblname FROM MASTER and LOAD
+  DATA FROM MASTER). Drops the table (if 'overwrite' is true) and recreates it
+  from the dump. Honours replication inclusion/exclusion rules.
+
+  RETURN VALUES
+    0           success
+    1           error
+*/
 
 static int create_table_from_dump(THD* thd, NET* net, const char* db,
-                                 const char* table_name)
+                                 const char* table_name, bool overwrite)
 {
   ulong packet_len = my_net_read(net); // read create table statement
   char *query;
+  char* save_db;
   Vio* save_vio;
   HA_CHECK_OPT check_opt;
   TABLE_LIST tables;
@@ -1078,13 +1088,24 @@
   thd->current_tablenr = 0;
   thd->query_error = 0;
   thd->net.no_send_ok = 1;
+
+  bzero((char*) &tables,sizeof(tables));
+  tables.db = (char*)db;
+  tables.alias= tables.real_name= (char*)table_name;
+  /* Drop the table if 'overwrite' is true */
+  if (overwrite && mysql_rm_table(thd,&tables,1)) /* drop if exists */
+  {
+    send_error(&thd->net);
+    sql_print_error("create_table_from_dump: failed to drop the table");
+    goto err;
+  }
   
-  /* we do not want to log create table statement */
+  /* Create the table. We do not want to log the "create table" statement */
   save_options = thd->options;
   thd->options &= ~(ulong) (OPTION_BIN_LOG);
   thd->proc_info = "Creating table from master dump";
   // save old db in case we are creating in a different database
-  char* save_db = thd->db;
+  save_db = thd->db;
   thd->db = (char*)db;
   mysql_parse(thd, thd->query, packet_len); // run create table
   thd->db = save_db;           // leave things the way the were before
@@ -1093,11 +1114,8 @@
   if (thd->query_error)
     goto err;                  // mysql_parse took care of the error send
 
-  bzero((char*) &tables,sizeof(tables));
-  tables.db = (char*)db;
-  tables.alias= tables.real_name= (char*)table_name;
-  tables.lock_type = TL_WRITE;
   thd->proc_info = "Opening master dump table";
+  tables.lock_type = TL_WRITE;
   if (!open_ltable(thd, &tables, TL_WRITE))
   {
     send_error(&thd->net,0,0);                 // Send error from open_ltable
@@ -1107,10 +1125,11 @@
   
   file = tables.table->file;
   thd->proc_info = "Reading master dump table data";
+  /* Copy the data file */
   if (file->net_read_dump(net))
   {
     net_printf(&thd->net, ER_MASTER_NET_READ);
-    sql_print_error("create_table_from_dump::failed in\
+    sql_print_error("create_table_from_dump: failed in\
  handler::net_read_dump()");
     goto err;
   }
@@ -1125,6 +1144,7 @@
   */
   save_vio = thd->net.vio;
   thd->net.vio = 0;
+  /* Rebuild the index file from the copied data file (with REPAIR) */
   error=file->repair(thd,&check_opt) != 0;
   thd->net.vio = save_vio;
   if (error)
@@ -1137,7 +1157,7 @@
 }
 
 int fetch_master_table(THD *thd, const char *db_name, const char *table_name,
-                      MASTER_INFO *mi, MYSQL *mysql)
+                      MASTER_INFO *mi, MYSQL *mysql, bool overwrite)
 {
   int error= 1;
   const char *errmsg=0;
@@ -1169,9 +1189,10 @@
     errmsg= "Failed on table dump request";
     goto err;
   }
+
   if (create_table_from_dump(thd, &mysql->net, db_name,
-                           table_name))
-    goto err;    // create_table_from_dump will have sent the error already
+                           table_name, overwrite))
+    goto err; // create_table_from_dump will have send_error already
   error = 0;
 
  err:

--- 1.338/sql/sql_parse.cc      Tue Sep  9 19:06:48 2003
+++ 1.339/sql/sql_parse.cc      Thu Sep 11 23:17:22 2003
@@ -1568,9 +1568,12 @@
        goto error;
     }
     LOCK_ACTIVE_MI;
-    // fetch_master_table will send the error to the client on failure
+    /*
+      fetch_master_table will send the error to the client on failure.
+      Give error if the table already exists.
+    */
     if (!fetch_master_table(thd, tables->db, tables->real_name,
-                           active_mi, 0))
+                           active_mi, 0, 0))
     {
       send_ok(&thd->net);
     }

--- 1.64/sql/slave.h    Mon Aug 25 16:20:11 2003
+++ 1.65/sql/slave.h    Thu Sep 11 23:17:21 2003
@@ -384,9 +384,9 @@
 int mysql_table_dump(THD* thd, const char* db,
                     const char* tbl_name, int fd = -1);
 
-/* retrieve non-exitent table from master */
+/* retrieve table from master and copy to slave*/
 int fetch_master_table(THD* thd, const char* db_name, const char* table_name,
-                      MASTER_INFO* mi, MYSQL* mysql);
+                      MASTER_INFO* mi, MYSQL* mysql, bool overwrite);
 
 int show_master_info(THD* thd, MASTER_INFO* mi);
 int show_binlog_info(THD* thd);

--- 1.33/sql/repl_failsafe.cc   Wed Aug 20 01:38:11 2003
+++ 1.34/sql/repl_failsafe.cc   Thu Sep 11 23:17:21 2003
@@ -717,7 +717,8 @@
       if (!tables_ok(thd, &table))
        continue;
     }
-    if ((error= fetch_master_table(thd, db, table_name, mi, mysql)))
+    /* download master's table and overwrite slave's table */
+    if ((error= fetch_master_table(thd, db, table_name, mi, mysql, 1)))
       return error;
   }
   return 0;
@@ -819,8 +820,11 @@
       char* db = row[0];
 
       /*
-       Do not replicate databases excluded by rules
-       also skip mysql database - in most cases the user will
+       Do not replicate databases excluded by rules. We also test
+       replicate_wild_*_table rules (replicate_wild_ignore_table='db1.%' will
+       be considered as "ignore the 'db1' database as a whole, as it already
+       works for CREATE DATABASE and DROP DATABASE).
+       Also skip 'mysql' database - in most cases the user will
        mess up and not exclude mysql database with the rules when
        he actually means to - in this case, he is up for a surprise if
        his priv tables get dropped and downloaded from master
@@ -830,14 +834,14 @@
       */
 
       if (!db_ok(db, replicate_do_db, replicate_ignore_db) ||
+          !db_ok_with_wild_table(db) ||
          !strcmp(db,"mysql"))
       {
        *cur_table_res = 0;
        continue;
       }
 
-      if (mysql_rm_db(thd, db, 1,1) ||
-         mysql_create_db(thd, db, 0, 1))
+      if (mysql_create_db(thd, db, HA_LEX_CREATE_IF_NOT_EXISTS, 1))
       {
        send_error(&thd->net, 0, 0);
        cleanup_mysql_results(db_res, cur_table_res - 1, table_res);