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)
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:"flag";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.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 :)
micah