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 at
example.com)<54415><BmevY0hpMN5/AAAB>:
> Error: last_login_dict: Failed to write value for user
> user at 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 at
example.com)<54415><BmevY0hpMN5/AAAB>:
> Error: last_login_dict: Failed to write value for user
> user at 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 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 {
> 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 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
> }
> 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 = </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
> }