[Dovecot] Expire plugin with Mysql
Hey guys, got a question about the expire plugin. According to the wiki, the expire plugin "keeps an internal database (Berkeley DB or SQL)". I was wondering what fields in the mysql table are needed for expire to work. I assume it is setup is similar to the quota dict setup, so was just wondering what fields are required (i.e: select_field, where_field, username_field, for quota).
Thanks
On Sat, 2008-03-29 at 15:57 -0400, Eric wrote:
Hey guys, got a question about the expire plugin. According to the wiki, the expire plugin "keeps an internal database (Berkeley DB or SQL)". I was wondering what fields in the mysql table are needed for expire to work. I assume it is setup is similar to the quota dict setup, so was just wondering what fields are required (i.e: select_field, where_field, username_field, for quota).
A bit late reply, but you should be able to use a table exactly like with quota (http://wiki.dovecot.org/Quota/Dict):
create table expire ( username varchar(255) not null, path varchar(100) not null, current integer, primary key (username, path) );
A better name for "current" would be maybe "timestamp" or something.
I think this dict-sql setup should be changed at some point to allow for more complex configuration..
On Sun, 04 May 2008 17:50:45 +0300 Timo Sirainen tss@iki.fi wrote:
A bit late reply, but you should be able to use a table exactly like with quota (http://wiki.dovecot.org/Quota/Dict):
Just got around to trying this out, and I get a segfault when attempting to move a message into the trash.
Dovecot Version: 1.1rc6 - complied with SunStudio 12 OS: Solaris 10 CPU Arch: x86_64 Filesystem: ZFS Description: Move message into the trash
dovecot -n: # 1.1.rc6: /opt/dovecot/etc/dovecot.conf log_path: /var/log/dovecot.log protocols: imap imaps pop3 pop3s listen(default): *:143 listen(imap): *:143 listen(pop3): *:110 ssl_listen(default): *:993 ssl_listen(imap): *:993 ssl_listen(pop3): *:995 ssl_cert_file: /opt/dovecot/etc/ssl/certs/dovecot.pem ssl_key_file: /opt/dovecot/etc/ssl/private/dovecot.pem ssl_cipher_list: ALL:!LOW:!SSLv2 disable_plaintext_auth: no login_dir: /opt/dovecot/var/run/dovecot/login login_executable(default): /opt/dovecot/libexec/dovecot/imap-login login_executable(imap): /opt/dovecot/libexec/dovecot/imap-login login_executable(pop3): /opt/dovecot/libexec/dovecot/pop3-login login_user: dovecota login_process_per_connection: no login_max_processes_count: 10 max_mail_processes: 25 first_valid_uid: 2000 last_valid_uid: 2000 first_valid_gid: 2000 last_valid_gid: 2000 mail_uid: 2000 mail_gid: 2000 mail_drop_priv_before_exec: yes mail_executable(default): /opt/dovecot/libexec/dovecot/imap mail_executable(imap): /opt/dovecot/libexec/dovecot/imap mail_executable(pop3): /opt/dovecot/libexec/dovecot/pop3 mail_plugins(default): fts fts_squat quota imap_quota expire mail_plugins(imap): fts fts_squat quota imap_quota expire mail_plugins(pop3): expire quota mail_plugin_dir(default): /opt/dovecot/lib/dovecot/imap mail_plugin_dir(imap): /opt/dovecot/lib/dovecot/imap mail_plugin_dir(pop3): /opt/dovecot/lib/dovecot/pop3 auth default: default_realm: flerd.com cache_size: 1024 user: dovecotl verbose: yes debug: yes passdb: driver: ldap args: /opt/dovecot/etc/dovecot-ldap.conf userdb: driver: prefetch userdb: driver: ldap args: /opt/dovecot/etc/dovecot-ldap.conf socket: type: listen client: path: /opt/dovecot/var/run/dovecot/auth-client mode: 438 master: path: /opt/dovecot/var/run/dovecot/auth-master mode: 438 user: dovecota plugin: fts: squat quota: dict:user::proxy::quotadict quota_rule: *:storage=1G quota_rule2: Trash:storage=100M expire: Trash 7 Trash/* 7 Spam 3 expire_dict: proxy::expiredict dict: quotadict: mysql:/opt/dovecot/etc/dovecot-dict-quota.conf expiredict: mysql:/opt/dovecot/etc/dovecot-dict-expire.conf
Coredump: Core was generated by `imap'. Program terminated with signal 11, Segmentation fault. #0 0xfed0578c in strlen () from /lib/libc.so.1 (gdb) bt full #0 0xfed0578c in strlen () from /lib/libc.so.1 No symbol table info available. #1 0xfed5d976 in _ndoprnt () from /lib/libc.so.1 No symbol table info available. #2 0xfed60aab in vsnprintf () from /lib/libc.so.1 No symbol table info available. #3 0x0814a214 in t_noalloc_strdup_vprintf ( format=0x8163a34 "%c%u\t%u\t%d\t%s\t%s\n", args=0x804765c, size_r=0x8047620) at /var/tmp/dovecot-1.1.rc6/src/lib/strfuncs.c:120 args2 = (va_list) 0x804765c tmp = 0x818fb08 "H2\t0\t1\t" init_size = 273 ret = -21546712 #4 0x0814a2e7 in p_strdup_vprintf (pool=0x8178664, format=0x8163a34 "%c%u\t%u\t%d\t%s\t%s\n", args=0x804765c) at /var/tmp/dovecot-1.1.rc6/src/lib/strfuncs.c:139 tmp = 0xfed00460 "Z\211\020�����\213��" buf = 0x8047660 "\002" size = 0 #5 0x0814a66f in t_strdup_printf (format=0x8163a34 "%c%u\t%u\t%d\t%s\t%s\n", ...=0x48) at /var/tmp/dovecot-1.1.rc6/src/lib/strfuncs.c:246 args = (va_list) 0x804765c ret = 0x0 ---Type <return> to continue, or q <return> to quit--- #6 0x0812e512 in client_dict_connect (dict=0x819abc8) at /var/tmp/dovecot-1.1.rc6/src/lib-dict/dict-client.c:268 query = 0x8178664 "D\206\027\b\003" #7 0x0812dfec in client_dict_send_query (dict=0x819abc8, query=0x818fae8 "Lshared/eric@flerd.com/Trash\n") at /var/tmp/dovecot-1.1.rc6/src/lib-dict/dict-client.c:137 No locals. #8 0x0812e7f2 in client_dict_lookup (_dict=0x819abc8, pool=0x818fad8, key=0x818fab8 "shared/eric@flerd.com/Trash", value_r=0x8047710) at /var/tmp/dovecot-1.1.rc6/src/lib-dict/dict-client.c:349 query = 0x818fae8 "Lshared/eric@flerd.com/Trash\n" _data_stack_cur_id = 5 dict = (struct client_dict *) 0x819abc8 line = 0x80476c8 "��\030\b��\030\b\020w\004\b" ret = 135854776 #9 0x0812da62 in dict_lookup (dict=0x819abc8, pool=0x818fad8, key=0x818fab8 "shared/eric@flerd.com/Trash", value_r=0x8047710) at /var/tmp/dovecot-1.1.rc6/src/lib-dict/dict.c:88 No locals. #10 0xfeb62d8e in expire_mailbox_transaction_commit (t=0x81ac2d8, uid_validity_r=0x8047800, first_saved_uid_r=0x80477fc, last_saved_uid_r=0x80477f8) at /var/tmp/dovecot-1.1.rc6/src/plugins/expire/expire-plugin.c:131 ---Type <return> to continue, or q <return> to quit--- key = 0x818fab8 "shared/eric@flerd.com/Trash" value = 0x0 _data_stack_cur_id = 4 xpr_box = (struct expire_mailbox *) 0x81a8a80 xt = (struct expire_transaction_context *) 0x81a7680 mailbox_name = 0x81a8a40 "Trash" new_stamp = 135999896 update_dict = 0 '\0' ret = 134510388 #11 0xfec7e70d in quota_mailbox_transaction_commit (ctx=0x81ac2d8, uid_validity_r=0x8047800, first_saved_uid_r=0x80477fc, last_saved_uid_r=0x80477f8) at /var/tmp/dovecot-1.1.rc6/src/plugins/quota/quota-storage.c:98 qbox = (struct quota_mailbox *) 0x81a8b00 qt = (struct quota_transaction_context *) 0x81ac798 #12 0xfeb467fc in fts_transaction_commit (t=0x81ac2d8, uid_validity_r=0x8047800, first_saved_uid_r=0x80477fc, last_saved_uid_r=0x80477f8) at /var/tmp/dovecot-1.1.rc6/src/plugins/fts/fts-storage.c:657 box = (struct mailbox *) 0x81a88b0 fbox = (struct fts_mailbox *) 0x81a9f20 ft = (struct fts_transaction_context *) 0x8197b58 ret = 135853832
On Sun, 2008-06-01 at 20:26 -0400, Eric wrote:
On Sun, 04 May 2008 17:50:45 +0300 Timo Sirainen tss@iki.fi wrote:
A bit late reply, but you should be able to use a table exactly like with quota (http://wiki.dovecot.org/Quota/Dict):
Just got around to trying this out, and I get a segfault when attempting to move a message into the trash.
Seems to work in my tests. Could you do a bit more with gdb:
#6 0x0812e512 in client_dict_connect (dict=0x819abc8) at /var/tmp/dovecot-1.1.rc6/src/lib-dict/dict-client.c:268 query = 0x8178664 "D\206\027\b\003"
fr 6 p *dict
Seems to work in my tests. Could you do a bit more with gdb:
#6 0x0812e512 in client_dict_connect (dict=0x819abc8) at /var/tmp/dovecot-1.1.rc6/src/lib-dict/dict-client.c:268 query = 0x8178664 "D\206\027\b\003"
fr 6 p *dict
Program terminated with signal 11, Segmentation fault. #0 0xfed0578c in strlen () from /lib/libc.so.1 (gdb) fr 6 #6 0x0812e512 in client_dict_connect (dict=0x819abc8) at /var/tmp/dovecot-1.1.rc6/src/lib-dict/dict-client.c:268 268 dict->value_type, dict->username, dict->uri); Current language: auto; currently minimal (gdb) p *dict $1 = {dict = {name = 0x8163b6c "proxy", v = { init = 0x812e640 <client_dict_init>, deinit = 0x812e770 <client_dict_deinit>, lookup = 0x812e7b0 <client_dict_lookup>, iterate_init = 0x812e8c0 <client_dict_iterate_init>, iterate = 0x812e9c0 <client_dict_iterate>, iterate_deinit = 0x812eb00 <client_dict_iterate_deinit>, transaction_init = 0x812eb70 <client_dict_transaction_init>, transaction_commit = 0x812ebd0 <client_dict_transaction_commit>, transaction_rollback = 0x812ed00 <client_dict_transaction_rollback>, set = 0x812ed90 <client_dict_set>, unset = 0x812ee20 <client_dict_unset>, atomic_inc = 0x812ee90 <client_dict_atomic_inc>}}, pool = 0x819abb0, fd = 13, uri = 0x819ac30 "expiredict", username = 0x0, path = 0x8163a8c "/opt/dovecot/var/run/dovecot/dict-server", value_type = DICT_DATA_TYPE_UINT32, last_connect_try = 1212365823, input = 0x81b3018, output = 0x81b8d9c, connect_counter = 0, transaction_id_counter = 0, in_iteration = 0, handshaked = 0}
On Mon, 2008-06-02 at 14:50 -0400, Eric T wrote:
Seems to work in my tests. Could you do a bit more with gdb:
Wonder why it worked :) Fixed: http://hg.dovecot.org/dovecot-1.1/rev/d73cc550b389
Hi Timo,
I had setup the expire plugin using proxy dict method with mysql table.
Whenever I run the command : dovecot --exec-mail ext /usr/local/libexec/dovecot/expire-tool , I saw dovecot query the table : Query SELECT path, timestamp FROM dict_expire ORDER BY timestamp
But then, nothing happened at all. No mails being expunge from the mailbox Trash folder. Also, What is the purpose for the table to store username, path & timestamp ? Because the table is empty and no data populated.
Spec. FreeBSD 7.0 AMD64 dovecot-1.1.1 from Port
Dovecot Config
[root@dove /usr/local/etc]# dovecot -n # 1.1.1: /usr/local/etc/dovecot.conf base_dir: /var/run/dovecot/ log_path: /var/log/dovecot.log protocols: imap pop3 listen: 192.168.2.20 ssl_disable: yes disable_plaintext_auth: no shutdown_clients: no login_dir: /var/run/dovecot/login login_executable(default): /usr/local/libexec/dovecot/imap-login login_executable(imap): /usr/local/libexec/dovecot/imap-login login_executable(pop3): /usr/local/libexec/dovecot/pop3-login login_greeting: Service Ready. login_greeting_capability(default): yes login_greeting_capability(imap): yes login_greeting_capability(pop3): no login_process_size: 32 login_processes_count: 10 login_max_processes_count: 256 max_mail_processes: 1024 verbose_proctitle: yes first_valid_uid: 26 last_valid_uid: 26 first_valid_gid: 26 last_valid_gid: 26 mail_access_groups: mail mail_location: maildir:~/Maildir mmap_disable: yes dotlock_use_excl: no mail_nfs_storage: yes mail_nfs_index: yes maildir_stat_dirs: yes mail_executable(default): /usr/local/libexec/dovecot/imap mail_executable(imap): /usr/local/libexec/dovecot/imap mail_executable(pop3): /usr/local/libexec/dovecot/pop3 mail_plugins(default): expire fts quota imap_quota mail_plugins(imap): expire fts quota imap_quota mail_plugins(pop3): expire fts quota mail_plugin_dir(default): /usr/local/lib/dovecot/imap mail_plugin_dir(imap): /usr/local/lib/dovecot/imap mail_plugin_dir(pop3): /usr/local/lib/dovecot/pop3 imap_client_workarounds(default): delay-newmail outlook-idle netscape-eoh tb-extra-mailbox-sep imap_client_workarounds(imap): delay-newmail outlook-idle netscape-eoh tb-extra-mailbox-sep imap_client_workarounds(pop3): pop3_client_workarounds(default): pop3_client_workarounds(imap): pop3_client_workarounds(pop3): outlook-no-nuls oe-ns-eoh namespace: type: private separator: . prefix: INBOX. inbox: yes list: yes subscriptions: yes auth default: cache_size: 8192 cache_ttl: 900 verbose: yes worker_max_count: 10 passdb: driver: sql args: /usr/local/etc/dovecot-mysql.conf userdb: driver: sql args: /usr/local/etc/dovecot-mysql.conf socket: type: listen client: path: /var/run/dovecot/auth-client mode: 438 master: path: /var/run/dovecot/auth-master mode: 438 plugin: quota: maildir quota_rule: *:storage=2G quota_rule2: Trash:storage=10M expire: Trash 1 Trash/* 1 expire_dict: proxy::expiredict auth_socket_path: /var/run/dovecot/auth-master fts: squat fts_squat: partial=4 full=10 dict: expiredict: mysql:/usr/local/etc/dovecot-dict-expire.conf
Thx Dino
On Tue, Jun 3, 2008 at 3:21 AM, Timo Sirainen tss@iki.fi wrote:
On Mon, 2008-06-02 at 14:50 -0400, Eric T wrote:
Seems to work in my tests. Could you do a bit more with gdb:
Wonder why it worked :) Fixed: http://hg.dovecot.org/dovecot-1.1/rev/d73cc550b389
Hi Timo,
I had setup the expire plugin using proxy dict method with mysql table.
Whenever I run the command : dovecot --exec-mail ext /usr/local/libexec/dovecot/expire-tool , I saw dovecot query the table : Query SELECT path, timestamp FROM dict_expire ORDER BY timestamp
But then, nothing happened at all. No mails being expunge from the mailbox Trash folder. Also, What is the purpose for the table to store username, path & timestamp ? Because the table is empty and no data populated.
Spec. FreeBSD 7.0 AMD64 dovecot-1.1.1 from Port
Dovecot Config
[root@dove /usr/local/etc]# dovecot -n # 1.1.1: /usr/local/etc/dovecot.conf base_dir: /var/run/dovecot/ log_path: /var/log/dovecot.log protocols: imap pop3 listen: 192.168.2.20 ssl_disable: yes disable_plaintext_auth: no shutdown_clients: no login_dir: /var/run/dovecot/login login_executable(default): /usr/local/libexec/dovecot/imap-login login_executable(imap): /usr/local/libexec/dovecot/imap-login login_executable(pop3): /usr/local/libexec/dovecot/pop3-login login_greeting: Service Ready. login_greeting_capability(default): yes login_greeting_capability(imap): yes login_greeting_capability(pop3): no login_process_size: 32 login_processes_count: 10 login_max_processes_count: 256 max_mail_processes: 1024 verbose_proctitle: yes first_valid_uid: 26 last_valid_uid: 26 first_valid_gid: 26 last_valid_gid: 26 mail_access_groups: mail mail_location: maildir:~/Maildir mmap_disable: yes dotlock_use_excl: no mail_nfs_storage: yes mail_nfs_index: yes maildir_stat_dirs: yes mail_executable(default): /usr/local/libexec/dovecot/imap mail_executable(imap): /usr/local/libexec/dovecot/imap mail_executable(pop3): /usr/local/libexec/dovecot/pop3 mail_plugins(default): expire fts quota imap_quota mail_plugins(imap): expire fts quota imap_quota mail_plugins(pop3): expire fts quota mail_plugin_dir(default): /usr/local/lib/dovecot/imap mail_plugin_dir(imap): /usr/local/lib/dovecot/imap mail_plugin_dir(pop3): /usr/local/lib/dovecot/pop3 imap_client_workarounds(default): delay-newmail outlook-idle netscape-eoh tb-extra-mailbox-sep imap_client_workarounds(imap): delay-newmail outlook-idle netscape-eoh tb-extra-mailbox-sep imap_client_workarounds(pop3): pop3_client_workarounds(default): pop3_client_workarounds(imap): pop3_client_workarounds(pop3): outlook-no-nuls oe-ns-eoh namespace: type: private separator: . prefix: INBOX. inbox: yes list: yes subscriptions: yes auth default: cache_size: 8192 cache_ttl: 900 verbose: yes worker_max_count: 10 passdb: driver: sql args: /usr/local/etc/dovecot-mysql.conf userdb: driver: sql args: /usr/local/etc/dovecot-mysql.conf socket: type: listen client: path: /var/run/dovecot/auth-client mode: 438 master: path: /var/run/dovecot/auth-master mode: 438 plugin: quota: maildir quota_rule: *:storage=2G quota_rule2: Trash:storage=10M expire: Trash 1 Trash/* 1 expire_dict: proxy::expiredict auth_socket_path: /var/run/dovecot/auth-master fts: squat fts_squat: partial=4 full=10 dict: expiredict: mysql:/usr/local/etc/dovecot-dict-expire.conf
Thx Dino
On Tue, Jun 3, 2008 at 3:21 AM, Timo Sirainen tss@iki.fi wrote:
On Mon, 2008-06-02 at 14:50 -0400, Eric T wrote:
Seems to work in my tests. Could you do a bit more with gdb:
Wonder why it worked :) Fixed: http://hg.dovecot.org/dovecot-1.1/rev/d73cc550b389
On Tue, 2008-07-08 at 15:07 +0800, Dino Ming wrote:
Hi Timo,
I had setup the expire plugin using proxy dict method with mysql table.
Whenever I run the command : dovecot --exec-mail ext /usr/local/libexec/dovecot/expire-tool , I saw dovecot query the table : Query SELECT path, timestamp FROM dict_expire ORDER BY timestamp
But then, nothing happened at all. No mails being expunge from the mailbox Trash folder. Also, What is the purpose for the table to store username, path & timestamp ? Because the table is empty and no data populated.
If there are no rows in the table, then that's the reason expire-tool doesn't do anything. IMAP should insert rows into the table when you're copying messages to Trash.
expire: Trash 1 Trash/* 1
I guess by Trash/* you're thinking about mailboxes thare are renamed there by Thunderbird? That won't work currently..
Dear Timo,
I tried to change back to
expire: Trash 1
and it seem still not work whenever I delete email within Mail.app &
Thunderbird.
the dict_expire table not being updated.
Here is my dict.conf file
connect = host=sql dbname=vmail user=xxxxxx password=xxxxxxxxxx table = dict_expire select_field = timestamp where_field = path username_field = username
How can I debug the imap and check why the table didn't got updated
when we delete email ?
Thanks Dino
On 21 Jul 2008, at 12:11 AM, Timo Sirainen wrote:
On Tue, 2008-07-08 at 15:07 +0800, Dino Ming wrote:
Hi Timo,
I had setup the expire plugin using proxy dict method with mysql
table.Whenever I run the command : dovecot --exec-mail ext /usr/local/libexec/dovecot/expire-tool , I saw dovecot query the
table : Query SELECT path, timestamp FROM dict_expire ORDER BY
timestampBut then, nothing happened at all. No mails being expunge from the
mailbox Trash folder. Also, What is the purpose for the table to store
username, path & timestamp ? Because the table is empty and no data populated.If there are no rows in the table, then that's the reason expire-tool doesn't do anything. IMAP should insert rows into the table when
you're copying messages to Trash.expire: Trash 1 Trash/* 1
I guess by Trash/* you're thinking about mailboxes thare are renamed there by Thunderbird? That won't work currently..
Dino Ming wrote:
Here is my dict.conf file
connect = host=sql dbname=vmail user=xxxxxx password=xxxxxxxxxx table = dict_expire select_field = timestamp where_field = path username_field = username
That looks good
How can I debug the imap and check why the table didn't got updated when we delete email ?
When I was having issues previous with mysql quota I turned on mysql query logging to see what exactly was being done on the database side, and discovered what the errors were. You may want to give it a shot:
participants (5)
-
Dino Ming
-
Eric
-
Eric T
-
Eric Toczek
-
Timo Sirainen