no login with MySQL

[Andreas Meyer]

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