MySQL Master-Master

Discussion in 'Server Operation' started by guimnk, May 24, 2011.

  1. guimnk

    guimnk New Member

    Hi all!

    I've problems with Master-Master Replication.
    Replication was working until now, and stopped with the following error:

    Code:
    Master1:
    
    mysql> show slave status \G;
    *************************** 1. row ***************************
                   Slave_IO_State: Waiting for master to send event
                      Master_Host: 192.168.1.38
                      Master_User: replication
                      Master_Port: 3306
                    Connect_Retry: 60
                  Master_Log_File: mysql-bin.000075
              Read_Master_Log_Pos: 1276894
                   Relay_Log_File: brain-relay-bin.000005
                    Relay_Log_Pos: 251
            Relay_Master_Log_File: mysql-bin.000074
                 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: 1062
                       Last_Error: Error 'Duplicate entry '1644775' for key 'PRIMARY'' on query. Default database: 'db1Furlan'. Query: 'insert into FTCXLCTO ( ID_CXLC,ID_EMPR,ID_FILI,ID_USER,CX_DATA,CX_ORCA,CX_SERV,CX_OUTR,CX_TOTA,CX_RECE,CX_TROC,CX_FRQA,CP_NUME,NF_NUME,NF_SERI,CX_CART,CX_QTPA,CX_DBCR,CX_CNPF,CX_DTID,CX_OPID ) values (   1644775,'FLN',         1,'GASILVA','2011/05/23 00:00:00',                10.00,                 0.00,                 0.00,                10.00,                 0.00,                 0.00,         0,     31509,         0,' ',         0,         1,         0,' ','2011/05/23 07:50:01',       447)'
                     Skip_Counter: 0
              Exec_Master_Log_Pos: 106
                  Relay_Log_Space: 6326966
                  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 '1644775' for key 'PRIMARY'' on query. Default database: 'db1Furlan'. Query: 'insert into FTCXLCTO ( ID_CXLC,ID_EMPR,ID_FILI,ID_USER,CX_DATA,CX_ORCA,CX_SERV,CX_OUTR,CX_TOTA,CX_RECE,CX_TROC,CX_FRQA,CP_NUME,NF_NUME,NF_SERI,CX_CART,CX_QTPA,CX_DBCR,CX_CNPF,CX_DTID,CX_OPID ) values (   1644775,'FLN',         1,'GASILVA','2011/05/23 00:00:00',                10.00,                 0.00,                 0.00,                10.00,                 0.00,                 0.00,         0,     31509,         0,' ',         0,         1,         0,' ','2011/05/23 07:50:01',       447)'
    1 row in set (0.00 sec)
    
    ERROR:
    No query specified
    
    mysql>
    
    Code:
    Master2:
    
    mysql> show slave status \G;
    *************************** 1. row ***************************
                   Slave_IO_State: Waiting for master to send event
                      Master_Host: 192.168.1.39
                      Master_User: replication
                      Master_Port: 3306
                    Connect_Retry: 60
                  Master_Log_File: mysql-bin.000080
              Read_Master_Log_Pos: 29612653
                   Relay_Log_File: brain2-relay-bin.000003
                    Relay_Log_Pos: 10233
            Relay_Master_Log_File: mysql-bin.000077
                 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: 1062
                       Last_Error: Error 'Duplicate entry '3704344' for key 'PRIMARY'' on query. Default database: 'db1Furlan'. Query: 'insert into SERVORDS ( ID_EMPR,ID_NROS,ID_SITU,ID_FILI,ID_PEDI,ID_USER,ID_CLIE,ID_VEND,ID_MATR,ID_GRSE,OS_NUME,OS_DATA,OS_DTPR,OS_DTIN,OS_DTFI,OS_DTAC,OS_DTAG,OS_LOJA,OS_LCOL,OS_PLAC,OS_NCOR,OS_MODE,OS_CONT,OS_FONE,OS_MATR,OS_DTID,OS_OPID ) values ('FLN',   3704344,         1,         1,   3704344,' ',    585635,        49,         0,         0,   3662009,'2011/05/21 00:00:00','1900/01/01','1900/01/01','1900/01/01','1900/01/01','1900/01/01',         0,         0,' ',' ',' ',' ',' ',         0,'2011/05/21 07:15:59',        84)'
                     Skip_Counter: 0
              Exec_Master_Log_Pos: 10088
                  Relay_Log_Space: 280846407
                  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 '3704344' for key 'PRIMARY'' on query. Default database: 'db1Furlan'. Query: 'insert into SERVORDS ( ID_EMPR,ID_NROS,ID_SITU,ID_FILI,ID_PEDI,ID_USER,ID_CLIE,ID_VEND,ID_MATR,ID_GRSE,OS_NUME,OS_DATA,OS_DTPR,OS_DTIN,OS_DTFI,OS_DTAC,OS_DTAG,OS_LOJA,OS_LCOL,OS_PLAC,OS_NCOR,OS_MODE,OS_CONT,OS_FONE,OS_MATR,OS_DTID,OS_OPID ) values ('FLN',   3704344,         1,         1,   3704344,' ',    585635,        49,         0,         0,   3662009,'2011/05/21 00:00:00','1900/01/01','1900/01/01','1900/01/01','1900/01/01','1900/01/01',         0,         0,' ',' ',' ',' ',' ',         0,'2011/05/21 07:15:59',        84)'
    1 row in set (0.00 sec)
    
    ERROR:
    No query specified
    
    mysql> show slave status \G;
    *************************** 1. row ***************************
                   Slave_IO_State: Waiting for master to send event
                      Master_Host: 192.168.1.39
                      Master_User: replication
                      Master_Port: 3306
                    Connect_Retry: 60
                  Master_Log_File: mysql-bin.000080
              Read_Master_Log_Pos: 29878299
                   Relay_Log_File: brain2-relay-bin.000003
                    Relay_Log_Pos: 10233
            Relay_Master_Log_File: mysql-bin.000077
                 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: 1062
                       Last_Error: Error 'Duplicate entry '3704344' for key 'PRIMARY'' on query. Default database: 'db1Furlan'. Query: 'insert into SERVORDS ( ID_EMPR,ID_NROS,ID_SITU,ID_FILI,ID_PEDI,ID_USER,ID_CLIE,ID_VEND,ID_MATR,ID_GRSE,OS_NUME,OS_DATA,OS_DTPR,OS_DTIN,OS_DTFI,OS_DTAC,OS_DTAG,OS_LOJA,OS_LCOL,OS_PLAC,OS_NCOR,OS_MODE,OS_CONT,OS_FONE,OS_MATR,OS_DTID,OS_OPID ) values ('FLN',   3704344,         1,         1,   3704344,' ',    585635,        49,         0,         0,   3662009,'2011/05/21 00:00:00','1900/01/01','1900/01/01','1900/01/01','1900/01/01','1900/01/01',         0,         0,' ',' ',' ',' ',' ',         0,'2011/05/21 07:15:59',        84)'
                     Skip_Counter: 0
              Exec_Master_Log_Pos: 10088
                  Relay_Log_Space: 281112053
                  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 '3704344' for key 'PRIMARY'' on query. Default database: 'db1Furlan'. Query: 'insert into SERVORDS ( ID_EMPR,ID_NROS,ID_SITU,ID_FILI,ID_PEDI,ID_USER,ID_CLIE,ID_VEND,ID_MATR,ID_GRSE,OS_NUME,OS_DATA,OS_DTPR,OS_DTIN,OS_DTFI,OS_DTAC,OS_DTAG,OS_LOJA,OS_LCOL,OS_PLAC,OS_NCOR,OS_MODE,OS_CONT,OS_FONE,OS_MATR,OS_DTID,OS_OPID ) values ('FLN',   3704344,         1,         1,   3704344,' ',    585635,        49,         0,         0,   3662009,'2011/05/21 00:00:00','1900/01/01','1900/01/01','1900/01/01','1900/01/01','1900/01/01',         0,         0,' ',' ',' ',' ',' ',         0,'2011/05/21 07:15:59',        84)'
    1 row in set (0.00 sec)
    
    ERROR:
    No query specified
    
    mysql> show slave status \G;
    *************************** 1. row ***************************
                   Slave_IO_State: Waiting for master to send event
                      Master_Host: 192.168.1.39
                      Master_User: replication
                      Master_Port: 3306
                    Connect_Retry: 60
                  Master_Log_File: mysql-bin.000080
              Read_Master_Log_Pos: 29895190
                   Relay_Log_File: brain2-relay-bin.000003
                    Relay_Log_Pos: 10233
            Relay_Master_Log_File: mysql-bin.000077
                 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: 1062
                       Last_Error: Error 'Duplicate entry '3704344' for key 'PRIMARY'' on query. Default database: 'db1Furlan'. Query: 'insert into SERVORDS ( ID_EMPR,ID_NROS,ID_SITU,ID_FILI,ID_PEDI,ID_USER,ID_CLIE,ID_VEND,ID_MATR,ID_GRSE,OS_NUME,OS_DATA,OS_DTPR,OS_DTIN,OS_DTFI,OS_DTAC,OS_DTAG,OS_LOJA,OS_LCOL,OS_PLAC,OS_NCOR,OS_MODE,OS_CONT,OS_FONE,OS_MATR,OS_DTID,OS_OPID ) values ('FLN',   3704344,         1,         1,   3704344,' ',    585635,        49,         0,         0,   3662009,'2011/05/21 00:00:00','1900/01/01','1900/01/01','1900/01/01','1900/01/01','1900/01/01',         0,         0,' ',' ',' ',' ',' ',         0,'2011/05/21 07:15:59',        84)'
                     Skip_Counter: 0
              Exec_Master_Log_Pos: 10088
                  Relay_Log_Space: 281128944
                  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 '3704344' for key 'PRIMARY'' on query. Default database: 'db1Furlan'. Query: 'insert into SERVORDS ( ID_EMPR,ID_NROS,ID_SITU,ID_FILI,ID_PEDI,ID_USER,ID_CLIE,ID_VEND,ID_MATR,ID_GRSE,OS_NUME,OS_DATA,OS_DTPR,OS_DTIN,OS_DTFI,OS_DTAC,OS_DTAG,OS_LOJA,OS_LCOL,OS_PLAC,OS_NCOR,OS_MODE,OS_CONT,OS_FONE,OS_MATR,OS_DTID,OS_OPID ) values ('FLN',   3704344,         1,         1,   3704344,' ',    585635,        49,         0,         0,   3662009,'2011/05/21 00:00:00','1900/01/01','1900/01/01','1900/01/01','1900/01/01','1900/01/01',         0,         0,' ',' ',' ',' ',' ',         0,'2011/05/21 07:15:59',        84)'
    1 row in set (0.00 sec)
    
    ERROR:
    No query specified
    
    mysql>
    
    To reduce the load of database Master1, I put some users at the database Master1 and some at the database Master2.

    How can I do to fix the problem?

    I tried to run the command "skip count error" but not solved the problem...
     

Share This Page