Micah Anderson <mi...@riseup.net> writes: > Thomas Bruederli <thomas-tzjs5fsp0j0qczcgjlu...@public.gmane.org> > writes: > >> On Sat, Dec 19, 2015 at 1:30 AM, Micah Anderson >> <micah-sgozh3hwpm2stnjn9+b...@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) mysql> SELECT * FROM mysql.general_log where user_host LIKE 'roundcube%' and command_type NOT LIKE 'Connect' and command_type NOT LIKE 'Quit' and argument not LIKE 'SELECT%' and argument not LIKE 'SET NAMES%' and argument not LIKE 'SHOW%'; +---------------------+-------------------------------------+-----------+-----------+--------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------------------------------------------+ | event_time | user_host | thread_id | server_id | command_type | argument | +---------------------+-------------------------------------+-----------+-----------+--------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------------------------------------------+ | 2015-12-29 00:22:03 | roundcube[roundcube] @ [127.0.0.1] | 18882611 | 3 | Query | DELETE FROM `cache` WHERE `user_id` = '85143' AND `cache_key` LIKE 'ATTACHqifhfjuoliugv5l1d9i39hvqu2.%' | | 2015-12-29 00:22:03 | roundcube[roundcube] @ [127.0.0.1] | 18882600 | 3 | Query | UPDATE `users` SET `last_login` = now() WHERE `user_id` = '31643' | | 2015-12-29 00:22:09 | roundcube[roundcube] @ [127.0.0.1] | 18882446 | 3 | Query | DELETE FROM `cache` WHERE `user_id` = '263603' AND `cache_key` LIKE 'ATTACHisvbumgsk5ouoi367ilhkej5m2.18922776125681d17a7aa22%' | | 2015-12-29 00:22:16 | roundcube[roundcube] @ [127.0.0.1] | 18882737 | 3 | Query | UPDATE `users` SET `last_login` = now() WHERE `user_id` = '28113' | | 2015-12-29 00:22:24 | roundcube[roundcube] @ [127.0.0.1] | 18882792 | 3 | Query | UPDATE `users` SET `last_login` = now() WHERE `user_id` = '28913' | | 2015-12-29 00:22:24 | roundcube[roundcube] @ [127.0.0.1] | 18882797 | 3 | Query | DELETE FROM `cache` WHERE `user_id` = '31643' AND `cache_key` LIKE 'ATTACHfvs4pntjvdt54pvh791nd5l4i1.%' | | 2015-12-29 00:22:25 | roundcube[roundcube] @ [127.0.0.1] | 18882803 | 3 | Query | UPDATE `users` SET `last_login` = now() WHERE `user_id` = '514963' | | 2015-12-29 00:22:26 | roundcube[roundcube] @ [127.0.0.1] | 18882810 | 3 | Query | UPDATE `users` SET `last_login` = now() WHERE `user_id` = '211493' | | 2015-12-29 00:22:28 | roundcube[roundcube] @ [127.0.0.1] | 18882832 | 3 | Query | DELETE FROM `cache` WHERE `user_id` = '250093' AND `cache_key` LIKE 'ATTACHmp7m6d9qppv4kr0ulla861h0v0.%' | | 2015-12-29 00:22:53 | roundcube[roundcube] @ [127.0.0.1] | 18882957 | 3 | Query | UPDATE `users` SET `last_login` = now() WHERE `user_id` = '20893' | | 2015-12-29 00:22:55 | roundcube[roundcube] @ [127.0.0.1] | 18883026 | 3 | Query | UPDATE `users` SET `last_login` = now() WHERE `user_id` = '619173' | | 2015-12-29 00:23:14 | roundcube[roundcube] @ [127.0.0.1] | 18882991 | 3 | Query | UPDATE `users` SET `last_login` = now() WHERE `user_id` = '377533' | | 2015-12-29 01:30:11 | roundcube[roundcube] @ [127.0.0.1] | 18917716 | 3 | Query | UPDATE `users` SET `last_login` = now() WHERE `user_id` = '55803' | | 2015-12-29 01:30:11 | roundcube[roundcube] @ [127.0.0.1] | 18917596 | 3 | Query | UPDATE `users` SET `last_login` = now() WHERE `user_id` = '446603' | | 2015-12-29 01:30:20 | roundcube[roundcube] @ [127.0.0.1] | 18917657 | 3 | Query | UPDATE `users` SET `last_login` = now() WHERE `user_id` = '485343' | | 2015-12-29 01:30:29 | roundcube[roundcube] @ [127.0.0.1] | 18917514 | 3 | Query | DELETE FROM `cache` WHERE `user_id` = '72113' AND `cache_key` LIKE 'ATTACH36nq1uh6qr32lq2ecjtm8pa6m7.17460303925681e13b51d0c%' | | 2015-12-29 01:30:29 | roundcube[roundcube] @ [127.0.0.1] | 18918008 | 3 | Query | DELETE FROM `cache` WHERE `user_id` = '484413' AND `cache_key` LIKE 'ATTACHfjmgl3j151qgg3d4acu35lsre0.%' | | 2015-12-29 01:30:29 | roundcube[roundcube] @ [127.0.0.1] | 18917660 | 3 | Query | UPDATE `users` SET `preferences` = 'a:14:{s:16:\"message_sort_col\";s:4:\"date\";s:18:\"message_sort_order\";s:3:\"ASC\";s:11:\"search_mods\";a:3:{s:1:\"*\";a:2:{s:7:\"subject\";i:1;s:4:\"from\";i:1;}s:4:\"Sent\";a:2:{s:7:\"subject\";i:1;s:2:\"to\";i:1;}s:6:\"Drafts\";a:2:{s:7:\"subject\";i:1;s:2:\"to\";i:1;}}s:17:\"message_threading\";a:3:{s:8:\"redacted\";b:0;s:5:\"INBOX\";b:0;s:9:\"redacted\";b:0;}s:12:\"mdn_requests\";i:2;s:13:\"mail_pagesize\";i:200;s:11:\"prefer_html\";b:1;s:12:\"display_next\";b:0;s:10:\"htmleditor\";i:1;s:10:\"reply_mode\";i:1;s:9:\"sig_below\";b:1;s:17:\"compose_responses\";a:1:{i:0;a:4:{s:4:\"name\";s:3:\"lol\";s:4:\"text\";s:6:\"lollek\";s:6:\"format\";s:4:\"text\";s:3:\"key\";s:16:\"9cdfb439c7876e70\";}}s:9:\"list_cols\";a:8:{i:0;s:7:\"threads\";i:1;s:7:\"subject\";i:2;s:6:\"status\";i:3;s:6:\"fromto\";i:4;s:4:\"date\";i:5;s:4:\"size\";i:6;s:4:\"fl ag\";i:7;s:10:\"attachment\";}s:11:\"client_hash\";s:32:\"6c4b47cb03199d019a39ad9fb461e018\";}', `language` = 'pl_PL' WHERE `user_id` = '658183' | | 2015-12-29 01:30:30 | roundcube[roundcube] @ [127.0.0.1] | 18917876 | 3 | Query | UPDATE `users` SET `last_login` = now() WHERE `user_id` = '37433' | | 2015-12-29 01:30:30 | roundcube[roundcube] @ [127.0.0.1] | 18917968 | 3 | Query | UPDATE `users` SET `last_login` = now() WHERE `user_id` = '74373' | | 2015-12-29 01:30:30 | roundcube[roundcube] @ [127.0.0.1] | 18917950 | 3 | Query | UPDATE `users` SET `last_login` = now() WHERE `user_id` = '126763' | | 2015-12-29 01:30:30 | roundcube[roundcube] @ [127.0.0.1] | 18917733 | 3 | Query | DELETE FROM `cache` WHERE `expires` < now() | | 2015-12-29 01:30:30 | roundcube[roundcube] @ [127.0.0.1] | 18917733 | 3 | Query | DELETE FROM `cache_shared` WHERE `expires` < now() | | 2015-12-29 01:30:30 | roundcube[roundcube] @ [127.0.0.1] | 18917733 | 3 | Query | DELETE FROM `cache_messages` WHERE `expires` < now() | | 2015-12-29 01:30:31 | roundcube[roundcube] @ [127.0.0.1] | 18917733 | 3 | Query | DELETE FROM `cache_index` WHERE `expires` < now() | | 2015-12-29 01:30:31 | roundcube[roundcube] @ [127.0.0.1] | 18917733 | 3 | Query | DELETE FROM `cache_thread` WHERE `expires` < now() | | 2015-12-29 01:30:32 | roundcube[roundcube] @ [127.0.0.1] | 18918155 | 3 | Query | UPDATE `users` SET `last_login` = now() WHERE `user_id` = '292603' | | 2015-12-29 01:30:37 | roundcube[roundcube] @ [127.0.0.1] | 18918193 | 3 | Query | UPDATE `users` SET `last_login` = now() WHERE `user_id` = '58623' | | 2015-12-29 01:30:39 | roundcube[roundcube] @ [127.0.0.1] | 18917938 | 3 | Query | DELETE FROM `cache` WHERE `user_id` = '335783' AND `cache_key` LIKE 'ATTACH5r67jlimb24etuhcvd638u4jf1.6098462345681e1118692c%' | | 2015-12-29 01:30:41 | roundcube[roundcube] @ [127.0.0.1] | 18918165 | 3 | Query | DELETE FROM `cache` WHERE `user_id` = '614823' AND `cache_key` LIKE 'ATTACH2hjt6m819f76h3hah3ibqfkda4.4718250645681dada9ba06%' | +---------------------+-------------------------------------+-----------+-----------+--------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------------------------------------------+ 30 rows in set (3 min 46.24 sec) mysql> As you can see, there are UPDATE and DELETE queries for the 'cache' and 'users' tables (last_login and preferences). To work around this, I've done: REVOKE ALL PRIVILEGES ON `roundcube`.* FROM 'roundcube'@'localhost' GRANT SELECT on `roundcube`.* TO 'roundcube'@'localhost'; flush priviledges; But obviously something is wrong here. >> 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? I'm still curious about how this config option works, I understand holidaze, etc. just dont want to lose this part in the thread :) micah _______________________________________________ Roundcube Users mailing list users@lists.roundcube.net http://lists.roundcube.net/mailman/listinfo/users
