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
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
