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=<u...@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(u...@domain.com)<75934><db0EjH9pOix/AAAB>: > Error: last_login_dict: Failed to write value for user > u...@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(u...@domain.com)<44193><4GzoqFdpisJ/AAAB:StoZDD4xylqhrAAA0J78UA>: > Info: Indexed 0 messages in INBOX > 2018-04-08 11:11:58 > indexer-worker(u...@domain.com)<44193><4GzoqFdpisJ/AAAB:0AaXFD4xylqhrAAA0J78UA>: > Info: Indexed 0 messages in INBOX > 2018-04-08 11:11:58 > indexer-worker(u...@domain.com)<44193><4GzoqFdpisJ/AAAB:CP3TIT4xylqhrAAA0J78UA>: > Info: Indexed 0 messages in INBOX > 2018-04-08 11:11:58 > indexer-worker(u...@domain.com)<44193><4GzoqFdpisJ/AAAB:eNEWMT4xylqhrAAA0J78UA>: > Info: Indexed 0 messages in INBOX > 2018-04-08 11:15:28 imap(u...@domain.com)<21374><4GzoqFdpisJ/AAAB>: > Fatal: block_alloc(8388608): Out of memory > 2018-04-08 11:15:28 imap(u...@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:postmas...@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@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 > }