thr3ads.net - dovecot - More information about - Dovecot 2.2.x quota mysql and dict [Oct 2015]

If this information is useful, please help other people find it:
Share via:

Simon

2015-Oct-06 22:34 UTC

More information about - Dovecot 2.2.x quota mysql and dict

I some add information about bug...


Dovecot 2.2.19 version on server FreeBSD 10.2 Release.
Config quota (dict + mysql)
Database create Postfixadmin 2.3.7

Old version Dovecot 2.2.13 use method UPDATE in MySQL base + dict
example mysql.log
-----------------
Connect   postfix at localhost on postfix
            134 Query     SELECT bytes FROM quota2 WHERE username 'user at
mydomain.lan'
            135 Query     SELECT bytes FROM quota2 WHERE username 'user at
mydomain.lan'
            135 Query     BEGIN
            135 Query     UPDATE quota2 SET
bytes=bytes+2570,messages=messages+1 WHERE username = 'user at
mydomain.lan'
            136 Connect   postfix at localhost on postfix
            136 Query     BEGIN
            136 Query     UPDATE domain SET quota=quota+2570 WHERE domain
'mydomain.lan'
            134 Query     BEGIN
            134 Query     UPDATE quota2 SET
bytes=bytes+1723,messages=messages+1 WHERE username = 'user at
mydomain.lan'
            137 Connect   postfix at localhost on postfix
            137 Query     BEGIN
            137 Query     UPDATE domain SET quota=quota+1723 WHERE domain
'mydomain.lan'
            136 Query     UPDATE quota2 SET messages=messages+1 WHERE
username = 'mydomain.lan'
            - - - 
            137 Query     COMMIT
151004  3:12:27   128 Quit
-----------------

# cat dovecot-2.2.13/src/lib-dict/dict-sql.c
=== some text delete ==
static const char *
sql_dict_update_query(const struct dict_sql_build_query *build)
{
      struct sql_dict *dict = build->dict;
      const struct dict_sql_build_query_field *fields;
      unsigned int i, field_count;
      string_t *query;

      i_assert(build->inc);

      fields = array_get(&build->fields, &field_count);
      i_assert(field_count > 0);

      query = t_str_new(64);
      str_printfa(query, "UPDATE %s SET ", fields[0].map->table);
#### this
in code, work excellent 
      for (i = 0; i < field_count; i++) {
              if (i > 0)
                      str_append_c(query, ',');
              str_printfa(query, "%s=%s",
fields[i].map->value_field,
                          fields[i].map->value_field);
              if (fields[i].value[0] != '-')
                      str_append_c(query, '+');
              str_append(query, fields[i].value);
      }

      sql_dict_where_build(dict, fields[0].map, build->extra_values,
                           build->key1, SQL_DICT_RECURSE_NONE, query);
      return str_c(query);
}

=== === ==

Latest version Dovecot 2.2.19 use method DELETE/INSERT in MySQL base + dict

            140 Connect   postfix at localhost on postfix
            140 Query     SELECT quota FROM domain WHERE domain
'mydomain.lan'
            140 Query     SELECT quota FROM domain WHERE domain
'mydomain.lan'
            140 Query     SELECT messages FROM quota2 WHERE username
'mydomain.lan'
            140 Query     BEGIN
            140 Query     DELETE FROM domain WHERE domain =
'mydomain.lan'
            140 Query     DELETE FROM quota2 WHERE username =
'mydomain.lan'
            140 Query     INSERT INTO domain (quota,domain) VALUES
('8581','mydomain.lan') ON DUPLICATE KEY UPDATE
quota='8581'
151004 15:18:45  140 Quit

