[Dovecot] problem mysql and dovecot 1.2
Hi everybody ,
Since my version 1.2. I have a problem with the management of quotas.
select * from virtual_users;
+----+-----------+----------------------------------+---------------------------+----------+----------------+
| id | domain_id | password | email
| quota_kb | quota_messages |
+----+-----------+----------------------------------+---------------------------+----------+----------------+
| 1 | 1 | xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx | xxxxxx@domain.com
| 10000000 | 0 |
..................
Before my time in 1.2. I did this for the quota management: user_query = SELECT CONCAT('/var/vmail/',CONCAT(SUBSTRING_INDEX(email,'@',-1),'/',SUBSTRING_INDEX(email,'@',1))) AS home, 5000 AS uid, 5000 AS gid, CONCAT('maildir:storage=',quota_kb,':messages=',quota_messages) AS quota FROM virtual_users WHERE email='%u';
My configuration
dovecot -n # 1.2.11: /etc/dovecot/dovecot.conf # OS: Linux 2.6.30-2-686 i686 Debian squeeze/sid ext3 log_path: /var/vmail/dovecot-deliver.log info_log_path: /var/vmail/dovecot-deliver.log log_timestamp: %Y-%m-%d %H:%M:%S protocols: imaps pop3s managesieve ssl_cert_file: /etc/ssl/certs/popimap.crt ssl_key_file: /etc/ssl/private/popimap.key verbose_ssl: yes login_dir: /var/run/dovecot/login login_executable(default): /usr/lib/dovecot/imap-login login_executable(imap): /usr/lib/dovecot/imap-login login_executable(pop3): /usr/lib/dovecot/pop3-login login_executable(managesieve): /usr/lib/dovecot/managesieve-login mail_max_userip_connections(default): 10 mail_max_userip_connections(imap): 10 mail_max_userip_connections(pop3): 3 mail_max_userip_connections(managesieve): 10 mail_location: maildir:/var/vmail/%d/%n/Maildir mail_debug: yes mbox_write_locks: fcntl dotlock mail_executable(default): /usr/lib/dovecot/imap mail_executable(imap): /usr/lib/dovecot/imap mail_executable(pop3): /usr/lib/dovecot/pop3 mail_executable(managesieve): /usr/lib/dovecot/managesieve mail_plugins(default): quota imap_quota mail_plugins(imap): quota imap_quota mail_plugins(pop3): quota mail_plugins(managesieve): mail_plugin_dir(default): /usr/lib/dovecot/modules/imap mail_plugin_dir(imap): /usr/lib/dovecot/modules/imap mail_plugin_dir(pop3): /usr/lib/dovecot/modules/pop3 mail_plugin_dir(managesieve): /usr/lib/dovecot/modules/managesieve namespace: type: private separator: . prefix: INBOX. inbox: yes list: yes subscriptions: yes lda: postmaster_address: postmaster@mydomain.com mail_plugins: quota sieve auth_socket_path: /var/run/dovecot/auth-master auth default: mechanisms: plain login passdb: driver: sql args: /etc/dovecot/dovecot-sql.conf userdb: driver: static args: uid=5000 gid=5000 home=/var/vmail/%d/%n allow_all_users=yes socket: type: listen client: path: /var/spool/postfix/private/auth mode: 432 user: postfix group: postfix master: path: /var/run/dovecot/auth-master mode: 384 user: vmail plugin: sieve: ~/.dovecot.sieve sieve_dir: ~/sieve quota: dict:user::proxy::quotadict quota_rule: *:storage=100M quota_warning: storage=95%% /usr/local/bin/quota-warning.sh 95 dict: quotadict: mysql:/etc/dovecot/dovecot-dict-sql.conf
my configuration dovecot-sql.conf cat dovecot-sql.conf #protocols = imap imaps pop3 pop3s # Database driver: mysql, pgsql, sqlite driver = mysql
connect = host=127.0.0.1 dbname=mailserver user=mailsuer password=***** default_pass_scheme = PLAIN-MD5
password_query = SELECT email,password FROM virtual_users WHERE email='%u';
#user_query = SELECT home, uid, gid, concat('*:storage=', quota_bytes, 'B') AS quota_rule FROM virtual_users WHERE userid = '%u' #user_query = SELECT maildir, 5000 AS uid, 5000 AS gid, CONCAT('*:storage=', quota_bytes, 'B') AS quota_rule FROM virtual_users WHERE userid = '%u' #user_query = SELECT maildir AS home , 5000 AS uid, 5000 AS gid, CONCAT('*:storage=',quota, 'B') AS quota_rule FROM virtual_users WHERE username = '%u' #user_query = SELECT maildir AS home, 5000 AS uid, 5000 AS gid, CONCAT('*:bytes=', CAST(quota AS CHAR)) AS quota_rule FROM quota WHERE username = '%u' AND active = '1
I used all these configurations but none work.
yet but the quotas work through this option: quota_rule: *:storage=100M The problem is that quotas are not personalized.
I'm looking for a way to use quotas, but using my mysql database. I galley for some time. So if someone has an idea I am willing
yet but the quotas work through this option: quota_rule: *:storage=100M The problem is that quotas are not personalized.
try using 'quota_rule2' in dovecot.conf and 'quota_rule' in user_query.
On Thu, 22 Jul 2010 16:51:03 +0200, Simone Caruso info@simonecaruso.com wrote: the problem using the quotas_rules I no customization.(If I'm wrong, he must tell me) What I have liked is a few things like:
select * from virtual_users;
+----+-----------+----------------------------------+---------------------------+----------+----------------+
| id | domain_id | password | email
| quota_kb | quota_messages |
+----+-----------+----------------------------------+---------------------------+----------+----------------+
| 1 | 1 | xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx | bla1@domain.com
| 10 | 0 |
| 3 | 1 | yyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy | bla@domain.com
| 20 | 0 |
With v1.1 quotas were stored in a database. Because I want to again. It is possible ?
ps (i dont use cast in sql query) I confess I do not know too syntax mysql I used it there in the wiki:)
On Thu, 2010-07-22 at 17:19 +0200, debian@robertain.com wrote:
yet but the quotas work through this option: quota_rule: *:storage=100M The problem is that quotas are not personalized.
try using 'quota_rule2' in dovecot.conf and 'quota_rule' in user_query.
On Thu, 22 Jul 2010 16:51:03 +0200, Simone Caruso info@simonecaruso.com wrote: the problem using the quotas_rules I no customization.(If I'm wrong, he must tell me)
The problem is that you need to return "quota_rule" from your sql, not "quota". The "quota" worked with v1.0, but not with v1.1+. See http://wiki.dovecot.org/Quota/1.1 for examples.
Timo Sirainen wrote:
On Thu, 2010-07-22 at 17:19 +0200, debian@robertain.com wrote:
On Thu, 22 Jul 2010 16:51:03 +0200, Simone Caruso info@simonecaruso.com wrote:
yet but the quotas work through this option: quota_rule: *:storage=100M The problem is that quotas are not personalized.
try using 'quota_rule2' in dovecot.conf and 'quota_rule' in user_query.
the problem using the quotas_rules I no customization.(If I'm wrong, he must tell me)
The problem is that you need to return "quota_rule" from your sql, not "quota". The "quota" worked with v1.0, but not with v1.1+. See http://wiki.dovecot.org/Quota/1.1 for examples.
Ok i look this page and i try various configuration.
// TO be sure i made queries on mysql
mysql> SELECT concat('*:storage=', quota_kb, 'k') AS quota_rule FROM virtual_users where email='bla@domain.com'; +---------------+ | quota_rule | +---------------+ | *:storage=20k | +---------------+ 1 row in set (0.00 sec)
mysql> select * from virtual_users where email='bla@domain.com'; +----+-----------+----------------------------------+-----------------------+----------+----------------+ | id | domain_id | password | email | quota_kb | quota_messages | +----+-----------+----------------------------------+-----------------------+----------+----------------+ | 1 | 1 | xxxxxxxxxxxxxxxxxxxxxxxx | bla@domain.com | 20 | 0 | +----+-----------+----------------------------------+-----------------------+----------+----------------+ 1 row in set (0.01 sec)
I add this line in dovecot-sql.conf user_query = SELECT concat('*:storage=', quota_kb, 'K') AS quota_rule FROM virtual_users WHERE email = '%u'
I don't change dovecot.conf file.
I spent the quota value has 20K in mysql voluntarily on behalf 'bla@domain.com' . But unfortunately I always reload after a 100M quota. Are you a idea ?
PS ( if i do SELECT home , uid, gid concat('*:storage=', quota_kb, 'K') AS quota_rule FROM virtual_users WHERE email = '%u ) it doesn't work too .
Best Regards , Mickael
On 22.7.2010, at 22.20, mickael wrote:
I add this line in dovecot-sql.conf user_query = SELECT concat('*:storage=', quota_kb, 'K') AS quota_rule FROM virtual_users WHERE email = '%u' .. I spent the quota value has 20K in mysql voluntarily on behalf 'bla@domain.com' . But unfortunately I always reload after a 100M quota. Are you a idea ?
That's because you're using userdb static instead of userdb sql:
userdb: driver: static args: uid=5000 gid=5000 home=/var/vmail/%d/%n allow_all_users=yes
Timo Sirainen wrote:
On 22.7.2010, at 22.20, mickael wrote:
I add this line in dovecot-sql.conf user_query = SELECT concat('*:storage=', quota_kb, 'K') AS quota_rule FROM virtual_users WHERE email = '%u'
..
I spent the quota value has 20K in mysql voluntarily on behalf 'bla@domain.com' . But unfortunately I always reload after a 100M quota. Are you a idea ?
That's because you're using userdb static instead of userdb sql:
Was exactly that.
userdb: driver: static args: uid=5000 gid=5000 home=/var/vmail/%d/%n allow_all_users=yes
Thank it's work now.
participants (4)
-
debian＠robertain.com
-
mickael
-
Simone Caruso
-
Timo Sirainen