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