[ZODB-Dev] RelStorage: Which tables to replicate?

[Tino Wildenhain]

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
-------------- next part --------------
A non-text attachment was scrubbed...
Name: smime.p7s
Type: application/x-pkcs7-signature
Size: 3241 bytes
Desc: S/MIME Cryptographic Signature
Url : http://mail.zope.org/pipermail/zodb-dev/attachments/20080522/5bb34147/smime.bin