Multi-master Duplicate entry, error no. 1062
Hello,
I am configuring a Multi-master duplicate setup.
I have 2 servers acting as master/slave, and replication is working beautifully when writing to one of the servers at a time.
However I am experiencing issues when writing to both servers at the same time (A likely scenario when in normal operation.)
A "show slave status" command will show the following error, after starting the script:
Last_Errno: 1062
Last_Error: Error 'Duplicate entry '23494' for key 'PRIMARY'' on query. Default database: 'dkdk'. Query: 'INSERT DELAYED INTO test (description) VALUES ('2031')'
Here you can have a look at the entry, 23494 from both servers:
| 23484 | 0 | 0 | 0 | 0 | 0 | 0 | NULL | 2026 | NULL | 0 |
| 23486 | 0 | 0 | 0 | 0 | 0 | 0 | NULL | 2027 | NULL | 0 |
| 23488 | 0 | 0 | 0 | 0 | 0 | 0 | NULL | 2028 | NULL | 0 |
| 23490 | 0 | 0 | 0 | 0 | 0 | 0 | NULL | 2029 | NULL | 0 |
| 23492 | 0 | 0 | 0 | 0 | 0 | 0 | NULL | 2030 | NULL | 0 |
| 23494 | 0 | 0 | 0 | 0 | 0 | 0 | NULL | 2032 | NULL | 0 |
| 23496 | 0 | 0 | 0 | 0 | 0 | 0 | NULL | 2034 | NULL | 0 |
| 23498 | 0 | 0 | 0 | 0 | 0 | 0 | NULL | 2036 | NULL | 0 |
| 23486 | 0 | 0 | 0 | 0 | 0 | 0 | NULL | 2027 | NULL | 0 |
| 23488 | 0 | 0 | 0 | 0 | 0 | 0 | NULL | 2028 | NULL | 0 |
| 23490 | 0 | 0 | 0 | 0 | 0 | 0 | NULL | 2029 | NULL | 0 |
| 23492 | 0 | 0 | 0 | 0 | 0 | 0 | NULL | 2030 | NULL | 0 |
| 23494 | 0 | 0 | 0 | 0 | 0 | 0 | NULL | 2031 | NULL | 0 |
| 23496 | 0 | 0 | 0 | 0 | 0 | 0 | NULL | 2033 | NULL | 0 |
| 23498 | 0 | 0 | 0 | 0 | 0 | 0 | NULL | 2035 | NULL | 0 |
| 23500 | 0 | 0 | 0 | 0 | 0 | 0 | NULL | 2037 | NULL | 0 |
Why is this error occuring? I want the server to replicate faster than the inserts, which I guess would prevent the following error from occuring.
Inserting slave-skip-errors = 1062 to my.cnf, will result in loss of inserts, so out of 10000 inserts, there would be approx. 9700, so a loss of 300 inserts.
Here you have the test-code for inserting rows to the test tabel.
for ($i=0; $i<10000; $i++) {
$server = "xxx.xxx.xxx.xxx";
if ($i%2==0) {
$server = "xxx.xxx.xxx.xxx";
}
mysql_connect($server, $dbuser, $dbpasswd) or die();
mysql_select_db($dbname) or debug(mysql_error());
$sql = "INSERT DELAYED INTO test (description) VALUES ('$i')";
mysql_query($sql);
mysql_close();
Looking forward to some replies.
Subject
Views
Written By
Posted
Multi-master Duplicate entry, error no. 1062
3195
December 29, 2011 09:42AM
1025
December 30, 2011 11:31AM
Sorry, you can't reply to this topic. It has been closed.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion
of Oracle or any other party.