3 Configuration

3.1 Preparing MySQL for logging

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 all available run-time directives is available in section 3.6.

1. mod_log_sql can make its own tables on-the-fly, or you can pre-make the tables by hand. The advantage of letting the module make the tables is ease-of-use, but for raw performance you will want to pre-make the tables in order to save some overhead. In this basic setup we'll just let the module create tables for us.
2. We still need to have a logging database created and ready, so run the MySQL command line client and create a database:

   # mysql -uadmin -pmypassword 

   Enter password:

   mysql> create database apachelogs;

3. If you want to hand-create the tables, run the enclosed 'create-tables' SQL script as follows:

   mysql> source create_tables.sql

4. 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.

   mysql> grant insert,create on apachelogs.* to loguser@my.apachemachine.com

   identified by 'l0gger';

5. You may be especially security-paranoid and want "loguser" to not 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 3 and use the following GRANT statement instead of the one above:

   mysql> grant insert on apachelogs.* to loguser@my.apachemachine.com

   identified by 'l0gger';

6. 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:

   log=/var/log/mysql-messages

   Then restart MySQL.

   # /etc/rc.d/init.d/mysql restart

3.2 A very basic logging setup in Apache

1. Tell the module what database to use and the appropriate authentication information.

   So, edit httpd.conf and insert the following lines somewhere after any LoadModule / AddModule statements. Make sure these statements are ``global,'' i.e. not inside any VirtualHost stanza. You will also note that you are embedding a password in the file. Therefore you are advised to ``chmod 660 httpd.conf'' to prevent unauthorized regular users from viewing your database user and password.

   Example: 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.

   LogSQLLoginInfo dbmachine.foo.com loguser l0gg3r 

   LogSQLDatabase apachelogs

   LogSQLCreateTables on

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

   LogSQLSocketFile /your/path/to/mysql.sock

   If your database is listening on a port other than 3306, specify the correct TCP port as follows:

   LogSQLTCPPort 1234

2. 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 that you'll learn about later simply tune the module's behavior.)

   <VirtualHost 1.2.3.4>

    [snip]

    LogSQLTransferLogTable access_log

    [snip]

   </VirtualHost>

3. Restart apache.

   # /etc/rc.d/init.d/httpd stop

   # /etc/rc.d/init.d/httpd start

3.3 Testing the basic setup

1. Visit your web site in a browser to trigger some hits, then confirm that the entries are being successfully logged:

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

   Enter password:

   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 4.2.2 of the FAQ on how to debug and fix the situation.

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

3.4 How to tune logging with run-time directives

3.4.1 Instructing the module what to log

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

After LOGSQLTRANSFERLOGFORMAT you supply a string of characters that tell the module what information to log. In the configuration directive reference (section 3.6.17) there is a table which clearly defines all the possible things to log. Let's say you want to log only the ``request time,'' the ``remote host,'' and the ``request''; you'd use:

LogSQLTransferLogFormat hUS

But a more appropriate string to use is

LogSQLTransferLogFormat AbHhmRSsTUuv

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

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.

Some of the LOGSQLTRANSFERLOGFORMAT characters require a little extra configuration:

• 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 LOGSQLWHICHCOOKIE - after all, there could be many cookies associated with a given request. Fail to specify LOGSQLWHICHCOOKIE, and no cookie information at all will be logged.
• 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 LOGSQLMACHINEID directive. Fail to specify LOGSQLMACHINEID, and a simple '-' character will be logged in the machine_id column.

3.4.2 Instructing the module what NOT to log using filtering directives

One ``accept'' and two ``ignore'' 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.

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

When a request comes in, the contents of LOGSQLREQUESTACCEPT are evaluated first. This optional, ``blanket'' directive lets you specify that only certain things are to be accepted for logging, and everything else discarded. Because it is evaluated before LOGSQLREQUESTIGNORE and LOGSQLREMHOSTIGNORE it can halt logging before those two filtering directives ``get their chance.''

Once a request makes it past LOGSQLREQUESTACCEPT, it still can be excluded based on LOGSQLREMHOSTIGNORE and LOGSQLREQUESTIGNORE. A good way to use LOGSQLREMHOSTIGNORE is to prevent the module from logging the traffic that your internal hosts generate. LOGSQLREQUESTIGNORE is great for preventing things like requests for ``favicon.ico'' from cluttering up your database, as well as excluding the various requests that worms make, etc.

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 if it is a substring of the larger request or remote-host; the comarison is case-sensitive. This means that ``LOGSQLREMHOSTIGNORE micro'' will ignore requests from ``microsoft.com,'' ``microworld.net,'' ``mymicroscope.org,'' etc. ``LOGSQLREQUESTIGNORE gif'' will instruct the module to ignore requests for ``leftbar.gif,'' ``bluedot.gif'' and even ``giftwrap.jpg'' - but ``RED.GIF'' and ``Tree.Gif'' would still get logged because of case sensitivity.

