On 2015-09-13 22:52, Andreas Meyer wrote:> Christian Kivalo <ml+dovecot at valo.at> schrieb am 13.09.15 um
22:08:04 Uhr:
>
>>> I think the query-string f?r MySQL would overwrite this
mail_location,
>>> right? But how do I define it in the query?
>> yes thats how it should work but your query (looking at the query
result
>> from your first email) overwrites the mail_location with
>> "/var/spool/vhosts/nimmini.de/" without a username.
>>
>>>> With my setup "mail" and "home" is returned
from the userdb query from
>>>> mysql
>>>>
>>>> home: /srv/mail/%u
>>>> mail: /srv/mail/%u/Maildir
>>> The query-strings for virtual users look like this:
>>>
>>> password_query = SELECT username AS username, password FROM mailbox
>>> WHERE username = '%u' AND active = 1
>>> user_query = SELECT maildir, uid, gid, concat('*:storage=',
quota) AS
>>> quota_rule, concat('Trash:storage=+', quota_trash) AS
quota_rule2 FROM
>>> mailbox WHERE username = '%u'
>> what about
>> user_query = SELECT CONCAT(maildir,
SUBSTRING_INDEX(username,'@',+1)) as
>> mail, uid, gid, concat('*:storage=', quota) AS quota_rule,
>> concat('Trash:storage=+', quota_trash) AS quota_rule2 FROM
mailbox WHERE
>> username = '%u'
> With this query above I can login again with Roundcube and claws-mail but
> with the mysql-client I get:
>
> mysql> SELECT CONCAT(maildir, SUBSTRING_INDEX(username,'@',+1))
as mail, uid, gid, concat('*:storage=', quota) AS quota_rule,
concat('Trash:storage=+', quota_trash) AS quota_rule2 FROM mailbox WHERE
username = '%u';
> Empty set (0,00 sec)
>
> This is my old query with wich I couldn't log in.
> mysql> SELECT maildir, uid, gid, concat('*:storage=', quota) AS
quota_rule, concat('Trash:storage=+', quota_trash) AS quota_rule2 FROM
mailbox WHERE username = '%u';
> Empty set (0,00 sec)
>
>> The SUBSTRING_INDEX returns everything left of the '@' from
field
>> username, that should give you something like
>>
>>
+--------------------------------------+------+------+-------------------+--------------------+
>> | mail | uid | gid | quota_rule
>> | quota_rule2 |
>>
+--------------------------------------+------+------+-------------------+--------------------+
>> | /var/spool/vhosts/nimmini.de/a.meyer | 5000 | 5000 |
*:storage=5242880
>> | Trash:storage=+100 |
>>
+--------------------------------------+------+------+-------------------+--------------------+
>>
>> which hopefully is the path to your maildir.
> I get am Emty set
>
> Don't understand it.
>
> Andreas
Hi Andreas,
Make sure to return a "home" attribute, and only optionally a
"mail"
attribute, from your SQL user query.
Your "home" attribute for user "a.meyer at nimmini.de"
should return
"/var/spool/vhosts/nimmini.de/a.meyer", and only if you want to
override
the global mail_location specify a "mail" attribute to return
something
like "maildir:/var/spool/vhosts/nimmini.de/a.meyer/Maildir". There is
no
"maildir" attribute used from the SQL query, to my knowledge.
If you want to have the maildir stored directly in the user's home
folder, e.g. "/var/spool/vhosts/nimmini.de/a.meyer", and your global
mail_location already is "maildir:~/", then you only need to return a
proper "home" attribute from SQL.
BR
Tobias