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 >> } > >
Thomas Zajic
2018-Apr-11 15:40 UTC
two unrelated issues, lastlogin, and an out of memory fatal error
* David Mehler, 2018-04-11 17:23> [...] > 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? > [...]This is in fact a timestamp: [zlatko at disclosure:~]$ date -d @1523459718 Mit Apr 11 17:15:18 CEST 2018 Your output might look different depending on your locale. HTH, Thomas
Reio Remma
2018-Apr-11 15:41 UTC
two unrelated issues, lastlogin, and an out of memory fatal error
On 11.04.18 18:23, David Mehler wrote:> 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?SELECT FROM_UNIXTIME(1523459718) Maybe that will help? Good luck, Reio
David Mehler
2018-Apr-12 23:42 UTC
two unrelated issues, lastlogin, and an out of memory fatal error
Hello Aki and everyone, "Increase memory, your cache takes more memory than you have anticipated." What setting do I need to update in dovecot? I hope your not talking about the vps it has 4GB of ram. Thanks. Dave. On 4/12/18, Reio Remma <reio at mrstuudio.ee> wrote:> Hey, > > maybe it's like Aki said, you need more memory. :) Other than that I > can't think of anything. :/ > > Good luck, > Reio > > On 12.04.18 2:42, David Mehler wrote: >> Hello, >> >> Thanks a lot. That did it. >> >> Do you have any idea as to my other issue, the fatal out of memory one? >> >> Thanks. >> Dave. >> >> >> On 4/11/18, Reio Remma <reio at mrstuudio.ee> wrote: >>> Hello, try: >>> >>> SELECT userid, FROM_UNIXTIME(last_login) FROM users; >>> >>> Reio >>> >>> On 11.04.2018 21:39, David Mehler wrote: >>>> Hi, >>>> >>>> Can I get the database display to display those timestamps in a >>>> particular format? >>>> >>>> Thanks. >>>> Dave. >>>> >>>> >>>> On 4/11/18, Reio Remma <reio at mrstuudio.ee> wrote: >>>>> On 11.04.18 18:23, David Mehler wrote: >>>>>> 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? >>>>> SELECT FROM_UNIXTIME(1523459718) >>>>> >>>>> Maybe that will help? >>>>> >>>>> Good luck, >>>>> Reio >>>>> >>> > > > -- > Tervitades > Reio Remma > > > MR Stuudio 25 aastat > > *MR Stuudio O?* > Tondi 17b, 11316, Tallinn > Tel +372 650 4808 > Mob +372 56 22 00 33 > reio at mrstuudio.ee > www.mrstuudio.ee > > > >