[Dovecot] Expire plugin - empty expires table
Hello,
Dovecot does not fill expires table when moving messages to Trash or Junk folder.
I looked all over mailing list and googled around and cannot find what could be wrong - pleas help :)
Dict works for quota, so I presume connection to mysql is not a problem - mailuser has Select,Insert,Update,Delete privileges over
quota and expires table (log below for deleting message)
dovecot log does not show any problems, not even with mail_debug.
doveadm -A works
dovecot -n
# 2.2.6: /etc/dovecot/dovecot.conf
# OS: Linux 3.10.17-gentoo x86_64 Gentoo Base System release 2.2 xfs
auth_mechanisms = plain login
dict {
expire = mysql:/etc/dovecot/dovecot-dict-sql.conf.ext
quota = mysql:/etc/dovecot/dovecot-dict-sql.conf.ext
}
hostname = ****@****.****
listen = *************
login_greeting = ***** ready.
mail_access_groups = vmail
mail_attachment_dir = /data/vmail/attachments
mail_attachment_fs = sis-queue /data/vmail/attachments/queue:posix
mail_attachment_hash = %{sha256}
mail_location = mdbox:/data/vmail/storage/%d/%n/:ALT=/data/vmail/altstorage/%d/%n/:INDEX=/da ta/vmail/indexes/%d/%n/
mail_plugins = " expire quota zlib mail_log notify"
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
mdbox_preallocate_space = yes
mdbox_rotate_interval = 1 days
namespace Unwanted {
hidden = no
inbox = no
list = yes
location = maildir:/data/vmail/unwanted/%d/%n/
mailbox Ham {
auto = subscribe}
mailbox Spam {
auto = subscribe}
prefix = Unwanted.
separator = .
subscriptions = yes
type = private
}
namespace inbox {
hidden = no
inbox = yes
list = yes
location = mdbox:/data/vmail/storage/%d/%n/:ALT=/data/vmail/altstorage/%d/%n/:INDEX=/da ta/vmail/indexes/%d/%n/
mailbox Archives {
auto = subscribe
special_use = \Archive}
mailbox Drafts {
auto = subscribe
special_use = \Drafts}
mailbox Folders {
auto = subscribe}
mailbox Junk {
auto = subscribe
special_use = \Junk}
mailbox Sent {
auto = subscribe
special_use = \Sent}
mailbox Trash {
auto = subscribe
special_use = \Trash}
prefix =
separator = .
subscriptions = yes
type = private
}
passdb {
args = /etc/dovecot/dovecot-sql.conf.ext
driver = sql
}
plugin {
expire = Trash 1 Trash.* 1 Junk 2
expire_dict = proxy::expire
mail_log_events = delete undelete expunge copy mailbox_delete mailbox_rename
mail_log_fields = uid box msgid from subject, size, vsize, flags
quota = dict:User quota::proxy::quota
quota_exceeded_message = Quota exceeded (mailbox is full)
quota_grace = 10%%
quota_rule = *:storage=1G
quota_rule2 = Trash:storage=+100M
quota_rule3 = Junk:ignore
quota_status_nouser = DUNNO
quota_status_overquota = 552 5.2.2 Mailbox is full
quota_status_success = DUNNO
quota_warning = storage=95%% quota-warning 95 %u
quota_warning2 = storage=85%% quota-warning 85 %u
quota_warning3 = storage=70%% quota-warning 70 %u
sieve = /data/vmail/sieve/%d/%n/.dovecot.sieve
sieve_before = /data/vmail/sieve/before.sieve
sieve_default = /data/vmail/sieve/default.sieve
sieve_dir = /data/vmail/sieve/%d/%n/sieve
sieve_global_dir = /data/vmail/sieve/
zlib_save = bz2
zlib_save_level = 9
}
postmaster_address = postmaster@fingerprints.si
protocols = imap sieve
service auth {
unix_listener /var/spool/postfix/private/auth {
group = postfix
mode = 0666
user = postfix}
}
service dict {
unix_listener dict {
group = vmail
mode = 0660}
}
service managesieve-login {
inet_listener sieve {
port = 4190}
}
service quota-status {
client_limit = 1
executable = quota-status -p postfix
inet_listener {
port = 12340}
}
service quota-warning {
executable = script /usr/bin/dovecot-quota-warning.sh
unix_listener quota-warning {
mode = 0666
user = vmail}
user = vmail
}
ssl_cert =
ssl_key =
userdb {
args = /etc/dovecot/dovecot-sql.conf.ext
driver = sql
}
protocol lda {
mail_plugins = expire sieve zlib
}
protocol imap {
mail_plugins = " expire quota zlib mail_log notify expire imap_quota quota"
}
protocol sieve {
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
}
dovecot-dict-sql.conf.ext
connect = host=127.0.0.1 dbname=mailserver user=mailuser password=*************************
map {
pattern = priv/quota/storage
table = quota
username_field = username
value_field = bytes
}
map {
pattern = priv/quota/messages
table = quota
username_field = username
value_field = messages
}
map {
pattern = shared/expire/$user/$mailbox
table = expires
value_field = expire_stamp
fields {
username = $user
mailbox = $mailbox}
}
dovecot-sql.conf.ext
driver = mysql
connect = host=127.0.0.1 dbname=mailserver user=mailuser password=*************************
default_pass_scheme = PLAIN-MD5
iterate_query = SELECT email AS user FROM virtual_users
password_query = SELECT email AS user, password, \
uid AS userdb_uid, gid AS userdb_gid, \
concat('*:bytes=', quota_bytes) AS userdb_quota_rule \
FROM virtual_users WHERE email = '%u'
user_query = SELECT uid, gid, \
CONCAT('/data/vmail/storage/',CONCAT(SUBSTRING_INDEX(email,'@',-1),'/',SUBST RING_INDEX(email,'@',1))) AS home, \
CONCAT('*:bytes=', quota_bytes) AS quota_rule \
FROM virtual_users WHERE email = '%u'
MYSQL:
+-----------+------------+-------------+------------+----------------+------ ---------------+------------------------------------------------------------ ----------------------------------+-------------+
| Host | Db | User | Table_name | Grantor | Timestamp | Table_priv | Column_priv |
+-----------+------------+-------------+------------+----------------+------ ---------------+------------------------------------------------------------ ----------------------------------+-------------+
| 127.0.0.1 | mailserver | mailuser | quota | root@localhost | 2013-12-06 02:14:57 | Select,Insert,Update,Delete | |
| 127.0.0.1 | mailserver | mailuser | expires | root@localhost | 2013-12-08 05:49:42 | Select,Insert,Update,Delete | |
+-----------+------------+-------------+------------+----------------+------ ---------------+------------------------------------------------------------ ----------------------------------+-------------+
mysql> describe expires;
+--------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------+--------------+------+-----+---------+-------+
| username | varchar(75) | NO | PRI | NULL | |
| mailbox | varchar(255) | NO | PRI | NULL | |
| expire_stamp | int(11) | NO | | NULL | |
+--------------+--------------+------+-----+---------+-------+
mysql> select * from expires;
Empty set (0.00 sec)
MYSQL LOG:
1 Query SELECT email AS user,password, uid AS userdb_uid, gid AS userdb_gid, concat('*:bytes=', quota_bytes) AS userdb_quota_rule FROM virtual_users WHERE email = '*********************'
2 Connect mailuser@*************on mailserver
2 Query BEGIN
1 Query SELECT uid, gid,CONCAT('/data/vmail/storage/',CONCAT(SUBSTRING_INDEX(email,'@',-1),'/',SUBST RING_INDEX(email,'@',1))) AS home, CONCAT('*:bytes=', quota_bytes) AS quota_rule FROM virtual_users WHERE email = '*********************'
2 Query UPDATE quota SETbytes=bytes-15989,messages=messages-1 WHERE username = '*********************'
2 Query COMMIT
2 Query SELECT bytes FROMquota WHERE username = '*********************'
2 Query SELECT messages FROMquota WHERE username = '*********************'
2 Query SELECT bytes FROMquota WHERE username = '*********************'
2 Query BEGIN
2 Query UPDATE quota SETbytes=bytes+16025,messages=messages+1 WHERE username = '*********************'
2 Query COMMIT
2 Query SELECT bytes FROMquota WHERE username = '*********************'
2 Query SELECT messages FROMquota WHERE username = '*********************'
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1
On Sun, 8 Dec 2013, Peter Šori wrote:
Dovecot does not fill expires table when moving messages to Trash or Junk folder.
I don't use expire, but: http://wiki2.dovecot.org/Plugins/Expire
disagrees with
# 2.2.6: /etc/dovecot/dovecot.conf [...] plugin { expire = Trash 1 Trash.* 1 Junk 2
looks like this is Dovecot v1 syntax.
Steffen Kaiser -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.11 (GNU/Linux)
iQEVAwUBUqV6ZF3r2wJMiz2NAQJNPwgAmNBMiNmeyTCn5BjB44dLqq3M3vJLHkji YTSKhYTd5twiskFI06CeDql5l+uBcGc5vfH5SKTIg/2zjvUJwpR/vIfBmM3U+ZNq YphEfT/V6+oVWUeEERBjw7v7eT3IgG8do7cZ4/EHGNScRB8bBTHTUzNbpPWVSo+U RlER9OqspBD7hLJCc1P8jZ7vCNV0LT8/T1hdx1HGRFJnrvH8FsXiwKTekxs2868B HYNy6iF+zLtZJDoOUUVvZ3w8CkU96T5njZC8YxosOBiAanykadoLCdnwOq+h91VG qgpaFRz6m4oABgIuENHntNjGL9PhPB5eebR4YDdMStpKBsi0UDHG8Q== =VDz7 -----END PGP SIGNATURE-----
Embarrassment at its best :)
Working conf: plugin { expire = Trash expire2 = Trash/* expire3 = Spam }
First testing was done with 2.x syntax and it seems I had something else wrong then -- later on I just assumed the wiki syntax must be wrong, as I struggled for whole day with combinations. Thanks for help Steffen.
-----Original Message----- From: Steffen Kaiser [mailto:skdovecot@smail.inf.fh-brs.de] Sent: Monday, December 9, 2013 9:08 AM To: Peter Šori Cc: dovecot@dovecot.org Subject: Re: [Dovecot] Expire plugin - empty expires table
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1
On Sun, 8 Dec 2013, Peter Šori wrote:
Dovecot does not fill expires table when moving messages to Trash or Junk folder.
I don't use expire, but: http://wiki2.dovecot.org/Plugins/Expire
disagrees with
# 2.2.6: /etc/dovecot/dovecot.conf [...] plugin { expire = Trash 1 Trash.* 1 Junk 2
looks like this is Dovecot v1 syntax.
Steffen Kaiser -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.11 (GNU/Linux)
iQEVAwUBUqV6ZF3r2wJMiz2NAQJNPwgAmNBMiNmeyTCn5BjB44dLqq3M3vJLHkji YTSKhYTd5twiskFI06CeDql5l+uBcGc5vfH5SKTIg/2zjvUJwpR/vIfBmM3U+ZNq YphEfT/V6+oVWUeEERBjw7v7eT3IgG8do7cZ4/EHGNScRB8bBTHTUzNbpPWVSo+U RlER9OqspBD7hLJCc1P8jZ7vCNV0LT8/T1hdx1HGRFJnrvH8FsXiwKTekxs2868B HYNy6iF+zLtZJDoOUUVvZ3w8CkU96T5njZC8YxosOBiAanykadoLCdnwOq+h91VG qgpaFRz6m4oABgIuENHntNjGL9PhPB5eebR4YDdMStpKBsi0UDHG8Q== =VDz7 -----END PGP SIGNATURE-----
participants (2)
-
Peter Šori
-
Steffen Kaiser