last_login_dict: Failed to write value: dict-server returned failure: sql dict: commit failed

[Nicolas DEFFAYET]

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 at 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 at sqlserver as
anonymous on mail
		    4 Connect	dovecot at sqlserver as anonymous on
mail
		    4 Query	BEGIN
		    4 Query	INSERT INTO dovecot_last_login
(timestamp,email,ip_address) VALUES (1568814599,'test-email-cc at exemple.
com','abcd:aabb:cc:ddd::e') ON DUPLICATE KEY UPDATE
timestamp=1568814599
		    5 Connect	dovecot at sqlserver as anonymous on
mail
		    4 Query	COMMIT
---

In the Dovecot mail server log:
---
       1 mailserver dovecot: imap(test-email-bb at 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 at 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 at 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 at 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 at exemple.com  | abcd:aabb:cc:ddd::e | 2019-09-18
14:14:33 | 1568816073 |
| test-email-aa at exemple.com  | abcd:aabb:cc:ddd::e | 2019-09-18
14:14:33 | 1568816073 |
| test-email-bb at 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 at 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 at 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