Thomas Bruederli <[email protected]> writes:
> On Sat, Dec 19, 2015 at 1:30 AM, Micah Anderson > <[email protected]> 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.php#L48 > > 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 _______________________________________________ Roundcube Users mailing list [email protected] http://lists.roundcube.net/mailman/listinfo/users
