From adc67fa41225b82d63828f6a7bb7c187783125ca Mon Sep 17 00:00:00 2001
From: Christopher Powell
Date: Thu, 14 Nov 2002 22:52:56 +0000
Subject: More documentation work on the way to final 1.17 release. Additional
 work on the Makefile.

---
 Documentation/documentation.tex | 1534 ---------------------------------------
 1 file changed, 1534 deletions(-)
 delete mode 100644 Documentation/documentation.tex

(limited to 'Documentation/documentation.tex')

diff --git a/Documentation/documentation.tex b/Documentation/documentation.tex
deleted file mode 100644
index 0eb6359..0000000
--- a/Documentation/documentation.tex
+++ /dev/null
@@ -1,1534 +0,0 @@
-%% LyX 1.1 created this file.  For more info, see http://www.lyx.org/.
-%% Do not edit unless you really know what you are doing.
-\documentclass[10pt,english]{article}
-\usepackage[T1]{fontenc}
-\usepackage{geometry}
-\geometry{verbose,letterpaper,tmargin=0.5in,bmargin=1in,lmargin=1in,rmargin=1in}
-\usepackage{babel}
-\IfFileExists{url.sty}{\usepackage{url}}
-                      {\newcommand{\url}{\texttt}}
-
-\makeatletter
-
-%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% LyX specific LaTeX commands.
-\providecommand{\LyX}{L\kern-.1667em\lower.25em\hbox{Y}\kern-.125emX\@}
-
-%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% Textclass specific LaTeX commands.
- \newenvironment{lyxcode}
-   {\begin{list}{}{
-     \setlength{\rightmargin}{\leftmargin}
-     \raggedright
-     \setlength{\itemsep}{0pt}
-     \setlength{\parsep}{0pt}
-     \normalfont\ttfamily}%
-    \item[]}
-   {\end{list}}
-
-\makeatother
-\begin{document}
-
-\vfill{}
-\title{Installing and Running mod\_log\_sql}
-\vfill{}
-
-
-\author{Christopher Powell }
-
-
-\date{11/13/02}
-
-\maketitle
-\tableofcontents{}
-\newpage
-
-
-\section{Introduction}
-
-
-\subsection{Homepage }
-
-\begin{lyxcode}
-http://www.grubbybaby.com/mod\_log\_sql/
-\end{lyxcode}
-
-\subsection{Summary}
-
-This Apache module will permit you to log Apache accesses (and a lot
-of related information) to a SQL database. Unlike logging to a flat
-text file (which is standard in Apache), there are a lot of powerful
-advantages to logging to SQL. This module can either replace or happily
-coexist with Apache's text file logging facility.
-
-
-\subsection{Approach}
-
-This project was formerly known as mod\_log\_mysql. It has been renamed
-to mod\_log\_sql in order to reflect the project goal of database-inspecificity.
-The module currently supports MySQL, and development for other database
-backends is underway.
-
-In order to save speed and overhead, links are kept alive in between
-queries. This module uses one SQL link per httpd child. Among other
-things, this means that this module supports logging into only one
-MySQL server, and for now, also, only one SQL database. But that's
-a small tradeoff compared to the blinding speed of this module.
-
-Virtual hosts are supported in the same manner they are in the regular
-logging modules. You define some basic 'global' directives in the
-main server config, then you define more specific 'local' directives
-inside each virtualhost stanza.
-
-SQL links are opened by each child process when it is born. Error
-reporting is robust throughout and will let you know about database
-issues in the standard Apache error-log for the server or virtual
-server.
-
-A robust \char`\"{}preserve\char`\"{} capability has now been implemented.
-This permits the module to preserve any failed INSERT commands to
-a local file on its machine. In any situation that the database is
-unavailable -- e.g. the network fails, you reboot the db host, etc.
--- mod\_log\_sql will note this in the error log and begin appending
-its log entries to the preserve file (which is created with the user
-\& group ID of the running Apache process, e.g. \char`\"{}nobody\char`\"{}
-on many Linux installations). When your database becomes available
-again, mod\_log\_sql seamlessly resumes logging to it. When convenient
-for the sysadmin, he/she can easily import the preserve file into
-the database because it is simply a series of SQL insert statements:
-
-\begin{lyxcode}
-\#~mysql~-uadminuser~-p~mydbname~<~/tmp/mysql-preserve
-\end{lyxcode}
-
-\subsection{Supported directives}
-
-Please see the web-based documentation for full explanation of all
-supported run-time directives.
-
-http://www.grubbybaby.com/mod\_log\_sql/directives.html
-
-See the FAQ for some handy examples:
-
-http://www.grubbybaby.com/mod\_log\_sql/faq.html
-
-
-\subsection{What gets logged by default? }
-
-All the data that would be contained in the \char`\"{}Combined Log
-Format\char`\"{} is logged by default, plus a little extra. Your best
-bet is to begin by accepting this default, then later customize the
-log configuration based on your needs.
-
-The online documentation of the run-time directives includes a full
-explanation of what you can log, including examples.
-
-
-\subsection{Minor Notes}
-
-\begin{itemize}
-\item Note which directives go in the 'main server config' and which directives
-apply to the 'virtual host config'. This is made clear in the directive
-documentation.
-\item The 'time\_stamp' field is stored in an UNSIGNED INTEGER column, in
-the standard unix \char`\"{}seconds since 1/1/1970 12:00:00\char`\"{}
-format. This is superior to storing the access time as a string due
-to size requirements: an UNSIGNED INT type fits in 4 bytes, whereas
-the Apache date string (e.g. \char`\"{}18/Nov/2001:13:59:52 -0800\char`\"{})
-requires 26 bytes -- significantly larger, and those extra 22 bytes
-will add up over the thousands of accesses that a busy server will
-experience. Besides, an INT type is far more flexible for comparisons,
-etc.
-
-
-In MySQL 3.21 and above you can easily convert this to a human readable
-format using from\_unixtime(), e.g.:
-
-\begin{lyxcode}
-select~remote\_host,request\_uri,from\_unixtime(time\_stamp)~from~access\_log;
-\end{lyxcode}
-The enclosed perl program ''make\_combined\_log.pl'' shows how you
-can extract your access records in a format that is completely Combined
-Log Format compliant. You can then feed this to your favorite web
-log analysis tool.
-
-\item The table's string values can be CHAR or VARCHAR, at a length of your
-choice. VARCHAR is superior because it truncates long strings; CHAR
-types are fixed-length and will be padded with spaces. Just like the
-time\_stamp described above, that kind of space waste will add up
-over thousands of records.
-\item Be careful not to go overboard setting fields to NOT NULL. If a field
-is marked NOT NULL then it must contain data in the INSERT or the
-INSERT will fail, sometimes in a really mysterious way to the frustrated
-sysad.
-\item Apache normally logs numeric fields with a '-' character to mean \char`\"{}not
-applicable,\char`\"{} e.g. bytes\_sent on a request with a 304 response
-code. Since '-' is an illegal character in an SQL numeric field, such
-fields are assigned the value 0 instead of '-' which, of course, makes
-perfect sense anyway.
-\end{itemize}
-
-\subsection{Author / Maintainer}
-
-The actual logging code was taken from the already existing flat file
-text modules, so all that credit goes to the Apache Server group.
-
-The MySQL routines and directives were added by Zeev Suraski <bourbon@netvision.net.il>.
-
-All changes from 1.06+ and the new documentation were added by Chris
-Powell <chris@grubbybaby.com>. It seems that the module had fallen
-into the \char`\"{}unmaintained\char`\"{} category -- it hadn't been
-updated since 1998 -- so Chris adopted it as the new maintainer.
-
-
-\section{Installation}
-
-
-\subsection{Requirements}
-
-\begin{itemize}
-\item A compatible system. I have run mod\_log\_sql on Red Hat based systems
-(Red Hat, Mandrake). These instructions should easily adapt to any
-modern distro.
-\item Apache 1.2 or 1.3 installed. (I run 1.3.22 and it works fine). You
-should have already successfully compiled Apache and know what you're
-doing there.
-\item The MySQL development headers. This is called different things on
-different distros. For example, Red Hat 6.x called this RPM \char`\"{}MySQL-devel\char`\"{}
-whereas Mandrake calls it \char`\"{}libmysql10-devel\char`\"{}.
-\item MySQL >= 3.23.15 configured, installed and running on either localhost
-or an accessible networked machine. You should already have a basic
-understanding of MySQL and how it functions.
-\item Again, basic administrative skills with Apache and MySQL. I try to
-make things as easy as possible in this file, but its purpose is not
-to be an administrative tutorial.
-\item Additionally, if you want to be able to log SSL information such as
-keysize or cipher, you need OpenSSL and glibc-devel installed. Both
-are available as RPMs.
-\end{itemize}
-
-\subsection{Do I want a DSO?}
-
-You need to know the answer to this question before you proceed. The
-answer is pretty straightforward: what have you done in the past?
-If you like all your Apache modules to be dynamic, then you should
-keep doing that. If you're more of an old-school type and prefer to
-compile the modules right into apache, do that. Both methods work
-equally well.
-
-FWIW, the DSO method is more modern and increasing in popularity because
-apxs takes care of a lot of dirty little details for you. As you'll
-see below, the static-module method is a little more complex.
-
-
-\subsection{Installation as an Apache DSO (Preferred) }
-
-\begin{enumerate}
-\item Perform all the following steps as root so that you have install privs,
-etc. Unpack the archive into a working directory.
-
-\begin{lyxcode}
-\#~tar~zxf~mod\_log\_sql.tar.gz~-C~/usr/local/src~\#~cd~/usr/local/src/mod\_log\_sql
-\end{lyxcode}
-\item Edit Makefile for your system.\\
-
-
-
-NECESSARY:
-
-\begin{itemize}
-\item The location where you installed Apache -- usually /usr/local/apache,
-'locate apxs' can help you find it.
-\item The location of your MySQL libraries, find using 'locate libmysqlclient'
-\item The location of your MySQL header files, find using 'locate mysql.h'
-\end{itemize}
-OPTIONAL if you have included mod\_ssl in Apache and want to log SSL
-data such as keysize and cipher type: 
-
-\begin{itemize}
-\item The location of your SSL header files, find using 'locate mod\_ssl.h'
-\end{itemize}
-Now that you know these things, edit Makefile and replace the stock
-values with your own.
-
-IMPORTANT: If you are not logging SSL info, comment out MODSSLHDRS
-by putting a \# character in front of it:
-
-\begin{lyxcode}
-\#MODSSLHDRS=/usr/include/...
-\end{lyxcode}
-\item Instruct apxs to compile the module as a DSO.
-
-\begin{lyxcode}
-\#~make~dso
-\end{lyxcode}
-\item You should see output similar to the following:
-
-\begin{lyxcode}
-/usr/local/Apache/bin/apxs~-Wc,-O2~-Wc,-Wall~-Wc,-DEAPI~-c~-I/usr/include/mysql~-I/usr/local/src/apache\_1.3.27-dso/src/modules/ssl~-L/usr/lib~-lmysqlclient~-lz~mod\_log\_sql.c~
-
-gcc~-DLINUX=22~-DNO\_DBM\_REWRITEMAP~-DMOD\_SSL=208111~-DUSE\_HSREGEX~-DEAPI~-DUSE\_EXPAT~-I../lib/expat-lite~-fpic~-DSHARED\_CORE~-DSHARED\_MODULE~-I/usr/local/Apache/include~-O2~-Wall~-DEAPI~-I/usr/include/mysql~-I/usr/local/src/apache\_1.3.27-dso/src/modules/ssl~-c~mod\_log\_sql.c~
-
-gcc~-shared~-o~mod\_log\_sql.so~mod\_log\_sql.o~-Wc,-O2~-Wc,-Wall~-Wc,-DEAPI~-L/usr/lib~-lmysqlclient~-lz~-lm~-lcrypt~-ldb
-\end{lyxcode}
-You should see no errors and have a file called \char`\"{}mod\_log\_sql.so\char`\"{}
-in your directory.
-
-\item Instruct apxs to install the DSO.
-
-\begin{lyxcode}
-\#~make~dsoinstall
-\end{lyxcode}
-You should see output similar to the following:
-
-\begin{lyxcode}
-/usr/local/Apache/bin/apxs~-i~mod\_log\_sql.so
-
-cp~mod\_log\_sql.so~/usr/local/Apache/libexec/mod\_log\_sql.so
-
-chmod~755~/usr/local/Apache/libexec/mod\_log\_sql.so
-\end{lyxcode}
-\item Module ordering within httpd.conf is important. If you are logging
-SSL, you must make sure that
-
-\begin{lyxcode}
-LoadModule~ssl\_module~libexec/libssl.so
-\end{lyxcode}
-comes before
-
-\begin{lyxcode}
-LoadModule~sql\_log\_module~libexec/mod\_log\_sql.so
-\end{lyxcode}
-If you don't, you will get this error when you start Apache:
-
-\begin{lyxcode}
-/usr/local/apache/libexec/mod\_log\_mysql.so:~undefined~symbol:~ssl\_var\_lookup
-
-/usr/local/apache/bin/apachectl~startssl:~httpd~could~not~be~started
-\end{lyxcode}
-(Because mod\_log\_sql doesn't yet have the required symbols that
-mod\_ssl provides.)
-
-Now skip below to the \char`\"{}Configuration\char`\"{} section.
-
-\end{enumerate}
-
-\subsection{Installation as a static module compiled into httpd}
-
-\begin{enumerate}
-\item Perform all the following steps as root so that you have install privs,
-etc.
-\item Unpack the archive into a working directory.
-
-\begin{lyxcode}
-\#~tar~zxf~mod\_log\_sql.tar.gz~-C~/usr/local/src~\#~cd~/usr/local/src/mod\_log\_sql
-\end{lyxcode}
-\item \label{step:editMF}Edit Makefile for your system.\\
-
-
-
-NECESSARY: 
-
-\begin{itemize}
-\item The location where you installed Apache -- usually /usr/local/apache,
-'locate apxs' can help you find it. 
-\item The location of your Apache {*}sources{*}, find using 'locate ABOUT\_APACHE' 
-\item The location of your MySQL header files, find using 'locate mysql.h'
-\item The location of your MySQL libraries, find using 'locate libmysqlclient'
-\end{itemize}
-OPTIONAL if you have included mod\_ssl in Apache and want to log SSL
-data such as keysize and cipher type: 
-
-\begin{itemize}
-\item The location of your mod\_ssl header files, find using 'locate mod\_ssl.h'
-\item The location of your OpenSSL header files, find using 'locate x509.h'
-\item The location of your db1 header files, find using 'locate mpool.h'
-\end{itemize}
-Now that you know these things, edit Makefile and replace the stock
-values with your own.
-
-IMPORTANT: If you are not logging SSL info, comment out MODSSLHDRS,
-OPNSSLHDRS and DB1HDRS by putting a \# character in front of each
-one, e.g. \#OPNSSLHDRS=/usr/include/...
-
-\item \# make static
-\item \# make statinstall
-\item Change to your Apache source dir.
-
-\begin{lyxcode}
-\#~cd~/usr/local/src/apache-1.3.22/src
-\end{lyxcode}
-\item Re-make your httpd binary as follows.
-
-\begin{enumerate}
-\item Edit Configuration.apaci as follows...
-
-\begin{itemize}
-\item Append the following string to the EXTRA\_LIBS= line. (\char`\"{}/usr/lib/mysql\char`\"{}
-is from step \ref{step:editMF}, where your MySQL libraries live):
-\end{itemize}
-\begin{lyxcode}
--L/usr/lib/mysql~-lmysqlclient~-lm~-lz
-\end{lyxcode}
-\begin{itemize}
-\item Find the mod\_log\_config.o line, and add this line immediately after
-it:
-\end{itemize}
-\begin{lyxcode}
-AddModule~modules/sql/mod\_log\_sql.o
-\end{lyxcode}
-\item \# cp Configuration.apaci Configuration
-\item \# ./Configure
-\item \# make
-\item \# strip httpd
-\end{enumerate}
-\item Test your new apache binary:
-
-\begin{lyxcode}
-\#~./httpd~-l
-\end{lyxcode}
-\item You should see something like:
-
-\begin{lyxcode}
-Compiled-in~modules:~
-
-http\_core.c
-
-mod\_log\_sql.c~<-{}-~That's~the~line~you're~looking~for.
-
-mod\_env.c~
-
-mod\_log\_config.c~
-
-mod\_mime.c~
-
-mod\_negotiation.c~...
-
-etc...
-\end{lyxcode}
-\item Install your httpd binary. Copy it over your old httpd binary, wherever
-it lives. You can and should rename your old httpd first so that you
-can easily revert to that working version in case of bugs with the
-new version.
-
-\begin{lyxcode}
-\#~/etc/rc.d/init.d/httpd~stop~
-
-\#~mv~/usr/local/Apache/bin/httpd~\textasciitilde{}/httpd-save~
-
-\#~cp~-f~./httpd~/usr/local/Apache/bin/
-\end{lyxcode}
-\end{enumerate}
-
-\section{Configuration}
-
-
-\subsection{Preparing MySQL}
-
-You have to prepare the database to receive data from mod\_log\_sql,
-and set up run-time directives in httpd.conf to control how and what
-mod\_log\_sql logs.
-
-This section will discuss how to get started with a basic config.
-Full documentation of the run-time directives is available here: http://www.grubbybaby.com/mod\_log\_sql/directives.html
-
-\begin{enumerate}
-\item mod\_log\_sql can make its own tables on-the-fly, or you can pre-make
-the tables by hand. The advantage of letting the module make the tables
-is ease-of-use, but for raw performance you will want to pre-make
-the tables in order to save overhead. In this basic setup we'll let
-the module create tables for us.
-\item We still need to have a logging database created and ready, so run
-the MySQL command line client and create a database:
-
-\begin{lyxcode}
-\#~mysql~-uadmin~-pmypassword~mysql>~create~database~apachelogs;
-\end{lyxcode}
-\item If you want to hand-create the tables, run the enclosed 'create-tables'
-SQL script as follows:
-
-\begin{lyxcode}
-mysql>~source~create\_tables.sql
-\end{lyxcode}
-\item Create a specific MySQL userid that httpd will use to authenticate
-and enter data. This userid need not be an actual Unix user. It is
-a userid internal to MySQL with specific privileges. In the following
-example command, \char`\"{}apachelogs\char`\"{} is the database, \char`\"{}loguser\char`\"{}
-is the userid to create, \char`\"{}my.apachemachine.com\char`\"{}
-is the name of the Apache machine, and \char`\"{}l0gger\char`\"{}
-is the password to assign. Choose values that are different from these
-examples.
-
-\begin{lyxcode}
-mysql>~grant~insert,create~on~apachelogs.{*}~to~loguser@my.apachemachine.com~identified~by~'l0gger';
-\end{lyxcode}
-\item You may be especially security-paranoid and not want \char`\"{}loguser\char`\"{}
-to have \char`\"{}create\char`\"{} capability within the \char`\"{}apachelogs\char`\"{}
-databse. You can disable that but the cost is that you cannot use
-the module's automatic-table-creation feature. If that's an acceptable
-cost, hand-create the tables as described in step 1 and use the following
-GRANT statement instead of the one above:
-
-\begin{lyxcode}
-mysql>~grant~insert~on~apachelogs.{*}~to~loguser@my.apachemachine.com~identified~by~'l0gger';
-\end{lyxcode}
-\item Enable full logging of your MySQL daemon (at least temporarily for
-debugging purposes) if you don't do this already. Edit /etc/my.cnf
-and add the following line to your {[}mysqld{]} section:
-
-\begin{lyxcode}
-log=/var/log/mysql-messages
-\end{lyxcode}
-Then restart MySQL.
-
-\begin{lyxcode}
-\#~/etc/rc.d/init.d/mysql~restart
-\end{lyxcode}
-\end{enumerate}
-
-\subsection{Preparing Apache}
-
-\begin{enumerate}
-\item Tell the module what database to use and the appropriate authentication
-information.
-
-\begin{description}
-\item [EXAMPLE:]Use the MySQL database called \char`\"{}apachelogs\char`\"{}
-running on \char`\"{}dbmachine.foo.com\char`\"{}. The module uses
-username \char`\"{}loguser\char`\"{} and password \char`\"{}l0gger\char`\"{}
-to authenticate to the database. The log entries will be INSERTed
-into the table called \char`\"{}access\_log\char`\"{}.
-\end{description}
-So, edit httpd.conf and insert the following lines somewhere AFTER
-any LoadModule / AddModule statements. Make sure these statements
-are \char`\"{}global,\char`\"{} i.e. not inside any VirtualHost stanza.
-
-\begin{lyxcode}
-LogSQLDatabase~apachelogs
-
-LogSQLLoginInfo~dbmachine.foo.com~loguser~l0gger~
-
-LogSQLCreateTables~on
-\end{lyxcode}
-\item If your database resides on localhost instead of another host, specify
-the MySQL server's socket file as follows:
-
-\begin{lyxcode}
-LogSQLSocketFile~/your/path/to/mysql.sock
-\end{lyxcode}
-\item The actual logging is set up on a virtual-host-by-host basis. So,
-skip down to the virtual host you want to set up. Instruct this virtual
-host to log entries to the table ''access\_log'' by inserting a
-LogSQLTransferLogTable directive. (The LogSQLTransferLogTable directive
-is the minimum required to log -- other directives simply tune the
-module's behavior.)
-
-\begin{lyxcode}
-<VirtualHost~1.2.3.4>
-
-{[}snip{]}
-
-LogSQLTransferLogTable~access\_log
-
-{[}snip{]}
-
-</VirtualHost>
-\end{lyxcode}
-\item Restart apache.
-
-\begin{lyxcode}
-\#~/etc/rc.d/init.d/httpd~start
-\end{lyxcode}
-\end{enumerate}
-
-\subsection{Testing the module}
-
-\begin{enumerate}
-\item Load your web site in a browser to trigger some hits, then confirm
-that the entries are being successfully logged:
-
-\begin{lyxcode}
-\#~mysql~-hmysql.host.com~-umysqladmin~-p~-e~\char`\"{}select~{*}~from~access\_log\char`\"{}~apachelogs~
-
-Enter~password:
-
-(Several~lines~of~output~should~follow,~corresponding~to~your~hits~on~the~site.)
-\end{lyxcode}
-You now have basic functionality. Don't disable your regular Apache
-logs until you feel comfortable that the database is behaving as you'd
-like and that things are going well.
-
-\item If you do not see any entries in the access\_log, then something is
-preventing the inserts from happening. This problem could be caused
-by several things: 
-
-\begin{itemize}
-\item Improper privileges set up in the MySQL database 
-\item You aren't hitting a VirtualHost that has a LogSQLTransferLogTable
-entry 
-\item You didn't specify the right host
-\end{itemize}
-If you have confirmed your LogSQL{*} directives and know them to be
-correct, you should examine the httpd server logs for mod\_log\_sql
-messages; the module will offer hints as to why it cannot connect,
-etc. Also examine the MySQL log that you established in step 3. Ensure
-that the INSERTs are not being rejected because of a malformed table
-entry or other clerical error. If you see no INSERT attempts in the
-log, the module isn't successfully connecting to the database.
-
-The next thing to do is recompile the module with debugging output
-activated. change the \char`\"{}\#undef DEBUG\char`\"{} on line 8
-of mod\_log\_sql.c to \char`\"{}\#define DEBUG\char`\"{} and recompile/reinstall.
-The module will now output copious notes about what it is doing, and
-this will help you (and the maintainer) solve the problem.
-
-\end{enumerate}
-You can now activate the advanced features of mod\_log\_sql. These
-are all described in the next section.
-
-
-\subsection{Configuration directive reference}
-
-
-\subsubsection{LogSQLLoginInfo }
-
-\begin{lyxcode}
-\textbf{MANDATORY}~
-
-Syntax:~LogSQLLoginInfo~host~user~password
-
-Example:~LogSQLLoginInfo~foobar.baz.com~logwriter~passw0rd~
-
-Context:~server~config
-\end{lyxcode}
-\begin{quote}
-Defines the general parameters of the MySQL host to which you will
-be logging. host is the hostname or IP address of the MySQL machine.
-user is the MySQL userid (not a Unix userid!) with INSERT privileges
-on the table defined in LogSQLTransferLogTable. password is that user's
-password. This is defined only once in the httpd.conf file.
-\end{quote}
-
-\subsubsection{LogSQLDatabase }
-
-\begin{lyxcode}
-\textbf{MANDATORY}
-
-Syntax:~LogSQLDatabase~database~
-
-Example:~LogSQLDatabase~loggingdb~
-
-Context:~server~config
-\end{lyxcode}
-\begin{quote}
-Defines the database that is used for logging. database must be operating
-on the MySQL host defined in LogSQLLoginInfo. This is defined only
-once in the httpd.conf file.
-\end{quote}
-
-\subsubsection{LogSQLTransferLogTable}
-
-\begin{lyxcode}
-\textbf{MANDATORY~}
-
-Syntax:~LogSQLTransferLogTable~table-name~
-
-Example:~LogSQLTransferLogTable~access\_log\_table~
-
-Context:~virtual~host
-\end{lyxcode}
-\begin{quote}
-Defines which table is used for logging of Apache's transfers; this
-is analogous to Apache's TransferLog directive. table-name must be
-a valid table within the database defined in LogSQLDatabase.
-
-This directive is not necessary if you declare LogSQLMassVirtualHosting
-On, since that directive activates dynamically-named tables. If you
-attempt to use LogSQLTransferLogTable at the same time a warning will
-be logged and it will be ignored, since LogSQLMassVirtualHosting takes
-priority.
-\end{quote}
-
-\subsubsection{LogSQLRequestIgnore}
-
-\begin{lyxcode}
-Syntax:~LogSQLRequestIgnore~req1~req2~req3~...~reqN~
-
-Example:~LogSQLRequestIgnore~root.exe~cmd.exe~default.ida~favicon.ico~
-
-Context:~virtual~host
-\end{lyxcode}
-\begin{quote}
-Lists a series of strings that, if present in the URI, will cause
-that request NOT to be logged. This directive is useful for cutting
-down on log clutter when you KNOW that you do not want to log requests
-for certain objects. (The example above is a genuinely useful one;
-it will prevent logging of many common Microsoft-based worm intrusion
-attempts, as well as those ridiculous requests for the favicon.)
-
-Each string is separated by a space, and NO regular expressions or
-globbing are allowed. Each string is evaluated as a substring of the
-URI using strstr().
-\end{quote}
-
-\subsubsection{LogSQLRemhostIgnore}
-
-\begin{lyxcode}
-Syntax:~LogSQLRemhostIgnore~host1~host2~host3~...~hostN~
-
-Example:~LogSQLRemhostIgnore~localnet.com~
-
-Context:~virtual~host
-\end{lyxcode}
-\begin{quote}
-Lists a series of strings that, if present in the REMOTE\_HOST, will
-cause that request NOT to be logged. This directive is useful for
-cutting down on log clutter when you KNOW that you do not want to
-log requests from certain hosts, such as your own internal network
-machines.
-
-Each string is separated by a space, and NO regular expressions or
-globbing are allowed. Each string is evaluated as a substring of the
-REMOTE\_HOST using strstr().
-\end{quote}
-
-\subsubsection{LogSQLRefererIgnore}
-
-\begin{lyxcode}
-Syntax:~LogSQLRefererIgnore~ref1~ref2~ref3~...~refN~
-
-Example:~LogSQLRefererIgnore~google.com~
-
-Context:~virtual~host
-\end{lyxcode}
-\begin{quote}
-Lists a series of strings that, if present in the HTTP\_REFERER, will
-cause that request NOT to be logged. This directive is useful for
-cutting down on log clutter when you know that you do not want to
-log requests from certain referers.
-
-Each string is separated by a space, and NO regular expressions or
-globbing are allowed. Each string is evaluated as a substring of the
-HTTP\_REFERER using strstr().
-\end{quote}
-
-\subsubsection{LogSQLWhichCookie}
-
-\begin{lyxcode}
-Syntax:~LogSQLWhichCookie~cookiename~
-
-Example:~LogSQLWhichCookie~Clicks
-
-Default:~None.~You~must~set~this~if~you~wish~to~capture~cookies.
-
-Context:~virtual~host
-\end{lyxcode}
-\begin{quote}
-In HTTP, cookies have names to distinguish them from each other. Using
-mod\_usertrack, for example, you can give your user-tracking cookies
-a name with the CookieName directive.
-
-If you have activated cookie logging in LogSQLTransferLogFormat, then
-LogSQLWhichCookie tells mod\_log\_sql which cookie to log. This is
-useful because many times you will be setting and receiving more than
-one cookie from a client; without this directive you'd be unable to
-choose which cookie is your mod\_usertrack cookie.
-
-Note: although this was intended for people who are using mod\_usertrack
-to set user-tracking cookies, you aren't restricted in any way. You
-can choose which cookie you wish to log to the database, and it doesn't
-necessarily have to have anything to do with mod\_usertrack.
-\end{quote}
-
-\subsubsection{LogSQLCreateTables}
-
-\begin{lyxcode}
-Syntax:~LogSQLCreateTables~flag
-
-Example:~LogSQLCreateTables~On~
-
-Default:~Off~
-
-Context:~server~config
-\end{lyxcode}
-\begin{quote}
-mod\_log\_sql now has the ability to create its tables on-the-fly.
-The advantage to this is convenience: you don't have to execute any
-SQL by hand to prepare the table. This is especially helpful for people
-with lots of virtual hosts (who should also see the LogSQLMassVirtualHosting
-directive).
-
-There is a slight disadvantage: if you wish to activate this feature,
-then the user specified by LogSQLLoginInfo must have CREATE privileges
-on the database. In an absolutely paranoid, locked-down situation
-you may only want to grant your mod\_log\_sql user INSERT privileges
-on the database; in that situation you are unable to take advantage
-of LogSQLCreateTables. But most people -- even the very security-conscious
--- will find that granting CREATE on the logging database is reasonable.
-
-This is defined only once in the httpd.conf file.
-\end{quote}
-
-\subsubsection{LogSQLMassVirtualHosting}
-
-\begin{lyxcode}
-Syntax:~LogSQLMassVirtualHosting~flag~
-
-Example:~LogSQLMassVirtualHosting~On~
-
-Default:~Off~
-
-Context:~server~config
-\end{lyxcode}
-\begin{quote}
-If you administer a site hosting many, many virtual hosts then this
-option will appeal to you. If you activate LogSQLMassVirtualHosting
-then several things happen:
-\begin{itemize}
-\item the on-the-fly table creation feature is activated automatically 
-\item the transfer log table name is dynamically set from the virtual host's
-name (example: a virtual host www.grubbybaby.com gets logged to table
-access\_www\_grubbybaby\_com) 
-\item which, in turn, means that each virtual host logs to its own segregated
-table. Because there is no data shared between virtual servers you
-can grant your users access to the tables they need; they will be
-unable to view others' data.
-\end{itemize}
-This is a huge boost in convenience for sites with many virtual servers.
-Activating LogSQLMassVirtualHosting obviates the need to create every
-virtual server's table and provides more granular security possibilities.
-
-This is defined only once in the httpd.conf file.
-\end{quote}
-
-\subsubsection{LogSQLPreserveFile}
-
-\begin{lyxcode}
-Syntax:~LogSQLPreserveFile~filename~
-
-Example:~LogSQLPreserveFile~offline-preserve~
-
-Default:~mysql-preserve~(in~/tmp)~
-
-Context:~virtual~host
-\end{lyxcode}
-\begin{quote}
-mod\_log\_sql writes queries to this local preserve file in the event
-that it cannot reach the database, and thus ensures that your high-availability
-web frontend does not lose logs during a temporary database outage.
-This could happen for a number of reasons: the database goes offline,
-the network breaks, etc. You will not lose entries since the module
-has this backup. The file consists of a series of SQL statements that
-can be imported into your database at your convenience; furthermore,
-because the SQL queries contain the access timestamps you do not need
-to worry about out-of-order data after the import.
-
-For security purposes the name you supply will be prepended with \char`\"{}/tmp/\char`\"{}
-to force creation of the file in /tmp.
-
-If you do not define LogSQLPreserveFile then all virtual servers will
-log to the same default preserve file (/tmp/mysql-preserve). You can
-redefine this on a virtual-host basis in order to segregate your preserve
-files if you desire. Note that segregation is not really necessary,
-as the SQL statements that are written to the preserve file already
-distinguish between different virtual hosts.
-
-The module will log to error-log if/when it notices a database outage,
-and upon database return. You will therefore know when the preserve
-file is being used, although it is your responsibility to import the
-file.
-
-The file does not need to be created in advance. It is safe to remove
-or rename the file without interrupting Apache, as the module closes
-the filehandle immediately after completing the write. The file is
-created with the user \& group ID of the running Apache process (e.g.
-'nobody' on many Linux distributions).
-\end{quote}
-
-\subsubsection{LogSQLSocketFile }
-
-\begin{lyxcode}
-Syntax:~LogSQLSocketFile~filename~
-
-Example:~LogSQLSocketFile~/tmp/mysql.sock~
-
-Default:~/var/lib/mysql/mysql.sock~
-
-Context:~server~config
-\end{lyxcode}
-\begin{quote}
-At Apache runtime you can specify the MySQL socket file to use. Set
-this once in your main server config to override the default value.
-This value is irrelevant if your database resides on a separate machine.
-
-mod\_log\_sql will automatically employ the socket for db communications
-if the database resides on the local host. If the db resides on a
-separate host the module will automatically use TCP/IP. This is a
-function of the MySQL API and is not user-configurable.
-\end{quote}
-
-\subsubsection{LogSQLTransferLogFormat }
-
-\begin{lyxcode}
-Syntax:~LogSQLTransferLogFormat~format-string~
-
-Example:~LogSQLTransferLogFormat~huSUTv~
-
-Default:~AbHhmRSsTUuv~
-
-Context:~virtual~host
-\end{lyxcode}
-\begin{quote}
-Each character in the format-string defines an attribute of the request
-that you wish to log. The default logs the information required to
-create Combined Log Format logs, plus several extras. Here is the
-full list of allowable keys, which sometimes resemble their Apache
-counterparts, but do not always:
-
-\begin{tabular}{|c|l|l|l|l|}
-\hline 
-\textbf{Character}&
-\textbf{What is this?}&
-\textbf{MySQL DB field}&
-\textbf{Recommended column type}&
-\textbf{Example}\\
-\hline
-\hline 
-A&
-User agent&
-agent&
-varchar(255)&
-Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 4.0)\\
-\hline 
-b&
-Bytes transfered&
-bytes\_sent&
-int unsigned&
-32561\\
-\hline 
-c&
-Text of cookie&
-cookie&
-varchar(255)&
-Apache=sdyn.fooonline.net.130051007102700823\\
-\hline 
-f&
-&
-request\_file&
-varchar(255)&
-\\
-\hline 
-H&
-HTTP request protocol&
-request\_protocol&
-varchar(10)&
-HTTP/1.1\\
-\hline 
-h&
-Name of remote host&
-remote\_host&
-varchar(50)&
-blah.foobar.com\\
-\hline 
-I&
-Ident user info&
-remote\_logname&
-varchar(50)&
-bobby\\
-\hline 
-m&
-HTTP request method&
-request\_method&
-varchar(6)&
-GET\\
-\hline 
-P&
-httpd child PID&
-child\_pid&
-smallint unsigned&
-3215\\
-\hline 
-p&
-httpd port&
-server\_port&
-smallint unsigned&
-80\\
-\hline 
-R&
-Referer&
-referer&
-varchar(255)&
-http://www.biglinkstoyou.com/linkpage.html\\
-\hline 
-r&
-Request in full form&
-request\_line&
-varchar(255)&
-GET /books-cycroad.html HTTP/1.1\\
-\hline 
-S&
-Time of request in UNIX format&
-time\_stamp&
-int unsigned&
-1005598029\\
-\hline 
-s&
-HTTP status of request&
-status&
-smallint unsigned&
-404\\
-\hline 
-T&
-Seconds to service request&
-request\_duration&
-smallint unsigned&
-2\\
-\hline 
-t&
-Time of request in human format&
-request\_time&
-char(28)&
-{[}02/Dec/2001:15:01:26 -0800{]}\\
-\hline 
-U&
-Request in simple form&
-request\_uri&
-varchar(255)&
-/books-cycroad.html\\
-\hline 
-u&
-User info from HTTP authentication&
-remote\_user&
-varchar(50)&
-bobby\\
-\hline 
-v&
-Virtual host servicing the request&
-virtual\_host&
-varchar(50)&
-www.foobar.com\\
-\hline
-\end{tabular}
-
-If you have compiled mod\_log\_sql with WANT\_SSL\_LOGGING, you also
-get these:
-
-\begin{tabular}{|c|l|l|l|}
-\hline 
-\textbf{Character}&
-\textbf{MySQL DB field it activates}&
-\textbf{MySQL DB field it activatesRecommended column type}&
-\textbf{Example}\\
-\hline
-\hline 
-z&
-ssl\_cipher&
-varchar(25)&
-RC4-MD5\\
-\hline 
-q&
-ssl\_keysize&
-smallint unsigned&
-56\\
-\hline 
-Q&
-ssl\_maxkeysize&
-smallint unsigned&
-128\\
-\hline
-\end{tabular}
-\end{quote}
-
-\section{FAQ}
-
-
-\subsection{Why log to an SQL database?}
-
-To begin with, let's get it out of the way: logging to a database
-is not a panacea. But while there are complexities with this solution,
-the benefit can be substantial for certain classes of administrator
-or people with advanced requirements:
-
-\begin{itemize}
-\item Chores like log rotation go away, as you can DELETE records from the
-SQL database once they are no longer useful. For example, the excellent
-and popular log-analysis tool Webalizer (http://www.webalizer.com/)
-does not need historic logs after it has processed them, enabling
-you to delete older logs. 
-\item People with clusters of web servers (for high availability) will benefit
-the most -- all their webservers can log to a single SQL database.
-This obviates the need to collate/interleave the many separate logfiles,
-which can be / highly/ problematic. 
-\item People acquainted with the power of SQL SELECT statements will know
-the flexibility of the extraction possibilities at their fingertips.
-\end{itemize}
-For example, do you want to see all your 404's? Do this:
-
-\begin{lyxcode}
-select~remote\_host,status,request\_uri,bytes\_sent,from\_unixtime(time\_stamp)~from~acc\_log\_tbl~where~status=404~order~by~time\_stamp;
-
-+-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-+-{}-{}-{}-{}-{}-{}-{}-+-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-+-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-+-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-
-
-+~~~~~~~~~~~~~~~~~~~~~~~remote\_host~|~status~|~request\_uri~~~~~~~~~~~~~~|~bytes\_sent~|~from\_unixtime(time\_stamp)|
-
-+-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-+-{}-{}-{}-{}-{}-{}-{}-+-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-+-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-+-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-
-
-|~marge.mmm.co.uk~~~~~~~~~~~~~~~~~~~|~404~~~~|~/favicon.ico~~~~~~~~~~~~~|~321~~~~~~~~|~2001-11-20~02:30:56~~~~~~|
-
-|~62.180.239.251~~~~~~~~~~~~~~~~~~~~|~404~~~~|~/favicon.ico~~~~~~~~~~~~~|~333~~~~~~~~|~2001-11-20~02:45:25~~~~~~|
-
-|~212.234.12.66~~~~~~~~~~~~~~~~~~~~~|~404~~~~|~/favicon.ico~~~~~~~~~~~~~|~321~~~~~~~~|~2001-11-20~03:01:00~~~~~~|~
-
-|~212.210.78.254~~~~~~~~~~~~~~~~~~~~|~404~~~~|~/favicon.ico~~~~~~~~~~~~~|~333~~~~~~~~|~2001-11-20~03:26:05~~~~~~|~
-
-+-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-+-{}-{}-{}-{}-{}-{}-{}-+-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-+-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-+-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-+
-\end{lyxcode}
-Or do you want to see how many bytes you've sent within a certain
-directory or site? Do this:
-
-\begin{lyxcode}
-select~request\_uri,sum(bytes\_sent)~as~bytes,count(request\_uri)~as~howmany~from~acc\_log\_tbl~where~request\_uri~like~'\%mod\_log\_sql\%'~group~by~request\_uri~order~by~howmany~desc;~
-
-+-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-+-{}-{}-{}-{}-{}-{}-{}-{}-+-{}-{}-{}-{}-{}-{}-{}-{}-+~
-
-|~request\_uri~~~~~~~~~~~~~~~~~~~~~~~|~bytes~~~|~howmany~|~
-
-+-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-+-{}-{}-{}-{}-{}-{}-{}-{}-+-{}-{}-{}-{}-{}-{}-{}-{}-+~
-
-|~/mod\_log\_sql/style\_1.css~~~~~~~~~~|~157396~~|~~~~1288~|~
-
-|~/mod\_log\_sql/~~~~~~~~~~~~~~~~~~~~~|~2514337~|~~~~~801~|~
-
-|~/mod\_log\_sql/mod\_log\_sql.tar.gz~~~|~9769312~|~~~~~456~|~
-
-|~/mod\_log\_sql/faq.html~~~~~~~~~~~~~|~5038728~|~~~~~436~|~
-
-|~/mod\_log\_sql/INSTALL~~~~~~~~~~~~~~|~1196161~|~~~~~202~|~
-
-|~/mod\_log\_sql/directives.html~~~~~~|~1096821~|~~~~~171~|~
-
-|~/mod\_log\_sql/CHANGELOG~~~~~~~~~~~~|~424481~~|~~~~~107~|~
-
-|~/mod\_log\_sql/README~~~~~~~~~~~~~~~|~796072~~|~~~~~~97~|~
-
-|~/mod\_log\_sql/directives-old.html~~|~42480~~~|~~~~~~~6~|
-
-|~/mod\_log\_sql/index.html~~~~~~~~~~~|~9531~~~~|~~~~~~~3~|~
-
-+-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-+-{}-{}-{}-{}-{}-{}-{}-{}-+-{}-{}-{}-{}-{}-{}-{}-{}-+
-\end{lyxcode}
-Or maybe you want to see who's linking to you? Do this:
-
-\begin{lyxcode}
-select~count(referer)~as~num,referer~from~acc\_log\_tbl~where~request\_uri='/mod\_log\_sql/'~group~by~referer~order~by~num~desc;
-
-+-{}-{}-{}-{}-+-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-+
-
-|~num~|~referer~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|~
-
-+-{}-{}-{}-{}-+-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-+
-
-|~271~|~http://freshmeat.net/projects/mod\_log\_sql/~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|~
-
-|~96~~|~http://modules.apache.org/search?id=339~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|~
-
-|~48~~|~http://freshmeat.net/~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|~
-
-|~8~~~|~http://freshmeat.net~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
-
-|~7~~~|~http://freshmeat.net/daily/2001/11/30/~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|~
-
-|~6~~~|~http://freshmeat.net/daily/2001/11/20/~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
-
-+-{}-{}-{}-{}-+-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-+
-\end{lyxcode}
-As you can see, there are myriad possibilities that can be constructed
-with the wonderful SQL SELECT statement. Logging to an SQL database
-can be really quite useful!
-
-
-\subsection{Why use MySQL? Are there alternatives?}
-
-MySQL is a robust, free, and very powerful production-quality database
-engine. It is well supported and comes with detailed documentation.
-Many 3rd-party software pacakges (e.g. Slashcode, the engine that
-powers Slashdot) run exclusively with MySQL. In other words, you will
-belong to a very robust and well-supported community by choosing MySQL.
-
-That being said, there are alternatives. PostgreSQL is probably MySQL's
-leading \char`\"{}competitor\char`\"{} in the free database world.
-There is also an excellent module available for Apache to permit logging
-to a PostgreSQL database, called pgLOGd \url{(http://www.digitalstratum.com/pglogd/)}.
-
-
-\subsection{Is this code production-ready?}
-
-By all accounts it is. It is known to work without a problem on many-thousands-of-hits-per-day
-webservers. Does that mean it is 100\% bug free? Well, no software
-is. But it is well-tested and /believed/ to be fully compatible with
-production environments. (The usual disclaimers apply. This software
-is provided without warranty of any kind.)
-
-
-\subsection{How well does it perform?}
-
-mod\_log\_sql scales to very high loads. Apache 1.3.22 + mod\_log\_sql
-was benchmarked using the \char`\"{}ab\char`\"{} (Apache Bench) program
-that comes with the Apache distribution; here are the results.
-
-Overall configuration:
-
-\begin{itemize}
-\item Machine A: Apache webserver 
-\item Machine B: MySQL server 
-\item Machines A and B connected with 100Mbps Ethernet
-\end{itemize}
-Webserver configuration:
-
-\begin{itemize}
-\item Celeron 400 128 MB RAM IDE storage
-\end{itemize}
-Apache configuration:
-
-\begin{lyxcode}
-Timeout~300~
-
-KeepAlive~On~
-
-MaxKeepAliveRequests~100~
-
-KeepAliveTimeout~15~
-
-MinSpareServers~5~
-
-StartServers~10~
-
-MaxSpareServers~15~
-
-MaxClients~256~
-
-MaxRequestsPerChild~5000~
-
-LogSQLTransferLogFormat~AbHhmRSsTUuvc~
-
-LogSQLWhichCookie~Clicks~
-
-CookieTracking~on~
-
-CookieName~Clicks
-\end{lyxcode}
-\char`\"{}ab\char`\"{} commandline:
-
-\begin{lyxcode}
-./ab~-c~10~-t~20~-v~2~-C~Clicks=ab\_run~http://www.hostname.com/target~
-\end{lyxcode}
-( 10 concurrent requests; 20 second test; setting a cookie \char`\"{}Clicks=ab\_run\char`\"{};
-target = the mod\_log\_sql homepage. )
-
-Ten total ab runs were conducted: five with MySQL logging enabled,
-and five with all MySQL directives commented out of httpd.conf. Then
-each five were averaged. The results:
-
-\begin{itemize}
-\item Average of five runs employing MySQL \emph{and} standard text logging:
-139.01 requests per second, zero errors.
-\item Average of five runs employing \emph{only} standard text logging:
-139.96 requests per second, zero errors.
-\end{itemize}
-In other words, any rate-limiting effects on this particular hardware
-setup are not caused by MySQL. Note that although this very simple
-webserver setup is hardly cutting-edge -- it is, after all, a fairly
-small machine -- 139 requests per second equal over twelve million
-hits per day\emph{.}
-
-If you run this benchmark yourself, take note of three things:
-
-\begin{enumerate}
-\item Use a target URL that is on your own webserver :-). 
-\item Wait until all your connections are closed out between runs; after
-several thousand requests your TCP/IP stack will be filled with hundreds
-of connections in TIME\_WAIT that need to close. Do a \char`\"{}netstat
--t|wc -l\char`\"{} on the webserver to see. If you don't wait, you
-can expect to see a lot of messages like \char`\"{}ip\_conntrack:
-table full, dropping packet\char`\"{} in your logs. (This has nothing
-to do with mod\_log\_sql, this is simply the nature of the TCP/IP
-stack in the Linux kernel.)
-\item When done with your runs, clean these many thousands of requests out
-of your database:
-\end{enumerate}
-\begin{lyxcode}
-mysql>~delete~from~access\_log~where~agent~like~'ApacheBench\%';~mysql>~optimize~table~access\_log;~
-\end{lyxcode}
-
-\subsection{Who's using mod\_log\_sql?}
-
-Good question! It would be great to find out! If you are a production-level
-mod\_log\_sql user, please contact the maintainer, Chris Powell \url{chris@grubbybaby.com}so
-that you can be mentioned here.
-
-
-\subsection{How do I extract the data in a format that my analysis tool can understand?}
-
-mod\_log\_sql would be virtually useless if there weren't a way for
-you to extract the data from your database in a somewhat meaningful
-fashion. To that end there's a Perl script enclosed with the distribution.
-That script (make\_combined\_log.pl) is designed to extract N-many
-days worth of access logs and provide them in a Combined Log Format
-output. You can use this very tool right in /etc/crontab to extract
-logs on a regular basis so that your favorite web analysis tool can
-read them. Or you can examine the Perl code to construct your own
-custom tool.
-
-For example, let's say that you want your web statistics updated once
-per day in the wee hours of the morning. A good way to accomplish
-that would be the following entries in /etc/crontab:
-
-\begin{lyxcode}
-\#~Generate~the~temporary~apache~logs~from~the~MySQL~database~(for~webalizer)~
-
-05~04~{*}~{*}~{*}~root~/usr/local/sbin/make\_combined\_log.pl~1~www.grubbybaby.com~>~/var/log/httpd/mysql-grubbybaby
-
-\#~Run~webalizer~on~httpd~log~
-
-30~04~{*}~{*}~{*}~root~/usr/local/bin/webalizer~-c~/etc/webalizer.conf;~rm~-f~/var/log/httpd/mysql-grubbybaby
-\end{lyxcode}
-Or if you have a newer system that puts files in /etc/cron.daily etc.,
-create a file called ''webalizer'' in the cron.\_\_\_\_ subdir of
-your choice. Use the following as the contents of your file, and make
-sure to chmod 755 it when done.
-
-\begin{lyxcode}
-\#!/bin/sh
-
-/usr/local/sbin/make\_combined\_log.pl~1~www.yourdomain.com~>~/var/log/httpd/templog
-
-/usr/local/bin/webalizer~-q~-c~/etc/webalizer.conf~
-
-rm~-f~/var/log/httpd/templog
-\end{lyxcode}
-See? Easy.
-
-
-\subsection{Why doesn't the module also replace the Apache ErrorLog?}
-
-There are circumstances when that would be quite unwise -- for example,
-if Apache could not reach the MySQL server for some reason and needed
-to log that fact. Without a text-based error log you'd never know
-anything was wrong, because Apache would be trying to log a database
-connection error to the database... you get the point.
-
-Error logs are usually not very high-traffic and are really best left
-as text files on a web server machine.
-
-
-\subsection{\label{sec:cookie}How can I log mod\_usertrack cookies?}
-
-A number of people like to log mod\_usertrack cookies in their Apache
-TransferLog to aid in understanding their visitors' clickstreams.
-This is accomplished, for example, with a statement as follows:
-
-\begin{lyxcode}
-LogFormat~\char`\"{}\%h~\%l~\%u~\%t~\textbackslash{}\char`\"{}\%r\textbackslash{}\char`\"{}~\%s~\%b~\textbackslash{}\char`\"{}\%\{Referer\}i\textbackslash{}\char`\"{}~\textbackslash{}\char`\"{}\%\{User-Agent\}i\textbackslash{}\char`\"{}\char`\"{}~\textbackslash{}\char`\"{}\%\{cookie\}n\textbackslash{}\char`\"{}\char`\"{}
-\end{lyxcode}
-Naturally it would be nice for mod\_log\_sql to permit the admin to
-log the cookie data as well, so as of version 1.10 you can do this.
-You need to have already compiled mod\_usertrack into httpd -- it's
-one of the standard Apache modules.
-
-First make sure you have a column called \char`\"{}cookie\char`\"{}
-in the MySQL database to hold the cookies, which can be done as follows
-if you already have a working database:
-
-\begin{lyxcode}
-alter~table~acc\_log\_tbl~add~column~cookie~varchar(255);
-\end{lyxcode}
-Next configure your server to set usertracking cookies as follows,
-and make sure you include the new 'c' directive in your LogSQLTransferLogFormat,
-which activates cookie logging. Here's an example:
-
-\begin{lyxcode}
-<VirtualHost~1.2.3.4>~
-
-~CookieTracking~on~
-
-~CookieStyle~Cookie~
-
-~CookieName~Foobar~
-
-~LogSQLTransferLogFormat~huSUsbTvRAc~
-
-~LogSQLWhichCookie~Foobar~
-
-</VirtualHost>
-\end{lyxcode}
-The first three lines configure mod\_usertrack to create a COOKIE
-(RFC 2109) format cookie called Foobar. The last two lines tell mod\_log\_sql
-to log cookies named Foobar. You have to choose which cookie to log
-because more than one cookie can/will be sent to the server by the
-client.
-
-FYI, you are advised NOT to use CookieStyle Cookie2 -- it seems that
-even newer browsers (IE 5.5, etc.) have trouble with the new COOKIE2
-(RFC 2965) format. Just stick with the standard COOKIE format and
-you'll be fine.
-
-Perform some hits on your server and run a select:
-
-\begin{lyxcode}
-mysql>~select~request\_uri,cookie~from~access\_log~where~cookie~is~not~null;
-
-+-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-+-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-+
-
-|~request\_uri~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~cookie~|~
-
-+-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-+-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-+~
-
-|~/mod\_log\_sql/~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|~ool-18e4.dyn.optonline.net.130051007102700823~~~~~~|~
-
-|~/mod\_log\_sql/usa.gif~~~~~~~~~~~~~~~~~~~~~~~|~ool-18e4.dyn.optonline.net.130051007102700823~~~~~~|~
-
-|~/mod\_log\_sql/style\_1.css~~~~~~~~~~~~~~~~~~~|~ool-18e4.dyn.optonline.net.130051007102700823~~~~~~|~
-
-...etc...
-\end{lyxcode}
-
-\subsection{What if I want to log more than one cookie?}
-
-No problem. As of version 1.17, you have a choice. If you are just
-interested in a single cookie, follow the instructions in section
-\ref{sec:cookie} above. That cookie will be logged to a column in
-the regular access\_log table.
-
-However, if you need to log multiple cookies, you'll employ the LogSQLWhichCookies
-(note the plural) directive. The cookies you specify will be logged
-to a separate table, and entries in that table will be linked to the
-regular access\_log entries via the unique ID that is supplied by
-mod\_unique\_id. Without mod\_unique\_id the information will still
-be logged but you will be unable to correlate which cookies go with
-which access-requests.
-
-LogSQLWhichCookie and LogSQLWhichCookies can coexist without conflict,
-but you're better off choosing the one you need.
-
-
-\subsection{What are the SSL logging features, and how do I activate them?}
-
-If you run an SSL-enabled server you may benefit from logging some
-SSL details. mod\_log\_sql now supports this ability. By adding certain
-characters to your LogSQLTransferLogFormat string you can tell mod\_log\_sql
-to log the SSL cipher, the SSL keysize of the connection, and the
-Max-keysize that was available. This would let you tell, for example,
-which clients were using only export-grade security to access your
-secure software area.
-
-You can compile mod\_log\_sql with SSL logging support if you have
-the right packages installed. If you already have an SSL-enabled Apache
-then you by definition have the correct packages already installed:
-OpenSSL and mod\_ssl.
-
-You need to ensure that your database is set up to log the SSL data.
-Issue the following commands to MySQL once you have your basic access\_log
-table built:
-
-\begin{lyxcode}
-alter~table~access\_log~add~column~ssl\_cipher~varchar(25);
-
-alter~table~access\_log~add~column~ssl\_keysize~smallint~unsigned;
-
-alter~table~access\_log~add~column~ssl\_maxkeysize~smallint~unsigned;
-\end{lyxcode}
-Finally configure httpd.conf to activate the SSL fields. Note that
-this is only meaningful in a VirtualHost that is set up for SSL.
-
-\begin{lyxcode}
-<VirtualHost~1.2.3.4:443>~
-
-~LogSQLTransferLogFormat~AbHhmRSsTUuvcQqz~
-
-</VirtualHost>
-\end{lyxcode}
-The last three characters (Qqz) in the directive are the SSL ones;
-see the directives documentation for details.
-
-Perform some hits on your server and run a select:
-
-\begin{lyxcode}
-mysql>~select~remote\_host,request\_uri,ssl\_cipher,ssl\_keysize,ssl\_maxkeysize~from~access\_log~where~ssl\_cipher~is~not~null;
-
-+-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-+-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-+-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-+-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-+-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-+~
-
-|~remote\_host~~~~~~~~~~~~~~|~request\_uri~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|~ssl\_cipher~|~ssl\_keysize~|~ssl\_maxkeysize~|
-
-+-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-+-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-+-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-+-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-+-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-+~
-
-|~216.190.52.4~~~~~~~~~~~~~|~/dir/somefile.html~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|~RC4-MD5~~~~|~128~~~~~~~~~|~128~~~~~~~~~~~~|~
-
-|~216.190.52.4~~~~~~~~~~~~~|~/dir/somefile.gif~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|~RC4-MD5~~~~|~128~~~~~~~~~|~128~~~~~~~~~~~~|~
-
-|~216.190.52.4~~~~~~~~~~~~~|~/dir/somefile.jpg~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|~RC4-MD5~~~~|~128~~~~~~~~~|~128~~~~~~~~~~~~|~
-
-...etc...
-\end{lyxcode}
-
-\subsection{Does mod\_log\_sql connect to MySQL via TCP/IP or a socket?}
-
-It depends! Actually this isn't determined by mod\_log\_sql. mod\_log\_sql
-relies on a connection command that is supplied in the MySQL API,
-and that command is somewhat intelligent. When mod\_log\_sql issues
-the connect command to MySQL, this intelligent connect command uses
-sockets to communicate with MySQL if the specified MySQL database
-is on the same machine (because sockets are more efficient than TCP/IP).
-However, if the specified MySQL db is on a different machine, mod\_log\_sql
-connects using TCP/IP. You don't have any control of which methodology
-is used.
-
-You do have control over where mod\_log\_sql looks for the socket.
-The LogSQLSocketFile runtime configuration directive overrides the
-default of \char`\"{}/var/lib/mysql/mysql.sock\char`\"{} to whatever
-you wish. (Applies to mod\_log\_sql 1.16 or later only.)
-
-
-\subsection{Why do I occasionally see a \char`\"{}connection lost, attempting
-reconnect\char`\"{} message in my error-log?}
-
-This message may appear every now and then in your Apache error log,
-especially on very lightly loaded servers. This doesn't mean that
-anything is necessarily wrong. Within each httpd child process, mod\_log\_sql
-will open (and keep open) a connection to the MySQL server. MySQL,
-however, will close connections that haven't been used in a while;
-the default timeout is 8 hours. When this occurs, mod\_log\_sql will
-notice and re-open the connection. That event is what is being logged,
-and looks like this:
-
-\begin{lyxcode}
-{[}Thu~Dec~13~05:42:18~2001{]}~{[}error{]}~mod\_log\_sql:~connection~lost,~attempting~reconnect
-
-{[}Thu~Dec~13~05:42:18~2001{]}~{[}error{]}~mod\_log\_sql:~reconnect~successful
-\end{lyxcode}
-Reference: MySQL documentation \url{http://www.mysql.com/documentation/mysql/bychapter/manual_Problems.html#Gone_away}
-
-
-\subsection{Does mod\_log\_sql work with Apache 2.x?}
-
-As of this writing, no. The Apache Group significantly altered the
-module API with the release of Apache 2.0. All modules written for
-1.3, including mod\_log\_sql, will not work with 2.0.
-
-mod\_log\_sql will eventually be ported to Apache 2.x, but not immediately.
-It is going to take some time, and there are other features that have
-higher priority. Please sign up for the announcements list (on the
-main website) or monitor the website for updates to learn when the
-port (and other releases) are available.
-
-<OPINION>If you're a {*}NIX user, stick with Apache 1.3.x for now.
-Major modules like mod\_ssl and PHP are not even ready for 2.0 yet,
-and the main benefits in 2.0 are for Win32 users anyway. Apache 1.3.x
-is rock-stable and performs equally well on {*}NIX as 2.0.</OPINION>
-
-
-\subsection{I have discovered a bug. Who can I contact?}
-
-Please contact the maintainer \url{chris@grubbybaby.com}! Your comments,
-suggestions, bugfixes, bug catches, and usage testimonials are always
-welcome. As free software, mod\_log\_sql is intended to be a community
-effort -- any code contributions or other ideas will be fully and
-openly credited, of course.
-\end{document}
-- 
cgit 

