You are viewing a plain text version of this content. The canonical link for it is here.
Posted to users@httpd.apache.org by Ben <be...@redsnapper.net> on 2006/08/30 14:53:49 UTC
[users@httpd] A NetHack-esque Journey of the dark arts for DBD(mysql) under httpd-2.2.3
This is my story of yesterday, on how I managed to bring something
resembling mysql onto dbd over httpd 2.2.3;
It may explain why I am astounded and amazed that 2.2.3 is called
"The best available version of Apache", and why I believe it needs a
lot more work to get that badge.
The story has a less than happy ending, but this epic journey of
NetHack proportions is to be continued....
We have a production server that requires mysql based
authentication. We used to use one of (any number of) mysql
authentication modules.
We have well over 100 virtual hosts on this server, using over 30
distinct mysql database instances.
httpd 2.1 Has changed things a bit, and the Apache devteam is
actively promoting the use of mod_dbd for all sql connections:
Developers are told "DBD Framework (SQL Database API). [...] Apache
2.1 and later provides the ap_dbd API for managing database
connections, while APR 1.2 and later provides the apr_dbd API for
interacting with the database. New modules SHOULD now use these APIs
for all SQL database operations. Existing applications SHOULD be
upgraded to use it where feasible, either transparently or as a
recommended option to their users."
So - naive man that I am, it sounds like Apache's recommended
strategy is to move over to dbd.
And - even better, Apache 2.1+ provides (to me that means provides a
working version!) what we need, so 2.2.3 shouldn't be a problem.
What follows is my route to successfully install mysql dbd -
overcoming typos, bugs, and so on in the process.
However, the light of day is yet to be reached.
Rather than take you minute by minute through one of the most
convoluted days of my life, I will take you straight to the (near)
solution. This was for some linux server - but most of the work was
duplicated on os x. All binaries are compiled from source using gcc
v4.0.2 (and I use tools such as wget)
FYI, all our production binaries are in the /opt directory (whereas
by default, most stuff is installed to /usr/local) and /opt/bin is in
$PATH.
Also on our linux, /etc/ld.so.conf includes the directories /opt/
lib /opt/lib/mysql /opt/httpd/lib /opt/httpd/apr/lib
Lastly, we also have ldap installed (which for sake of brevity, I
won't describe the install here - but it's not a tenth as complex as
the httpd)
Before we start, let's make sure that we have a recent version of
autoconf (mine was out of date.. even though it was only 6 months old)
== autoconf ==
mkdir -p ~/dev
cd ~/dev
wget http://ftp.gnu.org/gnu/autoconf/autoconf-2.60.tar.gz
#why don't gnu have a 'latest' symlink for autoconf!
tar -xzf autoconf-2.60.tar.gz
cd autoconf-2.60
./configure --prefix=/opt --enable-threads=posix
make; make install
Now, we will need to make sure that our mysql server has the thread-
safe client library. Mine didn't.
We already had a 5 server so I only needed a reboot.
============= mysql =============
cd ~/dev
wget http://dev.mysql.com/get/Downloads/MySQL-5.0/mysql-5.0.24.tar.gz/
from/ftp://ftp.mirrorservice.org/sites/ftp.mysql.com/
tar -xzf mysql-5.0.24.tar.gz
cd mysql-5.0.24
CFLAGS="-O3" CXX=gcc CXXFLAGS="-O3 -felide-constructors -fno-
exceptions -fno-rtti" ./configure --prefix=/opt --with-charset=utf8 --
enable-thread-safe-client --enable-threads=posix --with-ncurses
make all; make install
sudo ldconfig -v #linux only
mysqladmin shutdown; mysqld_safe&
Now to install httpd (at last)
============= httpd =============
cd ~/dev
wget http://apache.rmplc.co.uk/httpd/httpd-2.2.3.tar.gz
tar -xzf httpd-2.2.3.tar.gz
#Apache devteam. don't like us using nph- anymore. But I live in the
real world and have legacy systems to look after.
cd httpd-2.2.3/modules/generators
wget -O mod_cgi.patch "http://issues.apache.org/bugzilla/
attachment.cgi?id=17891"
cp mod_cgi.c mod_cgi.c.orig
patch mod_cgi.c mod_cgi.patch
#Now I need to manually change the install locations for apr and apr-
util
cd ~/dev/httpd-2.2.3/srclib/apr
vi config.layout # (line 14 - in Layout apr, change prefix to /opt/
httpd/apr )
cd ~/dev/httpd-2.2.3/srclib/apr-util
vi config.layout # (line 14 - in Layout apr, change prefix to /opt/
httpd/apr )
#Now I need to get the mysql driver
cd ~/dev/httpd-2.2.3/srclib/apr-util/dbd
wget http://apache.webthing.com/svn/apache/apr/apr_dbd_mysql.c
vi apr_dbd_mysql.c #Remove mysql header conditional lines
48,49,50,51,54 - keep #include <mysql/mysql.h> and #include <mysql/
errmsg.h>
cd ~/dev/httpd-2.2.3/srclib/apr-util
./buildconf #Otherwise the mysql file will not be found.
#we can test to see if the mysql driver will be loaded now by doing
# make; ldd .libs/libaprutil-1.so | grep libmysqlclient_r
cd ~/dev/httpd-2.2.3/srclib/apr-util/build
#Now we need to patch the bugged dbd.m4 file -- we can spend hours
reading the threads regarding this issue - if we can find them.
wget -O dbd.patch -p0 "http://marc2.theaimsgroup.com/?l=apr-
dev&m=114384830419072&q=p3"
cp dbd.m4 dbd.m4.orig
patch -p0 dbd.m4 dbd.patch #(Use ctrl-d to skip the second patch.)
#Now we need to move any current directory (to stop some silly logic
from ruining the day - nothing to do with symlinks )
mv /opt/httpd /opt/httpd.x
cd ~/dev/httpd-2.2.3
./buildconf
vi config.layout #( Copy the 22 lines of the Apache Layout and call
it Opt - change just the prefix line to opt/httpd )
./configure --prefix=/opt/httpd --enable-layout=Opt --enable-dbd --
with-mysql=/opt --enable-access --enable-ldap --enable-actions --
enable-alias --enable-auth --enable-auth_dbm --enable-auth_digest --
enable-authn_dbd --enable-authnz-ldap --enable-cache --enable-cgi --
enable-dir --enable-disk_cache --enable-dumpio --enable-env --enable-
expires --enable-fastcgi --enable-file_cache --enable-headers --
enable-include --enable-info --enable-log_config --enable-
log_forensic --enable-logio --enable-mem_cache --enable-mime --enable-
mime_magic --enable-negotiation --enable-rewrite --enable-setenvif --
enable-ssl --enable-status --enable-unique_id --enable-usertrack --
enable-ldap --enable-version --enable-vhost_alias --enable-so --
enable-module=all --enable-shared=max
make
#Now move back the directory (To keep conf files)
mv /opt/httpd.x /opt/httpd
make install
--------------------------------------------------
Phew. Now to set up the conf files.
httpd.conf
DBDriver mysql
# DBDPersist Off #Off/On Not 0 or 1 !!! When we RTFM, we must
realise it is way out of date. !!!
DBDMin 1
DBDKeep 2 #This isn't generating DBD for each VirtualHost
by any chance?
DBDMax 20
DBDExptime 10
#If you stick the above values into a VirtualHost directive, httpd
will die catastrophically. The sign of it dying is the following log
messages:
#Believe me, the following messages occur if the values are put in
the context of a VirtualHost. The driver is definitely installed and
loaded.
[crit] (70023)This function has not been implemented on this
platform: DBD: driver for [DBDriver unset] not available
[crit] (70023)This function has not been implemented on this
platform: DBD: failed to initialise
#Now we need to stick the DBDParams under each VirtualHost
DBDParams "dbname=client005 user=dbdauthent" #no password etc needed.
#And the following (straight out from RTFM )
<Directory />
AuthType Basic
AuthName "Database access"
AuthBasicProvider dbd
Require valid-user
AuthDBDUserPWQuery "select password from authn where username = %s"
</Directory>
----------------------------------------
Restart the server..
Whee! And I get a prompt for my username and password..
but.. but.. but.. Ho!! Put in my user details and what's this? A
500 (Internal Server Error)? Surely with LogLevel debug, I have
something about this?
[error] Error looking up testuser in database
Well, that's informative. So much for LogLevel debug
What else happens - that shouldn't?
Everytime I restart the server, I am swamped with additional httpd
threads - which kill the server. Coming from DBD somewhere (when I
comment out dbd, the problem goes) - are DBDMin/ DVDKeep installing
new threads for each virtualhost? 'cos something seriously weird is
going on...
----------------------------------------
Current conclusion. The apache devteam are forgetting to implement
QA in their releases.
They should not be recommending a set of tools that are hard to
implement and subsequently fail.
The documentation for dbd is is out of date, sparse, uninformative,
and distributed all over the place.
(At least one Apache developer I know is amazed if one hasn't read
every single text file in every directory of the release! Have you
looked through every directory of the release)
Moreover - much of the documentation assumes that we know which
directory things are to go into.
This epic journey is to be continued....
---------------------------------------------------------------------
The official User-To-User support forum of the Apache HTTP Server Project.
See <URL:http://httpd.apache.org/userslist.html> for more info.
To unsubscribe, e-mail: users-unsubscribe@httpd.apache.org
" from the digest: users-digest-unsubscribe@httpd.apache.org
For additional commands, e-mail: users-help@httpd.apache.org
Re: [users@httpd] A NetHack-esque Journey of the dark arts for DBD(mysql) under httpd-2.2.3
Posted by Ben <be...@redsnapper.net>.
> Nice to hear you got it working, though you seem to have gone
> the long way round.
You say I went the 'long way round', well other than using a package,
is there a shorter way to install dbd(mysql) into a custom directory?
Would you like to write out the steps in a complete and unambiguous
manner?
Regarding package installation, I am wondering if I would have got a
useful error message when the mysqlclient_r library wouldn't have
been found.
> I don't see anything here that we can use.
> Your account seems to boil down to a call for better packaging, and
> we could use some expertise in that area
Interesting spin.
Maybe you are unclear about the differences between QA, CCM, and
packaging?
I don't think so really - I have read your bio.
> But most end-users who don't want to concern themselves with that
> will use their
> distro's packages, so (arguably) that's what really matters.
I disagree [so yes! it is arguable :-) ]. Packaging is not what
'really' matters when it comes to applications like httpd.
In my opinion, for enterprise software such as httpd what really
matters is:
up-to-date source distribution
comprehensive, organised and up-to-date documentation
unit tests built in
useful (unambiguous, informative) debugging and error messages
throughout
centralised patch distribution
a working, centralised code update system
full backward-compatibility (regarding interfacing third party
applications)
Also, your statement implicitly suggests to me that the source
distribution of 2.2.3 is not really ready for production use, just
the packages.
For example, it seems to be really odd - seriously - that there have
been patches for the "myql" typo in dbd.m4 for several months, and
yet it is still found in the 2.2.3 source distribution. That's not
packaging - that's a lack of change/control management.
There are some major issues with the current build - as I mentioned
there is a bug somewhere that causes a massive bloat of httpd forks -
I don't know where, but it's to do with dbd.
As for whether or not I am a module developer - I could be - but a
management that has no control over it's software tends to lead to
dead software. I am seriously wondering if Apache 2.x will ever
become as popular as it's more robust 1.3x sibling: it's my belief
that if it doesn't get to work clean, out of the box (or from a
compile) it will end up being a dead branch. Until I feel confident
in the Apache 2.x codebase, why should I be interested in developing
modules for it?
The lack of relevant error messages in mod_authn_dbd and mod_dbd is a
major concern also.
Here is a (non-exclusive) list of some that could be added at little
cost to the development team:
DBD Driver xxx not found. It should be at yyy. See http://
dbddriversupportpage for more
DBD Driver xxx file found but it is not linked correctly. See http://
dbddriverpage for more
DBD Driver xxx found but not loaded. (Reason)
DBD connection failure for driver xxx - no sql server found at yyy
DBD connection failure for driver xxx - authentication failed for uuu
for server found at yyy
DBD connection failure for driver xxx - (other reasons / results)
DBD Driver xxx handle creation failed (Reason)
DBD connection failure for driver xxx - database yyy was not found
DBD connection failure for driver xxx - database yyy was found but
authentication failed with sqlserver message "EEEEE"
DBD Driver xxx sql query creation failed for query = "xxxxx" (Reasons)
DBD Driver xxx sql query "qqq" returned the message "EEEEEE"
DBD Driver xxx sql query "qqq" failed due to lack of privileges with
message "EEEEE"
And mod_authn_dbd:
DBD Driver xxx authentication failed matching "pppp" with
"qqqq" (with sqlserver message "EEEEE") (obviously showing the
encrypted match)
Some LogDebug messages:
DBD Driver xxx successfully connected to address yyy
DBD Driver xxx successfully connected to sql server
DBD Driver xxx successfully authenticated with sql server
DBD Driver xxx successfully executed query "qqqqq" with nnn results
So maybe 50 lines of code, which will significantly reduce the
headaches involved for end-users with DBD/driver connection or
authentication issues.
None of these are to do with packaging - they serve a major purpose -
which is to inform the enduser and the developers of where something
is failing. More importantly to the development team, they reduce the
support time for the software and improve the signal/noise ratio.
They help with the process of QA.
Now I could possibly add all those messages into the code base (I
could also help with other issues) - but last time I contributed,
nothing happened - the code remains untouched even though the
relevant bugs have been present in Apache 2.x since 2003. So I've
lost some will regarding contributing to the code base.
---------------------------------------------------------------------
The official User-To-User support forum of the Apache HTTP Server Project.
See <URL:http://httpd.apache.org/userslist.html> for more info.
To unsubscribe, e-mail: users-unsubscribe@httpd.apache.org
" from the digest: users-digest-unsubscribe@httpd.apache.org
For additional commands, e-mail: users-help@httpd.apache.org
Re: [users@httpd] A NetHack-esque Journey of the dark arts for DBD(mysql) under httpd-2.2.3
Posted by Nick Kew <ni...@webthing.com>.
On Wednesday 30 August 2006 13:53, Ben wrote:
> This is my story of yesterday, on how I managed to bring something
> resembling mysql onto dbd over httpd 2.2.3;
Nice to hear you got it working, though you seem to have gone
the long way round. Contributions to making it easier are of
course welcome, but I don't see anything here that we can use.
As a general rule, you should build from source if you're happy
to do that. Otherwise, you install a package from your operating
system (or distro, in the case of Linux). Choice is good!
> New modules SHOULD now use these APIs
> for all SQL database operations. Existing applications SHOULD be
> upgraded to use it where feasible, either transparently or as a
> recommended option to their users."
Yep. Are you developing a module? Or are you on the team for
one of the scripting languages?
> What follows is my route to successfully install mysql dbd -
> overcoming typos, bugs, and so on in the process.
> However, the light of day is yet to be reached.
Your account seems to boil down to a call for better packaging,
and we could use some expertise in that area. But most end-users
who don't want to concern themselves with that will use their
distro's packages, so (arguably) that's what really matters.
--
Nick Kew
---------------------------------------------------------------------
The official User-To-User support forum of the Apache HTTP Server Project.
See <URL:http://httpd.apache.org/userslist.html> for more info.
To unsubscribe, e-mail: users-unsubscribe@httpd.apache.org
" from the digest: users-digest-unsubscribe@httpd.apache.org
For additional commands, e-mail: users-help@httpd.apache.org
Re: [users@httpd] A NetHack-esque Journey of the dark arts for DBD(mysql) under httpd-2.2.3
Posted by Boysenberry Payne <bo...@humaniteque.com>.
I ran into difficulties with getting the dbd_auth to work also.
My solution was to remark it out of the config file and use
mod_perl to authenticate via DBD::mysql.
Sure saved me a lot of the head aches you had. But you
probably don't use mod_perl.
Thanks,
Boysenberry
boysenberrys.com | habitatlife.com | selfgnosis.com
On Aug 30, 2006, at 9:38 AM, Ben wrote:
> The next installment...
>
> It was time for me to add some meaningful messages to mod_authn_dbd.c
> Heads up to you apache developers: Try to be more imaginative when
> writing error messages - At least make each message unique!!
> There are six "Error looking up %s in database." errors in
> mod_autn_dbd.c - why not be a little bit more informative?
>
> Well, the good news is that I managed to get authentication working
> - after a new build and a couple of conf tweaks.
>
> Here is a breakdown of some of the [crit] messages - and what
> they appear to mean..
>
> (70023)This function has not been implemented on this platform:
> DBD: driver for [DBDriver unset] not available
> (70023)This function has not been implemented on this platform:
> DBD: failed to initialise
>
> ---- This means that you have stuck the DBD settings inside a
> VirtualHost! It will pass configtest, it is (supposedly) supported,
> but it will fry your httpd! Rawr!
>
> Here is a breakdown of some of the [error] messages - and what
> they appear to mean..
> "DBD: No driver for mysql" Your mysql_client_r is not
> linked properly! Try: ldd (apache install directory)/lib/
> libaprutil-1.so | grep libmysqlclient_r
> "Error looking up testuser in database" One of a whole bunch
> of things - including no database connection
> "user testuser not found ..." The user
> is not in the table specified - (or the table doesn't exist, or the
> query fails... )
> "user testuser: authentication failure ..." Good news! The
> user table / record was found - just the password was entered
> wrong! (or the field isn't encrypted)
>
> There is no return message saying whether or not the sql query
> actually worked. It would be nice for a developer to stick in a
> debug of the queries themselves.. (hint!)
> Also, LogDebug for .. dynamic library loading, function calling,
> sql connections, database connections, handle creation, sql query
> creation, the actual sql queries, the execution, an ERROR for sql
> error messages, LogDebug for number of records returned, the value
> of the password returned the and the value of the encrypted
> password it is trying to match - you know what LogDebug is useful
> for??? Debugging. (hint!) (hint!) (hint!)
>
> Also, I got a bit wobbly about which encrypt function to use.. So I
> tried the lot, and it turned out to be encrypt (doh)
> An example insert user query is:
>
> insert into users set user='test5', password=encrypt('test','test');
>
> The conf settings that are working for me are:
>
> <VirtualHost xxxxx:80>
> DBDParams "dbname=client06 user=dbdauth"
> [........]
> <Directory />
> AuthType Basic
> AuthName "Database access"
> AuthBasicProvider dbd
> Require valid-user
> AuthDBDUserPWQuery "select password from users where user =
> %s"
> </Directory>
> [........]
> </VirtualHost>
>
> Also - at the top of the httpd.conf file
>
> DBDriver mysql
> DBDPersist Off
> DBDMin 1
> DBDKeep 1
> DBDMax 1
> #DBDExptime 10
>
> Now all but the first of these settings remain mysterious and
> magical to me - but I think I have trouble with other settings.
> I still don't know what causes a massive increase of active httpd
> threads that invariably burns the server to the ground - but hey..
> here be dragons ...
>
> I am a little worried about touching anything. Eighteen hours and
> several levels of pain later - my keyboard dripping with the blood
> of ripped code - directories filled with random notes and partial
> files - and things work - but my confidence is in tatters.
>
> Unless the server keels over in the next few days - this adventure
> appears to be done!
>
> ---------------------------------------------------------------------
> The official User-To-User support forum of the Apache HTTP Server
> Project.
> See <URL:http://httpd.apache.org/userslist.html> for more info.
> To unsubscribe, e-mail: users-unsubscribe@httpd.apache.org
> " from the digest: users-digest-unsubscribe@httpd.apache.org
> For additional commands, e-mail: users-help@httpd.apache.org
>
>
>
---------------------------------------------------------------------
The official User-To-User support forum of the Apache HTTP Server Project.
See <URL:http://httpd.apache.org/userslist.html> for more info.
To unsubscribe, e-mail: users-unsubscribe@httpd.apache.org
" from the digest: users-digest-unsubscribe@httpd.apache.org
For additional commands, e-mail: users-help@httpd.apache.org
Re: [users@httpd] A NetHack-esque Journey of the dark arts for DBD(mysql) under httpd-2.2.3
Posted by Ben <be...@redsnapper.net>.
The next installment...
It was time for me to add some meaningful messages to mod_authn_dbd.c
Heads up to you apache developers: Try to be more imaginative when
writing error messages - At least make each message unique!!
There are six "Error looking up %s in database." errors in
mod_autn_dbd.c - why not be a little bit more informative?
Well, the good news is that I managed to get authentication working -
after a new build and a couple of conf tweaks.
Here is a breakdown of some of the [crit] messages - and what they
appear to mean..
(70023)This function has not been implemented on this platform: DBD:
driver for [DBDriver unset] not available
(70023)This function has not been implemented on this platform: DBD:
failed to initialise
---- This means that you have stuck the DBD settings inside a
VirtualHost! It will pass configtest, it is (supposedly) supported,
but it will fry your httpd! Rawr!
Here is a breakdown of some of the [error] messages - and what they
appear to mean..
"DBD: No driver for mysql" Your mysql_client_r is not linked
properly! Try: ldd (apache install directory)/lib/libaprutil-1.so |
grep libmysqlclient_r
"Error looking up testuser in database" One of a whole bunch of
things - including no database connection
"user testuser not found ..." The user is
not in the table specified - (or the table doesn't exist, or the
query fails... )
"user testuser: authentication failure ..." Good news! The user
table / record was found - just the password was entered wrong! (or
the field isn't encrypted)
There is no return message saying whether or not the sql query
actually worked. It would be nice for a developer to stick in a debug
of the queries themselves.. (hint!)
Also, LogDebug for .. dynamic library loading, function calling, sql
connections, database connections, handle creation, sql query
creation, the actual sql queries, the execution, an ERROR for sql
error messages, LogDebug for number of records returned, the value of
the password returned the and the value of the encrypted password it
is trying to match - you know what LogDebug is useful for???
Debugging. (hint!) (hint!) (hint!)
Also, I got a bit wobbly about which encrypt function to use.. So I
tried the lot, and it turned out to be encrypt (doh)
An example insert user query is:
insert into users set user='test5', password=encrypt('test','test');
The conf settings that are working for me are:
<VirtualHost xxxxx:80>
DBDParams "dbname=client06 user=dbdauth"
[........]
<Directory />
AuthType Basic
AuthName "Database access"
AuthBasicProvider dbd
Require valid-user
AuthDBDUserPWQuery "select password from users where user = %s"
</Directory>
[........]
</VirtualHost>
Also - at the top of the httpd.conf file
DBDriver mysql
DBDPersist Off
DBDMin 1
DBDKeep 1
DBDMax 1
#DBDExptime 10
Now all but the first of these settings remain mysterious and magical
to me - but I think I have trouble with other settings.
I still don't know what causes a massive increase of active httpd
threads that invariably burns the server to the ground - but hey..
here be dragons ...
I am a little worried about touching anything. Eighteen hours and
several levels of pain later - my keyboard dripping with the blood of
ripped code - directories filled with random notes and partial files
- and things work - but my confidence is in tatters.
Unless the server keels over in the next few days - this adventure
appears to be done!
---------------------------------------------------------------------
The official User-To-User support forum of the Apache HTTP Server Project.
See <URL:http://httpd.apache.org/userslist.html> for more info.
To unsubscribe, e-mail: users-unsubscribe@httpd.apache.org
" from the digest: users-digest-unsubscribe@httpd.apache.org
For additional commands, e-mail: users-help@httpd.apache.org
Re: [users@httpd] A NetHack-esque Journey of the dark arts for DBD(mysql) under httpd-2.2.3
Posted by Gregor Schneider <rc...@googlemail.com>.
ok - i just got my popcorn & beer ;)
cheers!
greg
ps: thank god there are some volunteers like you who test the new
stuff so we don't have to ;)
--
what's puzzlin' you, is the nature of my game
---------------------------------------------------------------------
The official User-To-User support forum of the Apache HTTP Server Project.
See <URL:http://httpd.apache.org/userslist.html> for more info.
To unsubscribe, e-mail: users-unsubscribe@httpd.apache.org
" from the digest: users-digest-unsubscribe@httpd.apache.org
For additional commands, e-mail: users-help@httpd.apache.org