# cat dovecot-2.2.19/src/lib-dict/dict-sql.c
=== some text delete ==
static void sql_dict_unset(struct dict_transaction_context *_ctx,
                         const char *key)
{
      struct sql_dict_transaction_context *ctx               (struct
sql_dict_transaction_context *)_ctx;
      struct sql_dict *dict = (struct sql_dict *)_ctx->dict;
      const struct dict_sql_map *map;
      ARRAY_TYPE(const_string) values;

      if (ctx->prev_inc_map != NULL)
              sql_dict_prev_inc_flush(ctx);

      map = sql_dict_find_map(dict, key, &values);
      if (map == NULL) {
              i_error("sql dict unset: Invalid/unmapped key: %s",
key);
              ctx->failed = TRUE;
              return;
      }

      T_BEGIN {
              string_t *query = t_str_new(256);
              const char *error;

              str_printfa(query, "DELETE FROM %s", map->table);  
#### here
deleted all information about domain, this code i think need change
              if (sql_dict_where_build(dict, map, &values, key[0],
                                       SQL_DICT_RECURSE_NONE, query, &error)
< 0) {
                      i_error("dict-sql: Failed to delete %s: %s",
key,
error);
                      ctx->failed = TRUE;
              } else {
                      sql_update(ctx->sql_ctx, str_c(query));
              }
      } T_END;
}
=== === ==


Now dic + mysql doesn?t work in Dovecot 2.2.19, because first DELETE
operator delete information from base about my domain mydomain.lan  

Thanks!

Simon. 



--
View this message in context:
http://dovecot.2317879.n4.nabble.com/More-information-about-Dovecot-2-2-x-quota-mysql-and-dict-tp54077.html
Sent from the Dovecot mailing list archive at Nabble.com.

Simon

2015-Oct-14 22:51 UTC

head link

More information about - Dovecot 2.2.x quota mysql and dict

I am sorry... but may be already somebody solved this bug ?



--
View this message in context:
http://dovecot.2317879.n4.nabble.com/More-information-about-Dovecot-2-2-x-quota-mysql-and-dict-tp54077p54152.html
Sent from the Dovecot mailing list archive at Nabble.com.

Timo Sirainen

2015-Oct-17 09:41 UTC

head link

More information about - Dovecot 2.2.x quota mysql and dict

On 07 Oct 2015, at 01:34, Simon <semfly at gmail.com>
wrote:> 
> I some add information about bug...
> 
> 
> Dovecot 2.2.19 version on server FreeBSD 10.2 Release.
> Config quota (dict + mysql)
> Database create Postfixadmin 2.3.7
> 
> Old version Dovecot 2.2.13 use method UPDATE in MySQL base + dict
> example mysql.log
> -----------------
> Connect   postfix at localhost on postfix
>            134 Query     SELECT bytes FROM quota2 WHERE username >
'user at mydomain.lan'
>            135 Query     SELECT bytes FROM quota2 WHERE username >
'user at mydomain.lan'
>            135 Query     BEGIN
>            135 Query     UPDATE quota2 SET
> bytes=bytes+2570,messages=messages+1 WHERE username = 'user at
mydomain.lan'
Here's it's updating quota with user at mydomain.lan
>            140 Query     SELECT messages FROM quota2 WHERE username >
'mydomain.lan'
>            140 Query     BEGIN
>            140 Query     DELETE FROM domain WHERE domain =
'mydomain.lan'
>            140 Query     DELETE FROM quota2 WHERE username =
'mydomain.lan'
>            140 Query     INSERT INTO domain (quota,domain) VALUES
> ('8581','mydomain.lan') ON DUPLICATE KEY UPDATE
quota='8581'
> 151004 15:18:45  140 Quit
Here it's only mydomain.lan. So something's not right.
> Now dic + mysql doesn?t work in Dovecot 2.2.19, because first DELETE
> operator delete information from base about my domain mydomain.lan  
I can't reproduce this. What's your doveconf -n output? And what's
in the dovecot-dict-sql.conf.ext?

Simon

2015-Oct-17 10:29 UTC

head link

More information about - Dovecot 2.2.x quota mysql and dict

Thanks for your answer, Timo! :)

I send to you my configoration for reproducing.
Please, follow the link, because configuration is a very big, there will be
more easy to read and understand
My configuration Dovecot 2.2.19 <http://www.heypasteit.com/clip/28CW>  

Below the same my configuration Dovecot 2.2.19 here.
BEGIN of configuration
---------------------

# doveconf -n

# 2.2.19: /usr/local/etc/dovecot/dovecot.conf
# OS: FreeBSD 10.2-RELEASE amd64  ufs
auth_debug = yes
auth_debug_passwords = yes
auth_mechanisms = plain login cram-md5
auth_verbose = yes
base_dir = /var/run/dovecot/

