dovecot lastlogin tracking

Tue Apr 3 02:10:42 EEST 2018

Hello,

I am trying to get Dovecot 2.3.1 to track lastlogins of users. I'm
using a MySQL database. When I log in I am getting this in the error
log:

2018-04-02 18:24:21 imap(user at domain.com)<61855><6R0rDeVodcl/AAAB>:
Error: last_login_dict: Failed to write value for user
user at domain.com: dict-server returned failure: sql dict: commit
failed: Field 'name' doesn't have a default value (reply took 0.026
secs (0.000 in dict wait, 0.020 in other ioloops, 0.001 in locks,
async-id reply 0.000 secs ago, started on dict-server 0.019 secs ago,
took 0.005 secs))

Here's an excerpt from my dovecot-dict-sql.conf.ext file:
map {
  pattern = shared/last-login/$username
  table = accounts
  username_field = username
  value_field = lastlogin
  fields {
    username = $username
  }
}

I've got all users in the accounts table and there is a lastlogin column:

describe accounts;
+-----------+------------------+------+-----+---------+----------------+
| Field     | Type             | Null | Key | Default | Extra          |
+-----------+------------------+------+-----+---------+----------------+
| id        | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| name      | varchar(255)     | NO   |     | NULL    |                |
| username  | varchar(64)      | NO   | MUL | NULL    |                |
| domain    | varchar(255)     | NO   | MUL | NULL    |                |
| password  | varchar(255)     | NO   |     | NULL    |                |
| quota     | int(10) unsigned | YES  |     | 0       |                |
| enabled   | tinyint(1)       | YES  |     | 0       |                |
| sendonly  | tinyint(1)       | YES  |     | 0       |                |
| lastlogin | int(11) unsigned | NO   |     | 0       |                |

Thanks.
Dave.

doveconf -n
# 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 example2.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 xxx.xxx.xxx.xxx
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 at 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 {
  list = children
  location = maildir:/home/vmail/mail/%%d/%%n:LAYOUT=fs:INDEX=/home/vmail/indexes/%d/%n/shared/%%u:INDEXPVT=/home/vmail/indexes/%d/%n/shared/%%u
  prefix = shared/%%d/%%n/
  separator = /
  subscriptions = no
  type = shared
}
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
  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
  welcome_wait = yes
}
postmaster_address = postmaster at 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
    port = 143
  }
  inet_listener imaps {
    address = xxx.xxx.xxx.xxx
    port = 993
    ssl = yes
  }
}
service imap {
  executable = imap
}
service 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 = </usr/local/etc/ssl/acme/example.com/fullchain.pem
ssl_cipher_list =
ECDHE-ECDSA-AES256-GCM-SHA384:ECDHE-RSA-AES256-GCM-SHA384:ECDHE-ECDSA-CHACHA20-POLY1305:ECDHE-RSA-CHACHA20-POLY1305:ECDHE-ECDSA-AES128-GCM-SHA256:ECDHE-RSA-AES128-GCM-SHA256:ECDHE-ECDSA-AES256-SHA384:ECDHE-RSA-AES256-SHA384:ECDHE-ECDSA-AES128-SHA256:ECDHE-RSA-AES128-SHA256
ssl_dh =  # hidden, use -P to show it
ssl_key =  # hidden, use -P to show it
ssl_min_protocol = TLSv1.2
ssl_prefer_server_ciphers = yes
userdb {
  args = /usr/local/etc/dovecot/dovecot-sql.conf.ext
  driver = sql
}
protocol lmtp {
  info_log_path = /var/log/dovecot/dovecot-lmtp.log
  log_path = /var/log/dovecot/dovecot-lmtp-errors.log
  mail_plugins = acl mail_log notify quota trash virtual welcome zlib sieve
}
protocol lda {
  mail_plugins = acl mail_log notify quota trash virtual welcome zlib sieve
}
protocol imap {
  mail_max_userip_connections = 20
  mail_plugins = acl mail_log notify quota trash virtual welcome zlib
imap_acl imap_quota imap_sieve imap_zlib last_login fts fts_lucene
}
protocol sieve {
  info_log_path = /var/log/dovecot/dovecot-sieve.log
  log_path = /var/log/dovecot/dovecot-sieve-errors.log
}