A summary of the decision flow:

1. If LOGSQLREQUESTACCEPT exists and a request does not match anything in that list, it is discarded.
2. If a request matches anything in the LOGSQLREQUESTIGNORE list, it is discarded.
3. If a reqiest matches anything in the LOGSQLREMHOSTIGNORE list, it is discarded.
4. Otherwise the request is logged.

This means that you can have a series of directives similar to the following:

LogSQLRequestAccept *.html *.gif *.jpg

LogSQLRequestIgnore statistics.html bluedot.jpg

So the first line instructs the module to only log files with html, gif and jpg suffixes; requests for ``formail.cgi'' and ``shopping-cart.pl'' will never be considered for logging. (``LeftArrow.JPG'' will also never be considered for logging - remember, the comparison is case sensitive.) The second line prunes the list further - you never want to log requests for those two objects.

Tip: if you want to match all the hosts in your domain such as ``host1.corp.foo.com'' and ``server.dmz.foo.com'', simply specify:

LogSQLRemhostIgnore foo.com

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

LogSQLRequestIgnore root.exe cmd.exe default.ida

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:

LogSQLRequestIgnore .gif .jpg

3.5 Advanced logging scenarios

3.5.1 Using the module in an ISP environment

mod_log_sql has three basic tiers of operation:

1. The administrator creates all necessary tables by hand and configures each Apache VirtualHost by hand. (LOGSQLCREATETABLES OFF)
2. The module is permitted to create necessary tables on-the-fly, but the administrator configures each Apache VirtualHost by hand. (LOGSQLCREATETABLES ON)
3. The module is permitted to create all necessary tables and to make intelligent, on-the-fly configuration of each VirtualHost. (LOGSQLMASSVIRTUALHOSTING ON)

Many users are happy to use the module in its most minimal form: they hand-create any necessary tables (using ``create_tables.sql''), 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 LOGSQLMASSVIRTUALHOSTING 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:

• the on-the-fly table creation feature is activated automatically
• 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'')

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 LOGSQLTRANSFERLOGTABLE 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.

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 LOGSQLMACHINEID directive. The administrator uses this directive to assign a unique identifier to each machine in the web cluster, e.g. ``LOGSQLMACHINEID web01,'' ``LOGSQLMACHINEID web02,'' etc. Used in conjunction with the 'M' character in LOGSQLTRANSFERLOGFORMAT, 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.

3.5.2 Logging many-to-one data in separate tables

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 ``mod_gzip'' notes associated with it. mod_log_sql is capable of logging these relationships due to the elegance of SQL relational data.

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 LOGSQLTRANSFERLOGFORMAT 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.

Table 1: access_log

id	remote_host	request_uri	time_stamp	status	bytes_sent
PPIDskBRH30AAGPtAsg	zerberus.aiacs.net	/mod_log_sql/index.html	1022493617	200	2215

Table 2: notes_log

id	item	val
PPIDskBRH30AAGPtAsg	mod_gzip_result	OK
PPIDskBRH30AAGPtAsg	mod_gzip_compression_ratio	69

Table 3: headers_log

id	item	val
PPIDskBRH30AAGPtAsg	Content-Type	text/html
PPIDskBRH30AAGPtAsg	Accept-Encoding	gzip, deflate
PPIDskBRH30AAGPtAsg	Expires	Tue, 28 May 2002 10:00:18 GMT
PPIDskBRH30AAGPtAsg	Cache-Control	max-age=86400

We have a certain request, and its unique ID is ``PPIDskBRH30AAGPtAsg''. 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 1, 2 and 3, 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 ``select'' statement and table joins. To see the notes associated with a particular request:

select a.remote_host, a.request_uri, n.item, n.val from access_log a, notes_log n

where a.id=n.id and a.id='PPIDskBRH30AAGPtAsg';

remote_host	request_uri	item	val
zerberus.aiacs.net	/mod_log_sql/index.html	mod_gzip_result	OK
zerberus.aiacs.net	/mod_log_sql/index.html	mod_gzip_compression_ratio	69

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.

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

