Hello,
I get last_login_dict: Failed to write value: dict-server returned failure: sql dict: commit failed randomly in the logs:
1 mailserver dovecot: imap(test-email-bb@exemple.com) <NtiT9H2SFIVcuGID> [xxx.xxx.xxx.xxx]: Error: last_login_dict: Failed to write value: dict-server returned failure: sql dict: commit failed: INSERT, UPDATE command denied to user 'dovecot'@'sqlserver' for table 'dovecot_last_login' (reply took 0.003 secs (0.000 in dict wait, 0.003 in other ioloops, 0.000 in locks, async-id reply 0.002 secs ago, started on dict-server 0.003 secs ago, took 0.003 secs))
The goal is to keep the unique couple email and ip_address in the table of SQL database and update only timestamp if email and ip_address exist on same row.
I have enabled auth_debug and mail_debug but it don't show last_login_dict SQL queries in the log (auth SQL queries are shown).
So I have enabled SQL debug on SQL server.
In the SQL server log:
190918 13:49:59 3 Connect dovecot@sqlserver as anonymous on mail 4 Connect dovecot@sqlserver as anonymous on mail 4 Query BEGIN 4 Query INSERT INTO dovecot_last_login (timestamp,email,ip_address) VALUES (1568814599,'test-email-cc@exemple. com','abcd:aabb:cc:ddd::e') ON DUPLICATE KEY UPDATE timestamp=1568814599 5 Connect dovecot@sqlserver as anonymous on mail 4 Query COMMIT
In the Dovecot mail server log:
1 mailserver dovecot: imap(test-email-bb@exemple.com) <NtiT9H2SFIVcuGID> [xxx.xxx.xxx.xxx]: Error: last_login_dict: Failed to write value: dict-server returned failure: sql dict: commit failed: INSERT, UPDATE command denied to user 'dovecot'@'sqlserver' for table 'dovecot_last_login' (reply took 0.003 secs (0.000 in dict wait, 0.003 in other ioloops, 0.000 in locks, async-id reply 0.002 secs ago, started on dict-server 0.003 secs ago, took 0.003 secs))
In the SQL server log:
17 Query BEGIN
17 Query INSERT INTO dovecot_last_login(timestamp,email,ip_address) VALUES (1568816073,'test-email-aa@exemple. com','abcd:aabb:cc:ddd::e') ON DUPLICATE KEY UPDATE timestamp=1568816073 17 Query COMMIT 19 Query BEGIN 19 Query INSERT INTO dovecot_last_login (timestamp,email,ip_address) VALUES (1568816073,'test-email-cc@exemple. com','abcd:aabb:cc:ddd::e') ON DUPLICATE KEY UPDATE timestamp=1568816073 19 Query COMMIT 20 Query BEGIN 20 Query INSERT INTO dovecot_last_login (timestamp,email,ip_address) VALUES (1568816073,'test-email-bb@exemple. com','abcd:aabb:cc:ddd::e') ON DUPLICATE KEY UPDATE timestamp=1568816073 20 Query COMMIT
Database contain: MariaDB [mail]> SELECT * FROM mail.dovecot_last_login WHERE ip_address='abcd:aabb:cc:ddd::e'; +----------------------------+---------------------+------------------- --+------------+ | email | ip_address | datetime | timestamp | +----------------------------+---------------------+------------------- --+------------+ | test-email-cc@exemple.com | abcd:aabb:cc:ddd::e | 2019-09-18 14:14:33 | 1568816073 | | test-email-aa@exemple.com | abcd:aabb:cc:ddd::e | 2019-09-18 14:14:33 | 1568816073 | | test-email-bb@exemple.com | abcd:aabb:cc:ddd::e | 2019-09-18 14:14:33 | 1568816073 | +----------------------------+---------------------+------------------- --+------------+ 3 rows in set (0.00 sec)
MariaDB [mail]>
The SQL request executed directly on SQL server works: MariaDB [mail]> INSERT INTO dovecot_last_login (timestamp,email,ip_address) VALUES (1568815883,'test-email-aa@exemple. com','abcd:aabb:cc:ddd::e') ON DUPLICATE KEY UPDATE timestamp=1568815883; MariaDB [mail]> INSERT INTO dovecot_last_login (timestamp,email,ip_address) VALUES (1568815883,'test-email-bb@exemple. com','abcd:aabb:cc:ddd::e') ON DUPLICATE KEY UPDATE timestamp=1568815883;
Did you have any tips how fix this issue ?
Many thanks
Best Regards,
-- Nicolas DEFFAYET