David Mehler
2018-Apr-10 16:31 UTC
two unrelated issues, lastlogin, and an out of memory fatal error
Hello,
I've got a few misc issues that I'm trying to get a handle on.
I'm having what is probably a simple error with last logins. It's not
working, unable to write values to the database. Here's the log lines:
2018-04-10 10:43:34 imap-login: Info: Login: user=<user at domain.com>,
method=PLAIN, rip=127.0.0.1, lip=127.0.0.1, mpid=75934, TLS,
session=<db0EjH9pOix/AAAB>
2018-04-10 10:43:34 imap(user at
domain.com)<75934><db0EjH9pOix/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.013
secs (0.000 in dict wait, 0.012 in other ioloops, 0.000 in locks,
async-id reply 0.002 secs ago, started on dict-server 0.005 secs ago,
took 0.005 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
}
}
and the relevant database table:
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 | |
| last_login | int(11) | YES | | NULL | |
Second issue, I've used imapsync to download my gmail messages to
dovecot. I am atempting to access these messages and getting:
2018-04-08 11:11:58
indexer-worker(user at
domain.com)<44193><4GzoqFdpisJ/AAAB:StoZDD4xylqhrAAA0J78UA>:
Info: Indexed 0 messages in INBOX
2018-04-08 11:11:58
indexer-worker(user at
domain.com)<44193><4GzoqFdpisJ/AAAB:0AaXFD4xylqhrAAA0J78UA>:
Info: Indexed 0 messages in INBOX
2018-04-08 11:11:58
indexer-worker(user at
domain.com)<44193><4GzoqFdpisJ/AAAB:CP3TIT4xylqhrAAA0J78UA>:
Info: Indexed 0 messages in INBOX
2018-04-08 11:11:58
indexer-worker(user at
domain.com)<44193><4GzoqFdpisJ/AAAB:eNEWMT4xylqhrAAA0J78UA>:
Info: Indexed 0 messages in INBOX
2018-04-08 11:15:28 imap(user at
domain.com)<21374><4GzoqFdpisJ/AAAB>:
Fatal: block_alloc(8388608): Out of memory
2018-04-08 11:15:28 imap(user at
domain.com)<21374><4GzoqFdpisJ/AAAB>:
Fatal: master: service(imap): child 21374 returned error 83 (Out of
memory (service imap { vsz_limit=256 MB }, you may need to increase
it) - set CORE_OUTOFMEM=1 environment to get core dump)
when I atempt to do so. For completeness the imapsync command I used was:
imapsync --host1 imap.gmail.com --user1 Username --password1 Password
--host2 localhost --user2 username --password2 Password --ssl1
--useheader 'Message-Id' --regextrans2
's/\[Gmail\]/user\@domain/'
Any help appreciated on either issue.
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 = domain.com
auth_mechanisms = plain login
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 = xxxx.xxxx.xxxx
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 davemehler.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/:LAYOUT=fs:CONTROL=~/mail/public:INDEX=~/mail/public
mailbox office {
auto = subscribe
comment = Public Folder for message sharing
}
prefix = public/
separator = /
subscriptions = yes
type = public
}
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_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/report-spam.sieve
imapsieve_mailbox1_causes = COPY
imapsieve_mailbox1_name = Spam
imapsieve_mailbox2_before = file:/home/vmail/sieve/global/report-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 xxx
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 = xxx.xxx.xxx.xxx
port = 993
ssl = yes
}
}
service imap-postlogin {
executable = script-login /usr/local/etc/dovecot/trackauth.sh
user = vmail
}
service imap {
executable = imap imap-postlogin
}
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/davemehler.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
}
Aki Tuomi
2018-Apr-11 08:48 UTC
two unrelated issues, lastlogin, and an out of memory fatal error
Hi! On 10.04.2018 19:31, David Mehler wrote:> Hello, > > I've got a few misc issues that I'm trying to get a handle on. > > I'm having what is probably a simple error with last logins. It's not > working, unable to write values to the database. Here's the log lines: > > 2018-04-10 10:43:34 imap-login: Info: Login: user=<user at domain.com>, > method=PLAIN, rip=127.0.0.1, lip=127.0.0.1, mpid=75934, TLS, > session=<db0EjH9pOix/AAAB> > 2018-04-10 10:43:34 imap(user at domain.com)<75934><db0EjH9pOix/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.013 > secs (0.000 in dict wait, 0.012 in other ioloops, 0.000 in locks, > async-id reply 0.002 secs ago, started on dict-server 0.005 secs ago, > took 0.005 secs))It's attempting to insert a new row there for some reason. I'd recommend using separate table for last-login values.> 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 > } > } > > and the relevant database table: > 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 | | > > | last_login | int(11) | YES | | NULL | | > > > Second issue, I've used imapsync to download my gmail messages to > dovecot. I am atempting to access these messages and getting: > 2018-04-08 11:11:58 > indexer-worker(user at domain.com)<44193><4GzoqFdpisJ/AAAB:StoZDD4xylqhrAAA0J78UA>: > Info: Indexed 0 messages in INBOX > 2018-04-08 11:11:58 > indexer-worker(user at domain.com)<44193><4GzoqFdpisJ/AAAB:0AaXFD4xylqhrAAA0J78UA>: > Info: Indexed 0 messages in INBOX > 2018-04-08 11:11:58 > indexer-worker(user at domain.com)<44193><4GzoqFdpisJ/AAAB:CP3TIT4xylqhrAAA0J78UA>: > Info: Indexed 0 messages in INBOX > 2018-04-08 11:11:58 > indexer-worker(user at domain.com)<44193><4GzoqFdpisJ/AAAB:eNEWMT4xylqhrAAA0J78UA>: > Info: Indexed 0 messages in INBOX > 2018-04-08 11:15:28 imap(user at domain.com)<21374><4GzoqFdpisJ/AAAB>: > Fatal: block_alloc(8388608): Out of memory > 2018-04-08 11:15:28 imap(user at domain.com)<21374><4GzoqFdpisJ/AAAB>: > Fatal: master: service(imap): child 21374 returned error 83 (Out of > memory (service imap { vsz_limit=256 MB }, you may need to increase > it) - set CORE_OUTOFMEM=1 environment to get core dump) > > when I atempt to do so. For completeness the imapsync command I used was: > > imapsync --host1 imap.gmail.com --user1 Username --password1 Password > --host2 localhost --user2 username --password2 Password --ssl1 > --useheader 'Message-Id' --regextrans2 's/\[Gmail\]/user\@domain/' >Increase memory, your cache takes more memory than you have anticipated.> Any help appreciated on either issue. > 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 = domain.com > auth_mechanisms = plain login > 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 = xxxx.xxxx.xxxx > 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 davemehler.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/:LAYOUT=fs:CONTROL=~/mail/public:INDEX=~/mail/public > mailbox office { > auto = subscribe > comment = Public Folder for message sharing > } > prefix = public/ > separator = / > subscriptions = yes > type = public > } > 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_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/report-spam.sieve > imapsieve_mailbox1_causes = COPY > imapsieve_mailbox1_name = Spam > imapsieve_mailbox2_before = file:/home/vmail/sieve/global/report-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 xxx > 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 = xxx.xxx.xxx.xxx > port = 993 > ssl = yes > } > } > service imap-postlogin { > executable = script-login /usr/local/etc/dovecot/trackauth.sh > user = vmail > } > service imap { > executable = imap imap-postlogin > } > 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/davemehler.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 > }
David Mehler
2018-Apr-11 15:23 UTC
two unrelated issues, lastlogin, and an out of memory fatal error
Hello Aki, Thanks for your suggestion. I followed it and the wiki at: https://wiki.dovecot.org/Plugins/LastLogin Good news, I get a record in the users table, with the username that just logged in. This is good. Bad news is I got unexpected output from the last_login time stamp. Here's the table and the user: mysql> describe users; +------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +------------+--------------+------+-----+---------+-------+ | userid | varchar(255) | NO | PRI | NULL | | | last_login | int(11) | YES | | NULL | | +------------+--------------+------+-----+---------+-------+ 2 rows in set (0.00 sec) mysql> select * from users; +------------------------+------------+ | userid | last_login | +------------------------+------------+ | user at domain.com | 1523459718 | +------------------------+------------+ 1 row in set (0.01 sec) The issue is the 1523459718 I was expecting something like a time stamp. Is this fixable? Also, can I use last_login to see on which IP the user last logged in from? Any ideas on my other issue, the one on the running out of memory when attempting to access the imported gmail messages? Thanks for your help so far. Thanks. Dave. On 4/11/18, Aki Tuomi <aki.tuomi at dovecot.fi> wrote:> Hi! > > > On 10.04.2018 19:31, David Mehler wrote: >> Hello, >> >> I've got a few misc issues that I'm trying to get a handle on. >> >> I'm having what is probably a simple error with last logins. It's not >> working, unable to write values to the database. Here's the log lines: >> >> 2018-04-10 10:43:34 imap-login: Info: Login: user=<user at domain.com>, >> method=PLAIN, rip=127.0.0.1, lip=127.0.0.1, mpid=75934, TLS, >> session=<db0EjH9pOix/AAAB> >> 2018-04-10 10:43:34 imap(user at domain.com)<75934><db0EjH9pOix/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.013 >> secs (0.000 in dict wait, 0.012 in other ioloops, 0.000 in locks, >> async-id reply 0.002 secs ago, started on dict-server 0.005 secs ago, >> took 0.005 secs)) > It's attempting to insert a new row there for some reason. I'd recommend > using separate table for last-login values. > >> 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 >> } >> } >> >> and the relevant database table: >> 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 | | >> >> | last_login | int(11) | YES | | NULL | | >> >> >> Second issue, I've used imapsync to download my gmail messages to >> dovecot. I am atempting to access these messages and getting: >> 2018-04-08 11:11:58 >> indexer-worker(user at domain.com)<44193><4GzoqFdpisJ/AAAB:StoZDD4xylqhrAAA0J78UA>: >> Info: Indexed 0 messages in INBOX >> 2018-04-08 11:11:58 >> indexer-worker(user at domain.com)<44193><4GzoqFdpisJ/AAAB:0AaXFD4xylqhrAAA0J78UA>: >> Info: Indexed 0 messages in INBOX >> 2018-04-08 11:11:58 >> indexer-worker(user at domain.com)<44193><4GzoqFdpisJ/AAAB:CP3TIT4xylqhrAAA0J78UA>: >> Info: Indexed 0 messages in INBOX >> 2018-04-08 11:11:58 >> indexer-worker(user at domain.com)<44193><4GzoqFdpisJ/AAAB:eNEWMT4xylqhrAAA0J78UA>: >> Info: Indexed 0 messages in INBOX >> 2018-04-08 11:15:28 imap(user at domain.com)<21374><4GzoqFdpisJ/AAAB>: >> Fatal: block_alloc(8388608): Out of memory >> 2018-04-08 11:15:28 imap(user at domain.com)<21374><4GzoqFdpisJ/AAAB>: >> Fatal: master: service(imap): child 21374 returned error 83 (Out of >> memory (service imap { vsz_limit=256 MB }, you may need to increase >> it) - set CORE_OUTOFMEM=1 environment to get core dump) >> >> when I atempt to do so. For completeness the imapsync command I used was: >> >> imapsync --host1 imap.gmail.com --user1 Username --password1 Password >> --host2 localhost --user2 username --password2 Password --ssl1 >> --useheader 'Message-Id' --regextrans2 's/\[Gmail\]/user\@domain/' >> > > Increase memory, your cache takes more memory than you have anticipated. > >> Any help appreciated on either issue. >> 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 = domain.com >> auth_mechanisms = plain login >> 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 = xxxx.xxxx.xxxx >> 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 davemehler.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/:LAYOUT=fs:CONTROL=~/mail/public:INDEX=~/mail/public >> mailbox office { >> auto = subscribe >> comment = Public Folder for message sharing >> } >> prefix = public/ >> separator = / >> subscriptions = yes >> type = public >> } >> 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_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/report-spam.sieve >> imapsieve_mailbox1_causes = COPY >> imapsieve_mailbox1_name = Spam >> imapsieve_mailbox2_before >> file:/home/vmail/sieve/global/report-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 xxx >> 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 = xxx.xxx.xxx.xxx >> port = 993 >> ssl = yes >> } >> } >> service imap-postlogin { >> executable = script-login /usr/local/etc/dovecot/trackauth.sh >> user = vmail >> } >> service imap { >> executable = imap imap-postlogin >> } >> 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/davemehler.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 >> } > >