The email has been received locally, but I couldn't read it through POP3 / imap.

[Ewbo_King]

Maillog doesn't show any errors. I see a new email in my / home / mail / domain / user / new. I just can't read the email by using the client or telnet 110 / 143. Dovecot info The log doesn't seem to show any errors. I don't know what to do. Everything shows that there seems to be no problem.dovecot.conf have some configurations：
mail_location = maildir:/home/mail/%u/
userdb {
driver = sql
args = /etc/dovecot/dovecot-sql.conf
}
dovecot-sql.conf have some configurations：
user_query = SELECT '/home/mail/' || maildir AS home, 200 as uid, 12 as gid FROM mailbox WHERE username = '%u' AND active = '1'
Other configurations should be irrelevant.Can anyone help me, because all the information seems to indicate that they are normal, but I can't get the expected results.Thanks.

[Simon Hobson]

In your mailbox table, what is in the maildir column ? And what is passed to Dovecot as %u ?
I'm not using Dovecot so don't know what the config should look like, but the line starting mail_location looks wrong - I'd expect it to either be a root for the mail store (i.e. without the %u) or not present altogether. Your sql there gets the full path of the mailbox, and I wonder if Dovecot is trying to prepend mail_location to it ?
In my Courier config, it's got a similar construct to get the full path of the mailbox: "MYSQL_MAILDIR_FIELD concat('/var/spool/mail/virtual','/',maildir)"

[Ewbo_King]

Thanks for your help！In my mailbox table, some users‘ maildir like this：domain.com/www8/.This is the storage path of its local mail. and in postfix main.cf ：virtual_mailbox_base = /home/mail . So ，the mail directory of all users is /home/mail/domain.com/user/ . %u means full username. Mail_location tell Dovecot where to find the mails, and mail User Databases (userdb) overrides mail_location setting.If I try to read the email, there are some information that might be helpful：

Apr 01 08:34:39 auth: Debug: auth client connected (pid=1134)
Apr 01 08:34:39 auth: Debug: client in: AUTH    1   LOGIN   service=pop3    session=Uouh8YzbIlYOeHPy    lip=172.16.0.2  rip=14.120.115.242  lport=110   rport=22050
Apr 01 08:34:39 auth: Debug: client passdb out: CONT    1   VXNlcm5hbWU6
Apr 01 08:34:39 auth: Debug: client in: CONT    1   d3d3OEBibnJldml2ZS5jb20= (previous base64 data may contain sensitive data)
Apr 01 08:34:39 auth: Debug: client passdb out: CONT    1   UGFzc3dvcmQ6
Apr 01 08:34:39 auth: Debug: client in: CONT    1   d3d3MjFjbmNvbQ== (previous base64 data may contain sensitive data)
Apr 01 08:34:39 auth: Debug: sql(www8@domain.com,14.120.115.242,<Uouh8YzbIlYOeHPy>): query: SELECT username AS user,password FROM mailbox WHERE username = 'www8@domain.com' AND active='1'
Apr 01 08:34:39 auth: Debug: client passdb out: OK  1   user=www8@domain.com    
Apr 01 08:34:39 auth: Debug: master in: REQUEST 558235649   1134    1   a4ce81ea1a5d752720dc51499056b39e    session_pid=1135
Apr 01 08:34:39 auth: Debug: sql(www8@domain.com,14.120.115.242,<Uouh8YzbIlYOeHPy>): SELECT '/home/mail/' || maildir AS home, 200 as uid, 12 as gid FROM mailbox WHERE username = 'www8@bnrevive.com' AND active = '1'
Apr 01 08:34:39 auth: Debug: master userdb out: USER    558235649   www8@domain.com home=/home/mail/bnrevive.com/www8/  uid=200 gid=12  auth_mech=LOGIN
Apr 01 08:34:39 pop3-login: Info: Login: user=<www8@domain.com>, method=LOGIN, rip=14.120.115.242, lip=172.16.0.2, mpid=1135, session=<Uouh8YzbIlYOeHPy>

It seems to have got the right mailbox location.

[Rhys McWilliams]

I'm running dovecot and have the following in dovecot-sql.conf
user_query = SELECT CONCAT('/var/spool/mail/virtmail/', maildir) AS home, 5000 AS uid, 5000 AS gid, CONCAT('*:bytes=', quota) AS quota_rule FROM mailbox WHERE username = '%u'

[Ewbo_King]

I've solved it. I just changed SELECT '/home/mail/' || maildir AS hometoSELECT '/home/mail/' || maildir AS mail，then I can receive mail. Its funny, because this is the configuration file of the official document.