Mysql – Master-Master Replication Duplicate Key

duplicationlinuxMySQLmysql-5.5replication

I have set two servers master-master replication mysql , i manage to set successfully but the problem is the duplicate key i got

MASTER 1 Configuration

/etc/my.cnf

[mysqld]
server-id=1
bind-address = 0.0.0.0


log_bin = /var/log/mysql/bin.log
log-slave-updates
log_bin-index = /var/log/mysql/bin.index
log_error = /var/log/mysql/error.log

relay_log = /var/log/mysql/relay.log
relay-log-info-file = /var/log/mysql/relay.info
relay-log-index = /var/log/mysql/relay.index

binlog_do_db=database_name
replicate-do-db = database_name

auto-increment-increment = 10
auto-increment-offset = 1

innodb_file_per_table=1
open_files_limit=7306

mysql> show slave status\G

*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.0.0.1
                  Master_User: database_name
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: bin.000021
          Read_Master_Log_Pos: 8385119
               Relay_Log_File: relay.000019
                Relay_Log_Pos: 79533
        Relay_Master_Log_File: bin.000021
             Slave_IO_Running: Yes
            Slave_SQL_Running: No
              Replicate_Do_DB: database_name
          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 '19882' for key 'PRIMARY'' on query. Default database: 'database_name'. Query: 'INSERT INTO dle_comments (post_id, date, autor, email, text, ip, is_register, approve) values ('353', '2014-06-16 13:04:43', 'ofadoruo', 'ebuxaje@asdfooff.org', 'Peritoneal &lt;a href=&quot;//accutane-pills-buy.com/#Accutane-bzakam&quot;&gt;Accutane&lt;/<br />a&gt; emerge subjective, section state; pattern; &lt;a href=&quot;//buycanadapropecia.com/#Propecia-Online-xj5iln&quot;&gt;Propeci<br />a Online&lt;/a&gt; rhinoscopy, haemorrhage cats, median non-essential Buy Propecia &lt;a href=&quot;//cialis-buy-canada.com/#Cialis-20mg-Price-h2sfgx&quot;&gt;Gener<br />ic Cialis&lt;/a&gt; orthopnoea thrombin widely; mouth, limits &lt;a href=&quot;//genericaccutanebuy.com/#Accutane-Online-qk48il&quot;&gt;Online<br /> Accutane&lt;/a&gt; detachment, supplements triage, kinder Buy Accutane itch &lt;a href=&quot;//topamax-buy.com/#Buy-Topamax-Online-t5us55&quot;&gt;Topamax Tablet&lt;/a&gt; until
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 172345
              Relay_Log_Space: 8223117
              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 '19882' for key 'PRIMARY'' on query. Default database: 'database_name'. Query: 'INSERT INTO dle_comments (post_id, date, autor, email, text, ip, is_register, approve) values ('353', '2014-06-16 13:04:43', 'ofadoruo', 'ebuxaje@asdfooff.org', 'Peritoneal &lt;a href=&quot;//accutane-pills-buy.com/#Accutane-bzakam&quot;&gt;Accutane&lt;/<br />a&gt; emerge subjective, section state; pattern; &lt;a href=&quot;//buycanadapropecia.com/#Propecia-Online-xj5iln&quot;&gt;Propeci<br />a Online&lt;/a&gt; rhinoscopy, haemorrhage cats, median non-essential Buy Propecia &lt;a href=&quot;//cialis-buy-canada.com/#Cialis-20mg-Price-h2sfgx&quot;&gt;Gener<br />ic Cialis&lt;/a&gt; orthopnoea thrombin widely; mouth, limits &lt;a href=&quot;//genericaccutanebuy.com/#Accutane-Online-qk48il&quot;&gt;Online<br /> Accutane&lt;/a&gt; detachment, supplements triage, kinder Buy Accutane itch &lt;a href=&quot;//topamax-buy.com/#Buy-Topamax-Online-t5us55&quot;&gt;Topamax Tablet&lt;/a&gt; until
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 2
1 row in set (0.00 sec)

MASTER 2 Configuration

/etc/my.cnf

[mysqld]
server-id=2
bind-address = 0.0.0.0


log_bin = /var/log/mysql/bin.log
log-slave-updates
log_bin-index = /var/log/mysql/bin.index
log_error = /var/log/mysql/error.log

relay_log = /var/log/mysql/relay.log
relay-log-info-file = /var/log/mysql/relay.info
relay-log-index = /var/log/mysql/relay.index

binlog_do_db=database_name
replicate-do-db = database_name

auto-increment-increment = 10
auto-increment-offset = 2

innodb_file_per_table=1
open_files_limit=7306

mysql> show slave status\G

*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.0.0.2
                  Master_User: database_name
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: bin.000023
          Read_Master_Log_Pos: 411543
               Relay_Log_File: relay.000029
                Relay_Log_Pos: 1378971
        Relay_Master_Log_File: bin.000022
             Slave_IO_Running: Yes
            Slave_SQL_Running: No
              Replicate_Do_DB: database_name
          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 '22091' for key 'PRIMARY'' on query. Default database: 'database_name'. Query: 'INSERT INTO dle_comments (post_id, date, autor, email, text, ip, is_register, approve) values ('353', '2014-06-16 13:46:46', 'hojosisuoiw', 'ogibkpefi@mannbdinfo.org', 'Continuous <a href=\"//20mg-online-levitra.com/#Levitra-rm4nq9\" target=\"_blank\">Levitra 20mg</a> semilaterally Levitra 20 Mg undescended myelofibrosis: earlier prepatellar Levitra 20 Mg <a href=\"//pills-accutane.com/#Accutane-Pills-ks2dm4\" target=\"_blank\">Accutane</a> enlarges unneeded non-paracetamol vomiting boards <a href=\"//buycanadaaccutane.com/#20-Mg-Accutane-fk0jr1\" target=\"_blank\">Accutane Dose</a> post volume; characteristics communicate ingestion, <a href=\"//buyaccutanecanada.com/#Order-Accutane-Online-ncit4x\" target=\"_blank\">Order Accutane Online</a> office Buy Isotretinoin agitated implications hypocaloric doughnut <a href=\"//cialis-generic-20mg.com/#Tadalafil-20mg-nv91qa\" target=\"_bla
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 1562757
              Relay_Log_Space: 3210092
              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 '22091' for key 'PRIMARY'' on query. Default database: 'database_name'. Query: 'INSERT INTO dle_comments (post_id, date, autor, email, text, ip, is_register, approve) values ('353', '2014-06-16 13:46:46', 'hojosisuoiw', 'ogibkpefi@mannbdinfo.org', 'Continuous <a href=\"//20mg-online-levitra.com/#Levitra-rm4nq9\" target=\"_blank\">Levitra 20mg</a> semilaterally Levitra 20 Mg undescended myelofibrosis: earlier prepatellar Levitra 20 Mg <a href=\"//pills-accutane.com/#Accutane-Pills-ks2dm4\" target=\"_blank\">Accutane</a> enlarges unneeded non-paracetamol vomiting boards <a href=\"//buycanadaaccutane.com/#20-Mg-Accutane-fk0jr1\" target=\"_blank\">Accutane Dose</a> post volume; characteristics communicate ingestion, <a href=\"//buyaccutanecanada.com/#Order-Accutane-Online-ncit4x\" target=\"_blank\">Order Accutane Online</a> office Buy Isotretinoin agitated implications hypocaloric doughnut <a href=\"//cialis-generic-20mg.com/#Tadalafil-20mg-nv91qa\" target=\"_bla
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 1
1 row in set (0.00 sec)

My Question is : How Can I safely fix the duplicate key with no problem for both databases in both servers

UPDATE : after i set 324234 i got the following error in the MASTER 2 ONLY

               Slave_IO_Running: Yes
               Slave_SQL_Running: No
               Last_SQL_Errno: 1690
               Last_SQL_Error: Error 'BIGINT UNSIGNED value is out of range in '(`database_name`.`dle_post`.`comm_num` - 1)'' on query. Default database: 'database_name'. Query: 'UPDATE dle_post SET comm_num=comm_num-1 where id='353''
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 0

Update 2 :

Master 1 replicate to master 2 but master 2 not replicate to master 1
in show slave status\G i got

Slave_IO_Running: Yes
Slave_SQL_Running: Yes
ERROR:
No query specified


 show grants for 'database_name'@'10.0.0.1';
+--------------------------------------------------------------------------------------------------------------------------------------+
| Grants for database_name@10.0.0.1                                                                                            |
+--------------------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'database_name'@'10.0.0.1' IDENTIFIED BY PASSWORD '*17E4C8685DDE5BE5230C4357C1AFD9674EC6A3B0' |
+--------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

Best Answer

If you want to safely skip duplicate key errors, please add this to my.cnf on both Masters

[mysqld]
slave-skip-errors=1062

and restart mysql

Please never run

SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1000;

as this may skip real data the Slave should have.

UPDATE 2014-06-16 20:51 EDT

Please look at the query you are replicating

UPDATE dle_post SET comm_num=comm_num-1 where id='353'

If comm_num is 0, you cannot decrement 0 if it is BIGINT UNSIGNED. The minimum BIGINT UNSIGNED value is 0. So, subtracting 1 from a 0 is not allowed. Storing a negative number is not allowed, either. That explains the error.

UPDATE 2014-06-16 21:51 EDT

You could have repair all those zero comm_nums by setting them to 1 trillion

UPDATE dle_post SET comm_num = 1000000000000 where comm_num = 0;

This is just my suggestion.You could set it to whatever value you see fit.

You should be really looking closer to find these situations.

Best Answer

UPDATE 2014-06-16 20:51 EDT

UPDATE 2014-06-16 21:51 EDT

Related Solutions

Mysql – Master – Master replication Duplicate entry error

MySQL slave replicates changes that are in neither binlog_do_db nor replicate_do_db

Related Question