first_valid_gid = 1983
first_valid_uid = 1983
listen = *
log_path = /var/log/dovecot.log
mail_debug = yes
mail_gid = virtual
mail_location = maildir:/var/spool/mail/%d/%u
mail_uid = virtual

passdb {
  args = /usr/local/etc/dovecot/dovecot-sql.conf
  driver = sql
}

dict {
  sqluserquota = mysql:/usr/local/etc/dovecot/dovecot-dict-sql-user.conf
  sqldomainquota = mysql:/usr/local/etc/dovecot/dovecot-dict-sql-domain.conf
}

plugin {
  quota = dict:domain_quota:%d:proxy::sqldomainquota
  quota2 = dict:user_quota::proxy::sqluserquota
  quota2_rule2 = Trash:storage=+20%%
  quota2_rule3 = Junk:storage=+20%%
  quota2_grace = 10%%
  quota2_warning = storage=100%% quota-exceeded 100 %u
  quota2_warning2 = storage=95%% quota-warning 95 %u
  quota2_warning3 = storage=90%% quota-warning 90 %u
  quota2_warning4 = storage=75%% quota-warning 75 %u
}

service quota-warning {
  executable = script /usr/local/etc/dovecot/quota_warning.sh
  unix_listener quota-warning {
    mode = 0660
    user = virtual
  }
}

protocols = imap pop3
service auth {
  unix_listener /var/spool/postfix/private/auth {
    group = postfix
    mode = 0660
    user = postfix
  }
  unix_listener auth-master {
    group = virtual
    mode = 0660
    user = virtual
  }
  unix_listener auth-userdb {
    group = virtual
    mode = 0660
    user = virtual
  }
}
service dict {
  unix_listener dict {
    mode = 0660
    user = virtual
  }
}

service imap-login {
  client_limit = 256
  inet_listener imap {
    address = *
    port = 143
  }
  inet_listener imaps {
    address = *
    port = 993
  }
  process_limit = 64
  process_min_avail = 8
  service_count = 1
}

service pop3-login {
  inet_listener pop3 {
    address = *
    port = 110
  }
  inet_listener pop3s {
    address = *
    port = 995
  }
}

ssl_cert = </usr/local/etc/postfix/cert.pem
ssl_key = </usr/local/etc/postfix/key.pem
ssl_parameters_regenerate = 1 hours
ssl_require_crl = no
userdb {
  args = /usr/local/etc/dovecot/dovecot-sql.conf
  driver = sql
}

verbose_proctitle = yes
verbose_ssl = yes
protocol imap {
  imap_client_workarounds = delay-newmail tb-lsub-flags tb-extra-mailbox-sep
  imap_idle_notify_interval = 2 mins
  mail_max_userip_connections = 10
  mail_plugin_dir = /usr/local/lib/dovecot
  mail_plugins = " quota imap_quota"
  quota_full_tempfail = yes
}

protocol pop3 {
  pop3_client_workarounds = outlook-no-nuls oe-ns-eoh
  pop3_uidl_format = %08Xu%08Xv
}

protocol lda {
  auth_socket_path = /var/run/dovecot/auth-master
  hostname = mydomain.lan
  lda_mailbox_autocreate = yes
  lda_mailbox_autosubscribe = yes
  lda_original_recipient_header   mail_plugins = " quota"
  postmaster_address = postmaster at mydomain.lan
}


--------------------------
-= END of dovecot.conf  =-
-------------------------------------------------------------------------


# cat dovecot-sql.conf | grep -v ^# | grep -v ^$

driver = mysql
connect = host=localhost dbname=postfix user=postfix password=mypassword
default_pass_scheme = CRAM-MD5

password_query = SELECT username, domain, password \
FROM mailbox WHERE username = '%u' and domain = '%d' and
active='1' \

user_query = SELECT '/var/spool/mail/%d/%u' AS home, \
'maildir:/var/spool/mail/%d/%u' AS mail, 1983 AS uid, 1983 AS gid, \
CONCAT('*:bytes=', domain.maxquota*1048576) AS quota_rule, \
CONCAT('*:bytes=', mailbox.quota) AS quota2_rule \
FROM mailbox, domain WHERE username = '%u' AND mailbox.active =
'1' \
AND domain.domain = '%d' AND domain.active = '1'