• 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.
• Create the appropriate tables. This will be done for you if you permit mod_log_sql to create its own tables using LOGSQLCREATETABLES ON, or if you use the enclosed ``create_tables.sql'' script.
• Create a SQL index on the ``id'' 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.
• Within each appropriate VirtualHost stanza, use the LOGSQLWHICH* and LOGSQL*LOGTABLE 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 LOGSQLTRANSFERLOGTABLE.)

<VirtualHost 216.231.36.128>

  (snip)

  LogSQLNotesLogTable notestable

  LogSQLWhichCookies bluecookie redcookie greencookie 

  LogSQLWhichNotes mod_gzip_result mod_gzip_compression_ratio

  LogSQLWhichHeadersOut Expires Content-Type Cache-Control 

  LogSQLWhichHeadersIn UserAgent Accept-Encoding Host

  (snip)

</VirtualHost>

3.5.3 Using the same database for production and test

Although suboptimal, it is not uncommon to use the same backend database for the ``production'' webservers as well as the ``test'' webservers (budgetary constraints, rackspace limits, etc.). Furthermore, an administrator in this situation may be unable to use LOGSQLREMHOSTIGNORE 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.

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 LOGSQLMACHINEID directive. Assume a scenario where the production webservers have IDs like ``web01,'' ``web02,'' and so on - and the test webservers have IDs like ``test01,'' ``test02,'' 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:

delete from access_log where machine_id like 'test%';

3.5.4 Optimizing for a busy database

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 ``delayed inserts,'' which are described as follows in the MySQL documentation:

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 periodically 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.

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.

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.

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.

The general disadvantages of delayed inserts are:

1. 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.
2. There is additional overhead for the server to handle a separate thread for each table on which you use INSERT DELAYED.

The MySQL documentation concludes, ``This means that you should only use INSERT DELAYED when you are really sure you need it!'' 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 4.3.4 in the FAQ - you have been warned.

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.

3.6 Configuration directive reference

It is imperative that you understand which directives are used only once in the main server config, and which are used inside VirtualHost stanzas and therefore multiple times within httpd.conf. The ``context'' listed with each entry informs you of this.

3.6.1 LogSQLCookieLogTable

Syntax: LogSQLCookieLogTable table-name 

Example: LogSQLCookieLogTable cookie_log

Default: cookies

Context: virtual host

Defines which table is used for logging of cookies. Working in conjunction with LOGSQLWHICHCOOKIES, 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.

Note that you must create the table (see create-tables.sql, included in the package), or LOGSQLCREATETABLES must be set to ``on''.

3.6.2 LogSQLCreateTables

Syntax: LogSQLCreateTables flag

Example: LogSQLCreateTables On 

Default: Off 

Context: main server config

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 LOGSQLMASSVIRTUALHOSTING directive).

There is a slight disadvantage: if you wish to activate this feature, then the userid specified in 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.

3.6.3 LogSQLDatabase

MANDATORY

Syntax: LogSQLDatabase database 

Example: LogSQLDatabase loggingdb 

Context: main server config

Defines the database that is used for logging. ``database'' must be a valid db on the MySQL host defined in LOGSQLLOGININFO.

This is defined only once in the httpd.conf file.

3.6.4 LogSQLForcePreserve

Syntax: LogSQLForcePreserve Flag

Example: LogSQLPreserveFile on

Default: off

Context: main server config

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.

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.

This is defined only once in the httpd.conf file.

3.6.5 LogSQLHeadersInLogTable

Syntax: LogSQLHeadersInLogTable table-name 

Example: LogSQLHeadersInLogTable headers

Default: headers_in

Context: virtual host

Defines which table is used for logging of inbound headers. Working in conjunction with LOGSQLWHICHHEADERSIN, 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_unique_id.

Note that you must create the table (see create-tables.sql, included in the package), or LOGSQLCREATETABLES must be set to ``on''.

3.6.6 LogSQLHeadersOutLogTable

Syntax: LogSQLHeadersOutLogTable table-name 

Example: LogSQLHeadersOutLogTable headers

Default: headers_out

Context: virtual host

Defines which table is used for logging of outbound headers. Working in conjunction with LOGSQLWHICHHEADERSOUT, 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_unique_id.

Note that you must create the table (see create-tables.sql, included in the package), or LOGSQLCREATETABLES must be set to ``on''.

3.6.7 LogSQLLoginInfo

MANDATORY 

