Re: [ZODB-Dev] RelStorage: Which tables to replicate?
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 ( zoidBIGINT NOT NULL PRIMARY KEY, prev_tidBIGINT 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 [EMAIL PROTECTED] ___ 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
Re: [ZODB-Dev] RelStorage: Which tables to replicate?
Hi Russ, This is correct. I should have mentioned that the two servers are configured so that server A creates odd, server B even sequence numbers. This makes the duplicate id error pretty mysterious, IMO ;-) Stefan On 22.05.2008, at 13:15, Russ Ferriday wrote: I have not read the reference, but it seems that this scheme can never work without keys (OIDs) being qualified by which master they originated on. Otherwise one of the mySQLs would need to be responsible for handing out unique oids. You need an OID origination namespace for each master. -- Stefan H. Holek [EMAIL PROTECTED] ___ 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
Re: [ZODB-Dev] RelStorage: Which tables to replicate?
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 ( zoidBIGINT NOT NULL PRIMARY KEY, prev_tidBIGINT 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 ___ 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
Re: [ZODB-Dev] RelStorage: Which tables to replicate?
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 ( zoidBIGINT NOT NULL PRIMARY KEY, prev_tidBIGINT 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
Re: [ZODB-Dev] RelStorage: Which tables to replicate?
Let me rephrase this. Would it be ok to change *all* tables to use the InnoDB engine (except new_oid)? Stefan On 19.05.2008, at 10:55, Stefan H. Holek wrote: I am playing with database replication (MySQL) and have found the following caveats: - don't mix engine types (MyISAM, InnoDB) - don't use temporary tables Now, RelStorage does both and so I was wondering how to proceed with replication. Would it be prudent to assume that all I ever need to replicate are the InnoDB tables transaction, object_state, and current_object? All other tables don't seem to be used across transaction boundaries, but I may be missing something here. -- Stefan H. Holek [EMAIL PROTECTED] ___ 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
Re: [ZODB-Dev] RelStorage: Which tables to replicate?
Stefan H. Holek wrote: I am playing with database replication (MySQL) and have found the following caveats: - don't mix engine types (MyISAM, InnoDB) - don't use temporary tables Now, RelStorage does both and so I was wondering how to proceed with replication. Would it be prudent to assume that all I ever need to Maybe use postgres and slony instead? The mysql shortcomings on replication and integrity should be well known. It should also be possible to replicate on ZODB layer instead. At least I read something in the ML beside ZRE of course :-) Cheers 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
