[Dovecot] Quota with dict backend - sql does not get registered

[David Jonas]

On Mon, 2006-07-31 at 22:43 +0300, Timo Sirainen wrote:
> After I just yesterday added Berkeley DB support for lib-dict, I thought
> maybe everyone should just use dict via the proxy. The dict server is
> linked against all the necessary libraries, and it's probably faster to
> make it go through dict server anyway. Otherwise it gets a bit difficult
> to figure out how the linking should work..

Cool, thanks Tim. I tried the proxy before but was getting funny
results. So now I've focused my efforts on working those out. I've come
across some interesting stuff.

First, it appears that the order of fields/values in the SQL insert
queries in src/lib-dict/dict-sql.c are out of order. It seemed to me a
confirmed error upon evaluating the ON DUPLICATE KEY UPDATE portion of
the query, not just me messing up the config. Diff is at the bottom. I'd
make a formal patch for you, but there is some other hoky things going
on.

Here's my logs. I'm running down these bugs now:

Jul 31 15:56:06 uniroyal dovecot: dict: sql dict: commit failed: Not
connected to database
Jul 31 15:56:06 uniroyal dovecot: IMAP(djonas@): dict_quota: Couldn't
update quota
Jul 31 15:56:06 uniroyal dovecot: dict: sql dict: commit failed: Not
connected to database
Jul 31 15:56:06 uniroyal dovecot: IMAP(djonas@): dict_quota: Couldn't
update quota
Jul 31 15:56:06 uniroyal dovecot: IMAP(djonas@): Sending log messages
too fast, throttling..
Jul 31 15:56:06 uniroyal dovecot: dict: sql dict: commit failed: Not
connected to database
Jul 31 15:56:07 uniroyal dovecot: IMAP(djonas@): dict_quota: Couldn't
update quota
Jul 31 15:56:08 uniroyal dovecot: IMAP(djonas@): dict_quota: Couldn't
update quota
Jul 31 15:56:09 uniroyal dovecot: IMAP(djonas@): dict_quota: Couldn't
update quota

Even though dict says the commit failed, Not Connected, the db gets
updated. Why there are three "Couldn't update quota" messages as well I
have not discovered.


Also, as a side note, I changed my mail_log_prefix to "%Us(%u@%d): " yet
I don't ever see the domain portion. This also happens in the quota
plugin. The user gets set to just the part before the @. 


Thanks for the help.


---- Reorder SQL arguments diff ----

--- dict-sql.c.orig     2006-07-31 16:01:45.000000000 -0700
+++ dict-sql.c  2006-07-31 16:07:12.000000000 -0700
@@ -310,8 +310,8 @@
                        "ON DUPLICATE KEY UPDATE %s = '%s'",
                        dict->table, dict->select_field,
dict->where_field,
                        dict->username_field,
-                       sql_escape_string(dict->db, key),
                        sql_escape_string(dict->db, value),
+                       sql_escape_string(dict->db, key),
                        sql_escape_string(dict->db, dict->username),
                        dict->select_field,
                        sql_escape_string(dict->db, value));
@@ -320,8 +320,8 @@
                        "INSERT INTO %s (%s, %s) VALUES ('%s', '%s') "
                        "ON DUPLICATE KEY UPDATE %s = '%s'",
                        dict->table, dict->select_field,
dict->where_field,
-                       sql_escape_string(dict->db, key),
                        sql_escape_string(dict->db, value),
+                       sql_escape_string(dict->db, key),
                         dict->select_field,
                        sql_escape_string(dict->db, value));
        }
@@ -344,19 +344,19 @@
        t_push();
        if (priv) {
                query = t_strdup_printf(
-                       "INSERT INTO %s (%s, %s, %s) VALUES ('%s', %lld,
'%s') "
+                       "INSERT INTO %s (%s, %s, %s) VALUES (%lld, '%s',
'%s') "
                        "ON DUPLICATE KEY UPDATE %s = %s + %lld",
                        dict->table, dict->select_field,
dict->where_field,
-                       dict->username_field,
-                        sql_escape_string(dict->db, key), diff,
+                       dict->username_field, diff,
+                        sql_escape_string(dict->db, key),
                        sql_escape_string(dict->db, dict->username),
                         dict->select_field, dict->select_field, diff);
        } else {
                query = t_strdup_printf(
-                       "INSERT INTO %s (%s, %s) VALUES (%s, %lld) "
+                       "INSERT INTO %s (%s, %s) VALUES (%lld, '%s') "
                        "ON DUPLICATE KEY UPDATE %s = %s + %lld",
-                       dict->table, dict->select_field,
dict->where_field,
-                        sql_escape_string(dict->db, key), diff,
+                       dict->table, dict->select_field,
dict->where_field, diff,
+                        sql_escape_string(dict->db, key),
                         dict->select_field, dict->select_field, diff);
        }
        sql_update(ctx->sql_ctx, query);