You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@httpd.apache.org by Phil Endecott <sp...@chezphil.org> on 2007/08/21 15:51:41 UTC
auth dbd pgsql issues
Dear Experts,
I am trying to track down a broken postgresql authn issue which I have
described here:
http://marc.info/?l=apache-httpd-users&m=118765132424174&w=2
My guess is that I'm encountering the known issues with DBD described here:
http://marc.info/?l=apache-httpd-dev&m=116742014418304&w=2
Am I right in thinking that this is fixed in the trunk but not in
2.2.4? What about 2.2.5/6 - I don't see anything in the 2.2.5 changelog.
Anyway, I have been looking at the source, and a few issues thoughts
have occurred to me:
- In mod_authn_dbd.c, a couple of global variables are used to point to
the dbd_acquire and dbd_prepare functions. Am I right in thinking that
this means you can have only one dbd driver for authn? So you can't
for example, have postgresql in one virtual host and mysql in another?
(Is this already a limitation of dbd?) This isn't a problem for me,
unless these variables get set to NULL by a virtual host with no
DBDriver directive. Maybe DBDriver should only appear in the global
configuration? Is it certain that these variables are set before the
server first forks?
- It looks as if, when a new db connection is created, all prepared
statements are prepared on the new connection. However, when a new
prepared statement is created, it is not prepared on any existing
connections. This is fine as long as all prepared statements are
declared before any connections are established. Correct?
- authn_dbd_password() uses the error message "Error looking up %s in
database" for 3 different errors. It would be really great to have
different messages in each case.
- The mod_authn_dbd docs
(http://httpd.apache.org/docs/2.2/mod/mod_authn_dbd.html) claim "DBD
drivers recognise both stdio-like %s and native syntax". Is this
accurate? It seems that the postgresql DBD driver does some type magic
based on the character after the %, which wouldn't be possible with the
postgresql $1 syntax. Maybe $1 only works for strings (which would be
OK for usernames, of course). (Does it correctly count the number of
parameters if I use $1?)
- The mod_dbd docs (http://httpd.apache.org/docs/2.2/mod/mod_dbd.html)
say that DBDPersist can be 0 or 1; this should be on or off.
Many thanks for any feedback. If the answer is "this is fixed in
2.2.6", and that's due soon, I'll just wait.
Regards,
Phil.
Re: auth dbd pgsql issues
Posted by Phil Endecott <sp...@chezphil.org>.
Chris Darroch wrote:
> Phil Endecott wrote:
>
>> OK; my experience seems to be that in this respect 2.2.4 has regressed
>> compared to 2.2.3 (though I may have been lucky in some way with my
>> 2.2.3 setup) and certainly compared to 2.0.x + the 3rd-party
>> mod_auth_pgsql. I don't know if this affects how the issue is
>> prioritised for inclusion in future versions. I would love to see
>> working authn_dbd ASAP.
>
> We have mod_authn_dbd + mod_dbd working but we use the trunk
> version of mod_dbd.c. It's a drop-in replacement for 2.2.x's
> mod_dbd.c; you can just copy it into 2.2.4 and recompile.
$ svn cat
http://svn.apache.org/repos/asf/httpd/httpd/trunk/modules/database/mod_dbd.c
> mod_dbd.c
$ apxs2 -c mod_dbd.c
# apxs2 -i mod_dbd.la
# /etc/init.d/apache2 restart
..and it works. (Well, for all of 5 minutes so far.)
I'm also pleased to see that performance is quite reasonable. I had
assumed that I would need to add some sort of credentials caching, and
have posted about this here before. But it looks as if, with a
persistent connection, PostgreSQL responds very quickly and takes very
little CPU to do so.
Many thanks for your help.
Phil.
Re: auth dbd pgsql issues
Posted by Chris Darroch <ch...@pearsoncmg.com>.
Phil Endecott wrote:
> OK; my experience seems to be that in this respect 2.2.4 has regressed
> compared to 2.2.3 (though I may have been lucky in some way with my
> 2.2.3 setup) and certainly compared to 2.0.x + the 3rd-party
> mod_auth_pgsql. I don't know if this affects how the issue is
> prioritised for inclusion in future versions. I would love to see
> working authn_dbd ASAP.
We have mod_authn_dbd + mod_dbd working but we use the trunk
version of mod_dbd.c. It's a drop-in replacement for 2.2.x's
mod_dbd.c; you can just copy it into 2.2.4 and recompile. I'd recommend
trying that to see if it deals with your concerns; for example, it should
handle the original issue I think you had with putting DBD directives
into virtual hosts.
If you still have problems, they may of course either be previously
unnoticed bugs in the driver or elsewhere. But I'd suggest trying
trunk's mod_dbd.c as a first step.
Chris.
--
GPG Key ID: 366A375B
GPG Key Fingerprint: 485E 5041 17E1 E2BB C263 E4DE C8E3 FA36 366A 375B
Re: auth dbd pgsql issues
Posted by Phil Endecott <sp...@chezphil.org>.
Hi Chris, thanks for your reply.
Chris Darroch wrote:
> Phil Endecott wrote:
>
>> http://marc.info/?l=apache-httpd-users&m=118765132424174&w=2
>>
>> My guess is that I'm encountering the known issues with DBD described here:
>> http://marc.info/?l=apache-httpd-dev&m=116742014418304&w=2
>>
>> Am I right in thinking that this is fixed in the trunk but not in
>> 2.2.4? What about 2.2.5/6 - I don't see anything in the 2.2.5 changelog.
>
> I think you're right about the problem you're encountering;
> the patches for 2.2.x await a third vote and so they're not in
> expected in 2.2.5/6, as it stands at the moment.
OK; my experience seems to be that in this respect 2.2.4 has regressed
compared to 2.2.3 (though I may have been lucky in some way with my
2.2.3 setup) and certainly compared to 2.0.x + the 3rd-party
mod_auth_pgsql. I don't know if this affects how the issue is
prioritised for inclusion in future versions. I would love to see
working authn_dbd ASAP.
>> - authn_dbd_password() uses the error message "Error looking up %s in
>> database" for 3 different errors. It would be really great to have
>> different messages in each case.
>
> I'd suggest opening a Bugzilla report and, if possible, attaching
> a patch file with the revised messages you'd like to see (and please add
> the keyword PatchAvailable in this case).
done; http://issues.apache.org/bugzilla/show_bug.cgi?id=43210
>> - The mod_authn_dbd docs
>> (http://httpd.apache.org/docs/2.2/mod/mod_authn_dbd.html) claim "DBD
>> drivers recognise both stdio-like %s and native syntax". Is this
>> accurate? It seems that the postgresql DBD driver does some type magic
>> based on the character after the %, which wouldn't be possible with the
>> postgresql $1 syntax. Maybe $1 only works for strings (which would be
>> OK for usernames, of course). (Does it correctly count the number of
>> parameters if I use $1?)
>
> I seem to recall some type magic in this driver, but I'm not
> particularly familiar with it. If you encounter problems, I'd again
> suggest opening a Bugzilla report.
I haven't actually got as far as encountering any problems with this
since dbd is not working at all :-(
However, see the documentation for apr_dbd_prepare() at http://apr.apache.org/docs/apr-util/trunk/group___a_p_r___util___d_b_d.html#gcf21412447c4357c64d1e9200a0f5eec
"To specify parameters of the prepared query, use %s, %d etc. (see
below for full list) in place of database specific parameter syntax"
Looking at the source it's pretty clear that it understands only
%letter synatx, not $n or even MySQL % syntax. So I've filed a bug on
the mod_authn_dbd documentation:
http://issues.apache.org/bugzilla/show_bug.cgi?id=43211
Regards,
Phil.
Re: auth dbd pgsql issues
Posted by Guenter Knauf <fu...@apache.org>.
Hi,
> If a single patch file helps, here it is:
> http://people.apache.org/~chrisd/patches/mod_dbd_pools_groups/mod_dbd-all-2.2.x.patch
thanks! Applied cleanly, and solved at least two crash conditions for me;
another friend who's on NetWare tried the patch too, and reported same;
therefore voted for it. Tested with libmysql 5.0.45.
greets, Guenter.
Re: auth dbd pgsql issues
Posted by Chris Darroch <ch...@pearsoncmg.com>.
Guenter Knauf wrote:
> I think you would increase chance for another vote if there would be
> only _one_ patch which applies cleanly against current 2.2.x head;
> I've started testing with them, but got stopped since one patch didnt
> apply of them all....; therefore I only voted on the first one which I
> could test. If possible please create a new diff which includes all
> changes, and you get my vote at once after I have verified that at least
> some of the issues I see on threaded NetWare are gone with the patch
> (which I believe from what I've read so far)...
If a single patch file helps, here it is:
http://people.apache.org/~chrisd/patches/mod_dbd_pools_groups/mod_dbd-all-2.2.x.patch
Thanks,
Chris.
--
GPG Key ID: 366A375B
GPG Key Fingerprint: 485E 5041 17E1 E2BB C263 E4DE C8E3 FA36 366A 375B
Re: auth dbd pgsql issues
Posted by Guenter Knauf <fu...@apache.org>.
Hi Chris,
> I think you're right about the problem you're encountering;
> the patches for 2.2.x await a third vote and so they're not in
> expected in 2.2.5/6, as it stands at the moment.
I think you would increase chance for another vote if there would be only _one_ patch which applies cleanly against current 2.2.x head; I've started testing with them, but got stopped since one patch didnt apply of them all....; therefore I only voted on the first one which I could test.
If possible please create a new diff which includes all changes, and you get my vote at once after I have verified that at least some of the issues I see on threaded NetWare are gone with the patch (which I believe from what I've read so far)...
thanks, Guen.
Re: auth dbd pgsql issues
Posted by "Dr. Peter Poeml" <po...@suse.de>.
On Tue, Aug 21, 2007 at 10:32:56AM -0700, Chris Darroch wrote:
> I think you're right about the problem you're encountering;
> the patches for 2.2.x await a third vote and so they're not in
> expected in 2.2.5/6, as it stands at the moment.
I am using the trunk version of mod_dbd.c with 2.2.4 since about March,
and I can confirm that it works well enough to have served millions of
requests with database lookups since then, by a custom module through
mod_dbd into a mysql database.
The only difference of my mod_dbd.c version being the recent small
apr_dbd_check_conn() change (r539687).
Peter
--
"WARNING: This bug is visible to non-employees. Please be respectful!"
SUSE LINUX Products GmbH
Research & Development
Re: auth dbd pgsql issues
Posted by Chris Darroch <ch...@pearsoncmg.com>.
Phil Endecott wrote:
> http://marc.info/?l=apache-httpd-users&m=118765132424174&w=2
>
> My guess is that I'm encountering the known issues with DBD described here:
> http://marc.info/?l=apache-httpd-dev&m=116742014418304&w=2
>
> Am I right in thinking that this is fixed in the trunk but not in
> 2.2.4? What about 2.2.5/6 - I don't see anything in the 2.2.5 changelog.
I think you're right about the problem you're encountering;
the patches for 2.2.x await a third vote and so they're not in
expected in 2.2.5/6, as it stands at the moment.
> - In mod_authn_dbd.c, a couple of global variables are used to point to
> the dbd_acquire and dbd_prepare functions. Am I right in thinking that
> this means you can have only one dbd driver for authn? So you can't
> for example, have postgresql in one virtual host and mysql in another?
This shouldn't limit the use of multiple drivers (if you're using
the mod_dbd from trunk). These mod_authn_dbd globals just point to
common mod_dbd functions. Assuming there are no underlying
incompatibilities between the drivers (or other bugs), mod_dbd's
functions should invoke the relevant one's functions for each request.
> - It looks as if, when a new db connection is created, all prepared
> statements are prepared on the new connection. However, when a new
> prepared statement is created, it is not prepared on any existing
> connections. This is fine as long as all prepared statements are
> declared before any connections are established. Correct?
Yes. The ap_dbd_prepare() statement must be used during the
configuration phase only. The trunk code says:
/* Prepare a statement for use by a client module during
* the server startup/configuration phase. Can't be called
* after the server has created its children (use apr_dbd_*).
*/
> - authn_dbd_password() uses the error message "Error looking up %s in
> database" for 3 different errors. It would be really great to have
> different messages in each case.
I'd suggest opening a Bugzilla report and, if possible, attaching
a patch file with the revised messages you'd like to see (and please add
the keyword PatchAvailable in this case).
> - The mod_authn_dbd docs
> (http://httpd.apache.org/docs/2.2/mod/mod_authn_dbd.html) claim "DBD
> drivers recognise both stdio-like %s and native syntax". Is this
> accurate? It seems that the postgresql DBD driver does some type magic
> based on the character after the %, which wouldn't be possible with the
> postgresql $1 syntax. Maybe $1 only works for strings (which would be
> OK for usernames, of course). (Does it correctly count the number of
> parameters if I use $1?)
I seem to recall some type magic in this driver, but I'm not
particularly familiar with it. If you encounter problems, I'd again
suggest opening a Bugzilla report.
> - The mod_dbd docs (http://httpd.apache.org/docs/2.2/mod/mod_dbd.html)
> say that DBDPersist can be 0 or 1; this should be on or off.
I'll try to remember to fix that, but if you don't see it in
trunk's docs after a while ... submit a Bugzilla report! :-)
Thanks,
Chris.
--
GPG Key ID: 366A375B
GPG Key Fingerprint: 485E 5041 17E1 E2BB C263 E4DE C8E3 FA36 366A 375B