MySQL Lists are EOL. Please join:

List:Commits« Previous MessageNext Message »
From:Andrei Elkin Date:March 2 2007 10:18am
Subject:bk commit into 5.1 tree (aelkin:1.2410) BUG#22583
View as plain text  
Below is the list of changes that have just been committed into a local
5.1 repository of elkin. When elkin 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@stripped, 2007-03-02 12:18:04+02:00, aelkin@stripped +8 -0
  WL#3557 Conflict detection
  
  This one is an intermediate commit reflecting the piece of work without ndb's
  specific to pack only changed fields into rows-event.
  
  Activation of conflict detection is done with --slave-conflict-detection or
  namesake global system variable.
  Slave server stops when there is the conflict unless the user specifies 
  --slave-skip-errors=ER_REPLICATION_CONFLICT. In the latter case, the accident
  will be error-logged.
  
  The patch engages HA_ERR_RECORD_CHANGED to be reported from do_exec_row 
  about mismatch of OR and BI even though the actual code returned from lookup
  routines may vary.
  
  At the moment not-merged (perhaps not latest) patch for bug#22583 is used so that
  I will need to "subtract" these changes  after the patch will be finally pushed and remerge
  with the final one.

  mysql-test/r/rpl_row_conflicts.result@stripped, 2007-03-02 12:18:00+02:00, aelkin@stripped +762 -0
    New BitKeeper file ``mysql-test/r/rpl_row_conflicts.result''

  mysql-test/r/rpl_row_conflicts.result@stripped, 2007-03-02 12:18:00+02:00, aelkin@stripped +0 -0

  mysql-test/t/rpl_row_conflicts-slave.opt@stripped, 2007-03-02 12:18:00+02:00, aelkin@stripped +1 -0
    innodb is needed

  mysql-test/t/rpl_row_conflicts-slave.opt@stripped, 2007-03-02 12:18:00+02:00, aelkin@stripped +0 -0

  mysql-test/t/rpl_row_conflicts.test@stripped, 2007-03-02 12:18:00+02:00, aelkin@stripped +423 -0
    Testing conflicts that can happen with events generated by UPDATE/DELETE queries.
    

  mysql-test/t/rpl_row_conflicts.test@stripped, 2007-03-02 12:18:00+02:00, aelkin@stripped +0 -0

  sql/log_event.cc@stripped, 2007-03-02 12:17:58+02:00, aelkin@stripped +128 -61
    changes in row_event error handling, HA_ERR_RECORD_CHANGED is returned
    to exec_event's switch case in there is a problem to locate (update/delete) OR or
    OR was found while it should not be (write event).
    comparision BI and OR in find_and_fectch activated if table def on master is the same +
    conflict global var is 1
    
    
    This patch relies on changes for bug#22583.

  sql/mysql_priv.h@stripped, 2007-03-02 12:17:59+02:00, aelkin@stripped +2 -1
    option and global variable

  sql/mysqld.cc@stripped, 2007-03-02 12:17:59+02:00, aelkin@stripped +7 -2
    new option

  sql/set_var.cc@stripped, 2007-03-02 12:17:59+02:00, aelkin@stripped +3 -0
    global variable associated with the option

  sql/share/errmsg.txt@stripped, 2007-03-02 12:17:59+02:00, aelkin@stripped +2 -0
    New error message on user level with the intent to show it when handler level error
    returned to exec_event method means a conflict has been found.

# 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:	aelkin
# Host:	dsl-kpogw6-fe8ddc00-181.dhcp.inet.fi
# Root:	/home/elkin/MySQL/TEAM/FIXES/5.1/wl3557_conflict_detection

--- 1.263/sql/log_event.cc	2007-03-02 12:18:17 +02:00
+++ 1.264/sql/log_event.cc	2007-03-02 12:18:17 +02:00
@@ -5842,28 +5842,65 @@ int Rows_log_event::exec_event(st_relay_
       THD* old_thd= table->in_use;
       if (!table->in_use)
         table->in_use= thd;
-      error= do_exec_row(table);
+      error= do_exec_row(table);  // returns 0 | HA_ error
       table->in_use = old_thd;
+
       switch (error)
       {
-        /* Some recoverable errors */
-      case HA_ERR_RECORD_CHANGED:
-      case HA_ERR_KEY_NOT_FOUND:	/* Idempotency support: OK if
-                                           tuple does not exist */
-	error= 0;
-      case 0:
+
+      case HA_ERR_KEY_NOT_FOUND:
+	/* 
+	   Failed loopkup for OR when opt_slave_conflict_detection OFF.
+	   Because of NDB replication specifics is reset by default.
+	   Can not be benign OR-lookup failure with conflict-detection ON
+	   as the one is set to be HA_ERR_RECORD_CHANGED (find_and_fetch_row)
+	*/
+	error= opt_slave_conflict_detection? ER_KEY_NOT_FOUND : 0;
+	break;
+	
+      case HA_ERR_RECORD_CHANGED: 
+        /* 
+	   failed lookup for OR, can be only with conflict-detection ON
+	   engines' delete-update-write methods can not return it
+	   (todo: assert that)
+	*/
+	DBUG_ASSERT(opt_slave_conflict_detection);
+	error= ER_REPLICATION_CONFLICT;
 	break;
 
+      case 0:
+	break;
+	
       default:
-	slave_print_msg(ERROR_LEVEL, rli, error,
-                        "Error in %s event: row application failed",
-                        get_type_str());
-	thd->query_error= 1;
 	break;
       }
 
+      if (error)
+      {
+	char llbuf[22];
+	if (ignored_error_code(error))
+	{
+	  if (opt_slave_conflict_detection && error == ER_REPLICATION_CONFLICT)
+	    slave_print_msg(WARNING_LEVEL, rli, error,
+			    "Warning in %s event: replication conflict at %s:%s",
+			    get_type_str(),
+			    rli->event_relay_log_name,
+			    llstr(rli->event_relay_log_pos,llbuf));
+	  error= 0;
+	}
+	else
+	{
+	  slave_print_msg(ERROR_LEVEL, rli, error,
+			  "Error in %s event: row application failed at %s:%s",
+			  get_type_str(),
+			  rli->event_relay_log_name,
+			  llstr(rli->event_relay_log_pos,llbuf));
+	  thd->query_error= 1;
+	}
+      }
       row_start= row_end;
     }
+
     DBUG_EXECUTE_IF("STOP_SLAVE_after_first_Rows_event",
                     rli->abort_slave=1;);
     error= do_after_row_operations(table, error);
@@ -6480,6 +6517,7 @@ int Write_rows_log_event::do_before_row_
   thd->lex->sql_command= SQLCOM_REPLACE;
 
   table->file->extra(HA_EXTRA_IGNORE_DUP_KEY);  // Needed for ndbcluster
