On Tue, Dec 29, 2015 at 8:30 PM, micah micah@riseup.net wrote:
Micah Anderson micah@riseup.net writes:
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:
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?
So I turned on the general global query log on the slave (SET global general_log = 1;) and waited for replication to break again. Eventually it failed again and I looked at what queries were done on the replicated slave that were not SELECT queries and found these:
(in case the formatting is impossible to read, you can view these on this pastebin: https://share.riseup.net/#aqcIrGmnJ_2LmC2EtphXCQ)
[...]
As you can see, there are UPDATE and DELETE queries for the 'cache' and 'users' tables (last_login and preferences).
Hmm, that doesn't look good. Can you maybe share your database connection config options with us? Config options 'db_dsnw', 'db_dsnr', 'db_dsnw_noread', 'db_persistent' and 'db_table_dsn' are relevant for this. Replace sensitive information accordingly before posting them here.
The decision which connection to use is made in rcube_db::dsn_select() https://github.com/roundcube/roundcubemail/blob/master/program/lib/Roundcube...
This is done for each query and we need to investigate if there's a major issue in this part of the code. Although you're the first to report problems like this...
To work around this, I've done:
REVOKE ALL PRIVILEGES ON
roundcube.* FROM 'roundcube'@'localhost' GRANT SELECT onroundcube.* TO 'roundcube'@'localhost'; flush priviledges;But obviously something is wrong here.
That's not the preferred way to do it and will probably result in database failures due to missing prifileges.
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?
I'm still curious about how this config option works, I understand holidaze, etc. just dont want to lose this part in the thread :)
Yeah, sorry for the delay.
Anyway, the 'db_table_dsn' property holds a map of table names and connection identifiers. You can set either 'r' for using the 'db_dsnr' or 'w' for using the 'db_dsnw' connection for all interactions with the according table. This will overrule the default determination whether to use the read or write connection.
The example given in the default config would keep all cache data in the local database. In such a scenario, you would not create these tables on the master and therefore also not replicate them to the slaves.
However, the database_attachments plugin uses the cache table for storing the uploaded attachments. This is something you want to share amongst all nodes and therefore you should not be using this configuration for the 'cache' table.
Kind regards, Thomas