path: root/Documentation/documentation.lyx

#LyX 1.3 created this file. For more info see http://www.lyx.org/
\lyxformat 221
\textclass article
\language english
\inputencoding default
\fontscheme default
\graphics default
\float_placement !htbp
\paperfontsize 10
\spacing single 
\papersize letterpaper
\paperpackage a4
\use_geometry 1
\use_amsmath 0
\use_natbib 0
\use_numerical_citations 0
\paperorientation portrait
\leftmargin 1in
\topmargin 0.5in
\rightmargin 1in
\bottommargin 0.65in
\secnumdepth 3
\tocdepth 3
\paragraph_separation indent
\defskip medskip
\quotes_language english
\quotes_times 2
\papercolumns 1
\papersides 1
\paperpagestyle default

\layout Title
\added_space_top vfill \added_space_bottom vfill 
Installing and Running mod_log_sql
\layout Author

Christopher Powell, <chris@grubbybaby.com> 
\layout Standard
\pagebreak_bottom 

\begin_inset LatexCommand \tableofcontents{}

\end_inset 


\layout Section

Introduction
\layout Subsection

Homepage 
\layout LyX-Code

http://www.grubbybaby.com/mod_log_sql/
\layout Subsection

Summary
\layout Standard

This Apache module will permit you to log to a SQL database; it can log
 each access request as well as data associated with each request: cookies,
 notes, and inbound/outbound headers.
 Unlike logging to a flat text file -- which is standard in Apache -- a
 SQL-based log exhibits tremendous flexibility and power of data extraction.
 (See section 
\begin_inset LatexCommand \ref{sub:why}

\end_inset 

 in the FAQ for further discussion and examples of the advantages to SQL.)
\layout Standard

This module can either replace or happily coexist with mod_log_config, Apache's
 text file logging facility.
 In addition to being more configurable than the standard module, mod_log_sql
 is much more flexible.
\layout Subsection

Approach
\layout Standard

This project was formerly known as 
\begin_inset Quotes eld
\end_inset 

mod_log_mysql.
\begin_inset Quotes erd
\end_inset 

 It was renamed 
\begin_inset Quotes eld
\end_inset 

mod_log_sql
\begin_inset Quotes erd
\end_inset 

 in order to reflect the project goal of database-inspecificity.
 The module currently supports MySQL, but support for other database backends
 is underway.
\layout Standard

In order to save speed and overhead, links are kept alive in between queries.
 This module uses one dedicated SQL link per httpd child, opened by each
 child process when it is born.
 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.
 Error reporting is robust throughout the module and will inform the administrat
or of database issues in the Apache 
\noun on 
ErrorLog
\noun default 
 for the server/virtual server.
\layout Standard

Virtual hosts are supported in the same manner they are in the regular logging
 modules.
 The administrator defines some basic 'global' directives in the main server
 config, then defines more specific 'local' directives inside each VirtualHost
 stanza.
\layout Standard

A robust "preserve" capability has now been implemented.
 This permits the module to preserve any failed INSERT commands to a local
 file on its machine.
 In any situation that the database is unavailable -- e.g.
 the network fails or the database host is rebooted -- mod_log_sql will
 note this in the error log and begin appending its log entries to the preserve
 file (which is created with the user & group ID of the running Apache process,
 e.g.
 "nobody/nobody" on many Linux installations).
 When database availablity returns, mod_log_sql seamlessly resumes logging
 to it.
 When convenient for the sysadmin, he/she can easily import the preserve
 file into the database because it is simply a series of SQL insert statements.
\layout Subsection

What gets logged by default? 
\layout Standard

All the data that would be contained in the "Combined Log Format" is logged
 by default, plus a little extra.
 Your best bet is to begin by accepting this default, then later customize
 the log configuration based on your needs.
\layout Standard

The documentation of the run-time directives includes a full explanation
 of what you can log, including examples -- see section 
\begin_inset LatexCommand \ref{sec:ConfRef}

\end_inset 

.
\layout Subsection

Miscellaneous Notes
\layout Itemize

Note which directives go in the 'main server config' and which directives
 apply to the 'virtual host config'.
 This is made clear in the directive documentation.
\layout Itemize

The 'time_stamp' field is stored in an UNSIGNED INTEGER format, in the standard
 unix 
\begin_inset Quotes eld
\end_inset 

seconds since the epoch
\begin_inset Quotes erd
\end_inset 

 format.
 This is superior to storing the access time as a string due to size requirement
s: an UNSIGNED INT requires 4 bytes, whereas an Apache date string (e.g.
 "18/Nov/2001:13:59:52 -0800") requires 26 bytes: those extra 22 bytes become
 significant when multiplied by thousands of accesses on a busy server.
 Besides, an INT type is far more flexible for comparisons, etc.
\begin_deeper 
\layout Standard

In MySQL 3.21 and above you can easily convert this to a human readable format
 using from_unixtime(), e.g.:
\layout LyX-Code

select remote_host,request_uri,from_unixtime(time_stamp) from access_log;
\layout Standard

The enclosed perl program 
\begin_inset Quotes eld
\end_inset 

make_combined_log.pl
\begin_inset Quotes srd
\end_inset 

 extracts your access log in a format that is completely compatible with
 the Combined Log Format.
 You can then feed this to your favorite web log analysis tool.
\end_deeper 
\layout Itemize

The table's string values can be CHAR or VARCHAR, at a length of your choice.
 VARCHAR is superior because it truncates long strings; CHAR types are fixed-len
gth and will be padded with spaces, resulting in waste.
 Just like the time_stamp issue described above, that kind of space waste
 multiplies over thousands of records.
\layout Itemize

Be careful not to go overboard setting fields to NOT NULL.
 If a field is marked NOT NULL then it must contain data in the INSERT statement
, or the INSERT will fail.
 These mysterious failures can be quite frustrating and difficult to debug.
\layout Itemize

When Apache logs a numeric field, it uses a '-' character to mean 
\begin_inset Quotes eld
\end_inset 

not applicable,
\begin_inset Quotes erd
\end_inset 

 e.g.
 the number of bytes returned on a 304 (unchanged) request.
 Since '-' is an illegal character in an SQL numeric field, such fields
 are assigned the value 0 instead of '-' which, of course, makes perfect
 sense anyway.
\layout Subsection

Author / Maintainer
\layout Standard

The actual logging code was taken from the already existing flat file text
 modules, so all that credit goes to the Apache Server group.
\layout Standard

The MySQL routines and directives were added by Zeev Suraski <bourbon@netvision.n
et.il>.
\layout Standard

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 "unmaintained" category --
 it hadn't been updated since 1998 -- so Chris adopted it as the new maintainer.
\layout Section

Installation
\layout Subsection

Requirements
\layout Itemize

A compatible system.
 mod_log_sql was authored and tested on systems based on Red Hat Linux (Red
 Hat, Mandrake), but the module should easily adapt to any modern distribution.
 mod_log_sql has also been ported successfully to Solaris and FreeBSD.
\layout Itemize

Apache 1.2 or 1.3.
 Ideally you should already have successfully compiled Apache and understand
 the process, but this document tries to make it simple for beginners.
\layout Itemize

The MySQL development headers.
 This package is called different things on different distros.
 For example, Red Hat 6.x calls this RPM 
\begin_inset Quotes eld
\end_inset 

MySQL-devel
\begin_inset Quotes erd
\end_inset 

 whereas Mandrake calls it 
\begin_inset Quotes eld
\end_inset 

libmysql10-devel.
\begin_inset Quotes erd
\end_inset 


\layout Itemize

MySQL >= 3.23.15 configured, installed and running on either localhost or
 an accessible networked machine.
 You should already have a basic understanding of MySQL and how it functions.
\layout Itemize

Optionally, if you want to be able to log SSL information such as keysize
 or cipher, you need OpenSSL and mod_ssl installed.
\layout Subsection

Platform-specific notes
\layout Standard

These installation documents assume a relatively modern GNU/Linux scenario.
 mod_log_sql has been ported to other platforms; following are notes on
 compiling the module for those platforms.
\layout Subsubsection


\begin_inset LatexCommand \label{sub:Solaris}

\end_inset 

Solaris
\layout Standard

The nanosleep() function used in mod_log_sql relies on linking aginst the
 librt library.
 Make the following alterations before proceeding:
\layout Enumerate

In Makefile, search for the string 
\begin_inset Quotes eld
\end_inset 

-lmysqlclient -lz
\begin_inset Quotes erd
\end_inset 

 and change it to read 
\begin_inset Quotes eld
\end_inset 

-lmysqlclient -lz -lrt
\begin_inset Quotes erd
\end_inset 


\layout Enumerate

In part 
\begin_inset LatexCommand \ref{step:Linking}

\end_inset 

 of section 
\begin_inset LatexCommand \ref{sec:Static}

\end_inset 

 below, change 
\begin_inset Quotes eld
\end_inset 

-lmysqlclient -lm -lz
\begin_inset Quotes erd
\end_inset 

 to read 
\begin_inset Quotes eld
\end_inset 

-lmysqlclient -lm -lz -lrt
\begin_inset Quotes erd
\end_inset 


\layout Subsubsection

BSD
\layout Standard

No notes are available at present, but they are desired.
 If you have successfully ported mod_log_sql to BSD, 
\emph on 
please 
\emph default 
contact 
\begin_inset LatexCommand \url[the maintaniner, Chris Powell]{(chris@grubbybaby.com)}

\end_inset 

 and help fill in this section.
\layout Subsubsection

Win32
\layout Standard

No notes are available at present, but they are desired.
 If you have successfully ported mod_log_sql to Win32, 
\emph on 
please 
\emph default 
contact 
\begin_inset LatexCommand \url[the maintaniner, Chris Powell]{(chris@grubbybaby.com)}

\end_inset 

 and help fill in this section.
\layout Subsubsection

OS X
\layout Standard

mod_log_sql should compile and work out-of-the-box on this platform.
 Here are some notes from a user successfully running the module on OS X:
\layout Quote


\emph on 
The only changes I had to make were to where I had the various libraries
 installed.
\layout Quote


\emph on 
Here are the changes I made to the head of the Makefile:
\layout LyX-Code

APACHESOURCE    = /usr/local/src/apache_1.3.27 
\layout Quote


\emph on 
(Wasn't sure if this was really needed or not, so I downloaded the Apache
 source just in case)
\layout LyX-Code

APACHEINSTALLED = /usr/sbin 
\layout LyX-Code

APACHEHEADERS   = /usr/include/httpd 
\layout LyX-Code

APXS            = $(APACHEINSTALLED)/apxs
\layout LyX-Code

MYSQLLIBRARIES  = /usr/local/mysql/lib 
\layout LyX-Code

MYSQLHEADERS    = /usr/local/mysql/include
\layout Quote


\emph on 
I'm using a binary installation of MySQL and the default apache installation
 on OS X Client 10.2.3, the locations of these files may vary depending on
 how you've installed MySQL and will almost certainly be different if you're
 using OS X Server.
\layout Standard

My thanks to Tom Wiebe for being the first (to my knowlege) mod_log_sql
 user on OS X and for providing these notes.
\layout Subsubsection

Digital Unix
\layout Standard

Digital Unix, like Solaris, needs to be linked against librt; see section
 
\begin_inset LatexCommand \ref{sub:Solaris}

\end_inset 

.
 Here are further notes from a user successfully running the module on Digital
 Unix:
\layout Quote


\emph on 
Instead of trying to get the module to remember where the MySQL libraries
 were, I instead compiled apache with the information:
\layout Quote


\emph on 
LDFLAGS='-rpath /isp/mysql/lib/mysql' ./configure ...
\layout Quote