Syntax: LogSQLLoginInfo host user password

Example: LogSQLLoginInfo foobar.baz.com logwriter passw0rd 

Context: main server config

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, and is simply ``localhost'' if the database lives on the same machine as Apache. ``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.

3.6.8 LogSQLMachineID

Syntax: LogSQLMachineID somename

Example: LogSQLMachineID web01

Context: main server config

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. LOGSQLMACHINEID permits you to distinguish each machine's entries if you assign each machine its own LOGSQLMACHINEID: for example, the first webserver gets ``LOGSQLMACHINEID web01,'' the second gets ``LOGSQLMACHINEID web02,'' etc.

This is defined only once in the httpd.conf file.

3.6.9 LogSQLMassVirtualHosting

Syntax: LogSQLMassVirtualHosting flag 

Example: LogSQLMassVirtualHosting On 

Default: Off 

Context: main server config

If you administer a site hosting many, many virtual hosts then this option will appeal to you. If you turn on LOGSQLMASSVIRTUALHOSTING then several things happen:

• the on-the-fly table creation feature is activated automatically
• 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)
• 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.

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.

You are advised to investigate the use of Apache's USECANONICALNAME ON directive with this directive in order to ensure that each virtual host maps to one table namespace.

This is defined only once in the httpd.conf file.

3.6.10 LogSQLNotesLogTable

Syntax: LogSQLNotesLogTable table-name 

Example: LogSQLNotesLogTable notes_log

Default: notes

Context: virtual host 

Defines which table is used for logging of notes. Working in conjunction with LOGSQLWHICHNOTES, 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.

Note that you must create the table (see create-tables.sql, included in the package), or LOGSQLCREATETABLES must be set to ``on''.

3.6.11 LogSQLPreserveFile

Syntax: LogSQLPreserveFile filename 

Example: LogSQLPreserveFile offline-preserve 

Default: /tmp/sql-preserve

Context: virtual host

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:

# mysql -uadminuser -p mydbname < /tmp/sql-preserve

