LinuxQuestions.org - qpopper mysql "sql query fetch row failed ()"

I have just installed qpopper with the mysql patch on my CentOS 5.5 server and am having a problem querying the database. I've had this working on various other linux servers without any problem. If I query the default database it works but if I query one of the virtual database it fails.

When I telnet to the default database it works:

Quote:

telnet localhost 110
popper[26256]: (v4.0.18-mysql-0.16) Servicing request from "server.domain.com" at 127.0.0.1+OK ready
User exampleuser
+OK Password required for exampleuser.
Pass examplepass
+OK exampleuser has 0 visible messages (0 hidden) in 0 octets

However, if I telnet to a virtual database it fails:

Quote:

telnet localhost 110
popper[26256]: (v4.0.18-mysql-0.16) Servicing request from "server.domain.com" at 127.0.0.1+OK ready
User exampleuser@differentdomain.com
<21>Sep 14 13:59:54 popper[25647]: exampleuse@differentdomain.com: ERROR: sql query fetch row failed (). User may not exist+OK Password required for exampleuse.
NOTE: Missing letter "r" at the end of UserID.

The query seems to chop off the last letter of the UserId when I use @domain.com

**********************************************

Additional information regarding the set up.

-Mysql 5.1.50 tarball

Quote:

./configure \
--prefix=/usr/local/mysql \
--disable-maintainer-mode \
--with-mysqld-user=mysql \
--with-unix-socket-path=/tmp/mysql.sock \
--without-comment \
--without-debug \
--without-bench

This is the same database that I've been using for years and earlier versions of qpopper have always worked.

I inserted a test user/domain.

Quote:

mysql> INSERT INTO radcheck (id, UserName, attribute, value, uid, gid,domain, alias, is_alias, net_id) VALUES ('999', 'exampleuser', '', 'test','500', '12', 'differentdomain.com', '', 'no', 'exampleuser');
Query OK, 1 row affected (0.03 sec)

When I log on to the database all looks fine:

Quote:

I granted mailadmin full permission.

Quote:

> grant all privileges on radius.* to 'mailadmin'@'localhost' identified by 'secret';

>flush privileges;

I download Qpopper qpopper4.0.19.tar.gz (this is the version mysql patch was written for) and the patch qpopper3.1.2-mysql-0.1.patch. I patched it (no errors) and compiled/made it (no errors).

Quote:

./configure \
--with-mysqlconfig=/etc/mysql-popper.conf \
--enable-mysql \
--with-mysqllibpath=/usr/local/mysql/lib/mysql \
--with-mysqlincludepath=/usr/local/mysql/include/mysql \
--enable-standalone \
--enable-shy \
--enable-servermode

This is a copy of mysql-popper.conf

Quote:

MysqlAuthHost localhost
MyslAuthPort 3306
MysqlAuthDb radius
MysqlAuthTable radcheck
MysqlAuthUsername mailadmin
MysqlAuthPassword secret
MysqlAuthUsernameField username
MysqlAuthPasswordField value
MysqlAuthDomainField domain
MysqlAuthAliasField alias
AliasField is_alias
MysqlAuthAliasNo no
MysqlAuthPasswordMethod both
MysqlAuthDefaultDomain domain.com
MysqlAuthUIDField uid
MysqlAuthGIDField gid
MysqlAuthUID mail
MysqlAuthGID mail
POP_MAILDIR /var/spool/virtual_mail

I recompiled Qpopper to run debug mode.

Quote:

./configure --with-mysqlconfig=/etc/mysql-popper.conf --enable-mysql --with-mysqllibpath=/usr/local/lib/mysql --with-mysqlincludepath=/usr/local/include/mysql/ --enable-standalone --enable-shy --enable-servermode --enable-debugging --enable-spool-dir=/var/spool/virtual_mail

This is the debug message:

Quote:

