[ZODB-Dev] RelStorage: Clearing temp_store in replication-friendly way

[Shane Hathaway]

Stefan H. Holek wrote:
> 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.

I would expect that when a slave goes down, the slave must replay all of 
the statements since the beginning of a transaction, including the 
statements that create temporary tables.  Does it not work that way?

Do slaves respect transaction boundaries?  If they don't, then ZODB 
clients of slaves will miss object updates, leading to sporadically 
inconsistent ZODB caches, especially under load.

> 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.

We can do that, but I hope it doesn't impact performance too much.  It 
seems a shame to not use a RAM-based temporary table.  OTOH, I've tried 
to structure RelStorage to allow changes like this without too much effort.

Shane