[tpop3d-discuss] MySQL problems

Chris Elsworth chris at shagged.org
Sun, 25 Nov 2001 15:39:36 +0000


Hiya,

Just tinkering with 1.4.1pre1, and I'm experiencing strange 
intermittent MySQL failures.

One user can authenticate, but most others can't.

Here's a failed authentication (make your terminal wide) :)

connection_sendresponse: client [6]195.11.8.226/shagged.org: sent `+OK <5ce7c6479c371fdb5cdbd8ae8f0ea709@shagged.org>'
listeners_post_select: client [6]195.11.8.226/shagged.org: connected
connection_parsecommand: client [6]195.11.8.226/shagged.org: received `user test@dmeon.net'
connection_sendresponse: client [6]195.11.8.226/shagged.org: sent `+OK Tell me your password.'
connection_parsecommand: client [6]195.11.8.226/shagged.org: received `pass [...]'
auth_mysql_new_user_pass: SQL query: SELECT CONCAT(pop3_mbox.MailPath, '/', pop3_mbox.UserName), pop3_mbox.Password, pop3_mbox.UserName, 'bsd' FROM pop3_mbox,domain WHERE pop3_mbox.UserName = 'test' AND domain.DomainName = 'dmeon.net' AND pop3_mbox.DomainID = domain.DomainID
auth_mysql_new_user_pass: unix user `bsd' for test@dmeon.net does not make sense
connection_sendresponse: client [6]195.11.8.226/shagged.org: sent `-ERR Authentication failed.'
connection_do: client `[6]195.11.8.226/shagged.org': 1 authentication failures
connection_parsecommand: client [6]195.11.8.226/shagged.org: received `quit'
connection_sendresponse: client [6]195.11.8.226/shagged.org: sent `+OK Done.'
connections_post_select: client [6]195.11.8.226/shagged.org: disconnected

Note:
auth_mysql_new_user_pass: unix user `bsd' for test@dmeon.net does not make sense

This would lead me to believe that my MySQL query is wrong, but if thats
the case, why does it then work for this user:

connection_parsecommand: client [6]195.11.8.226/shagged.org: received `USER chris@black-sun.co.uk'
connection_sendresponse: client [6]195.11.8.226/shagged.org: sent `+OK Tell me your password.'
connection_parsecommand: client [6]195.11.8.226/shagged.org: received `PASS [...]'
auth_mysql_new_user_pass: SQL query: SELECT CONCAT(pop3_mbox.MailPath, '/', pop3_mbox.UserName), pop3_mbox.Password, pop3_mbox.UserName, 'bsd' FROM pop3_mbox,domain WHERE pop3_mbox.UserName = 'chris' AND domain.DomainName = 'black-sun.co.uk' AND pop3_mbox.DomainID = domain.DomainID
authcontext_new_user_pass: began session for `chris@black-sun.co.uk' with mysql; uid 1001, gid 6
fork_child: new child is PID 42059
mailspool_new_from_file: indexed mailspool /var/mail/black-sun.co.uk/chris (0 bytes) in 0.000s
connection_sendresponse: client [6]chris@black-sun.co.uk(195.11.8.226): sent `+OK Welcome aboard! You have no messages at all.'
fork_child: [6]chris@black-sun.co.uk(195.11.8.226): successfully authenticated with mysql
connection_parsecommand: client [6]chris@black-sun.co.uk(195.11.8.226): received `STAT'
connection_sendresponse: client [6]chris@black-sun.co.uk(195.11.8.226): sent `+OK 0 0'
connection_parsecommand: client [6]chris@black-sun.co.uk(195.11.8.226): received `QUIT'
connection_sendresponse: client [6]chris@black-sun.co.uk(195.11.8.226): sent `+OK Done'
connections_post_select: client [6]chris@black-sun.co.uk(195.11.8.226): disconnected
authcontext_delete: finished session for `chris@black-sun.co.uk' with mysql

Same query, same database, same permissions on all the files its using.
To enforce the point further, here's another user from the same domain
as the one above, black-sun.co.uk, failing to auth:

connection_sendresponse: client [6]195.149.46.61/shagged.org: sent `+OK <3d348fdeada808876317506ae9edff72@shagged.org>'
listeners_post_select: client [6]195.149.46.61/shagged.org: connected
connection_parsecommand: client [6]195.149.46.61/shagged.org: received `USER alex@black-sun.co.uk'
connection_sendresponse: client [6]195.149.46.61/shagged.org: sent `+OK Tell me your password.'
connection_parsecommand: client [6]195.149.46.61/shagged.org: received `PASS [...]'
auth_mysql_new_user_pass: SQL query: SELECT CONCAT(pop3_mbox.MailPath, '/', pop3_mbox.UserName), pop3_mbox.Password, pop3_mbox.UserName, 'bsd' FROM pop3_mbox,domain WHERE pop3_mbox.UserName = 'alex' AND domain.DomainName = 'black-sun.co.uk' AND pop3_mbox.DomainID = domain.DomainID
auth_mysql_new_user_pass: unix user `bsd' for alex@black-sun.co.uk does not make sense
connection_sendresponse: client [6]195.149.46.61/shagged.org: sent `-ERR Authentication failed.'
connection_do: client `[6]195.149.46.61/shagged.org': 1 authentication failures
connection_parsecommand: client [6]195.149.46.61/shagged.org: received `QUIT'
connection_sendresponse: client [6]195.149.46.61/shagged.org: sent `+OK Done.'
connections_post_select: client [6]195.149.46.61/shagged.org: disconnected


The query, though you can probably work it out from the above:

auth-mysql-pass-query: SELECT CONCAT(pop3_mbox.MailPath, '/', pop3_mbox.UserName), pop3_mbox.Password, pop3_mbox.UserName, 'bsd' FROM pop3_mbox,domain WHERE pop3_mbox.UserName = '$(local_part)' AND domain.DomainName = '$(domain)' AND pop3_mbox.DomainID = domain.DomainID

Which I understand should be right, looking at the source:

/* Default query templates. The returned fields are:
 *  [0] location of mailbox
 *  [1] password hash
 *  [2] unix user
 *  [3] mailbox type
 */

Relevant contents of spooldir:
drwxrwx---   2 mail     mail          512 Nov 25 15:32 .
-rw-rw----   1 mail     mail            0 Nov 25 13:05 alex
-rw-rw----   1 mail     mail            0 Nov 25 13:11 chris

Anyone got a clue cos I'm all out :)

Cheers,
-- 
Chris Elsworth  -  Software & Systems Developer  /  Systems Administrator