MySQL Lists are EOL. Please join:

List:Commits« Previous MessageNext Message »
From:Andrei Elkin Date:February 19 2007 12:24pm
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-02-19 14:24:49+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.
  Additionally, Write_rows event does not hold info which of REPLACE
  of INSERT is the parent. This make the event useless to find out
  DUP_KEY conflict on slave because if the parent is REPLACE slave has
  to accept the event.
  The class needs extending with that bit of info in order to be able to catch DUP_KEY
  conflict.
  
  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-02-19 14:24:43+02:00, aelkin@stripped +706 -0
    New BitKeeper file ``mysql-test/r/rpl_row_conflicts.result''

  mysql-test/r/rpl_row_conflicts.result@stripped, 2007-02-19 14:24:43+02:00, aelkin@stripped +0 -0

  mysql-test/t/rpl_row_conflicts-slave.opt@stripped, 2007-02-19 14:24:43+02:00, aelkin@stripped +1 -0
    innodb is needed

  mysql-test/t/rpl_row_conflicts-slave.opt@stripped, 2007-02-19 14:24:43+02:00, aelkin@stripped +0 -0

  mysql-test/t/rpl_row_conflicts.test@stripped, 2007-02-19 14:24:43+02:00, aelkin@stripped +311 -0
    Testing conflicts that can happen with events generated by UPDATE/DELETE queries.
    Rows-events from INSERT/REPLACE needs extending of Write_row class.

  mysql-test/t/rpl_row_conflicts.test@stripped, 2007-02-19 14:24:43+02:00, aelkin@stripped +0 -0

  sql/log_event.cc@stripped, 2007-02-19 14:24:41+02:00, aelkin@stripped +92 -50
    changes in row_event error handling, needs semantical merge with active rbr bugs on this topic
    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-02-19 14:24:41+02:00, aelkin@stripped +2 -1
    option and global variable

  sql/mysqld.cc@stripped, 2007-02-19 14:24:42+02:00, aelkin@stripped +7 -2
    new option

  sql/set_var.cc@stripped, 2007-02-19 14:24:42+02:00, aelkin@stripped +3 -0
    global variable associated with the option

  sql/share/errmsg.txt@stripped, 2007-02-19 14:24:43+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-02-19 14:24:58 +02:00
+++ 1.264/sql/log_event.cc	2007-02-19 14:24:58 +02:00
@@ -5846,22 +5846,56 @@ int Rows_log_event::exec_event(st_relay_
       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_RECORD_CHANGED:  /* fixme, what's the problem with heap? */
+      case HA_ERR_KEY_NOT_FOUND:
+	/* 
+	   there is no normal reason to continue by slave when
+	   the current update row event failed, at least when user
+	   specifies that conflicts should be detected.
+	   So slave is supposed to halt in that case.
+	   User still can instruct slave not to stop via --slave-skip-errors
+	 */
+	if (!opt_slave_conflict_detection)
+	  /* 
+	     Masking out the error has been doing since the beginning
+	     rbr code. This is defacto some sort of Conflict Resolution
+	  */
+	  error= 0;
+	else
+	{
+	  /* 
+	     todo/fixme mats: all around handler level codes should be
+	     translated into server's level and be reported as the new
+	     rbr conflict related like in the following:
+	  */
+	  error= ER_MATCHING_ROW_NOT_FOUND;
+	}
 	break;
 
+      case HA_ERR_END_OF_FILE:
+      case HA_ERR_FOUND_DUPP_KEY:
+	error= ER_MATCHING_ROW_NOT_FOUND;
+      case ER_MATCHING_ROW_NOT_FOUND:
+	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)
+      {
+	bool ignored= FALSE;
+	if (!(ignored= ignored_error_code(error)) || opt_slave_conflict_detection)
+	    slave_print_msg(ERROR_LEVEL, rli, error,
+			    "Error in %s event: row application failed",
+			    get_type_str());
+	if (ignored)
+	  error= 0;
+	else
+	  thd->query_error= 1;
+      }
       row_start= row_end;
     }
     DBUG_EXECUTE_IF("STOP_SLAVE_after_first_Rows_event",
@@ -6480,7 +6514,8 @@ 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
-  table->file->extra(HA_EXTRA_WRITE_CAN_REPLACE);  // Needed for ndbcluster
+  if (!opt_slave_conflict_detection)
+    table->file->extra(HA_EXTRA_WRITE_CAN_REPLACE);  // Needed for ndbcluster
   table->file->extra(HA_EXTRA_IGNORE_NO_KEY);   // Needed for ndbcluster
   /*
     TODO: the cluster team (Tomas?) says that it's better if the engine knows
@@ -6678,7 +6713,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 +6725,22 @@ 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 */
       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 +6824,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) */