-------------------------------------------------------------------------


# cat dovecot-dict-sql-domain.conf

connect = host=localhost dbname=postfix user=postfix password=mypassword
map {
    pattern = priv/quota/storage
    table = domain
    username_field = domain
    value_field = quota
}
map {
    pattern = priv/quota/messages
    table = quota2
    username_field = username
    value_field = messages
}


-------------------------------------------------------------------------


# cat dovecot-dict-sql-user.conf

connect = host=localhost dbname=postfix user=postfix password=mypassword
map {
  pattern = priv/quota/storage
  table = quota2
  username_field = username
  value_field = bytes
}
map {
  pattern = priv/quota/messages
  table = quota2
  username_field = username
  value_field = messages
}

-------------------------
-= END of configuration =-
-------------------------

If need anymore about configuration, please write me.
Today I can respond quickly.




--
View this message in context:
http://dovecot.2317879.n4.nabble.com/More-information-about-Dovecot-2-2-x-quota-mysql-and-dict-tp54077p54189.html
Sent from the Dovecot mailing list archive at Nabble.com.

Timo Sirainen

2015-Oct-19 10:31 UTC

head link

More information about - Dovecot 2.2.x quota mysql and dict

On 17 Oct 2015, at 12:41, Timo Sirainen <tss at iki.fi>
wrote:> 
>> Connect   postfix at localhost on postfix
>>           134 Query     SELECT bytes FROM quota2 WHERE username
>> 'user at mydomain.lan'
>>           135 Query     SELECT bytes FROM quota2 WHERE username
>> 'user at mydomain.lan'
>>           135 Query     BEGIN
>>           135 Query     UPDATE quota2 SET
>> bytes=bytes+2570,messages=messages+1 WHERE username = 'user at
mydomain.lan'
> 
> Here's it's updating quota with user at mydomain.lan
> 
>>           140 Query     SELECT messages FROM quota2 WHERE username
>> 'mydomain.lan'
>>           140 Query     BEGIN
>>           140 Query     DELETE FROM domain WHERE domain =
'mydomain.lan'
>>           140 Query     DELETE FROM quota2 WHERE username =
'mydomain.lan'
>>           140 Query     INSERT INTO domain (quota,domain) VALUES
>> ('8581','mydomain.lan') ON DUPLICATE KEY UPDATE
quota='8581'
>> 151004 15:18:45  140 Quit
> 
> Here it's only mydomain.lan. So something's not right.
After seeing your configs, this makes more sense now, although it's a bit
confusing. You're updating quota bytes to "domain" table, but
quota messages to "quota2" table where both domain and user quotas are
mixed.
>> Now dic + mysql doesn?t work in Dovecot 2.2.19, because first DELETE
>> operator delete information from base about my domain mydomain.lan  
> 
> I can't reproduce this. What's your doveconf -n output? And
what's in the dovecot-dict-sql.conf.ext?
I still couldn't reproduce this with your exact config. I think that there
is no bug or change in behavior in v2.2.19. There are some situations when
Dovecot wants to recalculate the quota (e.g. a message is expunged but its size
isn't known). In these situations Dovecot recalculates the quota and does a
DELETE + INSERT to dict. This code has been there since the beginning. Maybe you
just happened to be finally triggering this situation. So it's not safe to
place the quota updates to a table that has other information also. Also this
means that the domain-quota doesn't work 100% in all situations and I guess
some kind of a daily recalculation for them would be a good idea..

Reasonably Related Threads

Search for more apparently analagous threads

dovecot - Oct 2015 - More information about - Dovecot 2.2.x quota mysql and dict

More information about - Dovecot 2.2.x quota mysql and dict

More information about - Dovecot 2.2.x quota mysql and dict

More information about - Dovecot 2.2.x quota mysql and dict

More information about - Dovecot 2.2.x quota mysql and dict

More information about - Dovecot 2.2.x quota mysql and dict

Reasonably Related Threads