Thomas Bruederli thomas-tZJS5fsp0j0qcZcGjlUOXw@public.gmane.org writes:
On Sat, Dec 19, 2015 at 1:30 AM, Micah Anderson micah-sGOZH3hwPm2sTnJN9+BGXg@public.gmane.org wrote:
Hi,
I've got the database_attachments plugin enabled because I have multiple machines with a memcached session store. It seems to work fairly well... except that I am replicating the database to a read-only slave for redundancy (and read-balancing) and the replication keeps breaking.
The slave will get this error:
Could not execute Delete_rows event on table roundcube.cache; Can't find record in 'cache', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's master log mysql-bin.000151, end_log_pos 182376
I've got a db_dnsw and db_dnsr configured in my roundcube config, could it be that roundcube is also writing to the db_dnsr causing entries to be removed on the slave, and then when the replication happens it fails because it has been removed on the slave already?
This should not happen. All INSERT/UPDATE/DELETE queries go to dsnw and also subsequent reads from the same PHP process should use that connection in order to not hit any replication delays. Can you log all SQL queries and compare that with the replication log?
Indeed, it shouldn't happen... unfortunately, it has happened now four times this week, and it just happened again today. Originally, I tried to skip the query in the replica and continue replication, but then it happened again so I thought maybe skipping that query was a bad idea, so I re-setup replication from scratch... and then it happened again.
Unfortunately, right now I don't have the space to log all SQL queries, its too much data for what we have available on that system :(
I tried to look at mysql-bin.000151 for the 182376 event, but couldn't find it.
Ideally, I wouldn't even replicate the roundcube.cache table, because its a *lot* of data, but if I dont replicate it, I suspect the db_dnsr would fail to find the records?
You might look into the 'db_table_dsn' config option: https://github.com/roundcube/roundcubemail/blob/master/config/defaults.inc.p...
This was added for exactly this case. You can define 'r' or 'w' connections to be used on a per-table basis.
Thanks for that, although I don't quite understand how this works, the example you linked to seems to be related to the cache table, and it has 'r' set for cache, cache_index, cache_thread and cache_messages... what does this example do? It sets that table read-only on the master? How does this allow configuration on a per-table basis?
Here it is for reference:
// Mapping of table names and connections to use for ALL operations. // This can be used in a setup with replicated databases and a DB master // where read/write access to cache tables should not go to master. $config['db_table_dsn'] = array( // 'cache' => 'r', // 'cache_index' => 'r', // 'cache_thread' => 'r', // 'cache_messages' => 'r', );
thanks for the reply! micah