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...@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...@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...@exemple.com | abcd:aabb:cc:ddd::e | 2019-09-18
14:14:33 | 1568816073 |
| test-email...@exemple.com | abcd:aabb:cc:ddd::e | 2019-09-18
14:14:33 | 1568816073 |
| test-email...@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
