CODEWITHSUNDEEP
mysqllinuxdatabasereplicationdatabase-replication
user2916639
user2916639

Reputation: 418

master master replication failed after same Row ID

Hello I have setup Master - Master mysql replication. 

Master A = Localsystem
Master B = Server

it is working fine. but when Master A goes unreachable from Master B

and the table i have replicated had Id field till - 233

Now there is one row inserted on both master in same table .

both got next ID 234 in table.

and when Master A become Reachable to Master B. replication failed on both end.

**

on Master A;
show master status \G;

**

*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 13.235.146.234
                  Master_User: replica
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000097
          Read_Master_Log_Pos: 33310336
               Relay_Log_File: ded18908-1-relay-bin.000003
                Relay_Log_Pos: 4445481
        Relay_Master_Log_File: mysql-bin.000097
             Slave_IO_Running: Yes
            Slave_SQL_Running: No
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: school_testing.ai_student_attendance,school_testing.ai_staff_attendance
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 1062
                   Last_Error: Error 'Duplicate entry '234' for key 'PRIMARY'' on query. Default database: 'school_testing'. Query: 'INSERT INTO `school_testing`.`ai_student_attendance` (`ids`, `student_id`, `atten_list`, `atten_cont`, `month`) VALUES (NULL, '124', 'sasas', 'sasas', 'sasas')'
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 20766001
              Relay_Log_Space: 22735128
              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
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 1062
               Last_SQL_Error: Error 'Duplicate entry '234' for key 'PRIMARY'' on query. Default database: 'school_testing'. Query: 'INSERT INTO `school_testing`.`ai_student_attendance` (`ids`, `student_id`, `atten_list`, `atten_cont`, `month`) VALUES (NULL, '124', 'sasas', 'sasas', 'sasas')'
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 3
1 row in set (0.00 sec)

ERROR: 
No query specified

**

Master - B 
show slave status \G;

**

*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 182.21.11.142
                  Master_User: replica
                  Master_Port: 22331
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000003
          Read_Master_Log_Pos: 111212
               Relay_Log_File: mysqld-relay-bin.000002
                Relay_Log_Pos: 43815
        Relay_Master_Log_File: mysql-bin.000003
             Slave_IO_Running: Yes
            Slave_SQL_Running: No
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: school_testing.ai_student_attendance,school_testing.ai_staff_attendance
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 1062
                   Last_Error: Error 'Duplicate entry '234' for key 'PRIMARY'' on query. Default database: 'school_testing'. Query: 'INSERT INTO `school_testing`.`ai_student_attendance` (`ids`, `student_id`, `atten_list`, `atten_cont`, `month`) VALUES (NULL, '125', 'dsdsdsa', 'dsada', 'asdasdadasdadada')'
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 83284
              Relay_Log_Space: 72201
              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
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 1062
               Last_SQL_Error: Error 'Duplicate entry '234' for key 'PRIMARY'' on query. Default database: 'school_testing'. Query: 'INSERT INTO `school_testing`.`ai_student_attendance` (`ids`, `student_id`, `atten_list`, `atten_cont`, `month`) VALUES (NULL, '125', 'dsdsdsa', 'dsada', 'asdasdadasdadada')'
1 row in set (0.00 sec)

ERROR: 
No query specified

I have no idea how would i rectify this issue. I need Someone Experts Help.

this is a rare case . but my replication got failed when i face this issue

Upvotes: 1

Views: 1358

Answers (1)

IGGt
IGGt

Reputation: 2779

What you need to do is use different ID Offsets on each master:

in your my.cnf files -

On Master 1:

auto_increment_increment=2 
auto_increment_offset=1

On Master 2:

auto_increment_increment=2 
auto_increment_offset=2

so that each one generates a different set of id's.

e.g.

Master 1 = 1, 3, 5

Master 2 = 2, 4, 6

In this way you shouldn't get duplicate entry errors (as long as you are using auto_increment for your ID's) and not manually inputting them.

Upvotes: 3

Related Questions