[Dovecot] Expire plugin - empty expires table
Peter Šori
peter.sori at fingerprints.si
Sun Dec 8 13:20:07 EET 2013
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 at 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 = </etc/ssl/certs/san_*****.****-2048.crt
ssl_key = </etc/ssl/private/san_*****.****-2048.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 at localhost |
2013-12-06 02:14:57 | Select,Insert,Update,Delete
| |
| 127.0.0.1 | mailserver | mailuser | expires | root at 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 SET
bytes=bytes-15989,messages=messages-1 WHERE username =
'*********************'
2 Query COMMIT
2 Query SELECT bytes FROM
quota WHERE username = '*********************'
2 Query SELECT messages FROM
quota WHERE username = '*********************'
2 Query SELECT bytes FROM
quota WHERE username = '*********************'
2 Query BEGIN
2 Query UPDATE quota SET
bytes=bytes+16025,messages=messages+1 WHERE username =
'*********************'
2 Query COMMIT
2 Query SELECT bytes FROM
quota WHERE username = '*********************'
2 Query SELECT messages FROM
quota WHERE username = '*********************'
-------------- next part --------------
A non-text attachment was scrubbed...
Name: smime.p7s
Type: application/pkcs7-signature
Size: 3319 bytes
Desc: not available
URL: <http://dovecot.org/pipermail/dovecot/attachments/20131208/e7129ab0/attachment-0001.bin>
More information about the dovecot
mailing list