If you do not define LOGSQLPRESERVEFILE then all virtual servers will log to the same default preserve file (/tmp/sql-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 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 LOGSQLTRANSFERLOGFORMAT directive. It is only necessary to segregate preserve-files by virualhost if you also segregate access logs by virtualhost.

The module will log to Apache's ERRORLOG 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).

3.6.12 LogSQLRemhostIgnore

Syntax: LogSQLRemhostIgnore host1 host2 host3 ... hostN 

Example: LogSQLRemhostIgnore localnet.com 

Context: virtual host

Lists a series of strings that, if present in the REMOTE_HOST, will cause that request to not 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 3.4.2 for some tips for using this directive.

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.

3.6.13 LogSQLRequestAccept

Syntax: LogSQLRequestAccept req1 req2 req3 ... reqN 

Example: LogSQLRequestAccept .html .php .jpg

Default: if not specified, all requests are ``accepted''

Context: virtual host

Lists a series of strings that, if present in the URI, will permit that request to be considered for logging (depending on additional filtering by the ``ignore'' directives). Any request that fails to match one of the LOGSQLREQUESTACCEPT entries will be discarded.

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 3.4.2 for some tips for using this directive.

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.

This directive is completely optional. It is more general than LOGSQLREQUESTIGNORE and is evaluated before LOGSQLREQUESTIGNORE. If this directive is not used, all 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.

3.6.14 LogSQLRequestIgnore

Syntax: LogSQLRequestIgnore req1 req2 req3 ... reqN 

Example: LogSQLRequestIgnore root.exe cmd.exe default.ida favicon.ico 

Context: virtual host

Lists a series of strings that, if present in the URI, will cause that request to NOT be 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 3.4.2 for some tips for using this directive.

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.

3.6.15 LogSQLSocketFile

Syntax: LogSQLSocketFile filename 

Example: LogSQLSocketFile /tmp/mysql.sock 

Default: /var/lib/mysql/mysql.sock 

Context: main server config

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.

This is defined only once in the httpd.conf file.

3.6.16 LogSQLTCPPort

Syntax: LogSQLTCPPort portnumber

Example: LogSQLTCPPort 3309

Default: 3306

Context: main server config

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.

This is defined only once in the httpd.conf file.

3.6.17 LogSQLTransferLogFormat

Syntax: LogSQLTransferLogFormat format-string 

Example: LogSQLTransferLogFormat huSUTv 

Default: AbHhmRSsTUuv 

Context: virtual host

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:

	What is this?	Data field	Column type	Example
A	User agent	agent	varchar(255)	Mozilla/4.0 (compat; MSIE 6.0; Windows)
a	CGI request arguments	request_args	varchar(255)	user=Smith&cart=1231&item=532
b	Bytes transfered	bytes_sent	int unsigned	32561
c	Text of cookie $^{\textrm{1}}$	cookie	varchar(255)	Apache=sdyn.fooonline.net.1300102700823
H	HTTP request protocol	request_protocol	varchar(10)	HTTP/1.1
h	Name of remote host	remote_host	varchar(50)	blah.foobar.com
I	Request ID (from mod_unique_id)	id	char(19)	POlFcUBRH30AAALdBG8
l	Ident user info	remote_logname	varchar(50)	bobby
M	Machine ID $^{\textrm{2}}$	machine_id	varchar(25)	web01
m	HTTP request method	request_method	varchar(6)	GET
P	httpd child PID	child_pid	smallint unsigned	3215
p	httpd port	server_port	smallint unsigned	80
R	Referer	referer	varchar(255)	http://www.biglinks4u.com/linkpage.html
r	Request in full form	request_line	varchar(255)	GET /books-cycroad.html HTTP/1.1
S	Time of request in UNIX format	time_stamp	int unsigned	1005598029
s	HTTP status of request	status	smallint unsigned	404
T	Seconds to service request	request_duration	smallint unsigned	2
t	Time of request in human format	request_time	char(28)	[02/Dec/2001:15:01:26 -0800]
U	Request in simple form	request_uri	varchar(255)	/books-cycroad.html
u	User info from HTTP auth	remote_user	varchar(50)	bobby
v	Virtual host servicing the request	virtual_host	varchar(50)	www.foobar.com

$^{\textrm{1}}$ You must also specify LOGSQLWHICHCOOKIE for this to take effect.

$^{\textrm{2}}$ You must also specify LOGSQLMACHINEID for this to take effect.

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

	What is this?	Data field	Column Type	Example
z	SSL cipher used	ssl_cipher	varchar(25)	RC4-MD5
q	Keysize of the SSL connection	ssl_keysize	smallint unsigned	56
Q	Maximum keysize supported	ssl_maxkeysize	smallint unsigned	128

3.6.18 LogSQLTransferLogTable

MANDATORY (unless LOGSQLMASSVIRTUALHOSTING is ``on'')

Syntax: LogSQLTransferLogTable table-name 

Example: LogSQLTransferLogTable access_log_table 

Context: virtual host

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.

3.6.19 LogSQLWhichCookie

Syntax: LogSQLWhichCookie cookiename 

Example: LogSQLWhichCookie Clicks

Default: None

Context: virtual host

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.

You must include a 'c' character in LOGSQLTRANSFERLOGFORMAT for this directive to take effect; once you specify 'c', LOGSQLWHICHCOOKIE 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; this cookie designates which one to log.

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 -any cookie at all - and it doesn't necessarily have to have anything to do with mod_usertrack.

3.6.20 LogSQLWhichCookies

Syntax: LogSQLWhichCookies cookie1 cookie2 ... cookieN

Example: LogSQLWhichCookies userlogin foobar foobaz

Default: None

Context: virtual host

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

Note that you must have already created the table (see create-tables.sql, included in the package), or LOGSQLCREATETABLES must be set to ``on''.

3.6.21 LogSQLWhichHeadersIn

Syntax: LogSQLWhichHeadersIn item1 item2 ... itemN

Example: LogSQLWhichHeadersIn UserAgent Accept-Encoding Host

Default: None

Context: virtual host

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

Note that you must have already created the table (see create-tables.sql, included in the package), or LOGSQLCREATETABLES must be set to ``on''.

3.6.22 LogSQLWhichHeadersOut

Syntax: LogSQLWhichHeadersOut item1 item2 ... itemN

Example: LogSQLWhichHeadersOut Expires Content-Type Cache-Control

Default: None

Context: virtual host

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

Note that you must have already created the table (see create-tables.sql, included in the package), or LOGSQLCREATETABLES must be set to ``on''.

3.6.23 LogSQLWhichNotes

Syntax: LogSQLWhichNotes item1 item2 ... itemN

Example: LogSQLWhichNotes mod_gzip_result mod_gzip_compression_ratio

Default: None

Context: virtual host

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

Note that you must have already created the table (see create-tables.sql, included in the package), or LOGSQLCREATETABLES must be set to ``on''.