Hi,

Shane Hathaway wrote:
Stefan H. Holek wrote:
We have put up two boxes in a MySQL master-master replication setup [1]. As long as we only write to one of the masters all is fine. Writing to both masters (and expecting MySQL to sort it out) is giving us trouble. We see things like:


Last_Errno: 1062
Last_Error: Error 'Duplicate entry '41860' for key 1' on query.
Default database: 'prod_zodb'.
Query: 'INSERT INTO new_oid VALUES ()'

Perhaps we can solve this by making each server allocate a different set of OIDs. For example, one server might allocate odd-numbered OIDs while the other allocates even-numbered OIDs. Even better would be some kind of standard MySQL solution, but I haven't found any yet.

the standard approach for master-master replicated data is to use
an UUID-Datatype (see http://en.wikipedia.org/wiki/UUID )

Last_Errno: 1050
Last_Error: Error 'Table 'temp_store' already exists' on query.
Default database: 'prod_zodb'.
Query: 'CREATE TEMPORARY TABLE temp_store
        (
         zoid        BIGINT NOT NULL PRIMARY KEY,
         prev_tid    BIGINT NOT NULL,
         md5         CHAR(32),
         state       LONGBLOB
        ) ENGINE MyISAM'

Uh-oh. The commit lock should have prevented this, so this error suggests that each master has a completely independent set of locks!

surprise ;)


That is a serious issue that will lead to database corruption. The commit and pack locks need to be cluster-wide. Does MySQL have a way to do that?

Well, synchronous replication is a hard beast, master-master synchronous
even harder (and you have to ask what problem you really want to solve
with it, since the trade-offs are massive)

Tino

Attachment: smime.p7s
Description: S/MIME Cryptographic Signature

_______________________________________________
For more information about ZODB, see the ZODB Wiki:
http://www.zope.org/Wikis/ZODB/

ZODB-Dev mailing list  -  ZODB-Dev@zope.org
http://mail.zope.org/mailman/listinfo/zodb-dev

Reply via email to