MySQL Replication Primary KEY violation

See the problem of replication, the most obvious case is the primary key conflict, today to see this problem

1. What causes this problem
   

2. How to circumvent this problem

First, what causes

Most of the online is: for the existence of auto_increment field or unique index field, use replace into operation or master-slave switch, because replace into for auto or unique field will be deleted and then inserted

Execute replace into t values is deleted and the number of rows inserted (greater than or equal to 1)

Master on SHOW CREATE TABLE ' Test_autoinc ' (

' id ' int (one) not NULL auto_increment,

' C1 ' int (one) DEFAULT NULL,

' C2 ' varchar DEFAULT NULL,

PRIMARY KEY (' id '),

UNIQUE KEY ' C1 ' (' C1 ')

) Engine=innodbauto_increment=7

Slave on SHOW CREATE TABLE ' Test_autoinc ' (

' id ' int (one) not NULL auto_increment,

' C1 ' int (one) DEFAULT NULL,

' C2 ' varchar DEFAULT NULL,

PRIMARY KEY (' id '),

UNIQUE KEY ' C1 ' (' C1 ')

) Engine=innodbauto_increment=6

you can see that the field grows differently after you perform the replace into at this point, the self-increment of master is listed as 7, and the slave self-increment column is 6, the same as the maximum value in the table, if the primary and standby switch occurs, the slave provides the service, and the primary key conflict occurs when the record of the primary key 6 is inserted by self-increment columns .

There are two ways to solve this problem:

1st suggested fix Is:use a delete event and a insert event to record this replace.2nd suggested fix is:update the auto_ Incrment in UPDATE statements.

I think the solution is like this, but do not know how to implement the database

if not exists ( select phone from t where phone= ‘1‘ )              insert into t(phone, update_time) values ( ‘1‘ , getdate())   else

          updatet set update_time = getdate() wherephone= ‘1‘




MySQL Replication Primary KEY violation