I've been mucking about, experimenting with the expire plugin and using a
dictionary. I've got the iteration query working when I do a normal
expunge using:
doveadm expunge -A mailbox "INBOX.Trash" savedbefore 1w
and expunging works as expected. However, I've got over 12,000 accounts
on this server, so I was hoping using the expire plugin to could help out.
I've configured the plugin, and things kinda work, except that somewhere
between 3700 and 3800 users, I abort with this:
doveadm(someuseraccount at somedomain): Error: dict client
(/var/run/dovecot/dict) sent broken reply
doveadm(someuseraccount at somedomain): Error: Dictionary iteration failed
doveadm: Error: Failed to iterate through some users
It consistently fails at the same user. If I delete that user from the
expire database, then it appears to fail on the next user.
I also see this in the logs:
dovecot: dict: Error: dict client: COMMIT: Can't commit while iterating
I've absolutely no idea where to go from here to troubleshoot this. Any
guidance would be appreciated.
Thanks,
Chris
doveconf -n:
# 2.1.12: /etc/dovecot/dovecot.conf
# OS: Linux 3.7.5-hardened-r1 x86_64 Gentoo Base System release 2.1 ext4
auth_master_user_separator = *
auth_mechanisms = plain login
auth_username_chars
abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ01234567890.-_@&
auth_verbose_passwords = plain
default_process_limit = 200
dict {
quota = mysql:/etc/dovecot/dovecot-dict-sql.conf.ext
}
disable_plaintext_auth = no
login_greeting = Awaiting command...
mail_location = maildir:/home/vmail/%d/%n/Maildir
mail_plugins = " quota"
mail_privileged_group = 100
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 ihave
namespace inbox {
inbox = yes
location mailbox Drafts {
special_use = \Drafts
}
mailbox Junk {
special_use = \Junk
}
mailbox Sent {
special_use = \Sent
}
mailbox "Sent Messages" {
special_use = \Sent
}
mailbox Trash {
special_use = \Trash
}
prefix = INBOX.
separator = .
type = private
}
passdb {
args = /etc/dovecot/dovecot-sql.conf.ext
driver = sql
}
plugin {
expire = Trash 7
expire2 = Trash/* 7
expire3 = Spam 7
quota = dict:User quota:%u:proxy::quota
quota_rule = *:storage=200M
quota_warning = storage=99%% quota-warning 99 %n %d
quota_warning2 = storage=95%% quota-warning 95 %n %d
quota_warning3 = storage=80%% quota-warning 80 %n %d
quota_warning4 = -storage=95%% quota-warning 'less than 95' %n %d
sieve = ~/.dovecot.sieve
sieve_default = /home/vmail/dovecot/sieve/default.sieve
sieve_dir = ~/sieve
sieve_global_dir = /home/vmail/dovecot/sieve
}
protocols = imap pop3 sieve lmtp
service auth-worker {
user = $default_internal_user
}
service auth {
unix_listener /var/spool/postfix/private/auth {
group = postfix
mode = 0666
user = postfix
}
unix_listener auth-userdb {
group = dovecot
mode = 0666
user = dovecot
}
user = $default_internal_user
}
service dict {
unix_listener dict {
mode = 0600
user = vmail
}
}
service lmtp {
unix_listener /var/spool/postfix/private/dovecot-lmtp {
group = postfix
mode = 0600
user = postfix
}
}
service quota-warning {
executable = script /etc/dovecot/quota-warning.sh
unix_listener quota-warning {
user = vmail
}
user = dovecot
}
ssl_cert = </etc/ssl/dovecot/server.pem
ssl_key = </etc/ssl/dovecot/server.key
userdb {
driver = prefetch
}
userdb {
args = /etc/dovecot/dovecot-sql.conf.ext
driver = sql
}
protocol lmtp {
mail_plugins = sieve quota
postmaster_address = postmaster at domain
}
protocol lda {
mail_plugins = " quota sieve quota"
}
protocol imap {
mail_max_userip_connections = 20
mail_plugins = " quota quota imap_quota"
}
protocol pop3 {
mail_plugins = " quota quota"
}
>From dovecot-dict-sql.conf.ext:
connect = host=localhost dbname=maildb user=dbuser password=dbpass
# CREATE TABLE quota (
# username varchar(100) not null,
# bytes bigint not null default 0,
# messages integer not null default 0,
# primary key (username)
# );
map {
pattern = priv/quota/storage
table = quota_usage
username_field = address
value_field = quota_bytes
}
map {
pattern = priv/quota/messages
table = quota_usage
username_field = address
value_field = quota_messages
}
# CREATE TABLE expires (
# username varchar(100) not null,
# mailbox varchar(255) not null,
# expire_stamp integer not null,
# primary key (username, mailbox)
# );
map {
pattern = shared/expire/$user/$mailbox
table = expires
value_field = expire_stamp
fields {
address = $user
folder = $mailbox
}
}
dovecot-sql.conf.ext
driver = mysql
connect = host=/var/run/mysqld/mysqld.sock dbname=maildb user=dbuser
password=dbpass
default_pass_scheme = PLAIN
user_query = SELECT homedir AS home, maildir AS mail, uid AS uid, gid AS
gid, quota_rule AS quota_rule FROM email WHERE address = CONVERT('%u'
USING latin1) AND is_alias=0;
password_query = SELECT address AS user, NULL as password, homedir AS
userdb_home, maildir as userdb_mail, uid AS userdb_uid, gid AS userdb_gid,
quota_rule AS userdb_quota_rule, 'Y' AS nopassword FROM email WHERE
address = CASE WHEN ('%d' = '') THEN CONCAT
(CONVERT('%n' USING latin1),
'@bordernet.com.au') ELSE CONVERT('%u' USING latin1) END AND
is_alias=0
AND CheckPasswordFunc(CONVERT('%n' USING latin1), '%d',
CONVERT('%w' USING
latin1), '%r');
iterate_query = SELECT address AS user FROM email WHERE is_alias=0 AND
length(password) > 1
Anyone have any thoughts on this? Chris On Sun, May 19, 2013 4:33 pm, Chris Richards wrote:> I've been mucking about, experimenting with the expire plugin and using a > dictionary. I've got the iteration query working when I do a normal > expunge using: > > doveadm expunge -A mailbox "INBOX.Trash" savedbefore 1w > > and expunging works as expected. However, I've got over 12,000 accounts > on this server, so I was hoping using the expire plugin to could help out. > I've configured the plugin, and things kinda work, except that somewhere > between 3700 and 3800 users, I abort with this: > > doveadm(someuseraccount at somedomain): Error: dict client > (/var/run/dovecot/dict) sent broken reply > doveadm(someuseraccount at somedomain): Error: Dictionary iteration failed > doveadm: Error: Failed to iterate through some users > > It consistently fails at the same user. If I delete that user from the > expire database, then it appears to fail on the next user. > > I also see this in the logs: > > dovecot: dict: Error: dict client: COMMIT: Can't commit while iterating > > I've absolutely no idea where to go from here to troubleshoot this. Any > guidance would be appreciated. > > Thanks, > Chris > > doveconf -n: > > # 2.1.12: /etc/dovecot/dovecot.conf > # OS: Linux 3.7.5-hardened-r1 x86_64 Gentoo Base System release 2.1 ext4 > auth_master_user_separator = * > auth_mechanisms = plain login > auth_username_chars > abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ01234567890.-_@& > auth_verbose_passwords = plain > default_process_limit = 200 > dict { > quota = mysql:/etc/dovecot/dovecot-dict-sql.conf.ext > } > disable_plaintext_auth = no > login_greeting = Awaiting command... > mail_location = maildir:/home/vmail/%d/%n/Maildir > mail_plugins = " quota" > mail_privileged_group = 100 > 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 ihave > namespace inbox { > inbox = yes > location > mailbox Drafts { > special_use = \Drafts > } > mailbox Junk { > special_use = \Junk > } > mailbox Sent { > special_use = \Sent > } > mailbox "Sent Messages" { > special_use = \Sent > } > mailbox Trash { > special_use = \Trash > } > prefix = INBOX. > separator = . > type = private > } > passdb { > args = /etc/dovecot/dovecot-sql.conf.ext > driver = sql > } > plugin { > expire = Trash 7 > expire2 = Trash/* 7 > expire3 = Spam 7 > quota = dict:User quota:%u:proxy::quota > quota_rule = *:storage=200M > quota_warning = storage=99%% quota-warning 99 %n %d > quota_warning2 = storage=95%% quota-warning 95 %n %d > quota_warning3 = storage=80%% quota-warning 80 %n %d > quota_warning4 = -storage=95%% quota-warning 'less than 95' %n %d > sieve = ~/.dovecot.sieve > sieve_default = /home/vmail/dovecot/sieve/default.sieve > sieve_dir = ~/sieve > sieve_global_dir = /home/vmail/dovecot/sieve > } > protocols = imap pop3 sieve lmtp > service auth-worker { > user = $default_internal_user > } > service auth { > unix_listener /var/spool/postfix/private/auth { > group = postfix > mode = 0666 > user = postfix > } > unix_listener auth-userdb { > group = dovecot > mode = 0666 > user = dovecot > } > user = $default_internal_user > } > service dict { > unix_listener dict { > mode = 0600 > user = vmail > } > } > service lmtp { > unix_listener /var/spool/postfix/private/dovecot-lmtp { > group = postfix > mode = 0600 > user = postfix > } > } > service quota-warning { > executable = script /etc/dovecot/quota-warning.sh > unix_listener quota-warning { > user = vmail > } > user = dovecot > } > ssl_cert = </etc/ssl/dovecot/server.pem > ssl_key = </etc/ssl/dovecot/server.key > userdb { > driver = prefetch > } > userdb { > args = /etc/dovecot/dovecot-sql.conf.ext > driver = sql > } > protocol lmtp { > mail_plugins = sieve quota > postmaster_address = postmaster at domain > } > protocol lda { > mail_plugins = " quota sieve quota" > } > protocol imap { > mail_max_userip_connections = 20 > mail_plugins = " quota quota imap_quota" > } > protocol pop3 { > mail_plugins = " quota quota" > } > > > From dovecot-dict-sql.conf.ext: > > connect = host=localhost dbname=maildb user=dbuser password=dbpass > > # CREATE TABLE quota ( > # username varchar(100) not null, > # bytes bigint not null default 0, > # messages integer not null default 0, > # primary key (username) > # ); > > map { > pattern = priv/quota/storage > table = quota_usage > username_field = address > value_field = quota_bytes > } > map { > pattern = priv/quota/messages > table = quota_usage > username_field = address > value_field = quota_messages > } > > # CREATE TABLE expires ( > # username varchar(100) not null, > # mailbox varchar(255) not null, > # expire_stamp integer not null, > # primary key (username, mailbox) > # ); > > map { > pattern = shared/expire/$user/$mailbox > table = expires > value_field = expire_stamp > > fields { > address = $user > folder = $mailbox > } > } > > > > dovecot-sql.conf.ext > > driver = mysql > connect = host=/var/run/mysqld/mysqld.sock dbname=maildb user=dbuser > > password=dbpass > default_pass_scheme = PLAIN > > user_query = SELECT homedir AS home, maildir AS mail, uid AS uid, gid AS > gid, quota_rule AS quota_rule FROM email WHERE address = CONVERT('%u' > USING latin1) AND is_alias=0; > > password_query = SELECT address AS user, NULL as password, homedir AS > userdb_home, maildir as userdb_mail, uid AS userdb_uid, gid AS userdb_gid, > quota_rule AS userdb_quota_rule, 'Y' AS nopassword FROM email WHERE > address = CASE WHEN ('%d' = '') THEN CONCAT (CONVERT('%n' USING latin1), > '@bordernet.com.au') ELSE CONVERT('%u' USING latin1) END AND is_alias=0 > AND CheckPasswordFunc(CONVERT('%n' USING latin1), '%d', CONVERT('%w' USING > latin1), '%r'); > > iterate_query = SELECT address AS user FROM email WHERE is_alias=0 AND > length(password) > 1 > >
Having the same problem here. Dovecot 2.1.7. But all I get from doveadm -D is: doveadm(user1 at domain.tld): Debug: expire: Stopping iteration on key shared/expire/anotheruser at domain.tld/Trash (1373817132 > 1373816705) and that's it. The log says: dovecot: dict: Error: dict client: COMMIT: Can't commit while iterating
On 20.5.2013, at 0.33, Chris Richards <gizmo at giz-works.com> wrote:> doveadm(someuseraccount at somedomain): Error: dict client > (/var/run/dovecot/dict) sent broken reply > doveadm(someuseraccount at somedomain): Error: Dictionary iteration failed > doveadm: Error: Failed to iterate through some users..> dovecot: dict: Error: dict client: COMMIT: Can't commit while iterating..> # 2.1.12: /etc/dovecot/dovecot.confThere are a few fixes in lib-dict since v2.1.12. Also v2.2 has one more fix, which I just added to v2.1 hg. Would be helpful to know if one of those fixes the problem before I spend a lot of time testing this..