Try adding no-unset to the dict parameter. Or use dedicated table for this information.
dict:no-unset:sql
Aki
On 08.04.2018 00:07, David Mehler wrote:
Hello,
I'm running dovecot 2.2 and trying to do last login tracking. I am using a MySQL database. When a user logs in I get this in the log:
2018-04-07 16:55:14 imap(user@example.com)<54415>
: Error: last_login_dict: Failed to write value for user user@example.com: dict-server returned failure: sql dict: commit failed: Field 'name' doesn't have a default value (reply took 0.018 secs (0.000 in dict wait, 0.009 in other ioloops, 0.001 in locks, async-id reply 0.000 secs ago, started on dict-server 0.006 secs ago, took 0.006 secs)) Here's an excerpt from my dovecot-dict-sql.conf.ext file:
map { pattern = shared/last-login/$user table = accounts value_field = last_login value_type = uint
fields { username = $user } }
The username does have a value. Can anyone see what I've got wrong?
Thanks. Dave.
doveconf -n 2018-04-07 16:55:14 imap(user@example.com)<54415>
: Error: last_login_dict: Failed to write value for user user@example.com: dict-server returned failure: sql dict: commit failed: Field 'name' doesn't have a default value (reply took 0.018 secs (0.000 in dict wait, 0.009 in other ioloops, 0.001 in locks, async-id reply 0.000 secs ago, started on dict-server 0.006 secs ago, took 0.006 secs)) # 2.3.1 (8e2f634): /usr/local/etc/dovecot/dovecot.conf # Pigeonhole version 0.5.1 (d9bc6dfe) # OS: FreeBSD 11.1-RELEASE-p4 amd64 # Hostname: localhost auth_cache_size = 24 M auth_cache_ttl = 18 hours auth_default_realm = example.com auth_mechanisms = plain login auth_realms = example.com nickandwill.com dict { acl = mysql:/usr/local/etc/dovecot/dovecot-dict-sql.conf.ext lastlogin = mysql:/usr/local/etc/dovecot/dovecot-dict-sql.conf.ext quota = mysql:/usr/local/etc/dovecot/dovecot-dict-sql.conf.ext } first_valid_gid = 999 first_valid_uid = 999 hostname = mail.example.com imap_idle_notify_interval = 10 mins last_valid_gid = 999 last_valid_uid = 999 lda_mailbox_autocreate = yes lda_mailbox_autosubscribe = yes lda_original_recipient_header = X-Original-To listen = 127.0.0.1 66.228.47.34 log_path = /var/log/dovecot/dovecot.log log_timestamp = "%Y-%m-%d %H:%M:%S " mail_access_groups = vmail mail_gid = vmail mail_home = /home/vmail/mailboxes/%d/%n mail_location = maildir:~/mail:LAYOUT=fs mail_plugins = acl mail_log notify quota trash virtual welcome zlib mail_privileged_group = vmail mail_server_admin = mailto:postmaster@example.com mail_uid = vmail mailbox_idle_check_interval = 59 secs managesieve_notify_capability = mailto managesieve_sieve_capability = fileinto reject envelope encoded-character vacation subaddress comparator-i;ascii-numeric relational regex imap4flags copy include variables body enotify environment mailbox date index ihave duplicate mime foreverypart extracttext spamtest spamtestplus virustest editheader imapflags notify imapsieve vnd.dovecot.imapsieve namespace { location = maildir:/home/vmail/public/:CONTROL=~/mail/public:INDEX=~/mail/public mailbox TestFolder { auto = subscribe comment = Public Folder for message sharing } prefix = Public/ separator = / subscriptions = yes type = public } namespace { hidden = no location = maildir:/home/vmail/shared/office/.Maildir:CONTROL=~/.Maildir/control/office:INDEX=~/.Maildir/index/office prefix = office/ separator = / } namespace inbox { inbox = yes location = mailbox Archives { auto = subscribe special_use = \Archive } mailbox Drafts { auto = subscribe special_use = \Drafts } mailbox Sent { auto = subscribe special_use = \Sent } mailbox Spam { auto = subscribe autoexpunge = 30 days special_use = \Junk } mailbox Trash { auto = subscribe autoexpunge = 30 days special_use = \Trash } mailbox virtual/All { comment = All my messages special_use = \All } prefix = separator = / type = private } namespace virtual { location = virtual:/usr/local/etc/dovecot/virtual:INDEX=~/virtual:CONTROL=~/virtual prefix = virtual/ separator = / } passdb { args = /usr/local/etc/dovecot/dovecot-sql.conf.ext driver = sql } plugin { acl = vfile:/usr/local/etc/dovecot/global-acls:cache_secs=300 acl_anyone = allow acl_globals_only = yes acl_shared_dict = proxy::acl fts = lucene fts_autoindex = yes fts_autoindex_max_recent_msgs = 80 fts_index_timeout = 90 fts_lucene = whitespace_chars=@. normalize no_snowball imapsieve_mailbox1_before = file:/home/vmail/sieve/global/learn-spam.sieve imapsieve_mailbox1_causes = COPY imapsieve_mailbox1_name = Spam imapsieve_mailbox2_before = file:/home/vmail/sieve/global/learn-ham.sieve imapsieve_mailbox2_causes = COPY imapsieve_mailbox2_from = Spam imapsieve_mailbox2_name = * last_login_dict = proxy::lastlogin last_login_key = last-login/%n mail_log_events = delete undelete expunge copy mailbox_delete mailbox_rename mail_log_fields = uid box msgid size quota = dict:User quota::proxy::quota quota_exceeded_message = Storage quota for this account has been exceeded, please try again later. quota_grace = 10%% quota_status_nouser = DUNNO quota_status_overquota = 552 5.2.2 Mailbox is full quota_status_success = DUNNO quota_vsizes = true quota_warning = storage=100%% quota-exceeded 100 %u quota_warning2 = storage=95%% quota-warning 95 %u quota_warning3 = storage=90%% quota-warning 90 %u quota_warning4 = storage=85%% quota-warning 85 %u quota_warning5 = storage=75%% quota-warning 75 %u sieve = ~/.dovecot.sieve sieve_before = /home/vmail/sieve/before.d sieve_default = /home/vmail/sieve/default.sieve sieve_dir = ~/sieve sieve_extensions = +notify +imapflags +spamtest +spamtestplus +virustest +editheader sieve_global_dir = /home/vmail/sieve sieve_global_extensions = +vnd.dovecot.pipe +vnd.dovecot.execute +vnd.dovecot.environment sieve_max_redirects = 30 sieve_max_script_size = 1M sieve_pipe_bin_dir = /home/vmail/sieve sieve_plugins = sieve_imapsieve sieve_extprograms sieve_spamtest_max_header = X-Spamd-Result: default: [[:alnum:]]+ \[-?[[:digit:]]+\.[[:digit:]]+ / (-?[[:digit:]]+\.[[:digit:]]+)\] sieve_spamtest_status_header = X-Spamd-Result: default: [[:alnum:]]+ \[(-?[[:digit:]]+\.[[:digit:]]+) / -?[[:digit:]]+\.[[:digit:]]+\] sieve_spamtest_status_type = score sieve_user_log = /home/vmail/sieve/sieve_error.log sieve_virustest_status_header = X-Virus-Scan: Found to be (.+)\. sieve_virustest_status_type = text sieve_virustest_text_value1 = clean sieve_virustest_text_value5 = infected trash = /usr/local/etc/dovecot/trash.conf welcome_script = welcome %u postmaster@%d welcome_wait = yes } postmaster_address = postmaster@example.com protocols = imap lmtp sieve sendmail_path = /usr/local/sbin/sendmail service auth-worker { user = vmail } service auth { unix_listener /var/spool/postfix/private/auth { group = postfix mode = 0666 user = postfix } unix_listener auth-userdb { group = vmail mode = 0666 user = vmail } } service dict { unix_listener dict { group = vmail mode = 0660 user = vmail } user = root } service imap-login { inet_listener imap { address = 127.0.0.1 } inet_listener imaps { address = 66.228.47.34 port = 993 ssl = yes } } service imap-postlogin { executable = script-login /usr/local/etc/dovecot/trackauth.sh user = vmail } service imap { executable = imap } service lmtp { executable = lmtp unix_listener /var/spool/postfix/private/dovecot-lmtp { group = postfix mode = 0666 user = postfix } } service managesieve-login { inet_listener sieve { address = 127.0.0.1 port = 4190 } } service quota-status { client_limit = 1 executable = quota-status -p postfix unix_listener /var/spool/postfix/private/dovecot-quota { group = postfix mode = 0660 user = postfix } } service quota-warning { executable = script /usr/local/etc/dovecot/quota-warning.sh unix_listener quota-warning { group = vmail mode = 0660 user = vmail } user = vmail } service welcome { executable = script /usr/local/etc/dovecot/welcome.sh unix_listener welcome { user = vmail } user = vmail } ssl = required ssl_cert =