1

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)
Improve this question

1 Answer 1

Reset to default
2

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.

Improve this answer
4
  • I followed your answer but i got another problem , another error i update my question with that error so you can take a look , Thanks
    – Linux Guy
    Jun 17, 2014 at 0:38
  • How can i fix the comm_num-1 ? thanks
    – Linux Guy
    Jun 17, 2014 at 1:29
  • master 1 replicate but master 2 not replicate i got error No query specified in master 2 show slave status\G; i update my question for more details , thanks
    – Linux Guy
    Jun 17, 2014 at 3:51
  • i figure it out , i was set wrong database table name in the master 1 , that's why master 2 not replicate to master 1
    – Linux Guy
    Jun 17, 2014 at 4:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.