[Dovecot] expire-plugin: configuration dict-server
Hello all!
I tried to add the expire-plugin to my dovecot-installation (CentOS-RPM: dovecot-1.1.1-2_76.el5) and followed the plugin-page from the WIKI (http://wiki.dovecot.org/Plugins/Expire).
Unfortunately I have problems with the dictionary: --> dovecot: Aug 05 13:30:25 Error: dict: Unknown dict module: db dovecot: Aug 05 13:30:25 Error: dict: Failed to initialize dictionary 'expire' <--
As far as I understand the hints from mailinglist-archive the db-support is not standard with dovecot and Timo suggested to use a mysql-connection.
Are there any further tipps how to use the expire-plugin with mysql-connection (i.e. database-scheme, SQL-Select)?
Kind regards,
JensJens Meyer wrote:
Unfortunately I have problems with the dictionary: --> dovecot: Aug 05 13:30:25 Error: dict: Unknown dict module: db dovecot: Aug 05 13:30:25 Error: dict: Failed to initialize dictionary 'expire'
Looking at the rpm it doesn't look like it has bdb support built in. But it sounds like you want to use Mysql so that's alright.
Are there any further tipps how to use the expire-plugin with mysql-connection (i.e. database-scheme, SQL-Select)?
You will need to setup your configuration like this:
dovecot.conf:
dict { ... expiredict = mysql:/opt/dovecot/etc/dovecot-dict-expire.conf ... }
plugin { ... expire = Trash 7 Trash/* 7 Spam 3 Junk 3 expire_dict = proxy::expiredict ... }
dovecot-dict-expire.conf: connect = host=<host> dbname=<db> user=<username> password=<password> table = expire select_field = timestamp where_field = path username_field = username
Then you'll want to create the table like so: create table expire( username varchar(255) not null, path varchar(100) not null, timestamp integer, primary key (username, path))engine=innodb;
That should work for you. Of course enter in your correct config location and your specific expire settings.
-Eric
Hello Eric,
thank you very much for your prompt and helpful reply!
The connect seem to work fine now.
Please allow me two additional questions: Is it correct that this database-table is only a "caching-table" which is empty at first and will be filled later? It is not necessary to adapt the SQL-statement to my user-configuration, is it?!
Is it necessary to reference the foldernames with "INBOX.Trash" or only "Trash"? For Sieve I have to use "INBOX.Trash".
Actually nothing happens when trying the plugin with "dovecot --exec-mail ext /usr/libexec/dovecot/expire-tool --test".
Thank you again, kind regards!
JensEric Toczek schrieb:
Jens Meyer wrote:
Unfortunately I have problems with the dictionary: --> dovecot: Aug 05 13:30:25 Error: dict: Unknown dict module: db dovecot: Aug 05 13:30:25 Error: dict: Failed to initialize dictionary 'expire'
Looking at the rpm it doesn't look like it has bdb support built in. But it sounds like you want to use Mysql so that's alright.
Are there any further tipps how to use the expire-plugin with mysql-connection (i.e. database-scheme, SQL-Select)? You will need to setup your configuration like this:
dovecot.conf:
dict { ... expiredict = mysql:/opt/dovecot/etc/dovecot-dict-expire.conf ... }
plugin { ... expire = Trash 7 Trash/* 7 Spam 3 Junk 3 expire_dict = proxy::expiredict ... }
dovecot-dict-expire.conf: connect = host=<host> dbname=<db> user=<username> password=<password> table = expire select_field = timestamp where_field = path username_field = username
Then you'll want to create the table like so: create table expire( username varchar(255) not null, path varchar(100) not null, timestamp integer, primary key (username, path))engine=innodb;
That should work for you. Of course enter in your correct config location and your specific expire settings.
-Eric
Hi Jens,
Ive got the same problem with you. The expire plugin not work. Nothing happened even I delete email from a mail client. And the expire table on mysql never updated.
Thanks, Dino
Jens Meyer wrote:
Hello Eric,
thank you very much for your prompt and helpful reply!
The connect seem to work fine now.
Please allow me two additional questions: Is it correct that this database-table is only a "caching-table" which is empty at first and will be filled later? It is not necessary to adapt the SQL-statement to my user-configuration, is it?!
Is it necessary to reference the foldernames with "INBOX.Trash" or only "Trash"? For Sieve I have to use "INBOX.Trash".
Actually nothing happens when trying the plugin with "dovecot --exec-mail ext /usr/libexec/dovecot/expire-tool --test".
Thank you again, kind regards!
JensEric Toczek schrieb:
Jens Meyer wrote:
Unfortunately I have problems with the dictionary: --> dovecot: Aug 05 13:30:25 Error: dict: Unknown dict module: db dovecot: Aug 05 13:30:25 Error: dict: Failed to initialize dictionary 'expire'
Looking at the rpm it doesn't look like it has bdb support built in. But it sounds like you want to use Mysql so that's alright.
Are there any further tipps how to use the expire-plugin with mysql-connection (i.e. database-scheme, SQL-Select)? You will need to setup your configuration like this:
dovecot.conf:
dict { ... expiredict = mysql:/opt/dovecot/etc/dovecot-dict-expire.conf ... }
plugin { ... expire = Trash 7 Trash/* 7 Spam 3 Junk 3 expire_dict = proxy::expiredict ... }
dovecot-dict-expire.conf: connect = host=<host> dbname=<db> user=<username> password=<password> table = expire select_field = timestamp where_field = path username_field = username
Then you'll want to create the table like so: create table expire( username varchar(255) not null, path varchar(100) not null, timestamp integer, primary key (username, path))engine=innodb;
That should work for you. Of course enter in your correct config location and your specific expire settings.
-Eric
Hello Eric,
thank you very much for your prompt and helpful reply!
The connect seem to work fine now.
Please allow me two additional questions: Is it correct that this database-table is only a "caching-table" which is empty at first and will be filled later? It is not necessary to adapt the SQL-statement to my user-configuration, is it?! Correct. The table is filled when the message is moved to one of the folders that is marked as an Expire folder. That folder is added to the
Jens Meyer wrote: table with a timestamp like so:
$ echo "select * from mail.expire where path like 'eric%'" | mysql -u root -p Enter password: username path timestamp eric@flerd.com/Junk 1217943338 eric@flerd.com/Trash 1217941084
Is it necessary to reference the foldernames with "INBOX.Trash" or only "Trash"? For Sieve I have to use "INBOX.Trash".
Use INBOX.Trash if the trash folder you're looking to clear out is a subfolder of your Inbox.
Actually nothing happens when trying the plugin with "dovecot --exec-mail ext /usr/libexec/dovecot/expire-tool --test". Do you have the plugin loaded in the imap protocol section?
protocol imap { ... mail_plugins = fts fts_squat quota imap_quota expire ... }
If you do then the table should get updated when you move a message into the trash folder.
If you've got the plugin set correctly and you're still not getting anything written into the table you can restart mysql with query logging on:
http://dev.mysql.com/doc/refman/5.0/en/query-log.html
Then move a message to one of the expire folders and see what query is run on the database and if it's not succeeding due to an error.
Hello Eric,
thank you for your further help.
Now I understand the concept of this plugin - great!
The record was created:
username path timestamp jens@test.jensmeyer.de/Trash 1218029872
AFAIK I have to add "Trash" instead of "INBOX.Trash" when seeing this record, correct?
Kind regards,
JensEric Toczek schrieb:
Hello Eric,
thank you very much for your prompt and helpful reply!
The connect seem to work fine now.
Please allow me two additional questions: Is it correct that this database-table is only a "caching-table" which is empty at first and will be filled later? It is not necessary to adapt the SQL-statement to my user-configuration, is it?! Correct. The table is filled when the message is moved to one of the folders that is marked as an Expire folder. That folder is added to the
Jens Meyer wrote: table with a timestamp like so:
$ echo "select * from mail.expire where path like 'eric%'" | mysql -u root -p Enter password: username path timestamp eric@flerd.com/Junk 1217943338 eric@flerd.com/Trash 1217941084
Is it necessary to reference the foldernames with "INBOX.Trash" or only "Trash"? For Sieve I have to use "INBOX.Trash".
Use INBOX.Trash if the trash folder you're looking to clear out is a subfolder of your Inbox.
Actually nothing happens when trying the plugin with "dovecot --exec-mail ext /usr/libexec/dovecot/expire-tool --test". Do you have the plugin loaded in the imap protocol section?
protocol imap { ... mail_plugins = fts fts_squat quota imap_quota expire ... }
If you do then the table should get updated when you move a message into the trash folder.
If you've got the plugin set correctly and you're still not getting anything written into the table you can restart mysql with query logging on:
http://dev.mysql.com/doc/refman/5.0/en/query-log.html
Then move a message to one of the expire folders and see what query is run on the database and if it's not succeeding due to an error.
Dear Jens,
Mind didn't worked ever. Below is my dovecot config. Could you give me a hint on it.? Dovecot didn't update the dict_expire table whenever I remove a message or move a message into the Trash folder. Is there any different on the dovecot.conf between u and me ?
# dovecot-dict-expire.conf connect = host=xxxx dbname=xxxx user=xxxxpassword=xxxx table = dict_expire select_field = timestamp where_field = path username_field = username
# dovecot -n # 1.1.2: /usr/local/etc/dovecot.conf base_dir: /var/run/dovecot/ log_path: /var/log/dovecot.log protocols: imap pop3 listen: 192.168.18.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 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 fts_squat quota imap_quota mail_plugins(imap): expire fts fts_squat quota imap_quota mail_plugins(pop3): expire 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 2 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
Thanks, Dino
Jens Meyer wrote:
Hello Eric,
thank you for your further help.
Now I understand the concept of this plugin - great!
The record was created:
username path timestamp jens@test.jensmeyer.de/Trash 1218029872
AFAIK I have to add "Trash" instead of "INBOX.Trash" when seeing this record, correct?
Kind regards,
JensEric Toczek schrieb:
Hello Eric,
thank you very much for your prompt and helpful reply!
The connect seem to work fine now.
Please allow me two additional questions: Is it correct that this database-table is only a "caching-table" which is empty at first and will be filled later? It is not necessary to adapt the SQL-statement to my user-configuration, is it?! Correct. The table is filled when the message is moved to one of the folders that is marked as an Expire folder. That folder is added to the
Jens Meyer wrote: table with a timestamp like so:
$ echo "select * from mail.expire where path like 'eric%'" | mysql -u root -p Enter password: username path timestamp eric@flerd.com/Junk 1217943338 eric@flerd.com/Trash 1217941084
Is it necessary to reference the foldernames with "INBOX.Trash" or only "Trash"? For Sieve I have to use "INBOX.Trash".
Use INBOX.Trash if the trash folder you're looking to clear out is a subfolder of your Inbox.
Actually nothing happens when trying the plugin with "dovecot --exec-mail ext /usr/libexec/dovecot/expire-tool --test". Do you have the plugin loaded in the imap protocol section?
protocol imap { ... mail_plugins = fts fts_squat quota imap_quota expire ... }
If you do then the table should get updated when you move a message into the trash folder.
If you've got the plugin set correctly and you're still not getting anything written into the table you can restart mysql with query logging on: http://dev.mysql.com/doc/refman/5.0/en/query-log.html
Then move a message to one of the expire folders and see what query is run on the database and if it's not succeeding due to an error.
- Eric Toczek, 2008-08-05 15:30
Do you have the plugin loaded in the imap protocol section?
protocol imap { ... mail_plugins = fts fts_squat quota imap_quota expire ... }
If you do then the table should get updated when you move a message into the trash folder.
If you've got the plugin set correctly and you're still not getting anything written into the table you can restart mysql with query logging on:
http://dev.mysql.com/doc/refman/5.0/en/query-log.html
Then move a message to one of the expire folders and see what query is run on the database and if it's not succeeding due to an error.
Hi,
Same problem here - dovecot never adds any records to the expire table, although the database connection is fine. All it ever does is query for existing records when a message gets moved to Trash or Junk, so at least that part is working.
Here's the mysql.log entries:
080806 6:59:04 39 Connect dovecot@localhost on dovecot 39 Query SELECT when FROM expire WHERE path = 'zlatko-mail/Trash' 080806 7:00:21 39 Query SELECT when FROM expire WHERE path = 'zlatko-mail/Trash' 080806 7:00:23 39 Query SELECT when FROM expire WHERE path = 'zlatko-mail/Trash' 080806 7:00:25 39 Query SELECT when FROM expire WHERE path = 'zlatko-mail/Trash' 080806 7:18:13 39 Query SELECT when FROM expire WHERE path = 'zlatko-mail/Junk' 080806 7:19:47 39 Query SELECT path, when FROM expire ORDER BY when
These SELECTs don't return any rows, as the table is still empty. If I understand the expire-plugin.c source correctly, then dovecot should insert a new record in this case (folder path and timestamp when to expire this folder), but that's just not happening.
The last query is from an 'expire-tool --test' run, and of course this SELECT also doesn't return anything from the empty table.
Do I have to have my userdb and/or in MySQL as well to make it work properly? Right now, I'm using userdb=passwd and passdb=passwd-file (for CRAM-MD5 authentication).
Bye, Thomas
Thomas Zajic wrote:
Hi, Same problem here - dovecot never adds any records to the expire table, although the database connection is fine. All it ever does is query for existing records when a message gets moved to Trash or Junk, so at least that part is working.
Do you use namespaces? Since Timo just discovered they may not be working with expire properly. "Looking at the code it looks like the expire plugin ignores the namespace prefix but expire-tool requires it, so it probably won't work.."
- Eric Toczek, 2008-08-06 11:39
Thomas Zajic wrote:
Hi, Same problem here - dovecot never adds any records to the expire table, although the database connection is fine. All it ever does is query for existing records when a message gets moved to Trash or Junk, so at least that part is working.
Do you use namespaces? Since Timo just discovered they may not be working with expire properly. "Looking at the code it looks like the expire plugin ignores the namespace prefix but expire-tool requires it, so it probably won't work.."
No, I'm not using namespaces, or at least not intentionally. Here's the output of 'dovecot -n' and other possibly relevant stuff:
root@airframe:~# dovecot -n # 1.1.2: /usr/local/etc/dovecot.conf login_dir: /usr/local/var/run/dovecot/login login_executable: /usr/local/libexec/dovecot/imap-login login_greeting_capability: yes mail_max_userip_connections: 20 verbose_proctitle: yes mail_location: maildir:/home/%u/Maildir:INBOX=/home/%u/Maildir/.INBOX fsync_disable: yes mail_plugins: expire fts fts_squat auth default: mechanisms: plain digest-md5 cram-md5 passdb: driver: passwd-file args: /usr/local/etc/dovecot-passwd userdb: driver: passwd socket: type: listen master: path: /usr/local/var/run/dovecot/auth-master mode: 384 plugin: expire: Trash 30 Junk 60 expire_dict: proxy::expire fts: squat fts_squat: partial=4 full=6 dict: expire: mysql:/usr/local/etc/dovecot-sql-expire.conf
root@airframe:~# cat /usr/local/etc/dovecot-sql-expire.conf driver = mysql connect = host=/var/run/mysql/mysql.sock user=XXX password=YYY dbname=dovecot table = expire select_field = when where_field = path username_field = username
root@airframe:~# mysql -p dovecot Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 900 Server version: 5.0.51 Source distribution
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> describe expire; +----------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+--------------+------+-----+---------+-------+ | username | varchar(64) | NO | PRI | NULL | | | path | varchar(255) | NO | PRI | NULL | | | when | varchar(64) | NO | | NULL | | +----------+--------------+------+-----+---------+-------+
Do you see any obvious errors and/or problems?
Thanks, Thomas
participants (5)
-
Dino Ming
-
Dino Ming
-
Eric Toczek
-
Jens Meyer
-
Thomas Zajic