@@ -6849,7 +6891,7 @@ static bool record_compare(TABLE *table)
   <code>table->record[1]</code>, error code otherwise.
  */
 
-static int find_and_fetch_row(TABLE *table, byte *key)
+static int find_and_fetch_row(TABLE *table, byte *key, bool conflict_check)
 {
   DBUG_ENTER("find_and_fetch_row(TABLE *table, byte *key, byte *record)");
   DBUG_PRINT("enter", ("table: 0x%lx, key: 0x%lx  record: 0x%lx",
@@ -6869,24 +6911,26 @@ 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]);
+    store_record(table,record[1]);           // BI
+    table->file->position(table->record[0]); // filler bits ok after unpack
     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);
+    if (!error)
+    {
+      if (conflict_check && record_compare(table))
+      {
+	error= ER_MATCHING_ROW_NOT_FOUND;
+      }
+      /*
+	rnd_pos() returns OR 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);
+    }
     DBUG_RETURN(error);
   }
 
@@ -6910,15 +6954,6 @@ 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)))
@@ -6927,7 +6962,10 @@ static int find_and_fetch_row(TABLE *tab
       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.
@@ -6950,7 +6988,7 @@ static int find_and_fetch_row(TABLE *tab
       found.  I can see no scenario where it would be incorrect to
       chose the row to change only using a PK or an UNNI.
     */
-    if (table->key_info->flags & HA_NOSAME)
+    if (!conflict_check && table->key_info->flags & HA_NOSAME)
     {
       table->file->ha_index_end();
       DBUG_RETURN(0);
@@ -6972,7 +7010,7 @@ static int find_and_fetch_row(TABLE *tab
       {
 	table->file->print_error(error, MYF(0));
         table->file->ha_index_end();
-	DBUG_RETURN(error);
+	DBUG_RETURN(error); // cr todo: might be translated into server error
       }
     }
 
@@ -7020,7 +7058,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); // cr todo: sever level error as above
       }
     }
     while (restart_count < 2 && record_compare(table));
