From da2aa667865606ade6a47d060ab12c851231933c Mon Sep 17 00:00:00 2001 From: Christopher Powell Date: Thu, 14 Nov 2002 03:51:37 +0000 Subject: Monster changes en route to 1.17. Esp. with new documentation. --- CHANGELOG | 53 +- Documentation/documentation.lyx | 2969 +++++++++++++++++++++++++++++++++++++++ Documentation/documentation.tex | 1534 ++++++++++++++++++++ INSTALL | 303 ++-- Makefile | 147 +- README | 133 +- access_log.sql | 25 - create_tables.sql | 50 + make_combined_log.pl | 4 +- mod_log_sql.c | 639 ++++++--- 10 files changed, 5289 insertions(+), 568 deletions(-) create mode 100644 Documentation/documentation.lyx create mode 100644 Documentation/documentation.tex delete mode 100644 access_log.sql create mode 100644 create_tables.sql diff --git a/CHANGELOG b/CHANGELOG index 1d38ca9..8be7dda 100644 --- a/CHANGELOG +++ b/CHANGELOG @@ -1,9 +1,10 @@ -$Id: CHANGELOG,v 1.15 2002/09/04 18:46:00 helios Exp $ +$Id: CHANGELOG,v 1.16 2002/11/14 03:51:34 helios Exp $ TODO: * Rethink documentation flow and rewrite? -* Port connection portion to other DBMS? Genericize the module? Start with PostgreSQL. +* Port connection portion to other DBMS? Genericize the module? Start with +PostgreSQL. * check for mandatory conf directives / syntax quit if not * GNU autoconf * merge server config into vh config @@ -14,13 +15,36 @@ TODO: * new format char: IP as bigint? * socket-based middleman daemon with configurable conns? -* go ahead and allow dashes - quote the table name! -* maxlength of request field should be longer + +* Cookies in separate table +* ignore by cookie + + CHANGES: 1.17: +* In make_combined_log.pl, backtick-quoted the table name and added an + order-by clause within the select statement. +* TCP port number for db connection is now configurable using LogSqlTCPPort +* New directive LogSQLForcePreserve sends all log entries directly to the + preserve file and *entirely bypasses* the db. Useful for debugging, but + can be dangerous if you forget it's set! +* Table names are now quoted in all cases with backticks in order to permit + names containing dashes (since many hostnames contain dashes). +* The request_method field is now created as a varchar(10) instead of (6), + after it was pointed out to me that some methods can be longer than 6. +* New directive LogSQLMachineID sets a string identifier for the webserver + machine. This is useful if you have a cluster of many webservers and you + want to differentiate between them in the logs: you can tell which log + entries came from each machine and thereby analyze your loadbalacing + performance. Activate with the TransferLogFormat character 'M'. Much + faster than doing some sort of wacky IP addr lookup via local_addr(). +* Added an alternative way of logging cookies. If you need to associate + multiple cookies with each request, the new way involves using + LogSQLWhichCookies (note the plural) and LogSQLCookieLogTable. + * Renamed the module mod_log_sql to conform to the project goal of database-inspecificity. * Added capability of logging Notes field. This is useful for folks using @@ -35,11 +59,11 @@ CHANGES: be opened.) * Changed default socket file to /tmp/mysql.sock because that's the default on a compiled MySQL. -* Also check for '-' when I'm creating the table when MySQLMassVirtualHosting - is on, and change it to underscore. (Already did this with '.') +* Put back-quotes (`) around table name so that names with dashes are + legal. * Took away the prepend of /tmp to the preserve filename. Now the user can specify whatever local path they want. I figure that - filesystem permissions will prevent people from doing anything + filesystem permissions will prevent people from doing anything really dumb, and people have requested this change. * Better checking in the extract_cookie routine. Before, it could segfault if a person specified "c" but didn't define MySQLWhichCookie. @@ -58,9 +82,10 @@ CHANGES: * Minor: remind user not to set createtables when massvirtual is on. * If Apache started but MySQL was unavailable, the module would do everything it was supposed to (preserve entries, etc.) but not notify - the sysadmin. Added a log message to alert sysadmin if MySQL is + the sysadmin. Added a log message to alert sysadmin if MySQL is unreachable at startup. * New config key 'I' to extract & log unique_id, provided by mod_unique_id. + unique_id is the key that links separate tables (access, notes, etc.) * Migrated to the Artistic License (as used by Perl). * Moved table creation into its own callable function * Robustifying table creation @@ -161,7 +186,7 @@ CHANGES: (e.g. GET, PUT, etc.) * New config capabilities: 'z' 'q' & 'Q' to log SSL_CIPHER, SSL_CIPHER_USEKEYSIZE and SSL_CIPHER_ALGKEYSIZE. These require - openssl and glibc-devel to be installed. (See the online + openssl and glibc-devel to be installed. (See the online directives documentation and FAQ.) * Fixed a bug in make_combined_log.pl that caused it to generate incorrect output on single-digit days. @@ -182,11 +207,13 @@ CHANGES: MySQLTransferLogFormat Instruct the module what information to log to the MySQL transfer log MySQLRefererIgnore - List of referers to ignore, accesses that match will not be logged to MySQL + List of referers to ignore, accesses that match will not be logged to +MySQL MySQLRequestIgnore List of URIs to ignore, accesses that match will not be logged to MySQL MySQLRemhostIgnore - List of remote hosts to ignore, accesses that match will not be logged to MySQL + List of remote hosts to ignore, accesses that match will not be logged +to MySQL MySQLDatabase The name of the MySQL database for logging MySQLWhichCookie @@ -256,7 +283,7 @@ CHANGES: more. * Now include a headerfile (http_log.h) that was missing. Its absence was giving us this warning message: "implicit declaration of - function `ap_log_error_old'." + function `ap_log_error_old'." * For numerics that Apache customarily logs as a "-" we now log a zero instead of a -1. This seems to be more intuitive, e.g. in the "bytes_sent" column. @@ -285,7 +312,7 @@ CHANGES: 1.03: * Changed the check for 'mysql server has gone away' to be case - insensitive, so that it works with MySQL 3.21 + insensitive, so that it works with MySQL 3.21 * Changed the behavior so that a link isn't established until it's necessary (e.g., if SQL logging is used for one virtual IP, a link won't be opened until there's an access to that IP). diff --git a/Documentation/documentation.lyx b/Documentation/documentation.lyx new file mode 100644 index 0000000..48c3968 --- /dev/null +++ b/Documentation/documentation.lyx @@ -0,0 +1,2969 @@ +#LyX 1.1 created this file. For more info see http://www.lyx.org/ +\lyxformat 218 +\textclass article +\language english +\inputencoding default +\fontscheme default +\graphics default +\paperfontsize 10 +\spacing single +\papersize letterpaper +\paperpackage a4 +\use_geometry 1 +\use_amsmath 0 +\paperorientation portrait +\paperwidth 8.5in +\paperheight 11in +\leftmargin 1in +\topmargin 0.5in +\rightmargin 1in +\bottommargin 1in +\secnumdepth 3 +\tocdepth 3 +\paragraph_separation indent +\defskip medskip +\quotes_language swedish +\quotes_times 2 +\papercolumns 1 +\papersides 1 +\paperpagestyle default + +\layout Title +\added_space_top vfill \added_space_bottom vfill +Installing and Running mod_log_sql +\layout Author + +Christopher Powell +\layout Date + +11/13/02 +\layout Standard +\pagebreak_bottom + +\begin_inset LatexCommand \tableofcontents{} + +\end_inset + + +\layout Section + +Introduction +\layout Subsection + +Homepage +\layout LyX-Code + +http://www.grubbybaby.com/mod_log_sql/ +\layout Subsection + +Summary +\layout Standard + +This Apache module will permit you to log Apache accesses (and a lot of + related information) to a SQL database. + Unlike logging to a flat text file (which is standard in Apache), there + are a lot of powerful advantages to logging to SQL. + This module can either replace or happily coexist with Apache's text file + logging facility. +\layout Subsection + +Approach +\layout Standard + +This project was formerly known as mod_log_mysql. + It has been renamed to mod_log_sql in order to reflect the project goal + of database-inspecificity. + The module currently supports MySQL, and development for other database + backends is underway. +\layout Standard + +In order to save speed and overhead, links are kept alive in between queries. + This module uses one SQL link per httpd child. + Among other things, this means that this module supports logging into only + one MySQL server, and for now, also, only one SQL database. + But that's a small tradeoff compared to the blinding speed of this module. +\layout Standard + +Virtual hosts are supported in the same manner they are in the regular logging + modules. + You define some basic 'global' directives in the main server config, then + you define more specific 'local' directives inside each virtualhost stanza. +\layout Standard + +SQL links are opened by each child process when it is born. + Error reporting is robust throughout and will let you know about database + issues in the standard Apache error-log for the server or virtual server. +\layout Standard + +A robust "preserve" capability has now been implemented. + This permits the module to preserve any failed INSERT commands to a local + file on its machine. + In any situation that the database is unavailable -- e.g. + the network fails, you reboot the db host, etc. + -- mod_log_sql will note this in the error log and begin appending its + log entries to the preserve file (which is created with the user & group + ID of the running Apache process, e.g. + "nobody" on many Linux installations). + When your database becomes available again, mod_log_sql seamlessly resumes + logging to it. + When convenient for the sysadmin, he/she can easily import the preserve + file into the database because it is simply a series of SQL insert statements: +\layout LyX-Code + +# mysql -uadminuser -p mydbname < /tmp/mysql-preserve +\layout Subsection + +Supported directives +\layout Standard + +Please see the web-based documentation for full explanation of all supported + run-time directives. +\layout Standard + +http://www.grubbybaby.com/mod_log_sql/directives.html +\layout Standard + +See the FAQ for some handy examples: +\layout Standard + +http://www.grubbybaby.com/mod_log_sql/faq.html +\layout Subsection + +What gets logged by default? +\layout Standard + +All the data that would be contained in the "Combined Log Format" is logged + by default, plus a little extra. + Your best bet is to begin by accepting this default, then later customize + the log configuration based on your needs. +\layout Standard + +The online documentation of the run-time directives includes a full explanation + of what you can log, including examples. +\layout Subsection + +Minor Notes +\layout Itemize + +Note which directives go in the 'main server config' and which directives + apply to the 'virtual host config'. + This is made clear in the directive documentation. +\layout Itemize + +The 'time_stamp' field is stored in an UNSIGNED INTEGER column, in the standard + unix "seconds since 1/1/1970 12:00:00" format. + This is superior to storing the access time as a string due to size requirement +s: an UNSIGNED INT type fits in 4 bytes, whereas the Apache date string + (e.g. + "18/Nov/2001:13:59:52 -0800") requires 26 bytes -- significantly larger, + and those extra 22 bytes will add up over the thousands of accesses that + a busy server will experience. + Besides, an INT type is far more flexible for comparisons, etc. +\begin_deeper +\layout Standard + +In MySQL 3.21 and above you can easily convert this to a human readable format + using from_unixtime(), e.g.: +\layout LyX-Code + +select remote_host,request_uri,from_unixtime(time_stamp) from access_log; +\layout Standard + +The enclosed perl program +\begin_inset Quotes sld +\end_inset + +make_combined_log.pl +\begin_inset Quotes srd +\end_inset + + shows how you can extract your access records in a format that is completely + Combined Log Format compliant. + You can then feed this to your favorite web log analysis tool. +\end_deeper +\layout Itemize + +The table's string values can be CHAR or VARCHAR, at a length of your choice. + VARCHAR is superior because it truncates long strings; CHAR types are fixed-len +gth and will be padded with spaces. + Just like the time_stamp described above, that kind of space waste will + add up over thousands of records. +\layout Itemize + +Be careful not to go overboard setting fields to NOT NULL. + If a field is marked NOT NULL then it must contain data in the INSERT or + the INSERT will fail, sometimes in a really mysterious way to the frustrated + sysad. +\layout Itemize + +Apache normally logs numeric fields with a '-' character to mean "not applicable +," e.g. + bytes_sent on a request with a 304 response code. + Since '-' is an illegal character in an SQL numeric field, such fields + are assigned the value 0 instead of '-' which, of course, makes perfect + sense anyway. +\layout Subsection + +Author / Maintainer +\layout Standard + +The actual logging code was taken from the already existing flat file text + modules, so all that credit goes to the Apache Server group. +\layout Standard + +The MySQL routines and directives were added by Zeev Suraski . +\layout Standard + +All changes from 1.06+ and the new documentation were added by Chris Powell + . + It seems that the module had fallen into the "unmaintained" category -- + it hadn't been updated since 1998 -- so Chris adopted it as the new maintainer. +\layout Section + +Installation +\layout Subsection + +Requirements +\layout Itemize + +A compatible system. + I have run mod_log_sql on Red Hat based systems (Red Hat, Mandrake). + These instructions should easily adapt to any modern distro. +\layout Itemize + +Apache 1.2 or 1.3 installed. + (I run 1.3.22 and it works fine). + You should have already successfully compiled Apache and know what you're + doing there. +\layout Itemize + +The MySQL development headers. + This is called different things on different distros. + For example, Red Hat 6.x called this RPM "MySQL-devel" whereas Mandrake + calls it "libmysql10-devel". +\layout Itemize + +MySQL >= 3.23.15 configured, installed and running on either localhost or + an accessible networked machine. + You should already have a basic understanding of MySQL and how it functions. +\layout Itemize + +Again, basic administrative skills with Apache and MySQL. + I try to make things as easy as possible in this file, but its purpose + is not to be an administrative tutorial. +\layout Itemize + +Additionally, if you want to be able to log SSL information such as keysize + or cipher, you need OpenSSL and glibc-devel installed. + Both are available as RPMs. +\layout Subsection + +Do I want a DSO? +\layout Standard + +You need to know the answer to this question before you proceed. + The answer is pretty straightforward: what have you done in the past? If + you like all your Apache modules to be dynamic, then you should keep doing + that. + If you're more of an old-school type and prefer to compile the modules + right into apache, do that. + Both methods work equally well. +\layout Standard + +FWIW, the DSO method is more modern and increasing in popularity because + apxs takes care of a lot of dirty little details for you. + As you'll see below, the static-module method is a little more complex. +\layout Subsection + +Installation as an Apache DSO (Preferred) +\layout Enumerate + +Perform all the following steps as root so that you have install privs, + etc. + Unpack the archive into a working directory. +\begin_deeper +\layout LyX-Code + +# tar zxf mod_log_sql.tar.gz -C /usr/local/src # cd /usr/local/src/mod_log_sql +\end_deeper +\layout Enumerate + +Edit Makefile for your system. +\newline + +\begin_deeper +\layout Standard + +NECESSARY: +\layout Itemize + +The location where you installed Apache -- usually /usr/local/apache, 'locate + apxs' can help you find it. +\layout Itemize + +The location of your MySQL libraries, find using 'locate libmysqlclient' +\layout Itemize + +The location of your MySQL header files, find using 'locate mysql.h' +\layout Standard + +OPTIONAL if you have included mod_ssl in Apache and want to log SSL data + such as keysize and cipher type: +\layout Itemize + +The location of your SSL header files, find using 'locate mod_ssl.h' +\layout Standard + +Now that you know these things, edit Makefile and replace the stock values + with your own. +\layout Standard + +IMPORTANT: If you are not logging SSL info, comment out MODSSLHDRS by putting + a # character in front of it: +\layout LyX-Code + +#MODSSLHDRS=/usr/include/... +\end_deeper +\layout Enumerate + +Instruct apxs to compile the module as a DSO. +\begin_deeper +\layout LyX-Code + +# make dso +\end_deeper +\layout Enumerate + +You should see output similar to the following: +\begin_deeper +\layout LyX-Code + +/usr/local/Apache/bin/apxs -Wc,-O2 -Wc,-Wall -Wc,-DEAPI -c -I/usr/include/mysql + -I/usr/local/src/apache_1.3.27-dso/src/modules/ssl -L/usr/lib -lmysqlclient + -lz mod_log_sql.c +\layout LyX-Code + +gcc -DLINUX=22 -DNO_DBM_REWRITEMAP -DMOD_SSL=208111 -DUSE_HSREGEX -DEAPI + -DUSE_EXPAT -I../lib/expat-lite -fpic -DSHARED_CORE -DSHARED_MODULE -I/usr/local/ +Apache/include -O2 -Wall -DEAPI -I/usr/include/mysql -I/usr/local/src/apache_1.3.2 +7-dso/src/modules/ssl -c mod_log_sql.c +\layout LyX-Code + +gcc -shared -o mod_log_sql.so mod_log_sql.o -Wc,-O2 -Wc,-Wall -Wc,-DEAPI -L/usr/li +b -lmysqlclient -lz -lm -lcrypt -ldb +\layout Standard + +You should see no errors and have a file called "mod_log_sql.so" in your + directory. +\end_deeper +\layout Enumerate + +Instruct apxs to install the DSO. +\begin_deeper +\layout LyX-Code + +# make dsoinstall +\layout Standard + +You should see output similar to the following: +\layout LyX-Code + +/usr/local/Apache/bin/apxs -i mod_log_sql.so +\layout LyX-Code + +cp mod_log_sql.so /usr/local/Apache/libexec/mod_log_sql.so +\layout LyX-Code + +chmod 755 /usr/local/Apache/libexec/mod_log_sql.so +\end_deeper +\layout Enumerate + +Module ordering within httpd.conf is important. + If you are logging SSL, you must make sure that +\begin_deeper +\layout LyX-Code + +LoadModule ssl_module libexec/libssl.so +\layout Standard + +comes before +\layout LyX-Code + +LoadModule sql_log_module libexec/mod_log_sql.so +\layout Standard + +If you don't, you will get this error when you start Apache: +\layout LyX-Code + +/usr/local/apache/libexec/mod_log_mysql.so: undefined symbol: ssl_var_lookup +\layout LyX-Code + +/usr/local/apache/bin/apachectl startssl: httpd could not be started +\layout Standard + +(Because mod_log_sql doesn't yet have the required symbols that mod_ssl + provides.) +\layout Standard + +Now skip below to the "Configuration" section. +\end_deeper +\layout Subsection + +Installation as a static module compiled into httpd +\layout Enumerate + +Perform all the following steps as root so that you have install privs, + etc. +\layout Enumerate + +Unpack the archive into a working directory. +\begin_deeper +\layout LyX-Code + +# tar zxf mod_log_sql.tar.gz -C /usr/local/src # cd /usr/local/src/mod_log_sql +\end_deeper +\layout Enumerate + + +\begin_inset LatexCommand \label{step:editMF} + +\end_inset + +Edit Makefile for your system. +\newline + +\begin_deeper +\layout Standard + +NECESSARY: +\layout Itemize + +The location where you installed Apache -- usually /usr/local/apache, 'locate + apxs' can help you find it. + +\layout Itemize + +The location of your Apache *sources*, find using 'locate ABOUT_APACHE' + +\layout Itemize + +The location of your MySQL header files, find using 'locate mysql.h' +\layout Itemize + +The location of your MySQL libraries, find using 'locate libmysqlclient' +\layout Standard + +OPTIONAL if you have included mod_ssl in Apache and want to log SSL data + such as keysize and cipher type: +\layout Itemize + +The location of your mod_ssl header files, find using 'locate mod_ssl.h' +\layout Itemize + +The location of your OpenSSL header files, find using 'locate x509.h' +\layout Itemize + +The location of your db1 header files, find using 'locate mpool.h' +\layout Standard + +Now that you know these things, edit Makefile and replace the stock values + with your own. +\layout Standard + +IMPORTANT: If you are not logging SSL info, comment out MODSSLHDRS, OPNSSLHDRS + and DB1HDRS by putting a # character in front of each one, e.g. + #OPNSSLHDRS=/usr/include/... +\end_deeper +\layout Enumerate + +# make static +\layout Enumerate + +# make statinstall +\layout Enumerate + +Change to your Apache source dir. +\begin_deeper +\layout LyX-Code + +# cd /usr/local/src/apache-1.3.22/src +\end_deeper +\layout Enumerate + +Re-make your httpd binary as follows. +\begin_deeper +\layout Enumerate + +Edit Configuration.apaci as follows... +\begin_deeper +\layout Itemize + +Append the following string to the EXTRA_LIBS= line. + ("/usr/lib/mysql" is from step +\begin_inset LatexCommand \ref{step:editMF} + +\end_inset + +, where your MySQL libraries live): +\layout LyX-Code + +-L/usr/lib/mysql -lmysqlclient -lm -lz +\layout Itemize + +Find the mod_log_config.o line, and add this line immediately after it: +\layout LyX-Code + +AddModule modules/sql/mod_log_sql.o +\end_deeper +\layout Enumerate + +# cp Configuration.apaci Configuration +\layout Enumerate + +# ./Configure +\layout Enumerate + +# make +\layout Enumerate + +# strip httpd +\end_deeper +\layout Enumerate + +Test your new apache binary: +\begin_deeper +\layout LyX-Code + +# ./httpd -l +\end_deeper +\layout Enumerate + +You should see something like: +\begin_deeper +\layout LyX-Code + +Compiled-in modules: +\layout LyX-Code + +http_core.c +\layout LyX-Code + +mod_log_sql.c <-- That's the line you're looking for. +\layout LyX-Code + +mod_env.c +\layout LyX-Code + +mod_log_config.c +\layout LyX-Code + +mod_mime.c +\layout LyX-Code + +mod_negotiation.c ... +\layout LyX-Code + +etc... +\end_deeper +\layout Enumerate + +Install your httpd binary. + Copy it over your old httpd binary, wherever it lives. + You can and should rename your old httpd first so that you can easily revert + to that working version in case of bugs with the new version. +\begin_deeper +\layout LyX-Code + +# /etc/rc.d/init.d/httpd stop +\layout LyX-Code + +# mv /usr/local/Apache/bin/httpd ~/httpd-save +\layout LyX-Code + +# cp -f ./httpd /usr/local/Apache/bin/ +\end_deeper +\layout Section + +Configuration +\layout Subsection + +Preparing MySQL +\layout Standard + +You have to prepare the database to receive data from mod_log_sql, and set + up run-time directives in httpd.conf to control how and what mod_log_sql + logs. +\layout Standard + +This section will discuss how to get started with a basic config. + Full documentation of the run-time directives is available here: http://www.grub +bybaby.com/mod_log_sql/directives.html +\layout Enumerate + +mod_log_sql can make its own tables on-the-fly, or you can pre-make the + tables by hand. + The advantage of letting the module make the tables is ease-of-use, but + for raw performance you will want to pre-make the tables in order to save + overhead. + In this basic setup we'll let the module create tables for us. +\layout Enumerate + +We still need to have a logging database created and ready, so run the MySQL + command line client and create a database: +\begin_deeper +\layout LyX-Code + +# mysql -uadmin -pmypassword mysql> create database apachelogs; +\end_deeper +\layout Enumerate + +If you want to hand-create the tables, run the enclosed 'create-tables' + SQL script as follows: +\begin_deeper +\layout LyX-Code + +mysql> source create_tables.sql +\end_deeper +\layout Enumerate + +Create a specific MySQL userid that httpd will use to authenticate and enter + data. + This userid need not be an actual Unix user. + It is a userid internal to MySQL with specific privileges. + In the following example command, "apachelogs" is the database, "loguser" + is the userid to create, "my.apachemachine.com" is the name of the Apache + machine, and "l0gger" is the password to assign. + Choose values that are different from these examples. +\begin_deeper +\layout LyX-Code + +mysql> grant insert,create on apachelogs.* to loguser@my.apachemachine.com + identified by 'l0gger'; +\end_deeper +\layout Enumerate + +You may be especially security-paranoid and not want "loguser" to have "create" + capability within the "apachelogs" databse. + You can disable that but the cost is that you cannot use the module's automatic +-table-creation feature. + If that's an acceptable cost, hand-create the tables as described in step + 1 and use the following GRANT statement instead of the one above: +\begin_deeper +\layout LyX-Code + +mysql> grant insert on apachelogs.* to loguser@my.apachemachine.com identified + by 'l0gger'; +\end_deeper +\layout Enumerate + +Enable full logging of your MySQL daemon (at least temporarily for debugging + purposes) if you don't do this already. + Edit /etc/my.cnf and add the following line to your [mysqld] section: +\begin_deeper +\layout LyX-Code + +log=/var/log/mysql-messages +\layout Standard + +Then restart MySQL. +\layout LyX-Code + +# /etc/rc.d/init.d/mysql restart +\end_deeper +\layout Subsection + +Preparing Apache +\layout Enumerate + +Tell the module what database to use and the appropriate authentication + information. +\begin_deeper +\layout Description + +EXAMPLE: Use the MySQL database called "apachelogs" running on "dbmachine.foo.com". + The module uses username "loguser" and password "l0gger" to authenticate + to the database. + The log entries will be INSERTed into the table called "access_log". +\layout Standard + +So, edit httpd.conf and insert the following lines somewhere AFTER any LoadModule + / AddModule statements. + Make sure these statements are "global," i.e. + not inside any VirtualHost stanza. +\layout LyX-Code + +LogSQLDatabase apachelogs +\layout LyX-Code + +LogSQLLoginInfo dbmachine.foo.com loguser l0gger +\layout LyX-Code + +LogSQLCreateTables on +\end_deeper +\layout Enumerate + +If your database resides on localhost instead of another host, specify the + MySQL server's socket file as follows: +\begin_deeper +\layout LyX-Code + +LogSQLSocketFile /your/path/to/mysql.sock +\end_deeper +\layout Enumerate + +The actual logging is set up on a virtual-host-by-host basis. + So, skip down to the virtual host you want to set up. + Instruct this virtual host to log entries to the table +\begin_inset Quotes sld +\end_inset + +access_log +\begin_inset Quotes srd +\end_inset + + by inserting a LogSQLTransferLogTable directive. + (The LogSQLTransferLogTable directive is the minimum required to log -- + other directives simply tune the module's behavior.) +\begin_deeper +\layout LyX-Code + + +\layout LyX-Code + +[snip] +\layout LyX-Code + +LogSQLTransferLogTable access_log +\layout LyX-Code + +[snip] +\layout LyX-Code + + +\end_deeper +\layout Enumerate + +Restart apache. +\begin_deeper +\layout LyX-Code + +# /etc/rc.d/init.d/httpd start +\end_deeper +\layout Subsection + +Testing the module +\layout Enumerate + +Load your web site in a browser to trigger some hits, then confirm that + the entries are being successfully logged: +\begin_deeper +\layout LyX-Code + +# mysql -hmysql.host.com -umysqladmin -p -e "select * from access_log" apachelogs + +\layout LyX-Code + +Enter password: +\layout LyX-Code + +(Several lines of output should follow, corresponding to your hits on the + site.) +\layout Standard + +You now have basic functionality. + Don't disable your regular Apache logs until you feel comfortable that + the database is behaving as you'd like and that things are going well. +\end_deeper +\layout Enumerate + +If you do not see any entries in the access_log, then something is preventing + the inserts from happening. + This problem could be caused by several things: +\begin_deeper +\layout Itemize + +Improper privileges set up in the MySQL database +\layout Itemize + +You aren't hitting a VirtualHost that has a LogSQLTransferLogTable entry + +\layout Itemize + +You didn't specify the right host +\layout Standard + +If you have confirmed your LogSQL* directives and know them to be correct, + you should examine the httpd server logs for mod_log_sql messages; the + module will offer hints as to why it cannot connect, etc. + Also examine the MySQL log that you established in step 3. + Ensure that the INSERTs are not being rejected because of a malformed table + entry or other clerical error. + If you see no INSERT attempts in the log, the module isn't successfully + connecting to the database. +\layout Standard + +The next thing to do is recompile the module with debugging output activated. + change the "#undef DEBUG" on line 8 of mod_log_sql.c to "#define DEBUG" + and recompile/reinstall. + The module will now output copious notes about what it is doing, and this + will help you (and the maintainer) solve the problem. +\end_deeper +\layout Standard + +You can now activate the advanced features of mod_log_sql. + These are all described in the next section. +\layout Subsection + +Configuration directive reference +\layout Subsubsection + +LogSQLLoginInfo +\layout LyX-Code + + +\series bold +MANDATORY +\series default + +\layout LyX-Code + +Syntax: LogSQLLoginInfo host user password +\layout LyX-Code + +Example: LogSQLLoginInfo foobar.baz.com logwriter passw0rd +\layout LyX-Code + +Context: server config +\layout Quote + +Defines the general parameters of the MySQL host to which you will be logging. + host is the hostname or IP address of the MySQL machine. + user is the MySQL userid (not a Unix userid!) with INSERT privileges on + the table defined in LogSQLTransferLogTable. + password is that user's password. + This is defined only once in the httpd.conf file. +\layout Subsubsection + +LogSQLDatabase +\layout LyX-Code + + +\series bold +MANDATORY +\layout LyX-Code + +Syntax: LogSQLDatabase database +\layout LyX-Code + +Example: LogSQLDatabase loggingdb +\layout LyX-Code + +Context: server config +\layout Quote + +Defines the database that is used for logging. + database must be operating on the MySQL host defined in LogSQLLoginInfo. + This is defined only once in the httpd.conf file. +\layout Subsubsection + +LogSQLTransferLogTable +\layout LyX-Code + + +\series bold +MANDATORY +\layout LyX-Code + +Syntax: LogSQLTransferLogTable table-name +\layout LyX-Code + +Example: LogSQLTransferLogTable access_log_table +\layout LyX-Code + +Context: virtual host +\layout Quote + +Defines which table is used for logging of Apache's transfers; this is analogous + to Apache's TransferLog directive. + table-name must be a valid table within the database defined in LogSQLDatabase. +\layout Quote + +This directive is not necessary if you declare LogSQLMassVirtualHosting + On, since that directive activates dynamically-named tables. + If you attempt to use LogSQLTransferLogTable at the same time a warning + will be logged and it will be ignored, since LogSQLMassVirtualHosting takes + priority. +\layout Subsubsection + +LogSQLRequestIgnore +\layout LyX-Code + +Syntax: LogSQLRequestIgnore req1 req2 req3 ... + reqN +\layout LyX-Code + +Example: LogSQLRequestIgnore root.exe cmd.exe default.ida favicon.ico +\layout LyX-Code + +Context: virtual host +\layout Quote + +Lists a series of strings that, if present in the URI, will cause that request + NOT to be logged. + This directive is useful for cutting down on log clutter when you KNOW + that you do not want to log requests for certain objects. + (The example above is a genuinely useful one; it will prevent logging of + many common Microsoft-based worm intrusion attempts, as well as those ridiculou +s requests for the favicon.) +\layout Quote + +Each string is separated by a space, and NO regular expressions or globbing + are allowed. + Each string is evaluated as a substring of the URI using strstr(). +\layout Subsubsection + +LogSQLRemhostIgnore +\layout LyX-Code + +Syntax: LogSQLRemhostIgnore host1 host2 host3 ... + hostN +\layout LyX-Code + +Example: LogSQLRemhostIgnore localnet.com +\layout LyX-Code + +Context: virtual host +\layout Quote + +Lists a series of strings that, if present in the REMOTE_HOST, will cause + that request NOT to be logged. + This directive is useful for cutting down on log clutter when you KNOW + that you do not want to log requests from certain hosts, such as your own + internal network machines. +\layout Quote + +Each string is separated by a space, and NO regular expressions or globbing + are allowed. + Each string is evaluated as a substring of the REMOTE_HOST using strstr(). +\layout Subsubsection + +LogSQLRefererIgnore +\layout LyX-Code + +Syntax: LogSQLRefererIgnore ref1 ref2 ref3 ... + refN +\layout LyX-Code + +Example: LogSQLRefererIgnore google.com +\layout LyX-Code + +Context: virtual host +\layout Quote + +Lists a series of strings that, if present in the HTTP_REFERER, will cause + that request NOT to be logged. + This directive is useful for cutting down on log clutter when you know + that you do not want to log requests from certain referers. +\layout Quote + +Each string is separated by a space, and NO regular expressions or globbing + are allowed. + Each string is evaluated as a substring of the HTTP_REFERER using strstr(). +\layout Subsubsection + +LogSQLWhichCookie +\layout LyX-Code + +Syntax: LogSQLWhichCookie cookiename +\layout LyX-Code + +Example: LogSQLWhichCookie Clicks +\layout LyX-Code + +Default: None. + You must set this if you wish to capture cookies. +\layout LyX-Code + +Context: virtual host +\layout Quote + +In HTTP, cookies have names to distinguish them from each other. + Using mod_usertrack, for example, you can give your user-tracking cookies + a name with the CookieName directive. +\layout Quote + +If you have activated cookie logging in LogSQLTransferLogFormat, then LogSQLWhic +hCookie tells mod_log_sql which cookie to log. + This is useful because many times you will be setting and receiving more + than one cookie from a client; without this directive you'd be unable to + choose which cookie is your mod_usertrack cookie. +\layout Quote + +Note: although this was intended for people who are using mod_usertrack + to set user-tracking cookies, you aren't restricted in any way. + You can choose which cookie you wish to log to the database, and it doesn't + necessarily have to have anything to do with mod_usertrack. +\layout Subsubsection + +LogSQLCreateTables +\layout LyX-Code + +Syntax: LogSQLCreateTables flag +\layout LyX-Code + +Example: LogSQLCreateTables On +\layout LyX-Code + +Default: Off +\layout LyX-Code + +Context: server config +\layout Quote + +mod_log_sql now has the ability to create its tables on-the-fly. + The advantage to this is convenience: you don't have to execute any SQL + by hand to prepare the table. + This is especially helpful for people with lots of virtual hosts (who should + also see the LogSQLMassVirtualHosting directive). +\layout Quote + +There is a slight disadvantage: if you wish to activate this feature, then + the user specified by LogSQLLoginInfo must have CREATE privileges on the + database. + In an absolutely paranoid, locked-down situation you may only want to grant + your mod_log_sql user INSERT privileges on the database; in that situation + you are unable to take advantage of LogSQLCreateTables. + But most people -- even the very security-conscious -- will find that granting + CREATE on the logging database is reasonable. +\layout Quote + +This is defined only once in the httpd.conf file. +\layout Subsubsection + +LogSQLMassVirtualHosting +\layout LyX-Code + +Syntax: LogSQLMassVirtualHosting flag +\layout LyX-Code + +Example: LogSQLMassVirtualHosting On +\layout LyX-Code + +Default: Off +\layout LyX-Code + +Context: server config +\layout Quote + +If you administer a site hosting many, many virtual hosts then this option + will appeal to you. + If you activate LogSQLMassVirtualHosting then several things happen: +\begin_deeper +\layout Itemize + +the on-the-fly table creation feature is activated automatically +\layout Itemize + +the transfer log table name is dynamically set from the virtual host's name + (example: a virtual host www.grubbybaby.com gets logged to table access_www_grubb +ybaby_com) +\layout Itemize + +which, in turn, means that each virtual host logs to its own segregated + table. + Because there is no data shared between virtual servers you can grant your + users access to the tables they need; they will be unable to view others' + data. +\end_deeper +\layout Quote + +This is a huge boost in convenience for sites with many virtual servers. + Activating LogSQLMassVirtualHosting obviates the need to create every virtual + server's table and provides more granular security possibilities. +\layout Quote + +This is defined only once in the httpd.conf file. +\layout Subsubsection + +LogSQLPreserveFile +\layout LyX-Code + +Syntax: LogSQLPreserveFile filename +\layout LyX-Code + +Example: LogSQLPreserveFile offline-preserve +\layout LyX-Code + +Default: mysql-preserve (in /tmp) +\layout LyX-Code + +Context: virtual host +\layout Quote + +mod_log_sql writes queries to this local preserve file in the event that + it cannot reach the database, and thus ensures that your high-availability + web frontend does not lose logs during a temporary database outage. + This could happen for a number of reasons: the database goes offline, the + network breaks, etc. + You will not lose entries since the module has this backup. + The file consists of a series of SQL statements that can be imported into + your database at your convenience; furthermore, because the SQL queries + contain the access timestamps you do not need to worry about out-of-order + data after the import. +\layout Quote + +For security purposes the name you supply will be prepended with "/tmp/" + to force creation of the file in /tmp. +\layout Quote + +If you do not define LogSQLPreserveFile then all virtual servers will log + to the same default preserve file (/tmp/mysql-preserve). + You can redefine this on a virtual-host basis in order to segregate your + preserve files if you desire. + Note that segregation is not really necessary, as the SQL statements that + are written to the preserve file already distinguish between different + virtual hosts. +\layout Quote + +The module will log to error-log if/when it notices a database outage, and + upon database return. + You will therefore know when the preserve file is being used, although + it is your responsibility to import the file. +\layout Quote + +The file does not need to be created in advance. + It is safe to remove or rename the file without interrupting Apache, as + the module closes the filehandle immediately after completing the write. + The file is created with the user & group ID of the running Apache process + (e.g. + 'nobody' on many Linux distributions). +\layout Subsubsection + +LogSQLSocketFile +\layout LyX-Code + +Syntax: LogSQLSocketFile filename +\layout LyX-Code + +Example: LogSQLSocketFile /tmp/mysql.sock +\layout LyX-Code + +Default: /var/lib/mysql/mysql.sock +\layout LyX-Code + +Context: server config +\layout Quote + +At Apache runtime you can specify the MySQL socket file to use. + Set this once in your main server config to override the default value. + This value is irrelevant if your database resides on a separate machine. +\layout Quote + +mod_log_sql will automatically employ the socket for db communications if + the database resides on the local host. + If the db resides on a separate host the module will automatically use + TCP/IP. + This is a function of the MySQL API and is not user-configurable. +\layout Subsubsection + +LogSQLTransferLogFormat +\layout LyX-Code + +Syntax: LogSQLTransferLogFormat format-string +\layout LyX-Code + +Example: LogSQLTransferLogFormat huSUTv +\layout LyX-Code + +Default: AbHhmRSsTUuv +\layout LyX-Code + +Context: virtual host +\layout Quote + +Each character in the format-string defines an attribute of the request + that you wish to log. + The default logs the information required to create Combined Log Format + logs, plus several extras. + Here is the full list of allowable keys, which sometimes resemble their + Apache counterparts, but do not always: +\layout Quote + + +\begin_inset Tabular + + + + + + + + + +\begin_inset Text + +\layout Standard + + +\series bold +Character +\end_inset + + +\begin_inset Text + +\layout Standard + + +\series bold +What is this? +\end_inset + + +\begin_inset Text + +\layout Standard + + +\series bold +MySQL DB field +\end_inset + + +\begin_inset Text + +\layout Standard + + +\series bold +Recommended column type +\end_inset + + +\begin_inset Text + +\layout Standard + + +\series bold +Example +\end_inset + + + + +\begin_inset Text + +\layout Standard + +A +\end_inset + + +\begin_inset Text + +\layout Standard + +User agent +\end_inset + + +\begin_inset Text + +\layout Standard + +agent +\end_inset + + +\begin_inset Text + +\layout Standard + +varchar(255) +\end_inset + + +\begin_inset Text + +\layout Standard + +Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 4.0) +\end_inset + + + + +\begin_inset Text + +\layout Standard + +b +\end_inset + + +\begin_inset Text + +\layout Standard + +Bytes transfered +\end_inset + + +\begin_inset Text + +\layout Standard + +bytes_sent +\end_inset + + +\begin_inset Text + +\layout Standard + +int unsigned +\end_inset + + +\begin_inset Text + +\layout Standard + +32561 +\end_inset + + + + +\begin_inset Text + +\layout Standard + +c +\end_inset + + +\begin_inset Text + +\layout Standard + +Text of cookie +\end_inset + + +\begin_inset Text + +\layout Standard + +cookie +\end_inset + + +\begin_inset Text + +\layout Standard + +varchar(255) +\end_inset + + +\begin_inset Text + +\layout Standard + +Apache=sdyn.fooonline.net.130051007102700823 +\end_inset + + + + +\begin_inset Text + +\layout Standard + +f +\end_inset + + +\begin_inset Text + +\layout Standard + +\end_inset + + +\begin_inset Text + +\layout Standard + +request_file +\end_inset + + +\begin_inset Text + +\layout Standard + +varchar(255) +\end_inset + + +\begin_inset Text + +\layout Standard + +\end_inset + + + + +\begin_inset Text + +\layout Standard + +H +\end_inset + + +\begin_inset Text + +\layout Standard + +HTTP request protocol +\end_inset + + +\begin_inset Text + +\layout Standard + +request_protocol +\end_inset + + +\begin_inset Text + +\layout Standard + +varchar(10) +\end_inset + + +\begin_inset Text + +\layout Standard + +HTTP/1.1 +\end_inset + + + + +\begin_inset Text + +\layout Standard + +h +\end_inset + + +\begin_inset Text + +\layout Standard + +Name of remote host +\end_inset + + +\begin_inset Text + +\layout Standard + +remote_host +\end_inset + + +\begin_inset Text + +\layout Standard + +varchar(50) +\end_inset + + +\begin_inset Text + +\layout Standard + +blah.foobar.com +\end_inset + + + + +\begin_inset Text + +\layout Standard + +I +\end_inset + + +\begin_inset Text + +\layout Standard + +Ident user info +\end_inset + + +\begin_inset Text + +\layout Standard + +remote_logname +\end_inset + + +\begin_inset Text + +\layout Standard + +varchar(50) +\end_inset + + +\begin_inset Text + +\layout Standard + +bobby +\end_inset + + + + +\begin_inset Text + +\layout Standard + +m +\end_inset + + +\begin_inset Text + +\layout Standard + +HTTP request method +\end_inset + + +\begin_inset Text + +\layout Standard + +request_method +\end_inset + + +\begin_inset Text + +\layout Standard + +varchar(6) +\end_inset + + +\begin_inset Text + +\layout Standard + +GET +\end_inset + + + + +\begin_inset Text + +\layout Standard + +P +\end_inset + + +\begin_inset Text + +\layout Standard + +httpd child PID +\end_inset + + +\begin_inset Text + +\layout Standard + +child_pid +\end_inset + + +\begin_inset Text + +\layout Standard + +smallint unsigned +\end_inset + + +\begin_inset Text + +\layout Standard + +3215 +\end_inset + + + + +\begin_inset Text + +\layout Standard + +p +\end_inset + + +\begin_inset Text + +\layout Standard + +httpd port +\end_inset + + +\begin_inset Text + +\layout Standard + +server_port +\end_inset + + +\begin_inset Text + +\layout Standard + +smallint unsigned +\end_inset + + +\begin_inset Text + +\layout Standard + +80 +\end_inset + + + + +\begin_inset Text + +\layout Standard + +R +\end_inset + + +\begin_inset Text + +\layout Standard + +Referer +\end_inset + + +\begin_inset Text + +\layout Standard + +referer +\end_inset + + +\begin_inset Text + +\layout Standard + +varchar(255) +\end_inset + + +\begin_inset Text + +\layout Standard + +http://www.biglinkstoyou.com/linkpage.html +\end_inset + + + + +\begin_inset Text + +\layout Standard + +r +\end_inset + + +\begin_inset Text + +\layout Standard + +Request in full form +\end_inset + + +\begin_inset Text + +\layout Standard + +request_line +\end_inset + + +\begin_inset Text + +\layout Standard + +varchar(255) +\end_inset + + +\begin_inset Text + +\layout Standard + +GET /books-cycroad.html HTTP/1.1 +\end_inset + + + + +\begin_inset Text + +\layout Standard + +S +\end_inset + + +\begin_inset Text + +\layout Standard + +Time of request in UNIX format +\end_inset + + +\begin_inset Text + +\layout Standard + +time_stamp +\end_inset + + +\begin_inset Text + +\layout Standard + +int unsigned +\end_inset + + +\begin_inset Text + +\layout Standard + +1005598029 +\end_inset + + + + +\begin_inset Text + +\layout Standard + +s +\end_inset + + +\begin_inset Text + +\layout Standard + +HTTP status of request +\end_inset + + +\begin_inset Text + +\layout Standard + +status +\end_inset + + +\begin_inset Text + +\layout Standard + +smallint unsigned +\end_inset + + +\begin_inset Text + +\layout Standard + +404 +\end_inset + + + + +\begin_inset Text + +\layout Standard + +T +\end_inset + + +\begin_inset Text + +\layout Standard + +Seconds to service request +\end_inset + + +\begin_inset Text + +\layout Standard + +request_duration +\end_inset + + +\begin_inset Text + +\layout Standard + +smallint unsigned +\end_inset + + +\begin_inset Text + +\layout Standard + +2 +\end_inset + + + + +\begin_inset Text + +\layout Standard + +t +\end_inset + + +\begin_inset Text + +\layout Standard + +Time of request in human format +\end_inset + + +\begin_inset Text + +\layout Standard + +request_time +\end_inset + + +\begin_inset Text + +\layout Standard + +char(28) +\end_inset + + +\begin_inset Text + +\layout Standard + +[02/Dec/2001:15:01:26 -0800] +\end_inset + + + + +\begin_inset Text + +\layout Standard + +U +\end_inset + + +\begin_inset Text + +\layout Standard + +Request in simple form +\end_inset + + +\begin_inset Text + +\layout Standard + +request_uri +\end_inset + + +\begin_inset Text + +\layout Standard + +varchar(255) +\end_inset + + +\begin_inset Text + +\layout Standard + +/books-cycroad.html +\end_inset + + + + +\begin_inset Text + +\layout Standard + +u +\end_inset + + +\begin_inset Text + +\layout Standard + +User info from HTTP authentication +\end_inset + + +\begin_inset Text + +\layout Standard + +remote_user +\end_inset + + +\begin_inset Text + +\layout Standard + +varchar(50) +\end_inset + + +\begin_inset Text + +\layout Standard + +bobby +\end_inset + + + + +\begin_inset Text + +\layout Standard + +v +\end_inset + + +\begin_inset Text + +\layout Standard + +Virtual host servicing the request +\end_inset + + +\begin_inset Text + +\layout Standard + +virtual_host +\end_inset + + +\begin_inset Text + +\layout Standard + +varchar(50) +\end_inset + + +\begin_inset Text + +\layout Standard + +www.foobar.com +\end_inset + + + + +\end_inset + + +\layout Quote + +If you have compiled mod_log_sql with WANT_SSL_LOGGING, you also get these: +\layout Quote + + +\begin_inset Tabular + + + + + + + + +\begin_inset Text + +\layout Standard + + +\series bold +Character +\end_inset + + +\begin_inset Text + +\layout Standard + + +\series bold +MySQL DB field it activates +\end_inset + + +\begin_inset Text + +\layout Standard + + +\series bold +MySQL DB field it activatesRecommended column type +\end_inset + + +\begin_inset Text + +\layout Standard + + +\series bold +Example +\end_inset + + + + +\begin_inset Text + +\layout Standard + +z +\end_inset + + +\begin_inset Text + +\layout Standard + +ssl_cipher +\end_inset + + +\begin_inset Text + +\layout Standard + +varchar(25) +\end_inset + + +\begin_inset Text + +\layout Standard + +RC4-MD5 +\end_inset + + + + +\begin_inset Text + +\layout Standard + +q +\end_inset + + +\begin_inset Text + +\layout Standard + +ssl_keysize +\end_inset + + +\begin_inset Text + +\layout Standard + +smallint unsigned +\end_inset + + +\begin_inset Text + +\layout Standard + +56 +\end_inset + + + + +\begin_inset Text + +\layout Standard + +Q +\end_inset + + +\begin_inset Text + +\layout Standard + +ssl_maxkeysize +\end_inset + + +\begin_inset Text + +\layout Standard + +smallint unsigned +\end_inset + + +\begin_inset Text + +\layout Standard + +128 +\end_inset + + + + +\end_inset + + +\layout Section + +FAQ +\layout Subsection + +Why log to an SQL database? +\layout Standard + +To begin with, let's get it out of the way: logging to a database is not + a panacea. + But while there are complexities with this solution, the benefit can be + substantial for certain classes of administrator or people with advanced + requirements: +\layout Itemize + +Chores like log rotation go away, as you can DELETE records from the SQL + database once they are no longer useful. + For example, the excellent and popular log-analysis tool Webalizer (http://www.w +ebalizer.com/) does not need historic logs after it has processed them, enabling + you to delete older logs. + +\layout Itemize + +People with clusters of web servers (for high availability) will benefit + the most -- all their webservers can log to a single SQL database. + This obviates the need to collate/interleave the many separate logfiles, + which can be / highly/ problematic. + +\layout Itemize + +People acquainted with the power of SQL SELECT statements will know the + flexibility of the extraction possibilities at their fingertips. +\layout Standard + +For example, do you want to see all your 404's? Do this: +\layout LyX-Code + +select remote_host,status,request_uri,bytes_sent,from_unixtime(time_stamp) + from acc_log_tbl where status=404 order by time_stamp; +\layout LyX-Code + ++-----------------------------------+--------+--------------------------+------- +-----+--------------------------- +\layout LyX-Code + ++ remote_host | status | request_uri + | bytes_sent | from_unixtime(time_stamp)| +\layout LyX-Code + ++-----------------------------------+--------+--------------------------+------- +-----+--------------------------- +\layout LyX-Code + +| marge.mmm.co.uk | 404 | /favicon.ico | 321 + | 2001-11-20 02:30:56 | +\layout LyX-Code + +| 62.180.239.251 | 404 | /favicon.ico | 333 + | 2001-11-20 02:45:25 | +\layout LyX-Code + +| 212.234.12.66 | 404 | /favicon.ico | 321 + | 2001-11-20 03:01:00 | +\layout LyX-Code + +| 212.210.78.254 | 404 | /favicon.ico | 333 + | 2001-11-20 03:26:05 | +\layout LyX-Code + ++-----------------------------------+--------+--------------------------+------- +-----+---------------------------+ +\layout Standard + +Or do you want to see how many bytes you've sent within a certain directory + or site? Do this: +\layout LyX-Code + +select request_uri,sum(bytes_sent) as bytes,count(request_uri) as howmany + from acc_log_tbl where request_uri like '%mod_log_sql%' group by request_uri + order by howmany desc; +\layout LyX-Code + ++-----------------------------------+---------+---------+ +\layout LyX-Code + +| request_uri | bytes | howmany | +\layout LyX-Code + ++-----------------------------------+---------+---------+ +\layout LyX-Code + +| /mod_log_sql/style_1.css | 157396 | 1288 | +\layout LyX-Code + +| /mod_log_sql/ | 2514337 | 801 | +\layout LyX-Code + +| /mod_log_sql/mod_log_sql.tar.gz | 9769312 | 456 | +\layout LyX-Code + +| /mod_log_sql/faq.html | 5038728 | 436 | +\layout LyX-Code + +| /mod_log_sql/INSTALL | 1196161 | 202 | +\layout LyX-Code + +| /mod_log_sql/directives.html | 1096821 | 171 | +\layout LyX-Code + +| /mod_log_sql/CHANGELOG | 424481 | 107 | +\layout LyX-Code + +| /mod_log_sql/README | 796072 | 97 | +\layout LyX-Code + +| /mod_log_sql/directives-old.html | 42480 | 6 | +\layout LyX-Code + +| /mod_log_sql/index.html | 9531 | 3 | +\layout LyX-Code + ++-----------------------------------+---------+---------+ +\layout Standard + +Or maybe you want to see who's linking to you? Do this: +\layout LyX-Code + +select count(referer) as num,referer from acc_log_tbl where request_uri='/mod_lo +g_sql/' group by referer order by num desc; +\layout LyX-Code + ++-----+------------------------------------------------------------------------- +------+ +\layout LyX-Code + +| num | referer + | +\layout LyX-Code + ++-----+------------------------------------------------------------------------- +------+ +\layout LyX-Code + +| 271 | http://freshmeat.net/projects/mod_log_sql/ + | +\layout LyX-Code + +| 96 | http://modules.apache.org/search?id=339 + | +\layout LyX-Code + +| 48 | http://freshmeat.net/ + | +\layout LyX-Code + +| 8 | http://freshmeat.net + | +\layout LyX-Code + +| 7 | http://freshmeat.net/daily/2001/11/30/ + | +\layout LyX-Code + +| 6 | http://freshmeat.net/daily/2001/11/20/ + | +\layout LyX-Code + ++-----+------------------------------------------------------------------------- +------+ +\layout Standard + +As you can see, there are myriad possibilities that can be constructed with + the wonderful SQL SELECT statement. + Logging to an SQL database can be really quite useful! +\layout Subsection + +Why use MySQL? Are there alternatives? +\layout Standard + +MySQL is a robust, free, and very powerful production-quality database engine. + It is well supported and comes with detailed documentation. + Many 3rd-party software pacakges (e.g. + Slashcode, the engine that powers Slashdot) run exclusively with MySQL. + In other words, you will belong to a very robust and well-supported community + by choosing MySQL. +\layout Standard + +That being said, there are alternatives. + PostgreSQL is probably MySQL's leading "competitor" in the free database + world. + There is also an excellent module available for Apache to permit logging + to a PostgreSQL database, called +\begin_inset LatexCommand \url[pgLOGd]{(http://www.digitalstratum.com/pglogd/)} + +\end_inset + +. +\layout Subsection + +Is this code production-ready? +\layout Standard + +By all accounts it is. + It is known to work without a problem on many-thousands-of-hits-per-day + webservers. + Does that mean it is 100% bug free? Well, no software is. + But it is well-tested and /believed/ to be fully compatible with production + environments. + (The usual disclaimers apply. + This software is provided without warranty of any kind.) +\layout Subsection + +How well does it perform? +\layout Standard + +mod_log_sql scales to very high loads. + Apache 1.3.22 + mod_log_sql was benchmarked using the "ab" (Apache Bench) + program that comes with the Apache distribution; here are the results. +\layout Standard + +Overall configuration: +\layout Itemize + +Machine A: Apache webserver +\layout Itemize + +Machine B: MySQL server +\layout Itemize + +Machines A and B connected with 100Mbps Ethernet +\layout Standard + +Webserver configuration: +\layout Itemize + +Celeron 400 128 MB RAM IDE storage +\layout Standard + +Apache configuration: +\layout LyX-Code + +Timeout 300 +\layout LyX-Code + +KeepAlive On +\layout LyX-Code + +MaxKeepAliveRequests 100 +\layout LyX-Code + +KeepAliveTimeout 15 +\layout LyX-Code + +MinSpareServers 5 +\layout LyX-Code + +StartServers 10 +\layout LyX-Code + +MaxSpareServers 15 +\layout LyX-Code + +MaxClients 256 +\layout LyX-Code + +MaxRequestsPerChild 5000 +\layout LyX-Code + +LogSQLTransferLogFormat AbHhmRSsTUuvc +\layout LyX-Code + +LogSQLWhichCookie Clicks +\layout LyX-Code + +CookieTracking on +\layout LyX-Code + +CookieName Clicks +\layout Standard + +"ab" commandline: +\layout LyX-Code + +./ab -c 10 -t 20 -v 2 -C Clicks=ab_run http://www.hostname.com/target +\layout Standard + +( 10 concurrent requests; 20 second test; setting a cookie "Clicks=ab_run"; + target = the mod_log_sql homepage. + ) +\layout Standard + +Ten total ab runs were conducted: five with MySQL logging enabled, and five + with all MySQL directives commented out of httpd.conf. + Then each five were averaged. + The results: +\layout Itemize + +Average of five runs employing MySQL +\emph on +and +\emph default + standard text logging: 139.01 requests per second, zero errors. +\layout Itemize + +Average of five runs employing +\emph on +only +\emph default + standard text logging: 139.96 requests per second, zero errors. +\layout Standard + +In other words, any rate-limiting effects on this particular hardware setup + are not caused by MySQL. + Note that although this very simple webserver setup is hardly cutting-edge + -- it is, after all, a fairly small machine -- 139 requests per second + equal over twelve million hits per day +\emph on +. +\layout Standard + +If you run this benchmark yourself, take note of three things: +\layout Enumerate + +Use a target URL that is on your own webserver :-). + +\layout Enumerate + +Wait until all your connections are closed out between runs; after several + thousand requests your TCP/IP stack will be filled with hundreds of connections + in TIME_WAIT that need to close. + Do a "netstat -t|wc -l" on the webserver to see. + If you don't wait, you can expect to see a lot of messages like "ip_conntrack: + table full, dropping packet" in your logs. + (This has nothing to do with mod_log_sql, this is simply the nature of + the TCP/IP stack in the Linux kernel.) +\layout Enumerate + +When done with your runs, clean these many thousands of requests out of + your database: +\layout LyX-Code + +mysql> delete from access_log where agent like 'ApacheBench%'; mysql> optimize + table access_log; +\layout Subsection + +Who's using mod_log_sql? +\layout Standard + +Good question! It would be great to find out! If you are a production-level + mod_log_sql user, please contact +\begin_inset LatexCommand \url[the maintainer, Chris Powell]{chris@grubbybaby.com} + +\end_inset + +so that you can be mentioned here. +\layout Subsection + +How do I extract the data in a format that my analysis tool can understand? +\layout Standard + +mod_log_sql would be virtually useless if there weren't a way for you to + extract the data from your database in a somewhat meaningful fashion. + To that end there's a Perl script enclosed with the distribution. + That script (make_combined_log.pl) is designed to extract N-many days worth + of access logs and provide them in a Combined Log Format output. + You can use this very tool right in /etc/crontab to extract logs on a regular + basis so that your favorite web analysis tool can read them. + Or you can examine the Perl code to construct your own custom tool. +\layout Standard + +For example, let's say that you want your web statistics updated once per + day in the wee hours of the morning. + A good way to accomplish that would be the following entries in /etc/crontab: +\layout LyX-Code + +# Generate the temporary apache logs from the MySQL database (for webalizer) + +\layout LyX-Code + +05 04 * * * root /usr/local/sbin/make_combined_log.pl 1 www.grubbybaby.com + > /var/log/httpd/mysql-grubbybaby +\layout LyX-Code + +# Run webalizer on httpd log +\layout LyX-Code + +30 04 * * * root /usr/local/bin/webalizer -c /etc/webalizer.conf; rm -f /var/log/ +httpd/mysql-grubbybaby +\layout Standard + +Or if you have a newer system that puts files in /etc/cron.daily etc., create + a file called +\begin_inset Quotes sld +\end_inset + +webalizer +\begin_inset Quotes srd +\end_inset + + in the cron.____ subdir of your choice. + Use the following as the contents of your file, and make sure to chmod + 755 it when done. +\layout LyX-Code + +#!/bin/sh +\layout LyX-Code + +/usr/local/sbin/make_combined_log.pl 1 www.yourdomain.com > /var/log/httpd/templog +\layout LyX-Code + +/usr/local/bin/webalizer -q -c /etc/webalizer.conf +\layout LyX-Code + +rm -f /var/log/httpd/templog +\layout Standard + +See? Easy. +\layout Subsection + +Why doesn't the module also replace the Apache ErrorLog? +\layout Standard + +There are circumstances when that would be quite unwise -- for example, + if Apache could not reach the MySQL server for some reason and needed to + log that fact. + Without a text-based error log you'd never know anything was wrong, because + Apache would be trying to log a database connection error to the database... + you get the point. +\layout Standard + +Error logs are usually not very high-traffic and are really best left as + text files on a web server machine. +\layout Subsection + + +\begin_inset LatexCommand \label{sec:cookie} + +\end_inset + +How can I log mod_usertrack cookies? +\layout Standard + +A number of people like to log mod_usertrack cookies in their Apache TransferLog + to aid in understanding their visitors' clickstreams. + This is accomplished, for example, with a statement as follows: +\layout LyX-Code + +LogFormat "%h %l %u %t +\backslash +"%r +\backslash +" %s %b +\backslash +"%{Referer}i +\backslash +" +\backslash +"%{User-Agent}i +\backslash +"" +\backslash +"%{cookie}n +\backslash +"" +\layout Standard + +Naturally it would be nice for mod_log_sql to permit the admin to log the + cookie data as well, so as of version 1.10 you can do this. + You need to have already compiled mod_usertrack into httpd -- it's one + of the standard Apache modules. +\layout Standard + +First make sure you have a column called "cookie" in the MySQL database + to hold the cookies, which can be done as follows if you already have a + working database: +\layout LyX-Code + +alter table acc_log_tbl add column cookie varchar(255); +\layout Standard + +Next configure your server to set usertracking cookies as follows, and make + sure you include the new 'c' directive in your LogSQLTransferLogFormat, + which activates cookie logging. + Here's an example: +\layout LyX-Code + + +\layout LyX-Code + + CookieTracking on +\layout LyX-Code + + CookieStyle Cookie +\layout LyX-Code + + CookieName Foobar +\layout LyX-Code + + LogSQLTransferLogFormat huSUsbTvRAc +\layout LyX-Code + + LogSQLWhichCookie Foobar +\layout LyX-Code + + +\layout Standard + +The first three lines configure mod_usertrack to create a COOKIE (RFC 2109) + format cookie called Foobar. + The last two lines tell mod_log_sql to log cookies named Foobar. + You have to choose which cookie to log because more than one cookie can/will + be sent to the server by the client. +\layout Standard + +FYI, you are advised NOT to use CookieStyle Cookie2 -- it seems that even + newer browsers (IE 5.5, etc.) have trouble with the new COOKIE2 (RFC 2965) + format. + Just stick with the standard COOKIE format and you'll be fine. +\layout Standard + +Perform some hits on your server and run a select: +\layout LyX-Code + +mysql> select request_uri,cookie from access_log where cookie is not null; +\layout LyX-Code + ++--------------------------------------------+---------------------------------- +------------------+ +\layout LyX-Code + +| request_uri | + cookie | +\layout LyX-Code + ++--------------------------------------------+---------------------------------- +------------------+ +\layout LyX-Code + +| /mod_log_sql/ | ool-18e4.dyn.optonline.net.1300510071 +02700823 | +\layout LyX-Code + +| /mod_log_sql/usa.gif | ool-18e4.dyn.optonline.net.13005100710 +2700823 | +\layout LyX-Code + +| /mod_log_sql/style_1.css | ool-18e4.dyn.optonline.net.13005100710 +2700823 | +\layout LyX-Code + +...etc... +\layout Subsection + +What if I want to log more than one cookie? +\layout Standard + +No problem. + As of version 1.17, you have a choice. + If you are just interested in a single cookie, follow the instructions + in section +\begin_inset LatexCommand \ref{sec:cookie} + +\end_inset + + above. + That cookie will be logged to a column in the regular access_log table. +\layout Standard + +However, if you need to log multiple cookies, you'll employ the LogSQLWhichCooki +es (note the plural) directive. + The cookies you specify will be logged to a separate table, and entries + in that table will be linked to the regular access_log entries via the + unique ID that is supplied by mod_unique_id. + Without mod_unique_id the information will still be logged but you will + be unable to correlate which cookies go with which access-requests. +\layout Standard + +LogSQLWhichCookie and LogSQLWhichCookies can coexist without conflict, but + you're better off choosing the one you need. +\layout Subsection + +What are the SSL logging features, and how do I activate them? +\layout Standard + +If you run an SSL-enabled server you may benefit from logging some SSL details. + mod_log_sql now supports this ability. + By adding certain characters to your LogSQLTransferLogFormat string you + can tell mod_log_sql to log the SSL cipher, the SSL keysize of the connection, + and the Max-keysize that was available. + This would let you tell, for example, which clients were using only export-grad +e security to access your secure software area. +\layout Standard + +You can compile mod_log_sql with SSL logging support if you have the right + packages installed. + If you already have an SSL-enabled Apache then you by definition have the + correct packages already installed: OpenSSL and mod_ssl. +\layout Standard + +You need to ensure that your database is set up to log the SSL data. + Issue the following commands to MySQL once you have your basic access_log + table built: +\layout LyX-Code + +alter table access_log add column ssl_cipher varchar(25); +\layout LyX-Code + +alter table access_log add column ssl_keysize smallint unsigned; +\layout LyX-Code + +alter table access_log add column ssl_maxkeysize smallint unsigned; +\layout Standard + +Finally configure httpd.conf to activate the SSL fields. + Note that this is only meaningful in a VirtualHost that is set up for SSL. +\layout LyX-Code + + +\layout LyX-Code + + LogSQLTransferLogFormat AbHhmRSsTUuvcQqz +\layout LyX-Code + + +\layout Standard + +The last three characters (Qqz) in the directive are the SSL ones; see the + directives documentation for details. +\layout Standard + +Perform some hits on your server and run a select: +\layout LyX-Code + +mysql> select remote_host,request_uri,ssl_cipher,ssl_keysize,ssl_maxkeysize + from access_log where ssl_cipher is not null; +\layout LyX-Code + ++--------------------------+---------------------------------------------------- ++------------+-------------+----------------+ +\layout LyX-Code + +| remote_host | request_uri + | ssl_cipher | ssl_keysize | ssl_maxkeysize | +\layout LyX-Code + ++--------------------------+---------------------------------------------------- ++------------+-------------+----------------+ +\layout LyX-Code + +| 216.190.52.4 | /dir/somefile.html + | RC4-MD5 | 128 | 128 | +\layout LyX-Code + +| 216.190.52.4 | /dir/somefile.gif + | RC4-MD5 | 128 | 128 | +\layout LyX-Code + +| 216.190.52.4 | /dir/somefile.jpg + | RC4-MD5 | 128 | 128 | +\layout LyX-Code + +...etc... +\layout Subsection + +Does mod_log_sql connect to MySQL via TCP/IP or a socket? +\layout Standard + +It depends! Actually this isn't determined by mod_log_sql. + mod_log_sql relies on a connection command that is supplied in the MySQL + API, and that command is somewhat intelligent. + When mod_log_sql issues the connect command to MySQL, this intelligent + connect command uses sockets to communicate with MySQL if the specified + MySQL database is on the same machine (because sockets are more efficient + than TCP/IP). + However, if the specified MySQL db is on a different machine, mod_log_sql + connects using TCP/IP. + You don't have any control of which methodology is used. +\layout Standard + +You do have control over where mod_log_sql looks for the socket. + The LogSQLSocketFile runtime configuration directive overrides the default + of "/var/lib/mysql/mysql.sock" to whatever you wish. + (Applies to mod_log_sql 1.16 or later only.) +\layout Subsection + +Why do I occasionally see a "connection lost, attempting reconnect" message + in my error-log? +\layout Standard + +This message may appear every now and then in your Apache error log, especially + on very lightly loaded servers. + This doesn't mean that anything is necessarily wrong. + Within each httpd child process, mod_log_sql will open (and keep open) + a connection to the MySQL server. + MySQL, however, will close connections that haven't been used in a while; + the default timeout is 8 hours. + When this occurs, mod_log_sql will notice and re-open the connection. + That event is what is being logged, and looks like this: +\layout LyX-Code + +[Thu Dec 13 05:42:18 2001] [error] mod_log_sql: connection lost, attempting + reconnect +\layout LyX-Code + +[Thu Dec 13 05:42:18 2001] [error] mod_log_sql: reconnect successful +\layout Standard + +Reference: +\begin_inset LatexCommand \url[MySQL documentation]{http://www.mysql.com/documentation/mysql/bychapter/manual_Problems.html#Gone_away} + +\end_inset + + +\layout Subsection + +Does mod_log_sql work with Apache 2.x? +\layout Standard + +As of this writing, no. + The Apache Group significantly altered the module API with the release + of Apache 2.0. + All modules written for 1.3, including mod_log_sql, will not work with 2.0. +\layout Standard + +mod_log_sql will eventually be ported to Apache 2.x, but not immediately. + It is going to take some time, and there are other features that have higher + priority. + Please sign up for the announcements list (on the main website) or monitor + the website for updates to learn when the port (and other releases) are + available. +\layout Standard + +If you're a *NIX user, stick with Apache 1.3.x for now. + Major modules like mod_ssl and PHP are not even ready for 2.0 yet, and the + main benefits in 2.0 are for Win32 users anyway. + Apache 1.3.x is rock-stable and performs equally well on *NIX as 2.0. +\layout Subsection + +I have discovered a bug. + Who can I contact? +\layout Standard + +Please contact +\begin_inset LatexCommand \url[the maintainer]{chris@grubbybaby.com} + +\end_inset + +! Your comments, suggestions, bugfixes, bug catches, and usage testimonials + are always welcome. + As free software, mod_log_sql is intended to be a community effort -- any + code contributions or other ideas will be fully and openly credited, of + course. +\the_end diff --git a/Documentation/documentation.tex b/Documentation/documentation.tex new file mode 100644 index 0000000..0eb6359 --- /dev/null +++ b/Documentation/documentation.tex @@ -0,0 +1,1534 @@ +%% LyX 1.1 created this file. For more info, see http://www.lyx.org/. +%% Do not edit unless you really know what you are doing. +\documentclass[10pt,english]{article} +\usepackage[T1]{fontenc} +\usepackage{geometry} +\geometry{verbose,letterpaper,tmargin=0.5in,bmargin=1in,lmargin=1in,rmargin=1in} +\usepackage{babel} +\IfFileExists{url.sty}{\usepackage{url}} + {\newcommand{\url}{\texttt}} + +\makeatletter + +%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% LyX specific LaTeX commands. +\providecommand{\LyX}{L\kern-.1667em\lower.25em\hbox{Y}\kern-.125emX\@} + +%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% Textclass specific LaTeX commands. + \newenvironment{lyxcode} + {\begin{list}{}{ + \setlength{\rightmargin}{\leftmargin} + \raggedright + \setlength{\itemsep}{0pt} + \setlength{\parsep}{0pt} + \normalfont\ttfamily}% + \item[]} + {\end{list}} + +\makeatother +\begin{document} + +\vfill{} +\title{Installing and Running mod\_log\_sql} +\vfill{} + + +\author{Christopher Powell } + + +\date{11/13/02} + +\maketitle +\tableofcontents{} +\newpage + + +\section{Introduction} + + +\subsection{Homepage } + +\begin{lyxcode} +http://www.grubbybaby.com/mod\_log\_sql/ +\end{lyxcode} + +\subsection{Summary} + +This Apache module will permit you to log Apache accesses (and a lot +of related information) to a SQL database. Unlike logging to a flat +text file (which is standard in Apache), there are a lot of powerful +advantages to logging to SQL. This module can either replace or happily +coexist with Apache's text file logging facility. + + +\subsection{Approach} + +This project was formerly known as mod\_log\_mysql. It has been renamed +to mod\_log\_sql in order to reflect the project goal of database-inspecificity. +The module currently supports MySQL, and development for other database +backends is underway. + +In order to save speed and overhead, links are kept alive in between +queries. This module uses one SQL link per httpd child. Among other +things, this means that this module supports logging into only one +MySQL server, and for now, also, only one SQL database. But that's +a small tradeoff compared to the blinding speed of this module. + +Virtual hosts are supported in the same manner they are in the regular +logging modules. You define some basic 'global' directives in the +main server config, then you define more specific 'local' directives +inside each virtualhost stanza. + +SQL links are opened by each child process when it is born. Error +reporting is robust throughout and will let you know about database +issues in the standard Apache error-log for the server or virtual +server. + +A robust \char`\"{}preserve\char`\"{} capability has now been implemented. +This permits the module to preserve any failed INSERT commands to +a local file on its machine. In any situation that the database is +unavailable -- e.g. the network fails, you reboot the db host, etc. +-- mod\_log\_sql will note this in the error log and begin appending +its log entries to the preserve file (which is created with the user +\& group ID of the running Apache process, e.g. \char`\"{}nobody\char`\"{} +on many Linux installations). When your database becomes available +again, mod\_log\_sql seamlessly resumes logging to it. When convenient +for the sysadmin, he/she can easily import the preserve file into +the database because it is simply a series of SQL insert statements: + +\begin{lyxcode} +\#~mysql~-uadminuser~-p~mydbname~<~/tmp/mysql-preserve +\end{lyxcode} + +\subsection{Supported directives} + +Please see the web-based documentation for full explanation of all +supported run-time directives. + +http://www.grubbybaby.com/mod\_log\_sql/directives.html + +See the FAQ for some handy examples: + +http://www.grubbybaby.com/mod\_log\_sql/faq.html + + +\subsection{What gets logged by default? } + +All the data that would be contained in the \char`\"{}Combined Log +Format\char`\"{} is logged by default, plus a little extra. Your best +bet is to begin by accepting this default, then later customize the +log configuration based on your needs. + +The online documentation of the run-time directives includes a full +explanation of what you can log, including examples. + + +\subsection{Minor Notes} + +\begin{itemize} +\item Note which directives go in the 'main server config' and which directives +apply to the 'virtual host config'. This is made clear in the directive +documentation. +\item The 'time\_stamp' field is stored in an UNSIGNED INTEGER column, in +the standard unix \char`\"{}seconds since 1/1/1970 12:00:00\char`\"{} +format. This is superior to storing the access time as a string due +to size requirements: an UNSIGNED INT type fits in 4 bytes, whereas +the Apache date string (e.g. \char`\"{}18/Nov/2001:13:59:52 -0800\char`\"{}) +requires 26 bytes -- significantly larger, and those extra 22 bytes +will add up over the thousands of accesses that a busy server will +experience. Besides, an INT type is far more flexible for comparisons, +etc. + + +In MySQL 3.21 and above you can easily convert this to a human readable +format using from\_unixtime(), e.g.: + +\begin{lyxcode} +select~remote\_host,request\_uri,from\_unixtime(time\_stamp)~from~access\_log; +\end{lyxcode} +The enclosed perl program ''make\_combined\_log.pl'' shows how you +can extract your access records in a format that is completely Combined +Log Format compliant. You can then feed this to your favorite web +log analysis tool. + +\item The table's string values can be CHAR or VARCHAR, at a length of your +choice. VARCHAR is superior because it truncates long strings; CHAR +types are fixed-length and will be padded with spaces. Just like the +time\_stamp described above, that kind of space waste will add up +over thousands of records. +\item Be careful not to go overboard setting fields to NOT NULL. If a field +is marked NOT NULL then it must contain data in the INSERT or the +INSERT will fail, sometimes in a really mysterious way to the frustrated +sysad. +\item Apache normally logs numeric fields with a '-' character to mean \char`\"{}not +applicable,\char`\"{} e.g. bytes\_sent on a request with a 304 response +code. Since '-' is an illegal character in an SQL numeric field, such +fields are assigned the value 0 instead of '-' which, of course, makes +perfect sense anyway. +\end{itemize} + +\subsection{Author / Maintainer} + +The actual logging code was taken from the already existing flat file +text modules, so all that credit goes to the Apache Server group. + +The MySQL routines and directives were added by Zeev Suraski . + +All changes from 1.06+ and the new documentation were added by Chris +Powell . It seems that the module had fallen +into the \char`\"{}unmaintained\char`\"{} category -- it hadn't been +updated since 1998 -- so Chris adopted it as the new maintainer. + + +\section{Installation} + + +\subsection{Requirements} + +\begin{itemize} +\item A compatible system. I have run mod\_log\_sql on Red Hat based systems +(Red Hat, Mandrake). These instructions should easily adapt to any +modern distro. +\item Apache 1.2 or 1.3 installed. (I run 1.3.22 and it works fine). You +should have already successfully compiled Apache and know what you're +doing there. +\item The MySQL development headers. This is called different things on +different distros. For example, Red Hat 6.x called this RPM \char`\"{}MySQL-devel\char`\"{} +whereas Mandrake calls it \char`\"{}libmysql10-devel\char`\"{}. +\item MySQL >= 3.23.15 configured, installed and running on either localhost +or an accessible networked machine. You should already have a basic +understanding of MySQL and how it functions. +\item Again, basic administrative skills with Apache and MySQL. I try to +make things as easy as possible in this file, but its purpose is not +to be an administrative tutorial. +\item Additionally, if you want to be able to log SSL information such as +keysize or cipher, you need OpenSSL and glibc-devel installed. Both +are available as RPMs. +\end{itemize} + +\subsection{Do I want a DSO?} + +You need to know the answer to this question before you proceed. The +answer is pretty straightforward: what have you done in the past? +If you like all your Apache modules to be dynamic, then you should +keep doing that. If you're more of an old-school type and prefer to +compile the modules right into apache, do that. Both methods work +equally well. + +FWIW, the DSO method is more modern and increasing in popularity because +apxs takes care of a lot of dirty little details for you. As you'll +see below, the static-module method is a little more complex. + + +\subsection{Installation as an Apache DSO (Preferred) } + +\begin{enumerate} +\item Perform all the following steps as root so that you have install privs, +etc. Unpack the archive into a working directory. + +\begin{lyxcode} +\#~tar~zxf~mod\_log\_sql.tar.gz~-C~/usr/local/src~\#~cd~/usr/local/src/mod\_log\_sql +\end{lyxcode} +\item Edit Makefile for your system.\\ + + + +NECESSARY: + +\begin{itemize} +\item The location where you installed Apache -- usually /usr/local/apache, +'locate apxs' can help you find it. +\item The location of your MySQL libraries, find using 'locate libmysqlclient' +\item The location of your MySQL header files, find using 'locate mysql.h' +\end{itemize} +OPTIONAL if you have included mod\_ssl in Apache and want to log SSL +data such as keysize and cipher type: + +\begin{itemize} +\item The location of your SSL header files, find using 'locate mod\_ssl.h' +\end{itemize} +Now that you know these things, edit Makefile and replace the stock +values with your own. + +IMPORTANT: If you are not logging SSL info, comment out MODSSLHDRS +by putting a \# character in front of it: + +\begin{lyxcode} +\#MODSSLHDRS=/usr/include/... +\end{lyxcode} +\item Instruct apxs to compile the module as a DSO. + +\begin{lyxcode} +\#~make~dso +\end{lyxcode} +\item You should see output similar to the following: + +\begin{lyxcode} +/usr/local/Apache/bin/apxs~-Wc,-O2~-Wc,-Wall~-Wc,-DEAPI~-c~-I/usr/include/mysql~-I/usr/local/src/apache\_1.3.27-dso/src/modules/ssl~-L/usr/lib~-lmysqlclient~-lz~mod\_log\_sql.c~ + +gcc~-DLINUX=22~-DNO\_DBM\_REWRITEMAP~-DMOD\_SSL=208111~-DUSE\_HSREGEX~-DEAPI~-DUSE\_EXPAT~-I../lib/expat-lite~-fpic~-DSHARED\_CORE~-DSHARED\_MODULE~-I/usr/local/Apache/include~-O2~-Wall~-DEAPI~-I/usr/include/mysql~-I/usr/local/src/apache\_1.3.27-dso/src/modules/ssl~-c~mod\_log\_sql.c~ + +gcc~-shared~-o~mod\_log\_sql.so~mod\_log\_sql.o~-Wc,-O2~-Wc,-Wall~-Wc,-DEAPI~-L/usr/lib~-lmysqlclient~-lz~-lm~-lcrypt~-ldb +\end{lyxcode} +You should see no errors and have a file called \char`\"{}mod\_log\_sql.so\char`\"{} +in your directory. + +\item Instruct apxs to install the DSO. + +\begin{lyxcode} +\#~make~dsoinstall +\end{lyxcode} +You should see output similar to the following: + +\begin{lyxcode} +/usr/local/Apache/bin/apxs~-i~mod\_log\_sql.so + +cp~mod\_log\_sql.so~/usr/local/Apache/libexec/mod\_log\_sql.so + +chmod~755~/usr/local/Apache/libexec/mod\_log\_sql.so +\end{lyxcode} +\item Module ordering within httpd.conf is important. If you are logging +SSL, you must make sure that + +\begin{lyxcode} +LoadModule~ssl\_module~libexec/libssl.so +\end{lyxcode} +comes before + +\begin{lyxcode} +LoadModule~sql\_log\_module~libexec/mod\_log\_sql.so +\end{lyxcode} +If you don't, you will get this error when you start Apache: + +\begin{lyxcode} +/usr/local/apache/libexec/mod\_log\_mysql.so:~undefined~symbol:~ssl\_var\_lookup + +/usr/local/apache/bin/apachectl~startssl:~httpd~could~not~be~started +\end{lyxcode} +(Because mod\_log\_sql doesn't yet have the required symbols that +mod\_ssl provides.) + +Now skip below to the \char`\"{}Configuration\char`\"{} section. + +\end{enumerate} + +\subsection{Installation as a static module compiled into httpd} + +\begin{enumerate} +\item Perform all the following steps as root so that you have install privs, +etc. +\item Unpack the archive into a working directory. + +\begin{lyxcode} +\#~tar~zxf~mod\_log\_sql.tar.gz~-C~/usr/local/src~\#~cd~/usr/local/src/mod\_log\_sql +\end{lyxcode} +\item \label{step:editMF}Edit Makefile for your system.\\ + + + +NECESSARY: + +\begin{itemize} +\item The location where you installed Apache -- usually /usr/local/apache, +'locate apxs' can help you find it. +\item The location of your Apache {*}sources{*}, find using 'locate ABOUT\_APACHE' +\item The location of your MySQL header files, find using 'locate mysql.h' +\item The location of your MySQL libraries, find using 'locate libmysqlclient' +\end{itemize} +OPTIONAL if you have included mod\_ssl in Apache and want to log SSL +data such as keysize and cipher type: + +\begin{itemize} +\item The location of your mod\_ssl header files, find using 'locate mod\_ssl.h' +\item The location of your OpenSSL header files, find using 'locate x509.h' +\item The location of your db1 header files, find using 'locate mpool.h' +\end{itemize} +Now that you know these things, edit Makefile and replace the stock +values with your own. + +IMPORTANT: If you are not logging SSL info, comment out MODSSLHDRS, +OPNSSLHDRS and DB1HDRS by putting a \# character in front of each +one, e.g. \#OPNSSLHDRS=/usr/include/... + +\item \# make static +\item \# make statinstall +\item Change to your Apache source dir. + +\begin{lyxcode} +\#~cd~/usr/local/src/apache-1.3.22/src +\end{lyxcode} +\item Re-make your httpd binary as follows. + +\begin{enumerate} +\item Edit Configuration.apaci as follows... + +\begin{itemize} +\item Append the following string to the EXTRA\_LIBS= line. (\char`\"{}/usr/lib/mysql\char`\"{} +is from step \ref{step:editMF}, where your MySQL libraries live): +\end{itemize} +\begin{lyxcode} +-L/usr/lib/mysql~-lmysqlclient~-lm~-lz +\end{lyxcode} +\begin{itemize} +\item Find the mod\_log\_config.o line, and add this line immediately after +it: +\end{itemize} +\begin{lyxcode} +AddModule~modules/sql/mod\_log\_sql.o +\end{lyxcode} +\item \# cp Configuration.apaci Configuration +\item \# ./Configure +\item \# make +\item \# strip httpd +\end{enumerate} +\item Test your new apache binary: + +\begin{lyxcode} +\#~./httpd~-l +\end{lyxcode} +\item You should see something like: + +\begin{lyxcode} +Compiled-in~modules:~ + +http\_core.c + +mod\_log\_sql.c~<-{}-~That's~the~line~you're~looking~for. + +mod\_env.c~ + +mod\_log\_config.c~ + +mod\_mime.c~ + +mod\_negotiation.c~... + +etc... +\end{lyxcode} +\item Install your httpd binary. Copy it over your old httpd binary, wherever +it lives. You can and should rename your old httpd first so that you +can easily revert to that working version in case of bugs with the +new version. + +\begin{lyxcode} +\#~/etc/rc.d/init.d/httpd~stop~ + +\#~mv~/usr/local/Apache/bin/httpd~\textasciitilde{}/httpd-save~ + +\#~cp~-f~./httpd~/usr/local/Apache/bin/ +\end{lyxcode} +\end{enumerate} + +\section{Configuration} + + +\subsection{Preparing MySQL} + +You have to prepare the database to receive data from mod\_log\_sql, +and set up run-time directives in httpd.conf to control how and what +mod\_log\_sql logs. + +This section will discuss how to get started with a basic config. +Full documentation of the run-time directives is available here: http://www.grubbybaby.com/mod\_log\_sql/directives.html + +\begin{enumerate} +\item mod\_log\_sql can make its own tables on-the-fly, or you can pre-make +the tables by hand. The advantage of letting the module make the tables +is ease-of-use, but for raw performance you will want to pre-make +the tables in order to save overhead. In this basic setup we'll let +the module create tables for us. +\item We still need to have a logging database created and ready, so run +the MySQL command line client and create a database: + +\begin{lyxcode} +\#~mysql~-uadmin~-pmypassword~mysql>~create~database~apachelogs; +\end{lyxcode} +\item If you want to hand-create the tables, run the enclosed 'create-tables' +SQL script as follows: + +\begin{lyxcode} +mysql>~source~create\_tables.sql +\end{lyxcode} +\item Create a specific MySQL userid that httpd will use to authenticate +and enter data. This userid need not be an actual Unix user. It is +a userid internal to MySQL with specific privileges. In the following +example command, \char`\"{}apachelogs\char`\"{} is the database, \char`\"{}loguser\char`\"{} +is the userid to create, \char`\"{}my.apachemachine.com\char`\"{} +is the name of the Apache machine, and \char`\"{}l0gger\char`\"{} +is the password to assign. Choose values that are different from these +examples. + +\begin{lyxcode} +mysql>~grant~insert,create~on~apachelogs.{*}~to~loguser@my.apachemachine.com~identified~by~'l0gger'; +\end{lyxcode} +\item You may be especially security-paranoid and not want \char`\"{}loguser\char`\"{} +to have \char`\"{}create\char`\"{} capability within the \char`\"{}apachelogs\char`\"{} +databse. You can disable that but the cost is that you cannot use +the module's automatic-table-creation feature. If that's an acceptable +cost, hand-create the tables as described in step 1 and use the following +GRANT statement instead of the one above: + +\begin{lyxcode} +mysql>~grant~insert~on~apachelogs.{*}~to~loguser@my.apachemachine.com~identified~by~'l0gger'; +\end{lyxcode} +\item Enable full logging of your MySQL daemon (at least temporarily for +debugging purposes) if you don't do this already. Edit /etc/my.cnf +and add the following line to your {[}mysqld{]} section: + +\begin{lyxcode} +log=/var/log/mysql-messages +\end{lyxcode} +Then restart MySQL. + +\begin{lyxcode} +\#~/etc/rc.d/init.d/mysql~restart +\end{lyxcode} +\end{enumerate} + +\subsection{Preparing Apache} + +\begin{enumerate} +\item Tell the module what database to use and the appropriate authentication +information. + +\begin{description} +\item [EXAMPLE:]Use the MySQL database called \char`\"{}apachelogs\char`\"{} +running on \char`\"{}dbmachine.foo.com\char`\"{}. The module uses +username \char`\"{}loguser\char`\"{} and password \char`\"{}l0gger\char`\"{} +to authenticate to the database. The log entries will be INSERTed +into the table called \char`\"{}access\_log\char`\"{}. +\end{description} +So, edit httpd.conf and insert the following lines somewhere AFTER +any LoadModule / AddModule statements. Make sure these statements +are \char`\"{}global,\char`\"{} i.e. not inside any VirtualHost stanza. + +\begin{lyxcode} +LogSQLDatabase~apachelogs + +LogSQLLoginInfo~dbmachine.foo.com~loguser~l0gger~ + +LogSQLCreateTables~on +\end{lyxcode} +\item If your database resides on localhost instead of another host, specify +the MySQL server's socket file as follows: + +\begin{lyxcode} +LogSQLSocketFile~/your/path/to/mysql.sock +\end{lyxcode} +\item The actual logging is set up on a virtual-host-by-host basis. So, +skip down to the virtual host you want to set up. Instruct this virtual +host to log entries to the table ''access\_log'' by inserting a +LogSQLTransferLogTable directive. (The LogSQLTransferLogTable directive +is the minimum required to log -- other directives simply tune the +module's behavior.) + +\begin{lyxcode} + + +{[}snip{]} + +LogSQLTransferLogTable~access\_log + +{[}snip{]} + + +\end{lyxcode} +\item Restart apache. + +\begin{lyxcode} +\#~/etc/rc.d/init.d/httpd~start +\end{lyxcode} +\end{enumerate} + +\subsection{Testing the module} + +\begin{enumerate} +\item Load your web site in a browser to trigger some hits, then confirm +that the entries are being successfully logged: + +\begin{lyxcode} +\#~mysql~-hmysql.host.com~-umysqladmin~-p~-e~\char`\"{}select~{*}~from~access\_log\char`\"{}~apachelogs~ + +Enter~password: + +(Several~lines~of~output~should~follow,~corresponding~to~your~hits~on~the~site.) +\end{lyxcode} +You now have basic functionality. Don't disable your regular Apache +logs until you feel comfortable that the database is behaving as you'd +like and that things are going well. + +\item If you do not see any entries in the access\_log, then something is +preventing the inserts from happening. This problem could be caused +by several things: + +\begin{itemize} +\item Improper privileges set up in the MySQL database +\item You aren't hitting a VirtualHost that has a LogSQLTransferLogTable +entry +\item You didn't specify the right host +\end{itemize} +If you have confirmed your LogSQL{*} directives and know them to be +correct, you should examine the httpd server logs for mod\_log\_sql +messages; the module will offer hints as to why it cannot connect, +etc. Also examine the MySQL log that you established in step 3. Ensure +that the INSERTs are not being rejected because of a malformed table +entry or other clerical error. If you see no INSERT attempts in the +log, the module isn't successfully connecting to the database. + +The next thing to do is recompile the module with debugging output +activated. change the \char`\"{}\#undef DEBUG\char`\"{} on line 8 +of mod\_log\_sql.c to \char`\"{}\#define DEBUG\char`\"{} and recompile/reinstall. +The module will now output copious notes about what it is doing, and +this will help you (and the maintainer) solve the problem. + +\end{enumerate} +You can now activate the advanced features of mod\_log\_sql. These +are all described in the next section. + + +\subsection{Configuration directive reference} + + +\subsubsection{LogSQLLoginInfo } + +\begin{lyxcode} +\textbf{MANDATORY}~ + +Syntax:~LogSQLLoginInfo~host~user~password + +Example:~LogSQLLoginInfo~foobar.baz.com~logwriter~passw0rd~ + +Context:~server~config +\end{lyxcode} +\begin{quote} +Defines the general parameters of the MySQL host to which you will +be logging. host is the hostname or IP address of the MySQL machine. +user is the MySQL userid (not a Unix userid!) with INSERT privileges +on the table defined in LogSQLTransferLogTable. password is that user's +password. This is defined only once in the httpd.conf file. +\end{quote} + +\subsubsection{LogSQLDatabase } + +\begin{lyxcode} +\textbf{MANDATORY} + +Syntax:~LogSQLDatabase~database~ + +Example:~LogSQLDatabase~loggingdb~ + +Context:~server~config +\end{lyxcode} +\begin{quote} +Defines the database that is used for logging. database must be operating +on the MySQL host defined in LogSQLLoginInfo. This is defined only +once in the httpd.conf file. +\end{quote} + +\subsubsection{LogSQLTransferLogTable} + +\begin{lyxcode} +\textbf{MANDATORY~} + +Syntax:~LogSQLTransferLogTable~table-name~ + +Example:~LogSQLTransferLogTable~access\_log\_table~ + +Context:~virtual~host +\end{lyxcode} +\begin{quote} +Defines which table is used for logging of Apache's transfers; this +is analogous to Apache's TransferLog directive. table-name must be +a valid table within the database defined in LogSQLDatabase. + +This directive is not necessary if you declare LogSQLMassVirtualHosting +On, since that directive activates dynamically-named tables. If you +attempt to use LogSQLTransferLogTable at the same time a warning will +be logged and it will be ignored, since LogSQLMassVirtualHosting takes +priority. +\end{quote} + +\subsubsection{LogSQLRequestIgnore} + +\begin{lyxcode} +Syntax:~LogSQLRequestIgnore~req1~req2~req3~...~reqN~ + +Example:~LogSQLRequestIgnore~root.exe~cmd.exe~default.ida~favicon.ico~ + +Context:~virtual~host +\end{lyxcode} +\begin{quote} +Lists a series of strings that, if present in the URI, will cause +that request NOT to be logged. This directive is useful for cutting +down on log clutter when you KNOW that you do not want to log requests +for certain objects. (The example above is a genuinely useful one; +it will prevent logging of many common Microsoft-based worm intrusion +attempts, as well as those ridiculous requests for the favicon.) + +Each string is separated by a space, and NO regular expressions or +globbing are allowed. Each string is evaluated as a substring of the +URI using strstr(). +\end{quote} + +\subsubsection{LogSQLRemhostIgnore} + +\begin{lyxcode} +Syntax:~LogSQLRemhostIgnore~host1~host2~host3~...~hostN~ + +Example:~LogSQLRemhostIgnore~localnet.com~ + +Context:~virtual~host +\end{lyxcode} +\begin{quote} +Lists a series of strings that, if present in the REMOTE\_HOST, will +cause that request NOT to be logged. This directive is useful for +cutting down on log clutter when you KNOW that you do not want to +log requests from certain hosts, such as your own internal network +machines. + +Each string is separated by a space, and NO regular expressions or +globbing are allowed. Each string is evaluated as a substring of the +REMOTE\_HOST using strstr(). +\end{quote} + +\subsubsection{LogSQLRefererIgnore} + +\begin{lyxcode} +Syntax:~LogSQLRefererIgnore~ref1~ref2~ref3~...~refN~ + +Example:~LogSQLRefererIgnore~google.com~ + +Context:~virtual~host +\end{lyxcode} +\begin{quote} +Lists a series of strings that, if present in the HTTP\_REFERER, will +cause that request NOT to be logged. This directive is useful for +cutting down on log clutter when you know that you do not want to +log requests from certain referers. + +Each string is separated by a space, and NO regular expressions or +globbing are allowed. Each string is evaluated as a substring of the +HTTP\_REFERER using strstr(). +\end{quote} + +\subsubsection{LogSQLWhichCookie} + +\begin{lyxcode} +Syntax:~LogSQLWhichCookie~cookiename~ + +Example:~LogSQLWhichCookie~Clicks + +Default:~None.~You~must~set~this~if~you~wish~to~capture~cookies. + +Context:~virtual~host +\end{lyxcode} +\begin{quote} +In HTTP, cookies have names to distinguish them from each other. Using +mod\_usertrack, for example, you can give your user-tracking cookies +a name with the CookieName directive. + +If you have activated cookie logging in LogSQLTransferLogFormat, then +LogSQLWhichCookie tells mod\_log\_sql which cookie to log. This is +useful because many times you will be setting and receiving more than +one cookie from a client; without this directive you'd be unable to +choose which cookie is your mod\_usertrack cookie. + +Note: although this was intended for people who are using mod\_usertrack +to set user-tracking cookies, you aren't restricted in any way. You +can choose which cookie you wish to log to the database, and it doesn't +necessarily have to have anything to do with mod\_usertrack. +\end{quote} + +\subsubsection{LogSQLCreateTables} + +\begin{lyxcode} +Syntax:~LogSQLCreateTables~flag + +Example:~LogSQLCreateTables~On~ + +Default:~Off~ + +Context:~server~config +\end{lyxcode} +\begin{quote} +mod\_log\_sql now has the ability to create its tables on-the-fly. +The advantage to this is convenience: you don't have to execute any +SQL by hand to prepare the table. This is especially helpful for people +with lots of virtual hosts (who should also see the LogSQLMassVirtualHosting +directive). + +There is a slight disadvantage: if you wish to activate this feature, +then the user specified by LogSQLLoginInfo must have CREATE privileges +on the database. In an absolutely paranoid, locked-down situation +you may only want to grant your mod\_log\_sql user INSERT privileges +on the database; in that situation you are unable to take advantage +of LogSQLCreateTables. But most people -- even the very security-conscious +-- will find that granting CREATE on the logging database is reasonable. + +This is defined only once in the httpd.conf file. +\end{quote} + +\subsubsection{LogSQLMassVirtualHosting} + +\begin{lyxcode} +Syntax:~LogSQLMassVirtualHosting~flag~ + +Example:~LogSQLMassVirtualHosting~On~ + +Default:~Off~ + +Context:~server~config +\end{lyxcode} +\begin{quote} +If you administer a site hosting many, many virtual hosts then this +option will appeal to you. If you activate LogSQLMassVirtualHosting +then several things happen: +\begin{itemize} +\item the on-the-fly table creation feature is activated automatically +\item the transfer log table name is dynamically set from the virtual host's +name (example: a virtual host www.grubbybaby.com gets logged to table +access\_www\_grubbybaby\_com) +\item which, in turn, means that each virtual host logs to its own segregated +table. Because there is no data shared between virtual servers you +can grant your users access to the tables they need; they will be +unable to view others' data. +\end{itemize} +This is a huge boost in convenience for sites with many virtual servers. +Activating LogSQLMassVirtualHosting obviates the need to create every +virtual server's table and provides more granular security possibilities. + +This is defined only once in the httpd.conf file. +\end{quote} + +\subsubsection{LogSQLPreserveFile} + +\begin{lyxcode} +Syntax:~LogSQLPreserveFile~filename~ + +Example:~LogSQLPreserveFile~offline-preserve~ + +Default:~mysql-preserve~(in~/tmp)~ + +Context:~virtual~host +\end{lyxcode} +\begin{quote} +mod\_log\_sql writes queries to this local preserve file in the event +that it cannot reach the database, and thus ensures that your high-availability +web frontend does not lose logs during a temporary database outage. +This could happen for a number of reasons: the database goes offline, +the network breaks, etc. You will not lose entries since the module +has this backup. The file consists of a series of SQL statements that +can be imported into your database at your convenience; furthermore, +because the SQL queries contain the access timestamps you do not need +to worry about out-of-order data after the import. + +For security purposes the name you supply will be prepended with \char`\"{}/tmp/\char`\"{} +to force creation of the file in /tmp. + +If you do not define LogSQLPreserveFile then all virtual servers will +log to the same default preserve file (/tmp/mysql-preserve). You can +redefine this on a virtual-host basis in order to segregate your preserve +files if you desire. Note that segregation is not really necessary, +as the SQL statements that are written to the preserve file already +distinguish between different virtual hosts. + +The module will log to error-log if/when it notices a database outage, +and upon database return. You will therefore know when the preserve +file is being used, although it is your responsibility to import the +file. + +The file does not need to be created in advance. It is safe to remove +or rename the file without interrupting Apache, as the module closes +the filehandle immediately after completing the write. The file is +created with the user \& group ID of the running Apache process (e.g. +'nobody' on many Linux distributions). +\end{quote} + +\subsubsection{LogSQLSocketFile } + +\begin{lyxcode} +Syntax:~LogSQLSocketFile~filename~ + +Example:~LogSQLSocketFile~/tmp/mysql.sock~ + +Default:~/var/lib/mysql/mysql.sock~ + +Context:~server~config +\end{lyxcode} +\begin{quote} +At Apache runtime you can specify the MySQL socket file to use. Set +this once in your main server config to override the default value. +This value is irrelevant if your database resides on a separate machine. + +mod\_log\_sql will automatically employ the socket for db communications +if the database resides on the local host. If the db resides on a +separate host the module will automatically use TCP/IP. This is a +function of the MySQL API and is not user-configurable. +\end{quote} + +\subsubsection{LogSQLTransferLogFormat } + +\begin{lyxcode} +Syntax:~LogSQLTransferLogFormat~format-string~ + +Example:~LogSQLTransferLogFormat~huSUTv~ + +Default:~AbHhmRSsTUuv~ + +Context:~virtual~host +\end{lyxcode} +\begin{quote} +Each character in the format-string defines an attribute of the request +that you wish to log. The default logs the information required to +create Combined Log Format logs, plus several extras. Here is the +full list of allowable keys, which sometimes resemble their Apache +counterparts, but do not always: + +\begin{tabular}{|c|l|l|l|l|} +\hline +\textbf{Character}& +\textbf{What is this?}& +\textbf{MySQL DB field}& +\textbf{Recommended column type}& +\textbf{Example}\\ +\hline +\hline +A& +User agent& +agent& +varchar(255)& +Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 4.0)\\ +\hline +b& +Bytes transfered& +bytes\_sent& +int unsigned& +32561\\ +\hline +c& +Text of cookie& +cookie& +varchar(255)& +Apache=sdyn.fooonline.net.130051007102700823\\ +\hline +f& +& +request\_file& +varchar(255)& +\\ +\hline +H& +HTTP request protocol& +request\_protocol& +varchar(10)& +HTTP/1.1\\ +\hline +h& +Name of remote host& +remote\_host& +varchar(50)& +blah.foobar.com\\ +\hline +I& +Ident user info& +remote\_logname& +varchar(50)& +bobby\\ +\hline +m& +HTTP request method& +request\_method& +varchar(6)& +GET\\ +\hline +P& +httpd child PID& +child\_pid& +smallint unsigned& +3215\\ +\hline +p& +httpd port& +server\_port& +smallint unsigned& +80\\ +\hline +R& +Referer& +referer& +varchar(255)& +http://www.biglinkstoyou.com/linkpage.html\\ +\hline +r& +Request in full form& +request\_line& +varchar(255)& +GET /books-cycroad.html HTTP/1.1\\ +\hline +S& +Time of request in UNIX format& +time\_stamp& +int unsigned& +1005598029\\ +\hline +s& +HTTP status of request& +status& +smallint unsigned& +404\\ +\hline +T& +Seconds to service request& +request\_duration& +smallint unsigned& +2\\ +\hline +t& +Time of request in human format& +request\_time& +char(28)& +{[}02/Dec/2001:15:01:26 -0800{]}\\ +\hline +U& +Request in simple form& +request\_uri& +varchar(255)& +/books-cycroad.html\\ +\hline +u& +User info from HTTP authentication& +remote\_user& +varchar(50)& +bobby\\ +\hline +v& +Virtual host servicing the request& +virtual\_host& +varchar(50)& +www.foobar.com\\ +\hline +\end{tabular} + +If you have compiled mod\_log\_sql with WANT\_SSL\_LOGGING, you also +get these: + +\begin{tabular}{|c|l|l|l|} +\hline +\textbf{Character}& +\textbf{MySQL DB field it activates}& +\textbf{MySQL DB field it activatesRecommended column type}& +\textbf{Example}\\ +\hline +\hline +z& +ssl\_cipher& +varchar(25)& +RC4-MD5\\ +\hline +q& +ssl\_keysize& +smallint unsigned& +56\\ +\hline +Q& +ssl\_maxkeysize& +smallint unsigned& +128\\ +\hline +\end{tabular} +\end{quote} + +\section{FAQ} + + +\subsection{Why log to an SQL database?} + +To begin with, let's get it out of the way: logging to a database +is not a panacea. But while there are complexities with this solution, +the benefit can be substantial for certain classes of administrator +or people with advanced requirements: + +\begin{itemize} +\item Chores like log rotation go away, as you can DELETE records from the +SQL database once they are no longer useful. For example, the excellent +and popular log-analysis tool Webalizer (http://www.webalizer.com/) +does not need historic logs after it has processed them, enabling +you to delete older logs. +\item People with clusters of web servers (for high availability) will benefit +the most -- all their webservers can log to a single SQL database. +This obviates the need to collate/interleave the many separate logfiles, +which can be / highly/ problematic. +\item People acquainted with the power of SQL SELECT statements will know +the flexibility of the extraction possibilities at their fingertips. +\end{itemize} +For example, do you want to see all your 404's? Do this: + +\begin{lyxcode} +select~remote\_host,status,request\_uri,bytes\_sent,from\_unixtime(time\_stamp)~from~acc\_log\_tbl~where~status=404~order~by~time\_stamp; + ++-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-+-{}-{}-{}-{}-{}-{}-{}-+-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-+-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-+-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}- + ++~~~~~~~~~~~~~~~~~~~~~~~remote\_host~|~status~|~request\_uri~~~~~~~~~~~~~~|~bytes\_sent~|~from\_unixtime(time\_stamp)| + ++-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-+-{}-{}-{}-{}-{}-{}-{}-+-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-+-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-+-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}- + +|~marge.mmm.co.uk~~~~~~~~~~~~~~~~~~~|~404~~~~|~/favicon.ico~~~~~~~~~~~~~|~321~~~~~~~~|~2001-11-20~02:30:56~~~~~~| + +|~62.180.239.251~~~~~~~~~~~~~~~~~~~~|~404~~~~|~/favicon.ico~~~~~~~~~~~~~|~333~~~~~~~~|~2001-11-20~02:45:25~~~~~~| + +|~212.234.12.66~~~~~~~~~~~~~~~~~~~~~|~404~~~~|~/favicon.ico~~~~~~~~~~~~~|~321~~~~~~~~|~2001-11-20~03:01:00~~~~~~|~ + +|~212.210.78.254~~~~~~~~~~~~~~~~~~~~|~404~~~~|~/favicon.ico~~~~~~~~~~~~~|~333~~~~~~~~|~2001-11-20~03:26:05~~~~~~|~ + ++-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-+-{}-{}-{}-{}-{}-{}-{}-+-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-+-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-+-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-+ +\end{lyxcode} +Or do you want to see how many bytes you've sent within a certain +directory or site? Do this: + +\begin{lyxcode} +select~request\_uri,sum(bytes\_sent)~as~bytes,count(request\_uri)~as~howmany~from~acc\_log\_tbl~where~request\_uri~like~'\%mod\_log\_sql\%'~group~by~request\_uri~order~by~howmany~desc;~ + ++-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-+-{}-{}-{}-{}-{}-{}-{}-{}-+-{}-{}-{}-{}-{}-{}-{}-{}-+~ + +|~request\_uri~~~~~~~~~~~~~~~~~~~~~~~|~bytes~~~|~howmany~|~ + ++-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-+-{}-{}-{}-{}-{}-{}-{}-{}-+-{}-{}-{}-{}-{}-{}-{}-{}-+~ + +|~/mod\_log\_sql/style\_1.css~~~~~~~~~~|~157396~~|~~~~1288~|~ + +|~/mod\_log\_sql/~~~~~~~~~~~~~~~~~~~~~|~2514337~|~~~~~801~|~ + +|~/mod\_log\_sql/mod\_log\_sql.tar.gz~~~|~9769312~|~~~~~456~|~ + +|~/mod\_log\_sql/faq.html~~~~~~~~~~~~~|~5038728~|~~~~~436~|~ + +|~/mod\_log\_sql/INSTALL~~~~~~~~~~~~~~|~1196161~|~~~~~202~|~ + +|~/mod\_log\_sql/directives.html~~~~~~|~1096821~|~~~~~171~|~ + +|~/mod\_log\_sql/CHANGELOG~~~~~~~~~~~~|~424481~~|~~~~~107~|~ + +|~/mod\_log\_sql/README~~~~~~~~~~~~~~~|~796072~~|~~~~~~97~|~ + +|~/mod\_log\_sql/directives-old.html~~|~42480~~~|~~~~~~~6~| + +|~/mod\_log\_sql/index.html~~~~~~~~~~~|~9531~~~~|~~~~~~~3~|~ + ++-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-+-{}-{}-{}-{}-{}-{}-{}-{}-+-{}-{}-{}-{}-{}-{}-{}-{}-+ +\end{lyxcode} +Or maybe you want to see who's linking to you? Do this: + +\begin{lyxcode} +select~count(referer)~as~num,referer~from~acc\_log\_tbl~where~request\_uri='/mod\_log\_sql/'~group~by~referer~order~by~num~desc; + ++-{}-{}-{}-{}-+-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-+ + +|~num~|~referer~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|~ + ++-{}-{}-{}-{}-+-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-+ + +|~271~|~http://freshmeat.net/projects/mod\_log\_sql/~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|~ + +|~96~~|~http://modules.apache.org/search?id=339~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|~ + +|~48~~|~http://freshmeat.net/~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|~ + +|~8~~~|~http://freshmeat.net~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| + +|~7~~~|~http://freshmeat.net/daily/2001/11/30/~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|~ + +|~6~~~|~http://freshmeat.net/daily/2001/11/20/~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| + ++-{}-{}-{}-{}-+-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-+ +\end{lyxcode} +As you can see, there are myriad possibilities that can be constructed +with the wonderful SQL SELECT statement. Logging to an SQL database +can be really quite useful! + + +\subsection{Why use MySQL? Are there alternatives?} + +MySQL is a robust, free, and very powerful production-quality database +engine. It is well supported and comes with detailed documentation. +Many 3rd-party software pacakges (e.g. Slashcode, the engine that +powers Slashdot) run exclusively with MySQL. In other words, you will +belong to a very robust and well-supported community by choosing MySQL. + +That being said, there are alternatives. PostgreSQL is probably MySQL's +leading \char`\"{}competitor\char`\"{} in the free database world. +There is also an excellent module available for Apache to permit logging +to a PostgreSQL database, called pgLOGd \url{(http://www.digitalstratum.com/pglogd/)}. + + +\subsection{Is this code production-ready?} + +By all accounts it is. It is known to work without a problem on many-thousands-of-hits-per-day +webservers. Does that mean it is 100\% bug free? Well, no software +is. But it is well-tested and /believed/ to be fully compatible with +production environments. (The usual disclaimers apply. This software +is provided without warranty of any kind.) + + +\subsection{How well does it perform?} + +mod\_log\_sql scales to very high loads. Apache 1.3.22 + mod\_log\_sql +was benchmarked using the \char`\"{}ab\char`\"{} (Apache Bench) program +that comes with the Apache distribution; here are the results. + +Overall configuration: + +\begin{itemize} +\item Machine A: Apache webserver +\item Machine B: MySQL server +\item Machines A and B connected with 100Mbps Ethernet +\end{itemize} +Webserver configuration: + +\begin{itemize} +\item Celeron 400 128 MB RAM IDE storage +\end{itemize} +Apache configuration: + +\begin{lyxcode} +Timeout~300~ + +KeepAlive~On~ + +MaxKeepAliveRequests~100~ + +KeepAliveTimeout~15~ + +MinSpareServers~5~ + +StartServers~10~ + +MaxSpareServers~15~ + +MaxClients~256~ + +MaxRequestsPerChild~5000~ + +LogSQLTransferLogFormat~AbHhmRSsTUuvc~ + +LogSQLWhichCookie~Clicks~ + +CookieTracking~on~ + +CookieName~Clicks +\end{lyxcode} +\char`\"{}ab\char`\"{} commandline: + +\begin{lyxcode} +./ab~-c~10~-t~20~-v~2~-C~Clicks=ab\_run~http://www.hostname.com/target~ +\end{lyxcode} +( 10 concurrent requests; 20 second test; setting a cookie \char`\"{}Clicks=ab\_run\char`\"{}; +target = the mod\_log\_sql homepage. ) + +Ten total ab runs were conducted: five with MySQL logging enabled, +and five with all MySQL directives commented out of httpd.conf. Then +each five were averaged. The results: + +\begin{itemize} +\item Average of five runs employing MySQL \emph{and} standard text logging: +139.01 requests per second, zero errors. +\item Average of five runs employing \emph{only} standard text logging: +139.96 requests per second, zero errors. +\end{itemize} +In other words, any rate-limiting effects on this particular hardware +setup are not caused by MySQL. Note that although this very simple +webserver setup is hardly cutting-edge -- it is, after all, a fairly +small machine -- 139 requests per second equal over twelve million +hits per day\emph{.} + +If you run this benchmark yourself, take note of three things: + +\begin{enumerate} +\item Use a target URL that is on your own webserver :-). +\item Wait until all your connections are closed out between runs; after +several thousand requests your TCP/IP stack will be filled with hundreds +of connections in TIME\_WAIT that need to close. Do a \char`\"{}netstat +-t|wc -l\char`\"{} on the webserver to see. If you don't wait, you +can expect to see a lot of messages like \char`\"{}ip\_conntrack: +table full, dropping packet\char`\"{} in your logs. (This has nothing +to do with mod\_log\_sql, this is simply the nature of the TCP/IP +stack in the Linux kernel.) +\item When done with your runs, clean these many thousands of requests out +of your database: +\end{enumerate} +\begin{lyxcode} +mysql>~delete~from~access\_log~where~agent~like~'ApacheBench\%';~mysql>~optimize~table~access\_log;~ +\end{lyxcode} + +\subsection{Who's using mod\_log\_sql?} + +Good question! It would be great to find out! If you are a production-level +mod\_log\_sql user, please contact the maintainer, Chris Powell \url{chris@grubbybaby.com}so +that you can be mentioned here. + + +\subsection{How do I extract the data in a format that my analysis tool can understand?} + +mod\_log\_sql would be virtually useless if there weren't a way for +you to extract the data from your database in a somewhat meaningful +fashion. To that end there's a Perl script enclosed with the distribution. +That script (make\_combined\_log.pl) is designed to extract N-many +days worth of access logs and provide them in a Combined Log Format +output. You can use this very tool right in /etc/crontab to extract +logs on a regular basis so that your favorite web analysis tool can +read them. Or you can examine the Perl code to construct your own +custom tool. + +For example, let's say that you want your web statistics updated once +per day in the wee hours of the morning. A good way to accomplish +that would be the following entries in /etc/crontab: + +\begin{lyxcode} +\#~Generate~the~temporary~apache~logs~from~the~MySQL~database~(for~webalizer)~ + +05~04~{*}~{*}~{*}~root~/usr/local/sbin/make\_combined\_log.pl~1~www.grubbybaby.com~>~/var/log/httpd/mysql-grubbybaby + +\#~Run~webalizer~on~httpd~log~ + +30~04~{*}~{*}~{*}~root~/usr/local/bin/webalizer~-c~/etc/webalizer.conf;~rm~-f~/var/log/httpd/mysql-grubbybaby +\end{lyxcode} +Or if you have a newer system that puts files in /etc/cron.daily etc., +create a file called ''webalizer'' in the cron.\_\_\_\_ subdir of +your choice. Use the following as the contents of your file, and make +sure to chmod 755 it when done. + +\begin{lyxcode} +\#!/bin/sh + +/usr/local/sbin/make\_combined\_log.pl~1~www.yourdomain.com~>~/var/log/httpd/templog + +/usr/local/bin/webalizer~-q~-c~/etc/webalizer.conf~ + +rm~-f~/var/log/httpd/templog +\end{lyxcode} +See? Easy. + + +\subsection{Why doesn't the module also replace the Apache ErrorLog?} + +There are circumstances when that would be quite unwise -- for example, +if Apache could not reach the MySQL server for some reason and needed +to log that fact. Without a text-based error log you'd never know +anything was wrong, because Apache would be trying to log a database +connection error to the database... you get the point. + +Error logs are usually not very high-traffic and are really best left +as text files on a web server machine. + + +\subsection{\label{sec:cookie}How can I log mod\_usertrack cookies?} + +A number of people like to log mod\_usertrack cookies in their Apache +TransferLog to aid in understanding their visitors' clickstreams. +This is accomplished, for example, with a statement as follows: + +\begin{lyxcode} +LogFormat~\char`\"{}\%h~\%l~\%u~\%t~\textbackslash{}\char`\"{}\%r\textbackslash{}\char`\"{}~\%s~\%b~\textbackslash{}\char`\"{}\%\{Referer\}i\textbackslash{}\char`\"{}~\textbackslash{}\char`\"{}\%\{User-Agent\}i\textbackslash{}\char`\"{}\char`\"{}~\textbackslash{}\char`\"{}\%\{cookie\}n\textbackslash{}\char`\"{}\char`\"{} +\end{lyxcode} +Naturally it would be nice for mod\_log\_sql to permit the admin to +log the cookie data as well, so as of version 1.10 you can do this. +You need to have already compiled mod\_usertrack into httpd -- it's +one of the standard Apache modules. + +First make sure you have a column called \char`\"{}cookie\char`\"{} +in the MySQL database to hold the cookies, which can be done as follows +if you already have a working database: + +\begin{lyxcode} +alter~table~acc\_log\_tbl~add~column~cookie~varchar(255); +\end{lyxcode} +Next configure your server to set usertracking cookies as follows, +and make sure you include the new 'c' directive in your LogSQLTransferLogFormat, +which activates cookie logging. Here's an example: + +\begin{lyxcode} +~ + +~CookieTracking~on~ + +~CookieStyle~Cookie~ + +~CookieName~Foobar~ + +~LogSQLTransferLogFormat~huSUsbTvRAc~ + +~LogSQLWhichCookie~Foobar~ + + +\end{lyxcode} +The first three lines configure mod\_usertrack to create a COOKIE +(RFC 2109) format cookie called Foobar. The last two lines tell mod\_log\_sql +to log cookies named Foobar. You have to choose which cookie to log +because more than one cookie can/will be sent to the server by the +client. + +FYI, you are advised NOT to use CookieStyle Cookie2 -- it seems that +even newer browsers (IE 5.5, etc.) have trouble with the new COOKIE2 +(RFC 2965) format. Just stick with the standard COOKIE format and +you'll be fine. + +Perform some hits on your server and run a select: + +\begin{lyxcode} +mysql>~select~request\_uri,cookie~from~access\_log~where~cookie~is~not~null; + ++-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-+-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-+ + +|~request\_uri~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~cookie~|~ + ++-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-+-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-+~ + +|~/mod\_log\_sql/~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|~ool-18e4.dyn.optonline.net.130051007102700823~~~~~~|~ + +|~/mod\_log\_sql/usa.gif~~~~~~~~~~~~~~~~~~~~~~~|~ool-18e4.dyn.optonline.net.130051007102700823~~~~~~|~ + +|~/mod\_log\_sql/style\_1.css~~~~~~~~~~~~~~~~~~~|~ool-18e4.dyn.optonline.net.130051007102700823~~~~~~|~ + +...etc... +\end{lyxcode} + +\subsection{What if I want to log more than one cookie?} + +No problem. As of version 1.17, you have a choice. If you are just +interested in a single cookie, follow the instructions in section +\ref{sec:cookie} above. That cookie will be logged to a column in +the regular access\_log table. + +However, if you need to log multiple cookies, you'll employ the LogSQLWhichCookies +(note the plural) directive. The cookies you specify will be logged +to a separate table, and entries in that table will be linked to the +regular access\_log entries via the unique ID that is supplied by +mod\_unique\_id. Without mod\_unique\_id the information will still +be logged but you will be unable to correlate which cookies go with +which access-requests. + +LogSQLWhichCookie and LogSQLWhichCookies can coexist without conflict, +but you're better off choosing the one you need. + + +\subsection{What are the SSL logging features, and how do I activate them?} + +If you run an SSL-enabled server you may benefit from logging some +SSL details. mod\_log\_sql now supports this ability. By adding certain +characters to your LogSQLTransferLogFormat string you can tell mod\_log\_sql +to log the SSL cipher, the SSL keysize of the connection, and the +Max-keysize that was available. This would let you tell, for example, +which clients were using only export-grade security to access your +secure software area. + +You can compile mod\_log\_sql with SSL logging support if you have +the right packages installed. If you already have an SSL-enabled Apache +then you by definition have the correct packages already installed: +OpenSSL and mod\_ssl. + +You need to ensure that your database is set up to log the SSL data. +Issue the following commands to MySQL once you have your basic access\_log +table built: + +\begin{lyxcode} +alter~table~access\_log~add~column~ssl\_cipher~varchar(25); + +alter~table~access\_log~add~column~ssl\_keysize~smallint~unsigned; + +alter~table~access\_log~add~column~ssl\_maxkeysize~smallint~unsigned; +\end{lyxcode} +Finally configure httpd.conf to activate the SSL fields. Note that +this is only meaningful in a VirtualHost that is set up for SSL. + +\begin{lyxcode} +~ + +~LogSQLTransferLogFormat~AbHhmRSsTUuvcQqz~ + + +\end{lyxcode} +The last three characters (Qqz) in the directive are the SSL ones; +see the directives documentation for details. + +Perform some hits on your server and run a select: + +\begin{lyxcode} +mysql>~select~remote\_host,request\_uri,ssl\_cipher,ssl\_keysize,ssl\_maxkeysize~from~access\_log~where~ssl\_cipher~is~not~null; + ++-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-+-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-+-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-+-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-+-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-+~ + +|~remote\_host~~~~~~~~~~~~~~|~request\_uri~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|~ssl\_cipher~|~ssl\_keysize~|~ssl\_maxkeysize~| + ++-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-+-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-+-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-+-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-+-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-+~ + +|~216.190.52.4~~~~~~~~~~~~~|~/dir/somefile.html~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|~RC4-MD5~~~~|~128~~~~~~~~~|~128~~~~~~~~~~~~|~ + +|~216.190.52.4~~~~~~~~~~~~~|~/dir/somefile.gif~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|~RC4-MD5~~~~|~128~~~~~~~~~|~128~~~~~~~~~~~~|~ + +|~216.190.52.4~~~~~~~~~~~~~|~/dir/somefile.jpg~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|~RC4-MD5~~~~|~128~~~~~~~~~|~128~~~~~~~~~~~~|~ + +...etc... +\end{lyxcode} + +\subsection{Does mod\_log\_sql connect to MySQL via TCP/IP or a socket?} + +It depends! Actually this isn't determined by mod\_log\_sql. mod\_log\_sql +relies on a connection command that is supplied in the MySQL API, +and that command is somewhat intelligent. When mod\_log\_sql issues +the connect command to MySQL, this intelligent connect command uses +sockets to communicate with MySQL if the specified MySQL database +is on the same machine (because sockets are more efficient than TCP/IP). +However, if the specified MySQL db is on a different machine, mod\_log\_sql +connects using TCP/IP. You don't have any control of which methodology +is used. + +You do have control over where mod\_log\_sql looks for the socket. +The LogSQLSocketFile runtime configuration directive overrides the +default of \char`\"{}/var/lib/mysql/mysql.sock\char`\"{} to whatever +you wish. (Applies to mod\_log\_sql 1.16 or later only.) + + +\subsection{Why do I occasionally see a \char`\"{}connection lost, attempting +reconnect\char`\"{} message in my error-log?} + +This message may appear every now and then in your Apache error log, +especially on very lightly loaded servers. This doesn't mean that +anything is necessarily wrong. Within each httpd child process, mod\_log\_sql +will open (and keep open) a connection to the MySQL server. MySQL, +however, will close connections that haven't been used in a while; +the default timeout is 8 hours. When this occurs, mod\_log\_sql will +notice and re-open the connection. That event is what is being logged, +and looks like this: + +\begin{lyxcode} +{[}Thu~Dec~13~05:42:18~2001{]}~{[}error{]}~mod\_log\_sql:~connection~lost,~attempting~reconnect + +{[}Thu~Dec~13~05:42:18~2001{]}~{[}error{]}~mod\_log\_sql:~reconnect~successful +\end{lyxcode} +Reference: MySQL documentation \url{http://www.mysql.com/documentation/mysql/bychapter/manual_Problems.html#Gone_away} + + +\subsection{Does mod\_log\_sql work with Apache 2.x?} + +As of this writing, no. The Apache Group significantly altered the +module API with the release of Apache 2.0. All modules written for +1.3, including mod\_log\_sql, will not work with 2.0. + +mod\_log\_sql will eventually be ported to Apache 2.x, but not immediately. +It is going to take some time, and there are other features that have +higher priority. Please sign up for the announcements list (on the +main website) or monitor the website for updates to learn when the +port (and other releases) are available. + +If you're a {*}NIX user, stick with Apache 1.3.x for now. +Major modules like mod\_ssl and PHP are not even ready for 2.0 yet, +and the main benefits in 2.0 are for Win32 users anyway. Apache 1.3.x +is rock-stable and performs equally well on {*}NIX as 2.0. + + +\subsection{I have discovered a bug. Who can I contact?} + +Please contact the maintainer \url{chris@grubbybaby.com}! Your comments, +suggestions, bugfixes, bug catches, and usage testimonials are always +welcome. As free software, mod\_log\_sql is intended to be a community +effort -- any code contributions or other ideas will be fully and +openly credited, of course. +\end{document} diff --git a/INSTALL b/INSTALL index 7c48c21..20a4035 100644 --- a/INSTALL +++ b/INSTALL @@ -1,26 +1,27 @@ -$Id: INSTALL,v 1.9 2002/06/27 20:09:17 helios Exp $ +$Id: INSTALL,v 1.10 2002/11/14 03:51:34 helios Exp $ Requirements ============ -* I run a Red Hat 6.2 system, but these instructions should easily - adapt to any modern distro. +* A compatible system. I have run mod_log_sql on Red Hat based systems + (Red Hat, Mandrake). These instructions should easily adapt to any + modern distro. -* Apache 1.2 or 1.3 installed. (I run 1.3.22 and it works fine). +* Apache 1.2 or 1.3 installed. (I run 1.3.22 and it works fine). You should have already successfully compiled Apache and know what - you're doing there. In fact, you should already have any other - modules and add-ons like mod_ssl or PHP configured and installed - before you start this process. + you're doing there. -* The MySQL development headers. (I run MySQL-devel-3.23.44-1.i386.rpm). +* The MySQL development headers. This is called different things on + different distros. For example, Red Hat 6.x called this RPM + "MySQL-devel" whereas Mandrake calls it "libmysql10-devel". * MySQL >= 3.23.15 configured, installed and running on either localhost or an accessible networked machine. You should already have a basic understanding of MySQL and how it functions. * Again, basic administrative skills with Apache and MySQL. I try to - make things as easy as possible in this README, but its purpose is + make things as easy as possible in this file, but its purpose is not to be an administrative tutorial. * Additionally, if you want to be able to log SSL information such as @@ -37,9 +38,13 @@ doing that. If you're more of an old-school type and prefer to compile the modules right into apache, do that. Both methods work equally well. +FWIW, the DSO method is more modern and increasing in popularity because +apxs takes care of a lot of dirty little details for you. As you'll +see below, the static-module method is a little more complex. -Installation (as an Apache DSO) -=============================== + +Installation as an Apache DSO (Preferred) +========================================= For folks interested in using this module as an Apache DSO: @@ -51,41 +56,68 @@ For folks interested in using this module as an Apache DSO: # tar zxf mod_log_sql.tar.gz -C /usr/local/src # cd /usr/local/src/mod_log_sql -2) Edit Makefile and make any adjustments for your system. These are - fully explained in the Makefile. +2) Edit Makefile for your system. -3) Instruct apxs to compile and install the module as a DSO. You need - to know two things before you run apxs: - - The location of the apxs binary, find using 'locate apxs' + NECESSARY: + - The location where you installed Apache -- usually /usr/local/apache, + 'locate apxs' can help you find it. - The location of your MySQL libraries, find using 'locate libmysqlclient' + - The location of your MySQL header files, find using 'locate mysql.h' + + OPTIONAL if you have included mod_ssl in Apache and want to log SSL data + such as keysize and cipher type: + - The location of your SSL header files, find using 'locate mod_ssl.h' + + Now that you know these things, edit Makefile and replace the stock + values with your own. + + IMPORTANT: If you are not logging SSL info, comment out MODSSLHDRS by putting + a # character in front of it, e.g. #MODSSLHDRS=/usr/include/... + +3) Instruct apxs to compile the module as a DSO. + + # make dso - FORMAT: - # /apxs -i -c -L/path/to/mysqllibs -lmysqlclient -lz mod_log_sql.c + You should see output similar to the following: - EXAMPLE: - # /usr/sbin/apxs -i -c -L/usr/lib/mysql -lmysqlclient -lz mod_log_sql.c + /usr/local/Apache/bin/apxs -Wc,-O2 -Wc,-Wall -Wc,-DEAPI -c -I/usr/include/mysql -I/usr/local/src/apache_1.3.27-dso/src/modules/ssl -L/usr/lib -lmysqlclient -lz mod_log_sql.c + gcc -DLINUX=22 -DNO_DBM_REWRITEMAP -DMOD_SSL=208111 -DUSE_HSREGEX -DEAPI -DUSE_EXPAT -I../lib/expat-lite -fpic -DSHARED_CORE -DSHARED_MODULE -I/usr/local/Apache/include -O2 -Wall -DEAPI -I/usr/include/mysql -I/usr/local/src/apache_1.3.27-dso/src/modules/ssl -c mod_log_sql.c + gcc -shared -o mod_log_sql.so mod_log_sql.o -Wc,-O2 -Wc,-Wall -Wc,-DEAPI -L/usr/lib -lmysqlclient -lz -lm -lcrypt -ldb - You should see something similar to this: + You should see no errors and have a file called "mod_log_sql.so" in your + directory. - gcc -fpic -DSHARED_MODULE -I/usr/local/apache/include -c mod_log_mysql.c - gcc -shared -o mod_log_mysql.so mod_log_mysql.o -L/usr/local/lib/mysql/ -lmysqlclient -lz - cp mod_log_mysql.so /usr/local/apache/libexec/mod_log_mysql.so - chmod 755 /usr/local/apache/libexec/mod_log_mysql.so +4) Instruct apxs to install the DSO. -4) Add the following lines to your httpd.conf file. Put the second line - somewhere after the ClearModuleList directive. + # make dsoinstall - LoadModule mysql_log_module libexec/mod_log_sql.so + You should see output similar to the following: - AddModule mod_log_sql.c + /usr/local/Apache/bin/apxs -i mod_log_sql.so + cp mod_log_sql.so /usr/local/Apache/libexec/mod_log_sql.so + chmod 755 /usr/local/Apache/libexec/mod_log_sql.so -5) Now go to step (9) in the instructions below to configure httpd.conf... +5) Module ordering within httpd.conf is important. If you are logging + SSL, you must make sure that + LoadModule ssl_module libexec/libssl.so -Installation (as a static module compiled into httpd) -===================================================== + comes before -[This is what I do, FYI.] + LoadModule sql_log_module libexec/mod_log_sql.so + + If you don't, you will get this error when you start Apache: + /usr/local/apache/libexec/mod_log_mysql.so: undefined symbol: ssl_var_lookup + /usr/local/apache/bin/apachectl startssl: httpd could not be started + + (Because mod_log_sql doesn't yet have the required symbols that mod_ssl + provides.) + +6) Now skip below to the "Configuration" section. + + +Installation as a static module compiled into httpd +=================================================== 0) Perform all the following steps as root so that you have install privs, etc. @@ -95,17 +127,31 @@ Installation (as a static module compiled into httpd) # tar zxf mod_log_sql.tar.gz -C /usr/local/src # cd /usr/local/src/mod_log_sql -2) Examine the DEFINEs at the top of mod_log_sql.c and alter any that - you choose. Edit Makefile and make any adjustments for your system. - These are fully explained in the Makefile. +2) Edit Makefile for your system. -3) # make all - (You should receive NO warnings or errors of any kind. - If you see messages like this: "mod_log_sql.c:69: httpd.h: No such - file or directory" then you do not have your CFLAGS correctly - pointing to the right include directory.) + NECESSARY: + - The location where you installed Apache -- usually /usr/local/apache, + 'locate apxs' can help you find it. + - The location of your Apache *sources*, find using 'locate ABOUT_APACHE' + - The location of your MySQL header files, find using 'locate mysql.h' + - The location of your MySQL libraries, find using 'locate libmysqlclient' -4) # make install + OPTIONAL if you have included mod_ssl in Apache and want to log SSL data + such as keysize and cipher type: + - The location of your mod_ssl header files, find using 'locate mod_ssl.h' + - The location of your OpenSSL header files, find using 'locate x509.h' + - The location of your db1 header files, find using 'locate mpool.h' + + Now that you know these things, edit Makefile and replace the stock + values with your own. + + IMPORTANT: If you are not logging SSL info, comment out MODSSLHDRS, + OPNSSLHDRS and DB1HDRS by putting a # character in front of each one, + e.g. #OPNSSLHDRS=/usr/include/... + +3) # make static + +4) # make statinstall 5) Change to your Apache source dir. @@ -115,11 +161,14 @@ Installation (as a static module compiled into httpd) 6a) Edit Configuration.apaci as follows... - * Append the following string to the EXTRA_LIBS= line. (/usr/lib/mysql is where your libmysqlclient.a file lives): - -L/usr/lib/mysql -lmysqlclient -lm -lz + * Append the following string to the EXTRA_LIBS= line. ("/usr/lib/mysql" + is from step 2, where your MySQL libraries live): + + -L/usr/lib/mysql -lmysqlclient -lm -lz * Find the mod_log_config.o line, and add this line immediately after it: - AddModule modules/sql/mod_log_sql.o + + AddModule modules/sql/mod_log_sql.o 6b) # cp Configuration.apaci Configuration @@ -146,100 +195,113 @@ Installation (as a static module compiled into httpd) 8) Install your httpd binary. Copy it over your old httpd binary, wherever it lives. You can and should rename your old httpd first so - that you can easily revert to that working version in case of bugs or - whatever. - + that you can easily revert to that working version in case of bugs + with the new version. + # /etc/rc.d/init.d/httpd stop + # mv /usr/local/Apache/bin/httpd ~/httpd-save # cp -f ./httpd /usr/local/Apache/bin/ -9) Configure your apache daemon to log to your database. Here's a very - basic set of config lines to start you off. Full documentation is - available here: http://www.grubbybaby.com/mod_log_sql/directives.html - EXAMPLE: Connect to the MySQL database called "apache" running - on "dbmachine.foo.com". The module uses username "loguser" and - password "l0gger" to authenticate to the database; this user must, - of course, exist in the MySQL user table and have the proper - permissions -- more on that in step 11. The log entries will be - INSERTed into the table called "access_log". +Configuration +============= +You have to prepare the database to receive data from mod_log_sql, and +set up run-time directives in httpd.conf to control how and what mod_log_sql +logs. - MySQLLoginInfo dbmachine.foo.com loguser l0gger - MySQLDatabase apache +This section will discuss how to get started with a basic config. Full +documentation of the run-time directives is available here: +http://www.grubbybaby.com/mod_log_sql/directives.html - - [snip] +1) mod_log_sql can make its own tables on-the-fly, or you can pre-make + the tables by hand. The advantage of letting the module make the + tables is ease-of-use, but for raw performance you will want to + pre-make the tables in order to save overhead. - MySQLTransferLogTable access_log - MySQLTransferLogFormat huSUsbTvRA + In this basic setup we'll let the module create tables for us. - [snip] - + We still need to have a logging database created and ready, so + run the MySQL command line client and create a database: + # mysql -uadmin -pmypassword + mysql> create database apachelogs; - 9a) Special step for users who have a DSO-enabled httpd: + If you want to hand-create the tables, run the enclosed 'create-tables' + SQL script as follows: - If you you are building mod_log_sql as a static module BUT - your httpd is enabled for DSOs, add the following line to your - httpd.conf: + mysql> source create_tables.sql - AddModule mod_log_sql.c +2) Create a specific MySQL userid that httpd will use to authenticate + and enter data. This userid need not be an actual Unix user. It + is a userid internal to MySQL with specific privileges. + In the following example command, "apachelogs" is the database, "loguser" + is the userid to create, "my.apachemachine.com" is the name of the Apache + machine, and "l0gger" is the password to assign. Choose values that are + different from these examples. -10) If you compiled mod_log_sql with the ability to make its own tables - then you can skip this step. Otherwise you need to do it by hand: + mysql> grant insert,create on apachelogs.* to loguser@my.apachemachine.com identified by 'l0gger'; - Create a database and table to hold the new log data. I log the - same data as the regular "combined log" plus a little extra information - that can be useful. + You may be especially security-paranoid and not want "loguser" to have + "create" capability within the "apachelogs" databse. You can disable that + but the cost is that you cannot use the module's automatic-table-creation + feature. If that's an acceptable cost, hand-create the tables as described + in step 1 and use the following GRANT statement instead of the one above: - The order that the fields appear in the table is irrelevant - because you can SELECT them in any order you choose. To create - this table I first created a new database called "apache": + mysql> grant insert on apachelogs.* to loguser@my.apachemachine.com identified by 'l0gger'; - # mysql -uadmin -pmypassword - mysql> create database apache; +3) Enable full logging of your MySQL daemon (at least temporarily + for debugging purposes) if you don't do this already: - Then create the table called "access_log". I enclosed an SQL file - that will create every column type that mod_log_sql supports. - Unless you're just testing or playing around, this is probably NOT - what you want, so edit the file first and delete the lines that - don't pertain to you. Then: + Edit /etc/my.cnf and add the following line to your [mysqld] section: - mysql> source access_log.sql + log=/var/log/mysql-messages + Then restart MySQL. -11) Create a specific MySQL userid that httpd will use to authenticate - and enter data. This userid need not be an actual Unix user. It - is a userid internal to MySQL with specific privileges. - - mysql> grant insert,create on apache.* to loguser@my.apachemachine.com identified by 'l0gger'; - - Security is a very real concern. mod_log_sql by default is - set up to create the SQL tables it needs. If you have deactivated - this capability, then create a user called "loguser" with the password - "l0gger" with only the capability of INSERT to "access_log": +4) Tell the module what database to use and the appropriate authentication + information. - mysql> grant insert on apache.access_log to loguser@my.apachemachine.com identified by 'l0gger'; + OUR EXAMPLE: use the MySQL database called "apachelogs" running + on "dbmachine.foo.com". The module uses username "loguser" and + password "l0gger" to authenticate to the database. The log entries + will be INSERTed into the table called "access_log". + So, edit httpd.conf and insert the following lines somewhere AFTER any + LoadModule / AddModule statements. Make sure these statements are + "global," i.e. not inside any VirtualHost stanza. -12) Enable full logging of your MySQL daemon (at least temporarily - for debugging purposes) if you don't do this already: + LogSQLDatabase apachelogs + LogSQLLoginInfo dbmachine.foo.com loguser l0gger + LogSQLCreateTables on - Edit /etc/my.cnf and add the following line to your [mysqld] section: + If your database resides on localhost instead of another host, specify + the MySQL server's socket file as follows: - log=/var/log/mysql-messages + LogSQLSocketFile /your/path/to/mysql.sock - Then restart MySQL. +5) The actual logging is set up on a virtual-host-by-host basis. So, + skip down to the virtual host you want to set up. The LogSQLTransferLogTable + directive is the minimum required to log -- other directives simply + tune the module's behavior. -13) Restart apache. + + [snip] + + LogSQLTransferLogTable access_log + + [snip] + + +6) Restart apache. # /etc/rc.d/init.d/httpd start -13) Load your web site in a browser to trigger some hits, then confirm that +7) Load your web site in a browser to trigger some hits, then confirm that the entries are being successfully logged: - # mysql -hmysql.host.com -umysqladmin -p -e "select * from access_log" apache; + # mysql -hmysql.host.com -umysqladmin -p -e "select * from access_log" apachelogs Enter password: +---------------------------------------------------+-------------+-------------+------------------+------------------+------------+--------+------------+------------------------------------+ @@ -251,10 +313,29 @@ Installation (as a static module compiled into httpd) . +---------------------------------------------------+-------------+-------------+------------------+------------------+------------+--------+------------+------------------------------------+ -14) You have basic functionality. Don't disable your regular Apache logs until + You have basic functionality. Don't disable your regular Apache logs until you feel comfortable that the database is behaving as you'd like and that - things are going well. - - - - + things are going well. + +8) If you do not see any entries in the access_log, then something is preventing + the inserts from happening. This problem could be caused by several things: + - Improper privileges set up in the MySQL database + - You aren't hitting a VirtualHost that has a LogSQLTransferLogTable entry + - You didn't specify the right host + + If you have confirmed your LogSQL* directives and know them to be correct, + you should examine the httpd server logs for mod_log_sql messages; the module + will offer hints as to why it cannot connect, etc. Also examine the MySQL + log that you established in step 3. Ensure that the INSERTs are not being + rejected because of a malformed table entry or other clerical error. If you + see no INSERT attempts in the log, the module isn't successfully connecting + to the database. + + The next thing to do is recompile the module with debugging output activated. + change the "#undef DEBUG" on line 8 of mod_log_sql.c to "#define DEBUG" and + recompile/reinstall. The module will now output copious notes about what + it is doing, and this will help you (and the maintainer) solve the problem. + +9) You can now activate the advanced features of mod_log_sql. These are all + described in the online directive documentation: + http://www.grubbybaby.com/mod_log_sql/directives.html diff --git a/Makefile b/Makefile index ed31430..8c654a6 100644 --- a/Makefile +++ b/Makefile @@ -1,88 +1,71 @@ -# $Id: Makefile,v 1.11 2002/09/04 18:46:00 helios Exp $ -MLMVERS = 1.17 - -# Where you unpacked your Apache tarball -- the source. -APACHESOURCE = /usr/local/src/apache_1.3.26 - -# Where Apache [got|will get] installed -APACHEINST = /usr/local/Apache - -# Do you want to log SSL information? -# Yes? -# - #define WANT_SSL_LOGGING in mod_log_sql.c -# - pick (A) below -# No? -# - #undef WANT_SSL_LOGGING in mod_log_sql.c -# - pick (B) below - - -# (A) -# -# Modify "/usr/include/mysql" to where YOUR mysql.h can be found, -# Modify "/usr/local/ssl/include" to where YOUR openssl/*.h files are, -# Modify "/usr/include/db1" to where YOUR ndbm.h can be found, -# Modify "/usr/local/src/apache_1.3.22/src/modules/ssl" to where YOUR mod_ssl.h can be found. -# -# How to find your directories: -# -# $ locate mysql.h -# /usr/include/mysql/mysql.h -# ^^^^^^^^^^^^^^^^^^ -# -# $ locate x509.h -# /usr/local/ssl/include/openssl/x509.h -# ^^^^^^^^^^^^^^^^^^^^^^ -# -# $ locate ndbm.h -# /usr/include/db1/ndbm.h -# ^^^^^^^^^^^^^^^^ -# -# $ locate mod_ssl.h -# /usr/local/src/apache_1.3.22/src/modules/ssl/mod_ssl.h -# ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ -# Now uncomment this CFLAGS and comment out the one further down: - -CFLAGS = -fpic -O2 -Wall -I${APACHEINST}/include -I/usr/include/mysql -I/usr/local/ssl/include -I/usr/include/db1 -I${APACHESOURCE}/src/modules/ssl - -# (B) -# -# Modify "/usr/include/mysql" to where YOUR mysql.h can be found, -# -# How to find your directories: -# -# $ locate mysql.h -# /usr/include/mysql/mysql.h -# ^^^^^^^^^^^^^^^^^^ -# -# Comment out CFLAGS above and uncomment CFLAGS below: - -#CFLAGS = -fpic -O2 -Wall -I${APACHEINST}/include -I/usr/include/mysql - - -# --------------------------------------------------------- -# You shouldn't have to touch below here. - -CC = gcc -INSTALL = /usr/bin/install -m 664 - -all: mod_log_sql.o +# $Id: Makefile,v 1.12 2002/11/14 03:51:34 helios Exp $ + +##################################### +# Important: +# Adjust these values as outlined in the INSTALL file. +# Not all are needed at all times. + +APACHEINST = /usr/local/Apache +MYSQLLIBS = /usr/lib +MYSQLHDRS = /usr/include/mysql +#MODSSLHDRS = /usr/local/src/apache_1.3.27-dso/src/modules/ssl + +APACHESOURCE = /usr/local/src/apache_1.3.27-dso +OPNSSLHDRS = /usr/include/openssl +DB1HDRS = /usr/include/db1 + + +##################################### +# Shouldn't have to touch below here. + +MLMVERS = 1.17 +APXS = $(APACHEINST)/bin/apxs +#APXSGDB = -Wc,-g +APXSOPTS = -Wc,-O2 -Wc,-Wall -Wc,-DEAPI +CC = gcc +INSTALL = /usr/bin/install -m 664 +RM = /bin/rm + +ifdef MODSSLHDRS + SSLDEF = -DWANT_SSL_LOGGING + CFLAGS = -fPIC -O2 -Wall -I$(APACHEINST)/include -I$(MYSQLHDRS) -I$(MODSSLHDRS) -I$(OPNSSLHDRS) $(SSLDEF) -I$(DB1HDRS) +else + CFLAGS = -fPIC -O2 -Wall -I$(APACHEINST)/include -I$(MYSQLHDRS) +endif + +all: + @echo "You can choose to make mod_log_sql as a static or dynamic module." + @echo "Either 'make dso' or 'make static'." + @echo + @echo "Please read the INSTALL file carefully!" + +dso: mod_log_sql.so + +static: mod_log_sql.o + +mod_log_sql.so: mod_log_sql.c Makefile + $(APXS) $(APXSGDB) $(APXOPTS) -c -I$(MYSQLHDRS) -I$(MODSSLHDRS) $(SSLDEF) -L$(MYSQLLIBS) -lmysqlclient -lz mod_log_sql.c mod_log_sql.o: mod_log_sql.c Makefile $(CC) ${CFLAGS} -c mod_log_sql.c - -install: all - $(INSTALL) -d -m 755 ${APACHESOURCE}/src/modules/sql - $(INSTALL) mod_log_sql.c ${APACHESOURCE}/src/modules/sql/mod_log_sql.c - $(INSTALL) Makefile ${APACHESOURCE}/src/modules/sql/Makefile - $(INSTALL) mod_log_sql.o ${APACHESOURCE}/src/modules/sql/mod_log_sql.o -distro: all - cp -f INSTALL ${APACHEINST}/html/mod_log_sql/ - cp -f README ${APACHEINST}/html/mod_log_sql/ - cp -f CHANGELOG ${APACHEINST}/html/mod_log_sql/ - cd ..; tar zcf mod_log_sql-${MLMVERS}.tar.gz --exclude mod_log_sql/CVS mod_log_sql/; $(INSTALL) mod_log_sql-${MLMVERS}.tar.gz ${APACHEINST}/html/mod_log_sql/; rm -f mod_log_sql-${MLMVERS}.tar.gz - rm -f ${APACHEINST}/html/mod_log_sql/mod_log_sql.tar.gz - ln -s mod_log_sql-${MLMVERS}.tar.gz ${APACHEINST}/html/mod_log_sql/mod_log_sql.tar.gz +dsoinstall: dso + $(APXS) -i mod_log_sql.so + +statinstall: static + $(INSTALL) -d -m 755 $(APACHESOURCE)/src/modules/sql + $(INSTALL) mod_log_sql.c $(APACHESOURCE)/src/modules/sql/mod_log_sql.c + $(INSTALL) Makefile $(APACHESOURCE)/src/modules/sql/Makefile + $(INSTALL) mod_log_sql.o $(APACHESOURCE)/src/modules/sql/mod_log_sql.o clean: - rm -f *.o *~ + $(RM) -rf *.o *.so + +distro: all + cp -f INSTALL $(APACHEINST)/html/mod_log_sql/ + cp -f README $(APACHEINST)/html/mod_log_sql/ + cp -f CHANGELOG $(APACHEINST)/html/mod_log_sql/ + cd ..; tar zcf mod_log_sql-$(MLMVERS).tar.gz --exclude mod_log_sql/CVS mod_log_sql/; $(INSTALL) mod_log_sql-$(MLMVERS).tar.gz $(APACHEINST)/html/mod_log_sql/; rm -f mod_log_sql-$(MLMVERS).tar.gz + rm -f $(APACHEINST)/html/mod_log_sql/mod_log_sql.tar.gz + ln -s mod_log_sql-$(MLMVERS).tar.gz $(APACHEINST)/html/mod_log_sql/mod_log_sql.tar.gz + diff --git a/README b/README index 077b6c4..fd1cdc7 100644 --- a/README +++ b/README @@ -1,130 +1,5 @@ -$Id: README,v 1.6 2002/05/14 21:47:15 helios Exp $ - - -Homepage --------- -http://www.grubbybaby.com/mod_log_sql/ - - -Approach --------- -This project was formerly known as mod_log_mysql. It has been renamed -to mod_log_sql in order to reflect the project goal of -database-inspecificity. The module currently supports MySQL, and -development for other database backends is underway. - -In order to save speed and overhead, links are kept alive in between -queries. This module uses one SQL link per httpd process. Among other -things, this means that this module supports logging into only one -MySQL server, and for now, also, only one SQL database. But that's a -small tradeoff compared to the blinding speed of this module. - -Virtual hosts are supported in the same manner they are in the regular -logging modules. You define some basic 'global' directives in the -main server config, then you define more specific 'local' directives -inside each virtualhost stanza. - -SQL links are opened by each child process when it is born. Error reporting -is robust throughout and will let you know about database issues -in the standard Apache error-log for the server or virtual server. - -A robust "preserve" capability has now been implemented. This permits -the module to preserve any failed INSERT commands to a local file on -its machine. In any situation that the database is unavailable -- e.g. -the network fails, you reboot the db host, etc. -- mod_log_sql will -note this in the error log and begin appending its log entries to the -preserve file (which is created with the user & group ID of the running -Apache process, e.g. "nobody" on many Linux installations). At the time -that your MySQL server returns to service, each of these preserve files -is easily imported because it is simply a series of SQL insert statements: - - # mysql -uadminuser -p mydbname < /tmp/mysql-preserve - - - -Supported directives --------------------- - -Please see the web-based documentation for full explanation of all -supported run-time directives. - - http://www.grubbybaby.com/mod_log_sql/directives.html - -See the FAQ for some handy examples: - - http://www.grubbybaby.com/mod_log_sql/faq.html - - -What gets logged by default? ----------------------------- - -All the data that would be contained in the "Combined Log Format" -is logged by default, plus a little extra. Your best bet is to -begin by accepting this default, then later customize the log -configuration based on your needs. - -The online documentation of the run-time directives includes a full -explanation of what you can log, including examples. - - -Notes ------ - -* You will customarily set most of your run-time configuration directives - on a per-virtualserver basis, with only MySQLMassVirtualHosting, - MySQLLoginInfo, MySQLDatabase, MySQLSocketFile, MySQLCreateTables, - and MySQLMassVirtualHosting 'outside' in the main server config. Any - directives other than those in the main config do NOT get inherited - by the virutal servers. - -* The 'time_stamp' field is stored in an UNSIGNED INTEGER column, in the - standard unix "seconds since 1/1/1970 12:00:00" format. This is - superior to storing the access time as a string due to size - requirements: an UNSIGNED INT type fits in 4 bytes, whereas the Apache date - string (e.g. "18/Nov/2001:13:59:52 -0800") requires 26 bytes -- - significantly larger, and those extra 22 bytes will add up over the - thousands of accesses that a busy server will experience. Besides, - an INT type is far more flexible for comparisons, etc. - - In MySQL 3.21 and above you can easily convert this to a human - readable format using from_unixtime(), e.g.: - - select remote_host,request_uri,from_unixtime(time_stamp) from access_log; - - The enclosed perl program make_combined_log.pl shows how you can - extract your access records in a format that is completely Combined - Log Format compliant. You can then feed this to your favorite web - log analysis tool. - -* The table's string values can be CHAR or VARCHAR, at a length of your choice. - VARCHAR is superior because it truncates long strings; CHAR types are - fixed-length and will be padded with spaces. Just like the - time_stamp described above, that kind of space waste will add up over - thousands of records. - -* Be careful not to go overboard setting fields to NOT NULL. If a field is - marked NOT NULL then it must contain data in the INSERT or the INSERT - will fail. - -* Apache normally logs numeric fields with a '-' character to mean "not - applicable," e.g. bytes_sent on a request with a 304 response code. - Since '-' is an illegal character in an SQL numeric field, such - fields are assigned the value 0 instead of '-' which, of course, - makes perfect sense anyway. - - - -Author / Maintainer -------------------- - -The actual logging code was taken from the already existing flat file -text modules, so all that credit goes to the Apache Server group. - -The MySQL routines and directives were added by Zeev Suraski -. - -All changes from 1.06+ and the new documentation were added by -Chris Powell . It seems that the module had fallen -into the "unmaintained" category -- it hadn't been updated since 1998 -- -so Chris adopted it as the new maintainer. +$Id: README,v 1.7 2002/11/14 03:51:34 helios Exp $ +This document has been superseded by the new documentation +in the Documentation/ directory. There you will find +PS, plaintext, and HTML versions of the documentation. diff --git a/access_log.sql b/access_log.sql deleted file mode 100644 index 4966c62..0000000 --- a/access_log.sql +++ /dev/null @@ -1,25 +0,0 @@ -create table access_log ( - agent varchar(255) , - bytes_sent int , - child_pid smallint unsigned, - cookie varchar(255), - request_file varchar(255), - referer varchar(255) , - remote_host varchar(50) , - remote_logname varchar(50) , - remote_user varchar(50) , - request_duration smallint , - request_line varchar(255), - request_method varchar(6) , - request_protocol varchar(10) , - request_time char(28), - request_uri varchar(50) , - server_port smallint unsigned, - ssl_cipher varchar(25), - ssl_keysize smallint unsigned, - ssl_maxkeysize smallint unsigned, - status smallint , - time_stamp int unsigned , - virtual_host varchar(50) -) - diff --git a/create_tables.sql b/create_tables.sql new file mode 100644 index 0000000..430d1a7 --- /dev/null +++ b/create_tables.sql @@ -0,0 +1,50 @@ +create table access_log ( + id char(19) , + agent varchar(255) , + bytes_sent int unsigned , + child_pid smallint unsigned, + cookie varchar(255), + machine_id varchar(25), + request_file varchar(255), + referer varchar(255) , + remote_host varchar(50) , + remote_logname varchar(50) , + remote_user varchar(50) , + request_duration smallint unsigned , + request_line varchar(255), + request_method varchar(10) , + request_protocol varchar(10) , + request_time char(28), + request_uri varchar(50) , + server_port smallint unsigned, + ssl_cipher varchar(25), + ssl_keysize smallint unsigned, + ssl_maxkeysize smallint unsigned, + status smallint unsigned , + time_stamp int unsigned , + virtual_host varchar(50) +); + +create table notes ( + id char(19), + item varchar(80), + val varchar(80) +); + +create table headers_in ( + id char(19), + item varchar(80), + val varchar(80) +); + +create table headers_out ( + id char(19), + item varchar(80), + val varchar(80) +); + +create table cookies ( + id char(19), + item varchar(80), + val varchar(80) +); \ No newline at end of file diff --git a/make_combined_log.pl b/make_combined_log.pl index cefc728..1741dbf 100755 --- a/make_combined_log.pl +++ b/make_combined_log.pl @@ -1,6 +1,6 @@ #!/usr/bin/perl -# $Id: make_combined_log.pl,v 1.4 2002/01/02 20:47:44 helios Exp $ +# $Id: make_combined_log.pl,v 1.5 2002/11/14 03:51:34 helios Exp $ # # make_combined_log.pl # @@ -69,7 +69,7 @@ if (not $dbh) { die "Unable to connect to the database. Please check your connection variables. (Bad password? Incorrect perms?)"; } -$records = $dbh->prepare("select remote_host,remote_user,request_uri,time_stamp,status,bytes_sent,referer,agent,request_method,request_protocol from $serverTbl where virtual_host='$virthost' and time_stamp >= $start"); +$records = $dbh->prepare("select remote_host,remote_user,request_uri,time_stamp,status,bytes_sent,referer,agent,request_method,request_protocol from `$serverTbl` where virtual_host='$virthost' and time_stamp >= $start order by time_stamp"); $records->execute; if (not $records) { die "No such table or the select returned no records." diff --git a/mod_log_sql.c b/mod_log_sql.c index a0cceb2..ba60089 100644 --- a/mod_log_sql.c +++ b/mod_log_sql.c @@ -1,21 +1,19 @@ -/* $Id: mod_log_sql.c,v 1.16 2002/09/04 18:46:00 helios Exp $ */ +/* $Id: mod_log_sql.c,v 1.17 2002/11/14 03:51:35 helios Exp $ */ /* --------* * DEFINES * * --------*/ -/* The enduser probably won't modify these */ +/* The enduser may wish to modify this */ +#undef DEBUG + +/* The enduser won't modify these */ #define MYSQL_ERROR(mysql) ((mysql)?(mysql_error(mysql)):"MySQL server has gone away") #define ERRLEVEL APLOG_ERR|APLOG_NOERRNO #define WARNINGLEVEL APLOG_WARNING|APLOG_NOERRNO #define NOTICELEVEL APLOG_NOTICE|APLOG_NOERRNO #define DEBUGLEVEL APLOG_DEBUG|APLOG_NOERRNO -/* The enduser may wish to modify these */ -#define WANT_SSL_LOGGING -#undef DEBUG - - /* ---------* * INCLUDES * * ---------*/ @@ -41,7 +39,7 @@ * -------------*/ /* Declare ourselves so the configuration routines can find and know us. */ -module mysql_log_module; +module sql_log_module; /* The contents of these are known 'Apache wide' and are not variable * on a per-virtual-server basis. Every virtual server 'knows' the @@ -51,18 +49,21 @@ MYSQL sql_server, *mysql_log = NULL; int massvirtual = 0; int create_tables = 0; +int force_preserve = 0; char *db_name = NULL; char *db_host = NULL; char *db_user = NULL; char *db_pwd = NULL; +char *mach_id = NULL; char *socket_file = "/tmp/mysql.sock"; +unsigned int tcp_port = 3306; typedef const char *(*item_key_func) (request_rec *, char *); -/* But the contents of this structure will vary by virtual server. +/* But the contents of this structure will vary by virtual server. * This permits each virtual server to vary its configuration slightly - * for per-server customization. - * + * for per-server customization. + * * Each child process has its own segregated copy of this structure. */ typedef struct { @@ -73,9 +74,11 @@ typedef struct { array_header *notes_list; array_header *hout_list; array_header *hin_list; + array_header *cookie_list; char *notes_table_name; char *hout_table_name; char *hin_table_name; + char *cookie_table_name; char *transfer_table_name; char *transfer_log_format; char *preserve_file; @@ -133,7 +136,7 @@ static const char *extract_remote_user(request_rec *r, char *a) static const char *extract_ssl_keysize(request_rec *r, char *a) { char *result = NULL; - + if (ap_ctx_get(r->connection->client->ctx, "ssl") != NULL) { result = ssl_var_lookup(r->pool, r->server, r->connection, r, "SSL_CIPHER_USEKEYSIZE"); #ifdef DEBUG @@ -150,7 +153,7 @@ static const char *extract_ssl_keysize(request_rec *r, char *a) static const char *extract_ssl_maxkeysize(request_rec *r, char *a) { char *result = NULL; - + if (ap_ctx_get(r->connection->client->ctx, "ssl") != NULL) { result = ssl_var_lookup(r->pool, r->server, r->connection, r, "SSL_CIPHER_ALGKEYSIZE"); #ifdef DEBUG @@ -167,7 +170,7 @@ static const char *extract_ssl_maxkeysize(request_rec *r, char *a) static const char *extract_ssl_cipher(request_rec *r, char *a) { char *result = NULL; - + if (ap_ctx_get(r->connection->client->ctx, "ssl") != NULL) { result = ssl_var_lookup(r->pool, r->server, r->connection, r, "SSL_CIPHER"); #ifdef DEBUG @@ -225,6 +228,7 @@ static const char *extract_bytes_sent(request_rec *r, char *a) } } +/* static const char *extract_header_in(request_rec *r, char *a) { return table_get(r->headers_in, a); @@ -241,6 +245,7 @@ static const char *extract_header_out(request_rec *r, char *a) } return table_get(r->err_headers_out, a); } +*/ static const char *extract_request_time(request_rec *r, char *a) { @@ -278,6 +283,14 @@ static const char *extract_virtual_host(request_rec *r, char *a) return ap_get_server_name(r); } +static const char *extract_machine_id(request_rec *r, char *a) +{ + if (!mach_id) + return "-"; + else + return mach_id; +} + static const char *extract_server_port(request_rec *r, char *a) { char portnum[22]; @@ -309,7 +322,7 @@ static const char *extract_referer(request_rec *r, char *a) static const char *extract_agent(request_rec *r, char *a) { const char *tempag; - + tempag = table_get(r->headers_in, "User-Agent"); if (!tempag) { @@ -325,14 +338,14 @@ static const char *extract_cookie(request_rec *r, char *a) char *cookieend; char *isvalid; char *cookiebuf; - - log_sql_state *cls = get_module_config(r->server->module_config, &mysql_log_module); - + + log_sql_state *cls = get_module_config(r->server->module_config, &sql_log_module); + if (cls->cookie_name != NULL) { #ifdef DEBUG ap_log_error(APLOG_MARK,DEBUGLEVEL,r->server,"watching for cookie '%s'", cls->cookie_name); #endif - + /* Fetch out the cookie header */ cookiestr = (char *)table_get(r->headers_in, "cookie2"); if (cookiestr != NULL) { @@ -346,7 +359,7 @@ static const char *extract_cookie(request_rec *r, char *a) isvalid += strlen(cls->cookie_name) + 1; /* Duplicate it into the pool */ cookiebuf = ap_pstrdup(r->pool, isvalid); - /* Segregate just this cookie out of the string + /* Segregate just this cookie out of the string * with a terminating nul at the first semicolon */ cookieend = strchr(cookiebuf, ';'); if (cookieend != NULL) @@ -354,7 +367,7 @@ static const char *extract_cookie(request_rec *r, char *a) return cookiebuf; } } - + cookiestr = (char *)table_get(r->headers_in, "cookie"); if (cookiestr != NULL) { #ifdef DEBUG @@ -370,7 +383,7 @@ static const char *extract_cookie(request_rec *r, char *a) return cookiebuf; } } - + cookiestr = table_get(r->headers_out, "set-cookie"); if (cookiestr != NULL) { #ifdef DEBUG @@ -387,10 +400,81 @@ static const char *extract_cookie(request_rec *r, char *a) } } } - - return "-"; + + return "-"; } +static const char *extract_specific_cookie(request_rec *r, char *a) +{ + const char *cookiestr; + char *cookieend; + char *isvalid; + char *cookiebuf; + + if (a != NULL) { + #ifdef DEBUG + ap_log_error(APLOG_MARK,DEBUGLEVEL,r->server,"watching for cookie '%s'", a); + #endif + + /* Fetch out the cookie header */ + cookiestr = (char *)table_get(r->headers_in, "cookie2"); + if (cookiestr != NULL) { + #ifdef DEBUG + ap_log_error(APLOG_MARK,DEBUGLEVEL,r->server,"Cookie2: [%s]", cookiestr); + #endif + /* Does the cookie string contain one with our name? */ + isvalid = strstr(cookiestr, a); + if (isvalid != NULL) { + /* Move past the cookie name and equal sign */ + isvalid += strlen(a) + 1; + /* Duplicate it into the pool */ + cookiebuf = ap_pstrdup(r->pool, isvalid); + /* Segregate just this cookie out of the string + * with a terminating nul at the first semicolon */ + cookieend = strchr(cookiebuf, ';'); + if (cookieend != NULL) + *cookieend = '\0'; + return cookiebuf; + } + } + + cookiestr = (char *)table_get(r->headers_in, "cookie"); + if (cookiestr != NULL) { + #ifdef DEBUG + ap_log_error(APLOG_MARK,DEBUGLEVEL,r->server,"Cookie: [%s]", cookiestr); + #endif + isvalid = strstr(cookiestr, a); + if (isvalid != NULL) { + isvalid += strlen(a) + 1; + cookiebuf = ap_pstrdup(r->pool, isvalid); + cookieend = strchr(cookiebuf, ';'); + if (cookieend != NULL) + *cookieend = '\0'; + return cookiebuf; + } + } + + cookiestr = table_get(r->headers_out, "set-cookie"); + if (cookiestr != NULL) { + #ifdef DEBUG + ap_log_error(APLOG_MARK,DEBUGLEVEL,r->server,"Set-Cookie: [%s]", cookiestr); + #endif + isvalid = strstr(cookiestr, a); + if (isvalid != NULL) { + isvalid += strlen(a) + 1; + cookiebuf = ap_pstrdup(r->pool, isvalid); + cookieend = strchr(cookiebuf, ';'); + if (cookieend != NULL) + *cookieend = '\0'; + return cookiebuf; + } + } + } + + return "-"; +} + + static const char *extract_request_timestamp(request_rec *r, char *a) { char tstr[32]; @@ -399,13 +483,13 @@ static const char *extract_request_timestamp(request_rec *r, char *a) return pstrdup(r->pool, tstr); } +/* static const char *extract_note(request_rec *r, char *a) { return ap_table_get(r->notes, a); - - /*ap_table_do(extract_table, r, r->notes, NULL);*/ - + } +*/ static const char *extract_env_var(request_rec *r, char *a) { @@ -415,7 +499,7 @@ static const char *extract_env_var(request_rec *r, char *a) static const char *extract_unique_id(request_rec *r, char *a) { const char *tempid; - + tempid = ap_table_get(r->subprocess_env, "UNIQUE_ID"); if (!tempid) return "-"; @@ -437,32 +521,30 @@ struct log_sql_item_list { { 'A', extract_agent, "agent", 1, 1 }, { 'b', extract_bytes_sent, "bytes_sent", 0, 0 }, - { 'c', extract_cookie, "cookie", 0, 1 }, - { 'e', extract_env_var, "env_var", 0, 1 }, - { 'f', extract_request_file, "request_file", 0, 1 }, + { 'c', extract_cookie, "cookie", 0, 1 }, + { 'e', extract_env_var, "env_var", 0, 1 }, + { 'f', extract_request_file, "request_file", 0, 1 }, { 'H', extract_request_protocol, "request_protocol", 0, 1 }, { 'h', extract_remote_host, "remote_host", 0, 1 }, - { 'i', extract_header_in, "header_in", 0, 1 }, { 'I', extract_unique_id, "id", 0, 1 }, { 'l', extract_remote_logname, "remote_logname", 0, 1 }, { 'm', extract_request_method, "request_method", 0, 1 }, - { 'n', extract_note, "note", 0, 1 }, - { 'o', extract_header_out, "header_out", 0, 1 }, - { 'P', extract_child_pid, "child_pid", 0, 0 }, - { 'p', extract_server_port, "server_port", 0, 0 }, - { 'R', extract_referer, "referer", 1, 1 }, - { 'r', extract_request_line, "request_line", 1, 1 }, - { 'S', extract_request_timestamp, "time_stamp", 0, 0 }, - { 's', extract_status, "status", 1, 0 }, - { 'T', extract_request_duration, "request_duration", 1, 0 }, - { 't', extract_request_time, "request_time", 0, 1 }, - { 'u', extract_remote_user, "remote_user", 0, 1 }, - { 'U', extract_request_uri, "request_uri", 1, 1 }, - { 'v', extract_virtual_host, "virtual_host", 0, 1 }, + { 'M', extract_machine_id, "machine_id", 0, 1 }, + { 'P', extract_child_pid, "child_pid", 0, 0 }, + { 'p', extract_server_port, "server_port", 0, 0 }, + { 'R', extract_referer, "referer", 1, 1 }, + { 'r', extract_request_line, "request_line", 1, 1 }, + { 'S', extract_request_timestamp, "time_stamp", 0, 0 }, + { 's', extract_status, "status", 1, 0 }, + { 'T', extract_request_duration, "request_duration", 1, 0 }, + { 't', extract_request_time, "request_time", 0, 1 }, + { 'u', extract_remote_user, "remote_user", 0, 1 }, + { 'U', extract_request_uri, "request_uri", 1, 1 }, + { 'v', extract_virtual_host, "virtual_host", 0, 1 }, #ifdef WANT_SSL_LOGGING - { 'q', extract_ssl_keysize, "ssl_keysize", 0, 1 }, - { 'Q', extract_ssl_maxkeysize, "ssl_maxkeysize", 0, 1 }, - { 'z', extract_ssl_cipher, "ssl_cipher", 0, 1 }, + { 'q', extract_ssl_keysize, "ssl_keysize", 0, 1 }, + { 'Q', extract_ssl_maxkeysize, "ssl_maxkeysize", 0, 1 }, + { 'z', extract_ssl_cipher, "ssl_cipher", 0, 1 }, #endif {'\0'} }; @@ -479,7 +561,7 @@ const char *escape_query(const char *from_str, pool *p) char *to_str; unsigned long length = strlen(from_str); unsigned long retval; - + /* Pre-allocate a new string that could hold twice the original, which would only * happen if the whole original string was 'dangerous' characters. */ @@ -487,20 +569,20 @@ const char *escape_query(const char *from_str, pool *p) if (!to_str) { return from_str; } - + if (!mysql_log) { /* Well, I would have liked to use the current database charset. mysql is * unavailable, however, so I fall back to the slightly less respectful * mysql_escape_string() function that uses the default charset. */ retval = mysql_escape_string(to_str, from_str, length); - } else { + } else { /* MySQL is available, so I'll go ahead and respect the current charset when * I perform the escape. */ retval = mysql_real_escape_string(mysql_log, to_str, from_str, length); } - + if (retval) return to_str; else @@ -508,24 +590,37 @@ const char *escape_query(const char *from_str, pool *p) } } -int open_logdb_link() +int open_logdb_link(server_rec* s) { - /* Returns 2 if already connected, 1 if successful, 0 if unsuccessful */ - - if (mysql_log != NULL) { + /* Returns: + 3 if preserve forced + 2 if already connected + 1 if successful + 0 if unsuccessful + */ + + if (force_preserve) + return 3; + + if (mysql_log != NULL) return 2; - } if (db_name) { mysql_init(&sql_server); - mysql_log = mysql_real_connect(&sql_server, db_host, db_user, db_pwd, db_name, 0, socket_file, 0); + mysql_log = mysql_real_connect(&sql_server, db_host, db_user, db_pwd, db_name, tcp_port, socket_file, 0); - if (mysql_log != NULL) { + if (mysql_log) { return 1; } else { + #ifdef DEBUG + ap_log_error(APLOG_MARK,DEBUGLEVEL,s,"mod_log_sql: database connection error: %s",MYSQL_ERROR(&sql_server)); + ap_log_error(APLOG_MARK,DEBUGLEVEL,s,"HOST: '%s' PORT: '%d' DB: '%s' USER: '%s' SOCKET: '%s'", + db_host, tcp_port, db_name, db_user, socket_file); + #endif return 0; } } + return 0; } @@ -536,13 +631,13 @@ static int trace(void *data, const char *key, const char *val) request_rec *r = (request_rec *)data; fp = pfopen(r->pool, "/tmp/trace", "a"); - + if (fp) { fprintf(fp, "Field '%s' == '%s'\n", key, val); } - + pfclose(r->pool, fp); - + return TRUE; } #endif @@ -557,8 +652,8 @@ const char *extract_table(void *data, const char *key, const char *val) void preserve_entry(request_rec *r, const char *query) { FILE *fp; - log_sql_state *cls = get_module_config(r->server->module_config, &mysql_log_module); - + log_sql_state *cls = get_module_config(r->server->module_config, &sql_log_module); + fp = pfopen(r->pool, cls->preserve_file, "a"); if (fp == NULL) ap_log_error(APLOG_MARK,ERRLEVEL,r->server,"mod_log_sql: attempted append of local preserve file but failed."); @@ -567,7 +662,7 @@ void preserve_entry(request_rec *r, const char *query) pfclose(r->pool, fp); #ifdef DEBUG ap_log_error(APLOG_MARK,DEBUGLEVEL,r->server,"mod_log_sql: entry preserved in %s", cls->preserve_file); - #endif + #endif } } @@ -588,32 +683,32 @@ unsigned int safe_mysql_query(request_rec *r, const char *query) struct timespec delay, remainder; int ret; void (*handler) (int); - + /* A failed mysql_query() may send a SIGPIPE, so we ignore that signal momentarily. */ - handler = signal(SIGPIPE, SIG_IGN); + handler = signal(SIGPIPE, SIG_IGN); /* First attempt for the query */ - if (mysql_log != NULL) + if (mysql_log != NULL) retval = mysql_query(mysql_log, query); else return 1; - + if ( retval != 0 ) { /* If we ran the query and it returned an error, try to be robust. * (After all, the module thought it had a valid mysql_log connection but the query * could have failed for a number of reasons, so we have to be extra-safe and check.) */ - - log_sql_state *cls = get_module_config(r->server->module_config, &mysql_log_module); + + log_sql_state *cls = get_module_config(r->server->module_config, &sql_log_module); real_error = mysql_errno(mysql_log); /* What really happened? */ - + /* Something went wrong, so start by trying to restart the db link. */ ap_log_error(APLOG_MARK,ERRLEVEL,r->server,"mod_log_sql: first attempt failed, API said: error %d, %s", real_error, MYSQL_ERROR(mysql_log)); mysql_close(mysql_log); mysql_log = NULL; - open_logdb_link(); + open_logdb_link(r->server); if (mysql_log == NULL) { /* still unable to link */ signal(SIGPIPE, handler); @@ -639,13 +734,13 @@ unsigned int safe_mysql_query(request_rec *r, const char *query) real_error = mysql_errno(mysql_log); ap_log_error(APLOG_MARK,ERRLEVEL,r->server,"mod_log_sql: second attempt failed, API said: error %d, %s", real_error, MYSQL_ERROR(mysql_log)); retval = real_error; - } else + } else ap_log_error(APLOG_MARK,ERRLEVEL,r->server,"mod_log_sql: second attempt successful"); } /* Restore SIGPIPE to its original handler function */ signal(SIGPIPE, handler); - + return retval; } @@ -661,18 +756,20 @@ int safe_create_tables(log_sql_state *cls, request_rec *r) { int retval; unsigned int create_results; - char *create_access = NULL; + char *create_access = NULL; char *create_notes = NULL; char *create_hout = NULL; char *create_hin = NULL; - - char *createprefix = "create table if not exists "; + char *create_cookies = NULL; + + char *createprefix = "create table if not exists `"; char *access_suffix = - " (id char(19),\ - agent varchar(255),\ - bytes_sent int unsigned,\ + "` (id char(19),\ + agent varchar(255),\ + bytes_sent int unsigned,\ child_pid smallint unsigned,\ cookie varchar(255),\ + machine_id varchar(25),\ request_file varchar(255),\ referer varchar(255),\ remote_host varchar(50),\ @@ -680,7 +777,7 @@ int safe_create_tables(log_sql_state *cls, request_rec *r) remote_user varchar(50),\ request_duration smallint unsigned,\ request_line varchar(255),\ - request_method varchar(6),\ + request_method varchar(10),\ request_protocol varchar(10),\ request_time char(28),\ request_uri varchar(50),\ @@ -691,58 +788,71 @@ int safe_create_tables(log_sql_state *cls, request_rec *r) status smallint unsigned,\ time_stamp int unsigned,\ virtual_host varchar(50))"; - - char *notes_suffix = - " (id char(19),\ + + char *notes_suffix = + "` (id char(19),\ item varchar(80),\ val varchar(80))"; - - char *headers_suffix = - " (id char(19),\ + + char *headers_suffix = + "` (id char(19),\ item varchar(80),\ val varchar(80))"; - + + char *cookies_suffix = + "` (id char(19),\ + item varchar(80),\ + val varchar(80))"; + /* Find memory long enough to hold the whole CREATE string + \0 */ - create_access = ap_pstrcat(r->pool, createprefix, cls->transfer_table_name, access_suffix, NULL); - create_notes = ap_pstrcat(r->pool, createprefix, cls->notes_table_name, notes_suffix, NULL); - create_hout = ap_pstrcat(r->pool, createprefix, cls->hout_table_name, headers_suffix, NULL); - create_hin = ap_pstrcat(r->pool, createprefix, cls->hin_table_name, headers_suffix, NULL); - + create_access = ap_pstrcat(r->pool, createprefix, cls->transfer_table_name, access_suffix, NULL); + create_notes = ap_pstrcat(r->pool, createprefix, cls->notes_table_name, notes_suffix, NULL); + create_hout = ap_pstrcat(r->pool, createprefix, cls->hout_table_name, headers_suffix, NULL); + create_hin = ap_pstrcat(r->pool, createprefix, cls->hin_table_name, headers_suffix, NULL); + create_cookies= ap_pstrcat(r->pool, createprefix, cls->cookie_table_name, cookies_suffix, NULL); + #ifdef DEBUG ap_log_error(APLOG_MARK,DEBUGLEVEL,r->server,"mod_log_sql: create string: %s", create_access); ap_log_error(APLOG_MARK,DEBUGLEVEL,r->server,"mod_log_sql: create string: %s", create_notes); ap_log_error(APLOG_MARK,DEBUGLEVEL,r->server,"mod_log_sql: create string: %s", create_hout); ap_log_error(APLOG_MARK,DEBUGLEVEL,r->server,"mod_log_sql: create string: %s", create_hin); + ap_log_error(APLOG_MARK,DEBUGLEVEL,r->server,"mod_log_sql: create string: %s", create_cookies); #endif /* Assume that things worked unless told otherwise */ cls->table_made = 1; retval = 0; - + if ((create_results = safe_mysql_query(r, create_access)) != 0) { cls->table_made = 0; ap_log_error(APLOG_MARK,ERRLEVEL,r->server,"mod_log_sql: failed to create access table"); retval = create_results; } - + if ((create_results = safe_mysql_query(r, create_notes)) != 0) { cls->table_made = 0; ap_log_error(APLOG_MARK,ERRLEVEL,r->server,"mod_log_sql: failed to create notes table"); - retval = create_results; + retval = create_results; } - + if ((create_results = safe_mysql_query(r, create_hin)) != 0) { cls->table_made = 0; ap_log_error(APLOG_MARK,ERRLEVEL,r->server,"mod_log_sql: failed to create header_out table"); retval = create_results; } - + if ((create_results = safe_mysql_query(r, create_hout)) != 0) { cls->table_made = 0; ap_log_error(APLOG_MARK,ERRLEVEL,r->server,"mod_log_sql: failed to create header_in table"); retval = create_results; } - + + if ((create_results = safe_mysql_query(r, create_cookies)) != 0) { + cls->table_made = 0; + ap_log_error(APLOG_MARK,ERRLEVEL,r->server,"mod_log_sql: failed to create cookies table"); + retval = create_results; + } + return retval; } @@ -757,6 +867,18 @@ const char *set_log_sql_massvirtual(cmd_parms *parms, void *dummy, int flag) return NULL; } +const char *set_log_sql_force_preserve(cmd_parms *parms, void *dummy, int flag) +{ + force_preserve = ( flag ? 1 : 0); + return NULL; +} + +const char *set_log_sql_machine_id(cmd_parms *parms, void *dummy, char *arg) +{ + mach_id = arg; + return NULL; +} + const char *set_log_sql_create(cmd_parms *parms, void *dummy, int flag) { if (massvirtual != 0) @@ -774,7 +896,7 @@ const char *set_log_sql_db(cmd_parms *parms, void *dummy, char *arg) const char *set_log_sql_cookie(cmd_parms *parms, void *dummy, char *arg) { - log_sql_state *cls = get_module_config(parms->server->module_config, &mysql_log_module); + log_sql_state *cls = get_module_config(parms->server->module_config, &sql_log_module); cls->cookie_name = arg; return NULL; @@ -783,7 +905,7 @@ const char *set_log_sql_cookie(cmd_parms *parms, void *dummy, char *arg) const char *set_log_sql_preserve_file(cmd_parms *parms, void *dummy, char *arg) { /* char *pfile; */ - log_sql_state *cls = get_module_config(parms->server->module_config, &mysql_log_module); + log_sql_state *cls = get_module_config(parms->server->module_config, &sql_log_module); /* pfile = ap_pstrcat(parms->pool, "/tmp/", arg, NULL); */ cls->preserve_file = arg; @@ -806,7 +928,7 @@ const char *set_log_sql_info(cmd_parms *parms, void *dummy, char *host, char *us const char *set_log_sql_transfer_table(cmd_parms *parms, void *dummy, char *arg) { - log_sql_state *cls = get_module_config(parms->server->module_config, &mysql_log_module); + log_sql_state *cls = get_module_config(parms->server->module_config, &sql_log_module); if (massvirtual != 0) ap_log_error(APLOG_MARK,WARNINGLEVEL,parms->server,"mod_log_sql: do not set LogSQLTransferLogTable when LogSQLMassVirtualHosting is On. Ignoring."); @@ -815,9 +937,20 @@ const char *set_log_sql_transfer_table(cmd_parms *parms, void *dummy, char *arg) return NULL; } +const char *set_log_sql_cookie_table(cmd_parms *parms, void *dummy, char *arg) +{ + log_sql_state *cls = get_module_config(parms->server->module_config, &sql_log_module); + + if (massvirtual != 0) + ap_log_error(APLOG_MARK,WARNINGLEVEL,parms->server,"mod_log_sql: do not set LogSQLCookieLogTable when LogSQLMassVirtualHosting is On. Ignoring."); + else + cls->cookie_table_name = arg; + return NULL; +} + const char *set_log_sql_notes_table(cmd_parms *parms, void *dummy, char *arg) { - log_sql_state *cls = get_module_config(parms->server->module_config, &mysql_log_module); + log_sql_state *cls = get_module_config(parms->server->module_config, &sql_log_module); if (massvirtual != 0) ap_log_error(APLOG_MARK,WARNINGLEVEL,parms->server,"mod_log_sql: do not set LogSQLNotesLogTable when LogSQLMassVirtualHosting is On. Ignoring."); @@ -828,7 +961,7 @@ const char *set_log_sql_notes_table(cmd_parms *parms, void *dummy, char *arg) const char *set_log_sql_hin_table(cmd_parms *parms, void *dummy, char *arg) { - log_sql_state *cls = get_module_config(parms->server->module_config, &mysql_log_module); + log_sql_state *cls = get_module_config(parms->server->module_config, &sql_log_module); if (massvirtual != 0) ap_log_error(APLOG_MARK,WARNINGLEVEL,parms->server,"mod_log_sql: do not set LogSQLHeadersInLogTable when LogSQLMassVirtualHosting is On. Ignoring."); @@ -839,7 +972,7 @@ const char *set_log_sql_hin_table(cmd_parms *parms, void *dummy, char *arg) const char *set_log_sql_hout_table(cmd_parms *parms, void *dummy, char *arg) { - log_sql_state *cls = get_module_config(parms->server->module_config, &mysql_log_module); + log_sql_state *cls = get_module_config(parms->server->module_config, &sql_log_module); if (massvirtual != 0) ap_log_error(APLOG_MARK,WARNINGLEVEL,parms->server,"mod_log_sql: do not set LogSQLHeadersOutLogTable when LogSQLMassVirtualHosting is On. Ignoring."); @@ -850,7 +983,7 @@ const char *set_log_sql_hout_table(cmd_parms *parms, void *dummy, char *arg) const char *set_log_sql_transfer_log_format(cmd_parms *parms, void *dummy, char *arg) { - log_sql_state *cls = get_module_config(parms->server->module_config, &mysql_log_module); + log_sql_state *cls = get_module_config(parms->server->module_config, &sql_log_module); cls->transfer_log_format = arg; return NULL; @@ -863,10 +996,17 @@ const char *set_log_sql_socket_file(cmd_parms *parms, void *dummy, char *arg) return NULL; } +const char *set_log_sql_tcp_port(cmd_parms *parms, void *dummy, char *arg) +{ + tcp_port = (unsigned int)atoi(arg); + + return NULL; +} + const char *add_log_sql_referer_ignore(cmd_parms *parms, void *dummy, char *arg) { char **addme; - log_sql_state *cls = get_module_config(parms->server->module_config, &mysql_log_module); + log_sql_state *cls = get_module_config(parms->server->module_config, &sql_log_module); addme = push_array(cls->referer_ignore_list); *addme = pstrdup(cls->referer_ignore_list->pool, arg); @@ -876,7 +1016,7 @@ const char *add_log_sql_referer_ignore(cmd_parms *parms, void *dummy, char *arg) const char *add_log_sql_transfer_ignore(cmd_parms *parms, void *dummy, char *arg) { char **addme; - log_sql_state *cls = get_module_config(parms->server->module_config, &mysql_log_module); + log_sql_state *cls = get_module_config(parms->server->module_config, &sql_log_module); addme = push_array(cls->transfer_ignore_list); *addme = pstrdup(cls->transfer_ignore_list->pool, arg); @@ -886,7 +1026,7 @@ const char *add_log_sql_transfer_ignore(cmd_parms *parms, void *dummy, char *arg const char *add_log_sql_remhost_ignore(cmd_parms *parms, void *dummy, char *arg) { char **addme; - log_sql_state *cls = get_module_config(parms->server->module_config, &mysql_log_module); + log_sql_state *cls = get_module_config(parms->server->module_config, &sql_log_module); addme = push_array(cls->remhost_ignore_list); *addme = pstrdup(cls->remhost_ignore_list->pool, arg); @@ -896,7 +1036,7 @@ const char *add_log_sql_remhost_ignore(cmd_parms *parms, void *dummy, char *arg) const char *add_log_sql_note(cmd_parms *parms, void *dummy, char *arg) { char **addme; - log_sql_state *cls = get_module_config(parms->server->module_config, &mysql_log_module); + log_sql_state *cls = get_module_config(parms->server->module_config, &sql_log_module); addme = push_array(cls->notes_list); *addme = pstrdup(cls->notes_list->pool, arg); @@ -906,7 +1046,7 @@ const char *add_log_sql_note(cmd_parms *parms, void *dummy, char *arg) const char *add_log_sql_hout(cmd_parms *parms, void *dummy, char *arg) { char **addme; - log_sql_state *cls = get_module_config(parms->server->module_config, &mysql_log_module); + log_sql_state *cls = get_module_config(parms->server->module_config, &sql_log_module); addme = push_array(cls->hout_list); *addme = pstrdup(cls->hout_list->pool, arg); @@ -916,13 +1056,22 @@ const char *add_log_sql_hout(cmd_parms *parms, void *dummy, char *arg) const char *add_log_sql_hin(cmd_parms *parms, void *dummy, char *arg) { char **addme; - log_sql_state *cls = get_module_config(parms->server->module_config, &mysql_log_module); + log_sql_state *cls = get_module_config(parms->server->module_config, &sql_log_module); addme = push_array(cls->hin_list); *addme = pstrdup(cls->hin_list->pool, arg); return NULL; } +const char *add_log_sql_cookie(cmd_parms *parms, void *dummy, char *arg) +{ + char **addme; + log_sql_state *cls = get_module_config(parms->server->module_config, &sql_log_module); + + addme = push_array(cls->cookie_list); + *addme = pstrdup(cls->cookie_list->pool, arg); + return NULL; +} @@ -932,7 +1081,7 @@ const char *add_log_sql_hin(cmd_parms *parms, void *dummy, char *arg) *------------------------------------------------------------*/ -/* +/* * This function is called during server initialisation when an heavy-weight * process (such as a child) is being initialised. As with the * module-initialisation function, any information that needs to be recorded @@ -943,19 +1092,20 @@ const char *add_log_sql_hin(cmd_parms *parms, void *dummy, char *arg) static void log_sql_child_init(server_rec *s, pool *p) { int retval; - - retval = open_logdb_link(); + + retval = open_logdb_link(s); if (retval == 0) - ap_log_error(APLOG_MARK,ERRLEVEL,s,"mod_log_sql: child spawned but unable to open database link"); - + ap_log_error(APLOG_MARK,ERRLEVEL,s,"mod_log_sql: child spawned but unable to open database link"); + #ifdef DEBUG - if (retval > 0) { - ap_log_error(APLOG_MARK,DEBUGLEVEL,s,"mod_log_sql: open_logdb_link successful"); - } + if ( (retval == 1) || (retval == 2) ) + ap_log_error(APLOG_MARK,DEBUGLEVEL,s,"mod_log_sql: open_logdb_link successful"); + if (retval == 3) + ap_log_error(APLOG_MARK,DEBUGLEVEL,s,"mod_log_sql: open_logdb_link said that preservation is forced"); #endif } -/* +/* * This function is called when an heavy-weight process (such as a child) is * being run down or destroyed. As with the child-initialisation function, * any information that needs to be recorded must be in static cells, since @@ -973,15 +1123,15 @@ static void log_sql_child_exit(server_rec *s, pool *p) void *log_sql_initializer(server_rec *main_server, pool *p) { server_rec *s; - - log_sql_state main_conf = ap_get_module_config(main_server->module_config, &mysql_log_module); + + log_sql_state main_conf = ap_get_module_config(main_server->module_config, &sql_log_module); for (server_rec *s = main_server; s; s = s->next) { - conf = ap_get_module_config(s->module_config, &mysql_log_module); + conf = ap_get_module_config(s->module_config, &sql_log_module); if (conf->transfer_log_format == NULL && s != main_server) { *conf = *main_conf; } - + } */ @@ -996,26 +1146,28 @@ void *log_sql_initializer(server_rec *main_server, pool *p) */ void *log_sql_make_state(pool *p, server_rec *s) { - + log_sql_state *cls = (log_sql_state *) ap_palloc(p, sizeof(log_sql_state)); - cls->transfer_table_name = NULL; - cls->transfer_log_format = NULL; + /* These defaults are overridable in the httpd.conf file. */ + cls->transfer_table_name = NULL; /* No default b/c we want its absence to disable logging */ + cls->transfer_log_format = "AbHhmRSsTUuv"; cls->notes_table_name = "notes"; cls->hin_table_name = "headers_in"; cls->hout_table_name = "headers_out"; - + cls->cookie_table_name = "cookies"; + cls->preserve_file = "/tmp/sql-preserve"; + cls->referer_ignore_list = make_array(p, 1, sizeof(char *)); cls->transfer_ignore_list = make_array(p, 1, sizeof(char *)); cls->remhost_ignore_list = make_array(p, 1, sizeof(char *)); cls->notes_list = make_array(p, 1, sizeof(char *)); cls->hin_list = make_array(p, 1, sizeof(char *)); cls->hout_list = make_array(p, 1, sizeof(char *)); + cls->cookie_list = make_array(p, 1, sizeof(char *)); cls->table_made = 0; - - cls->preserve_file = "/tmp/sql-preserve"; cls->cookie_name = NULL; - + return (void *) cls; } @@ -1036,9 +1188,15 @@ command_rec log_sql_cmds[] = { {"LogSQLHeadersInLogTable", set_log_sql_hin_table, NULL, RSRC_CONF, TAKE1, "The database table that holds the inbound headers"} , + {"LogSQLCookieLogTable", set_log_sql_cookie_table, NULL, RSRC_CONF, TAKE1, + "The database table that holds the cookie info"} + , {"LogSQLTransferLogFormat", set_log_sql_transfer_log_format, NULL, RSRC_CONF, TAKE1, "Instruct the module what information to log to the database transfer log"} , + {"LogSQLMachineID", set_log_sql_machine_id, NULL, RSRC_CONF, TAKE1, + "Machine ID that the module will log, useful in web clusters to differentiate machines"} + , {"LogSQLRefererIgnore", add_log_sql_referer_ignore, NULL, RSRC_CONF, ITERATE, "List of referers to ignore. Accesses that match will not be logged to database"} , @@ -1052,7 +1210,7 @@ command_rec log_sql_cmds[] = { "The name of the database database for logging"} , {"LogSQLWhichCookie", set_log_sql_cookie, NULL, RSRC_CONF, TAKE1, - "The CookieName that you want logged when using the 'c' config directive"} + "The single cookie that you want logged in the access_log when using the 'c' config directive"} , {"LogSQLLoginInfo", set_log_sql_info, NULL, RSRC_CONF, TAKE3, "The database host, user-id and password for logging"} @@ -1063,33 +1221,42 @@ command_rec log_sql_cmds[] = { {"LogSQLMassVirtualHosting", set_log_sql_massvirtual, NULL, RSRC_CONF, FLAG, "Activates option(s) useful for ISPs performing mass virutal hosting"} , + {"LogSQLForcePreserve", set_log_sql_force_preserve, NULL, RSRC_CONF, FLAG, + "Forces logging to preserve file and bypasses database"} + , {"LogSQLPreserveFile", set_log_sql_preserve_file, NULL, RSRC_CONF, TAKE1, "Name of the file to use for data preservation during database downtime"} , {"LogSQLSocketFile", set_log_sql_socket_file, NULL, RSRC_CONF, TAKE1, "Name of the file to employ for socket connections to database"} , + {"LogSQLTCPPort", set_log_sql_tcp_port, NULL, RSRC_CONF, TAKE1, + "Port number to use for TCP connections to database, defaults to 3306 if not set"} + , {"LogSQLWhichNotes", add_log_sql_note, NULL, RSRC_CONF, ITERATE, - "Members of the 'notes' that you would like to log"} + "Notes that you would like to log in a separate table"} , {"LogSQLWhichHeadersOut", add_log_sql_hout, NULL, RSRC_CONF, ITERATE, - "Members of the 'headers out' that you would like to log"} + "Outbound headers that you would like to log in a separate table"} , {"LogSQLWhichHeadersIn", add_log_sql_hin, NULL, RSRC_CONF, ITERATE, - "Members of the 'headers in' that you would like to log"} + "Inbound headers that you would like to log in a separate table"} + , + {"LogSQLWhichCookies", add_log_sql_cookie, NULL, RSRC_CONF, ITERATE, + "The cookie(s) that you would like to log in a separate table"} , {NULL} }; - + /* Routine to perform the actual construction and execution of the relevant * INSERT statements. */ int log_sql_transaction(request_rec *orig) { char **ptrptr, **ptrptr2; - log_sql_state *cls = get_module_config(orig->server->module_config, &mysql_log_module); + log_sql_state *cls = get_module_config(orig->server->module_config, &sql_log_module); const char *access_query; request_rec *r; @@ -1101,36 +1268,43 @@ int log_sql_transaction(request_rec *orig) char *notes_base = "notes_"; char *hout_base = "headout_"; char *hin_base = "headin_"; + char *cookie_base = "cookies_"; char *a_tablename; char *n_tablename; char *i_tablename; char *o_tablename; + char *c_tablename; int i; - + /* Find memory long enough to hold the table name + \0. */ a_tablename = ap_pstrcat(orig->pool, access_base, ap_get_server_name(orig), NULL); n_tablename = ap_pstrcat(orig->pool, notes_base, ap_get_server_name(orig), NULL); i_tablename = ap_pstrcat(orig->pool, hin_base, ap_get_server_name(orig), NULL); o_tablename = ap_pstrcat(orig->pool, hout_base, ap_get_server_name(orig), NULL); - - /* Transform any dots or dashes to underscores */ + c_tablename = ap_pstrcat(orig->pool, cookie_base, ap_get_server_name(orig), NULL); + + /* Transform any dots to underscores */ for (i = 0; i < strlen(a_tablename); i++) { - if ( (a_tablename[i] == '.') || (a_tablename[i] == '-') ) + if (a_tablename[i] == '.') a_tablename[i] = '_'; } for (i = 0; i < strlen(n_tablename); i++) { - if ( (n_tablename[i] == '.') || (n_tablename[i] == '-') ) + if (n_tablename[i] == '.') n_tablename[i] = '_'; } for (i = 0; i < strlen(i_tablename); i++) { - if ( (i_tablename[i] == '.') || (i_tablename[i] == '-') ) + if (i_tablename[i] == '.') i_tablename[i] = '_'; } for (i = 0; i < strlen(o_tablename); i++) { - if ( (o_tablename[i] == '.') || (o_tablename[i] == '-') ) + if (o_tablename[i] == '.') o_tablename[i] = '_'; } - + for (i = 0; i < strlen(c_tablename); i++) { + if (c_tablename[i] == '.') + c_tablename[i] = '_'; + } + /* Tell this virtual server its transfer table name, and * turn on create_tables, which is implied by massvirtual. */ @@ -1138,9 +1312,10 @@ int log_sql_transaction(request_rec *orig) cls->notes_table_name = n_tablename; cls->hout_table_name = o_tablename; cls->hin_table_name = i_tablename; + cls->cookie_table_name = c_tablename; create_tables = 1; } - + /* Do we have enough info to log? */ if ( cls->transfer_table_name == NULL ) { return DECLINED; @@ -1152,11 +1327,12 @@ int log_sql_transaction(request_rec *orig) char *note_query = NULL; char *hin_query = NULL; char *hout_query = NULL; + char *cookie_query = NULL; const char *unique_id; const char *formatted_item; int i, j, length; int result; - + for (r = orig; r->next; r = r->next) { continue; } @@ -1188,10 +1364,6 @@ int log_sql_transaction(request_rec *orig) } } - /* If not specified by the user, use the default format */ - if (cls->transfer_log_format == NULL) { - cls->transfer_log_format = "AbHhmRSsTUuv"; - } length = strlen(cls->transfer_log_format); /* Iterate through the format characters and set up the INSERT string according to @@ -1205,20 +1377,20 @@ int log_sql_transaction(request_rec *orig) /* Yes, this key is one of the configured keys. * Call the key's function and put the returned value into 'formatted_item' */ formatted_item = log_sql_item_keys[j].func(log_sql_item_keys[j].want_orig_default ? orig : r, ""); - + /* Massage 'formatted_item' for proper SQL eligibility... */ if (!formatted_item) { formatted_item = ""; } else if (formatted_item[0] == '-' && formatted_item[1] == '\0' && !log_sql_item_keys[j].string_contents) { - /* If apache tried to log a '-' character for a numeric field, convert that to a zero + /* If apache tried to log a '-' character for a numeric field, convert that to a zero * because the database expects a numeral and will reject the '-' character. */ formatted_item = "0"; } - + /* Append the fieldname and value-to-insert to the appropriate strings, quoting stringvals with ' as appropriate */ fields = pstrcat(r->pool, fields, (i > 0 ? "," : ""), log_sql_item_keys[j].sql_field_name, NULL); - + values = pstrcat(r->pool, values, (i > 0 ? "," : ""), (log_sql_item_keys[j].string_contents ? "'" : ""), escape_query(formatted_item, r->pool), @@ -1230,129 +1402,181 @@ int log_sql_transaction(request_rec *orig) } } - /* Work through the list of notes defined by LogSQLNotesToLog */ + /* Work through the list of notes defined by LogSQLWhichNotes */ i = 0; unique_id = extract_unique_id(r, ""); - + ptrptr2 = (char **) (cls->notes_list->elts + (cls->notes_list->nelts * cls->notes_list->elt_size)); for (ptrptr = (char **) cls->notes_list->elts; ptrptr < ptrptr2; ptrptr = (char **) ((char *) ptrptr + cls->notes_list->elt_size)) { /* If the specified note (*ptrptr) exists for the current request... */ if ((theitem = ap_table_get(r->notes, *ptrptr))) { - itemsets = ap_pstrcat(r->pool, itemsets, + itemsets = ap_pstrcat(r->pool, itemsets, (i > 0 ? "," : ""), "('", unique_id, "','", - escape_query(*ptrptr, r->pool), - "','", + escape_query(*ptrptr, r->pool), + "','", escape_query(theitem, r->pool), - "')", + "')", NULL); - i++; + i++; } } if ( itemsets != "" ) { - note_query = ap_pstrcat(r->pool, "insert into ", cls->notes_table_name, " (id, item, val) values ", itemsets, NULL); + note_query = ap_pstrcat(r->pool, "insert into `", cls->notes_table_name, "` (id, item, val) values ", itemsets, NULL); #ifdef DEBUG ap_log_error(APLOG_MARK,DEBUGLEVEL,orig->server,"mod_log_sql: note string: %s", note_query); #endif } - - /* Work through the list of headers-out defined by LogSQLHeadersOutToLog */ + + /* Work through the list of headers-out defined by LogSQLWhichHeadersOut*/ i = 0; itemsets = ""; - + ptrptr2 = (char **) (cls->hout_list->elts + (cls->hout_list->nelts * cls->hout_list->elt_size)); for (ptrptr = (char **) cls->hout_list->elts; ptrptr < ptrptr2; ptrptr = (char **) ((char *) ptrptr + cls->hout_list->elt_size)) { - /* If the specified note (*ptrptr) exists for the current request... */ + /* If the specified header (*ptrptr) exists for the current request... */ if ((theitem = ap_table_get(r->headers_out, *ptrptr))) { - itemsets = ap_pstrcat(r->pool, itemsets, + itemsets = ap_pstrcat(r->pool, itemsets, (i > 0 ? "," : ""), "('", unique_id, "','", - escape_query(*ptrptr, r->pool), - "','", + escape_query(*ptrptr, r->pool), + "','", escape_query(theitem, r->pool), - "')", + "')", NULL); - i++; + i++; } } if ( itemsets != "" ) { - hout_query = ap_pstrcat(r->pool, "insert into ", cls->hout_table_name, " (id, item, val) values ", itemsets, NULL); + hout_query = ap_pstrcat(r->pool, "insert into `", cls->hout_table_name, "` (id, item, val) values ", itemsets, NULL); #ifdef DEBUG ap_log_error(APLOG_MARK,DEBUGLEVEL,orig->server,"mod_log_sql: header_out string: %s", hout_query); #endif } - - /* Work through the list of headers-in defined by LogSQLHeadersInToLog */ + + /* Work through the list of headers-in defined by LogSQLWhichHeadersIn */ i = 0; itemsets = ""; - + ptrptr2 = (char **) (cls->hin_list->elts + (cls->hin_list->nelts * cls->hin_list->elt_size)); for (ptrptr = (char **) cls->hin_list->elts; ptrptr < ptrptr2; ptrptr = (char **) ((char *) ptrptr + cls->hin_list->elt_size)) { - /* If the specified note (*ptrptr) exists for the current request... */ + /* If the specified header (*ptrptr) exists for the current request... */ if ((theitem = ap_table_get(r->headers_in, *ptrptr))) { - itemsets = ap_pstrcat(r->pool, itemsets, + itemsets = ap_pstrcat(r->pool, itemsets, (i > 0 ? "," : ""), "('", unique_id, "','", - escape_query(*ptrptr, r->pool), - "','", + escape_query(*ptrptr, r->pool), + "','", escape_query(theitem, r->pool), - "')", + "')", NULL); - i++; + i++; } } if ( itemsets != "" ) { - hin_query = ap_pstrcat(r->pool, "insert into ", cls->hin_table_name, " (id, item, val) values ", itemsets, NULL); + hin_query = ap_pstrcat(r->pool, "insert into `", cls->hin_table_name, "` (id, item, val) values ", itemsets, NULL); #ifdef DEBUG ap_log_error(APLOG_MARK,DEBUGLEVEL,orig->server,"mod_log_sql: header_in string: %s", hin_query); #endif } - + + + /* Work through the list of cookies defined by LogSQLWhichCookies */ + i = 0; + itemsets = ""; + + ptrptr2 = (char **) (cls->cookie_list->elts + (cls->cookie_list->nelts * cls->cookie_list->elt_size)); + for (ptrptr = (char **) cls->cookie_list->elts; ptrptr < ptrptr2; ptrptr = (char **) ((char *) ptrptr + cls->cookie_list->elt_size)) { + /* If the specified cookie (*ptrptr) exists for the current request... */ + if ( strncmp((theitem = extract_specific_cookie(r, *ptrptr)), "-", 1) ) { + itemsets = ap_pstrcat(r->pool, itemsets, + (i > 0 ? "," : ""), + "('", + unique_id, + "','", + escape_query(*ptrptr, r->pool), + "','", + escape_query(theitem, r->pool), + "')", + NULL); + i++; + } + + } + if ( itemsets != "" ) { + cookie_query = ap_pstrcat(r->pool, "insert into `", cls->cookie_table_name, "` (id, item, val) values ", itemsets, NULL); + #ifdef DEBUG + ap_log_error(APLOG_MARK,DEBUGLEVEL,orig->server,"mod_log_sql: cookie string: %s", cookie_query); + #endif + } + + /* Set up the actual INSERT statement */ - access_query = ap_pstrcat(r->pool, "insert into ", cls->transfer_table_name, " (", fields, ") values (", values, ")", NULL); + access_query = ap_pstrcat(r->pool, "insert into `", cls->transfer_table_name, "` (", fields, ") values (", values, ")", NULL); #ifdef DEBUG ap_log_error(APLOG_MARK,DEBUGLEVEL,r->server,"mod_log_sql: access string: %s", access_query); #endif - + + /* If the person activated force-preserve, go ahead and push all the entries + * into the preserve file, then return. + */ + if (force_preserve) { + #ifdef DEBUG + ap_log_error(APLOG_MARK,DEBUGLEVEL,orig->server,"mod_log_sql: preservation forced"); + #endif + preserve_entry(orig, access_query); + if ( note_query != NULL ) + preserve_entry(orig, note_query); + if ( hin_query != NULL ) + preserve_entry(orig, hin_query); + if ( hout_query != NULL ) + preserve_entry(orig, hout_query); + if ( cookie_query != NULL ) + preserve_entry(orig, cookie_query); + return OK; + } + /* How's our mysql link integrity? */ if (mysql_log == NULL) { /* Make a try to establish the link */ - open_logdb_link(); - + open_logdb_link(r->server); + if (mysql_log == NULL) { /* Unable to re-establish a DB link, so assume that it's really - * gone and send the entry to the preserve file instead. + * gone and send the entry to the preserve file instead. * This short-circuits safe_mysql_query during a db outage and therefore - * we don't keep logging the db error over and over. + * we don't keep logging the db error over and over. */ preserve_entry(orig, access_query); - if ( note_query != NULL ) + if ( note_query != NULL ) preserve_entry(orig, note_query); if ( hin_query != NULL ) preserve_entry(orig, hin_query); if ( hout_query != NULL ) preserve_entry(orig, hout_query); - + if ( cookie_query != NULL ) + preserve_entry(orig, cookie_query); + return OK; } else { /* Whew, we got the DB link back */ ap_log_error(APLOG_MARK,NOTICELEVEL,orig->server,"mod_log_sql: child established database connection"); } } - - + + /* ---> So as of here we have a non-null value of mysql_log. <--- */ /* ---> i.e. we have a good MySQL connection. <--- */ - + /* Make the tables if we're supposed to. */ if ((cls->table_made != 1) && (create_tables != 0)) { @@ -1362,14 +1586,14 @@ int log_sql_transaction(request_rec *orig) else ap_log_error(APLOG_MARK,NOTICELEVEL,orig->server,"mod_log_sql: no problems creating tables for %s", ap_get_server_name(orig)); } - + /* Make the access-table insert */ result = safe_mysql_query(orig, access_query); - + /* It failed, but NOT because table didn't exist */ if ( (result != 0) && (result != 1146) ) preserve_entry(orig,access_query); - + /* It failed because table didn't exist */ if (result == 1146) { ap_log_error(APLOG_MARK,ERRLEVEL,orig->server,"mod_log_sql: hmm, table didn't yet exist; creating"); @@ -1387,17 +1611,20 @@ int log_sql_transaction(request_rec *orig) } } } - + /* Log the optional notes, headers, etc. */ if ( note_query != NULL ) safe_mysql_query(orig, note_query); - + if ( hout_query != NULL ) safe_mysql_query(orig, hout_query); - + if ( hin_query != NULL ) safe_mysql_query(orig, hin_query); - + + if ( cookie_query != NULL ) + safe_mysql_query(orig, cookie_query); + return OK; } } @@ -1406,7 +1633,7 @@ int log_sql_transaction(request_rec *orig) /* The configuration array that sets up the hooks into the module. */ -module mysql_log_module = { +module sql_log_module = { STANDARD_MODULE_STUFF, NULL, /* module initializer */ NULL, /* create per-dir config */ @@ -1428,5 +1655,5 @@ module mysql_log_module = { log_sql_child_exit, /* process exit/cleanup */ NULL /* [1] post read-request */ #endif - + }; -- cgit