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

[Stefan H. Holek]

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 ()'

and

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'

Stefan

[1] http://www.howtoforge.com/mysql_master_master_replication


On 20.05.2008, at 18:36, Shane Hathaway wrote:

> That is mostly correct.  You need to replicate transaction,  
> object_state, current_object, and do something with new_oid.  There  
> is no requirement to replicate the other tables, since they are used  
> only for packing, although replicating object_ref and  
> object_refs_added may save some work.
>
> The new_oid table is special to the MySQL adapter.  RelStorage uses  
> the new_oid table as a kind of sequence.  Like any sequence, it is  
> intentionally non-transactional.  Perhaps there would be no ill  
> effects in switching it to the InnoDB engine.
>
> Which MySQL replication method are you looking into?

--
Stefan H. Holek
stefan at epy.co.at