\emph on 
Everything worked as expected after that.
 (The error I got without this was "/sbin/loader: Fatal Error: cannot map
 libmysqlclient.so" )
\layout Quote


\emph on 
Digital Unix (v4.0f, at least ) appears to follow the same requirements needed
 by Solaris, so simply adding librt to the module made it compile without
 errors.
\layout Quote


\emph on 
As for the warnings, here's the text:
\layout LyX-Code


\emph on 
mod_log_sql.c: In function `extract_request_duration': 
\layout LyX-Code


\emph on 
mod_log_sql.c:292: warning: long int format, different type arg (arg 4)
\layout LyX-Code


\emph on 
mod_log_sql.c: In function `extract_request_timestamp': 
\layout LyX-Code


\emph on 
mod_log_sql.c:497: warning: long int format, different type arg (arg 4)
\layout Quote


\emph on 
Poking around in the code, it looks like the compiler was complaining that
 what time() is returning doesn't play nicely with %ld by default.
 I just typecast them as (long)'s and the warnings went away ( not that
 the module wasn't working correctly without them ).
\layout Quote


\emph on 
The module works very well so far in testing...
 hasn't dropped a single log entry yet.
 
\layout Standard

My thanks to Jim Turner for permitting me to quote him here, and for being
 the first known user of mod_log_sql on Digital Unix.
\layout Subsection

Do I want a DSO or a static module?
\layout Standard

You need to know the answer to this question before you proceed.
 The answer is pretty straightforward: what have you done in the past? If
 you like all your Apache modules to be dynamic, then you should keep doing
 that.
 If you're more of an old-school type and prefer to compile the modules
 right into apache, do that.
 Both methods work equally well.
\layout Standard

FWIW, the DSO method is more modern and increasing in popularity because
 apxs takes care of a lot of dirty little details for you.
 As you'll see below, the static-module method is a little more complex.
\layout Subsection

Installation as an Apache DSO (Preferred) 
\layout Enumerate

Perform all the following steps as root so that you have install privs,
 etc.
 Unpack the archive into a working directory.
\begin_deeper 
\layout LyX-Code

# tar zxf mod_log_sql.tar.gz -C /usr/local/src 
\layout LyX-Code

# cd /usr/local/src/mod_log_sql
\end_deeper 
\layout Enumerate

Edit Makefile and change the values of the variables in the first section.
 
\begin_deeper 
\layout Enumerate

These paths are 
\series bold 
necessary:
\begin_deeper 
\layout Description

APACHEINSTALLED: the location where you installed Apache -- usually /usr/local/a
pache, 'locate apxs' can help you find it.
\layout Description

APACHEHEADERS: The location of your Apache header files, find using 'locate
 httpd.h'
\layout Description

MYSQLLIBRARIES: The location of your MySQL libraries, find using 'locate
 libmysqlclient.so'
\layout Description

MYSQLHEADERS: The location of your MySQL header files, find using 'locate
 mysql.h'
\end_deeper 
\layout Enumerate


\series bold 
Optional
\series default 
: if you compiled mod_ssl for Apache and want to log SSL data such as 'keysize'
 and 'cipher type': 
\begin_deeper 
\layout Description

MODSSLHEADERS: the location of your mod_ssl header files, find using 'locate
 mod_ssl.h'
\layout Description

DB1HEADERS: the location of your db1 header files, find using 'locate ndbm.h'
\end_deeper 
\layout Standard

You do 
\series bold 
not 
\series default 
need to compile SSL support into mod_log_sql in order to simply use it with
 a secure site.
 You only need to compile SSL support into mod_log_sql 
\series bold 
if you want to log SSL-specific data 
\series default 
such as the cipher type.
\end_deeper 
\layout Enumerate

IMPORTANT: If you are not logging SSL info, comment out MODSSLHDRS by putting
 a # character in front of it:
\begin_deeper 
\layout LyX-Code

#MODSSLHDRS=/usr/include/...
\end_deeper 
\layout Enumerate

Instruct apxs to compile the module as a DSO.
\begin_deeper 
\layout LyX-Code

# make dso
\layout Standard

You should see output similar to the following:
\layout LyX-Code

/usr/local/Apache/bin/apxs -Wc,-O2 -Wc,-Wall -Wc,-DEAPI -c -I/usr/...
\layout LyX-Code

gcc -DLINUX=22 -DNO_DBM_REWRITEMAP -DMOD_SSL=208111 -DUSE_HS...
 
\layout LyX-Code

gcc -shared -o mod_log_sql.so mod_log_sql.o -Wc,-O2 -Wc,-Wall -Wc...
\layout Standard

You should see no errors and have a new file called "mod_log_sql.so" in your
 directory.
\end_deeper 
\layout Enumerate

Instruct apxs to install the DSO.
\begin_deeper 
\layout LyX-Code

# make dsoinstall
\layout Standard

You should see output similar to the following:
\layout LyX-Code

/usr/local/Apache/bin/apxs -i mod_log_sql.so 
\layout LyX-Code

cp mod_log_sql.so /usr/local/Apache/libexec/mod_log_sql.so 
\layout LyX-Code

chmod 755 /usr/local/Apache/libexec/mod_log_sql.so 
\end_deeper 
\layout Enumerate

Load and activate the module in httpd.conf:
\begin_deeper 
\layout Enumerate

Insert this line in the same area as other logging modules, e.g.
 near 
\begin_inset Quotes eld
\end_inset 

LoadModule config_log_module
\begin_inset Quotes erd
\end_inset 

:
\begin_deeper 
\layout LyX-Code

LoadModule sql_log_module libexec/mod_log_sql.so
\end_deeper 
\layout Enumerate

Insert this line in the same area as other logging modules, e.g.
 near 
\begin_inset Quotes eld
\end_inset 

AddModule mod_log_config.c
\begin_inset Quotes erd
\end_inset 

:
\begin_deeper 
\layout LyX-Code

AddModule mod_log_sql.c
\end_deeper 
\end_deeper 
\layout Enumerate

Module ordering within httpd.conf is important if you are logging SSL information.
 Please ensure that
\begin_deeper 
\layout LyX-Code

LoadModule ssl_module libexec/libssl.so
\layout Standard

comes before
\layout LyX-Code

LoadModule sql_log_module libexec/mod_log_sql.so
\layout Standard

in your httpd.conf file.
 If they are out of order, simply cut-and-paste the 
\begin_inset Quotes eld
\end_inset 

ssl_module
\begin_inset Quotes erd
\end_inset 

 section so that it is at the top.
 If you do not, you will get this error when you start Apache:
\layout LyX-Code

/usr/local/apache/libexec/mod_log_mysql.so: undefined symbol: ssl_var_lookup
\layout LyX-Code

/usr/local/apache/bin/apachectl startssl: httpd could not be started
\layout Standard

(mod_log_sql has a dependency on mod_ssl for SSL symbols.
 If the statements are out of order, mod_log_sql cannot recognize those
 symbols.)
\layout Standard

Now skip below to section 
\begin_inset LatexCommand \ref{sec:Configuration}

\end_inset 

, 
\series bold 
Configuration
\series default 
.
\end_deeper 
\layout Subsection


\begin_inset LatexCommand \label{sec:Static}

\end_inset 

Installation as a static module compiled into httpd
\layout Enumerate

Perform all the following steps as root so that you have install privs,
 etc.
\layout Enumerate

Unpack the archive into a working directory.
\begin_deeper 
\layout LyX-Code

# tar zxf mod_log_sql.tar.gz -C /usr/local/src 
\layout LyX-Code

# cd /usr/local/src/mod_log_sql
\end_deeper 
\layout Enumerate


\begin_inset LatexCommand \label{step:editMF}

\end_inset 

Edit Makefile and change the values of the variables in the first section.
 
\begin_deeper 
\layout Enumerate

These are 
\series bold 
necessary:
\begin_deeper 
\layout Description

APACHEINSTALLED: the location where you installed Apache -- usually /usr/local/a
pache, 'locate apxs' can help you find it.
\layout Description

APACHESOURCE: the location of your Apache 
\series bold 
sources
\series default 
, find using 'locate ABOUT_APACHE' 
\layout Description

APACHEHEADERS: the location of your Apache header files, find using 'locate
 httpd.h'
\layout Description

MYSQLLIBRARIES: the location of your MySQL libraries, find using 'locate
 libmysqlclient.so'
\layout Description

MYSQLHEADERS: the location of your MySQL header files, find using 'locate
 mysql.h'
\end_deeper 
\layout Enumerate


\series bold 
Optional
\series default 
: if you compiled mod_ssl for Apache and want to log SSL data such as 'keysize'
 and 'cipher type': 
\begin_deeper 
\layout Description

MODSSLHEADERS: the location of your mod_ssl header files, find using 'locate
 mod_ssl.h'
\layout Description

DB1HEADERS: the location of your db1 header files, find using 'locate ndbm.h'
\end_deeper 
\layout Standard

You do 
\series bold 
not 
\series default 
need to compile SSL support into mod_log_sql in order to simply use it with
 a secure site.
 You only need to compile SSL support into mod_log_sql 
\series bold 
if you want to log SSL-specific data
\series default 
 such as the cipher type.
\end_deeper 
\layout Enumerate

IMPORTANT: If you are not logging SSL info, comment out MODSSLHDRS by putting
 a # character in front of it:
\begin_deeper 
\layout LyX-Code

#MODSSLHDRS=/usr/include/...
\end_deeper 
\layout Enumerate

Compile the module.
\begin_deeper 
\layout LyX-Code

# make static
\layout Standard

You should see output similar to the following:
\layout LyX-Code

gcc -fpic -O2 -Wall -I/usr/local/Apache/include -I/usr/include/mysql -I/usr/lo...
\layout Standard

You should see no errors and have a new file called "mod_log_sql.o" in your
 directory.
\end_deeper 
\layout Enumerate

Install the module.
\begin_deeper 
\layout LyX-Code

# make statinstall
\end_deeper 
\layout Enumerate

Change to your Apache source directory.
\begin_deeper 
\layout LyX-Code

# cd /usr/local/src/apache-1.3.22/src
\end_deeper 
\layout Enumerate

Re-compile your httpd binary as follows.
\begin_deeper 
\layout Enumerate


\begin_inset LatexCommand \label{step:Linking}

\end_inset 

Make these changes to Configuration.apaci:
\begin_deeper 
\layout Itemize

Append the following string to the EXTRA_LIBS= line.
 ("/usr/lib/mysql" is from step 
\begin_inset LatexCommand \ref{step:editMF}

\end_inset 

, and is where your MySQL libraries live):
\layout LyX-Code

-L/usr/lib/mysql -lmysqlclient -lm -lz
\layout Itemize

Find the mod_log_config.o line, and insert this line immediately after it:
\layout LyX-Code

AddModule modules/sql/mod_log_sql.o
\end_deeper 
\layout Enumerate

# cp Configuration.apaci Configuration
\layout Enumerate

# ./Configure
\layout Enumerate

# make
\layout Enumerate

# strip httpd
\end_deeper 
\layout Enumerate

Test your new apache binary:
\begin_deeper 
\layout LyX-Code

# ./httpd -l
\layout Standard

You should see something like:
\layout LyX-Code

Compiled-in modules: 
\layout LyX-Code

http_core.c
\layout LyX-Code

mod_log_sql.c       <-- That's the line you're looking for.
\layout LyX-Code

mod_env.c 
\layout LyX-Code

mod_log_config.c 
\layout LyX-Code

mod_mime.c 
\layout LyX-Code

mod_negotiation.c
\layout LyX-Code

etc...
\end_deeper 
\layout Enumerate

Install your httpd binary.
 Copy it over your old httpd binary, wherever it lives.
 You can and should rename your old httpd first so that you can easily revert
 to that working version in case of bugs with the new version.
\begin_deeper 
\layout LyX-Code

# /etc/rc.d/init.d/httpd stop 
\layout LyX-Code

# mv /usr/local/Apache/bin/httpd ~/httpd-save 
\layout LyX-Code

# cp -f ./httpd /usr/local/Apache/bin/
\end_deeper 
\layout Section


\begin_inset LatexCommand \label{sec:Configuration}

\end_inset 

Configuration
\layout Subsection


\begin_inset LatexCommand \label{sub:PrepDb}

\end_inset 

Preparing MySQL for logging
\layout Standard

You have to prepare the database to receive data from mod_log_sql, and set
 up run-time directives in httpd.conf to control how and what mod_log_sql
 logs.
\layout Standard

This section will discuss how to get started with a basic config.
 Full documentation of all available run-time directives is available in
 section 
\begin_inset LatexCommand \ref{sec:ConfRef}

\end_inset 

.
\layout Enumerate

mod_log_sql can make its own tables on-the-fly, or you can pre-make the
 tables by hand.
 The advantage of letting the module make the tables is ease-of-use, but
 for raw performance you will want to pre-make the tables in order to save
 some overhead.
 In this basic setup we'll just let the module create tables for us.
\layout Enumerate

We still need to have a logging database created and ready, so run the MySQL
 command line client and create a database:
\begin_deeper 
\layout LyX-Code

# mysql -uadmin -pmypassword 
\layout LyX-Code

Enter password:
\layout LyX-Code

mysql> create database apachelogs;
\end_deeper 
\layout Enumerate


\begin_inset LatexCommand \label{part:CrTbl}

\end_inset 

If you want to hand-create the tables, run the enclosed 'create-tables'
 SQL script as follows (
\begin_inset Quotes eld
\end_inset 

create_tables.sql
\begin_inset Quotes erd
\end_inset 

 needs to be in your current working directory).
\begin_deeper 
\layout LyX-Code

mysql> use apachelogs
\layout LyX-Code

Database changed
\layout LyX-Code

mysql> source create_tables.sql
\end_deeper 
\layout Enumerate

Create a specific MySQL userid that httpd will use to authenticate and enter
 data.
 This userid need not be an actual Unix user.
 It is a userid internal to MySQL with specific privileges.
 In the following example command, "apachelogs" is the database, "loguser"
 is the userid to create, "my.apachemachine.com" is the name of the Apache
 machine, and "l0gger" is the password to assign.
 Choose values that are different from these examples.
\begin_deeper 
\layout LyX-Code

mysql> grant insert,create on apachelogs.* to loguser@my.apachemachine.com
 identified by 'l0gger';
\end_deeper 
\layout Enumerate

You may be especially security-paranoid and want "loguser" to 
\emph on 
not 
\emph default 
have "create" capability within the "apachelogs" database.
 You can disable that privilege, but the cost is that you will not be able
 to use the module's on-the-fly table creation feature.
 If that cost is acceptable, hand-create the tables as described in step
 
\begin_inset LatexCommand \ref{part:CrTbl}

\end_inset 

 and use the following GRANT statement instead of the one above:
\begin_deeper 
\layout LyX-Code

mysql> grant insert on apachelogs.* to loguser@my.apachemachine.com
\layout LyX-Code

identified by 'l0gger';
\end_deeper 
\layout Enumerate


\begin_inset LatexCommand \label{step:EnaLog}

\end_inset 

Enable full logging of your MySQL daemon (at least temporarily for debugging
 purposes) if you don't do this already.
 Edit /etc/my.cnf and add the following line to your [mysqld] section:
\begin_deeper 
\layout LyX-Code

log=/var/log/mysql-messages
\layout Standard

Then restart MySQL.
\layout LyX-Code

# /etc/rc.d/init.d/mysql restart
\end_deeper 
\layout Subsection

A very basic logging setup in Apache
\layout Enumerate

Tell the module what database to use and the appropriate authentication
 information.
\begin_deeper 
\layout Standard

So, edit httpd.conf and insert the following lines somewhere after any LoadModule
 / AddModule statements.
 
\emph on 
Make sure these statements are 
\begin_inset Quotes eld
\end_inset 

global,
\begin_inset Quotes erd
\end_inset 

 i.e.
 not inside any VirtualHost stanza
\emph default 
.
 You will also note that you are embedding a password in the file.
 Therefore you are advised to 
\begin_inset Quotes eld
\end_inset 

chmod 660 httpd.conf
\begin_inset Quotes erd
\end_inset 

 to prevent unauthorized regular users from viewing your database user and
 password.
\layout Standard


\series bold 
Example
\series default 
: Use the MySQL database called "apachelogs" running on "dbmachine.foo.com".
 Use username "loguser" and password "l0gg3r" to authenticate to the database.
 Permit the module create tables for us.
\layout LyX-Code

LogSQLLoginInfo dbmachine.foo.com loguser l0gg3r 
\layout LyX-Code

LogSQLDatabase apachelogs
\layout LyX-Code

LogSQLCreateTables on
\layout Standard

If your database resides on localhost instead of another host, specify the
 MySQL server's socket file as follows:
\layout LyX-Code

LogSQLSocketFile /your/path/to/mysql.sock
\layout Standard

If your database is listening on a port other than 3306, specify the correct
 TCP port as follows:
\layout LyX-Code

LogSQLTCPPort 1234
\end_deeper 
\layout Enumerate

The actual logging is set up on a virtual-host-by-host basis.
 So, skip down to the virtual host you want to set up.
 Instruct this virtual host to log entries to the table 
\begin_inset Quotes eld
\end_inset 

access_log
\begin_inset Quotes srd
\end_inset 

 by inserting a 
\noun on 
LogSQLTransferLogTable
\noun default 
 directive.
 (The 
\noun on 
LogSQLTransferLogTable
\noun default 
 directive is the minimum required to log -- other directives that you'll
 learn about later simply tune the module's behavior.)
\begin_deeper 
\layout LyX-Code

<VirtualHost 1.2.3.4>
\layout LyX-Code

 [snip]
\layout LyX-Code

 LogSQLTransferLogTable access_log
\layout LyX-Code

 [snip]
\layout LyX-Code

</VirtualHost>
\end_deeper 
\layout Enumerate

Restart apache.
\begin_deeper 
\layout LyX-Code

# /etc/rc.d/init.d/httpd stop
\layout LyX-Code

# /etc/rc.d/init.d/httpd start
\end_deeper 
\layout Subsection

Testing the basic setup
\layout Enumerate

Visit your web site in a browser to trigger some hits, then confirm that
 the entries are being successfully logged:
\begin_deeper 
\layout LyX-Code

# mysql -hdbmachine.foo.com -umysqladmin -p -e "select * from access_log"
 apachelogs 
\layout LyX-Code

Enter password:
\layout Standard

Several lines of output should follow, corresponding to your hits on the
 site.
 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.
 If you do not see any entries in the access_log, please consult section
 
\begin_inset LatexCommand \ref{faq:NothingLogged}

\end_inset 

 of the FAQ on how to debug and fix the situation.
\end_deeper 
\layout Enumerate

You can now activate the advanced features of mod_log_sql, which are described
 in the next section.
\layout Subsection

How to tune logging with run-time directives
\layout Subsubsection

Instructing the module what to log
\layout Standard

The most basic directive for the module is 
\noun on 
LogSQLTransferLogFormat
\noun default 
, which tells the module which information to send to the database; logging
 to the database will not take place without it.
 Place a 
\noun on 
LogSQLTransferLogFormat
\noun default 
 directive in the VirtualHost stanza of each virtual host that you want
 to activate.
\layout Standard

After 
\noun on 
LogSQLTransferLogFormat
\noun default 
 you supply a string of characters that tell the module what information
 to log.
 In the configuration directive reference (section 
\begin_inset LatexCommand \ref{sub:Frmat}

\end_inset 

) there is a table which clearly defines all the possible things to log.
 Let's say you want to log only the 
\begin_inset Quotes eld
\end_inset 

request time,
\begin_inset Quotes erd
\end_inset 

 the 
\begin_inset Quotes eld
\end_inset 

remote host,
\begin_inset Quotes erd
\end_inset 

 and the 
\begin_inset Quotes eld
\end_inset 

request
\begin_inset Quotes erd
\end_inset 

; you'd use:
\layout LyX-Code

LogSQLTransferLogFormat hUS
\layout Standard

But a more appropriate string to use is
\layout LyX-Code

LogSQLTransferLogFormat AbHhmRSsTUuv
\layout Standard

which logs all the information required to be compatible with the Combined
 Log Format (CLF).
\layout Standard

If you don't choose to log everything that is available, that's fine.
 Fields in the unused columns in your table will simply contain NULL.
\layout Standard

Some of the 
\noun on 
LogSQLTransferLogFormat
\noun default 
 characters require a little extra configuration:
\layout Itemize

If you specify 'c' to indicate that you want to log the cookie value, you
 must also tell the module which cookie you mean by using 
\noun on 
LogSQLWhichCookie
\noun default 
 -- after all, there could be many cookies associated with a given request.
 Fail to specify 
\noun on 
LogSQLWhichCookie
\noun default 
, and no cookie information at all will be logged.
 
\layout Itemize

If you specify 'M' to indicate that you want to log the machine ID, you
 must also tell the module this machine's identity using the 
\noun on 
LogSQLMachineID
\noun default 
 directive.
 Fail to specify 
\noun on 
LogSQLMachineID
\noun default 
, and a simple '-' character will be logged in the machine_id column.
\layout Subsubsection


\begin_inset LatexCommand \label{sub:Ignore}

\end_inset 

Instructing the module what NOT to log using filtering directives
\layout Standard

One 
\begin_inset Quotes eld
\end_inset 

accept
\begin_inset Quotes erd
\end_inset 

 and two 
\begin_inset Quotes eld
\end_inset 

ignore
\begin_inset Quotes srd
\end_inset 

 directives allow you to fine-tune what the module should not log.
 These are very handy for keeping your database as uncluttered as possible
 and keeping your statistics free of unneeded numbers.
 Think of each one as a gatekeeper.
\layout Standard


\emph on 
It is important to remember that each of these three directives is purely
 optional.
 mod_log_sql's default is to log everything.
 
\layout Standard

When a request comes in, the contents of 
\noun on 
LogSQLRequestAccept 
\noun default 
are evaluated first.
 This optional, 
\begin_inset Quotes eld
\end_inset 

blanket
\begin_inset Quotes erd
\end_inset 

 directive lets you specify that only certain things are to be accepted
 for logging, and everything else discarded.
 Because it is evaluated before 
\noun on 
LogSQLRequestIgnore 
\noun default 
and 
\noun on 
LogSQLRemhostIgnore 
\noun default 
it can halt logging before those two filtering directives 
\begin_inset Quotes eld
\end_inset 

get their chance.
\begin_inset Quotes erd
\end_inset 

 
\layout Standard

Once a request makes it past 
\noun on 
LogSQLRequestAccept
\noun default 
, it still can be excluded based on 
\noun on 
LogSQLRemhostIgnore
\noun default 
 and 
\noun on 
LogSQLRequestIgnore
\noun default 
.
 A good way to use 
\noun on 
LogSQLRemhostIgnore
\noun default 
 is to prevent the module from logging the traffic that your internal hosts
 generate.
 
\noun on 
LogSQLRequestIgnore
\noun default 
 is great for preventing things like requests for 
\begin_inset Quotes eld
\end_inset 

favicon.ico
\begin_inset Quotes erd
\end_inset 

 from cluttering up your database, as well as excluding the various requests
 that worms make, etc.
\layout Standard

You can specify a series of strings after each directive.
 Do not use any type of globbing or regular-expression syntax -- each string
 is considered a match
\emph on 
 if it is a substring of the larger request or remote-host; the comarison
 is case-sensitive.

\emph default 
 This means that 
\noun on 

\begin_inset Quotes eld
\end_inset 

LogSQLRemhostIgnore
\noun default 
 micro
\begin_inset Quotes erd
\end_inset 

 will ignore requests from 
\begin_inset Quotes eld
\end_inset 

microsoft.com,
\begin_inset Quotes erd
\end_inset 

 
\begin_inset Quotes eld
\end_inset 

microworld.net,
\begin_inset Quotes erd
\end_inset 

 
\begin_inset Quotes eld
\end_inset 

mymicroscope.org,
\begin_inset Quotes erd
\end_inset 

 etc.
 
\begin_inset Quotes eld
\end_inset 


\noun on 
LogSQLRequestIgnore
\noun default 
 gif
\begin_inset Quotes erd
\end_inset 

 will instruct the module to ignore requests for 
\begin_inset Quotes eld
\end_inset 

leftbar.gif,
\begin_inset Quotes erd
\end_inset 

 
\begin_inset Quotes eld
\end_inset 

bluedot.gif
\begin_inset Quotes erd
\end_inset 

 and even 
\begin_inset Quotes eld
\end_inset 

giftwrap.jpg
\begin_inset Quotes erd
\end_inset 

 -- but 
\begin_inset Quotes eld
\end_inset 

RED.GIF
\begin_inset Quotes erd
\end_inset 

 and 
\begin_inset Quotes eld
\end_inset 

Tree.Gif
\begin_inset Quotes erd
\end_inset 

 would still get logged because of case sensitivity.
\layout Standard

A summary of the decision flow:
\layout Enumerate

If 
\noun on 
LogSQLRequestAccept
\noun default 
 exists and a request does not match anything in that list, it is discarded.
\layout Enumerate

If a request matches anything in the 
\noun on 
LogSQLRequestIgnore 
\noun default 
list, it is discarded.
\layout Enumerate

If a reqiest matches anything in the 
\noun on 
LogSQLRemhostIgnore
\noun default 
 list, it is discarded.
\layout Enumerate

Otherwise the request is logged.
\layout Standard

This means that you can have a series of directives similar to the following:
\layout LyX-Code

LogSQLRequestAccept *.html *.gif *.jpg
\layout LyX-Code

LogSQLRequestIgnore statistics.html bluedot.jpg
\layout Standard

So the first line instructs the module to 
\series bold 
only
\series default 
 log files with html, gif and jpg suffixes; requests for 
\begin_inset Quotes eld
\end_inset 

formail.cgi
\begin_inset Quotes erd
\end_inset 

 and 
\begin_inset Quotes eld
\end_inset 

shopping-cart.pl
\begin_inset Quotes erd
\end_inset 

 will never be considered for logging.
 (
\begin_inset Quotes eld
\end_inset 

LeftArrow.JPG
\begin_inset Quotes erd
\end_inset 

 will also never be considered for logging -- remember, the comparison is
 
\series bold 
case sensitive
\series default 
.) The second line prunes the list further -- you never want to log requests
 for those two objects.
\layout Standard

Tip: if you want to match all the hosts in your domain such as 
\begin_inset Quotes eld
\end_inset 

host1.corp.foo.com
\begin_inset Quotes srd
\end_inset 

 and 
\begin_inset Quotes eld
\end_inset 

server.dmz.foo.com
\begin_inset Quotes srd
\end_inset 

, simply specify:
\layout LyX-Code

LogSQLRemhostIgnore foo.com
\layout Standard

Tip: a great way to catch the vast majority of worm-attack requests and
 prevent them from being logged is to specify:
\layout LyX-Code

LogSQLRequestIgnore root.exe cmd.exe default.ida
\layout Standard

Tip: to prevent the logging of requests for common graphic types, make sure
 to put a '.' before the suffix to avoid matches that you didn't intend:
\layout LyX-Code

LogSQLRequestIgnore .gif .jpg
\layout Subsection

Advanced logging scenarios
\layout Subsubsection

Using the module in an ISP environment
\layout Standard

mod_log_sql has three basic tiers of operation:
\layout Enumerate

The administrator creates all necessary tables by hand and configures each
 Apache VirtualHost by hand.
 (
\noun on 
LogSQLCreateTables Off
\noun default 
)
\layout Enumerate

The module is permitted to create necessary tables on-the-fly, but the administr
ator configures each Apache VirtualHost by hand.
 (
\noun on 
LogSQLCreateTables On
\noun default 
)
\layout Enumerate

The module is permitted to create all necessary tables and to make intelligent,
 on-the-fly configuration of each VirtualHost.
 (
\noun on 
LogSQLMassVirtualHosting On
\noun default 
)
\layout Standard

Many users are happy to use the module in its most minimal form: they hand-creat
e any necessary tables (using 
\begin_inset Quotes eld
\end_inset 

create_tables.sql
\begin_inset Quotes erd
\end_inset 

), and they configure each VirtualHost by hand to suit their needs.
 However, some administrators need extra features due to a large and growing
 number of VirtualHosts.
 The 
\noun on 
LogSQLMassVirtualHosting
\noun default 
 directive activates module capabilities that make it far easier to manage
 an ISP environment, or any situation characterized by a large and varying
 number of virtual servers:
\layout Itemize

the on-the-fly table creation feature is activated automatically
\layout Itemize

the transfer log table name is dynamically set from the virtual host's name
 (example: a virtual host 
\begin_inset Quotes eld
\end_inset 

www.grubbybaby.com
\begin_inset Quotes erd
\end_inset 

 gets logged to table 
\begin_inset Quotes eld
\end_inset 

access_www_grubbybaby_com
\begin_inset Quotes erd
\end_inset 

) 
\layout Standard

There are numerous benefits.
 The admin will not need to create new tables for every new VirtualHost.
 (Although the admin will still need to drop the tables of virtual hosts
 that are removed.) The admin will not need to set 
\noun on 
LogSQLTransferLogTable
\noun default 
 for each virtual host -- it will be configured automatically based on the
 host's name.
 Because each virtual host will log to its own segregated table, data about
 one virtual server will segregate from others; an admin can grant users
 access to the tables they need, and they will be unable to view data about
 another user's virtual host.
\layout Standard

In an ISP scenario the admin is likely to have a cluster of many front-end
 webservers logging to a back-end database.
 mod_log_sql has a feature that permits analysis of how well the web servers
 are loadbalancing: the 
\noun on 
LogSQLMachineID
\noun default 
 directive.
 The administrator uses this directive to assign a unique identifier to
 each machine in the web cluster, e.g.
 
\begin_inset Quotes eld
\end_inset 


\noun on 
LogSQLMachineID
\noun default 
 web01,
\begin_inset Quotes erd
\end_inset 

 
\begin_inset Quotes eld
\end_inset 


\noun on 
LogSQLMachineID
\noun default 
 web02,
\begin_inset Quotes erd
\end_inset 

 etc.
 Used in conjunction with the 'M' character in 
\noun on 
LogSQLTransferLogFormat
\noun default 
, each entry in the SQL log will include the machine ID of the machine that
 created the entry.
 This permits the administrator to count the entries made by each particular
 machine and thereby analyze the front-end loadbalancing algorithm.
\layout Subsubsection


\begin_inset LatexCommand \label{secMulTable}

\end_inset 

Logging many-to-one data in separate tables
\layout Standard

A given HTTP request can have a one-to-many relationship with certain kinds
 of data.
 For example, a single HTTP request can have 4 cookies, 3 headers and 5
 
\begin_inset Quotes eld
\end_inset 

mod_gzip
\begin_inset Quotes erd
\end_inset 

 notes associated with it.
 mod_log_sql is capable of logging these relationships due to the elegance
 of SQL relational data.
\layout Standard

You already have a single table containing access requests.
 One of the columns in that table is 'id' which is intended to contain the
 unique request ID supplied by the standard Apache module mod_unique_id
 -- all you need to do is compile in that module and employ the 
\noun on 
LogSQLTransferLogFormat
\noun default 
 character 'I'.
 Thereafter, each request gets a unique ID that can be thought of as a primary
 key within the database, useful for joining multiple tables.
 So let's envision several new tables: a notes table, a cookies table, and
 a table for inbound and outbound headers.
 
\layout Standard


\begin_inset Float table
wide false
collapsed false

\layout Caption


\begin_inset LatexCommand \label{tblAcc}

\end_inset 

access_log
\layout Standard
\align center 

\begin_inset  Tabular
<lyxtabular version="3" rows="2" columns="6">
<features>
<column alignment="left" valignment="top" leftline="true" width="0pt">
<column alignment="left" valignment="top" leftline="true" width="0pt">
<column alignment="left" valignment="top" leftline="true" rightline="true" width="0pt">
<column alignment="left" valignment="top" rightline="true" width="0pt">
<column alignment="left" valignment="top" rightline="true" width="0pt">
<column alignment="left" valignment="top" rightline="true" width="0pt">
<row topline="true" bottomline="true">
<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
\begin_inset Text

\layout Standard

id
\end_inset 
</cell>
<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
\begin_inset Text

\layout Standard

remote_host
\end_inset 
</cell>
<cell alignment="center" valignment="top" topline="true" leftline="true" rightline="true" usebox="none">
\begin_inset Text

\layout Standard

request_uri
\end_inset 
</cell>
<cell alignment="center" valignment="top" topline="true" leftline="true" rightline="true" usebox="none">
\begin_inset Text

\layout Standard

time_stamp
\end_inset 
</cell>
<cell alignment="center" valignment="top" topline="true" leftline="true" rightline="true" usebox="none">
\begin_inset Text

\layout Standard

status
\end_inset 
</cell>
<cell alignment="center" valignment="top" topline="true" leftline="true" rightline="true" usebox="none">
\begin_inset Text

\layout Standard

bytes_sent
\end_inset 
</cell>
</row>
<row topline="true" bottomline="true">
<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
\begin_inset Text

\layout Standard

PPIDskBRH30AAGPtAsg
\end_inset 
</cell>
<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
\begin_inset Text

\layout Standard

zerberus.aiacs.net
\end_inset 
</cell>
<cell alignment="center" valignment="top" topline="true" leftline="true" rightline="true" usebox="none">
\begin_inset Text

\layout Standard

/mod_log_sql/index.html
\end_inset 
</cell>
<cell alignment="center" valignment="top" topline="true" leftline="true" rightline="true" usebox="none">
\begin_inset Text

\layout Standard

1022493617
\end_inset 
</cell>
<cell alignment="center" valignment="top" topline="true" leftline="true" rightline="true" usebox="none">
\begin_inset Text

\layout Standard

200
\end_inset 
</cell>
<cell alignment="center" valignment="top" topline="true" leftline="true" rightline="true" usebox="none">
\begin_inset Text

\layout Standard

2215
\end_inset 
</cell>
</row>
</lyxtabular>

\end_inset 


\end_inset 


\begin_inset Float table
wide false
collapsed false

\layout Caption


\begin_inset LatexCommand \label{tblNotes}

\end_inset 

notes_log
\layout Standard
\align center 

\begin_inset  Tabular
<lyxtabular version="3" rows="3" columns="3">
<features>
<column alignment="left" valignment="top" leftline="true" width="0pt">
<column alignment="left" valignment="top" leftline="true" width="0pt">
<column alignment="left" valignment="top" leftline="true" rightline="true" width="0pt">
<row topline="true" bottomline="true">
<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
\begin_inset Text

\layout Standard

id
\end_inset 
</cell>
<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
\begin_inset Text

\layout Standard

item
\end_inset 
</cell>
<cell alignment="center" valignment="top" topline="true" leftline="true" rightline="true" usebox="none">
\begin_inset Text

\layout Standard

val
\end_inset 
</cell>
</row>
<row topline="true">
<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
\begin_inset Text

\layout Standard

PPIDskBRH30AAGPtAsg
\end_inset 
</cell>
<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
\begin_inset Text

\layout Standard

mod_gzip_result
\end_inset 
</cell>
<cell alignment="center" valignment="top" topline="true" leftline="true" rightline="true" usebox="none">
\begin_inset Text

\layout Standard

OK
\end_inset 
</cell>
</row>
<row topline="true" bottomline="true">
<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
\begin_inset Text

\layout Standard

PPIDskBRH30AAGPtAsg
\end_inset 
</cell>
<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
\begin_inset Text

\layout Standard

mod_gzip_compression_ratio
\end_inset 
</cell>
<cell alignment="center" valignment="top" topline="true" leftline="true" rightline="true" usebox="none">
\begin_inset Text

\layout Standard

69
\end_inset 
</cell>
</row>
</lyxtabular>

\end_inset 


\end_inset 


\layout Standard


\begin_inset Float table
wide false
collapsed false

\layout Caption


\begin_inset LatexCommand \label{tblHdr}

\end_inset 

headers_log
\layout Standard
\align center 

\begin_inset  Tabular
<lyxtabular version="3" rows="5" columns="3">
<features>
<column alignment="left" valignment="top" leftline="true" width="0pt">
<column alignment="left" valignment="top" leftline="true" width="0pt">
<column alignment="left" valignment="top" leftline="true" rightline="true" width="0pt">
<row topline="true" bottomline="true">
<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
\begin_inset Text

\layout Standard

id
\end_inset 
</cell>
<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
\begin_inset Text

\layout Standard

item
\end_inset 
</cell>
<cell alignment="center" valignment="top" topline="true" leftline="true" rightline="true" usebox="none">
\begin_inset Text

\layout Standard

val
\end_inset 
</cell>
</row>
<row topline="true">
<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
\begin_inset Text

\layout Standard

PPIDskBRH30AAGPtAsg
\end_inset 
</cell>
<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
\begin_inset Text

\layout Standard

Content-Type
\end_inset 
</cell>
<cell alignment="center" valignment="top" topline="true" leftline="true" rightline="true" usebox="none">
\begin_inset Text

\layout Standard

text/html
\end_inset 
</cell>
</row>
<row topline="true">
<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
\begin_inset Text

\layout Standard

PPIDskBRH30AAGPtAsg
\end_inset 
</cell>
<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
\begin_inset Text

\layout Standard

Accept-Encoding
\end_inset 
</cell>
<cell alignment="center" valignment="top" topline="true" leftline="true" rightline="true" usebox="none">
\begin_inset Text

\layout Standard

gzip, deflate
\end_inset 
</cell>
</row>
<row topline="true">
<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
\begin_inset Text

\layout Standard

PPIDskBRH30AAGPtAsg
\end_inset 
</cell>
<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
\begin_inset Text

\layout Standard

Expires
\end_inset 
</cell>
<cell alignment="center" valignment="top" topline="true" leftline="true" rightline="true" usebox="none">
\begin_inset Text

\layout Standard

Tue, 28 May 2002 10:00:18 GMT
\end_inset 
</cell>
</row>
<row topline="true" bottomline="true">
<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
\begin_inset Text

\layout Standard

PPIDskBRH30AAGPtAsg
\end_inset 
</cell>
<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
\begin_inset Text

\layout Standard

Cache-Control
\end_inset 
</cell>
<cell alignment="center" valignment="top" topline="true" leftline="true" rightline="true" usebox="none">
\begin_inset Text

\layout Standard

max-age=86400
\end_inset 
</cell>
</row>
</lyxtabular>

\end_inset 


\end_inset 


\layout Standard

We have a certain request, and its unique ID is 
\begin_inset Quotes eld
\end_inset 

PPIDskBRH30AAGPtAsg
\begin_inset Quotes erd
\end_inset 

.
 Within each separate table will be multiple entries with that request ID:
 several cookie entries, several header entries, etc.
 As you can see in tables 
\begin_inset LatexCommand \ref{tblAcc}

\end_inset 

, 
\begin_inset LatexCommand \ref{tblNotes}

\end_inset 

 and 
\begin_inset LatexCommand \ref{tblHdr}

\end_inset 

, you have a one-to-many relationship for request PPIDskBRH30AAGPtAsg: that
 one access has two associated notes and four associated headers.
 You can extract this data easily using the power of SQL's 
\begin_inset Quotes eld
\end_inset 

select
\begin_inset Quotes erd
\end_inset 

 statement and table joins.
 To see the notes associated with a particular request:
\layout LyX-Code

select a.remote_host, a.request_uri, n.item, n.val from access_log a, notes_log
 n
\layout LyX-Code

where a.id=n.id and a.id='PPIDskBRH30AAGPtAsg';
\layout LyX-Code

\layout Standard
\align center 

\begin_inset  Tabular
<lyxtabular version="3" rows="3" columns="4">
<features>
<column alignment="left" valignment="top" leftline="true" width="0pt">
<column alignment="left" valignment="top" leftline="true" width="0pt">
<column alignment="left" valignment="top" leftline="true" width="0pt">
<column alignment="left" valignment="top" leftline="true" rightline="true" width="0pt">
<row topline="true" bottomline="true">
<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
\begin_inset Text

\layout Standard

remote_host
\end_inset 
</cell>
<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
\begin_inset Text

\layout Standard

request_uri 
\end_inset 
</cell>
<cell alignment="center" valignment="top" topline="true" leftline="true" rightline="true" usebox="none">
\begin_inset Text

\layout Standard

item
\end_inset 
</cell>
<cell alignment="center" valignment="top" topline="true" leftline="true" rightline="true" usebox="none">
\begin_inset Text

\layout Standard

val
\end_inset 
</cell>
</row>
<row topline="true">
<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
\begin_inset Text

\layout Standard

zerberus.aiacs.net
\end_inset 
</cell>
<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
\begin_inset Text

\layout Standard

/mod_log_sql/index.html
\end_inset 
</cell>
<cell alignment="center" valignment="top" topline="true" leftline="true" rightline="true" usebox="none">
\begin_inset Text

\layout Standard

mod_gzip_result
\end_inset 
</cell>
<cell alignment="center" valignment="top" topline="true" leftline="true" rightline="true" usebox="none">
\begin_inset Text

\layout Standard

OK
\end_inset 
</cell>
</row>
<row topline="true" bottomline="true">
<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
\begin_inset Text

\layout Standard

zerberus.aiacs.net
\end_inset 
</cell>
<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
\begin_inset Text

\layout Standard

/mod_log_sql/index.html
\end_inset 
</cell>
<cell alignment="center" valignment="top" topline="true" leftline="true" rightline="true" usebox="none">
\begin_inset Text

\layout Standard

mod_gzip_compression_ratio
\end_inset 
</cell>
<cell alignment="center" valignment="top" topline="true" leftline="true" rightline="true" usebox="none">
\begin_inset Text

\layout Standard

69
\end_inset 
</cell>
</row>
</lyxtabular>

\end_inset 


\layout LyX-Code

\layout Standard

Naturally you can craft similar statements for the outboud headers, inbound
 headers and cookies, all of which can live in separate tables.
 Your statements are limited in power only by your skill with SQL.
\layout Standard

In order to use this capability of mod_log_sql, you must do several things:
\layout Itemize

Compile mod_unique_id into Apache (statically or as a DSO).
 mod_log_sql employs the unique request ID that mod_unique_id provides in
 order to key between the separate tables.
 You can still log the data without mod_unqiue_id, but it will be completely
 uncorrelated and you will have no way to discern any meaning.
\layout Itemize

Create the appropriate tables.
 This will be done for you if you permit mod_log_sql to create its own tables
 using 
\noun on 
LogSQLCreateTables On
\noun default 
, or if you use the enclosed 
\begin_inset Quotes eld
\end_inset 

create_tables.sql
\begin_inset Quotes erd
\end_inset 

 script.
\layout Itemize

Create a SQL index on the 
\begin_inset Quotes eld
\end_inset 

id
\begin_inset Quotes erd
\end_inset 

 column.
 Without this index, table joins will be deathly slow.
 I recommend you consult the MySQL documentation on the proper way to create
 a column index if you are not familiar with this operation.
\layout Itemize

Within each appropriate VirtualHost stanza, use the
\noun on 
 LogSQLWhich*
\noun default 
 and 
\noun on 
LogSQL*LogTable
\noun default 
 directives to tell the module what and where to log the data.
 In the following example, I have overridden the name for the notes table
 whereas I have left the other table names at their defaults.
 I have then specified the cookies, headers and notes that interest me.
 (And as you can see, these directives do not require me to add any characters
 to 
\noun on 
LogSQLTransferLogTable.)
\layout LyX-Code

<VirtualHost 216.231.36.128>
\layout LyX-Code

  (snip)
\layout LyX-Code

  LogSQLNotesLogTable notestable
\layout LyX-Code

  LogSQLWhichCookies bluecookie redcookie greencookie 
\layout LyX-Code

  LogSQLWhichNotes mod_gzip_result mod_gzip_compression_ratio
\layout LyX-Code

  LogSQLWhichHeadersOut Expires Content-Type Cache-Control 
\layout LyX-Code

  LogSQLWhichHeadersIn UserAgent Accept-Encoding Host
\layout LyX-Code

  (snip)
\layout LyX-Code

</VirtualHost>
\layout Subsubsection

Using the same database for production and test
\layout Standard

Although suboptimal, it is not uncommon to use the same backend database
 for the 
\begin_inset Quotes eld
\end_inset 

production
\begin_inset Quotes erd
\end_inset 

 webservers as well as the 
\begin_inset Quotes eld
\end_inset 

test
\begin_inset Quotes erd
\end_inset 

 webservers (budgetary constraints, rackspace limits, etc.).
 Furthermore, an administrator in this situation may be unable to use 
\noun on 
LogSQLRemhostIgnore 
\noun default 
to exclude requests from the test servers -- perhaps the generated entries
 are genuinely useful for analytical or QA purposes, but their value after
 analysis is minimal.
\layout Standard

It is wasteful and potentially confusing to permit this internal test data
 to clutter the database, and a solution to the problem is the proper use
 of the 
\noun on 
LogSQLMachineID 
\noun default 
directive.
 Assume a scenario where the production webservers have IDs like 
\begin_inset Quotes eld
\end_inset 

web01,
\begin_inset Quotes erd
\end_inset 

 
\begin_inset Quotes eld
\end_inset 

web02,
\begin_inset Quotes erd
\end_inset 

 and so on -- and the test webservers have IDs like 
\begin_inset Quotes eld
\end_inset 

test01,
\begin_inset Quotes erd
\end_inset 

 
\begin_inset Quotes eld
\end_inset 

test02,
\begin_inset Quotes erd
\end_inset 

 etc.
 Because entries in the log database are distinguished by their source machine,
 an administrator may purge unneeded test data from the access log as follows:
\layout LyX-Code

delete from access_log where machine_id like 'test%';
\layout Subsubsection


\begin_inset LatexCommand \label{sub:DelayedIns}

\end_inset 

Optimizing for a busy database
\layout Standard

A busy MySQL database will have SELECT statements running concurrently with
 INSERT and UPDATE statements.
 A long-running SELECT can in certain circumstances block INSERTs and therefore
 block mod_log_sql.
 A workaround is to compile mod_log_sql for 
\begin_inset Quotes eld
\end_inset 

delayed inserts,
\begin_inset Quotes erd
\end_inset 

 which are described as follows in the MySQL documentation:
\layout Quote

The DELAYED option for the INSERT statement is a MySQL-specific option that
 is very useful if you have clients that can't wait for the INSERT to complete.
 This is a common problem when you use MySQL for logging and you also periodical
ly run SELECT and UPDATE statements that take a long time to complete.
 DELAYED was introduced in MySQL Version 3.22.15.
 It is a MySQL extension to ANSI SQL92.
\layout Quote

INSERT DELAYED only works with ISAM and MyISAM tables.
 Note that as MyISAM tables supports concurrent SELECT and INSERT, if there
 is no free blocks in the middle of the data file, you very seldom need
 to use INSERT DELAYED with MyISAM.
 
\layout Quote

When you use INSERT DELAYED, the client will get an OK at once and the row
 will be inserted when the table is not in use by any other thread.
\layout Quote

Another major benefit of using INSERT DELAYED is that inserts from many
 clients are bundled together and written in one block.
 This is much faster than doing many separate inserts.
 
\layout Standard

The general disadvantages of delayed inserts are:
\layout Enumerate

The queued rows are only stored in memory until they are inserted into the
 table.
 If mysqld dies unexpectedly, any queued rows that weren't written to disk
 are lost.
\layout Enumerate

There is additional overhead for the server to handle a separate thread
 for each table on which you use INSERT DELAYED.
\layout Standard


\series bold 
The MySQL documentation concludes, 
\begin_inset Quotes eld
\end_inset 

This means that you should only use INSERT DELAYED when you are really sure
 you need it!
\begin_inset Quotes erd
\end_inset 

 Furthermore, the current state of error return from a failed INSERT DELAYED
 seems to be in flux, and may behave in unpredictable ways between different
 MySQL versions.
 See section 
\begin_inset LatexCommand \ref{sub:DelayedInsFAQ}

\end_inset 

 in the FAQ -- you have been warned.
\layout Standard

If you are experiencing issues which could be solved by delayed inserts,
 uncomment the #MYSQLDELAYED line in the Makefile by removing the # that
 is in front of it.
 Recompile and reinstall your module.
 All regular INSERT statements are now INSERT DELAYED, and you should see
 no more blocking of the module.
\layout Subsection


\begin_inset LatexCommand \label{sec:ConfRef}

\end_inset 

Configuration directive reference
\layout Standard

It is imperative that you understand which directives are used 
\emph on 
only once 
\emph default 
in the main server config, and which are used inside VirtualHost stanzas
 and therefore multiple times within httpd.conf.
 The 
\begin_inset Quotes eld
\end_inset 

context
\begin_inset Quotes srd
\end_inset 

 listed with each entry  informs you of this.
\layout Subsubsection

LogSQLCookieLogTable
\layout LyX-Code

Syntax: LogSQLCookieLogTable table-name 
\layout LyX-Code

Example: LogSQLCookieLogTable cookie_log
\layout LyX-Code

Default: cookies
\layout LyX-Code

Context: virtual host
\layout Standard

Defines which table is used for logging of cookies.
 Working in conjunction with 
\noun on 
LogSQLWhichCookies
\noun default 
, you can log many of each request's associated cookies to a separate table.
 For meaningful data retrieval the cookie table is keyed to the access table
 by the unique request ID supplied by the standard Apache module mod_unique_id.
\layout Standard

Note that you must create the table (see create-tables.sql, included in the
 package), or 
\noun on 
LogSQLCreateTables
\noun default 
 must be set to 
\begin_inset Quotes eld
\end_inset 

on
\begin_inset Quotes erd
\end_inset 

.
\layout Subsubsection

LogSQLCreateTables
\layout LyX-Code

Syntax: LogSQLCreateTables flag
\layout LyX-Code

Example: LogSQLCreateTables On 
\layout LyX-Code

Default: Off 
\layout LyX-Code

Context: main server config
\layout Standard

mod_log_sql 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 
\noun on 
LogSQLMassVirtualHosting
\noun default 
 directive).
\layout Standard

There is a slight disadvantage: if you wish to activate this feature, then
 the userid specified in 
\noun on 
LogSQLLoginInfo
\noun default 
 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 
\noun on 
LogSQLCreateTables
\noun default 
.
 But most people -- even the very security-conscious -- will find that granting
 CREATE on the logging database is reasonable.
\layout Standard

This is defined only once in the httpd.conf file.
\layout Subsubsection

LogSQLDatabase 
\layout LyX-Code


\series bold 
MANDATORY
\layout LyX-Code

Syntax: LogSQLDatabase database 
\layout LyX-Code

Example: LogSQLDatabase loggingdb 
\layout LyX-Code

Context: main server config
\layout Standard

Defines the database that is used for logging.
 
\begin_inset Quotes eld
\end_inset 

database
\begin_inset Quotes erd
\end_inset 

 must be a valid db on the MySQL host defined in 
\noun on 
LogSQLLoginInfo
\noun default 
.
 
\layout Standard

This is defined only once in the httpd.conf file.
\layout Subsubsection

LogSQLForcePreserve
\layout LyX-Code

Syntax: LogSQLForcePreserve Flag
\layout LyX-Code

Example: LogSQLPreserveFile on
\layout LyX-Code

Default: off
\layout LyX-Code

Context: main server config
\layout Standard

You may need to perform debugging on your database and specifically want
 mod_log_sql to make no attempts to log to it.
 This directive instructs the module to send all its log entries directly
 to the preserve file and to make no database INSERT attempts.
\layout Standard

This is presumably a directive for temporary use only; it could be dangerous
 if you set it and forget it, as all your entries will simply pile up in
 the preserve file.
\layout Standard

This is defined only once in the httpd.conf file.
\layout Subsubsection

LogSQLHeadersInLogTable
\layout LyX-Code

Syntax: LogSQLHeadersInLogTable table-name 
\layout LyX-Code

Example: LogSQLHeadersInLogTable headers
\layout LyX-Code

Default: headers_in
\layout LyX-Code

Context: virtual host
\layout Standard

Defines which table is used for logging of inbound headers.
 Working in conjunction with 
\noun on 
LogSQLWhichHeadersIn
\noun default 
, you can log many of each request's associated headers to a separate table.
 For meaningful data retrieval the headers table is keyed to the access
 table by the unique request ID supplied by the standard Apache module mod_uniqu
e_id.
\layout Standard

Note that you must create the table (see create-tables.sql, included in the
 package), or 
\noun on 
LogSQLCreateTables
\noun default 
 must be set to 
\begin_inset Quotes eld
\end_inset 

on
\begin_inset Quotes erd
\end_inset 

.
\layout Subsubsection

LogSQLHeadersOutLogTable
\layout LyX-Code

Syntax: LogSQLHeadersOutLogTable table-name 
\layout LyX-Code

Example: LogSQLHeadersOutLogTable headers
\layout LyX-Code

Default: headers_out
\layout LyX-Code

Context: virtual host
\layout Standard

Defines which table is used for logging of outbound headers.
 Working in conjunction with 
\noun on 
LogSQLWhichHeadersOut
\noun default 
, you can log many of each request's associated headers to a separate table.
 For meaningful data retrieval the headers table is keyed to the access
 table by the unique request ID supplied by the standard Apache module mod_uniqu
e_id.
\layout Standard

Note that you must create the table (see create-tables.sql, included in the
 package), or 
\noun on 
LogSQLCreateTables
\noun default 
 must be set to 
\begin_inset Quotes eld
\end_inset 

on
\begin_inset Quotes erd
\end_inset 

.
\layout Subsubsection

LogSQLLoginInfo 
\layout LyX-Code


\series bold 
MANDATORY
\series default 
 
\layout LyX-Code

Syntax: LogSQLLoginInfo host user password
\layout LyX-Code

Example: LogSQLLoginInfo foobar.baz.com logwriter passw0rd 
\layout LyX-Code

Context: main server config
\layout Standard

Defines the general parameters of the MySQL host to which you will be logging.
 
\begin_inset Quotes eld
\end_inset 

host
\begin_inset Quotes erd
\end_inset 

 is the hostname or IP address of the MySQL machine, and is simply 
\begin_inset Quotes eld
\end_inset 

localhost
\begin_inset Quotes erd
\end_inset 

 if the database lives on the same machine as Apache.
 
\begin_inset Quotes eld
\end_inset 

user
\begin_inset Quotes erd
\end_inset 

 is the MySQL userid (not a Unix userid!) with INSERT privileges on the
 table defined in 
\noun on 
LogSQLTransferLogTable
\noun default 
.
 
\begin_inset Quotes eld
\end_inset 

password
\begin_inset Quotes erd
\end_inset 

 is that user's password.
 
\layout Standard

This is defined only once in the httpd.conf file.
\layout Subsubsection

LogSQLMachineID
\layout LyX-Code

Syntax: LogSQLMachineID somename
\layout LyX-Code

Example: LogSQLMachineID web01
\layout LyX-Code

Context: main server config
\layout Standard

If you have a farm of webservers then you may wish to know which particular
 machine made each entry; this is useful for analyzing your loadbalancing
 methodology.
 
\noun on 
LogSQLMachineID
\noun default 
 permits you to distinguish each machine's entries if you assign each machine
 its own 
\noun on 
LogSQLMachineID
\noun default 
: for example, the first webserver gets 
\begin_inset Quotes eld
\end_inset 


\noun on 
LogSQLMachineID
\noun default 
 web01,
\begin_inset Quotes erd
\end_inset 

 the second gets 
\begin_inset Quotes eld
\end_inset 


\noun on 
LogSQLMachineID
\noun default 
 web02,
\begin_inset Quotes erd
\end_inset 

 etc.
\layout Standard

This is defined only once in the httpd.conf file.
\layout Subsubsection

LogSQLMassVirtualHosting
\layout LyX-Code

Syntax: LogSQLMassVirtualHosting flag 
\layout LyX-Code

Example: LogSQLMassVirtualHosting On 
\layout LyX-Code

Default: Off 
\layout LyX-Code

Context: main server config
\layout Standard

If you administer a site hosting many, many virtual hosts then this option
 will appeal to you.
 If you turn on 
\noun on 
LogSQLMassVirtualHosting
\noun default 
 then several things happen:
\layout Itemize

the on-the-fly table creation feature is activated automatically 
\layout Itemize

the transfer log table name is dynamically set from the virtual host's name
 after stripping out SQL-unfriendly characters (example: a virtual host
 www.grubbybaby.com gets logged to table access_www_grubbybaby_com) 
\layout Itemize

which, in turn, means that each virtual host logs to its own segregated
 table.
 Because there is no data shared between virtual servers you can grant your
 users access to the tables they need; they will be unable to view others'
 data.
\layout Standard

This is a huge boost in convenience for sites with many virtual servers.
 Activating 
\noun on 
LogSQLMassVirtualHosting
\noun default 
 obviates the need to create every virtual server's table and provides more
 granular security possibilities.
\layout Standard

You are advised to investigate the use of Apache's 
\noun on 
UseCanonicalName On 
\noun default 
directive with this directive in order to ensure that each virtual host
 maps to one table namespace.
\layout Standard

This is defined only once in the httpd.conf file.
 
\layout Subsubsection

LogSQLNotesLogTable
\layout LyX-Code

Syntax: LogSQLNotesLogTable table-name 
\layout LyX-Code

Example: LogSQLNotesLogTable notes_log
\layout LyX-Code

Default: notes
\layout LyX-Code

Context: virtual host 
\layout Standard

Defines which table is used for logging of notes.
 Working in conjunction with 
\noun on 
LogSQLWhichNotes
\noun default 
, you can log many of each request's associated notes to a separate table.
 For meaningful data retrieval the notes table is keyed to the access table
 by the unique request ID supplied by the standard Apache module mod_unique_id.
\layout Standard

Note that you must create the table (see create-tables.sql, included in the
 package), or 
\noun on 
LogSQLCreateTables
\noun default 
 must be set to ``on''.
 
\layout Subsubsection

LogSQLPreserveFile
\layout LyX-Code

Syntax: LogSQLPreserveFile filename 
\layout LyX-Code

Example: LogSQLPreserveFile offline-preserve 
\layout LyX-Code

Default: /tmp/sql-preserve
\layout LyX-Code

Context: virtual host
\layout Standard

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, which is done in a simple manner:
\layout LyX-Code

# mysql -uadminuser -p mydbname < /tmp/sql-preserve
\layout Standard

If you do not define 
\noun on 
LogSQLPreserveFile
\noun default 
 then all virtual servers will log to the same default preserve file (/tmp/sql-p
reserve).
 You can redefine this on a virtual-host basis in order to segregate your
 preserve files if you desire.
 Note that segregation is not usually necessary, as the SQL statements that
 are written to the preserve file already distinguish between different
 virtual hosts if you include the 'v' character in your 
\noun on 
LogSQLTransferLogFormat
\noun default 
 directive.
 It is only necessary to segregate preserve-files by virualhost if you also
 segregate access logs by virtualhost.
\layout Standard

The module will log to Apache's 
\noun on 
ErrorLog
\noun default 
 when it notices a database outage, and upon database return.
 You will therefore know when the preserve file is being used, although
 it is your responsibility to import the file.
\layout Standard

The file does not need to be created in advance.
 It is safe to remove or rename the file without interrupting Apache, as
 the module closes the filehandle immediately after completing the write.
 The file is created with the user & group ID of the running Apache process
 (e.g.
 'nobody' on many Linux distributions).
\layout Subsubsection

LogSQLRemhostIgnore
\layout LyX-Code

Syntax: LogSQLRemhostIgnore host1 host2 host3 ...
 hostN 
\layout LyX-Code

Example: LogSQLRemhostIgnore localnet.com 
\layout LyX-Code

Context: virtual host
\layout Standard

Lists a series of strings that, if present in the REMOTE_HOST, will cause
 that request to 
\series bold 
not
\series default 
 be logged.
 This directive is useful for cutting down on log clutter when you are certain
 that you want to ignore requests from certain hosts, such as your own internal
 network machines.
 See section 
\begin_inset LatexCommand \ref{sub:Ignore}

\end_inset 

 for some tips for using this directive.
\layout Standard

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().
 The comparison is case sensitive.
\layout Subsubsection

LogSQLRequestAccept
\layout LyX-Code

Syntax: LogSQLRequestAccept req1 req2 req3 ...
 reqN 
\layout LyX-Code

Example: LogSQLRequestAccept .html .php .jpg
\layout LyX-Code

Default: if not specified, all requests are 
\begin_inset Quotes eld
\end_inset 

accepted
\begin_inset Quotes erd
\end_inset 


\layout LyX-Code

Context: virtual host
\layout Standard

Lists a series of strings that, if present in the URI, will permit that
 request to be
\series bold 
 
\series default 
considered for logging (depending on additional filtering by the 
\begin_inset Quotes eld
\end_inset 

ignore
\begin_inset Quotes erd
\end_inset 

 directives).
 Any request that fails to match one of the 
\noun on 
LogSQLRequestAccept 
\noun default 
entries will be discarded.
\layout Standard

This directive is useful for cutting down on log clutter when you are certain
 that you only want to log certain kinds of requests, and just blanket-ignore
 everything else.
 See section 
\begin_inset LatexCommand \ref{sub:Ignore}

\end_inset 

 for some tips for using this directive.
\layout Standard

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().
 The comparison is case sensitive.
\layout Standard

This directive is completely optional.
 It is more general than 
\noun on 
LogSQLRequestIgnore 
\noun default 
and
\noun on 
 
\noun default 
is evaluated before
\noun on 
 LogSQLRequestIgnore
\noun default 
.
 If this directive is not used, 
\series bold 
all
\series default 
 requests are accepted and passed on to the other filtering directives.
 Therefore, only use this directive if you have a specific reason to do
 so.
\layout Subsubsection

LogSQLRequestIgnore
\layout LyX-Code

Syntax: LogSQLRequestIgnore req1 req2 req3 ...
 reqN 
\layout LyX-Code

Example: LogSQLRequestIgnore root.exe cmd.exe default.ida favicon.ico 
\layout LyX-Code

Context: virtual host
\layout Standard

Lists a series of strings that, if present in the URI, will cause that request
 to 
\series bold 
NOT
\series default 
 be
\series bold 
 
\series default 
logged.
 This directive is useful for cutting down on log clutter when you are certain
 that you want to ignore requests for certain objects.
 See section 
\begin_inset LatexCommand \ref{sub:Ignore}

\end_inset 

 for some tips for using this directive.
\layout Standard

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().
 The comparison is case sensitive.
\layout Subsubsection

LogSQLSocketFile 
\layout LyX-Code

Syntax: LogSQLSocketFile filename 
\layout LyX-Code

Example: LogSQLSocketFile /tmp/mysql.sock 
\layout LyX-Code

Default: /var/lib/mysql/mysql.sock 
\layout LyX-Code

Context: main server config
\layout Standard

At Apache runtime you can specify the MySQL socket file to use.
 Set this once in your main server config to override the default value.
 This value is irrelevant if your database resides on a separate machine.
\layout Standard

mod_log_sql will automatically employ the socket for db communications if
 the database resides on the local host.
 If the db resides on a separate host the module will automatically use
 TCP/IP.
 This is a function of the MySQL API and is not user-configurable.
\layout Standard

This is defined only once in the httpd.conf file.
\layout Subsubsection

LogSQLTCPPort
\layout LyX-Code

Syntax: LogSQLTCPPort portnumber
\layout LyX-Code

Example: LogSQLTCPPort 3309
\layout LyX-Code

Default: 3306
\layout LyX-Code

Context: main server config
\layout Standard

Your database may listen on a different port than the default.
 If so, use this directive to instruct the module which port to use.
 This directive only applies if the database is on a different machine connected
 via TCP/IP.
\layout Standard

This is defined only once in the httpd.conf file.
\layout Subsubsection


\begin_inset LatexCommand \label{sub:Frmat}

\end_inset 

LogSQLTransferLogFormat 
\layout LyX-Code

Syntax: LogSQLTransferLogFormat format-string 
\layout LyX-Code

Example: LogSQLTransferLogFormat huSUTv 
\layout LyX-Code

Default: AbHhmRSsTUuv 
\layout LyX-Code

Context: virtual host
\layout Standard

Each character in the format-string defines an attribute of the request
 that you wish to log.
 The default logs the information required to create Combined Log Format
 logs, plus several extras.
 Here is the full list of allowable keys, which sometimes resemble their
 Apache counterparts, but do not always:
\layout Quote


\size footnotesize 

\begin_inset  Tabular
<lyxtabular version="3" rows="22" columns="5">
<features>
<column alignment="center" valignment="top" leftline="true" rightline="true" width="0pt">
<column alignment="left" valignment="top" width="0pt">
<column alignment="left" valignment="top" leftline="true" rightline="true" width="0pt">
<column alignment="left" valignment="top" width="0pt">
<column alignment="left" valignment="top" leftline="true" rightline="true" width="0pt">
<row topline="true" bottomline="true" endhead="true">
<cell alignment="center" valignment="top" topline="true" bottomline="true" leftline="true" usebox="none">
\begin_inset Text

\layout Standard

\end_inset 
</cell>
<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
\begin_inset Text

\layout Standard


\series bold 
\size footnotesize 
What is this?
\end_inset 
</cell>
<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
\begin_inset Text

\layout Standard


\series bold 
\size footnotesize 
Data field
\end_inset 
</cell>
<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
\begin_inset Text

\layout Standard


\series bold 
\size footnotesize 
Column type
\end_inset 
</cell>
<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
\begin_inset Text

\layout Standard


\series bold 
\size footnotesize 
Example
\end_inset 
</cell>
</row>
<row topline="true">
<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
\begin_inset Text

\layout Standard


\size footnotesize 
A
\end_inset 
</cell>
<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
\begin_inset Text

\layout Standard


\size footnotesize 
User agent
\end_inset 
</cell>
<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
\begin_inset Text

\layout Standard


\size footnotesize 
agent
\end_inset 
</cell>
<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
\begin_inset Text

\layout Standard


\size footnotesize 
varchar(255)
\end_inset 
</cell>
<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
\begin_inset Text

\layout Standard


\size footnotesize 
Mozilla/4.0 (compat; MSIE 6.0; Windows)
\end_inset 
</cell>
</row>
<row topline="true">
<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
\begin_inset Text

\layout Standard

a
\end_inset 
</cell>
<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
\begin_inset Text

\layout Standard

CGI request arguments
\end_inset 
</cell>
<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
\begin_inset Text

\layout Standard

request_args
\end_inset 
</cell>
<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
\begin_inset Text

\layout Standard

varchar(255)
\end_inset 
</cell>
<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
\begin_inset Text

\layout Standard

user=Smith&cart=1231&item=532
\end_inset 
</cell>
</row>
<row topline="true">
<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
\begin_inset Text

\layout Standard


\size footnotesize 
b
\end_inset 
</cell>
<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
\begin_inset Text

\layout Standard


\size footnotesize 
Bytes transfered
\end_inset 
</cell>
<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
\begin_inset Text

\layout Standard


\size footnotesize 
bytes_sent
\end_inset 
</cell>
<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
\begin_inset Text

\layout Standard


\size footnotesize 
int unsigned
\end_inset 
</cell>
<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
\begin_inset Text

\layout Standard


\size footnotesize 
32561
\end_inset 
</cell>
</row>
<row topline="true">
<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
\begin_inset Text

\layout Standard


\size footnotesize 
c
\end_inset 
</cell>
<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
\begin_inset Text

\layout Standard


\size footnotesize 
Text of cookie 
\begin_inset Formula $^{\textrm{1}}$
\end_inset 


\end_inset 
</cell>
<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
\begin_inset Text

\layout Standard


\size footnotesize 
cookie
\end_inset 
</cell>
<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
\begin_inset Text

\layout Standard


\size footnotesize 
varchar(255)
\end_inset 
</cell>
<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
\begin_inset Text

\layout Standard


\size footnotesize 
Apache=sdyn.fooonline.net.1300102700823
\end_inset 
</cell>
</row>
<row topline="true">
<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
\begin_inset Text

\layout Standard


\size footnotesize 
H
\end_inset 
</cell>
<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
\begin_inset Text

\layout Standard


\size footnotesize 
HTTP request protocol
\end_inset 
</cell>
<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
\begin_inset Text

\layout Standard


\size footnotesize 
request_protocol
\end_inset 
</cell>
<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
\begin_inset Text

\layout Standard


\size footnotesize 
varchar(10)
\end_inset 
</cell>
<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
\begin_inset Text

\layout Standard


\size footnotesize 
HTTP/1.1
\end_inset 
</cell>
</row>
<row topline="true">
<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
\begin_inset Text

\layout Standard


\size footnotesize 
h
\end_inset 
</cell>
<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
\begin_inset Text

\layout Standard


\size footnotesize 
Name of remote host
\end_inset 
</cell>
<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
\begin_inset Text

\layout Standard


\size footnotesize 
remote_host
\end_inset 
</cell>
<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
\begin_inset Text

\layout Standard


\size footnotesize 
varchar(50)
\end_inset 
</cell>
<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
\begin_inset Text

\layout Standard


\size footnotesize 
blah.foobar.com
\end_inset 
</cell>
</row>
<row topline="true">
<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
\begin_inset Text

\layout Standard


\size footnotesize 
I
\end_inset 
</cell>
<cell alignment="left" valignment="top" topline="true" leftline="true" usebox="none">
\begin_inset Text

\layout Standard


\size footnotesize 
Request ID (from mod_unique_id)
\end_inset 
</cell>
<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
\begin_inset Text

\layout Standard


\size footnotesize 
id
\end_inset 
</cell>
<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
\begin_inset Text

\layout Standard


\size footnotesize 
char(19)
\end_inset 
</cell>
<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
\begin_inset Text

\layout Standard


\size footnotesize 
POlFcUBRH30AAALdBG8
\end_inset 
</cell>
</row>
<row topline="true">
<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
\begin_inset Text

\layout Standard


\size footnotesize 
l
\end_inset 
</cell>
<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
\begin_inset Text

\layout Standard


\size footnotesize 
Ident user info
\end_inset 
</cell>
<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
\begin_inset Text

\layout Standard


\size footnotesize 
remote_logname
\end_inset 
</cell>
<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
\begin_inset Text

\layout Standard


\size footnotesize 
varchar(50)
\end_inset 
</cell>
<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
\begin_inset Text

\layout Standard


\size footnotesize 
bobby
\end_inset 
</cell>
</row>
<row topline="true">
<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
\begin_inset Text

\layout Standard


\size footnotesize 
M
\end_inset 
</cell>
<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
\begin_inset Text

\layout Standard


\size footnotesize 
Machine ID 
\begin_inset Formula $^{\textrm{2}}$
\end_inset 


\end_inset 
</cell>
<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
\begin_inset Text

\layout Standard


\size footnotesize 
machine_id
\end_inset 
</cell>
<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
\begin_inset Text

\layout Standard


\size footnotesize 
varchar(25)
\end_inset 
</cell>
<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
\begin_inset Text

\layout Standard


\size footnotesize 
web01
\end_inset 
</cell>
</row>
<row topline="true">
<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
\begin_inset Text

\layout Standard


\size footnotesize 
m
\end_inset 
</cell>
<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
\begin_inset Text

\layout Standard


\size footnotesize 
HTTP request method
\end_inset 
</cell>
<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
\begin_inset Text

\layout Standard


\size footnotesize 
request_method
\end_inset 
</cell>
<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
\begin_inset Text

\layout Standard


\size footnotesize 
varchar(6)
\end_inset 
</cell>
<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
\begin_inset Text

\layout Standard


\size footnotesize 
GET
\end_inset 
</cell>
</row>
<row topline="true">
<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
\begin_inset Text

\layout Standard


\size footnotesize 
P
\end_inset 
</cell>
<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
\begin_inset Text

\layout Standard


\size footnotesize 
httpd child PID
\end_inset 
</cell>
<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
\begin_inset Text

\layout Standard


\size footnotesize 
child_pid
\end_inset 
</cell>
<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
\begin_inset Text

\layout Standard


\size footnotesize 
smallint unsigned
\end_inset 
</cell>
<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
\begin_inset Text

\layout Standard


\size footnotesize 
3215
\end_inset 
</cell>
</row>
<row topline="true">
<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
\begin_inset Text

\layout Standard


\size footnotesize 
p
\end_inset 
</cell>
<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
\begin_inset Text

\layout Standard


\size footnotesize 
httpd port
\end_inset 
</cell>
<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
\begin_inset Text

\layout Standard


\size footnotesize 
server_port
\end_inset 
</cell>
<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
\begin_inset Text

\layout Standard


\size footnotesize 
smallint unsigned
\end_inset 
</cell>
<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
\begin_inset Text

\layout Standard


\size footnotesize 
80
\end_inset 
</cell>
</row>
<row topline="true">
<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
\begin_inset Text

\layout Standard


\size footnotesize 
R
\end_inset 
</cell>
<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
\begin_inset Text

\layout Standard


\size footnotesize 
Referer
\end_inset 
</cell>
<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
\begin_inset Text

\layout Standard


\size footnotesize 
referer
\end_inset 
</cell>
<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
\begin_inset Text

\layout Standard


\size footnotesize 
varchar(255)
\end_inset 
</cell>
<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
\begin_inset Text

\layout Standard


\size footnotesize 
http://www.biglinks4u.com/linkpage.html
\end_inset 
</cell>
</row>
<row topline="true">
<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
\begin_inset Text

\layout Standard


\size footnotesize 
r
\end_inset 
</cell>
<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
\begin_inset Text

\layout Standard


\size footnotesize 
Request in full form
\end_inset 
</cell>
<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
\begin_inset Text

\layout Standard


\size footnotesize 
request_line
\end_inset 
</cell>
<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
\begin_inset Text

\layout Standard


\size footnotesize 
varchar(255)
\end_inset 
</cell>
<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
\begin_inset Text

\layout Standard


\size footnotesize 
GET /books-cycroad.html HTTP/1.1
\end_inset 
</cell>
</row>
<row topline="true">
<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
\begin_inset Text

\layout Standard


\size footnotesize 
S
\end_inset 
</cell>
<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
\begin_inset Text

\layout Standard


\size footnotesize 
Time of request in UNIX format
\end_inset 
</cell>
<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
\begin_inset Text

\layout Standard


\size footnotesize 
time_stamp
\end_inset 
</cell>
<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
\begin_inset Text

\layout Standard


\size footnotesize 
int unsigned
\end_inset 
</cell>
<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
\begin_inset Text

\layout Standard


\size footnotesize 
1005598029
\end_inset 
</cell>
</row>
<row topline="true">
<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
\begin_inset Text

\layout Standard


\size footnotesize 
s
\end_inset 
</cell>
<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
\begin_inset Text

\layout Standard


\size footnotesize 
HTTP status of request
\end_inset 
</cell>
<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
\begin_inset Text

\layout Standard


\size footnotesize 
status
\end_inset 
</cell>
<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
\begin_inset Text

\layout Standard


\size footnotesize 
smallint unsigned
\end_inset 
</cell>
<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
\begin_inset Text

\layout Standard


\size footnotesize 
404
\end_inset 
</cell>
</row>
<row topline="true">
<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
\begin_inset Text

\layout Standard


\size footnotesize 
T
\end_inset 
</cell>
<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
\begin_inset Text

\layout Standard


\size footnotesize 
Seconds to service request
\end_inset 
</cell>
<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
\begin_inset Text

\layout Standard


\size footnotesize 
request_duration
\end_inset 
</cell>
<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
\begin_inset Text

\layout Standard


\size footnotesize 
smallint unsigned
\end_inset 
</cell>
<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
\begin_inset Text

\layout Standard


\size footnotesize 
2
\end_inset 
</cell>
</row>
<row topline="true">
<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
\begin_inset Text

\layout Standard


\size footnotesize 
t
\end_inset 
</cell>
<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
\begin_inset Text

\layout Standard


\size footnotesize 
Time of request in human format
\end_inset 
</cell>
<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
\begin_inset Text

\layout Standard


\size footnotesize 
request_time
\end_inset 
</cell>
<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
\begin_inset Text

\layout Standard


\size footnotesize 
char(28)
\end_inset 
</cell>
<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
\begin_inset Text

\layout Standard


\size footnotesize 
[02/Dec/2001:15:01:26 -0800]
\end_inset 
</cell>
</row>
<row topline="true">
<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
\begin_inset Text

\layout Standard


\size footnotesize 
U
\end_inset 
</cell>
<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
\begin_inset Text

\layout Standard


\size footnotesize 
Request in simple form
\end_inset 
</cell>
<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
\begin_inset Text

\layout Standard


\size footnotesize 
request_uri
\end_inset 
</cell>
<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
\begin_inset Text

\layout Standard


\size footnotesize 
varchar(255)
\end_inset 
</cell>
<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
\begin_inset Text

\layout Standard


\size footnotesize 
/books-cycroad.html
\end_inset 
</cell>
</row>
<row topline="true">
<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
\begin_inset Text

\layout Standard


\size footnotesize 
u
\end_inset 
</cell>
<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
\begin_inset Text

\layout Standard


\size footnotesize 
User info from HTTP auth
\end_inset 
</cell>
<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
\begin_inset Text

\layout Standard


\size footnotesize 
remote_user
\end_inset 
</cell>
<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
\begin_inset Text

\layout Standard


\size footnotesize 
varchar(50)
\end_inset 
</cell>
<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
\begin_inset Text

\layout Standard


\size footnotesize 
bobby
\end_inset 
</cell>
</row>
<row topline="true" bottomline="true">
<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
\begin_inset Text

\layout Standard


\size footnotesize 
v
\end_inset 
</cell>
<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
\begin_inset Text

\layout Standard


\size footnotesize 
Virtual host servicing the request
\end_inset 
</cell>
<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
\begin_inset Text

\layout Standard


\size footnotesize 
virtual_host
\end_inset 
</cell>
<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
\begin_inset Text

\layout Standard


\size footnotesize 
varchar(50)
\end_inset 
</cell>
<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
\begin_inset Text

\layout Standard


\size footnotesize 
www.foobar.com
\end_inset 
</cell>
</row>
</lyxtabular>

\end_inset 


\layout Quote


\begin_inset Formula $^{\textrm{1}}$
\end_inset 

 You must also specify 
\noun on 
LogSQLWhichCookie 
\noun default 
for this to take effect.
\layout Quote


\begin_inset Formula $^{\textrm{2}}$
\end_inset 

 You must also specify 
\noun on 
LogSQLMachineID
\noun default 
 for this to take effect.
\layout Standard

If you have compiled mod_log_sql with SSL logging capability, you also can
 use these:
\layout Quote


\begin_inset  Tabular
<lyxtabular version="3" rows="4" columns="5">
<features>
<column alignment="center" valignment="top" leftline="true" width="0pt">
<column alignment="left" valignment="top" leftline="true" width="0pt">
<column alignment="left" valignment="top" leftline="true" width="0pt">
<column alignment="left" valignment="top" leftline="true" width="0pt">
<column alignment="left" valignment="top" leftline="true" rightline="true" width="0pt">
<row topline="true" bottomline="true">
<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
\begin_inset Text

\layout Standard

\end_inset 
</cell>
<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
\begin_inset Text

\layout Standard


\series bold 
What is this?
\end_inset 
</cell>
<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
\begin_inset Text

\layout Standard


\series bold 
Data field
\end_inset 
</cell>
<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
\begin_inset Text

\layout Standard


\series bold 
Column Type
\end_inset 
</cell>
<cell alignment="center" valignment="top" topline="true" leftline="true" rightline="true" usebox="none">
\begin_inset Text

\layout Standard


\series bold 
Example
\end_inset 
</cell>
</row>
<row topline="true">
<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
\begin_inset Text

\layout Standard

z
\end_inset 
</cell>
<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
\begin_inset Text

\layout Standard

SSL cipher used
\end_inset 
</cell>
<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
\begin_inset Text

\layout Standard

ssl_cipher
\end_inset 
</cell>
<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
\begin_inset Text

\layout Standard

varchar(25)
\end_inset 
</cell>
<cell alignment="center" valignment="top" topline="true" leftline="true" rightline="true" usebox="none">
\begin_inset Text

\layout Standard

RC4-MD5
\end_inset 
</cell>
</row>
<row topline="true">
<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
\begin_inset Text

\layout Standard

q
\end_inset 
</cell>
<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
\begin_inset Text

\layout Standard

Keysize of the SSL connection
\end_inset 
</cell>
<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
\begin_inset Text

\layout Standard

ssl_keysize
\end_inset 
</cell>
<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
\begin_inset Text

\layout Standard

smallint unsigned
\end_inset 
</cell>
<cell alignment="center" valignment="top" topline="true" leftline="true" rightline="true" usebox="none">
\begin_inset Text

\layout Standard

56
\end_inset 
</cell>
</row>
<row topline="true" bottomline="true">
<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
\begin_inset Text

\layout Standard

Q
\end_inset 
</cell>
<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
\begin_inset Text

\layout Standard

Maximum keysize supported
\end_inset 
</cell>
<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
\begin_inset Text

\layout Standard

ssl_maxkeysize
\end_inset 
</cell>
<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
\begin_inset Text

\layout Standard

smallint unsigned
\end_inset 
</cell>
<cell alignment="center" valignment="top" topline="true" leftline="true" rightline="true" usebox="none">
\begin_inset Text

\layout Standard

128
\end_inset 
</cell>
</row>
</lyxtabular>

\end_inset 


\layout Subsubsection

LogSQLTransferLogTable
\layout LyX-Code


\series bold 
MANDATORY (unless 
\noun on 
LogSQLMassVirtualHosting
\noun default 
 is 
\begin_inset Quotes eld
\end_inset 

on
\begin_inset Quotes erd
\end_inset 

)
\layout LyX-Code

Syntax: LogSQLTransferLogTable table-name 
\layout LyX-Code

Example: LogSQLTransferLogTable access_log_table 
\layout LyX-Code

Context: virtual host
\layout Standard

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 
\noun on 
LogSQLDatabase
\noun default 
.
\layout Standard

This directive is not necessary if you declare 
\noun on 
LogSQLMassVirtualHosting On
\noun default 
, since that directive activates dynamically-named tables.
 If you attempt to use 
\noun on 
LogSQLTransferLogTable
\noun default 
 at the same time a warning will be logged and it will be ignored, since
 
\noun on 
LogSQLMassVirtualHosting
\noun default 
 takes priority.
\layout Subsubsection

LogSQLWhichCookie
\layout LyX-Code

Syntax: LogSQLWhichCookie cookiename 
\layout LyX-Code

Example: LogSQLWhichCookie Clicks
\layout LyX-Code

Default: None
\layout LyX-Code

Context: virtual host
\layout Standard

In HTTP, cookies have names to distinguish them from each other.
 Using mod_usertrack, for example, you can give your user-tracking cookies
 a name with the CookieName directive.
 
\layout Standard

mod_log_sql allows you to log cookie information.

\noun on 
 LogSQLWhichCookie
\noun default 
 tells mod_log_sql which cookie to log.
 This is necessary because you will usually be setting and receiving more
 than one cookie from a client.
\layout Standard

You must include a 'c' character in 
\noun on 
LogSQLTransferLogFormat
\noun default 
 for this directive to take effect.
\layout Standard

Note: although this was origintally intended for people using mod_usertrack
 to create user-tracking cookies, you aren't restricted in any way.
 You can choose which cookie you wish to log to the database -- any cookie
 at all -- and it doesn't necessarily have to have anything to do with mod_usert
rack.
\layout Subsubsection

LogSQLWhichCookies
\layout LyX-Code

Syntax: LogSQLWhichCookies cookie1 cookie2 ...
 cookieN
\layout LyX-Code

Example: LogSQLWhichCookies userlogin foobar foobaz
\layout LyX-Code

Default: None
\layout LyX-Code

Context: virtual host
\layout Standard

Defines the list of cookies you would like logged.
 This works in conjunction with 
\noun on 
LogSQLCookieLogTable
\noun default 
.
 This directive does not require any additional characters to be added to
 the 
\noun on 
LogSQLTransferLogFormat
\noun default 
 string.
 The feature is activated simply by including this directive, upon which
 you will begin populating the separate cookie table with data.
\layout Standard

Note that you must have already created the table (see create-tables.sql,
 included in the package), or 
\noun on 
LogSQLCreateTables
\noun default 
 must be set to 
\begin_inset Quotes eld
\end_inset 

on
\begin_inset Quotes erd
\end_inset 

.
\layout Subsubsection

LogSQLWhichHeadersIn
\layout LyX-Code

Syntax: LogSQLWhichHeadersIn item1 item2 ...
 itemN
\layout LyX-Code

Example: LogSQLWhichHeadersIn UserAgent Accept-Encoding Host
\layout LyX-Code

Default: None
\layout LyX-Code

Context: virtual host
\layout Standard

Defines the list of inbound headers you would like logged.
 This works in conjunction with
\noun on 
 LogSQLHeadersInLogTable
\noun default 
.
 This directive does not require any additional characters to be added to
 the 
\noun on 
LogSQLTransferLogFormat
\noun default 
 string.
 The feature is activated simply by including this directive, upon which
 you will begin populating the separate inbound-headers table with data.
\layout Standard

Note that you must have already created the table (see create-tables.sql,
 included in the package), or 
\noun on 
LogSQLCreateTables
\noun default 
 must be set to 
\begin_inset Quotes eld
\end_inset 

on
\begin_inset Quotes erd
\end_inset 

.
\layout Subsubsection

LogSQLWhichHeadersOut
\layout LyX-Code

Syntax: LogSQLWhichHeadersOut item1 item2 ...
 itemN
\layout LyX-Code

Example: LogSQLWhichHeadersOut Expires Content-Type Cache-Control
\layout LyX-Code

Default: None
\layout LyX-Code

Context: virtual host
\layout Standard

Defines the list of outbound headers you would like logged.
 This works in conjunction with 
\noun on 
LogSQLHeadersOutLogTable
\noun default 
.
 This directive does not require any additional characters to be added to
 the 
\noun on 
LogSQLTransferLogFormat
\noun default 
 string.
 The feature is activated simply by including this directive, upon which
 you will begin populating the separate outbound-headers table with data.
\layout Standard

Note that you must have already created the table (see create-tables.sql,
 included in the package), or 
\noun on 
LogSQLCreateTables
\noun default 
 must be set to 
\begin_inset Quotes eld
\end_inset 

on
\begin_inset Quotes erd
\end_inset 

.
\layout Subsubsection

LogSQLWhichNotes
\layout LyX-Code

Syntax: LogSQLWhichNotes item1 item2 ...
 itemN
\layout LyX-Code

Example: LogSQLWhichNotes mod_gzip_result mod_gzip_compression_ratio
\layout LyX-Code

Default: None
\layout LyX-Code

Context: virtual host
\layout Standard

Defines the list of notes you would like logged.
 This works in conjunction with 
\noun on 
LogSQLNotesLogTable
\noun default 
.
 This directive does not require any additional characters to be added to
 the 
\noun on 
LogSQLTransferLogFormat
\noun default 
 string.
 The feature is activated simply by including this directive, upon which
 you will begin populating the separate notes table with data.
\layout Standard

Note that you must have already created the table (see create-tables.sql,
 included in the package), or 
\noun on 
LogSQLCreateTables
\noun default 
 must be set to 
\begin_inset Quotes eld
\end_inset 

on
\begin_inset Quotes erd
\end_inset 

.
\layout Section

FAQ
\layout Subsection

General module questions
\layout Subsubsection


\begin_inset LatexCommand \label{sub:why}

\end_inset 

Why log to an SQL database?
\layout Standard

To begin with, let's get it out of the way: logging to a database is not
 a panacea.
 But while there are complexities with this solution, the benefit can be
 substantial for certain classes of administrator or people with advanced
 requirements:
\layout Itemize

Chores like log rotation go away, as you can DELETE records from the SQL
 database once they are no longer useful.
 For example, the excellent and popular log-analysis tool 
\begin_inset LatexCommand \url[Webalizer]{(http://www.webalizer.com)}

\end_inset 

 does not need historic logs after it has processed them, enabling you to
 delete older logs.
\layout Itemize

People with clusters of web servers (for high availability) will benefit
 the most -- all their webservers can log to a single SQL database.
 This obviates the need to collate/interleave the many separate logfiles,
 which can be / highly/ problematic.
 
\layout Itemize

People acquainted with the power of SQL SELECT statements will know the
 flexibility of the extraction possibilities at their fingertips.
\layout Standard

For example, do you want to see all your 404's? Do this:
\layout LyX-Code

select remote_host,status,request_uri,bytes_sent,from_unixtime(time_stamp)
\layout LyX-Code

from acc_log_tbl where status=404 order by time_stamp;
\layout LyX-Code

\layout Standard
\align center 

\begin_inset  Tabular
<lyxtabular version="3" rows="5" columns="5">
<features>
<column alignment="left" valignment="top" leftline="true" width="0pt">
<column alignment="left" valignment="top" leftline="true" width="0pt">
<column alignment="left" valignment="top" leftline="true" width="0pt">
<column alignment="left" valignment="top" leftline="true" width="0pt">
<column alignment="left" valignment="top" leftline="true" rightline="true" width="0pt">
<row topline="true" bottomline="true">
<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
\begin_inset Text

\layout Standard

remote_host
\end_inset 
</cell>
<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
\begin_inset Text

\layout Standard

status
\end_inset 
</cell>
<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
\begin_inset Text

\layout Standard

request_uri
\end_inset 
</cell>
<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
\begin_inset Text

\layout Standard

bytes_sent
\end_inset 
</cell>
<cell alignment="center" valignment="top" topline="true" leftline="true" rightline="true" usebox="none">
\begin_inset Text

\layout Standard

from_unixtime(time_stamp)
\end_inset 
</cell>
</row>
<row topline="true">
<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
\begin_inset Text

\layout Standard

marge.mmm.co.uk
\end_inset 
</cell>
<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
\begin_inset Text

\layout Standard

404
\end_inset 
</cell>
<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
\begin_inset Text

\layout Standard

/favicon.ico
\end_inset 
</cell>
<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
\begin_inset Text

\layout Standard

321
\end_inset 
</cell>
<cell alignment="center" valignment="top" topline="true" leftline="true" rightline="true" usebox="none">
\begin_inset Text

\layout Standard

2001-11-20 02:30:56
\end_inset 
</cell>
</row>
<row topline="true">
<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
\begin_inset Text

\layout Standard

62.180.239.251
\end_inset 
</cell>
<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
\begin_inset Text

\layout Standard

404
\end_inset 
</cell>
<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
\begin_inset Text

\layout Standard

/favicon.ico
\end_inset 
</cell>
<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
\begin_inset Text

\layout Standard

333
\end_inset 
</cell>
<cell alignment="center" valignment="top" topline="true" leftline="true" rightline="true" usebox="none">
\begin_inset Text

\layout Standard

2001-11-20 02:45:25
\end_inset 
</cell>
</row>
<row topline="true">
<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
\begin_inset Text

\layout Standard

212.234.12.66
\end_inset 
</cell>
<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
\begin_inset Text

\layout Standard

404
\end_inset 
</cell>
<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
\begin_inset Text

\layout Standard

/favicon.ico
\end_inset 
</cell>
<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
\begin_inset Text

\layout Standard

321
\end_inset 
</cell>
<cell alignment="center" valignment="top" topline="true" leftline="true" rightline="true" usebox="none">
\begin_inset Text

\layout Standard

2001-11-20 03:01:00
\end_inset 
</cell>
</row>
<row topline="true" bottomline="true">
<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
\begin_inset Text

\layout Standard

212.210.78.254
\end_inset 
</cell>
<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
\begin_inset Text

\layout Standard

404
\end_inset 
</cell>
<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
\begin_inset Text

\layout Standard

/favicon.ico
\end_inset 
</cell>
<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
\begin_inset Text

\layout Standard

333
\end_inset 
</cell>
<cell alignment="center" valignment="top" topline="true" leftline="true" rightline="true" usebox="none">
\begin_inset Text

\layout Standard

2001-11-20 03:26:05
\end_inset 
</cell>
</row>
</lyxtabular>

\end_inset 


\layout LyX-Code

\layout Standard

Or do you want to see how many bytes you've sent within a certain directory
 or site? Do this:
\layout LyX-Code

select request_uri,sum(bytes_sent) as bytes,count(request_uri) as howmany
 from
\layout LyX-Code

acc_log_tbl where request_uri like '%mod_log_sql%' group by request_uri
 order
\layout LyX-Code

by howmany desc; 
\layout LyX-Code

\layout Standard
\align center 

\begin_inset  Tabular
<lyxtabular version="3" rows="5" columns="3">
<features>
<column alignment="left" valignment="top" leftline="true" width="0pt">
<column alignment="left" valignment="top" leftline="true" width="0pt">
<column alignment="left" valignment="top" leftline="true" rightline="true" width="0pt">
<row topline="true" bottomline="true">
<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
\begin_inset Text

\layout Standard

request_uri 
\end_inset 
</cell>
<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
\begin_inset Text

\layout Standard

bytes
\end_inset 
</cell>
<cell alignment="center" valignment="top" topline="true" leftline="true" rightline="true" usebox="none">
\begin_inset Text

\layout Standard

howmany
\end_inset 
</cell>
</row>
<row topline="true">
<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
\begin_inset Text

\layout Standard

/mod_log_sql/style_1.css
\end_inset 
</cell>
<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
\begin_inset Text

\layout Standard

157396 
\end_inset 
</cell>
<cell alignment="center" valignment="top" topline="true" leftline="true" rightline="true" usebox="none">
\begin_inset Text

\layout Standard

1288
\end_inset 
</cell>
</row>
<row topline="true">
<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
\begin_inset Text

\layout Standard

/mod_log_sql/
\end_inset 
</cell>
<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
\begin_inset Text

\layout Standard

2514337
\end_inset 
</cell>
<cell alignment="center" valignment="top" topline="true" leftline="true" rightline="true" usebox="none">
\begin_inset Text

\layout Standard

801
\end_inset 
</cell>
</row>
<row topline="true">
<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
\begin_inset Text

\layout Standard

/mod_log_sql/mod_log_sql.tar.gz
\end_inset 
</cell>
<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
\begin_inset Text

\layout Standard

9769312
\end_inset 
</cell>
<cell alignment="center" valignment="top" topline="true" leftline="true" rightline="true" usebox="none">
\begin_inset Text

\layout Standard

456 
\end_inset 
</cell>
</row>
<row topline="true" bottomline="true">
<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
\begin_inset Text

\layout Standard

/mod_log_sql/faq.html
\end_inset 
</cell>
<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
\begin_inset Text

\layout Standard

5038728
\end_inset 
</cell>
<cell alignment="center" valignment="top" topline="true" leftline="true" rightline="true" usebox="none">
\begin_inset Text

\layout Standard

436
\end_inset 
</cell>
</row>
</lyxtabular>

\end_inset 


\layout LyX-Code

\layout Standard

Or maybe you want to see who's linking to you? Do this:
\layout LyX-Code

select count(referer) as num,referer from acc_log_tbl where
\layout LyX-Code

request_uri='/mod_log_sql/' group by referer order by num desc;
\layout LyX-Code

\layout Standard
\align center 

\begin_inset  Tabular
<lyxtabular version="3" rows="5" columns="2">
<features>
<column alignment="left" valignment="top" leftline="true" width="0pt">
<column alignment="left" valignment="top" leftline="true" rightline="true" width="0pt">
<row topline="true" bottomline="true">
<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
\begin_inset Text

\layout Standard

num
\end_inset 
</cell>
<cell alignment="center" valignment="top" topline="true" leftline="true" rightline="true" usebox="none">
\begin_inset Text

\layout Standard

referer
\end_inset 
</cell>
</row>
<row topline="true">
<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
\begin_inset Text

\layout Standard

271
\end_inset 
</cell>
<cell alignment="center" valignment="top" topline="true" leftline="true" rightline="true" usebox="none">
\begin_inset Text

\layout Standard

http://freshmeat.net/projects/mod_log_sql/
\end_inset 
</cell>
</row>
<row topline="true">
<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
\begin_inset Text

\layout Standard

96
\end_inset 
</cell>
<cell alignment="center" valignment="top" topline="true" leftline="true" rightline="true" usebox="none">
\begin_inset Text

\layout Standard

http://modules.apache.org/search?id=339 
\end_inset 
</cell>
</row>
<row topline="true">
<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
\begin_inset Text

\layout Standard

48
\end_inset 
</cell>
<cell alignment="center" valignment="top" topline="true" leftline="true" rightline="true" usebox="none">
\begin_inset Text

\layout Standard

http://freshmeat.net/
\end_inset 
</cell>
</row>
<row topline="true" bottomline="true">
<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
\begin_inset Text

\layout Standard

8
\end_inset 
</cell>
<cell alignment="center" valignment="top" topline="true" leftline="true" rightline="true" usebox="none">
\begin_inset Text

\layout Standard

http://freshmeat.net    
\end_inset 
</cell>
</row>
</lyxtabular>

\end_inset 


\layout LyX-Code

\layout Standard

As you can see, there are myriad possibilities that can be constructed with
 the wonderful SQL SELECT statement.
 Logging to an SQL database can be really quite useful!
\layout Subsubsection

Why use MySQL? Are there alternatives?
\layout Standard

MySQL is a robust, free, and very powerful production-quality database engine.
 It is well supported and comes with detailed documentation.
 Many 3rd-party software pacakges (e.g.
 Slashcode, the engine that powers Slashdot) run exclusively with MySQL.
 In other words, you will belong to a very robust and well-supported community
 by choosing MySQL.
\layout Standard

That being said, there are alternatives.
 PostgreSQL is probably MySQL's leading "competitor" in the free database
 world.
 There is also an excellent module available for Apache to permit logging
 to a PostgreSQL database, called 
\begin_inset LatexCommand \url[pgLOGd]{(http://www.digitalstratum.com/pglogd/)}

\end_inset 

.
\layout Subsubsection

Is this code production-ready?
\layout Standard

By all accounts it is.
 It is known to work without a problem on many-thousands-of-hits-per-day
 webservers.
 Does that mean it is 100% bug free? Well, no software is.
 But it is well-tested and believed to be fully compatible with production
 environments.
 (The usual disclaimers apply.
 This software is provided without warranty of any kind.)
\layout Subsubsection

Who's using mod_log_sql?
\layout Standard

Good question! It would be great to find out! If you are a production-level
 mod_log_sql user, please contact 
\begin_inset LatexCommand \url[the maintainer, Chris Powell]{(chris@grubbybaby.com)}

\end_inset 

 so that you can be mentioned here.
\layout Subsubsection

Why doesn't the module also replace the Apache ErrorLog?
\layout Standard

There are circumstances when that would be quite unwise -- for example,
 if Apache could not reach the MySQL server for some reason and needed to
 log that fact.
 Without a text-based error log you'd never know anything was wrong, because
 Apache would be trying to log a database connection error to the database...
 you get the point.
\layout Standard

Error logs are usually not very high-traffic and are really best left as
 text files on a web server machine.
\layout Subsubsection

Does mod_log_sql work with Apache 2.x?
\layout Standard

As of this writing, no.
 The Apache Group significantly altered the module API with the release
 of Apache 2.0.
 All modules written for 1.3, including mod_log_sql, will not work with 2.0.
\layout Standard

mod_log_sql will eventually be ported to Apache 2.x, but not immediately.
 It is going to take some time, and there are other features that have higher
 priority.
 Please sign up for the announcements list (on the main website) or monitor
 the website for updates to learn when the port (and other releases) are
 available.
\layout Standard

<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>
\layout Subsubsection

Does mod_log_sql connect to MySQL via TCP/IP or a socket?
\layout Standard

It depends! This is not 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.
 How it works:
\layout Itemize

if the specified MySQL database is on the same machine, the connection command
 uses a socket to communicate with MySQL
\layout Itemize

if the specified MySQL database is on a different machine, mod_log_sql connects
 using TCP/IP.
 
\layout Standard

You don't have any control of which methodology is used.
 You can fine-tune some of the configuration, however.
 The 
\noun on 
LogSQLSocketFile
\noun default 
 runtime configuration directive overrides the default of 
\begin_inset Quotes eld
\end_inset 

/var/lib/mysql/mysql.sock
\begin_inset Quotes erd
\end_inset 

 for socket-based connections, whereas the 
\noun on 
LogSQLTCPPort
\noun default 
 command allows to you override the default TCP port of 3306 for TCP/IP
 connections.
\layout Subsubsection

I have discovered a bug.
 Who can I contact?
\layout Standard

Please contact 
\begin_inset LatexCommand \url[the maintainer]{(chris@grubbybaby.com)}

\end_inset 

! Your comments, suggestions, bugfixes, bug catches, and usage testimonials
 are always welcome.
 As free software, mod_log_sql is intended to be a community effort -- any
 code contributions or other ideas will be fully and openly credited, of
 course.
\layout Subsection

Problems
\layout Subsubsection

Apache segfaults or has other problems when using PHP and mod_log_sql
\layout Standard

This occurs if you compiled PHP with MySQL database support.
 PHP utilizes its internal, bundled MySQL libraries by default.
 These conflict with the 
\begin_inset Quotes eld
\end_inset 

real
\begin_inset Quotes erd
\end_inset 

 MySQL libraries linked by mod_log_sql, causing the segmentation fault.
 
\layout Standard

PHP and mod_log_sql can be configured to happily coexist.
 The solution is to configure PHP to link against the real MySQL libraries:
 recompile PHP using --with-mysql=/your/path.
 Apache will run properly once the modules are all using the same version
 of the MySQL libraries.
\layout Subsubsection


\begin_inset LatexCommand \label{faq:NothingLogged}

\end_inset 

Apache appears to start up fine, but nothing is getting logged in the database
\layout Standard

If you do not see any entries in the access_log, then something is preventing
 the inserts from happening.
 This could be caused by several things:
\layout Itemize

Improper privileges set up in the MySQL database 
\layout Itemize

You aren't hitting a VirtualHost that has a LogSQLTransferLogTable entry
 
\layout Itemize

You didn't specify the right database host or login information
\layout Itemize

Another factor is preventing a connection to the database
\layout Standard

Important: it is improper to ask for help before you have followed these
 steps.
\layout Standard

First examine the MySQL log that you established in step 
\begin_inset LatexCommand \ref{step:EnaLog}

\end_inset 

 of section 
\begin_inset LatexCommand \ref{sub:PrepDb}

\end_inset 

.
 Ensure that the INSERT statements are not being rejected because of a malformed
 table name or other typographical error.
 By enabling that log, you instructed MySQL to log every connection and
 command it receives -- if you see no INSERT attempts in the log, the module
 isn't successfully connecting to the database.
 If you see nothing at all in the log -- not even a record of your administrativ
e connection attempts, then you did not enable the log correctly.
 If you do see INSERT attempts but they are failing, the log should tell
 you why.
\layout Standard

Second, confirm that your 
\noun on 
LogSQL*
\noun default 
 directives are all correct.
\layout Standard

Third, examine the Apache
\noun on 
 
\noun default 
error logs for messages from mod_log_sql; the module will offer hints as
 to why it cannot connect, etc.
 
\layout Standard

The next thing to do is recompile the module with debugging output activated.
 change the "#undef DEBUG" on line 8 of mod_log_sql.c to "#define DEBUG"
 and recompile/reinstall.
 The module will now output copious notes about what it is doing, and this
 will help you (and the maintainer) solve the problem.
 In order to see the debugging messages, ensure that you make them visible
 using the 
\noun on 
LogLevel 
\noun default 
directive 
\series bold 
in the main server config as well as in each 
\noun on 
VirtualHost 
\noun default 
config:
\layout LyX-Code

LogLevel debug
\layout LyX-Code

ErrorLog /var/log/httpd/server-messages 
\layout Subsubsection

Why do I get the message 
\begin_inset Quotes eld
\end_inset 

insufficient configuration info to establish database link
\begin_inset Quotes erd
\end_inset 

 in my Apache error log?
\layout Standard

At a minimum, 
\noun on 
LogSQLDatabase
\noun default 
 and 
\noun on 
LogSQLLoginInfo 
\noun default 
must be defined in order for the module to be able to establish a database
 link.
 If these are not defined or are incomplete you will receive this error
 message.
\layout Subsubsection

My database cannot handle all the open connections from mod_log_sql, is
 there anything I can do?
\layout Standard

The rule of thumb: if you have 
\emph on 
n 
\emph default 
webservers each configured to support 
\emph on 
y
\emph default 
 
\noun on 
MaxClients
\noun default 
, then your database must be able to handle 
\begin_inset Formula $n\times y$
\end_inset 

 simultenous connections 
\emph on 
in the worst case.
 
\emph default 
Certainly you must use common sense, consider reasonable traffic expectations
 and structure things accordingly.
\layout Standard

Tweaking my.cnf to scale to high connection loads is imperative.
 But if hardware limitations prevent your MySQL server from gracefully handling
 the number of incoming connections, it would be beneficial to upgrade the
 memory or CPU on that server in order to handle the load.
 
\layout Standard

Jeremy Zawodny, a highly respected MySQL user and contributor to Linux Magazine,
 has this very helpful and highly appropriate article on tuning MySQL: 
\begin_inset LatexCommand \url{http://jeremy.zawodny.com/blog/archives/000173.html}

\end_inset 


\layout Standard

Please remember that mod_log_sql's overriding principle is 
\series bold 
performance
\series default 
 -- that is what the target audience demands and expects.
 Other database logging solutions do not open and maintain many database
 connections, but their performance suffers drastically.
 For example, pgLOGd funnels all log connections through a separate daemon
 that connects to the database, but that bottlenecks the entire process.
 mod_log_sql achieves performance numbers an order of magnitude greater
 than the alternatives because it dispenses with the overhead associated
 with rapid connection cycling, and it doesn't attempt to shoehorn all the
 database traffic through a single extra daemon or proxy process.
\layout Subsubsection

Why do I occasionally see a 
\begin_inset Quotes eld
\end_inset 

lost connection to MySQL server
\begin_inset Quotes erd
\end_inset 

 message in my Apache error log?
\layout Standard

This message may appear every now and then in your Apache error log, especially
 on very lightly loaded servers.
 This doesn't mean that anything is necessarily wrong.
 Within each httpd child process, mod_log_sql will open (and keep open)
 a connection to the MySQL server.
 MySQL, however, will close connections that haven't been used in a while;
 the default timeout is 8 hours.
 When this occurs, mod_log_sql will notice and re-open the connection.
 That event is what is being logged, and looks like this:
\layout LyX-Code

[Tue Nov 12 19:04:10 2002] [error] mod_log_sql: first attempt failed, 
\layout LyX-Code

  API said: error 2013, Lost connection to MySQL server during query 
\layout LyX-Code

[Tue Nov 12 19:04:10 2002] [error] mod_log_sql: reconnect successful
\layout LyX-Code

[Tue Nov 12 19:04:10 2002] [error] mod_log_sql: second attempt successful
\layout Standard

Reference: 
\begin_inset LatexCommand \url[MySQL documentation]{(http://www.mysql.com/documentation/mysql/bychapter/manual_Problems.html#Gone_away)}

\end_inset 


\layout Subsubsection

Sometimes a single VirtualHost gets logged to two different tables (e.g.
 access_foo_com, access_www_foo_com).
 Or, accesses to an unqualified hostname (e.g.
 
\begin_inset Quotes eld
\end_inset 

http://intranet/index.html
\begin_inset Quotes erd
\end_inset 

) get logged in separate tables.
\layout Standard

Proper usage of the Apache runtime 
\noun on 
ServerName
\noun default 
 directive and the directive 
\noun on 
UseCanonicalName On 
\noun default 
(or 
\noun on 
DNS
\noun default 
) are necessary to prevent this problem.
 
\begin_inset Quotes eld
\end_inset 

On
\begin_inset Quotes erd
\end_inset 

 is the default for 
\noun on 
UseCanonicalName
\noun default 
, and specifies that self-referential URLs are generated from the 
\noun on 
ServerName 
\noun default 
part of your VirtualHost:
\layout Quote

With UseCanonicalName on (and in all versions prior to 1.3) Apache will use
 the ServerName and Port directives to construct the canonical name for
 the server.
 With UseCanonicalName off Apache will form self-referential URLs using
 the hostname and port supplied by the client if any are supplied (otherwise
 it will use the canonical name, as defined above).
 [From 
\begin_inset LatexCommand \url[the Apache documentation]{http://httpd.apache.org/docs/mod/core.html#usecanonicalname}

\end_inset 

]
\layout Standard

The module inherits Apache's 
\begin_inset Quotes eld
\end_inset 

knowledge
\begin_inset Quotes erd
\end_inset 

 about the server name being accessed.
 As long as those two directives are properly configured, mod_log_sql will
 log to only one table per virtual host while using 
\noun on 
LogSQLMassVirtualHosting
\noun default 
.
\layout Subsection

Performance and Tuning
\layout Subsubsection

How well does it perform?
\layout Standard

mod_log_sql scales to very high loads.
 Apache 1.3.22 + mod_log_sql was benchmarked using the "ab" (Apache Bench)
 program that comes with the Apache distribution; here are the results.
\layout Standard

Overall configuration:
\layout Itemize

Machine A: Apache webserver 
\layout Itemize

Machine B: MySQL server 
\layout Itemize

Machines A and B connected with 100Mbps Ethernet
\layout Itemize

Webserver: Celeron 400, 128 MB RAM, IDE storage
\layout Standard

Apache configuration:
\layout LyX-Code

Timeout 300 
\layout LyX-Code

KeepAlive On 
\layout LyX-Code

MaxKeepAliveRequests 100 
\layout LyX-Code

KeepAliveTimeout 15 
\layout LyX-Code

MinSpareServers 5 
\layout LyX-Code

StartServers 10 
\layout LyX-Code

MaxSpareServers 15 
\layout LyX-Code

MaxClients 256 
\layout LyX-Code

MaxRequestsPerChild 5000 
\layout LyX-Code

LogSQLTransferLogFormat AbHhmRSsTUuvc 
\layout LyX-Code

LogSQLWhichCookie Clicks 
\layout LyX-Code

CookieTracking on 
\layout LyX-Code

CookieName Clicks
\layout Standard

"ab" commandline:
\layout LyX-Code

./ab -c 10 -t 20 -v 2 -C Clicks=ab_run http://www.hostname.com/target 
\layout Standard

( 10 concurrent requests; 20 second test; setting a cookie "Clicks=ab_run";
 target = the mod_log_sql homepage.
 )
\layout Standard

Ten total ab runs were conducted: five with MySQL logging enabled, and five
 with all MySQL directives commented out of httpd.conf.
 Then each five were averaged.
 The results:
\layout Itemize

Average of five runs employing MySQL 
\emph on 
and
\emph default 
 standard text logging: 
\series bold 
139.01 requests per second, zero errors
\series default 
.
\layout Itemize

Average of five runs employing 
\emph on 
only
\emph default 
 standard text logging: 
\series bold 
139.96 requests per second, zero errors
\series default 
.
\layout Standard

In other words, any rate-limiting effects on this particular hardware setup
 are not caused by MySQL.
 Note that although this very simple webserver setup is hardly cutting-edge
 -- it is, after all, a fairly small machine -- 139 requests per second
 equal over 
\emph on 
twelve million hits per day.
\layout Standard

If you run this benchmark yourself, take note of three things:
\layout Enumerate

Use a target URL that is on your own webserver :-).
 
\layout Enumerate

Wait until all your connections are closed out between runs; after several
 thousand requests your TCP/IP stack will be filled with hundreds of connections
 in TIME_WAIT that need to close.
 Do a "netstat -t|wc -l" on the webserver to see.
 If you don't wait, you can expect to see a lot of messages like "ip_conntrack:
 table full, dropping packet" in your logs.
 (This has nothing to do with mod_log_sql, this is simply the nature of
 the TCP/IP stack in the Linux kernel.)
\layout Enumerate

When done with your runs, clean these many thousands of requests out of
 your database:
\layout LyX-Code

mysql> delete from access_log where agent like 'ApacheBench%'; 
\layout LyX-Code

mysql> optimize table access_log; 
\layout Subsubsection

Do I need to be worried about all the running MySQL children? Will holding
 open 
\emph on 
n
\emph default 
 Apache-to-MySQL connections consume a lot of memory? 
\layout Standard

Short answer: you shouldn't be worried.
\layout Standard

Long answer: you might be evaluating at the output of 
\begin_inset Quotes eld
\end_inset 

ps -aufxw
\begin_inset Quotes erd
\end_inset 

 and becoming alarmed at all the 7MB httpd processes or 22MB mysqld children
 that you see.
 Don't be alarmed
\emph on 
.

\emph default 
 It's true that mod_log_sql opens and holds open many MySQL connections:
 each httpd child maintains one open database connection (and holds it open
 for performance reasons).
 Four webservers, each running 20 Apache children, will hold open 80 MySQL
 connections, which means that your MySQL server needs to handle 80 simultaneous
 connections.
 In truth, your MySQL server needs to handle far more than that if traffic
 to your website spikes and the Apache webservers spawn off an additional
 30 children each...
\layout Standard

Fortunately the cost reported by 'ps -aufxw' is deceptive.
 This is due to an OS memory-management feature called 
\begin_inset Quotes eld
\end_inset 

copy-on-write.
\begin_inset Quotes erd
\end_inset 

 When you have a number of identical child processes (e.g.
 Apache, MySQL), it would appear in 
\begin_inset Quotes eld
\end_inset 

ps
\begin_inset Quotes erd
\end_inset 

 as though each one occupies a great deal of RAM -- as much as 7MB per httpd
 child! In actuality each additional child only occupies a small bit of
 extra memory -- most of the memory pages are common to each child and therefore
 shared in a 
\begin_inset Quotes eld
\end_inset 

read-only
\begin_inset Quotes erd
\end_inset 

 fashion.
 The OS can get away with this because the majority of memory pages for
 one child are identical across all children.
 Instead of thinking of each child as a rubber stamp of the others, think
 of each child as a basket of links to a common memory area.
\layout Standard

A memory page is only duplicated when it needs to be written to, hence 
\begin_inset Quotes eld
\end_inset 

copy-on-write.
\begin_inset Quotes erd
\end_inset 

 The result is efficiency and decreased memory consumption.
 
\begin_inset Quotes eld
\end_inset 

ps
\begin_inset Quotes erd
\end_inset 

 may report 7MB per child, but it might really only 
\begin_inset Quotes eld
\end_inset 

cost
\begin_inset Quotes erd
\end_inset 

 900K of extra memory to add one more child.
 It is 
\series bold 
not
\emph on 
 
\emph default 
correct
\series default 
 to assume that 20 Apache children with a VSZ of 7MB each equals 
\begin_inset Formula $(20\times7MB)$
\end_inset 

 of memory consumption -- the real answer is much, much lower.
 The same 
\begin_inset Quotes eld
\end_inset 

copy-on-write
\begin_inset Quotes erd
\end_inset 

 rules apply to all your MySQL children: 40 mysqld children @ 22MB each
 
\series bold 
do not
\series default 
 occupy 880MB of RAM.
\layout Standard

The bottom line: although there is a cost to spawn extra httpd or mysqld
 children, that cost is not as great as 
\begin_inset Quotes eld
\end_inset 

ps
\begin_inset Quotes erd
\end_inset 

 would lead you to believe.
\layout Subsubsection

My webserver cannot handle all the traffic that my site receives, is there
 anything I can do?
\layout Standard

If you have exhausted all the tuning possibilities on your existing server,
 it is probably time you evaluated the benefits of clustering two or more
 webservers together in a load-balanced fashion.
 In fact, users of such a setup are mod_log_sql's target audience!
\layout Subsubsection


\begin_inset LatexCommand \label{sub:DelayedInsFAQ}

\end_inset 

What is the issue with activating delayed inserts?
\layout Standard

There are several.
\layout Enumerate

INSERT DELAYED is a specific syntax to MySQL and is not supported by any
 other database.
 Ergo, why is it needed, and what MySQL deficiency is it working around?
 INSERT DELAYED is a kluge.
\layout Enumerate

The MySQL documentation is unclear whether INSERT DELAYED is even necessary
 for an optimized database.
 It says, 
\begin_inset Quotes eld
\end_inset 

The DELAYED option for the INSERT statement is a MySQL-specific option that
 is very useful if you have clients that can't wait for the INSERT to complete.
\begin_inset Quotes erd
\end_inset 

 But then it goes on to say, 
\begin_inset Quotes eld
\end_inset 

Note that as MyISAM tables supports concurrent SELECT and INSERT, if there
 is no free blocks in the middle of the data file, you very seldom need
 to use INSERT DELAYED with MyISAM.
\begin_inset Quotes erd
\end_inset 


\layout Enumerate

Because INSERT DELAYED returns without waiting for the data to be written,
 a hard kill of your MySQL database at the right (wrong?) moment could lose
 those logfile entries.
\layout Enumerate

As of MySQL version 3.23.52, the error return functions disagree after a failed
 INSERT DELAYED: mysql_errno() always returns 0, even if mysql_error() returns
 a textual error.
 I have reported this bug to the MySQL folks.
 However, we have no way of knowing what solution they will adopt to fix
 this, and with the worst case solution mod_log_sql would not be able to
 tell if anything went wrong with a delayed insert.
\layout Standard

Instead of delayed inserts, you may wish to utilize InnoDB tables (instead
 of the standard MyISAM tables).
 InnoDB tables suppot row-level locking and are recommended for high-volume
 databases.
\layout Standard

If after understanding these problems you still wish to enable delayed inserts,
 section 
\begin_inset LatexCommand \ref{sub:DelayedIns}

\end_inset 

 discusses how.
\layout Subsection


\begin_inset Quotes eld
\end_inset 

How do I...?
\begin_inset Quotes erd
\end_inset 

 -- accomplishing certain tasks
\layout Subsubsection

How do I extract the data in a format that my analysis tool can understand?
\layout Standard

mod_log_sql would be virtually useless if there weren't a way for you to
 extract the data from your database in a somewhat meaningful fashion.
 To that end there's a Perl script enclosed with the distribution.
 That script (make_combined_log.pl) is designed to extract N-many days worth
 of access logs and provide them in a Combined Log Format output.
 You can use this very tool right in /etc/crontab to extract logs on a regular
 basis so that your favorite web analysis tool can read them.
 Or you can examine the Perl code to construct your own custom tool.
\layout Standard

For example, let's say that you want your web statistics updated once per
 day in the wee hours of the morning.
 A good way to accomplish that could be the following entries in /etc/crontab:
\layout LyX-Code

# Generate the temporary apache logs from the MySQL database (for webalizer)
 
\layout LyX-Code

05 04 * * * root make_combined_log.pl 1 www.grubbybaby.com > /var/log/temp01
\layout LyX-Code

# Run webalizer on httpd log 
\layout LyX-Code

30 04 * * * root webalizer -c /etc/webalizer.conf; rm -f /var/log/temp01
\layout Standard

Or if you have a newer system that puts files in /etc/cron.daily etc., create
 a file called 
\begin_inset Quotes eld
\end_inset 

webalizer
\begin_inset Quotes srd
\end_inset 

 in the cron.daily subdirectory.
 Use the following as the contents of your file, and make sure to chmod
 755 it when done.
\layout LyX-Code

#!/bin/sh
\layout LyX-Code

/usr/local/sbin/make_combined_log.pl 1 www.yourdomain.com > /var/log/httpd/templog
\layout LyX-Code

/usr/local/bin/webalizer -q -c /etc/webalizer.conf 
\layout LyX-Code

rm -f /var/log/httpd/templog
\layout Standard

See? Easy.
\layout Subsubsection


\begin_inset LatexCommand \label{sec:cookie}

\end_inset 

How can I log mod_usertrack cookies?
\layout Standard

A number of people like to log mod_usertrack cookies in their Apache TransferLog
 to aid in understanding their visitors' clickstreams.
 This is accomplished, for example, with a statement as follows:
\layout LyX-Code

LogFormat "%h %l %u %t 
\backslash 
"%r
\backslash 
" %s %b 
\backslash 
"%{Referer}i
\backslash 
" 
\backslash 
"%{User-Agent}i
\backslash 
"" 
\backslash 
"%{cookie}n
\backslash 
""
\layout Standard

Naturally it would be nice for mod_log_sql to permit the admin to log the
 cookie data as well, so as of version 1.10 you can do this.
 You need to have already compiled mod_usertrack into httpd -- it's one
 of the standard Apache modules.
\layout Standard

First make sure you have a column called "cookie" in the MySQL database
 to hold the cookies, which can be done as follows if you already have a
 working database:
\layout LyX-Code

alter table acc_log_tbl add column cookie varchar(255);
\layout Standard

Next configure your server to set usertracking cookies as follows, and make
 sure you include the new 'c' directive in your 
\noun on 
LogSQLTransferLogFormat
\noun default 
, which activates cookie logging.
 Here's an example:
\layout LyX-Code

<VirtualHost 1.2.3.4> 
\layout LyX-Code

 CookieTracking on 
\layout LyX-Code

 CookieStyle Cookie 
\layout LyX-Code

 CookieName Foobar 
\layout LyX-Code

 LogSQLTransferLogFormat huSUsbTvRAc 
\layout LyX-Code

 LogSQLWhichCookie Foobar 
\layout LyX-Code

</VirtualHost>
\layout Standard

The first three lines configure mod_usertrack to create a COOKIE (RFC 2109)
 format cookie called Foobar.
 The last two lines tell mod_log_sql to log cookies named Foobar.
 You have to choose which cookie to log because more than one cookie can/will
 be sent to the server by the client.
\layout Standard

Recap: the 'c' character 
\emph on 
activates
\emph default 
 cookie logging, and the 
\noun on 
LogSQLWhichCookie
\noun default 
 directive 
\emph on 
chooses 
\emph default 
which cookie to log.
\layout Standard

FYI, you are advised NOT to use 
\noun on 
CookieStyle Cookie2
\noun default 
 -- it seems that even newer browsers (IE 5.5, etc.) have trouble with the
 new COOKIE2 (RFC 2965) format.
 Just stick with the standard COOKIE format and you'll be fine.
\layout Standard

Perform some hits on your server and run a select:
\layout LyX-Code

mysql> select request_uri,cookie from access_log where cookie is not null;
\layout LyX-Code

\layout Standard
\align center 

\begin_inset  Tabular
<lyxtabular version="3" rows="4" columns="2">
<features>
<column alignment="left" valignment="top" leftline="true" width="0pt">
<column alignment="left" valignment="top" leftline="true" rightline="true" width="0pt">
<row topline="true" bottomline="true">
<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
\begin_inset Text

\layout Standard

request_uri 
\end_inset 
</cell>
<cell alignment="center" valignment="top" topline="true" leftline="true" rightline="true" usebox="none">
\begin_inset Text

\layout Standard

cookie
\end_inset 
</cell>
</row>
<row topline="true">
<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
\begin_inset Text

\layout Standard

/mod_log_sql/
\end_inset 
</cell>
<cell alignment="center" valignment="top" topline="true" leftline="true" rightline="true" usebox="none">
\begin_inset Text

\layout Standard

ool-18e4.dyn.optonline.net.130051007102700823
\end_inset 
</cell>
</row>
<row topline="true">
<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
\begin_inset Text

\layout Standard

/mod_log_sql/usa.gif
\end_inset 
</cell>
<cell alignment="center" valignment="top" topline="true" leftline="true" rightline="true" usebox="none">
\begin_inset Text

\layout Standard

ool-18e4.dyn.optonline.net.130051007102700823
\end_inset 
</cell>
</row>
<row topline="true" bottomline="true">
<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
\begin_inset Text

\layout Standard

/mod_log_sql/style_1.css
\end_inset 
</cell>
<cell alignment="center" valignment="top" topline="true" leftline="true" rightline="true" usebox="none">
\begin_inset Text

\layout Standard

ool-18e4.dyn.optonline.net.130051007102700823
\end_inset 
</cell>
</row>
</lyxtabular>

\end_inset 


\layout LyX-Code

\layout Subsubsection

What if I want to log more than one cookie? What is the difference between
 LogSQLWhichCookie and LogSQLWhichCookies?
\layout Standard

As of version 1.17, you have a choice in how you want cookie logging handled.
\layout Standard

If you are interested in logging only one cookie per request, follow the
 instructions in section 
\begin_inset LatexCommand \ref{sec:cookie}

\end_inset 

 above.
 That cookie will be logged to a column in the regular access_log table,
 and the actual cookie you want to log is specified with
\noun on 
 LogSQLWhichCookie
\noun default 
.
 Don't forget to specify the 'c' character in 
\noun on 
LogSQLTransferLogFormat
\noun default 
.
\layout Standard

If, however, you need to log multiple cookies per request, you must employ
 the 
\noun on 
LogSQLWhichCookies
\noun default 
 (note the plural) directive.
 The cookies you specify will be logged to a separate table (as discussed
 in section 
\begin_inset LatexCommand \ref{secMulTable}

\end_inset 

), 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.
 Furthermore, with 
\noun on 
LogSQLWhichCookies
\noun default 
, you do 
\series bold 
not
\series default 
 need to include the 'c' character in 
\noun on 
LogSQLTransferLogFormat
\noun default 
.
\layout Standard


\noun on 
LogSQLWhichCookie
\noun default 
 and 
\noun on 
LogSQLWhichCookies
\noun default 
 can coexist without conflict because they operate on entireley different
 tables, but you're better off choosing the one you need.
\layout Subsubsection

What are the SSL logging features, and how do I activate them?
\layout Standard

Note: you do 
\series bold 
not 
\series default 
need to compile SSL support into mod_log_sql in order to simply use it with
 a secure site.
 You only need to compile SSL support into mod_log_sql if you want to log
 SSL-specific data such as the cipher type used, or the keysize that was
 negotiated.
 If that information is unimportant to you, you can ignore this FAQ.
\layout Standard

By adding certain characters to your 
\noun on 
LogSQLTransferLogFormat
\noun default 
 string you can tell mod_log_sql to log the SSL cipher, the SSL keysize
 of the connection, and the maximum keysize that was available.
 This would let you tell, for example, which clients were using only export-grad
e security to access your secure software area.
\layout Standard

You can compile mod_log_sql with SSL logging support if you have the right
 packages installed.
 If you already have an SSL-enabled Apache then you by definition have the
 correct packages already installed: OpenSSL and mod_ssl.
\layout Standard

You need to ensure that your database is set up to log the SSL data.
 Issue the following commands to MySQL if your access table does not already
 have them:
\layout LyX-Code

alter table access_log add column ssl_cipher varchar(25);
\layout LyX-Code

alter table access_log add column ssl_keysize smallint unsigned;
\layout LyX-Code

alter table access_log add column ssl_maxkeysize smallint unsigned;
\layout Standard

Finally configure httpd.conf to activate the SSL fields.
 Note that this is only meaningful in a VirtualHost that is set up for SSL.
\layout LyX-Code

<VirtualHost 1.2.3.4:443> 
\layout LyX-Code

 LogSQLTransferLogFormat AbHhmRSsTUuvc
\series bold 
Qqz
\series default 
 
\layout LyX-Code

</VirtualHost>
\layout Standard

The last three characters (Qqz) in the directive are the SSL ones; see section
 
\begin_inset LatexCommand \ref{sub:Frmat}

\end_inset 

 in the directives documentation for details of the 
\noun on 
LogSQLTransferLogFormat
\noun default 
 directive.
\layout Standard

Restart Apache, then perform some hits on your server.
 Then run the following select statement:
\layout LyX-Code

mysql> select remote_host,request_uri,ssl_cipher,ssl_keysize,ssl_maxkeysize
\layout LyX-Code

from access_log where ssl_cipher is not null;
\layout LyX-Code

\layout Standard
\align center 

\begin_inset  Tabular
<lyxtabular version="3" rows="4" columns="5">
<features>
<column alignment="left" valignment="top" leftline="true" width="0pt">
<column alignment="left" valignment="top" leftline="true" width="0pt">
<column alignment="left" valignment="top" leftline="true" width="0pt">
<column alignment="left" valignment="top" leftline="true" width="0pt">
<column alignment="left" valignment="top" leftline="true" rightline="true" width="0pt">
<row topline="true" bottomline="true">
<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
\begin_inset Text

\layout Standard

remote_host
\end_inset 
</cell>
<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
\begin_inset Text

\layout Standard

request_uri
\end_inset 
</cell>
<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
\begin_inset Text

\layout Standard

ssl_cipher
\end_inset 
</cell>
<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
\begin_inset Text

\layout Standard

ssl_keysize
\end_inset 
</cell>
<cell alignment="center" valignment="top" topline="true" leftline="true" rightline="true" usebox="none">
\begin_inset Text

\layout Standard

ssl_maxkeysize
\end_inset 
</cell>
</row>
<row topline="true">
<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
\begin_inset Text

\layout Standard

216.190.52.4
\end_inset 
</cell>
<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
\begin_inset Text

\layout Standard

/dir/somefile.html 
\end_inset 
</cell>
<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
\begin_inset Text

\layout Standard

RC4-MD5
\end_inset 
</cell>
<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
\begin_inset Text

\layout Standard

128
\end_inset 
</cell>
<cell alignment="center" valignment="top" topline="true" leftline="true" rightline="true" usebox="none">
\begin_inset Text

\layout Standard

128
\end_inset 
</cell>
</row>
<row topline="true">
<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
\begin_inset Text

\layout Standard

216.190.52.4 
\end_inset 
</cell>
<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
\begin_inset Text

\layout Standard

/dir/somefile.gif
\end_inset 
</cell>
<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
\begin_inset Text

\layout Standard

RC4-MD5
\end_inset 
</cell>
<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
\begin_inset Text

\layout Standard

128
\end_inset 
</cell>
<cell alignment="center" valignment="top" topline="true" leftline="true" rightline="true" usebox="none">
\begin_inset Text

\layout Standard

128
\end_inset 
</cell>
</row>
<row topline="true" bottomline="true">
<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
\begin_inset Text

\layout Standard

216.190.52.4 
\end_inset 
</cell>
<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
\begin_inset Text

\layout Standard

/dir/somefile.jpg
\end_inset 
</cell>
<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
\begin_inset Text

\layout Standard

RC4-MD5
\end_inset 
</cell>
<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
\begin_inset Text

\layout Standard

128
\end_inset 
</cell>
<cell alignment="center" valignment="top" topline="true" leftline="true" rightline="true" usebox="none">
\begin_inset Text

\layout Standard

128
\end_inset 
</cell>
</row>
</lyxtabular>

\end_inset 


\layout LyX-Code

\the_end