1 files changed, 1534 insertions, 0 deletions

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 <bourbon@netvision.net.il>.
+
+All changes from 1.06+ and the new documentation were added by Chris
+Powell <chris@grubbybaby.com>. 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}
+<VirtualHost~1.2.3.4>
+
+{[}snip{]}
+
+LogSQLTransferLogTable~access\_log
+
+{[}snip{]}
+
+</VirtualHost>
+\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}
+<VirtualHost~1.2.3.4>~
+
+~CookieTracking~on~
+
+~CookieStyle~Cookie~
+
+~CookieName~Foobar~
+
+~LogSQLTransferLogFormat~huSUsbTvRAc~
+
+~LogSQLWhichCookie~Foobar~
+
+</VirtualHost>
+\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}
+<VirtualHost~1.2.3.4:443>~
+
+~LogSQLTransferLogFormat~AbHhmRSsTUuvcQqz~
+
+</VirtualHost>
+\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.
+
+<OPINION>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.</OPINION>
+
+
+\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}