This is not a problem of the ZODB or relstorage, but specific to how MySQL handles a) replication and b) temporary tables.

MySQL employs a log-based replication mechanism. This means the replication slave replays the commands performed on the master to keep its copy of the database in sync.


In MySQL, temporary tables live in RAM. So when a slave goes down, its copy of the table vanishes. When the slave comes back up the log may still contain commands using the temporary table however, causing execution to barf (and replication to stop). To fix this condition, and get replication going again, we have to perform a manual copy of the master's database to the affected slave.

We believe the solution is to avoid temporary tables altogether, and to recreate the needed semantics in a replication-safe way. The refactoring for temp_store could look like:

1) Create 'temp_store' as a permanent table.
2) Add a 'connid' column, storing the MySQL connection id.
3) Use CONNECTION_ID() in all inserts to populate 'connid'.
4) Qualify all updates and queries using 'temp_store' with
   WHERE connid = CONNECTION_ID() or equivalent.
5) Clear entries from 'temp_store' at transaction boundaries with
   DELETE FROM temp_store WHERE connid = CONNECTION_ID().

I plan to work on this in the near future.

Stefan


On 24. Jul 2008, at 18:33, Shane Hathaway wrote:

We hope to tackle the main issue (a.k.a. better-not-use-temporary- tables-with-mysql-replication-at-all) in a later installment.

Ok. Conceptually, what we need is a way for each connection to write to a scratch table that no other connection can see. Is there a better way to do that than temporary tables?

--
Anything that, in happening, causes something else to happen,
causes something else to happen.  --Douglas Adams


_______________________________________________
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

Reply via email to