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.
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
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 QuitHere 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.lanI can't reproduce this. What's your doveconf -n output? And what's in the dovecot-dict-sql.conf.ext?
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
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..