Re: auth dbd pgsql issues
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 pgp1LE9XW06MR.pgp Description: PGP signature
Re: auth dbd pgsql issues
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
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
Hi Chris, thanks for your reply. Chris Darroch wrote: Phil Endecott wrote: http://marc.info/?l=apache-httpd-usersm=118765132424174w=2 My guess is that I'm encountering the known issues with DBD described here: http://marc.info/?l=apache-httpd-devm=116742014418304w=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
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
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
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-usersm=118765132424174w=2 My guess is that I'm encountering the known issues with DBD described here: http://marc.info/?l=apache-httpd-devm=116742014418304w=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
Phil Endecott wrote: http://marc.info/?l=apache-httpd-usersm=118765132424174w=2 My guess is that I'm encountering the known issues with DBD described here: http://marc.info/?l=apache-httpd-devm=116742014418304w=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
Re: auth dbd pgsql issues
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.