[root@server qpopper4.0.19]# telnet localhost 110
Trying 127.0.0.1...
Connected to localhost.
Escape character is '^]'.
<23>Oct 4 10:04:24 popper[25671]: Debugging turned on (-d) [pop_init.c:781]<23>Oct 4 10:04:24 popper[25671]: Will generate stats records (-s) [pop_init.c:916]<23>Oct 4 10:04:24 popper[25671]: (v4.0.19-mysql-0.16) Servicing request from"server.differentdomain.com" at 127.0.0.1 [pop_init.c:1260]<23>Oct 4 10:04:24 popper[25671]: before TLS; tls_support==0 [popper.c:226]<23>Oct 4 10:04:24 popper[25671]: Skipped TLS Init [popper.c:250]<23>Oct 4 10:04:24 popper[25671]:
(v4.0.19-mysql-0.16) Intro [popper.c:292]<23>Oct 4 10:04:24 popper[25671]: +OK ready [popper.c:300]+OK ready<23>Oct 4 10:04:24 popper[25671]: Qpopper ready for input from (null) at server.differentdomain.com [127.0.0.1] [popper.c:339]user exampleuser@differentdomain.com<23>Oct 4 10:04:40 popper[25671]: Received (21): "user exampleuser@differentdomain.com" [pop_get_command.c:105]<23>Oct 4 10:04:40 popper[25671]:
exampleuser@differentdomain.com: Got virtual domain "server.differentdomain.com" from user "exampleuser@differentdomain.com" [pop_user.c:276]<23>Oct 4 10:04:40 popper[25671]: exampleuse@differentdomain.com: Connected to localhost:3306 db radius as root for authentication [pop_user.c:313]<23>Oct 4 10:04:40 popper[25671]: exampleuse@differentdomain.com: Ran sql query "SELECT value FROM radcheck WHERE UserName= 'exampleuse' AND domain = 'server.differentdomain.com'" against db radius [pop_user.c:399]<23>Oct 4 10:04:40 popper[25671]: exampleuse@differentdomain.com: Successfully got sql result from db radius [pop_user.c:411]<21>Oct 4 10:04:40 popper[25671]: exampleuse@differentdomain.com: ERROR: sql query fetch row failed (). User may not exist [pop_user.c:415]<23>Oct 4 10:04:40 popper[25671]: +OK Password required for fre. [pop_user.c:850]+OK Password required for exampleuse.<23>Oct 4 10:04:40 popper[25671]: user returned 1; CurrentState now auth2 [popper.c:383]<23>Oct 4 10:04:40 popper[25671]: Qpopper ready for input from exampleuse at server.differentdomain.com [127.0.0.1] [popper.c:339]pass test<23>Oct 4 10:06:32 popper[25671]: Received: "pass xxxxxxxxx" [pop_get_command.c:96]<23>Oct 4 10:06:32 popper[25671]: User exampleuse not known by system [pop_pass.c:1215]<21>Oct 4 10:06:42 popper[25671]: exampleuse at server.differentdomain.com (127.0.0.1): -ERR [AUTH] Password supplied for "exampleuse" is incorrect. [pop_pass.c:1217]-ERR [AUTH] Password supplied for "exampleuse" is incorrect.<23>Oct 4 10:06:42 popper[25671]: pass returned 0; CurrentState now halt [popper.c:383]<23>Oct 4 10:06:42 popper[25671]: +OK Pop server at server.differentdomain.com signing off. [popper.c:405]+OK Pop server at server.differentdomain.com signing off.<23>Oct 4 10:06:42 popper[25671]: (v4.0.19-mysql-0.16) Ending request from "exampleuse" at (server.differentdomain.com) 127.0.0.1 [popper.c:423]Connection closed by foreign host.

Quote:

On a different note, I did find one mistake in the mysql-popper.com file, the "MysqlAuthUsernameField username" was all in lower case where as in the Mysql database it was "UserName". This actually solved the error "sql query fetch row failed ()" I changed the "U" and "N" to uppercase but the original problem of chopping the UserID still exists.

As I mentioned, it is chopping the last letter off the UserID, in this case, it is chopping the "r" off of exampleuser so it quarries "exampleuse", not "exampleuser" that it should. Any advice on how to solve this would be greatly appreciated.

All the best
Fred