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

[Shane Hathaway]

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.

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

Thanks for working on this!

Shane