You are viewing a plain text version of this content. The canonical link for it is here.
Posted to modules-dev@httpd.apache.org by Danie Qian <da...@bestningning.com> on 2007/07/06 21:07:30 UTC
mod_authn_dbd - internal server error after certain idle time
I am using mod_authn_dbd and mod_dbd to do authentication again a mysql
database. Basically it works but after sometime I will get a internal server
error. I checked the log this happens at apr_dbd_pvselect() line in
mod_authn_dbd.c. So this looks like to me that apr_dbd_pvselect() is trying
to re-use a connection that is closed by the mysql server side whose
wait-timeout is set to 1200 seconds. my mod_dbd setting is this
DBDMin 1
DBDKeep 2
DBDMax 50
DBDExptime 60
My question is why the keepalive setting for 60sec doesnt work for me? How
do I fix this without changing the timeout setting on the mysql server?
Thanks,
Daniel
Re: mod_authn_dbd - internal server error after certain idle time
Posted by Tom Donovan <do...@bellatlantic.net>.
Danie Qian wrote:
> I am using mod_authn_dbd and mod_dbd to do authentication again a mysql
> database. Basically it works but after sometime I will get a internal
> server error. I checked the log this happens at apr_dbd_pvselect() line
> in mod_authn_dbd.c. So this looks like to me that apr_dbd_pvselect() is
> trying to re-use a connection that is closed by the mysql server side
> whose wait-timeout is set to 1200 seconds. my mod_dbd setting is this
> DBDMin 1
> DBDKeep 2
> DBDMax 50
> DBDExptime 60
> My question is why the keepalive setting for 60sec doesnt work for me?
> How do I fix this without changing the timeout setting on the mysql server?
>
It looks like a higher priority is given to DBDKeep and DBDMin than to
expiring old connections. This would mean that 2 connections could be
retained past their deadline to ensure that DBDKeep connections remain
available.
ref: apr-util/misc/apr_reslist.c:169 function: reslist_maint()
in Apache 2.2.4 / APR 1.2.8
Perhaps setting both DBDKeep and DBDMin to zero would let all the
connections expire when they are idle for 60 seconds.
DBDKeep is required to be >= DBDMin.
-tom-
thread gets a SIGPIPE in mod_dbd.c on a terminated mysql connection
Posted by Danie Qian <da...@bestningning.com>.
In mod_dbd.c dbd_check() gets a SIGPIPE when the connection has been
shutdown because it is idle for too long. The SIGPIPE causes the thread to
terminate abruptly. Is mod_dbd.c or the underlying libraries supposed to
handle (or ignore) the SIGPIPE?
Re: mod_authn_dbd - internal server error after certain idle time
Posted by Nick Kew <ni...@webthing.com>.
On Sat, 7 Jul 2007 12:50:05 +0200
"Ben Noordhuis" <bn...@gmail.com> wrote:
> On 7/7/07, Ben Noordhuis <bn...@gmail.com> wrote:
> > I'm not aware of a reliable workaround for earlier versions of
> > MySQL, unfortunately.
>
> Allow me to reply to myself. If you can live with a slight performance
> impact, the code snippet below should help you to reconnect in a
> controlled fashion. Hope this helps.
That looks reasonable: a mysql_ping by hand. I might adopt it
if no better suggestion emerges.
--
Nick Kew
Application Development with Apache - the Apache Modules Book
http://www.apachetutor.org/
Re: mod_authn_dbd - internal server error after certain idle time
Posted by Ben Noordhuis <bn...@gmail.com>.
On 7/7/07, Ben Noordhuis <bn...@gmail.com> wrote:
> I'm not aware of a reliable workaround for earlier versions of
> MySQL, unfortunately.
Allow me to reply to myself. If you can live with a slight performance
impact, the code snippet below should help you to reconnect in a
controlled fashion. Hope this helps.
switch (mysql_query(&mysql, "SELECT 1")) {
case 0:
mysql_free_result(mysql_use_result(&mysql));
break;
case CR_SERVER_GONE_ERROR:
/* reinitialize */
break;
default:
/* handle error */
}
Re: mod_authn_dbd - internal server error after certain idle time
Posted by Ben Noordhuis <bn...@gmail.com>.
On 7/7/07, Nick Kew <ni...@webthing.com> wrote:
> Any other suggestions for a workaround to check the connection now?
With MySQL 5.0.3 and later, mysql_ping() doesn't automatically
reconnect but returns CR_SERVER_GONE_ERROR instead, so you'll want to
reinitialize the prepared statements when that happens. I'm not aware
of a reliable workaround for earlier versions of MySQL, unfortunately.
Re: mod_authn_dbd - internal server error after certain idle time
Posted by Nick Kew <ni...@webthing.com>.
On Sat, 07 Jul 2007 05:44:27 -0400
Tom Donovan <do...@bellatlantic.net> wrote:
> Nick Kew wrote:
> > On Fri, 06 Jul 2007 18:37:07 -0700
> > Matthew Kent <ma...@bravenet.com> wrote:
> >
> >> think its the mysql_ping reconnection clobbering the prepared
> >> statement
> >> used in mod_authn_dbd.
> >
> > Buggrit. I can't see a satisfactory workaround for that.
> > I guess the kludge suggested by Tom Donovan in this thread
> > could be the best practical solution.
> >
>
> Alas, after some testing - it seems even that kludge won't be 100%
> satisfactory.
Heh. Anyone here have the ear of the MySQL devs?
I've just fired off a question to MySQL:
To: mysql@lists.mysql.com
Subject: Tying actions to (re)opening a connection
The Apache DBD module maintains a dynamic pool of connections.
SQL statements may be defined at system startup. Such queries
are then treated as prepared statements. In the MySQL driver,
the sequence is:
mysql_real_connect
for each defined SQL statement {
mysql_stmt_init
mysql_stmt_prepare
}
So far, so good. However, we also need to deal with connections
that go stale. Our API defines a check_connection function.
mysql_ping is *nearly* right for this, but unfortunately leaves us
(silently) with a new connection without the prepared statements.
It would work perfectly if we could tie a callback to mysql_ping.
Something like:
int mysql_ping2(MYSQL *mysql,
int (*callback)(MYSQL*, void*),
void *dptr);
where the callback (if non-null) is invoked whenever mysql_ping
detects a stale connection and reconnects.
Is this something you'd consider adding? Any other suggestions
for a workaround to check the connection now?
--
Nick Kew
Application Development with Apache - the Apache Modules Book
http://www.apachetutor.org/
Re: mod_authn_dbd - internal server error after certain idle time
Posted by Tom Donovan <do...@bellatlantic.net>.
Nick Kew wrote:
> On Fri, 06 Jul 2007 18:37:07 -0700
> Matthew Kent <ma...@bravenet.com> wrote:
>
>> think its the mysql_ping reconnection clobbering the prepared
>> statement
>> used in mod_authn_dbd.
>
> Buggrit. I can't see a satisfactory workaround for that.
> I guess the kludge suggested by Tom Donovan in this thread
> could be the best practical solution.
>
Alas, after some testing - it seems even that kludge won't be 100%
satisfactory. The expired connections are removed (by the APR-util
reslist_maint() function) only when connections are checked back into
the pool.
Following a long period of inactivity, an expired connection must be
used (unsuccessfully) to get recognized as expired only when it is released.
I'll enter a new APR bug for this within a few days. The check for
expired reslist resources should happen as a resource is acquired - not
just when they are released.
I also think that removing expired resources should not be prevented by
the pool Min and Keep limits. Instead, new resources should be created
if necessary to satisfy these limits.
-tom-
Re: mod_authn_dbd - internal server error after certain idle time
Posted by Danie Qian <da...@bestningning.com>.
----- Original Message -----
From: "Tom Donovan" <do...@bellatlantic.net>
To: <mo...@httpd.apache.org>
Sent: Monday, July 09, 2007 6:14 PM
Subject: Re: mod_authn_dbd - internal server error after certain idle time
> Danie Qian wrote:
>> Thank you for the kind words. I am a little overwhelmed by the complexity
>> of mod_dbd.c, especially its use of dbd_group_t. I couldnt find any notes
>> for it so I wish someone here could shed some light on it for me and
>> anyone interested.
>
> There do not seem to any group-related diretives for mod_authn_dbd, but it
> is pretty easy to put any group checks directly into the SQL.
>
> Here is an example, translated into MySQL SQL (I think. I didn't really
> test this with MySQL so I might have missed something...):
>
> DBDriver mysql
> DBDParams "...credentials..."
> DBDExptime 28800
> DBDKeep 0
> DBDMax 30
> DBDMin 0
>
> <Directory ...AP_Dir... >
> AuthType Basic
> AuthName "Accounts Payable"
> AuthBasicProvider dbd
> AuthDBDUserPWQuery \
> "SELECT CONCAT('{SHA}', A.pwhash) AS Password \
> FROM apacheusers A, apachegroups B \
> WHERE A.username = %s \
> AND CURRENT_DATE < A.Expires \
> AND B.username = A.username \
> AND B.apachegroup = 'AP';"
> Require valid-user
> </Directory>
>
> <Directory ...AR_Dir... >
> AuthType Basic
> AuthName "Accounts Receivable"
> AuthBasicProvider dbd
> AuthDBDUserPWQuery \
> "SELECT CONCAT('{SHA}', A.pwhash) AS Password \
> FROM apacheusers A, apachegroups B \
> WHERE A.username = %s \
> AND CURRENT_DATE < A.Expires \
> AND B.username = A.username \
> AND B.apachegroup = 'AR';"
> Require valid-user
> </Directory>
>
>
Thanks for replying, Tom. To understand what goes under the hood in
mod_dbd.c I think I will have to get some understanding of reslist and
resring in apr-util first which I am sure you have expert knowledge of.
Re: mod_authn_dbd - internal server error after certain idle time
Posted by Tom Donovan <do...@bellatlantic.net>.
Danie Qian wrote:
> Thank you for the kind words. I am a little overwhelmed by the
> complexity of mod_dbd.c, especially its use of dbd_group_t. I couldnt
> find any notes for it so I wish someone here could shed some light on it
> for me and anyone interested.
There do not seem to any group-related diretives for mod_authn_dbd, but
it is pretty easy to put any group checks directly into the SQL.
Here is an example, translated into MySQL SQL (I think. I didn't really
test this with MySQL so I might have missed something...):
DBDriver mysql
DBDParams "...credentials..."
DBDExptime 28800
DBDKeep 0
DBDMax 30
DBDMin 0
<Directory ...AP_Dir... >
AuthType Basic
AuthName "Accounts Payable"
AuthBasicProvider dbd
AuthDBDUserPWQuery \
"SELECT CONCAT('{SHA}', A.pwhash) AS Password \
FROM apacheusers A, apachegroups B \
WHERE A.username = %s \
AND CURRENT_DATE < A.Expires \
AND B.username = A.username \
AND B.apachegroup = 'AP';"
Require valid-user
</Directory>
<Directory ...AR_Dir... >
AuthType Basic
AuthName "Accounts Receivable"
AuthBasicProvider dbd
AuthDBDUserPWQuery \
"SELECT CONCAT('{SHA}', A.pwhash) AS Password \
FROM apacheusers A, apachegroups B \
WHERE A.username = %s \
AND CURRENT_DATE < A.Expires \
AND B.username = A.username \
AND B.apachegroup = 'AR';"
Require valid-user
</Directory>
Re: mod_authn_dbd - internal server error after certain idle time
Posted by Danie Qian <da...@bestningning.com>.
----- Original Message -----
From: "Tom Donovan" <do...@bellatlantic.net>
To: <mo...@httpd.apache.org>
Sent: Monday, July 09, 2007 11:29 AM
Subject: Re: mod_authn_dbd - internal server error after certain idle time
> Danie Qian wrote:
>>
>
>>>
>>> With this patch, setting DBDTimout lower than the MySQL system variable
>>> 'wait_timeout' will ensure than expired connections never get used.
>>>
>>
>> Assuming by DBDTimeout you mean DBDExptime in mod_dbd documentation
>> where it implies a keepalive value, how about making mod_dbd to send
>> something to mysql server so that its wait_timeout timer gets reset
>> whenever the keepalive time elapses? Is this what the setting originally
>> meant for?
>> As I am pretty new to this listing and I apologize if I am not on the
>> same page as you guys.
>>
>
> Yes, sorry - you are correct. I did mean DBDExptime. I slipped up
> translating the APR lingo to the mod_dbd lingo...
>
> re "not on the same page" - I didn't think that at all.
> If you're new to this, you sure catch on quick!
>
Thank you for the kind words. I am a little overwhelmed by the complexity of
mod_dbd.c, especially its use of dbd_group_t. I couldnt find any notes for
it so I wish someone here could shed some light on it for me and anyone
interested.
> The patch I proposed is not just for MySQL.
> I don't use MySQL myself (although I tested the patch with it anyway).
>
> The problem with "send something to mysql server" is that it is a
> MySQL-only solution to the problem. I was trying for a more general
> solution since this affects other databases too.
>
> As an aside - I have an apr_dbd_odbc driver:
> http://code.google.com/p/odbc-dbd/
> which I use with several different kinds of databases. This is what I
> used to test the patch.
>
> I don't know if the APR folks will like my proposed patch - they might
> not. You are welcome to use it, but you probably won't want to use it
> in production until they decide.
>
I am trying to get the whole thing to work for some server(farms) that will
eventually become very high traffic sites. We have been using the
third-party mod_auth_mysql.c for the last few years but decided to give a
try on these new modules. I will test your patch on a small server and
hopefully they will make the decision soon or come up with a better
solution.
Re: mod_authn_dbd - internal server error after certain idle time
Posted by Tom Donovan <do...@bellatlantic.net>.
Danie Qian wrote:
>
>>
>> With this patch, setting DBDTimout lower than the MySQL system variable
>> 'wait_timeout' will ensure than expired connections never get used.
>>
>
> Assuming by DBDTimeout you mean DBDExptime in mod_dbd documentation
> where it implies a keepalive value, how about making mod_dbd to send
> something to mysql server so that its wait_timeout timer gets reset
> whenever the keepalive time elapses? Is this what the setting originally
> meant for?
> As I am pretty new to this listing and I apologize if I am not on the
> same page as you guys.
>
Yes, sorry - you are correct. I did mean DBDExptime. I slipped up
translating the APR lingo to the mod_dbd lingo...
re "not on the same page" - I didn't think that at all.
If you're new to this, you sure catch on quick!
The patch I proposed is not just for MySQL.
I don't use MySQL myself (although I tested the patch with it anyway).
The problem with "send something to mysql server" is that it is a
MySQL-only solution to the problem. I was trying for a more general
solution since this affects other databases too.
As an aside - I have an apr_dbd_odbc driver:
http://code.google.com/p/odbc-dbd/
which I use with several different kinds of databases. This is what I
used to test the patch.
I don't know if the APR folks will like my proposed patch - they might
not. You are welcome to use it, but you probably won't want to use it
in production until they decide.
A better short-term MySQL-only solution would be a change to
apr_dbd_mysql like the one Nick Kew suggested.
-tom-
Re: mod_authn_dbd - internal server error after certain idle time
Posted by Danie Qian <da...@bestningning.com>.
----- Original Message -----
From: "Tom Donovan" <do...@bellatlantic.net>
To: <mo...@httpd.apache.org>
Sent: Monday, July 09, 2007 10:09 AM
Subject: Re: mod_authn_dbd - internal server error after certain idle time
> Danie Qian wrote:
>>
>>
>> It is working a little better now after I set both DBDKeep and DBDMin to
>> zero. But I am still getting the internal server error from time to
>> time. Users usually can get pass the error by reloading the same page.
>> Here is the error messages in the log:
>>
>> [Sat Jul 07 02:56:44 2007] [error] (20014)Internal error: DBD [mysql]
>> Error: Lost connection to MySQL server during query
>> [Sat Jul 07 02:56:44 2007] [error] [client 75.87.112.250]
>> authn_dbd_acquire: Error looking up josiahhansen in database
>>
>
> Unfortunately, DBD connection pooling (which is performed by the
> apr-util reslist facility) presumes that connections last longer than
> DBDTimeout, so just setting DBDKeep and DBDMin will not completely
> eliminate the problem.
>
> The proposed changes to the MySQL driver seem like a good idea and they
> may help - but they will only help MySQL, not any other databases.
>
> I entered a new APR bug and proposed a patch -
> http://issues.apache.org/bugzilla/show_bug.cgi?id=42841
> to change the interpretation of timeout so that it will work to avoid
> this error.
>
> With this patch, setting DBDTimout lower than the MySQL system variable
> 'wait_timeout' will ensure than expired connections never get used.
>
Assuming by DBDTimeout you mean DBDExptime in mod_dbd documentation where it
implies a keepalive value, how about making mod_dbd to send something to
mysql server so that its wait_timeout timer gets reset whenever the
keepalive time elapses? Is this what the setting originally meant for?
As I am pretty new to this listing and I apologize if I am not on the same
page as you guys.
Re: mod_authn_dbd - internal server error after certain idle time
Posted by Tom Donovan <do...@bellatlantic.net>.
Danie Qian wrote:
>
>
> It is working a little better now after I set both DBDKeep and DBDMin to
> zero. But I am still getting the internal server error from time to
> time. Users usually can get pass the error by reloading the same page.
> Here is the error messages in the log:
>
> [Sat Jul 07 02:56:44 2007] [error] (20014)Internal error: DBD [mysql]
> Error: Lost connection to MySQL server during query
> [Sat Jul 07 02:56:44 2007] [error] [client 75.87.112.250]
> authn_dbd_acquire: Error looking up josiahhansen in database
>
Unfortunately, DBD connection pooling (which is performed by the
apr-util reslist facility) presumes that connections last longer than
DBDTimeout, so just setting DBDKeep and DBDMin will not completely
eliminate the problem.
The proposed changes to the MySQL driver seem like a good idea and they
may help - but they will only help MySQL, not any other databases.
I entered a new APR bug and proposed a patch -
http://issues.apache.org/bugzilla/show_bug.cgi?id=42841
to change the interpretation of timeout so that it will work to avoid
this error.
With this patch, setting DBDTimout lower than the MySQL system variable
'wait_timeout' will ensure than expired connections never get used.
Likewise for Oracle 'IDLE_TIME', or IBM-DB2 'IDLE_TIMEOUT' - which are
similar to MySQL 'wait_timeout'.
In detail, here is how the patch would affect the behavior of mod_dbd
directives:
----------------------------------------------
The behavior of DBDMax and DBDMin are unchanged.
DBDMax is still the maximum number of connections - both in-use and idle.
DBDMin is still the minimum number of idle connections, except when this
would cause DBDMax to be exceeded because Min + in-use > Max)
Without the patch:
------------------
DBDKeep was the minimum number of idle connections which were retained
after being used one or more times.
DBDTimeout controlled how long idle connections in excess of DBDKeep
remained available.
All connections were presumed to last forever.
With the patch:
---------------
DBDKeep is now the maximum number of idle connections, not the minimum.
DBDTimeout is now the maximum lifetime of a connection. A connection
which has been idle longer than DBDTimeout will never be used.
The ramifications of the new algorithm are:
-------------------------------------------
There will never be more than DBDKeep idle connections, although there
still can be additional in-use connections (up to DBDMax total connections).
If there is a DBDTimeout, the number of idle connections will shrink as
they expire down to a minimum of DBDMin idle connections.
If there is no DBDTimeout, once the number of idle connections gets up
to DBDKeep it will stay there indefinitely.
A possible weakness of the new algorithm is that DBDMin doesn't (and
never did) add any benefit when the connection pool is inactive enough
to let connections expire. There is still nothing which spontaneously
recognizes and closes expired connections during periods of no activity.
The replacement of an expired connection only happens when the expired
connection is about to be used for a database query. Since this
weakness applies to inactive systems, it is not likely to be much of a
problem.
----------------------------------------------
-tom-
Re: mod_authn_dbd - internal server error after certain idle time
Posted by Danie Qian <da...@bestningning.com>.
----- Original Message -----
From: "Nick Kew" <ni...@webthing.com>
To: <mo...@httpd.apache.org>
Sent: Saturday, July 07, 2007 4:38 AM
Subject: Re: mod_authn_dbd - internal server error after certain idle time
> On Fri, 06 Jul 2007 18:37:07 -0700
> Matthew Kent <ma...@bravenet.com> wrote:
>
>> After some testing I think I better understand the issue, updated it
>> here
>>
>> http://issues.apache.org/bugzilla/show_bug.cgi?id=39329#c11
>
> Thanks. I saw that already (notification email reached me ahead of
> this one).
>
>> think its the mysql_ping reconnection clobbering the prepared
>> statement
>> used in mod_authn_dbd.
>
> Buggrit. I can't see a satisfactory workaround for that.
> I guess the kludge suggested by Tom Donovan in this thread
> could be the best practical solution.
>
It is working a little better now after I set both DBDKeep and DBDMin to
zero. But I am still getting the internal server error from time to time.
Users usually can get pass the error by reloading the same page. Here is the
error messages in the log:
[Sat Jul 07 02:56:44 2007] [error] (20014)Internal error: DBD [mysql] Error:
Lost connection to MySQL server during query
[Sat Jul 07 02:56:44 2007] [error] [client 75.87.112.250] authn_dbd_acquire:
Error looking up josiahhansen in database
Re: mod_authn_dbd - internal server error after certain idle time
Posted by Nick Kew <ni...@webthing.com>.
On Fri, 06 Jul 2007 18:37:07 -0700
Matthew Kent <ma...@bravenet.com> wrote:
> After some testing I think I better understand the issue, updated it
> here
>
> http://issues.apache.org/bugzilla/show_bug.cgi?id=39329#c11
Thanks. I saw that already (notification email reached me ahead of
this one).
> think its the mysql_ping reconnection clobbering the prepared
> statement
> used in mod_authn_dbd.
Buggrit. I can't see a satisfactory workaround for that.
I guess the kludge suggested by Tom Donovan in this thread
could be the best practical solution.
--
Nick Kew
Application Development with Apache - the Apache Modules Book
http://www.apachetutor.org/
Re: mod_authn_dbd - internal server error after certain idle time
Posted by Danie Qian <da...@bestningning.com>.
----- Original Message -----
From: "Matthew Kent" <ma...@bravenet.com>
To: <mo...@httpd.apache.org>
Sent: Friday, July 06, 2007 9:37 PM
Subject: Re: mod_authn_dbd - internal server error after certain idle time
> On Fri, 2007-06-07 at 23:04 +0100, Nick Kew wrote:
>> On Fri, 06 Jul 2007 13:13:03 -0700
>> Matthew Kent <ma...@bravenet.com> wrote:
>>
>>
>> > > >> My question is why the keepalive setting for 60sec doesnt work
>> > > >> for me? How
>> > > >> do I fix this without changing the timeout setting on the mysql
>> > > >> server?
>>
>> For future reference, this should really have been on users@.
>> But you've had some good replies here.
>>
>> > > > I've haven't used mod_authn_dbd yet but this sounds similar to
>> > > > issues I was having
>> > > >
>> > > > http://issues.apache.org/bugzilla/show_bug.cgi?id=39329
>> > > >
>> > > > patch hasn't made its way into apr_dbd_mysql.c yet. Please add a
>> > > > note if the it fixes your issue.
>
> After some testing I think I better understand the issue, updated it
> here
>
> http://issues.apache.org/bugzilla/show_bug.cgi?id=39329#c11
>
> think its the mysql_ping reconnection clobbering the prepared statement
> used in mod_authn_dbd.
>
> Danie, which version of MySQL is your apr-util compiled against?
> --
Actually this is happenning on mysql-4.1.20-2.RHEL4.1 and another manually
compiled 5.0.41 mysql server, both
Re: mod_authn_dbd - internal server error after certain idle time
Posted by Danie Qian <da...@bestningning.com>.
----- Original Message -----
From: "Matthew Kent" <ma...@bravenet.com>
To: <mo...@httpd.apache.org>
Sent: Friday, July 06, 2007 9:37 PM
Subject: Re: mod_authn_dbd - internal server error after certain idle time
> On Fri, 2007-06-07 at 23:04 +0100, Nick Kew wrote:
>> On Fri, 06 Jul 2007 13:13:03 -0700
>> Matthew Kent <ma...@bravenet.com> wrote:
>>
>>
>> > > >> My question is why the keepalive setting for 60sec doesnt work
>> > > >> for me? How
>> > > >> do I fix this without changing the timeout setting on the mysql
>> > > >> server?
>>
>> For future reference, this should really have been on users@.
>> But you've had some good replies here.
>>
>> > > > I've haven't used mod_authn_dbd yet but this sounds similar to
>> > > > issues I was having
>> > > >
>> > > > http://issues.apache.org/bugzilla/show_bug.cgi?id=39329
>> > > >
>> > > > patch hasn't made its way into apr_dbd_mysql.c yet. Please add a
>> > > > note if the it fixes your issue.
>
> After some testing I think I better understand the issue, updated it
> here
>
> http://issues.apache.org/bugzilla/show_bug.cgi?id=39329#c11
>
> think its the mysql_ping reconnection clobbering the prepared statement
> used in mod_authn_dbd.
>
> Danie, which version of MySQL is your apr-util compiled against?
> --
mysql-4.1.20-2.RHEL4.1
mysql-devel-4.1.20-2.RHEL4.1
Re: mod_authn_dbd - internal server error after certain idle time
Posted by Matthew Kent <ma...@bravenet.com>.
On Fri, 2007-06-07 at 23:04 +0100, Nick Kew wrote:
> On Fri, 06 Jul 2007 13:13:03 -0700
> Matthew Kent <ma...@bravenet.com> wrote:
>
>
> > > >> My question is why the keepalive setting for 60sec doesnt work
> > > >> for me? How
> > > >> do I fix this without changing the timeout setting on the mysql
> > > >> server?
>
> For future reference, this should really have been on users@.
> But you've had some good replies here.
>
> > > > I've haven't used mod_authn_dbd yet but this sounds similar to
> > > > issues I was having
> > > >
> > > > http://issues.apache.org/bugzilla/show_bug.cgi?id=39329
> > > >
> > > > patch hasn't made its way into apr_dbd_mysql.c yet. Please add a
> > > > note if the it fixes your issue.
After some testing I think I better understand the issue, updated it
here
http://issues.apache.org/bugzilla/show_bug.cgi?id=39329#c11
think its the mysql_ping reconnection clobbering the prepared statement
used in mod_authn_dbd.
Danie, which version of MySQL is your apr-util compiled against?
--
Matthew Kent \ SA \ bravenet.com
Re: mod_authn_dbd - internal server error after certain idle time
Posted by Danie Qian <da...@bestningning.com>.
----- Original Message -----
From: "Nick Kew" <ni...@webthing.com>
To: <mo...@httpd.apache.org>
Sent: Friday, July 06, 2007 6:04 PM
Subject: Re: mod_authn_dbd - internal server error after certain idle time
> On Fri, 06 Jul 2007 13:13:03 -0700
> Matthew Kent <ma...@bravenet.com> wrote:
>
>
>> > >> My question is why the keepalive setting for 60sec doesnt work
>> > >> for me? How
>> > >> do I fix this without changing the timeout setting on the mysql
>> > >> server?
>
> For future reference, this should really have been on users@.
> But you've had some good replies here.
>
>> > > I've haven't used mod_authn_dbd yet but this sounds similar to
>> > > issues I was having
>> > >
>> > > http://issues.apache.org/bugzilla/show_bug.cgi?id=39329
>> > >
>> > > patch hasn't made its way into apr_dbd_mysql.c yet. Please add a
>> > > note if the it fixes your issue.
>
> That's a mod_dbd patch. The trunk version of mod_dbd should
> supersede it. I started reviewing the /trunk/, but got distracted
> and need to find time to finish that.
>
>> > I am using apache-2.2.4 configured with apr-1.2.9 and apr-1.2.8. I
>> > have the above mentioned problem even after I replaced mod_dbd.c
>> > with the one from yesterday's snapshot. Do you think this patch
>> > will still apply?
>
> Good question. If you figure it out, tell us. If not, please bug me
> to check it.
>
>> Well the patch is against apr_dbd_mysql.c (in apr-util) not mod_dbd.c
>> (in httpd) so I think it would apply in this case.
>
> Huh? Where's the apr_dbd_mysql patch? I see only (variants of a)
> mod_dbd patch under the bug report you referenced.
>
>> Incidentally here's my httpd.conf ifmodule block.
>
> Why are you using IfModule?
>
>> I've been using
>> mod_dbd + apr-util with apr_dbd_mysql and the linked patch in
>> production since it came out without issue.
>
> Can you enumerate exactly what variants of mod_dbd, apr_dbd_mysql
> and any other DBD modules you're using, and what patches (bearing
> in mind there are several variants on the patch on that bug report,
> and yours seems to be marked obsolete). The more clarity we can get,
> the more likely it is we can apply the right patches.
>
I am using mod_authn_dbd,mod_auth_basic from apache-2.2.4 and snapshot
mod_dbd.c dated July 5.
apr_dbd_mysql.c was downloaded from http://apache.webthing.com/database/
yesterday too.
No patches have been applied so far as I dont know which one to apply if
there is one.
Re: mod_authn_dbd - internal server error after certain idle time
Posted by Nick Kew <ni...@webthing.com>.
On Fri, 06 Jul 2007 13:13:03 -0700
Matthew Kent <ma...@bravenet.com> wrote:
> > >> My question is why the keepalive setting for 60sec doesnt work
> > >> for me? How
> > >> do I fix this without changing the timeout setting on the mysql
> > >> server?
For future reference, this should really have been on users@.
But you've had some good replies here.
> > > I've haven't used mod_authn_dbd yet but this sounds similar to
> > > issues I was having
> > >
> > > http://issues.apache.org/bugzilla/show_bug.cgi?id=39329
> > >
> > > patch hasn't made its way into apr_dbd_mysql.c yet. Please add a
> > > note if the it fixes your issue.
That's a mod_dbd patch. The trunk version of mod_dbd should
supersede it. I started reviewing the /trunk/, but got distracted
and need to find time to finish that.
> > I am using apache-2.2.4 configured with apr-1.2.9 and apr-1.2.8. I
> > have the above mentioned problem even after I replaced mod_dbd.c
> > with the one from yesterday's snapshot. Do you think this patch
> > will still apply?
Good question. If you figure it out, tell us. If not, please bug me
to check it.
> Well the patch is against apr_dbd_mysql.c (in apr-util) not mod_dbd.c
> (in httpd) so I think it would apply in this case.
Huh? Where's the apr_dbd_mysql patch? I see only (variants of a)
mod_dbd patch under the bug report you referenced.
> Incidentally here's my httpd.conf ifmodule block.
Why are you using IfModule?
> I've been using
> mod_dbd + apr-util with apr_dbd_mysql and the linked patch in
> production since it came out without issue.
Can you enumerate exactly what variants of mod_dbd, apr_dbd_mysql
and any other DBD modules you're using, and what patches (bearing
in mind there are several variants on the patch on that bug report,
and yours seems to be marked obsolete). The more clarity we can get,
the more likely it is we can apply the right patches.
--
Nick Kew
Application Development with Apache - the Apache Modules Book
http://www.apachetutor.org/
Re: mod_authn_dbd - internal server error after certain idle time
Posted by Mark Constable <ma...@renta.net>.
On Saturday 07 July 2007 07:49:13 Danie Qian wrote:
> Apparently it hasnt been fixed as I AM using the mod_dbd.c file from trunk
> and still having this problem. but it did fix some problems in dbd_acquire()
> I had before I replaced the mod_dbd.c that comes with apache-2.2.4. I am
> surprised that it seems no one is using these new modules that are supposed
> to be better than mod_auth_mysql.c.
One contributing factor is that there is a libapache2-mod-auth-mysql
for Debian but no equivalent apr_dbd_mysql_driver in apr-util. This
sad situation will probably remain until the major distro's pick it
up. I'd say it's at least another year before RH/centos/cpanel based
hosting providers even start to use it. IMVHO.
--markc
Re: mod_authn_dbd - internal server error after certain idle time
Posted by Danie Qian <da...@bestningning.com>.
----- Original Message -----
From: "Matthew Kent" <ma...@bravenet.com>
To: <mo...@httpd.apache.org>
Sent: Friday, July 06, 2007 5:21 PM
Subject: Re: mod_authn_dbd - internal server error after certain idle time
> On Fri, 2007-06-07 at 16:52 -0400, Danie Qian wrote:
>> ----- Original Message -----
>> From: "Matthew Kent" <ma...@bravenet.com>
>> To: <mo...@httpd.apache.org>
>> Sent: Friday, July 06, 2007 4:13 PM
>> Subject: Re: mod_authn_dbd - internal server error after certain idle
>> time
>>
>>
>> > On Fri, 2007-06-07 at 15:54 -0400, Danie Qian wrote:
>> >> ----- Original Message -----
>> >> From: "Matthew Kent" <ma...@bravenet.com>
>> >> To: <mo...@httpd.apache.org>
>> >> Sent: Friday, July 06, 2007 3:18 PM
>> >> Subject: Re: mod_authn_dbd - internal server error after certain idle
>> >> time
>> >>
>> >>
>> >> > On Fri, 2007-06-07 at 15:07 -0400, Danie Qian wrote:
>> >> >> I am using mod_authn_dbd and mod_dbd to do authentication again a
>> >> >> mysql
>> >> >> database. Basically it works but after sometime I will get a
>> >> >> internal
>> >> >> server
>> >> >> error. I checked the log this happens at apr_dbd_pvselect() line in
>> >> >> mod_authn_dbd.c. So this looks like to me that apr_dbd_pvselect()
>> >> >> is
>> >> >> trying
>> >> >> to re-use a connection that is closed by the mysql server side
>> >> >> whose
>> >> >> wait-timeout is set to 1200 seconds. my mod_dbd setting is this
>> >> >> DBDMin 1
>> >> >> DBDKeep 2
>> >> >> DBDMax 50
>> >> >> DBDExptime 60
>> >> >> My question is why the keepalive setting for 60sec doesnt work for
>> >> >> me?
>> >> >> How
>> >> >> do I fix this without changing the timeout setting on the mysql
>> >> >> server?
>> >> >>
>> >> >>
>> >> >
>> >> > I've haven't used mod_authn_dbd yet but this sounds similar to
>> >> > issues I
>> >> > was having
>> >> >
>> >> > http://issues.apache.org/bugzilla/show_bug.cgi?id=39329
>> >> >
>> >> > patch hasn't made its way into apr_dbd_mysql.c yet. Please add a
>> >> > note
>> >> > if
>> >> > the it fixes your issue.
>> >> >
>> >> >> Thanks,
>> >> >> Daniel
>> >> >>
>> >> > --
>> >> > Matthew Kent \ SA \ bravenet.com
>> >> >
>> >> >
>> >>
>> >> I am using apache-2.2.4 configured with apr-1.2.9 and apr-1.2.8. I
>> >> have
>> >> the
>> >> above mentioned problem even after I replaced mod_dbd.c with the one
>> >> from
>> >> yesterday's snapshot. Do you think this patch will still apply?
>> >>
>> >>
>> >
>> > Well the patch is against apr_dbd_mysql.c (in apr-util) not mod_dbd.c
>> > (in httpd) so I think it would apply in this case.
>> >
>>
>> Are you sure? Here is first 2 lines of the 2006-10-11 patches in the
>> above
>> link?
>>
>> --- modules/database/mod_dbd.c.orig 2006-05-18 10:59:22.000000000 -0700
>> +++ modules/database/mod_dbd.c 2006-05-18 10:57:10.000000000 -0700
>>
>
> Ah jeez, proves how long its been since I looked at this issue.
>
> Apologies for wasting your time, no idea if this is fixed in trunk,
> though I'm looking now.
> --
> Matthew Kent \ SA \ bravenet.com
>
>
Apparently it hasnt been fixed as I AM using the mod_dbd.c file from trunk
and still having this problem. but it did fix some problems in dbd_acquire()
I had before I replaced the mod_dbd.c that comes with apache-2.2.4. I am
surprised that it seems no one is using these new modules that are supposed
to be better than mod_auth_mysql.c.
Re: mod_authn_dbd - internal server error after certain idle time
Posted by Matthew Kent <ma...@bravenet.com>.
On Fri, 2007-06-07 at 16:52 -0400, Danie Qian wrote:
> ----- Original Message -----
> From: "Matthew Kent" <ma...@bravenet.com>
> To: <mo...@httpd.apache.org>
> Sent: Friday, July 06, 2007 4:13 PM
> Subject: Re: mod_authn_dbd - internal server error after certain idle time
>
>
> > On Fri, 2007-06-07 at 15:54 -0400, Danie Qian wrote:
> >> ----- Original Message -----
> >> From: "Matthew Kent" <ma...@bravenet.com>
> >> To: <mo...@httpd.apache.org>
> >> Sent: Friday, July 06, 2007 3:18 PM
> >> Subject: Re: mod_authn_dbd - internal server error after certain idle
> >> time
> >>
> >>
> >> > On Fri, 2007-06-07 at 15:07 -0400, Danie Qian wrote:
> >> >> I am using mod_authn_dbd and mod_dbd to do authentication again a
> >> >> mysql
> >> >> database. Basically it works but after sometime I will get a internal
> >> >> server
> >> >> error. I checked the log this happens at apr_dbd_pvselect() line in
> >> >> mod_authn_dbd.c. So this looks like to me that apr_dbd_pvselect() is
> >> >> trying
> >> >> to re-use a connection that is closed by the mysql server side whose
> >> >> wait-timeout is set to 1200 seconds. my mod_dbd setting is this
> >> >> DBDMin 1
> >> >> DBDKeep 2
> >> >> DBDMax 50
> >> >> DBDExptime 60
> >> >> My question is why the keepalive setting for 60sec doesnt work for me?
> >> >> How
> >> >> do I fix this without changing the timeout setting on the mysql
> >> >> server?
> >> >>
> >> >>
> >> >
> >> > I've haven't used mod_authn_dbd yet but this sounds similar to issues I
> >> > was having
> >> >
> >> > http://issues.apache.org/bugzilla/show_bug.cgi?id=39329
> >> >
> >> > patch hasn't made its way into apr_dbd_mysql.c yet. Please add a note
> >> > if
> >> > the it fixes your issue.
> >> >
> >> >> Thanks,
> >> >> Daniel
> >> >>
> >> > --
> >> > Matthew Kent \ SA \ bravenet.com
> >> >
> >> >
> >>
> >> I am using apache-2.2.4 configured with apr-1.2.9 and apr-1.2.8. I have
> >> the
> >> above mentioned problem even after I replaced mod_dbd.c with the one from
> >> yesterday's snapshot. Do you think this patch will still apply?
> >>
> >>
> >
> > Well the patch is against apr_dbd_mysql.c (in apr-util) not mod_dbd.c
> > (in httpd) so I think it would apply in this case.
> >
>
> Are you sure? Here is first 2 lines of the 2006-10-11 patches in the above
> link?
>
> --- modules/database/mod_dbd.c.orig 2006-05-18 10:59:22.000000000 -0700
> +++ modules/database/mod_dbd.c 2006-05-18 10:57:10.000000000 -0700
>
Ah jeez, proves how long its been since I looked at this issue.
Apologies for wasting your time, no idea if this is fixed in trunk,
though I'm looking now.
--
Matthew Kent \ SA \ bravenet.com
Re: mod_authn_dbd - internal server error after certain idle time
Posted by Danie Qian <da...@bestningning.com>.
----- Original Message -----
From: "Matthew Kent" <ma...@bravenet.com>
To: <mo...@httpd.apache.org>
Sent: Friday, July 06, 2007 4:13 PM
Subject: Re: mod_authn_dbd - internal server error after certain idle time
> On Fri, 2007-06-07 at 15:54 -0400, Danie Qian wrote:
>> ----- Original Message -----
>> From: "Matthew Kent" <ma...@bravenet.com>
>> To: <mo...@httpd.apache.org>
>> Sent: Friday, July 06, 2007 3:18 PM
>> Subject: Re: mod_authn_dbd - internal server error after certain idle
>> time
>>
>>
>> > On Fri, 2007-06-07 at 15:07 -0400, Danie Qian wrote:
>> >> I am using mod_authn_dbd and mod_dbd to do authentication again a
>> >> mysql
>> >> database. Basically it works but after sometime I will get a internal
>> >> server
>> >> error. I checked the log this happens at apr_dbd_pvselect() line in
>> >> mod_authn_dbd.c. So this looks like to me that apr_dbd_pvselect() is
>> >> trying
>> >> to re-use a connection that is closed by the mysql server side whose
>> >> wait-timeout is set to 1200 seconds. my mod_dbd setting is this
>> >> DBDMin 1
>> >> DBDKeep 2
>> >> DBDMax 50
>> >> DBDExptime 60
>> >> My question is why the keepalive setting for 60sec doesnt work for me?
>> >> How
>> >> do I fix this without changing the timeout setting on the mysql
>> >> server?
>> >>
>> >>
>> >
>> > I've haven't used mod_authn_dbd yet but this sounds similar to issues I
>> > was having
>> >
>> > http://issues.apache.org/bugzilla/show_bug.cgi?id=39329
>> >
>> > patch hasn't made its way into apr_dbd_mysql.c yet. Please add a note
>> > if
>> > the it fixes your issue.
>> >
>> >> Thanks,
>> >> Daniel
>> >>
>> > --
>> > Matthew Kent \ SA \ bravenet.com
>> >
>> >
>>
>> I am using apache-2.2.4 configured with apr-1.2.9 and apr-1.2.8. I have
>> the
>> above mentioned problem even after I replaced mod_dbd.c with the one from
>> yesterday's snapshot. Do you think this patch will still apply?
>>
>>
>
> Well the patch is against apr_dbd_mysql.c (in apr-util) not mod_dbd.c
> (in httpd) so I think it would apply in this case.
>
Are you sure? Here is first 2 lines of the 2006-10-11 patches in the above
link?
--- modules/database/mod_dbd.c.orig 2006-05-18 10:59:22.000000000 -0700
+++ modules/database/mod_dbd.c 2006-05-18 10:57:10.000000000 -0700
Re: mod_authn_dbd - internal server error after certain idle time
Posted by Matthew Kent <ma...@bravenet.com>.
On Fri, 2007-06-07 at 15:54 -0400, Danie Qian wrote:
> ----- Original Message -----
> From: "Matthew Kent" <ma...@bravenet.com>
> To: <mo...@httpd.apache.org>
> Sent: Friday, July 06, 2007 3:18 PM
> Subject: Re: mod_authn_dbd - internal server error after certain idle time
>
>
> > On Fri, 2007-06-07 at 15:07 -0400, Danie Qian wrote:
> >> I am using mod_authn_dbd and mod_dbd to do authentication again a mysql
> >> database. Basically it works but after sometime I will get a internal
> >> server
> >> error. I checked the log this happens at apr_dbd_pvselect() line in
> >> mod_authn_dbd.c. So this looks like to me that apr_dbd_pvselect() is
> >> trying
> >> to re-use a connection that is closed by the mysql server side whose
> >> wait-timeout is set to 1200 seconds. my mod_dbd setting is this
> >> DBDMin 1
> >> DBDKeep 2
> >> DBDMax 50
> >> DBDExptime 60
> >> My question is why the keepalive setting for 60sec doesnt work for me?
> >> How
> >> do I fix this without changing the timeout setting on the mysql server?
> >>
> >>
> >
> > I've haven't used mod_authn_dbd yet but this sounds similar to issues I
> > was having
> >
> > http://issues.apache.org/bugzilla/show_bug.cgi?id=39329
> >
> > patch hasn't made its way into apr_dbd_mysql.c yet. Please add a note if
> > the it fixes your issue.
> >
> >> Thanks,
> >> Daniel
> >>
> > --
> > Matthew Kent \ SA \ bravenet.com
> >
> >
>
> I am using apache-2.2.4 configured with apr-1.2.9 and apr-1.2.8. I have the
> above mentioned problem even after I replaced mod_dbd.c with the one from
> yesterday's snapshot. Do you think this patch will still apply?
>
>
Well the patch is against apr_dbd_mysql.c (in apr-util) not mod_dbd.c
(in httpd) so I think it would apply in this case.
Incidentally here's my httpd.conf ifmodule block. I've been using
mod_dbd + apr-util with apr_dbd_mysql and the linked patch in production
since it came out without issue.
<IfModule mod_dbd.c>
DBDriver mysql
# persistant connections
DBDPersist On
# mod_dbd is still very fragile, may segfault with wrong connection
# params
# XXX: double check this claim
<IfDefine isstaging>
DBDParams host=x,user=x,pass=x,dbname=x,port=3306
</IfDefine>
<IfDefine isproduction>
DBDParams host=x,user=x,pass=x,dbname=x,port=3306
</IfDefine>
# this is one connection PER httpd thread, no need to be more than
# 1 unless we have multiple modules using mod_dbd
DBDMin 1
DBDMax 1
DBDKeep 1
DBDExptime 600
</IfModule>
Oh and apr-util was compiled against mysql 5.0.27. The MySQL server has
a wait_timeout of 600 seconds.
--
Matthew Kent \ SA \ bravenet.com
Re: mod_authn_dbd - internal server error after certain idle time
Posted by Danie Qian <da...@bestningning.com>.
----- Original Message -----
From: "Matthew Kent" <ma...@bravenet.com>
To: <mo...@httpd.apache.org>
Sent: Friday, July 06, 2007 3:18 PM
Subject: Re: mod_authn_dbd - internal server error after certain idle time
> On Fri, 2007-06-07 at 15:07 -0400, Danie Qian wrote:
>> I am using mod_authn_dbd and mod_dbd to do authentication again a mysql
>> database. Basically it works but after sometime I will get a internal
>> server
>> error. I checked the log this happens at apr_dbd_pvselect() line in
>> mod_authn_dbd.c. So this looks like to me that apr_dbd_pvselect() is
>> trying
>> to re-use a connection that is closed by the mysql server side whose
>> wait-timeout is set to 1200 seconds. my mod_dbd setting is this
>> DBDMin 1
>> DBDKeep 2
>> DBDMax 50
>> DBDExptime 60
>> My question is why the keepalive setting for 60sec doesnt work for me?
>> How
>> do I fix this without changing the timeout setting on the mysql server?
>>
>>
>
> I've haven't used mod_authn_dbd yet but this sounds similar to issues I
> was having
>
> http://issues.apache.org/bugzilla/show_bug.cgi?id=39329
>
> patch hasn't made its way into apr_dbd_mysql.c yet. Please add a note if
> the it fixes your issue.
>
>> Thanks,
>> Daniel
>>
> --
> Matthew Kent \ SA \ bravenet.com
>
>
I am using apache-2.2.4 configured with apr-1.2.9 and apr-1.2.8. I have the
above mentioned problem even after I replaced mod_dbd.c with the one from
yesterday's snapshot. Do you think this patch will still apply?
Re: mod_authn_dbd - internal server error after certain idle time
Posted by Matthew Kent <ma...@bravenet.com>.
On Fri, 2007-06-07 at 15:07 -0400, Danie Qian wrote:
> I am using mod_authn_dbd and mod_dbd to do authentication again a mysql
> database. Basically it works but after sometime I will get a internal server
> error. I checked the log this happens at apr_dbd_pvselect() line in
> mod_authn_dbd.c. So this looks like to me that apr_dbd_pvselect() is trying
> to re-use a connection that is closed by the mysql server side whose
> wait-timeout is set to 1200 seconds. my mod_dbd setting is this
> DBDMin 1
> DBDKeep 2
> DBDMax 50
> DBDExptime 60
> My question is why the keepalive setting for 60sec doesnt work for me? How
> do I fix this without changing the timeout setting on the mysql server?
>
>
I've haven't used mod_authn_dbd yet but this sounds similar to issues I
was having
http://issues.apache.org/bugzilla/show_bug.cgi?id=39329
patch hasn't made its way into apr_dbd_mysql.c yet. Please add a note if
the it fixes your issue.
> Thanks,
> Daniel
>
--
Matthew Kent \ SA \ bravenet.com