+  //   if (!opt_slave_conflict_detection)  TODO: ndb part of conflict detection
   table->file->extra(HA_EXTRA_WRITE_CAN_REPLACE);  // Needed for ndbcluster
   table->file->extra(HA_EXTRA_IGNORE_NO_KEY);   // Needed for ndbcluster
   /*
@@ -6668,7 +6706,10 @@ copy_extra_record_fields(TABLE *table,
              side.
 
   RETURN VALUE
-      Error code on failure, 0 on success.
+      HA_ error code on failure, 0 on success.
+      HA_ERR_RECORD_CHANGED
+         is returned with conflict-detection ON to designate there is
+	 OR with the same pkey of unique attribute
 
   DESCRIPTION
       Similar to how it is done in mysql_insert(), we first try to do
@@ -6678,7 +6719,8 @@ copy_extra_record_fields(TABLE *table,
 static int
 replace_record(THD *thd, TABLE *table,
                ulong const master_reclength,
-               uint const master_fields)
+               uint const master_fields,
+	       bool conflict_check)
 {
   DBUG_ENTER("replace_record");
   DBUG_ASSERT(table != NULL && thd != NULL);
@@ -6689,18 +6731,25 @@ replace_record(THD *thd, TABLE *table,
 
   while ((error= table->file->ha_write_row(table->record[0])))
   {
-    if (error == HA_ERR_LOCK_DEADLOCK || error == HA_ERR_LOCK_WAIT_TIMEOUT)
+    if (error == HA_ERR_LOCK_DEADLOCK ||
+	error == HA_ERR_LOCK_WAIT_TIMEOUT ||
+	conflict_check)
     {
-      table->file->print_error(error, MYF(0)); /* to check at exec_relay_log_event */
+      if (conflict_check &&
+	  (error == HA_ERR_FOUND_DUPP_KEY || error == HA_ERR_FOUND_DUPP_UNIQUE))
+	error= HA_ERR_RECORD_CHANGED;
+      table->file->print_error(error, MYF(0));
       DBUG_RETURN(error);
     }
-    if ((keynum= table->file->get_dup_key(error)) < 0)
+    if ((keynum= table->file->get_dup_key(error)) < 0) //ER(ER_DUP_ENTRY)
     {
-      /* We failed to retrieve the duplicate key */
-      DBUG_RETURN(HA_ERR_FOUND_DUPP_KEY);
+      /* We failed to retrieve the duplicate key, sever error */
+      DBUG_RETURN(error);
     }
 
     /*
+      The following is in fact conflict resolution.
+
        We need to retrieve the old row into record[1] to be able to
        either update or delete the offending record.  We either:
 
@@ -6784,7 +6833,9 @@ replace_record(THD *thd, TABLE *table,
 int Write_rows_log_event::do_exec_row(TABLE *table)
 {
   DBUG_ASSERT(table != NULL);
-  int error= replace_record(thd, table, m_master_reclength, m_width);
+  int error= replace_record(thd, table, m_master_reclength, m_width,
+			    /* mismatch of cols with master does not matter */
+			    opt_slave_conflict_detection);
   return error;
 }
 #endif /* !defined(MYSQL_CLIENT) && defined(HAVE_REPLICATION) */
@@ -6845,12 +6896,17 @@ static bool record_compare(TABLE *table)
   <code>table->record[1]</code> is undefined.  In either case,
   <code>table->record[0]</code> is undefined.
 
-  @return Zero if the row was successfully fetched into
-  <code>table->record[1]</code>, error code otherwise.
- */
+  @return
+     Zero if the row was successfully fetched into
+          <code>table->record[1]</code>,
+     HA_  error code otherwise.
+     HA_ERR_RECORD_CHANGED is returned with conflict-detection ON
+          to designate there is no OR matching BI
+*/
 
-static int find_and_fetch_row(TABLE *table, byte *key)
+static int find_and_fetch_row(TABLE *table, byte *key, bool conflict_check)
 {
+  int error;
   DBUG_ENTER("find_and_fetch_row(TABLE *table, byte *key, byte *record)");
   DBUG_PRINT("enter", ("table: 0x%lx, key: 0x%lx  record: 0x%lx",
 		       (long) table, (long) key, (long) table->record[1]));
@@ -6869,24 +6925,28 @@ static int find_and_fetch_row(TABLE *tab
       stored in table->file->ref) and the use rnd_pos() to position
       the "cursor" (i.e., record[0] in this case) at the correct row.
 
-      TODO: Add a check that the correct record has been fetched by
-      comparing with the original record. Take into account that the
-      record on the master and slave can be of different
-      length. Something along these lines should work:
-
-      ADD>>>  store_record(table,record[1]);
-              int error= table->file->rnd_pos(table->record[0], table->file->ref);
-      ADD>>>  DBUG_ASSERT(memcmp(table->record[1], table->record[0],
-                                 table->s->reclength) == 0);
+      Conflict Detection.
+      Whether the correct record ("Old Row", OR) has been fetched
+      is checked in record_compare against "Before Image" BI
 
     */
-    table->file->position(table->record[0]);
-    int error= table->file->rnd_pos(table->record[0], table->file->ref);
-    /*
-      rnd_pos() returns the record in table->record[0], so we have to
-      move it to table->record[1].
-     */
-    bmove_align(table->record[1], table->record[0], table->s->reclength);
+    store_record(table,record[1]);           // BI
+    table->file->position(table->record[0]); // filler bits ok after unpack
+    error= table->file->rnd_pos(table->record[0], table->file->ref);
+    if (conflict_check)
+    {
+      if ((!error && record_compare(table)) ||  error == HA_ERR_KEY_NOT_FOUND)
+	error= HA_ERR_RECORD_CHANGED;
+      /*
+	rnd_pos() returns OR in table->record[0], so we have to
+	move it to table->record[1].
+      */
+    }
+    else
+    {
+      if (!error)
+	bmove_align(table->record[1], table->record[0], table->s->reclength);
+    }
     DBUG_RETURN(error);
   }
 
