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