On 2015-09-13 21:05, Andreas Meyer wrote:

Hello!

Christian Kivalo ml+dovecot@valo.at schrieb am 13.09.15 um 19:51:38 Uhr:

...

...

Sep 13 11:55:49 imap(a.meyer@nimmini.de): Error: user a.meyer@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@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@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@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@kivalo.at): Debug: Namespace inbox: type=private, prefix=, sep=/, inbox=yes, hidden=no, list=yes, subscriptions=yes location=maildir:/srv/mail/aaa@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@nimmini.de whereas the location looks like /var/spool/vhosts/nimmini.de/a.meyer

Don't know what to do.

Regards

Andreas

• christian

On 2015-09-13 22:52, Andreas Meyer wrote:

Christian Kivalo ml+dovecot@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@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

Benny Pedersen me@junc.eu schrieb am 14.09.15 um 01:17:17 Uhr:

Andreas Meyer skrev den 2015-09-13 22:52:

...

Don't understand it.

in mysql shell you self need to expand %u

since there is possible no user email that is %u

Yes, of course. Too much stress today.

But I solved my problems with mysql, auth, dovecot_lda and postfix deferred mail by just setting mail_home = /var/spool/vhosts/%d/%n in dovecot.conf again.

And the old queries work again. Now is bedtime. Thank you everybody!

Andreas