@@ -7154,8 +7192,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 +7352,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-02-19 14:24:58 +02:00
+++ 1.472/sql/mysql_priv.h	2007-02-19 14:24:58 +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-02-19 14:24:58 +02:00
+++ 1.605/sql/mysqld.cc	2007-02-19 14:24:58 +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/02/19 14:24:43
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;
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	Waiting for master to send event
Master_Host	127.0.0.1
Master_User	root
Master_Port	9306
Connect_Retry	1
Master_Log_File	master-bin.000001
Read_Master_Log_Pos	1638
Relay_Log_File	slave-relay-bin.000003
Relay_Log_Pos	1783
Relay_Master_Log_File	master-bin.000001
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	0
Exec_Master_Log_Pos	1638
Relay_Log_Space	2061
Until_Condition	None
Until_Log_File	
Until_Log_Pos	0
Master_SSL_Allowed	No
Master_SSL_CA_File	
Master_SSL_CA_Path	
Master_SSL_Cert	
Master_SSL_Cipher	
Master_SSL_Key	
Seconds_Behind_Master	0
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	Waiting for master to send event
Master_Host	127.0.0.1
Master_User	root
Master_Port	9306
Connect_Retry	1
Master_Log_File	master-bin.000001
Read_Master_Log_Pos	1814
Relay_Log_File	slave-relay-bin.000003
Relay_Log_Pos	1871
Relay_Master_Log_File	master-bin.000001
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	0
Exec_Master_Log_Pos	1726
Relay_Log_Space	2237
Until_Condition	None
Until_Log_File	
Until_Log_Pos	0
Master_SSL_Allowed	No
Master_SSL_CA_File	
Master_SSL_CA_Path	
Master_SSL_Cert	
Master_SSL_Cipher	
Master_SSL_Key	
Seconds_Behind_Master	NULL
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	Waiting for master to send event
Master_Host	127.0.0.1
Master_User	root
Master_Port	9306
Connect_Retry	1
Master_Log_File	master-bin.000001
Read_Master_Log_Pos	1981
Relay_Log_File	slave-relay-bin.000003
Relay_Log_Pos	2047
Relay_Master_Log_File	master-bin.000001
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	0
Exec_Master_Log_Pos	1902
Relay_Log_Space	2404
Until_Condition	None
Until_Log_File	
Until_Log_Pos	0
Master_SSL_Allowed	No
Master_SSL_CA_File	
Master_SSL_CA_Path	
Master_SSL_Cert	
Master_SSL_Cipher	
Master_SSL_Key	
Seconds_Behind_Master	NULL
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	Waiting for master to send event
Master_Host	127.0.0.1
Master_User	root
Master_Port	9306
Connect_Retry	1
Master_Log_File	master-bin.000001
Read_Master_Log_Pos	2096
Relay_Log_File	slave-relay-bin.000003
Relay_Log_Pos	2126
Relay_Master_Log_File	master-bin.000001
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	0
Exec_Master_Log_Pos	1981
Relay_Log_Space	2519
Until_Condition	None
Until_Log_File	
Until_Log_Pos	0
Master_SSL_Allowed	No
Master_SSL_CA_File	
Master_SSL_CA_Path	
Master_SSL_Cert	
Master_SSL_Cipher	
Master_SSL_Key	
Seconds_Behind_Master	NULL
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	Waiting for master to send event
Master_Host	127.0.0.1
Master_User	root
Master_Port	9306
Connect_Retry	1
Master_Log_File	master-bin.000001
Read_Master_Log_Pos	2317
Relay_Log_File	slave-relay-bin.000003
Relay_Log_Pos	2356
Relay_Master_Log_File	master-bin.000001
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	0
Exec_Master_Log_Pos	2211
Relay_Log_Space	2740
Until_Condition	None
Until_Log_File	
Until_Log_Pos	0
Master_SSL_Allowed	No
Master_SSL_CA_File	
Master_SSL_CA_Path	
Master_SSL_Cert	
Master_SSL_Cipher	
Master_SSL_Key	
Seconds_Behind_Master	NULL
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	Waiting for master to send event
Master_Host	127.0.0.1
Master_User	root
Master_Port	9306
Connect_Retry	1
Master_Log_File	master-bin.000001
Read_Master_Log_Pos	2450
Relay_Log_File	slave-relay-bin.000003
Relay_Log_Pos	2462
Relay_Master_Log_File	master-bin.000001
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	0
Exec_Master_Log_Pos	2317
Relay_Log_Space	2873
Until_Condition	None
Until_Log_File	
Until_Log_Pos	0
Master_SSL_Allowed	No
Master_SSL_CA_File	
Master_SSL_CA_Path	
Master_SSL_Cert	
Master_SSL_Cipher	
Master_SSL_Key	
Seconds_Behind_Master	NULL
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
show slave status;;
Slave_IO_State	Waiting for master to send event
Master_Host	127.0.0.1
Master_User	root
Master_Port	9306
Connect_Retry	1
Master_Log_File	master-bin.000001
Read_Master_Log_Pos	2689
Relay_Log_File	slave-relay-bin.000003
Relay_Log_Pos	2728
Relay_Master_Log_File	master-bin.000001
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	0
Exec_Master_Log_Pos	2583
Relay_Log_Space	3112
Until_Condition	None
Until_Log_File	
Until_Log_Pos	0
Master_SSL_Allowed	No
Master_SSL_CA_File	
Master_SSL_CA_Path	
Master_SSL_Cert	
Master_SSL_Cipher	
Master_SSL_Key	
Seconds_Behind_Master	NULL
select * from t2i;
a	b
1	1
1	1
show slave status;;
Slave_IO_State	Waiting for master to send event
Master_Host	127.0.0.1
Master_User	root
Master_Port	9306
Connect_Retry	1
Master_Log_File	master-bin.000001
Read_Master_Log_Pos	2689
Relay_Log_File	slave-relay-bin.000003
Relay_Log_Pos	2728
Relay_Master_Log_File	master-bin.000001
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	0
Exec_Master_Log_Pos	2583
Relay_Log_Space	3112
Until_Condition	None
Until_Log_File	
Until_Log_Pos	0
Master_SSL_Allowed	No
Master_SSL_CA_File	
Master_SSL_CA_Path	
Master_SSL_Cert	
Master_SSL_Cipher	
Master_SSL_Key	
Seconds_Behind_Master	NULL
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
show slave status;;
Slave_IO_State	Waiting for master to send event
Master_Host	127.0.0.1
Master_User	root
Master_Port	9306
Connect_Retry	1
Master_Log_File	master-bin.000001
Read_Master_Log_Pos	2883
Relay_Log_File	slave-relay-bin.000003
Relay_Log_Pos	2949
Relay_Master_Log_File	master-bin.000001
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	0
Exec_Master_Log_Pos	2804
Relay_Log_Space	3306
Until_Condition	None
Until_Log_File	
Until_Log_Pos	0
Master_SSL_Allowed	No
Master_SSL_CA_File	
Master_SSL_CA_Path	
Master_SSL_Cert	
Master_SSL_Cipher	
Master_SSL_Key	
Seconds_Behind_Master	NULL
select * from t2m;
a	b
1	1
1	1
show slave status;;
Slave_IO_State	Waiting for master to send event
Master_Host	127.0.0.1
Master_User	root
Master_Port	9306
Connect_Retry	1
Master_Log_File	master-bin.000001
Read_Master_Log_Pos	2883
Relay_Log_File	slave-relay-bin.000003
Relay_Log_Pos	2949
Relay_Master_Log_File	master-bin.000001
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	0
Exec_Master_Log_Pos	2804
Relay_Log_Space	3306
Until_Condition	None
Until_Log_File	
Until_Log_Pos	0
Master_SSL_Allowed	No
Master_SSL_CA_File	
Master_SSL_CA_Path	
Master_SSL_Cert	
Master_SSL_Cipher	
Master_SSL_Key	
Seconds_Behind_Master	NULL
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	Waiting for master to send event
Master_Host	127.0.0.1
Master_User	root
Master_Port	9306
Connect_Retry	1
Master_Log_File	master-bin.000001
Read_Master_Log_Pos	3082
Relay_Log_File	slave-relay-bin.000003
Relay_Log_Pos	3116
Relay_Master_Log_File	master-bin.000001
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	0
Exec_Master_Log_Pos	2971
Relay_Log_Space	3505
Until_Condition	None
Until_Log_File	
Until_Log_Pos	0
Master_SSL_Allowed	No
Master_SSL_CA_File	
Master_SSL_CA_Path	
Master_SSL_Cert	
Master_SSL_Cipher	
Master_SSL_Key	
Seconds_Behind_Master	NULL
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	Waiting for master to send event
Master_Host	127.0.0.1
Master_User	root
Master_Port	9306
Connect_Retry	1
Master_Log_File	master-bin.000001
Read_Master_Log_Pos	3277
Relay_Log_File	slave-relay-bin.000003
Relay_Log_Pos	3338
Relay_Master_Log_File	master-bin.000001
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.t1m
Skip_Counter	0
Exec_Master_Log_Pos	3193
Relay_Log_Space	3700
Until_Condition	None
Until_Log_File	
Until_Log_Pos	0
Master_SSL_Allowed	No
Master_SSL_CA_File	
Master_SSL_CA_Path	
Master_SSL_Cert	
Master_SSL_Cipher	
Master_SSL_Key	
Seconds_Behind_Master	NULL
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
drop table t1i,t1m,t2i,t2m,t3i,t3m;

--- New file ---
+++ mysql-test/t/rpl_row_conflicts-slave.opt	07/02/19 14:24:43
--innodb

--- New file ---
+++ mysql-test/t/rpl_row_conflicts.test	07/02/19 14:24:43
-- 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;

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;
--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 will stop
# 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;
select * from t3m;
--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;
--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;
--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;
--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;
--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;
--query_vertical show slave status;

wait_for_slave_to_stop;

select * from t2i;
--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;
--query_vertical show slave status;

wait_for_slave_to_stop;

select * from t2m;
--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;
--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;
--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;



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


--- 1.139/sql/share/errmsg.txt	2007-02-19 14:24:58 +02:00
+++ 1.140/sql/share/errmsg.txt	2007-02-19 14:24:58 +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_MATCHING_ROW_NOT_FOUND
+        eng "Replication conflict: a row matching the event's before image is not found"

--- 1.211/sql/set_var.cc	2007-02-19 14:24:58 +02:00
+++ 1.212/sql/set_var.cc	2007-02-19 14:24:58 +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 Elkin19 Feb