no login with MySQL

[Christian Kivalo]

On 2015-09-13 21:05, Andreas Meyer wrote:
> Hello!
> 
> Christian Kivalo <ml+dovecot at valo.at> schrieb am 13.09.15 um 19:51:38 
> Uhr:
> 
>> > Sep 13 11:55:49 imap(a.meyer at nimmini.de): Error: user
>> > a.meyer at nimmini.de: Initialization failed: Namespace '': Home
>> > directory not set for user. Can't expand ~/ for mail root dir in: ~/
>> > Sep 13 11:55:49 imap(a.meyer at nimmini.de): Error: Invalid user
>> > settings. Refer to server log for more information.
>> 
>> i see an error here "Error: Invalid user settings" relating to the 
>> users
>> mail_location direcory.
>> 
>> Is "mail_location" (and not so important "mail_home") defined?
> 
> In dovecot.conf mail_location = maildir:~/ is set. This works with
> the system-users defined in the passwd file.
> 
> 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'

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.

> There is no mail_loation defined. I thought if maildir is queried, the 
> %u
> would expand to the mail_location of the user. Where the users only 
> with
> the local part of the address exist in the filesystem.

I don't know a setting called "maildir" dovecot expects a field "mail" 
to be returned by your sql query or uses the default one (set in 
10-mail.conf).

maybe even this works, but it too will only return the path without the 
username
user_query = SELECT maildir AS mail, uid, gid, concat('*:storage=', 
quota) AS quota_rule, concat('Trash:storage=+', quota_trash) AS 
quota_rule2 FROM mailbox WHERE username = '%u'

%u is the user at domain username
%n is the username without the @domain part

> /var/spool/vhosts/nimmini.de/a.meyer
> 
> whereas /var/spool/vhosts is a symlink to /home/vhosts.
> 
> This setup worked fine sometime ago and I wanted to reactivate MySQL
> for dovecot. Now the query doesn't work anymore, the location seems
> to be invalid.
> 
> location=maildir:~/
> instead of
> /var/spool/vhosts/nimmini.de/%u.
> 
>> > Sep 13 11:55:49 imap(a.meyer at nimmini.de): Debug: Namespace inbox:
>> > type=private, prefix=, sep=/, inbox=yes, hidden=no, list=yes,
>> > subscriptions=yes location=maildir:~/
>> 
>> on one of my accounts this same line looks like this:
>> imap(aaa at kivalo.at): Debug: Namespace inbox: type=private, prefix=,
>> sep=/, inbox=yes, hidden=no, list=yes, subscriptions=yes
>> location=maildir:/srv/mail/aaa at kivalo.at/Maildir:LAYOUT=fs
>> 
>> where location is the "mail_location" setting from 10-mail.conf and is
>> overridden by the userdb from sql for every user.
>> 
>> it's probably enough to set the option mail_location in 10-mail.conf 
>> to
>> an absolute path.
> 
> I cannot set the absolute path in mail_location, because I have
> systemusers and virtual users.
> 
> Don't know how to set mail_location in the query-string for MySQL,
> if the expandation of %u is not enough. %u would expand to the full
> address a.meyer at nimmini.de whereas the location looks like
> /var/spool/vhosts/nimmini.de/a.meyer

> Don't know what to do.
> 
> Regards
> 
>   Andreas

- christian