Re: [Dovecot] Dovecot quota dict
On Tue, 09 Aug 2011 11:18:55 -0300 Juan Bernhard wrote: Hi Nikita, thanks for the help, but I don't have any problem with bouncing the mail after the deliver (in fact, the mail pass two MTAs servers before reaching the final storage) I will try (with my limited english) to explain it better. Now I m working with a single quota rule for everyone. What I need is a mechanism to give a custom quota to each user. I thought that a flat file will be the simple and easy option to implement, and then use quota=${lookup {$local_part} lsearch .} in the exim transport. I need a way to configure dovecot to follow the same file (or at least a modified copy). I've been told on this list that I can't rely on the maildirsize file, because it can be deleted under some conditions... so i neet to stablish quotas to dovecot somewhere else. My question is: can dovecot get a custon quota for a user from a file (not sql or ldap) and use at the same time pam as userdb? Thanks, Juan. So, you need customizable userdb lookup without ldap or sql. You can try too use checkpassword as userdb and then, in the script - parse quota-file, make pam lookup and return userdb_quota* values for specific user. More on http://wiki2.dovecot.org/AuthDatabase/CheckPassword . I don't have experience with this authdatabase method, but seems that it suitable for you. But in general - it's better to prevent editing maildirsize file from 2 instances.
Re: [Dovecot] Dovecot quota dict
Thank you Nikita, that's what I need. El 10/08/2011 03:35 a.m., Nikita Koshikov escribió: On Tue, 09 Aug 2011 11:18:55 -0300 Juan Bernhard wrote: Hi Nikita, thanks for the help, but I don't have any problem with bouncing the mail after the deliver (in fact, the mail pass two MTAs servers before reaching the final storage) I will try (with my limited english) to explain it better. Now I m working with a single quota rule for everyone. What I need is a mechanism to give a custom quota to each user. I thought that a flat file will be the simple and easy option to implement, and then use quota=${lookup {$local_part} lsearch .} in the exim transport. I need a way to configure dovecot to follow the same file (or at least a modified copy). I've been told on this list that I can't rely on the maildirsize file, because it can be deleted under some conditions... so i neet to stablish quotas to dovecot somewhere else. My question is: can dovecot get a custon quota for a user from a file (not sql or ldap) and use at the same time pam as userdb? Thanks, Juan. So, you need customizable userdb lookup without ldap or sql. You can try too use checkpassword as userdb and then, in the script - parse quota-file, make pam lookup and return userdb_quota* values for specific user. More on http://wiki2.dovecot.org/AuthDatabase/CheckPassword . I don't have experience with this authdatabase method, but seems that it suitable for you. But in general - it's better to prevent editing maildirsize file from 2 instances.
Re: [Dovecot] Dovecot quota dict
On Mon, 08 Aug 2011 16:35:53 -0300 Juan Bernhard wrote: Hi, Im using dovecot with maildir++ quota and I have this problem, maybe some of you already solve this and can helpme. I want to use some flat file to tell exim and dovecot the quota limit for each user, i tried to work with exim and maildirsize file, but exim just ignores the limits there. I need a way (a really simple one, i dont want to create a database or an ldap server just for this) to tell exim and dovecot to lookup some file to get the quota limits. I know how to doit with exim, now i need to know how to tell dovecot to read the quota limits from this file. I know that dovecot can read the quota limit from the userdb, but im using pam and i dont want to change the entire auth schema. Can I configure dovecot to get the quota limit from a file using pam as userdb? Thanks, Juan. pd: here is my doveconf -n # 2.0.13: /usr/local/etc/dovecot/dovecot.conf # OS: Linux 2.6.18-238.9.1.el5 x86_64 CentOS release 5.6 (Final) ext3 auth_cache_size = 10 M auth_debug = yes auth_verbose = yes base_dir = /var/run/dovecot/ default_client_limit = 5000 default_process_limit = 1000 disable_plaintext_auth = no listen = * login_greeting = Server ready. mail_debug = yes mail_location = maildir:/var/mail/%u:INDEX=~/ mail_plugins = quota maildir_very_dirty_syncs = yes passdb { args = failure_show_msg=yes %s driver = pam } plugin { mail_log_events = delete undelete expunge copy mailbox_delete mailbox_rename mail_log_fields = uid box msgid size subject quota = maildir:Cuota de correo quota_rule = *:storage=1G quota_rule2 = Trash:storage=+100M } protocols = imap pop3 service auth { client_limit = 12000 } service imap { process_limit = 5000 } service pop3 { process_limit = 5000 } ssl = no ssl_cert = /etc/ssl/certs/dovecot.pem ssl_key = /etc/ssl/private/dovecot.pem userdb { driver = passwd } protocol imap { imap_client_workarounds = tb-extra-mailbox-sep tb-lsub-flags mail_plugins = quota imap_quota } protocol pop3 { pop3_client_workarounds = outlook-no-nuls oe-ns-eoh pop3_lock_session = yes pop3_uidl_format = %08Xv%08Xu } Try to google exim smtp-time quota bounce. This is what you need. Exim can't do rcpt acl quota checks from the box, thus you need: 1)Some external script which will periodically create overquota file\database of users and reject them at smtp-time using lookup 2)Or write perl-script and exec it in router stage. Script will open maildirsize file, count quota and gives you 'yes\no'. Also you need to deliver via dovecot deliver. This scheme will work.
Re: [Dovecot] Dovecot quota dict
Hi Nikita, thanks for the help, but I don't have any problem with bouncing the mail after the deliver (in fact, the mail pass two MTAs servers before reaching the final storage) I will try (with my limited english) to explain it better. Now I m working with a single quota rule for everyone. What I need is a mechanism to give a custom quota to each user. I thought that a flat file will be the simple and easy option to implement, and then use quota=${lookup {$local_part} lsearch .} in the exim transport. I need a way to configure dovecot to follow the same file (or at least a modified copy). I've been told on this list that I can't rely on the maildirsize file, because it can be deleted under some conditions... so i neet to stablish quotas to dovecot somewhere else. My question is: can dovecot get a custon quota for a user from a file (not sql or ldap) and use at the same time pam as userdb? Thanks, Juan. El 09/08/2011 03:29 a.m., Nikita Koshikov escribió: On Mon, 08 Aug 2011 16:35:53 -0300 Juan Bernhard wrote: Hi, Im using dovecot with maildir++ quota and I have this problem, maybe some of you already solve this and can helpme. I want to use some flat file to tell exim and dovecot the quota limit for each user, i tried to work with exim and maildirsize file, but exim just ignores the limits there. I need a way (a really simple one, i dont want to create a database or an ldap server just for this) to tell exim and dovecot to lookup some file to get the quota limits. I know how to doit with exim, now i need to know how to tell dovecot to read the quota limits from this file. I know that dovecot can read the quota limit from the userdb, but im using pam and i dont want to change the entire auth schema. Can I configure dovecot to get the quota limit from a file using pam as userdb? Thanks, Juan. pd: here is my doveconf -n # 2.0.13: /usr/local/etc/dovecot/dovecot.conf # OS: Linux 2.6.18-238.9.1.el5 x86_64 CentOS release 5.6 (Final) ext3 auth_cache_size = 10 M auth_debug = yes auth_verbose = yes base_dir = /var/run/dovecot/ default_client_limit = 5000 default_process_limit = 1000 disable_plaintext_auth = no listen = * login_greeting = Server ready. mail_debug = yes mail_location = maildir:/var/mail/%u:INDEX=~/ mail_plugins = quota maildir_very_dirty_syncs = yes passdb { args = failure_show_msg=yes %s driver = pam } plugin { mail_log_events = delete undelete expunge copy mailbox_delete mailbox_rename mail_log_fields = uid box msgid size subject quota = maildir:Cuota de correo quota_rule = *:storage=1G quota_rule2 = Trash:storage=+100M } protocols = imap pop3 service auth { client_limit = 12000 } service imap { process_limit = 5000 } service pop3 { process_limit = 5000 } ssl = no ssl_cert =/etc/ssl/certs/dovecot.pem ssl_key =/etc/ssl/private/dovecot.pem userdb { driver = passwd } protocol imap { imap_client_workarounds = tb-extra-mailbox-sep tb-lsub-flags mail_plugins = quota imap_quota } protocol pop3 { pop3_client_workarounds = outlook-no-nuls oe-ns-eoh pop3_lock_session = yes pop3_uidl_format = %08Xv%08Xu } Try to google exim smtp-time quota bounce. This is what you need. Exim can't do rcpt acl quota checks from the box, thus you need: 1)Some external script which will periodically create overquota file\database of users and reject them at smtp-time using lookup 2)Or write perl-script and exec it in router stage. Script will open maildirsize file, count quota and gives you 'yes\no'. Also you need to deliver via dovecot deliver. This scheme will work.
[Dovecot] Dovecot quota dict
Hi, Im using dovecot with maildir++ quota and I have this problem, maybe some of you already solve this and can helpme. I want to use some flat file to tell exim and dovecot the quota limit for each user, i tried to work with exim and maildirsize file, but exim just ignores the limits there. I need a way (a really simple one, i dont want to create a database or an ldap server just for this) to tell exim and dovecot to lookup some file to get the quota limits. I know how to doit with exim, now i need to know how to tell dovecot to read the quota limits from this file. I know that dovecot can read the quota limit from the userdb, but im using pam and i dont want to change the entire auth schema. Can I configure dovecot to get the quota limit from a file using pam as userdb? Thanks, Juan. pd: here is my doveconf -n # 2.0.13: /usr/local/etc/dovecot/dovecot.conf # OS: Linux 2.6.18-238.9.1.el5 x86_64 CentOS release 5.6 (Final) ext3 auth_cache_size = 10 M auth_debug = yes auth_verbose = yes base_dir = /var/run/dovecot/ default_client_limit = 5000 default_process_limit = 1000 disable_plaintext_auth = no listen = * login_greeting = Server ready. mail_debug = yes mail_location = maildir:/var/mail/%u:INDEX=~/ mail_plugins = quota maildir_very_dirty_syncs = yes passdb { args = failure_show_msg=yes %s driver = pam } plugin { mail_log_events = delete undelete expunge copy mailbox_delete mailbox_rename mail_log_fields = uid box msgid size subject quota = maildir:Cuota de correo quota_rule = *:storage=1G quota_rule2 = Trash:storage=+100M } protocols = imap pop3 service auth { client_limit = 12000 } service imap { process_limit = 5000 } service pop3 { process_limit = 5000 } ssl = no ssl_cert = /etc/ssl/certs/dovecot.pem ssl_key = /etc/ssl/private/dovecot.pem userdb { driver = passwd } protocol imap { imap_client_workarounds = tb-extra-mailbox-sep tb-lsub-flags mail_plugins = quota imap_quota } protocol pop3 { pop3_client_workarounds = outlook-no-nuls oe-ns-eoh pop3_lock_session = yes pop3_uidl_format = %08Xv%08Xu }
[Dovecot] quota dict play nice with existing tables
Hi, I'm trying to set Dovecot 2.0 with postgresql dict. It's kind of working but not as I'd like to. Dovecot expects to get a dedicated table for quota management. When I've run *doveadm quota recalc -A* I've noticed in the logs that Dovecot tries to run DELETE statements. Fortunately it didn't succeed as I've only given it SELECT and UPDATE permissions on the fields that it needs. I'd like to configure it, in UPDATE only mode as rows already exist and there is no need to issue any other statement. Regards, Miha -- It's time to get rid of your current e-mail client ... ... and start using si.Mail. It's small free. ( http://www.simail.si/ )
Re: [Dovecot] quota dict play nice with existing tables
On Tue, 2010-09-14 at 18:07 +0200, Miha Vrhovnik wrote: I'm trying to set Dovecot 2.0 with postgresql dict. It's kind of working but not as I'd like to. Dovecot expects to get a dedicated table for quota management. When I've run *doveadm quota recalc -A* I've noticed in the logs that Dovecot tries to run DELETE statements. Fortunately it didn't succeed as I've only given it SELECT and UPDATE permissions on the fields that it needs. I'd like to configure it, in UPDATE only mode as rows already exist and there is no need to issue any other statement. Maybe some day, but it's not something I'm planning on implementing anytime soon. Feel free to poke around the code to see if there's a way to do it without horrible changes.
Re: [Dovecot] quota dict play nice with existing tables
On Tue, 2010-09-14 at 17:49 +0100, Timo Sirainen wrote: On Tue, 2010-09-14 at 18:07 +0200, Miha Vrhovnik wrote: I'm trying to set Dovecot 2.0 with postgresql dict. It's kind of working but not as I'd like to. Dovecot expects to get a dedicated table for quota management. When I've run *doveadm quota recalc -A* I've noticed in the logs that Dovecot tries to run DELETE statements. Fortunately it didn't succeed as I've only given it SELECT and UPDATE permissions on the fields that it needs. I'd like to configure it, in UPDATE only mode as rows already exist and there is no need to issue any other statement. Maybe some day, but it's not something I'm planning on implementing anytime soon. Feel free to poke around the code to see if there's a way to do it without horrible changes. Maybe it could treat NULL value as row not found and DELETE could be replaced with UPDATE to NULL. Maybe.
Re: [Dovecot] quota dict play nice with existing table s
Timo Sirainen t...@iki.fi wrote on 14.9.2010 18:51:29: On Tue, 2010-09-14 at 17:49 +0100, Timo Sirainen wrote: On Tue, 2010-09-14 at 18:07 +0200, Miha Vrhovnik wrote: I'm trying to set Dovecot 2.0 with postgresql dict. It's kind of working but not as I'd like to. Dovecot expects to get a dedicated table for quota management. When I've run *doveadm quota recalc -A* I've noticed in the logs that Dovecot tries to run DELETE statements. Fortunately it didn't succeed as I've only given it SELECT and UPDATE permissions on the fields that it needs. I'd like to configure it, in UPDATE only mode as rows already exist and there is no need to issue any other statement. Maybe some day, but it's not something I'm planning on implementing anytime soon. Feel free to poke around the code to see if there's a way to do it without horrible changes. Maybe it could treat NULL value as row not found and DELETE could be replaced with UPDATE to NULL. Maybe. I don't know the internals but. Wouldn't it be possible to do: When removing/receiving new message and same in case of doveadm recalc. SELECT with key.. if not found INSERT else UPDATE unless I'm missing something upper one could be optimized to UPDATE if changed rows 1 then INSERT which wouldn't require the SELECT and would also spare the trigger when using postresql. Regards, Miha -- It's time to get rid of your current e-mail client ... ... and start using si.Mail. It's small free. ( http://www.simail.si/ )
Re: [Dovecot] quota dict play nice with existing tables
On Tue, 2010-09-14 at 19:08 +0200, Miha Vrhovnik wrote: When removing/receiving new message and same in case of doveadm recalc. SELECT with key.. if not found INSERT else UPDATE I don't think this kind of code exists anywhere? It's possible that doveadm does a SELECT, but it's not to find out if the row exists or not, it just wants to know what the current quota value is (although for quota recalc that's probably pointless and could be optimized away). The actual row updating code works with INSERT INTO .. ON DUPLICATE KEY UPDATE .. with MySQL, and with INSERT+trigger with PostgreSQL. unless I'm missing something upper one could be optimized to UPDATE if changed rows 1 then INSERT which wouldn't require the SELECT and would also spare the trigger when using postresql. The trigger is there to avoid race conditions: 1) two processes try to add the row at the same time 2) one process tries to update the row when another had just deleted it
Re: [Dovecot] quota dict play nice with existing table s
Timo Sirainen t...@iki.fi wrote on 14.9.2010 19:24:14: On Tue, 2010-09-14 at 19:08 +0200, Miha Vrhovnik wrote: When removing/receiving new message and same in case of doveadm recalc. SELECT with key.. if not found INSERT else UPDATE I don't think this kind of code exists anywhere? I was being hypothetical. The actual row updating code works with INSERT INTO .. ON DUPLICATE KEY UPDATE .. with MySQL, and with INSERT+trigger with PostgreSQL. unless I'm missing something upper one could be optimized to UPDATE if changed rows 1 then INSERT which wouldn't require the SELECT and would also spare the trigger when using postresql. The trigger is there to avoid race conditions: 1) two processes try to add the row at the same time 2) one process tries to update the row when another had just deleted it It seems that I missed the race conditions. Have decided that it would be best to give the Dovecot dedicated table and then add DELETE trigger and modify insert/update one to also update the values in my table. Regards, Miha -- It's time to get rid of your current e-mail client ... ... and start using si.Mail. It's small free. ( http://www.simail.si/ )
Re: [Dovecot] quota dict mysql not working (2.0.beta4)
On Mon, 2010-05-24 at 00:47 +0400, Andrey Melnikov wrote: after mysql_query() - mysql return right data (affected rows = 1), but there is no result to store (this is UPDATE/DELETE query), next unconditional call to mysql_store_result() return nothing (right, there no result) and reset affected_rows to -1ULL. You should modify driver_mysql_query_s() to call mysql_field_count() first, to ensure - there is any data to read or no. But the whole point of the code is to get number of affected rows for previous UPDATE, so that if there is no quota row for user, the quota gets recalculated. This fixed it for my tests: http://hg.dovecot.org/dovecot-2.0/rev/0c15a760dab8
Re: [Dovecot] quota dict mysql not working (2.0.beta4)
On 26.05.2010 16:32, wrote Timo Sirainen: On Mon, 2010-05-24 at 00:47 +0400, Andrey Melnikov wrote: after mysql_query() - mysql return right data (affected rows = 1), but there is no result to store (this is UPDATE/DELETE query), next unconditional call to mysql_store_result() return nothing (right, there no result) and reset affected_rows to -1ULL. You should modify driver_mysql_query_s() to call mysql_field_count() first, to ensure - there is any data to read or no. But the whole point of the code is to get number of affected rows for previous UPDATE, so that if there is no quota row for user, the quota gets recalculated. This fixed it for my tests: http://hg.dovecot.org/dovecot-2.0/rev/0c15a760dab8 Yes, this fixed it. Thanks Timo!
Re: [Dovecot] quota dict mysql not working (2.0.beta4)
On 20.05.2010 19:52, wrote Timo Sirainen: On 5.5.2010, at 20.34, Michael Kliewe wrote: -- dovecot-2.0.beta4-25/src/lib-sql/driver-mysql.c 2010-04-02 16:47:13.0 +0400 +++ dovecot-2.0.beta4/src/lib-sql/driver-mysql.c2010-05-01 03:29:02.0 +0400 @@ -630,7 +631,9 @@ struct mysql_result *my_result = (struct mysql_result *)result; rows = mysql_affected_rows(my_result-conn-mysql); - i_assert(rows != (my_ulonglong)-1); + if (mysql_errno(my_result-conn-mysql)) + i_assert(rows != (my_ulonglong)-1); + else rows = 0; *ctx-head-affected_rows = rows; } sql_result_unref(result); The above patch works, but it doesn't really fix the cause of the bug. This should fix it in a cleaner way: http://hg.dovecot.org/dovecot-2.0/rev/1e45b463b93a Hhm, this doesn't seem to fix it for me. Just tested with the latest hg version (f7527971fed0) and hitting the assertion again. Unfortunately I don't manage to get a real core dump from the dictionary process, just the backtrace attached. May 25 19:51:20 ubuntu-test dovecot: master: Dovecot v2.0.beta5 (f7527971fed0) starting up ... May 25 19:53:34 ubuntu-test dovecot: master: Error: service(dict): child 9755 killed with signal 6 (core not dumped) May 25 19:53:34 ubuntu-test dovecot: dict: Panic: file driver-mysql.c: line 482 (transaction_send_query): assertion failed: (rows != (my_ulonglong)-1) May 25 19:53:34 ubuntu-test dovecot: dict: Error: Raw backtrace: /usr/lib/dovecot/libdovecot.so.0(+0x33dd1) [0xb77a2dd1] - /usr/lib/dovecot/libdovecot.so.0(+0x33e3f) [0xb77a2e3f] - /usr/ lib/dovecot/libdovecot.so.0(i_error+0) [0xb777d70e] - dovecot/dict() [0x805219a] - dovecot/dict() [0x805220f] - dovecot/dict(sql_transaction_commit_s+0x20) [0x804f9e0] - dovecot/dict() [0x805144e] - dovecot/dict(sql_transaction_commit_s+0x20) [0x804f9e0] - dovecot/dict() [0x804e982] - /usr/lib/dovecot/libdovecot.so.0(dict_transaction_commit_async+0x2f) [0xb778285f] - dovecot/dict() [0x804cc40] - dovecot/dict() [0x804c6ad] - /usr/lib/dovecot/libdovecot.so.0(io_loop_handler_run+0xe2) [0xb77af3a2] - /usr/lib/dovecot/libdovecot.so.0(io_loop_run+0x30) [0xb77ae3f0] - /usr/lib/dovecot/libdovecot.so.0(master_service_run+0x2a) [0xb779bb5a] - dovecot/dict(main+0x19c) [0x804d30c] - /lib/tls/i686/cmov/libc.so.6(__libc_start_main+0xe6) [0xb7 379bd6] - dovecot/dict() [0x804c391] May 25 19:53:34 ubuntu-test dovecot: lmtp(9754): Error: read(/var/run/dovecot/dict) failed: Remote disconnected May 25 19:53:34 ubuntu-test dovecot: lmtp(9754): Disconnect from local: Client quit ...
Re: [Dovecot] quota dict mysql not working (2.0.beta4)
On 5.5.2010, at 20.34, Michael Kliewe wrote: -- dovecot-2.0.beta4-25/src/lib-sql/driver-mysql.c 2010-04-02 16:47:13.0 +0400 +++ dovecot-2.0.beta4/src/lib-sql/driver-mysql.c2010-05-01 03:29:02.0 +0400 @@ -630,7 +631,9 @@ struct mysql_result *my_result = (struct mysql_result *)result; rows = mysql_affected_rows(my_result-conn-mysql); - i_assert(rows != (my_ulonglong)-1); + if (mysql_errno(my_result-conn-mysql)) + i_assert(rows != (my_ulonglong)-1); + else rows = 0; *ctx-head-affected_rows = rows; } sql_result_unref(result); The above patch works, but it doesn't really fix the cause of the bug. This should fix it in a cleaner way: http://hg.dovecot.org/dovecot-2.0/rev/1e45b463b93a
[Dovecot] quota dict mysql not working (2.0.beta4)
Hi, I have a problem when setting up the quota dict plugin. I get this error in my logfile when trying to store a draft (APPEND): May 05 14:44:53 imap(t...@domain.de): Error: net_connect_unix(/usr/ local/var/run/dovecot/dict) failed: Permission denied May 05 14:44:53 imap(t...@domain.de): Error: Internal quota calculation error The file is there: u...@imap01:/$ ls -al /usr/local/var/run/dovecot/ total 20 drwxr-xr-x 4 rootroot4096 2010-05-05 14:44 . drwxr-xr-x 3 rootroot4096 2010-04-20 18:50 .. srw--- 1 rootroot 0 2010-05-05 14:44 anvil srw--- 1 rootroot 0 2010-05-05 14:44 anvil-auth-penalty srw--- 1 rootroot 0 2010-05-05 14:44 auth-client srw--- 1 rootroot 0 2010-05-05 14:44 auth-master srw--- 1 rootroot 0 2010-05-05 14:44 auth-userdb srw--- 1 dovecot root 0 2010-05-05 14:44 auth-worker srw--- 1 rootroot 0 2010-05-05 14:44 config srw--- 1 rootroot 0 2010-05-05 14:44 dict srw-rw-rw- 1 rootroot 0 2010-05-05 14:44 dns-client lrwxrwxrwx 1 rootroot 35 2010-05-05 14:44 dovecot.conf - / usr/local/etc/dovecot/dovecot.conf drwxr-xr-x 2 rootroot4096 2010-04-20 18:50 empty srw-rw-rw- 1 rootroot 0 2010-05-05 14:44 lmtp drwxr-x--- 2 rootdovecot 4096 2010-05-05 14:44 login -rw--- 1 rootroot 5 2010-05-05 14:44 master.pid What is going on there, how can I fix this? Michael
Re: [Dovecot] quota dict mysql not working (2.0.beta4)
Ok, found the problem and fixed it. Two things had to be done: master.cnf service dict { extra_groups = vmail unix_listener dict { group = vmail mode = 0660 } } After restart I got another problem which I fixed with this patch (Thanks Andrey!) -- dovecot-2.0.beta4-25/src/lib-sql/driver-mysql.c 2010-04-02 16:47:13.0 +0400 +++ dovecot-2.0.beta4/src/lib-sql/driver-mysql.c2010-05-01 03:29:02.0 +0400 @@ -630,7 +631,9 @@ struct mysql_result *my_result = (struct mysql_result *)result; rows = mysql_affected_rows(my_result-conn-mysql); - i_assert(rows != (my_ulonglong)-1); + if (mysql_errno(my_result-conn-mysql)) + i_assert(rows != (my_ulonglong)-1); + else rows = 0; *ctx-head-affected_rows = rows; } sql_result_unref(result); I also wrote Timo an email regarding this problem. Hope he fixes it soon. Michael On May 5, 2010, at 3:11 PM, Michael Kliewe wrote: Hi, I have a problem when setting up the quota dict plugin. I get this error in my logfile when trying to store a draft (APPEND): May 05 14:44:53 imap(t...@domain.de): Error: net_connect_unix(/usr/ local/var/run/dovecot/dict) failed: Permission denied May 05 14:44:53 imap(t...@domain.de): Error: Internal quota calculation error The file is there: u...@imap01:/$ ls -al /usr/local/var/run/dovecot/ total 20 drwxr-xr-x 4 rootroot4096 2010-05-05 14:44 . drwxr-xr-x 3 rootroot4096 2010-04-20 18:50 .. srw--- 1 rootroot 0 2010-05-05 14:44 anvil srw--- 1 rootroot 0 2010-05-05 14:44 anvil-auth-penalty srw--- 1 rootroot 0 2010-05-05 14:44 auth-client srw--- 1 rootroot 0 2010-05-05 14:44 auth-master srw--- 1 rootroot 0 2010-05-05 14:44 auth-userdb srw--- 1 dovecot root 0 2010-05-05 14:44 auth-worker srw--- 1 rootroot 0 2010-05-05 14:44 config srw--- 1 rootroot 0 2010-05-05 14:44 dict srw-rw-rw- 1 rootroot 0 2010-05-05 14:44 dns-client lrwxrwxrwx 1 rootroot 35 2010-05-05 14:44 dovecot.conf - / usr/local/etc/dovecot/dovecot.conf drwxr-xr-x 2 rootroot4096 2010-04-20 18:50 empty srw-rw-rw- 1 rootroot 0 2010-05-05 14:44 lmtp drwxr-x--- 2 rootdovecot 4096 2010-05-05 14:44 login -rw--- 1 rootroot 5 2010-05-05 14:44 master.pid What is going on there, how can I fix this? Michael
Re: [Dovecot] quota dict with mysql 4.0
On 25.3.2010, at 11.24, Andre Hübner wrote: i use successfully mysql-quotasystem with dovecot 1.2.11 on different servers except for mysql 4.0 servers dovecot internally uses sql: ON DUPLICATE KEY UPDATE which was introduced in mysql 4.1 The original code supported dict-sql only if ON DUPLICATE KEY UPDATE was supported by the server. Later I added support for PostgreSQL and SQLite by having them use INSERT triggers that update the data if it already exists. I suppose MySQL 4.0 doesn't support these kind of triggers either? Writing code manually to first try to UPDATE and if it fails then INSERT would be possible.. But too much trouble for me to spend time on.
Re: [Dovecot] quota dict with mysql 4.0
Hello, i use successfully mysql-quotasystem with dovecot 1.2.11 on different servers except for mysql 4.0 servers dovecot internally uses sql: ON DUPLICATE KEY UPDATE which was introduced in mysql 4.1 The original code supported dict-sql only if ON DUPLICATE KEY UPDATE was supported by the server. Later I added support for PostgreSQL and SQLite by having them use INSERT triggers that update the data if it already exists. I suppose MySQL 4.0 doesn't support these kind of triggers either? Writing code manually to first try to UPDATE and if it fails then INSERT would be possible.. But too much trouble for me to spend time on. its a pity :( i did now a other way. not really clean... i removed code for ON DUPLICATE KEY UPDATE from C-Code and splitted quota table into two tables. This seems to work because the second insert stays away. not very clean but seems to work... Thanks, Andre
[Dovecot] quota dict with mysql 4.0
Hello, i use successfully mysql-quotasystem with dovecot 1.2.11 on different servers except for mysql 4.0 servers dovecot internally uses sql: ON DUPLICATE KEY UPDATE which was introduced in mysql 4.1 Is there a chance to make this statement mysql 4.0 compatible or should i update mysql? i want to avoid updating mysql because some old software is running which is not compatible to higher mysql versions. Thanks, Andre
Re: [Dovecot] quota dict with mysql 4.0
On Thu, 25 Mar 2010 10:24:37 +0100, Andre Hübner andre.hueb...@gmx.de articulated: i use successfully mysql-quotasystem with dovecot 1.2.11 on different servers except for mysql 4.0 servers dovecot internally uses sql: ON DUPLICATE KEY UPDATE which was introduced in mysql 4.1 Is there a chance to make this statement mysql 4.0 compatible or should i update mysql? i want to avoid updating mysql because some old software is running which is not compatible to higher mysql versions. In my opinion, continued use of depreciated software is a practice that should be avoided whenever possible. Inevitably, it causes problems as you are now experiencing. I am somewhat surprised that the older software is not compatible with the newer version of MySQL. Usually, it is the other way around. Have you checked to see if newer versions of the incompatible software exist? I would be willing to wager that they have been updated to accommodate the newer MySQL versions. -- Jerry dovecot.u...@seibercom.net Disclaimer: off-list followups get on-list replies or get ignored. Please do not ignore the Reply-To header. __ There's a fine line between courage and foolishness. Too bad it's not a fence.
Re: [Dovecot] quota dict with mysql 4.0
On 2010-03-25 6:26 AM, Jerry wrote: In my opinion, continued use of depreciated software peeve-mode It is 'deprecated', not 'depreciated'... /peeve-mode -- Best regards, Charles
Re: [Dovecot] quota dict with mysql 4.0
Charles Marcus wrote: In my opinion, continued use of depreciated software peeve-mode It is 'deprecated', not 'depreciated'... /peeve-mode Perhaps he's found a way to depreciate free software? -- Regards, Daryl Tester Member of the Amalgamated Australian Association Against Apostrophe Abuse. (formerly the 6A's - no, wait ...).
Re: [Dovecot] quota dict with mysql 4.0
Hello, I am somewhat surprised that the older software is not compatible with the newer version of MySQL. Usually, it is the other way around. Have you checked to see if newer versions of the incompatible software exist? I would be willing to wager that they have been updated to accommodate the newer MySQL versions. Syntax changed a litte bit and also there are some new protected words which are used by older software in sql without backticks etc. I agree updating would be the better way but we talking about a big number of servers and customers still using it. this is not changed over night I looked into dovecot-1.2.11/src/lib-dict/dict-sql.c, there seems to be a check for ON DUPLICATE KEY UPDATE on line 588. I recompiled dovecot on mysql 4.0 machine and tried again but there was no change. SQL is successful when dataset in quotatable is existing. in this case dovecot triggers an update of the data. But if no line for user is existing in quotatable and an insert is needed there is still the error: sql dict: commit failed: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'ON DUPLICATE KEY UPDATE bytes='12525'' at line why is the mysql 4.0-recompiled dovecot thinking my mysql is able to do ON DUPLICATE KEY UPDATE? How to avoid this? Thanks, Andre
Re: [Dovecot] quota dict with mysql 4.0
On Thu, 25 Mar 2010 12:46:10 +0100, Andre Hübner andre.hueb...@gmx.de articulated: Hello, I am somewhat surprised that the older software is not compatible with the newer version of MySQL. Usually, it is the other way around. Have you checked to see if newer versions of the incompatible software exist? I would be willing to wager that they have been updated to accommodate the newer MySQL versions. Syntax changed a litte bit and also there are some new protected words which are used by older software in sql without backticks etc. I agree updating would be the better way but we talking about a big number of servers and customers still using it. this is not changed over night I looked into dovecot-1.2.11/src/lib-dict/dict-sql.c, there seems to be a check for ON DUPLICATE KEY UPDATE on line 588. I recompiled dovecot on mysql 4.0 machine and tried again but there was no change. SQL is successful when dataset in quotatable is existing. in this case dovecot triggers an update of the data. But if no line for user is existing in quotatable and an insert is needed there is still the error: sql dict: commit failed: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'ON DUPLICATE KEY UPDATE bytes='12525'' at line why is the mysql 4.0-recompiled dovecot thinking my mysql is able to do ON DUPLICATE KEY UPDATE? How to avoid this? I feel your pain. We have experienced problems in the past with similar situations. Perhaps you can implement some updating procedure that would only involve a percentage of your users at one time. We have had situations where we updated an idle server and then switched users over to it. Usually, although not always, it worked seamlessly. Unfortunately, there does come a time when you just have to bit the bullet http://www.phrases.org.uk/meanings/65400.html. I don't want the cliché police on my trail. Good luck, you will need it. -- Jerry dovecot.u...@seibercom.net Disclaimer: off-list followups get on-list replies or get ignored. Please do not ignore the Reply-To header. __ If you put it off long enough, it might go away.
Re: [Dovecot] quota dict with mysql 4.0
(sorry, i only read the digest version of list and always create i new thread by fake-Re) Hello, Perhaps you can implement some updating procedure that would only involve a percentage of your users at one time. no way, i want to fix this! but i need help because i have no idea of c-programming. i can read the code a little bit, but never lerned it. solution is to edit dovecot-1.2.11/src/lib-dict/dict-sql.c i can avoid using ON DUPLICATE KEY UPDATE by changing line 91 to: orig: dict-has_on_duplicate_key = strcmp(driver-name, mysql) == 0; changed: dict-has_on_duplicate_key = strcmp(driver-name, mysqlXXX) == 0; this works only 50%. if a user has no entry in quotatable, dovecot is updating his quota with 2 queries: INSERT INTO quota (bytes,username) VALUES ('2456','myuser') INSERT INTO quota (messages,username) VALUES ('2','myuser') The first query works, the second throws an error because username-field is uniqe. Duplicate entry 'myuser' for key 1 So i have to find a way to make this query in one step: INSERT INTO quota (bytes,messages, username) VALUES ('2456','2','myuser') or turn the second insert into an update update quota SET messages='2' where username='myuser' But iam afraid this is too much for my c-skills. I need help of an C-Crack or timo as developer. if timo could create a switch for mysql 4.0 would surely be the best. Thanks, Andre
[Dovecot] quota dict
Hello, i'm using dict quota stored in mysql (/etc/dovecot/dovecot-dict-quota.conf): connect = host=localhost dbname=vmail user=dovecot password= # 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 username_field = username value_field = bytes } map { pattern = priv/quota/messages table = quota username_field = username value_field = messages } --- from dovecot.conf (quota rule is get from passdb sql + prefetch): dict { quotadict = mysql:/etc/dovecot/dovecot-dict-quota.conf } plugin { quota = dict:user::proxy::quotadict quota_warning = storage=99%% /etc/dovecot/quota-warning.sh 99 quota_warning2 = storage=95%% /etc/dovecot/quota-warning.sh 95 quota_warning3 = storage=80%% /etc/dovecot/quota-warning.sh 80 But how to add another quota for Trash folder ? It seeems that will be needed another table something like quota2 but how to map it using map {} -- Lampa
Re: [Dovecot] quota dict
On 11/22/2009 11:36 AM Lampa wrote: Hello, … plugin { quota = dict:user::proxy::quotadict quota_warning = storage=99%% /etc/dovecot/quota-warning.sh 99 quota_warning2 = storage=95%% /etc/dovecot/quota-warning.sh 95 quota_warning3 = storage=80%% /etc/dovecot/quota-warning.sh 80 But how to add another quota for Trash folder ? It seeems that will be needed another table something like quota2 but how to map it using map {} No, one table for quota values is sufficient. All you need in addition are one or more quota rules: http://wiki.dovecot.org/Quota/1.1#Quota_rules Regards, Pascal -- The trapper recommends today: beeffeed.0932...@localdomain.org
Re: [Dovecot] quota dict
Hello, hmm, if you mean: quota2_rule = Trash:ignore that seems not working. I'm copying message between Trash and inbox, both contains one message with size 1m5, quota_rule = *:storage=2M:messages=1 (from userdb) now is not possible to copy message from Trash to inbox and vice versa. 2009/11/22 Pascal Volk user+dove...@localhost.localdomain.org: On 11/22/2009 11:36 AM Lampa wrote: Hello, … plugin { quota = dict:user::proxy::quotadict quota_warning = storage=99%% /etc/dovecot/quota-warning.sh 99 quota_warning2 = storage=95%% /etc/dovecot/quota-warning.sh 95 quota_warning3 = storage=80%% /etc/dovecot/quota-warning.sh 80 But how to add another quota for Trash folder ? It seeems that will be needed another table something like quota2 but how to map it using map {} No, one table for quota values is sufficient. All you need in addition are one or more quota rules: http://wiki.dovecot.org/Quota/1.1#Quota_rules Regards, Pascal -- The trapper recommends today: beeffeed.0932...@localdomain.org -- Lampa
Re: [Dovecot] quota dict
On 11/22/2009 11:56 AM Lampa wrote: Hello, hmm, if you mean: quota2_rule = Trash:ignore that seems not working. I'm copying message between Trash and inbox, both contains one message with size 1m5, quota_rule = *:storage=2M:messages=1 (from userdb) now is not possible to copy message from Trash to inbox and vice versa. Please stop top posting. Make sure your client executes the EXPUNGE command, after you've marked the message in the INBOX as deleted. When your Trash folder is a subfolder of your INBOX, use: quota2_rule = INBOX.Trash:ignore Regards, Pascal -- The trapper recommends today: beeffeed.0932...@localdomain.org
Re: [Dovecot] quota dict
On 11/22/2009 12:03 PM Pascal Volk wrote: On 11/22/2009 11:56 AM Lampa wrote: Hello, hmm, if you mean: quota2_rule = Trash:ignore ^ quota2_rule = INBOX.Trash:ignore ^ Use quota_ruleN: quota_rule2 = Trash:ignore Regards, Pascal -- The trapper recommends today: decade.0932...@localdomain.org
[Dovecot] Quota/Dict Postgres Trigger
I posted the trigger for v1.1 versions of Dovecot on the Wiki (http://wiki.dovecot.org/Quota/Dict ) and while I understand the comment posted about two process inserting at the same time, I'm not sure I understand how this is fixed in v1.2 other than via the revised trigger in the Wiki. Does someone have a known working trigger that will handle a double insert correctly? Also, out of curiosity why wasn't the code for dict written to do an update first and then an insert if this failed? That would eliminate the need for this trigger. Thanks. -Warren
Re: [Dovecot] Quota/Dict Postgres Trigger
On Apr 29, 2009, at 3:25 AM, Warren Volz wrote: I posted the trigger for v1.1 versions of Dovecot on the Wiki (http://wiki.dovecot.org/Quota/Dict ) and while I understand the comment posted about two process inserting at the same time, I'm not sure I understand how this is fixed in v1.2 other than via the revised trigger in the Wiki. Does someone have a known working trigger that will handle a double insert correctly? I don't think it's possible until PostgreSQL supports INSERT .. ON DUPLICATE KEY UPDATE .. like MySQL. Kind of annoying, I like PostgreSQL but this feature is really missing from it. Also, out of curiosity why wasn't the code for dict written to do an update first and then an insert if this failed? That would eliminate the need for this trigger. There would still be a race condition. It's still possible that two processes do the steps at the exact same time and still finally find out that one of the INSERTs fail because they did everything at the same time. You could do similar EXCEPTION WHEN unique_violation THEN .. check with v1.1 too, although that still breaks in the very rare (or practically non-existent) condition if you happen to delete the row while another process is inserting. Or something like that. Anyway the v1.0/v1.1 code was mainly written for MySQL and then I realized PostgreSQL requires some more kludgeing to get it working properly in all situations and added that code to v1.2.
Re: [Dovecot] Quota/Dict Postgres Trigger
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Wed, Apr 29, 2009 at 03:38:27AM -0400, Timo Sirainen wrote: On Apr 29, 2009, at 3:25 AM, Warren Volz wrote: I posted the trigger for v1.1 versions of Dovecot on the Wiki (http://wiki.dovecot.org/Quota/Dict) and while I understand the comment posted about two process inserting at the same time, I'm not sure I understand how this is fixed in v1.2 other than via the revised trigger in the Wiki. Does someone have a known working trigger that will handle a double insert correctly? I don't think it's possible until PostgreSQL supports INSERT .. ON DUPLICATE KEY UPDATE .. like MySQL. Kind of annoying, I like PostgreSQL but this feature is really missing from it. FWIW, this seems to be the canonical way of dealing with that in PostgreSQL: http://www.postgresql.org/docs/current/static/plpgsql-control-structures.html#PLPGSQL-UPSERT-EXAMPLE Also, out of curiosity why wasn't the code for dict written to do an update first and then an insert if this failed? That would eliminate the need for this trigger. There would still be a race condition. It's still possible that two processes do the steps at the exact same time and still finally find out that one of the INSERTs fail because they did everything at the same time. [...] Right. The trick seems to be to wrap the thing in one plpgsql function (which wraps the try-to-update-then-insert into one transaction), so the client doesn't see anything of that. The race condition is taken care of via the implicit (sub-) transaction in the BEGIN...EXCEPTION block, AFAIU. Regards - -- tomás -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFJ+B4FBcgs9XrR2kYRAlCrAJ9lAa/ZIvav/I66MhMRQzRzuTdI3wCfeaNq KFa8JvnNFQIo6OxfTDCo+2c= =U4BP -END PGP SIGNATURE-
[Dovecot] quota - dict - username = '(null)'
I am trying setting up at virtual domain with quota using postfix and dovecut. But I can't get the quota to work, every time I try sending a mail the following queries is run on the database. SELECT home, uid, gid FROM mail_view_tmp WHERE account = 'dummy' AND domain = 'example.com' SELECT current FROM mail_quota WHERE path = 'quota/storage' AND username = '(null)' BEGIN;INSERT INTO mail_quota (current, path, username) VALUES ('quota/ storage', 262, '(null)') ON DUPLICATE KEY UPDATE current = current + 262;COMMIT The mail is delivered, but the quota for the user is not updated. Instead I just have an other new row for the user (null). I am using the package dovecot-1.0.rc2p3-mysql on OpenBSD 4.0 for macppc My configuration: (posting the full content of alt the config files seemed unnecessary so I cut out the important bits) /etc/postfix/main.cf ... virtual_transport = dovecot /etc/postfix/master.cf : ... dovecot unix - n n - - pipe flags=DRhu user=vmail argv=/usr/local/libexec/dovecot/deliver -d $ {recipient} /etc/dovecot.conf protocol lda { mail_plugins = quota } .. auth default { user = root passdb sql { args = /etc/dovecot-mysql.conf } userdb static { args = uid=2000 gid=2000 home=/var/vmail/%d/%n/ } socket listen { master { path = /var/run/dovecot/auth-master mode = 0600 user = vmail group = vmail } } } dict { quota = mysql:/etc/dovecot-dict-quota.conf } plugin { quota = dict:storage=10240:messages=1000 proxy::quota } /etc/dovecot-mysql.conf: driver = mysql connect = host=localhost dbname=ingoBase user=dovecot password=dovecot default_pass_scheme = CRYPT password_query = SELECT password FROM mail_view_dovecot WHERE account = '%n' AND domain = '%d' user_query = SELECT home, uid, gid FROM mail_view_tmp WHERE account = '%n' AND domain = '%d' or account = '%u' /etc/dovecot-dict-quota.conf connect = host=localhost dbname=ingoBase user=dovecot password=dovecot table = mail_quota select_field = current where_field = path username_field = username Is there something wrong in my configuration or is this a bug in the version i am using ? Thanks in advance ! Andreas