path: root/Documentation/documentation.tex

%% 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}