Dirk,
>I think a solution could be using "REPLACE INTO" or "INSERT IGNORE INTO"
>instead of "INSERT INTO".
"INSERT INTO" is non standard SQL - it is MySQL unique - not known by any
other DB engine.
The following could happen in time order:
ASSP1 writes a record
Master1 sends the record to Slave2
ASSP2 writes the same record (because he has an equal connection from the
same host, sender ...)
Slave2 wants to write the record from replication respository - and failes
because the record already exists
Because the replication in MySQL is asynchron, you as DB-admin has to make
sure that the DB components (Master & Slave) get rid of duplicate keys.
How ever, assp checks before writing a record if it has to do an 'UPDATE'
or an 'INSERT'. An UPDATE makes no problem, because the record is locked.
An INSERT could fail, if the SLAVE writes the same record just in the
moment between the existency check and the record write of assp.
Thomas
"Dirk Assum" <d...@http.net>
02.12.2009 15:25
Bitte antworten an
ASSP development mailing list <assp-test@lists.sourceforge.net>
An
<assp-test@lists.sourceforge.net>
Kopie
Thema
[Assp-test] MySQL Duplicate entry problem
Hi all,
I've two servers running ASSP 2.0.1(RC 0.6.04) (sorry for being behind)
each connected to a local MySQL database.
MySQL is setup with master-master replication.
The replication fails very often because of duplicate entries:
[...]
[ERROR] Slave: Error 'Duplicate entry '1.2.3.4' for key 1' on query.
Default database: 'assp'. Query: 'insert into PBBlack
(pkey,pvalue,pfrozen) values ('1.2.3.4','1259760853 1259760853 1 5
1.2.3.4 IPinHELO','0')', Error_code: 1062
[ERROR] Slave: Error 'Duplicate entry '1.2.3.4' for key 1' on query.
Default database: 'assp'. Query: 'insert into RWLCache
(pkey,pvalue,pfrozen) values ('1.2.3.4','1259760854 2','0')',
Error_code: 1062
[ERROR] Slave: Error 'Duplicate entry 'nkoch...@gmx.de' for key 1' on
query. Default database: 'assp'. Query: 'insert into PBTrap
(pkey,pvalue,pfrozen) values ('xxx...@gmx.de','1259760877 1259760877
1','0')', Error_code: 1062
[...]
I think a solution could be using "REPLACE INTO" or "INSERT IGNORE INTO"
instead of "INSERT INTO".
Dirk
------------------------------------------------------------------------------
Join us December 9, 2009 for the Red Hat Virtual Experience,
a free event focused on virtualization and cloud computing.
Attend in-depth sessions from your desk. Your couch. Anywhere.
http://p.sf.net/sfu/redhat-sfdev2dev
_______________________________________________
Assp-test mailing list
Assp-test@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/assp-test
DISCLAIMER:
*******************************************************
This email and any files transmitted with it may be confidential, legally
privileged and protected in law and are intended solely for the use of the
individual to whom it is addressed.
This email was multiple times scanned for viruses. There should be no
known virus in this email!
*******************************************************
------------------------------------------------------------------------------
Join us December 9, 2009 for the Red Hat Virtual Experience,
a free event focused on virtualization and cloud computing.
Attend in-depth sessions from your desk. Your couch. Anywhere.
http://p.sf.net/sfu/redhat-sfdev2dev
_______________________________________________
Assp-test mailing list
Assp-test@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/assp-test