@@ -6896,7 +6956,6 @@ static int find_and_fetch_row(TABLE *tab
 
   if (table->s->keys > 0)
   {
-    int error;
     /* We have a key: search the table using the index */
     if (!table->file->inited && (error= table->file->ha_index_init(0, FALSE)))
       DBUG_RETURN(error);
@@ -6910,24 +6969,22 @@ static int find_and_fetch_row(TABLE *tab
     DBUG_DUMP("table->record[1]", (const char *)table->record[1], table->s->reclength);
 #endif
 
-    /*
-      We need to set the null bytes to ensure that the filler bit are
-      all set when returning.  There are storage engines that just set
-      the necessary bits on the bytes and don't set the filler bits
-      correctly.
-    */
-    my_ptrdiff_t const pos=
-      table->s->null_bytes > 0 ? table->s->null_bytes - 1 : 0;
-    table->record[1][pos]= 0xFF;
     if ((error= table->file->index_read(table->record[1], key,
                                         table->key_info->key_length,
                                         HA_READ_KEY_EXACT)))
     {
+      if (conflict_check &&
+	  /* benign errors due to locate OR are */
+	  (error == HA_ERR_KEY_NOT_FOUND || error == HA_ERR_END_OF_FILE))
+	error= HA_ERR_RECORD_CHANGED;
       table->file->print_error(error, MYF(0));
       table->file->ha_index_end();
       DBUG_RETURN(error);
     }
-
+    /* patching filler bits see comments at rnd_next call */
+    my_ptrdiff_t const pos=
+      table->s->null_bytes > 0 ? table->s->null_bytes - 1 : 0;
+    table->record[1][pos]|= 256U - (1U << table->s->last_null_bit_pos);
   /*
     Don't print debug messages when running valgrind since they can
     trigger false warnings.
@@ -6953,12 +7010,13 @@ static int find_and_fetch_row(TABLE *tab
     if (table->key_info->flags & HA_NOSAME)
     {
       table->file->ha_index_end();
-      DBUG_RETURN(0);
+      if (conflict_check)
+	error= record_compare(table) == 0 ?  0: HA_ERR_RECORD_CHANGED;
+      DBUG_RETURN(error);
     }
 
     while (record_compare(table))
     {
-      int error;
       /*
         We need to set the null bytes to ensure that the filler bit
         are all set when returning.  There are storage engines that
@@ -6970,6 +7028,8 @@ static int find_and_fetch_row(TABLE *tab
       table->record[1][pos]= 0xFF;
       if ((error= table->file->index_next(table->record[1])))
       {
+	if (conflict_check && error == HA_ERR_END_OF_FILE)
+	  error= HA_ERR_RECORD_CHANGED;
 	table->file->print_error(error, MYF(0));
         table->file->ha_index_end();
 	DBUG_RETURN(error);
@@ -6984,17 +7044,15 @@ static int find_and_fetch_row(TABLE *tab
   else
   {
     int restart_count= 0; // Number of times scanning has restarted from top
-    int error;
 
     /* We don't have a key: search the table using rnd_next() */
     if ((error= table->file->ha_rnd_init(1)))
-      return error;
+      DBUG_RETURN(error);
 
     /* Continue until we find the right record or have made a full loop */
     do
     {
       error= table->file->rnd_next(table->record[1]);
-
       /*
         Patching the returned record since some storage engines do
         not set the filler bits correctly.
@@ -7020,7 +7078,7 @@ static int find_and_fetch_row(TABLE *tab
       default:
 	table->file->print_error(error, MYF(0));
         table->file->ha_rnd_end();
-	DBUG_RETURN(error);
+	DBUG_RETURN(error); // failure to *search* for OR
       }
     }
     while (restart_count < 2 && record_compare(table));
@@ -7030,10 +7088,15 @@ static int find_and_fetch_row(TABLE *tab
     */
     table->file->ha_rnd_end();
 
-    DBUG_ASSERT(error == HA_ERR_END_OF_FILE || error == 0);
+    DBUG_ASSERT(error == HA_ERR_END_OF_FILE ||
+		error == HA_ERR_RECORD_DELETED ||
+		error == 0);
+
+    if (conflict_check &&
+	(error == HA_ERR_END_OF_FILE || error == HA_ERR_RECORD_DELETED))
+      error= HA_ERR_RECORD_CHANGED;
     DBUG_RETURN(error);
   }
-
   DBUG_RETURN(0);
 }
 #endif
@@ -7154,8 +7217,10 @@ int Delete_rows_log_event::do_exec_row(T
   int error;
   DBUG_ASSERT(table != NULL);
 
-  if (!(error= find_and_fetch_row(table, m_key)))
-  { 
+  if (!(error= find_and_fetch_row(table, m_key,
+				  (table->s->fields == m_width) &&
+				  opt_slave_conflict_detection)))
+  {
     /*
       Now we should have the right row to delete.  We are using
       record[0] since it is guaranteed to point to a record with the
@@ -7312,7 +7377,9 @@ int Update_rows_log_event::do_exec_row(T
 {
   DBUG_ASSERT(table != NULL);
 
-  int error= find_and_fetch_row(table, m_key);
+  int error= find_and_fetch_row(table, m_key,
+				(table->s->fields == m_width) &&
+				opt_slave_conflict_detection);
   if (error)
     return error;
 

--- 1.471/sql/mysql_priv.h	2007-03-02 12:18:17 +02:00
+++ 1.472/sql/mysql_priv.h	2007-03-02 12:18:17 +02:00
@@ -1593,7 +1593,8 @@ extern bool volatile abort_loop, shutdow
 extern uint volatile thread_count, thread_running, global_read_lock;
 extern my_bool opt_sql_bin_update, opt_safe_user_create, opt_no_mix_types;
 extern my_bool opt_safe_show_db, opt_local_infile, opt_myisam_use_mmap;
-extern my_bool opt_slave_compressed_protocol, use_temp_pool;
+extern my_bool opt_slave_compressed_protocol, opt_slave_conflict_detection;
+extern my_bool use_temp_pool;
 extern my_bool opt_readonly, lower_case_file_system;
 extern my_bool opt_enable_named_pipe, opt_sync_frm, opt_allow_suspicious_udfs;
 extern my_bool opt_secure_auth;

--- 1.604/sql/mysqld.cc	2007-03-02 12:18:17 +02:00
+++ 1.605/sql/mysqld.cc	2007-03-02 12:18:17 +02:00
@@ -341,6 +341,7 @@ static pthread_cond_t COND_thread_cache,
 
 bool opt_update_log, opt_bin_log;
 my_bool opt_log, opt_slow_log;
+my_bool opt_slave_conflict_detection;
 ulong log_output_options;
 my_bool opt_log_queries_not_using_indexes= 0;
 bool opt_error_log= IF_WIN(1,0);
@@ -4872,7 +4873,8 @@ enum options_mysqld
   OPT_GENERAL_LOG,
   OPT_SLOW_LOG,
   OPT_MERGE,
-  OPT_INNODB_ROLLBACK_ON_TIMEOUT
+  OPT_INNODB_ROLLBACK_ON_TIMEOUT,
+  OPT_SLAVE_CONFLICT_DETECTION
 };
 
 
@@ -6200,6 +6202,9 @@ The minimum value for this variable is 4
    "before giving up and stopping.",
    (gptr*) &slave_trans_retries, (gptr*) &slave_trans_retries, 0,
    GET_ULONG, REQUIRED_ARG, 10L, 0L, (longlong) ULONG_MAX, 0, 1, 0},
+  {"slave_conflict_detection", OPT_SLAVE_CONFLICT_DETECTION,
+   "Enable|disable detecting of data inconsistency on slave at execution of replication event. Default disabled.", (gptr*) &opt_slave_conflict_detection,
+   (gptr*) &opt_slave_conflict_detection, 0, GET_BOOL, OPT_ARG, 0, 0, 0, 0, 0, 0},
 #endif /* HAVE_REPLICATION */
   {"slow_launch_time", OPT_SLOW_LAUNCH_TIME,
    "If creating the thread takes longer than this value (in seconds), the Slow_launch_threads counter will be incremented.",
@@ -7013,6 +7018,7 @@ static void mysql_init_variables(void)
     master_ssl_capath= master_ssl_cipher= 0;
   report_user= report_password = report_host= 0;	/* TO BE DELETED */
   opt_relay_logname= opt_relaylog_index_name= 0;
+  opt_slave_conflict_detection= FALSE;
 
   /* Variables in libraries */
   charsets_dir= 0;
@@ -7037,7 +7043,6 @@ static void mysql_init_variables(void)
     when collecting index statistics for MyISAM tables.
   */
   global_system_variables.myisam_stats_method= MI_STATS_METHOD_NULLS_NOT_EQUAL;
-
   /* Variables that depends on compile options */
 #ifndef DBUG_OFF
   default_dbug_option=IF_WIN("d:t:i:O,\\mysqld.trace",
--- New file ---
+++ mysql-test/r/rpl_row_conflicts.result	07/03/02 12:18:00
stop slave;
drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9;
reset master;
reset slave;
drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9;
start slave;
set @@session.binlog_format=row;
drop table if exists  t1,t2,t3,t4;
CREATE TABLE t1i (a int, b bit(12)) ENGINE= innodb;
CREATE TABLE t1m (a int primary key, b bit(12)) ENGINE= myisam;
CREATE TABLE t2i (a int, b int) ENGINE= innodb;
CREATE TABLE t2m (a int, b int) ENGINE= myisam;
CREATE TABLE t3i (a int primary key, b int) ENGINE= innodb;
CREATE TABLE t3m (a int primary key, b int) ENGINE= myisam;
CREATE TABLE t4i (a int unique, b int) ENGINE= innodb;
insert into t1i values (1,b'111111111111'),(2,b'011111111111'),(1,b'000000000000');
insert into t1m values (1,b'111111111111'),(2,b'011111111111'),(3,b'000000000000');
insert into t2i values (1,1),(2,2),(1,1);
insert into t2m values (1,1),(2,2),(1,1);
insert into t3i values (1,1),(2,2),(3,3);
insert into t3m values (1,1),(2,2),(3,3);
update t3m set b=-1 where a=1;
select * from t3m;
a	b
1	-1
2	2
3	3
update t3m set b=-2 where a=1;
select * from t3m;
a	b
1	-2
2	2
3	3
select * from t3m;
a	b
1	-2
2	2
3	3
show slave status;;
Slave_IO_State	#
Master_Host	#
Master_User	#
Master_Port	#
Connect_Retry	#
Master_Log_File	#
Read_Master_Log_Pos	#
Relay_Log_File	#
Relay_Log_Pos	#
Relay_Master_Log_File	#
Slave_IO_Running	Yes
Slave_SQL_Running	Yes
Replicate_Do_DB	#
Replicate_Ignore_DB	#
Replicate_Do_Table	#
Replicate_Ignore_Table	#
Replicate_Wild_Do_Table	#
Replicate_Wild_Ignore_Table	#
Last_Errno	0
Last_Error	
Skip_Counter	#
Exec_Master_Log_Pos	#
Relay_Log_Space	#
Until_Condition	#
Until_Log_File	#
Until_Log_Pos	#
Master_SSL_Allowed	#
Master_SSL_CA_File	#
Master_SSL_CA_Path	#
Master_SSL_Cert	#
Master_SSL_Cipher	#
Master_SSL_Key	#
Seconds_Behind_Master	#
update t3m set b=1 where a=1;
update t3m set b=1 where a=1;
select * from t3m;
a	b
1	1
2	2
3	3
set @@global.slave_conflict_detection=1;
update t3m set b=-1 where a=1;
select * from t3m;
a	b
1	-1
2	2
3	3
update t3m set b=-2 where a=1;
select * from t3m;
a	b
1	-2
2	2
3	3
select * from t3m;
a	b
1	-1
2	2
3	3
show slave status;;
Slave_IO_State	#
Master_Host	#
Master_User	#
Master_Port	#
Connect_Retry	#
Master_Log_File	#
Read_Master_Log_Pos	#
Relay_Log_File	#
Relay_Log_Pos	#
Relay_Master_Log_File	#
Slave_IO_Running	Yes
Slave_SQL_Running	No
Replicate_Do_DB	#
Replicate_Ignore_DB	#
Replicate_Do_Table	#
Replicate_Ignore_Table	#
Replicate_Wild_Do_Table	#
Replicate_Wild_Ignore_Table	#
Last_Errno	1583
Last_Error	Error in Update_rows event: error during transaction execution on table test.t3m
Skip_Counter	#
Exec_Master_Log_Pos	#
Relay_Log_Space	#
Until_Condition	#
Until_Log_File	#
Until_Log_Pos	#
Master_SSL_Allowed	#
Master_SSL_CA_File	#
Master_SSL_CA_Path	#
Master_SSL_Cert	#
Master_SSL_Cipher	#
Master_SSL_Key	#
Seconds_Behind_Master	#
update t3m set b=1 where a=1;
start slave sql_thread;
update t3m set b=1 where a=1;
select * from t3m;
a	b
1	1
2	2
3	3
select * from t3m;
a	b
1	1
2	2
3	3
delete from t3m where a=1;
select * from t3m;
a	b
2	2
3	3
delete from t3m where a=1;
select * from t3m;
a	b
2	2
3	3
select * from t3m;
a	b
2	2
3	3
show slave status;;
Slave_IO_State	#
Master_Host	#
Master_User	#
Master_Port	#
Connect_Retry	#
Master_Log_File	#
Read_Master_Log_Pos	#
Relay_Log_File	#
Relay_Log_Pos	#
Relay_Master_Log_File	#
Slave_IO_Running	Yes
Slave_SQL_Running	No
Replicate_Do_DB	#
Replicate_Ignore_DB	#
Replicate_Do_Table	#
Replicate_Ignore_Table	#
Replicate_Wild_Do_Table	#
Replicate_Wild_Ignore_Table	#
Last_Errno	1583
Last_Error	Error in Delete_rows event: error during transaction execution on table test.t3m
Skip_Counter	#
Exec_Master_Log_Pos	#
Relay_Log_Space	#
Until_Condition	#
Until_Log_File	#
Until_Log_Pos	#
Master_SSL_Allowed	#
Master_SSL_CA_File	#
Master_SSL_CA_Path	#
Master_SSL_Cert	#
Master_SSL_Cipher	#
Master_SSL_Key	#
Seconds_Behind_Master	#
insert into t3m values (1,1);
start slave sql_thread;
delete from t3m where a=1;
select * from t3m;
a	b
2	2
3	3
select * from t3m;
a	b
2	2
3	3
update t3i set b=-1 where a=1;
select * from t3i;
a	b
1	-1
2	2
3	3
update t3i set b=-2 where a=1;
select * from t3i;
a	b
1	-2
2	2
3	3
select * from t3i;
a	b
1	-1
2	2
3	3
show slave status;;
Slave_IO_State	#
Master_Host	#
Master_User	#
Master_Port	#
Connect_Retry	#
Master_Log_File	#
Read_Master_Log_Pos	#
Relay_Log_File	#
Relay_Log_Pos	#
Relay_Master_Log_File	#
Slave_IO_Running	Yes
Slave_SQL_Running	No
Replicate_Do_DB	#
Replicate_Ignore_DB	#
Replicate_Do_Table	#
Replicate_Ignore_Table	#
Replicate_Wild_Do_Table	#
Replicate_Wild_Ignore_Table	#
Last_Errno	1583
Last_Error	Error in Update_rows event: error during transaction execution on table test.t3i
Skip_Counter	#
Exec_Master_Log_Pos	#
Relay_Log_Space	#
Until_Condition	#
Until_Log_File	#
Until_Log_Pos	#
Master_SSL_Allowed	#
Master_SSL_CA_File	#
Master_SSL_CA_Path	#
Master_SSL_Cert	#
Master_SSL_Cipher	#
Master_SSL_Key	#
Seconds_Behind_Master	#
update t3i set b=1 where a=1;
start slave sql_thread;
update t3i set b=1 where a=1;
select * from t3i;
a	b
1	1
2	2
3	3
select * from t3i;
a	b
1	1
2	2
3	3
delete from t3i where a=1;
select * from t3i;
a	b
2	2
3	3
delete from t3i where a=1;
select * from t3i;
a	b
2	2
3	3
select * from t3i;
a	b
2	2
3	3
show slave status;;
Slave_IO_State	#
Master_Host	#
Master_User	#
Master_Port	#
Connect_Retry	#
Master_Log_File	#
Read_Master_Log_Pos	#
Relay_Log_File	#
Relay_Log_Pos	#
Relay_Master_Log_File	#
Slave_IO_Running	Yes
Slave_SQL_Running	No
Replicate_Do_DB	#
Replicate_Ignore_DB	#
Replicate_Do_Table	#
Replicate_Ignore_Table	#
Replicate_Wild_Do_Table	#
Replicate_Wild_Ignore_Table	#
Last_Errno	1583
Last_Error	Error in Delete_rows event: error during transaction execution on table test.t3i
Skip_Counter	#
Exec_Master_Log_Pos	#
Relay_Log_Space	#
Until_Condition	#
Until_Log_File	#
Until_Log_Pos	#
Master_SSL_Allowed	#
Master_SSL_CA_File	#
Master_SSL_CA_Path	#
Master_SSL_Cert	#
Master_SSL_Cipher	#
Master_SSL_Key	#
Seconds_Behind_Master	#
insert into t3i values (1,1);
start slave sql_thread;
delete from t3i where a=1;
select * from t3i;
a	b
2	2
3	3
select * from t3i;
a	b
2	2
3	3
update t2i set b=-1 where a=1;
select * from t2i;
a	b
1	-1
2	2
1	-1
update t2i set b=-2 where a=1;
select * from t2i;
a	b
1	-2
2	2
1	-2
select * from t2i;
a	b
1	-1
2	2
1	-1
show slave status;;
Slave_IO_State	#
Master_Host	#
Master_User	#
Master_Port	#
Connect_Retry	#
Master_Log_File	#
Read_Master_Log_Pos	#
Relay_Log_File	#
Relay_Log_Pos	#
Relay_Master_Log_File	#
Slave_IO_Running	Yes
Slave_SQL_Running	No
Replicate_Do_DB	#
Replicate_Ignore_DB	#
Replicate_Do_Table	#
Replicate_Ignore_Table	#
Replicate_Wild_Do_Table	#
Replicate_Wild_Ignore_Table	#
Last_Errno	1583
Last_Error	Error in Update_rows event: error during transaction execution on table test.t2i
Skip_Counter	#
Exec_Master_Log_Pos	#
Relay_Log_Space	#
Until_Condition	#
Until_Log_File	#
Until_Log_Pos	#
Master_SSL_Allowed	#
Master_SSL_CA_File	#
Master_SSL_CA_Path	#
Master_SSL_Cert	#
Master_SSL_Cipher	#
Master_SSL_Key	#
Seconds_Behind_Master	#
update t2i set b=1 where a=1;
start slave sql_thread;
update t2i set b=1 where a=1;
select * from t2i;
a	b
1	1
2	2
1	1
select * from t2i;
a	b
1	1
2	2
1	1
delete from t2i where a=2;
select * from t2i;
a	b
1	1
1	1
delete from t2i where a=2;
select * from t2i;
a	b
1	1
1	1
select * from t2i;
a	b
1	1
1	1
show slave status;;
Slave_IO_State	#
Master_Host	#
Master_User	#
Master_Port	#
Connect_Retry	#
Master_Log_File	#
Read_Master_Log_Pos	#
Relay_Log_File	#
Relay_Log_Pos	#
Relay_Master_Log_File	#
Slave_IO_Running	Yes
Slave_SQL_Running	No
Replicate_Do_DB	#
Replicate_Ignore_DB	#
Replicate_Do_Table	#
Replicate_Ignore_Table	#
Replicate_Wild_Do_Table	#
Replicate_Wild_Ignore_Table	#
Last_Errno	1583
Last_Error	Error in Delete_rows event: error during transaction execution on table test.t2i
Skip_Counter	#
Exec_Master_Log_Pos	#
Relay_Log_Space	#
Until_Condition	#
Until_Log_File	#
Until_Log_Pos	#
Master_SSL_Allowed	#
Master_SSL_CA_File	#
Master_SSL_CA_Path	#
Master_SSL_Cert	#
Master_SSL_Cipher	#
Master_SSL_Key	#
Seconds_Behind_Master	#
insert into t2i values (2,2);
start slave sql_thread;
delete from t2i where a=1;
select * from t2i;
a	b
select * from t2i;
a	b
delete from t2m where a=2;
select * from t2m;
a	b
1	1
1	1
delete from t2m where a=2;
select * from t2m;
a	b
1	1
1	1
select * from t2m;
a	b
1	1
1	1
show slave status;;
Slave_IO_State	#
Master_Host	#
Master_User	#
Master_Port	#
Connect_Retry	#
Master_Log_File	#
Read_Master_Log_Pos	#
Relay_Log_File	#
Relay_Log_Pos	#
Relay_Master_Log_File	#
Slave_IO_Running	Yes
Slave_SQL_Running	No
Replicate_Do_DB	#
Replicate_Ignore_DB	#
Replicate_Do_Table	#
Replicate_Ignore_Table	#
Replicate_Wild_Do_Table	#
Replicate_Wild_Ignore_Table	#
Last_Errno	1583
Last_Error	Error in Delete_rows event: error during transaction execution on table test.t2m
Skip_Counter	#
Exec_Master_Log_Pos	#
Relay_Log_Space	#
Until_Condition	#
Until_Log_File	#
Until_Log_Pos	#
Master_SSL_Allowed	#
Master_SSL_CA_File	#
Master_SSL_CA_Path	#
Master_SSL_Cert	#
Master_SSL_Cipher	#
Master_SSL_Key	#
Seconds_Behind_Master	#
insert into t2m values (2,2);
start slave sql_thread;
delete from t2m where a=1;
select * from t2m;
a	b
select * from t2m;
a	b
select a,hex(b) from t1i;
a	hex(b)
1	FFF
2	7FF
1	0
update t1i set b=b'000100010001' where a=2;
select a,hex(b) from t1i;
a	hex(b)
1	FFF
2	111
1	0
update t1i set b=b'000100010001' where a=2;
select a,hex(b) from t1i;
a	hex(b)
1	FFF
2	111
1	0
show slave status;;
Slave_IO_State	#
Master_Host	#
Master_User	#
Master_Port	#
Connect_Retry	#
Master_Log_File	#
Read_Master_Log_Pos	#
Relay_Log_File	#
Relay_Log_Pos	#
Relay_Master_Log_File	#
Slave_IO_Running	Yes
Slave_SQL_Running	No
Replicate_Do_DB	#
Replicate_Ignore_DB	#
Replicate_Do_Table	#
Replicate_Ignore_Table	#
Replicate_Wild_Do_Table	#
Replicate_Wild_Ignore_Table	#
Last_Errno	1583
Last_Error	Error in Update_rows event: error during transaction execution on table test.t1i
Skip_Counter	#
Exec_Master_Log_Pos	#
Relay_Log_Space	#
Until_Condition	#
Until_Log_File	#
Until_Log_Pos	#
Master_SSL_Allowed	#
Master_SSL_CA_File	#
Master_SSL_CA_Path	#
Master_SSL_Cert	#
Master_SSL_Cipher	#
Master_SSL_Key	#
Seconds_Behind_Master	#
update t1i set b=b'011111111111' where a=2;
select a,hex(b) from t1i;
a	hex(b)
1	FFF
2	7FF
1	0
start slave sql_thread;
update t1i set b=b'011111111111' where a=2;
select a,hex(b) from t1i;
a	hex(b)
1	FFF
2	7FF
1	0
select a,hex(b) from t1m;
a	hex(b)
1	FFF
2	7FF
3	0
select a,hex(b) from t1m;
a	hex(b)
1	FFF
2	7FF
3	0
update t1m set b=b'000100010001' where a=2;
select a,hex(b) from t1m;
a	hex(b)
1	FFF
2	111
3	0
update t1m set b=b'000100010001' where a=2;
select a,hex(b) from t1m;
a	hex(b)
1	FFF
2	111
3	0
show slave status;;
Slave_IO_State	#
Master_Host	#
Master_User	#
Master_Port	#
Connect_Retry	#
Master_Log_File	#
Read_Master_Log_Pos	#
Relay_Log_File	#
Relay_Log_Pos	#
Relay_Master_Log_File	master-bin.000001
Slave_IO_Running	Yes
Slave_SQL_Running	#
Replicate_Do_DB	#
Replicate_Ignore_DB	#
Replicate_Do_Table	#
Replicate_Ignore_Table	#
Replicate_Wild_Do_Table	#
Replicate_Wild_Ignore_Table	#
Last_Errno	1583
Last_Error	Error in Update_rows event: error during transaction execution on table test.t1m
Skip_Counter	#
Exec_Master_Log_Pos	#
Relay_Log_Space	#
Until_Condition	#
Until_Log_File	#
Until_Log_Pos	#
Master_SSL_Allowed	#
Master_SSL_CA_File	#
Master_SSL_CA_Path	#
Master_SSL_Cert	#
Master_SSL_Cipher	#
Master_SSL_Key	#
Seconds_Behind_Master	#
update t1m set b=b'011111111111' where a=2;
select a,hex(b) from t1m;
a	hex(b)
1	FFF
2	7FF
3	0
start slave sql_thread;
update t1m set b=b'011111111111' where a=2;
select a,hex(b) from t1m;
a	hex(b)
1	FFF
2	7FF
3	0
select a,hex(b) from t1m;
a	hex(b)
1	FFF
2	7FF
3	0
select * from t3i;
a	b
2	2
3	3
delete from t3i where a=3;
insert into t3i values (3,3);
insert into t3i values (3,3);
show slave status;;
Slave_IO_State	#
Master_Host	#
Master_User	#
Master_Port	#
Connect_Retry	#
Master_Log_File	#
Read_Master_Log_Pos	#
Relay_Log_File	#
Relay_Log_Pos	#
Relay_Master_Log_File	#
Slave_IO_Running	Yes
Slave_SQL_Running	No
Replicate_Do_DB	#
Replicate_Ignore_DB	#
Replicate_Do_Table	#
Replicate_Ignore_Table	#
Replicate_Wild_Do_Table	#
Replicate_Wild_Ignore_Table	#
Last_Errno	1583
Last_Error	Error in Write_rows event: error during transaction execution on table test.t3i
Skip_Counter	#
Exec_Master_Log_Pos	#
Relay_Log_Space	#
Until_Condition	#
Until_Log_File	#
Until_Log_Pos	#
Master_SSL_Allowed	#
Master_SSL_CA_File	#
Master_SSL_CA_Path	#
Master_SSL_Cert	#
Master_SSL_Cipher	#
Master_SSL_Key	#
Seconds_Behind_Master	#
delete from t3i where a=3;
start slave sql_thread;
replace t3i values (3,0);
select * from t3i;
a	b
2	2
3	0
select * from t3m;
a	b
2	2
3	3
delete from t3m where a=3;
insert into t3m values (3,3);
insert into t3m values (3,3);
show slave status;;
Slave_IO_State	#
Master_Host	#
Master_User	#
Master_Port	#
Connect_Retry	#
Master_Log_File	#
Read_Master_Log_Pos	#
Relay_Log_File	#
Relay_Log_Pos	#
Relay_Master_Log_File	#
Slave_IO_Running	Yes
Slave_SQL_Running	No
Replicate_Do_DB	#
Replicate_Ignore_DB	#
Replicate_Do_Table	#
Replicate_Ignore_Table	#
Replicate_Wild_Do_Table	#
Replicate_Wild_Ignore_Table	#
Last_Errno	1583
Last_Error	Error in Write_rows event: error during transaction execution on table test.t3m
Skip_Counter	#
Exec_Master_Log_Pos	#
Relay_Log_Space	#
Until_Condition	#
Until_Log_File	#
Until_Log_Pos	#
Master_SSL_Allowed	#
Master_SSL_CA_File	#
Master_SSL_CA_Path	#
Master_SSL_Cert	#
Master_SSL_Cipher	#
Master_SSL_Key	#
Seconds_Behind_Master	#
delete from t3m where a=3;
start slave sql_thread;
replace t3m values (3,0);
select * from t3m;
a	b
2	2
3	0
insert into t4i values (1,1);
insert into t4i values (2,2);
insert into t4i values (2,2);
delete from t4i where a=2;
start slave sql_thread;
insert into t2i values (1,1),(2,2);
insert into t2i values (2,2);
insert into t2i values (1,1);
select * from t2i;
a	b
1	1
2	2
2	2
1	1
insert into t2i values (1,1),(2,2);
insert into t2i values (2,2);
insert into t2i values (1,1);
select * from t2i;
a	b
1	1
2	2
2	2
1	1
1	1
2	2
2	2
1	1
drop table t1i,t1m,t2i,t2m,t3i,t3m;

--- New file ---
+++ mysql-test/t/rpl_row_conflicts-slave.opt	07/03/02 12:18:00
--innodb

--- New file ---
+++ mysql-test/t/rpl_row_conflicts.test	07/03/02 12:18:00
-- source include/have_innodb.inc
-- source include/have_binlog_format_row.inc
-- source include/master-slave.inc

#
# Basic tests of row-level replication conflicts
# detection (WL3557)
#
#

connection master;
set @@session.binlog_format=row;
--disable_warnings
drop table if exists  t1,t2,t3,t4;
--enable_warnings
CREATE TABLE t1i (a int, b bit(12)) ENGINE= innodb;
CREATE TABLE t1m (a int primary key, b bit(12)) ENGINE= myisam;
CREATE TABLE t2i (a int, b int) ENGINE= innodb;
CREATE TABLE t2m (a int, b int) ENGINE= myisam;
CREATE TABLE t3i (a int primary key, b int) ENGINE= innodb;
CREATE TABLE t3m (a int primary key, b int) ENGINE= myisam;
CREATE TABLE t4i (a int unique, b int) ENGINE= innodb;

insert into t1i values (1,b'111111111111'),(2,b'011111111111'),(1,b'000000000000');
insert into t1m values (1,b'111111111111'),(2,b'011111111111'),(3,b'000000000000');

insert into t2i values (1,1),(2,2),(1,1);
insert into t2m values (1,1),(2,2),(1,1);
insert into t3i values (1,1),(2,2),(3,3);
insert into t3m values (1,1),(2,2),(3,3);

#
# 1. myisam table with primary key (index_read)
#    NO DETECTION at the beginning
#

sync_slave_with_master;
#connection slave;
update t3m set b=-1 where a=1;
select * from t3m;

connection master;
update t3m set b=-2 where a=1;
select * from t3m;

sync_slave_with_master;
#connection slave;
select * from t3m;
# instersting are 4 cols:
# 10 Slave_IO_Running, 11 Slave_SQL_Running, 18 Last_Errno, 19 Last_Error  

--replace_column 1 # 2 # 3 # 4 # 5 # 6 # 7 # 8 # 9 # 10 # 13 # 14 # 15 # 16 # 17 # 18 # 21 # 22 # 23 # 24 # 25 # 26 # 27 # 28 # 29 # 30 # 31 # 32 # 33 #
--query_vertical show slave status;
update t3m set b=1 where a=1;

connection master;
update t3m set b=1 where a=1;

sync_slave_with_master;
#connection slave;
select * from t3m;


# now with DETECTION ON will stop slave
# connection slave;
set @@global.slave_conflict_detection=1;
update t3m set b=-1 where a=1;
select * from t3m;

connection master;
update t3m set b=-2 where a=1;
select * from t3m;

connection slave;
wait_for_slave_to_stop; # conflict must be detected
select * from t3m;
--replace_column 1 # 2 # 3 # 4 # 5 # 6 # 7 # 8 # 9 # 10 # 13 # 14 # 15 # 16 # 17 # 18 # 21 # 22 # 23 # 24 # 25 # 26 # 27 # 28 # 29 # 30 # 31 # 32 # 33 #
--query_vertical show slave status;
update t3m set b=1 where a=1; # user fixes Old Row
start slave sql_thread;

connection master;
update t3m set b=1 where a=1;
select * from t3m;

sync_slave_with_master;
#connection slave;
select * from t3m; # must be same content with master

# conflict with detete, same as above
connection slave;
delete from t3m where a=1;
select * from t3m;

connection master;
delete from t3m where a=1;
select * from t3m;

connection slave;
wait_for_slave_to_stop;

select * from t3m;
--replace_column 1 # 2 # 3 # 4 # 5 # 6 # 7 # 8 # 9 # 10 # 13 # 14 # 15 # 16 # 17 # 18 # 21 # 22 # 23 # 24 # 25 # 26 # 27 # 28 # 29 # 30 # 31 # 32 # 33 #
--query_vertical show slave status;
insert into t3m values (1,1);

start slave sql_thread;

connection master;
delete from t3m where a=1;
select * from t3m;

sync_slave_with_master;
#connection slave;
select * from t3m; # must be same content with master

#
# 2. innodb table with primary key (position, rnd_pos)
#    (copy and paste of 1. with detection)
#
# connection slave;
update t3i set b=-1 where a=1;
select * from t3i;

connection master;
update t3i set b=-2 where a=1;
select * from t3i;

connection slave;
wait_for_slave_to_stop;
select * from t3i;
--replace_column 1 # 2 # 3 # 4 # 5 # 6 # 7 # 8 # 9 # 10 # 13 # 14 # 15 # 16 # 17 # 18 # 21 # 22 # 23 # 24 # 25 # 26 # 27 # 28 # 29 # 30 # 31 # 32 # 33 #
--query_vertical show slave status;
update t3i set b=1 where a=1; # user fixes Old Row
start slave sql_thread;

connection master;
update t3i set b=1 where a=1;
select * from t3i;

sync_slave_with_master;
#connection slave;
select * from t3i; # must be same content with master

# conflict with detete, same as above
# connection slave;
delete from t3i where a=1;
select * from t3i;

connection master;
delete from t3i where a=1;
select * from t3i;

connection slave;
wait_for_slave_to_stop;

select * from t3i;
--replace_column 1 # 2 # 3 # 4 # 5 # 6 # 7 # 8 # 9 # 10 # 13 # 14 # 15 # 16 # 17 # 18 # 21 # 22 # 23 # 24 # 25 # 26 # 27 # 28 # 29 # 30 # 31 # 32 # 33 #
--query_vertical show slave status;
insert into t3i values (1,1);

start slave sql_thread;

connection master;
delete from t3i where a=1;
select * from t3i;

sync_slave_with_master;
#connection slave;
select * from t3i; # must be same content with master

#
# 3. no primary keys case
#

# U. update
# connection slave;
update t2i set b=-1 where a=1;
select * from t2i;

connection master;
update t2i set b=-2 where a=1;
select * from t2i;

connection slave;
wait_for_slave_to_stop;
select * from t2i;
--replace_column 1 # 2 # 3 # 4 # 5 # 6 # 7 # 8 # 9 # 10 # 13 # 14 # 15 # 16 # 17 # 18 # 21 # 22 # 23 # 24 # 25 # 26 # 27 # 28 # 29 # 30 # 31 # 32 # 33 #
--query_vertical show slave status;
update t2i set b=1 where a=1; # user fixes Old Row
start slave sql_thread;

connection master;
update t2i set b=1 where a=1;
select * from t2i;

sync_slave_with_master;
#connection slave;
select * from t2i; # must be same content with master

# D. DELETE
#   i. innodb
# connection slave;
delete from t2i where a=2;
select * from t2i;

connection master;
delete from t2i where a=2;
select * from t2i;

connection slave;
wait_for_slave_to_stop;

select * from t2i;
--replace_column 1 # 2 # 3 # 4 # 5 # 6 # 7 # 8 # 9 # 10 # 13 # 14 # 15 # 16 # 17 # 18 # 21 # 22 # 23 # 24 # 25 # 26 # 27 # 28 # 29 # 30 # 31 # 32 # 33 #
--query_vertical show slave status;
insert into t2i values (2,2);

start slave sql_thread;

connection master;
delete from t2i where a=1;
select * from t2i;

sync_slave_with_master;

#connection slave;
select * from t2i; # must be same content with master

#   m. myisam
# connection slave;
delete from t2m where a=2;
select * from t2m;

connection master;
delete from t2m where a=2;
select * from t2m;

connection slave;
wait_for_slave_to_stop;

select * from t2m;
--replace_column 1 # 2 # 3 # 4 # 5 # 6 # 7 # 8 # 9 # 10 # 13 # 14 # 15 # 16 # 17 # 18 # 21 # 22 # 23 # 24 # 25 # 26 # 27 # 28 # 29 # 30 # 31 # 32 # 33 #
--query_vertical show slave status;
insert into t2m values (2,2);

start slave sql_thread;

connection master;
delete from t2m where a=1;
select * from t2m;

sync_slave_with_master;
#connection slave;
select * from t2m; # must be same content with master


#
# 4. Bit-fields
#

# i. innodb
# connection slave;
select a,hex(b) from t1i;
update t1i set b=b'000100010001' where a=2; ### bug (in unpack?) with a=1
select a,hex(b) from t1i;

connection master;
update t1i set b=b'000100010001' where a=2;

connection slave;
wait_for_slave_to_stop;
select a,hex(b) from t1i;
--replace_column 1 # 2 # 3 # 4 # 5 # 6 # 7 # 8 # 9 # 10 # 13 # 14 # 15 # 16 # 17 # 18 # 21 # 22 # 23 # 24 # 25 # 26 # 27 # 28 # 29 # 30 # 31 # 32 # 33 #
--query_vertical show slave status;
update t1i set b=b'011111111111' where a=2; # user fixes Old Row
select a,hex(b) from t1i;
start slave sql_thread;

connection master;
update t1i set b=b'011111111111' where a=2;
select a,hex(b) from t1i;

sync_slave_with_master;

# m. myisam
#connection slave;
select a,hex(b) from t1m;

select a,hex(b) from t1m;
update t1m set b=b'000100010001' where a=2; ### bug (in unpack?) with a=1
select a,hex(b) from t1m;

connection master;
update t1m set b=b'000100010001' where a=2;

connection slave;
wait_for_slave_to_stop;
select a,hex(b) from t1m;
--replace_column 1 # 2 # 3 # 4 # 5 # 6 # 7 # 8 # 9 # 12 # 13 # 14 # 15 # 16 # 17 # 18 # 21 # 22 # 23 # 24 # 25 # 26 # 27 # 28 # 29 # 30 # 31 # 32 # 33 #
--query_vertical show slave status;
update t1m set b=b'011111111111' where a=2; # user fixes Old Row
select a,hex(b) from t1m;
start slave sql_thread;

connection master;
update t1m set b=b'011111111111' where a=2;
select a,hex(b) from t1m;

sync_slave_with_master;
#connection slave;
select a,hex(b) from t1m;


#
# REPLACE/INSERT
# 

connection master;
select * from t3i;
delete from t3i where a=3;

sync_slave_with_master;
#connection slave;
insert into t3i values (3,3);

connection master;
insert into t3i values (3,3);

connection slave;
wait_for_slave_to_stop;
--replace_column 1 # 2 # 3 # 4 # 5 # 6 # 7 # 8 # 9 # 10 # 13 # 14 # 15 # 16 # 17 # 18 # 21 # 22 # 23 # 24 # 25 # 26 # 27 # 28 # 29 # 30 # 31 # 32 # 33 #
--query_vertical show slave status;
delete from t3i where a=3;
start slave sql_thread;

connection master;
replace t3i values (3,0);

sync_slave_with_master;
#connection slave;
select * from t3i;

connection master;
select * from t3m;
delete from t3m where a=3;

sync_slave_with_master;
#connection slave;
insert into t3m values (3,3);

connection master;
insert into t3m values (3,3);

connection slave;
wait_for_slave_to_stop;
--replace_column 1 # 2 # 3 # 4 # 5 # 6 # 7 # 8 # 9 # 10 # 13 # 14 # 15 # 16 # 17 # 18 # 21 # 22 # 23 # 24 # 25 # 26 # 27 # 28 # 29 # 30 # 31 # 32 # 33 #
--query_vertical show slave status;
delete from t3m where a=3;
start slave sql_thread;

connection master;
replace t3m values (3,0);

sync_slave_with_master;
#connection slave;
select * from t3m;

## table with unique attribute, consequences same as with prim key

connection master;
insert into t4i values (1,1);

sync_slave_with_master;
#connection slave;
insert into t4i values (2,2);
connection master;
insert into t4i values (2,2);

connection slave;
wait_for_slave_to_stop;
delete from t4i where a=2;
start slave sql_thread;

## no conflict to insert into non-unique 

connection master;
insert into t2i values (1,1),(2,2);

sync_slave_with_master;
#connection slave;
insert into t2i values (2,2);

connection master;
insert into t2i values (1,1);

sync_slave_with_master;
#connection slave;
select * from t2i;

## no conflict to insert into non-unique 

connection master;
insert into t2i values (1,1),(2,2);

sync_slave_with_master;
#connection slave;
insert into t2i values (2,2);

connection master;
insert into t2i values (1,1);

sync_slave_with_master;
#connection slave;
select * from t2i;


#
# cleanup
#
connection master;
drop table t1i,t1m,t2i,t2m,t3i,t3m;
sync_slave_with_master;


--- 1.139/sql/share/errmsg.txt	2007-03-02 12:18:17 +02:00
+++ 1.140/sql/share/errmsg.txt	2007-03-02 12:18:17 +02:00
@@ -6021,3 +6021,5 @@ ER_NATIVE_FCT_NAME_COLLISION
         eng "This function '%-.64s' has the same name as a native function."
 ER_BINLOG_PURGE_EMFILE
         eng "Too many files opened, please execute the command again"
+ER_REPLICATION_CONFLICT
+        eng "Replication conflict: a row matching the event's before image is not found"

--- 1.211/sql/set_var.cc	2007-03-02 12:18:17 +02:00
+++ 1.212/sql/set_var.cc	2007-03-02 12:18:17 +02:00
@@ -429,6 +429,8 @@ sys_var_thd_ulong	sys_sort_buffer("sort_
 					&SV::sortbuff_size);
 sys_var_thd_sql_mode    sys_sql_mode("sql_mode",
                                      &SV::sql_mode);
+sys_var_bool_ptr	sys_slave_conflict_detection("slave_conflict_detection",
+						     &opt_slave_conflict_detection);
 #ifdef HAVE_OPENSSL
 extern char *opt_ssl_ca, *opt_ssl_capath, *opt_ssl_cert, *opt_ssl_cipher,
             *opt_ssl_key;
@@ -977,6 +979,7 @@ SHOW_VAR init_vars[]= {
   {sys_slave_net_timeout.name,(char*) &sys_slave_net_timeout,	    SHOW_SYS},
   {"slave_skip_errors",       (char*) &show_slave_skip_errors,      SHOW_FUNC},
   {sys_slave_trans_retries.name,(char*) &sys_slave_trans_retries,   SHOW_SYS},
+  {sys_slave_conflict_detection.name, (char*) &sys_slave_conflict_detection, SHOW_SYS},
 #endif
   {sys_slow_launch_time.name, (char*) &sys_slow_launch_time,        SHOW_SYS},
   {sys_var_slow_query_log.name, (char*) &opt_slow_log,              SHOW_MY_BOOL},
Thread
bk commit into 5.1 tree (aelkin:1.2410) BUG#22583Andrei Elkin2 Mar