summaryrefslogtreecommitdiffstatsabout
diff options
context:
space:
mode:
-rw-r--r--docs/manual.xml390
1 files changed, 203 insertions, 187 deletions
diff --git a/docs/manual.xml b/docs/manual.xml
index 4178dde..5ecaeda 100644
--- a/docs/manual.xml
+++ b/docs/manual.xml
@@ -1,6 +1,6 @@
1<?xml version="1.0" encoding="UTF-8"?> 1<?xml version="1.0" encoding="UTF-8"?>
2<?xml-stylesheet href="file://localhost/home/urkle/Documents/DocBook/docbook.css" type="text/css"?> 2<?xml-stylesheet href="file://localhost/home/urkle/Documents/DocBook/docbook.css" type="text/css"?>
3<!DOCTYPE article PUBLIC "-//OASIS//DTD DocBook XML V4.1.2//EN" "http://www.oasis-open.org/docbook/xml/4.1.2/docbookx.dtd" [ 3<!DOCTYPE article PUBLIC "-//OOOCC//DTD Simplified DocBook XML V1.0 Variant V1.0//EN" "http://outoforder.cc/dtds/odocbook/1.0/odocbook.dtd" [
4<!ENTITY EmailContact "<email>urkle &lt;at&gt; outoforder &lt;dot&gt; cc</email>"> 4<!ENTITY EmailContact "<email>urkle &lt;at&gt; outoforder &lt;dot&gt; cc</email>">
5]> 5]>
6<article> 6<article>
@@ -11,18 +11,22 @@
11 <surname>Rudd</surname> 11 <surname>Rudd</surname>
12 <contrib>Conversion from Lyx to DocBook</contrib> 12 <contrib>Conversion from Lyx to DocBook</contrib>
13 <contrib>Current Maintainer</contrib> 13 <contrib>Current Maintainer</contrib>
14 <affiliation> 14 <authorblurb>
15 <address format="linespecific">&EmailContact;</address> 15 <simpara>
16 </affiliation> 16 &EmailContact;
17 </simpara>
18 </authorblurb>
17 </author> 19 </author>
18 <author> 20 <author>
19 <firstname>Christopher</firstname> 21 <firstname>Christopher</firstname>
20 <othername>B.</othername> 22 <othername>B.</othername>
21 <surname>Powell</surname> 23 <surname>Powell</surname>
22 <contrib>Original documentation author.</contrib> 24 <contrib>Original documentation author.</contrib>
23 <affiliation> 25 <authorblurb>
24 <address format="linespecific"><email>chris &lt;at&gt; grubbybaby &lt;dot&gt; com</email></address> 26 <simpara>
25 </affiliation> 27 <email>chris &lt;at&gt; grubbybaby &lt;dot&gt; com</email>
28 </simpara>
29 </authorblurb>
26 </author> 30 </author>
27 <copyright> 31 <copyright>
28 <year>2001</year> 32 <year>2001</year>
@@ -52,25 +56,25 @@
52 </revision> 56 </revision>
53 </revhistory> 57 </revhistory>
54 </articleinfo> 58 </articleinfo>
55 <sect1> 59 <section>
56 <title>Introduction</title> 60 <title>Introduction</title>
57 <sect2> 61 <section>
58 <title>Summary</title> 62 <title>Summary</title>
59 <para>This Apache module will permit you to log to a SQL database; it can log each access request as well as data associated with each request: cookies, notes, and inbound/outbound headers. Unlike logging to a flat text file -- which is standard in Apache -- a SQL-based log exhibits tremendous flexibility and power of data extraction. (See FAQ entry <xref linkend="FAQ.WhyLogToSQL"/> for further discussion and examples of the advantages to SQL.)</para> 63 <para>This Apache module will permit you to log to a SQL database; it can log each access request as well as data associated with each request: cookies, notes, and inbound/outbound headers. Unlike logging to a flat text file -- which is standard in Apache -- a SQL-based log exhibits tremendous flexibility and power of data extraction. (See FAQ entry <xref linkend="FAQ.WhyLogToSQL"/> for further discussion and examples of the advantages to SQL.)</para>
60 <para>This module can either replace or happily coexist with mod_log_config, Apache's text file logging facility. In addition to being more configurable than the standard module, mod_log_sql is much more flexible.</para> 64 <para>This module can either replace or happily coexist with mod_log_config, Apache's text file logging facility. In addition to being more configurable than the standard module, mod_log_sql is much more flexible.</para>
61 </sect2> 65 </section>
62 <sect2> 66 <section>
63 <title>Approach</title> 67 <title>Approach</title>
64 <para>This project was formerly known as "mod_log_mysql." It was renamed "mod_log_sql" in order to reflect the project goal of database in-specificity. The module currently supports MySQL, but support for other database back-ends is underway.</para> 68 <para>This project was formerly known as "mod_log_mysql." It was renamed "mod_log_sql" in order to reflect the project goal of database in-specificity. The module currently supports MySQL, but support for other database back-ends is underway.</para>
65 <para>In order to save speed and overhead, links are kept alive in between queries. This module uses one dedicated SQL link per httpd child, opened by each child process when it is born. Among other things, this means that this module supports logging into only one MySQL server, and for now, also, only one SQL database. But that's a small tradeoff compared to the blinding speed of this module. Error reporting is robust throughout the module and will inform the administrator of database issues in the Apache ErrorLog for the server/virtual server.</para> 69 <para>In order to save speed and overhead, links are kept alive in between queries. This module uses one dedicated SQL link per httpd child, opened by each child process when it is born. Among other things, this means that this module supports logging into only one MySQL server, and for now, also, only one SQL database. But that's a small tradeoff compared to the blinding speed of this module. Error reporting is robust throughout the module and will inform the administrator of database issues in the Apache ErrorLog for the server/virtual server.</para>
66 <para>Virtual hosts are supported in the same manner they are in the regular logging modules. The administrator defines some basic 'global' directives in the main server config, then defines more specific 'local' directives inside each VirtualHost stanza.</para> 70 <para>Virtual hosts are supported in the same manner they are in the regular logging modules. The administrator defines some basic 'global' directives in the main server config, then defines more specific 'local' directives inside each VirtualHost stanza.</para>
67 <para>A robust "preserve" capability has now been implemented. This permits the module to preserve any failed INSERT commands to a local file on its machine. In any situation that the database is unavailable -- e.g. the network fails or the database host is rebooted -- mod_log_sql will note this in the error log and begin appending its log entries to the preserve file (which is created with the user and group ID of the running Apache process, e.g. "nobody/nobody" on many Linux installations). When database availability returns, mod_log_sql seamlessly resumes logging to it. When convenient for the sysadmin, he/she can easily import the preserve file into the database because it is simply a series of SQL insert statements.</para> 71 <para>A robust "preserve" capability has now been implemented. This permits the module to preserve any failed INSERT commands to a local file on its machine. In any situation that the database is unavailable -- e.g. the network fails or the database host is rebooted -- mod_log_sql will note this in the error log and begin appending its log entries to the preserve file (which is created with the user and group ID of the running Apache process, e.g. "nobody/nobody" on many Linux installations). When database availability returns, mod_log_sql seamlessly resumes logging to it. When convenient for the sysadmin, he/she can easily import the preserve file into the database because it is simply a series of SQL insert statements.</para>
68 </sect2> 72 </section>
69 <sect2> 73 <section>
70 <title>What gets logged by default?</title> 74 <title>What gets logged by default?</title>
71 <para>All the data that would be contained in the "Combined Log Format" is logged by default, plus a little extra. Your best bet is to begin by accepting this default, then later customize the log configuration based on your needs. The documentation of the run-time directives includes a full explanation of what you can log, including examples -- see section <xref endterm="Sect.ConfigReference.title" linkend="Sect.ConfigReference"/>.</para> 75 <para>All the data that would be contained in the "Combined Log Format" is logged by default, plus a little extra. Your best bet is to begin by accepting this default, then later customize the log configuration based on your needs. The documentation of the run-time directives includes a full explanation of what you can log, including examples -- see section <xref endterm="Sect.ConfigReference.title" linkend="Sect.ConfigReference"/>.</para>
72 </sect2> 76 </section>
73 <sect2> 77 <section>
74 <title>Miscellaneous Notes</title> 78 <title>Miscellaneous Notes</title>
75 <itemizedlist> 79 <itemizedlist>
76 <listitem> 80 <listitem>
@@ -79,7 +83,7 @@
79 <listitem> 83 <listitem>
80 <para>The 'time_stamp' field is stored in an UNSIGNED INTEGER format, in the standard unix "seconds since the epoch" format. This is superior to storing the access time as a string due to size requirements: an UNSIGNED INT requires 4 bytes, whereas an Apache date string (e.g. "18/Nov/2001:13:59:52 -0800") requires 26 bytes: those extra 22 bytes become significant when multiplied by thousands of accesses on a busy server. Besides, an INT type is far more flexible for comparisons, etc.</para> 84 <para>The 'time_stamp' field is stored in an UNSIGNED INTEGER format, in the standard unix "seconds since the epoch" format. This is superior to storing the access time as a string due to size requirements: an UNSIGNED INT requires 4 bytes, whereas an Apache date string (e.g. "18/Nov/2001:13:59:52 -0800") requires 26 bytes: those extra 22 bytes become significant when multiplied by thousands of accesses on a busy server. Besides, an INT type is far more flexible for comparisons, etc.</para>
81 <para>In MySQL 3.21 and above you can easily convert this to a human readable format using from_unixtime(), e.g.: </para> 85 <para>In MySQL 3.21 and above you can easily convert this to a human readable format using from_unixtime(), e.g.: </para>
82 <programlisting format="linespecific">select remote_host,request_uri,from_unixtime(time_stamp) from access_log; </programlisting> 86 <programlisting>select remote_host,request_uri,from_unixtime(time_stamp) from access_log; </programlisting>
83 <para>The enclosed perl program "make_combined_log.pl" extracts your access log in a format that is completely compatible with the Combined Log Format. You can then feed this to your favorite web log analysis tool.</para> 87 <para>The enclosed perl program "make_combined_log.pl" extracts your access log in a format that is completely compatible with the Combined Log Format. You can then feed this to your favorite web log analysis tool.</para>
84 </listitem> 88 </listitem>
85 <listitem> 89 <listitem>
@@ -92,23 +96,23 @@
92 <para>When Apache logs a numeric field, it uses a '-' character to mean "not applicable," e.g. the number of bytes returned on a 304 (unchanged) request. Since '-' is an illegal character in an SQL numeric field, such fields are assigned the value 0 instead of '-' which, of course, makes perfect sense anyway.</para> 96 <para>When Apache logs a numeric field, it uses a '-' character to mean "not applicable," e.g. the number of bytes returned on a 304 (unchanged) request. Since '-' is an illegal character in an SQL numeric field, such fields are assigned the value 0 instead of '-' which, of course, makes perfect sense anyway.</para>
93 </listitem> 97 </listitem>
94 </itemizedlist> 98 </itemizedlist>
95 </sect2> 99 </section>
96 <sect2> 100 <section>
97 <title>Author / Maintainer</title> 101 <title>Author / Maintainer</title>
98 <para>The actual logging code was taken from the already existing flat file text modules, so all that credit goes to the Apache Software Foundation.</para> 102 <para>The actual logging code was taken from the already existing flat file text modules, so all that credit goes to the Apache Software Foundation.</para>
99 <para>The MySQL routines and directives were added by Zeev Suraski &lt;bourbon@netvision.net.il&gt;. </para> 103 <para>The MySQL routines and directives were added by Zeev Suraski &lt;bourbon@netvision.net.il&gt;. </para>
100 <para>All changes from 1.06+ and the new documentation were added by Chris Powell <email>chris &lt;at&gt; grubbybaby &lt;dot&gt; com</email>. It seems that the module had fallen into the "un-maintained" category -- it had not been updated since 1998 -- so Chris adopted it as the new maintainer.</para> 104 <para>All changes from 1.06+ and the new documentation were added by Chris Powell <email>chris &lt;at&gt; grubbybaby &lt;dot&gt; com</email>. It seems that the module had fallen into the "un-maintained" category -- it had not been updated since 1998 -- so Chris adopted it as the new maintainer.</para>
101 <para>In December of 2003, Edward Rudd &EmailContact; porting the module to Apache 2.0, cleaning up the code, converting the documentation to DocBook, optimizing the main logging loop, and added the much anticipated database abstraction layer.</para> 105 <para>In December of 2003, Edward Rudd &EmailContact; porting the module to Apache 2.0, cleaning up the code, converting the documentation to DocBook, optimizing the main logging loop, and added the much anticipated database abstraction layer.</para>
102 <para>As of February 2004, Chris Powell handed over maintenance of the module over to Edward Rudd. So you should contact Edward Rudd about the module from now on.</para> 106 <para>As of February 2004, Chris Powell handed over maintenance of the module over to Edward Rudd. So you should contact Edward Rudd about the module from now on.</para>
103 </sect2> 107 </section>
104 <sect2 id="Sect.MailingLists"> 108 <section id="Sect.MailingLists">
105 <title id="Sect.MailingLists.title">Mailing Lists</title> 109 <title id="Sect.MailingLists.title">Mailing Lists</title>
106 <para>A general discussion and support mailing list is provided for mod_log_sq at lists.outoforder.cc. To subscribe to the mailing list send a blank e-mail to mod_log_sql-subscribe@lists.outoforder.cc. The list archives can be accessed via Gmane.org's mailng list gateway via any new reader <ulink url="news://news.gmane.org/gmane.comp.apache.mod-log-sql">news://news.gmane.org/gmane.comp.apache.mod-log-sql</ulink>, or via a web browser at <ulink url="http://news.gmane.org/gmane.comp.apache.mod-log-sql">http://news.gmane.org/gmane.comp.apache.mod-log-sql</ulink>.</para> 110 <para>A general discussion and support mailing list is provided for mod_log_sq at lists.outoforder.cc. To subscribe to the mailing list send a blank e-mail to mod_log_sql-subscribe@lists.outoforder.cc. The list archives can be accessed via Gmane.org's mailng list gateway via any new reader <ulink url="news://news.gmane.org/gmane.comp.apache.mod-log-sql">news://news.gmane.org/gmane.comp.apache.mod-log-sql</ulink>, or via a web browser at <ulink url="http://news.gmane.org/gmane.comp.apache.mod-log-sql">http://news.gmane.org/gmane.comp.apache.mod-log-sql</ulink>.</para>
107 </sect2> 111 </section>
108 </sect1> 112 </section>
109 <sect1> 113 <section>
110 <title>Installation</title> 114 <title>Installation</title>
111 <sect2> 115 <section>
112 <title>Requirements</title> 116 <title>Requirements</title>
113 <itemizedlist> 117 <itemizedlist>
114 <listitem> 118 <listitem>
@@ -127,18 +131,18 @@
127 <para>Optionally, if you want to be able to log SSL information such as keysize or cipher, you need OpenSSL and mod_ssl installed.</para> 131 <para>Optionally, if you want to be able to log SSL information such as keysize or cipher, you need OpenSSL and mod_ssl installed.</para>
128 </listitem> 132 </listitem>
129 </itemizedlist> 133 </itemizedlist>
130 </sect2> 134 </section>
131 <sect2> 135 <section>
132 <title>Compiling and Installing</title> 136 <title>Compiling and Installing</title>
133 <orderedlist> 137 <orderedlist>
134 <listitem> 138 <listitem>
135 <para>Unpack the archive into a working directory.</para> 139 <para>Unpack the archive into a working directory.</para>
136 <screen>$ tar -xzf mod_log_sql-1.94.tar.gz 140 <programlisting>$ tar -xzf mod_log_sql-1.94.tar.gz
137$ cd mod_log_sql-1.9</screen> 141$ cd mod_log_sql-1.9</programlisting>
138 </listitem> 142 </listitem>
139 <listitem> 143 <listitem>
140 <para>run configure to configure the source directory.</para> 144 <para>run configure to configure the source directory.</para>
141 <screen>$ ./configure</screen> 145 <programlisting>$ ./configure</programlisting>
142 <para>The <filename>configure</filename> script should automatically detect all the required libraries and program if the are installed in standard locations.. If it returns an error, here is a description of the arguments you can specify when you run <filename>configure</filename>.</para> 146 <para>The <filename>configure</filename> script should automatically detect all the required libraries and program if the are installed in standard locations.. If it returns an error, here is a description of the arguments you can specify when you run <filename>configure</filename>.</para>
143 <variablelist> 147 <variablelist>
144 <varlistentry> 148 <varlistentry>
@@ -176,9 +180,9 @@ $ cd mod_log_sql-1.9</screen>
176 <term>--with-db-inc=/usr/include/db1</term> 180 <term>--with-db-inc=/usr/include/db1</term>
177 <listitem> 181 <listitem>
178 <para>This argument is only needed when compiling SSL support for Apache 1.3, and needs to be the directory which contains the ndbm.h header file. You can find this by using </para> 182 <para>This argument is only needed when compiling SSL support for Apache 1.3, and needs to be the directory which contains the ndbm.h header file. You can find this by using </para>
179 <screen format="linespecific">$ locate ndbm.h 183 <programlisting format="linespecific">$ locate ndbm.h
180/usr/include/db1/ndbm.h 184/usr/include/db1/ndbm.h
181/usr/include/gdbm/ndbm.h</screen> 185/usr/include/gdbm/ndbm.h</programlisting>
182 <para>As far as I can tell, there is no difference as to which you specify, but it should be the one that you compiled mod_ssl with.</para> 186 <para>As far as I can tell, there is no difference as to which you specify, but it should be the one that you compiled mod_ssl with.</para>
183 <para>The default is <filename>/usr/include/db1</filename>, which should work on most systems.</para> 187 <para>The default is <filename>/usr/include/db1</filename>, which should work on most systems.</para>
184 </listitem> 188 </listitem>
@@ -199,18 +203,28 @@ $ cd mod_log_sql-1.9</screen>
199 </listitem> 203 </listitem>
200 <listitem> 204 <listitem>
201 <para>Now compile the module with GNU make. You may have to specify gmake on some systems like FreeBSD.</para> 205 <para>Now compile the module with GNU make. You may have to specify gmake on some systems like FreeBSD.</para>
202 <screen>$ gmake</screen> 206 <programlisting>$ gmake</programlisting>
203 </listitem> 207 </listitem>
204 <listitem> 208 <listitem>
205 <para>If there were no errors, you can now install the module(s). If you compiled as a non-root user you may need to switch users with <application>su</application> or <application>sudo</application>.</para> 209 <para>If there were no errors, you can now install the module(s). If you compiled as a non-root user you may need to switch users with <application>su</application> or <application>sudo</application>.</para>
206 <screen>$ su -c "gmake install" 210 <programlisting>$ su -c "gmake install"
207Password:</screen> 211Password:</programlisting>
208 </listitem> 212 </listitem>
209 <listitem> 213 <listitem>
210 <para>Now edit your Apache configuration and load the modules.</para> 214 <para>Now edit your Apache configuration and load the modules.</para>
211 <remark>If you are loading the SSL logging module, you need to make sure it is loaded after mod_ssl and mod_log_sql.</remark> 215 <note>
212 <remark>If you have previously used mod_log_sql version 1.18, the name of the module has changed from sql_log_module to log_sql_module (the first parameter to LoadModule)</remark> 216 <itemizedlist>
213 <remark>If you are upgrading from any release earlier than 1.97 you need to add an extra LoadModule directive to load the database driver (ie mysql).</remark> 217 <listitem>
218 <para>If you are loading the SSL logging module, you need to make sure it is loaded after mod_ssl and mod_log_sql.</para>
219 </listitem>
220 <listitem>
221 <para>If you have previously used mod_log_sql version 1.18, the name of the module has changed from sql_log_module to log_sql_module (the first parameter to LoadModule)</para>
222 </listitem>
223 <listitem>
224 <para>If you are upgrading from any release earlier than 1.97 you need to add an extra LoadModule directive to load the database driver (ie mysql).</para>
225 </listitem>
226 </itemizedlist>
227 </note>
214 <orderedlist> 228 <orderedlist>
215 <listitem> 229 <listitem>
216 <para>Insert these lines to either the main <filename>httpd.conf</filename> or a file included via an include directive.</para> 230 <para>Insert these lines to either the main <filename>httpd.conf</filename> or a file included via an include directive.</para>
@@ -219,7 +233,7 @@ LoadModule log_sql_mysql_module modules/mod_log_sql_mysql.so
219&lt;IfModule mod_ssl.c&gt; 233&lt;IfModule mod_ssl.c&gt;
220LoadModule log_sql_ssl_module moduels/mod_log_sql_ssl.so 234LoadModule log_sql_ssl_module moduels/mod_log_sql_ssl.so
221&lt;/IfModule&gt;</programlisting> 235&lt;/IfModule&gt;</programlisting>
222 <remark>If you did not compile SSL support in mod_log_sql, do not include the lines between the &lt;IfModule&gt; directives.</remark> 236 <note><para>If you did not compile SSL support in mod_log_sql, do not include the lines between the &lt;IfModule&gt; directives.</para></note>
223 </listitem> 237 </listitem>
224 <listitem> 238 <listitem>
225 <para>If you are using Apache 1.3 you may need add these lines later in the configuration.</para> 239 <para>If you are using Apache 1.3 you may need add these lines later in the configuration.</para>
@@ -228,16 +242,16 @@ AddModule mod_log_sql_mysql.c
228&lt;IfModule mod_ssl.c&gt; 242&lt;IfModule mod_ssl.c&gt;
229AddModule mod_log_sql_ssl.c 243AddModule mod_log_sql_ssl.c
230&lt;/IfModule&gt;</programlisting> 244&lt;/IfModule&gt;</programlisting>
231 <remark>If you did not compile SSL support in mod_log_sql, do not include the lines between the &lt;IfModule&gt; directives.</remark> 245 <note><para>If you did not compile SSL support in mod_log_sql, do not include the lines between the &lt;IfModule&gt; directives.</para></note>
232 </listitem> 246 </listitem>
233 </orderedlist> 247 </orderedlist>
234 </listitem> 248 </listitem>
235 </orderedlist> 249 </orderedlist>
236 </sect2> 250 </section>
237 </sect1> 251 </section>
238 <sect1 id="Sect.Configuration"> 252 <section id="Sect.Configuration">
239 <title id="Sect.Configuration.title">Configuration</title> 253 <title id="Sect.Configuration.title">Configuration</title>
240 <sect2 id="Sect.Preperation"> 254 <section id="Sect.Preperation">
241 <title id="Sect.Preperation.title">Preparing MySQL for logging</title> 255 <title id="Sect.Preperation.title">Preparing MySQL for logging</title>
242 <para>You have to prepare the database to receive data from <application>mod_log_sql</application>, and set up run-time directives in <filename>httpd.conf</filename> to control how and what <application>mod_log_sql</application> logs.</para> 256 <para>You have to prepare the database to receive data from <application>mod_log_sql</application>, and set up run-time directives in <filename>httpd.conf</filename> to control how and what <application>mod_log_sql</application> logs.</para>
243 <para>This section will discuss how to get started with a basic configuration. Full documentation of all available run-time directives is available in section <xref endterm="Sect.ConfigReference.title" linkend="Sect.ConfigReference"/>.</para> 257 <para>This section will discuss how to get started with a basic configuration. Full documentation of all available run-time directives is available in section <xref endterm="Sect.ConfigReference.title" linkend="Sect.ConfigReference"/>.</para>
@@ -247,33 +261,33 @@ AddModule mod_log_sql_ssl.c
247 </listitem> 261 </listitem>
248 <listitem> 262 <listitem>
249 <para>We still need to have a logging database created and ready, so run the MySQL command line client and create a database:</para> 263 <para>We still need to have a logging database created and ready, so run the MySQL command line client and create a database:</para>
250 <screen># mysql -uadmin -pmypassword 264 <programlisting># mysql -uadmin -pmypassword
251Enter password: 265Enter password:
252mysql&gt; create database apachelogs;</screen> 266mysql&gt; create database apachelogs;</programlisting>
253 </listitem> 267 </listitem>
254 <listitem id="Item.CreateTable"> 268 <listitem id="Item.CreateTable">
255 <para>If you want to hand-create the tables, run the enclosed 'create-tables' SQL script as follows ("create_tables.sql" needs to be in your current working directory).</para> 269 <para>If you want to hand-create the tables, run the enclosed 'create-tables' SQL script as follows ("create_tables.sql" needs to be in your current working directory).</para>
256 <screen>mysql&gt; use apachelogs 270 <programlisting>mysql&gt; use apachelogs
257Database changed 271Database changed
258mysql&gt; source create_tables.sql</screen> 272mysql&gt; source create_tables.sql</programlisting>
259 </listitem> 273 </listitem>
260 <listitem> 274 <listitem>
261 <para>Create a specific <application>MySQL</application> userid that <application>httpd</application> will use to authenticate and enter data. This userid need not be an actual Unix user. It is a userid internal to <application>MySQL</application> 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.</para> 275 <para>Create a specific <application>MySQL</application> userid that <application>httpd</application> will use to authenticate and enter data. This userid need not be an actual Unix user. It is a userid internal to <application>MySQL</application> 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.</para>
262 <screen>mysql&gt; grant insert,create on apachelogs.* to loguser@my.apachemachine.com identified by 'l0gger';</screen> 276 <programlisting>mysql&gt; grant insert,create on apachelogs.* to loguser@my.apachemachine.com identified by 'l0gger';</programlisting>
263 </listitem> 277 </listitem>
264 <listitem> 278 <listitem>
265 <para>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 <xref linkend="Item.CreateTable"/> and use the following GRANT statement instead of the one above:</para> 279 <para>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 <xref linkend="Item.CreateTable"/> and use the following GRANT statement instead of the one above:</para>
266 <screen>mysql&gt; grant insert on apachelogs.* to loguser@my.apachemachine.com identified by 'l0gger';</screen> 280 <programlisting>mysql&gt; grant insert on apachelogs.* to loguser@my.apachemachine.com identified by 'l0gger';</programlisting>
267 </listitem> 281 </listitem>
268 <listitem id="Item.EnableLogging"> 282 <listitem id="Item.EnableLogging">
269 <para>Enable full logging of your <application>MySQL</application> 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:</para> 283 <para>Enable full logging of your <application>MySQL</application> 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:</para>
270 <programlisting>log=/var/log/mysql-messages</programlisting> 284 <programlisting>log=/var/log/mysql-messages</programlisting>
271 <para>Then restart <application>MySQL</application></para> 285 <para>Then restart <application>MySQL</application></para>
272 <screen># /etc/rc.d/init.d/mysql restart</screen> 286 <programlisting># /etc/rc.d/init.d/mysql restart</programlisting>
273 </listitem> 287 </listitem>
274 </orderedlist> 288 </orderedlist>
275 </sect2> 289 </section>
276 <sect2> 290 <section>
277 <title>A very basic logging setup in Apache</title> 291 <title>A very basic logging setup in Apache</title>
278 <orderedlist> 292 <orderedlist>
279 <listitem> 293 <listitem>
@@ -300,28 +314,28 @@ LogSQLCreateTables on</programlisting>
300 </listitem> 314 </listitem>
301 <listitem> 315 <listitem>
302 <para>Restart apache.</para> 316 <para>Restart apache.</para>
303 <screen># /etc/rc.d/init.d/httpd stop 317 <programlisting># /etc/rc.d/init.d/httpd stop
304# /etc/rc.d/init.d/httpd start</screen> 318# /etc/rc.d/init.d/httpd start</programlisting>
305 </listitem> 319 </listitem>
306 </orderedlist> 320 </orderedlist>
307 </sect2> 321 </section>
308 <sect2> 322 <section>
309 <title>Testing the basic setup</title> 323 <title>Testing the basic setup</title>
310 <orderedlist> 324 <orderedlist>
311 <listitem> 325 <listitem>
312 <para>Visit your web site in a browser to trigger some hits, then confirm that the entries are being successfully logged:</para> 326 <para>Visit your web site in a browser to trigger some hits, then confirm that the entries are being successfully logged:</para>
313 <screen># mysql -hdbmachine.foo.com -umysqladmin -p -e "select * from access_log" apachelogs 327 <programlisting># mysql -hdbmachine.foo.com -umysqladmin -p -e "select * from access_log" apachelogs
314Enter password:</screen> 328Enter password:</programlisting>
315 <para>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 <xref linkend="FAQ.NothingLogged"/> of the FAQ on how to debug and fix the situation.</para> 329 <para>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 <xref linkend="FAQ.NothingLogged"/> of the FAQ on how to debug and fix the situation.</para>
316 </listitem> 330 </listitem>
317 <listitem> 331 <listitem>
318 <para>You can now activate the advanced features of mod_log_sql, which are described in the next section.</para> 332 <para>You can now activate the advanced features of mod_log_sql, which are described in the next section.</para>
319 </listitem> 333 </listitem>
320 </orderedlist> 334 </orderedlist>
321 </sect2> 335 </section>
322 <sect2> 336 <section>
323 <title>How to tune logging with run-time directives</title> 337 <title>How to tune logging with run-time directives</title>
324 <sect3> 338 <section>
325 <title>Instructing the module what to log</title> 339 <title>Instructing the module what to log</title>
326 <para>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.</para> 340 <para>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.</para>
327 <para>After LogSQLTransferLogFormat you supply a string of characters that tell the module what information to log. In the configuration directive reference (section <xref linkend="Conf.LogSQLTransferLogFormat"/>) 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:</para> 341 <para>After LogSQLTransferLogFormat you supply a string of characters that tell the module what information to log. In the configuration directive reference (section <xref linkend="Conf.LogSQLTransferLogFormat"/>) 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:</para>
@@ -339,9 +353,9 @@ Enter password:</screen>
339 <para>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.</para> 353 <para>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.</para>
340 </listitem> 354 </listitem>
341 </itemizedlist> 355 </itemizedlist>
342 </sect3> 356 </section>
343 <sect3 id="Sect.Ignore"> 357 <section id="Sect.Ignore">
344 <title id="Sect.Ignore.title" xreflabel="Using Filtering Directives">Instructing the module what NOT to log using filtering directives</title> 358 <title id="Sect.Ignore.title">Instructing the module what NOT to log using filtering directives</title>
345 <para>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.</para> 359 <para>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.</para>
346 <para><emphasis>It is important to remember that each of these three directives is purely optional. mod_log_sql's default is to log everything.</emphasis></para> 360 <para><emphasis>It is important to remember that each of these three directives is purely optional. mod_log_sql's default is to log everything.</emphasis></para>
347 <para>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." </para> 361 <para>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." </para>
@@ -366,23 +380,27 @@ Enter password:</screen>
366 <programlisting>LogSQLRequestAccept .html .gif .jpg 380 <programlisting>LogSQLRequestAccept .html .gif .jpg
367LogSQLRequestIgnore statistics.html bluedot.jpg</programlisting> 381LogSQLRequestIgnore statistics.html bluedot.jpg</programlisting>
368 <para>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.</para> 382 <para>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.</para>
369 <tip> 383 <note role="tip">
370 <para>If you want to match all the hosts in your domain such as "host1.corp.foo.com" and "server.dmz.foo.com", simply specify:</para> 384 <itemizedlist>
371 <programlisting format="linespecific">LogSQLRemhostIgnore foo.com</programlisting> 385 <listitem>
372 </tip> 386 <para>If you want to match all the hosts in your domain such as "host1.corp.foo.com" and "server.dmz.foo.com", simply specify:</para>
373 <tip> 387 <programlisting>LogSQLRemhostIgnore foo.com</programlisting>
374 <para>A great way to catch the vast majority of worm-attack requests and prevent them from being logged is to specify:</para> 388 </listitem>
375 <programlisting format="linespecific">LogSQLRequestIgnore root.exe cmd.exe default.ida</programlisting> 389 <listitem>
376 </tip> 390 <para>A great way to catch the vast majority of worm-attack requests and prevent them from being logged is to specify:</para>
377 <tip> 391 <programlisting>LogSQLRequestIgnore root.exe cmd.exe default.ida</programlisting>
378 <para>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:</para> 392 </listitem>
379 <programlisting format="linespecific">LogSQLRequestIgnore .gif .jpg</programlisting> 393 <listitem>
380 </tip> 394 <para>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:</para>
381 </sect3> 395 <programlisting>LogSQLRequestIgnore .gif .jpg</programlisting>
382 </sect2> 396 </listitem>
383 <sect2> 397 </itemizedlist>
398 </note>
399 </section>
400 </section>
401 <section>
384 <title>Advanced logging scenarios</title> 402 <title>Advanced logging scenarios</title>
385 <sect3> 403 <section>
386 <title>Using the module in an ISP environment</title> 404 <title>Using the module in an ISP environment</title>
387 <para>mod_log_sql has three basic tiers of operation:</para> 405 <para>mod_log_sql has three basic tiers of operation:</para>
388 <orderedlist> 406 <orderedlist>
@@ -407,16 +425,16 @@ LogSQLRequestIgnore statistics.html bluedot.jpg</programlisting>
407 </itemizedlist> 425 </itemizedlist>
408 <para>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.</para> 426 <para>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.</para>
409 <para>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.</para> 427 <para>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.</para>
410 </sect3> 428 </section>
411 <sect3 id="Sect.MultiTable"> 429 <section id="Sect.MultiTable">
412 <title id="Sect.MultiTable.title">Logging many-to-one data in separate tables</title> 430 <title id="Sect.MultiTable.title">Logging many-to-one data in separate tables</title>
413 <para>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.</para> 431 <para>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.</para>
414 <para>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.</para> 432 <para>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.</para>
415 <table> 433 <table>
416 <title>&lt;tblAcc&gt;access_log</title> 434 <title>&lt;tblAcc&gt;access_log</title>
417 <tgroup cols="6"> 435 <tgroup cols="6">
418 <colspec colname="1" colnum="1"/> 436 <colspec colname="1"/>
419 <colspec colname="2" colnum="2"/> 437 <colspec colname="2"/>
420 <colspec colname="3"/> 438 <colspec colname="3"/>
421 <colspec colname="4"/> 439 <colspec colname="4"/>
422 <colspec colname="5" colwidth="40"/> 440 <colspec colname="5" colwidth="40"/>
@@ -508,10 +526,10 @@ LogSQLRequestIgnore statistics.html bluedot.jpg</programlisting>
508 </tgroup> 526 </tgroup>
509 </table> 527 </table>
510 <para>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 [tblAcc], [tblNotes] and [tblHdr], 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:</para> 528 <para>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 [tblAcc], [tblNotes] and [tblHdr], 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:</para>
511 <screen>select a.remote_host, a.request_uri, n.item, n.val from access_log a, notes_log n 529 <programlisting>select a.remote_host, a.request_uri, n.item, n.val from access_log a, notes_log n
512where a.id=n.id and a.id='PPIDskBRH30AAGPtAsg';</screen> 530where a.id=n.id and a.id='PPIDskBRH30AAGPtAsg';</programlisting>
513 <table> 531 <table>
514 <title></title> 532 <title>access_log joined to notes_log</title>
515 <tgroup cols="4"> 533 <tgroup cols="4">
516 <colspec colname="1"/> 534 <colspec colname="1"/>
517 <colspec colname="2"/> 535 <colspec colname="2"/>
@@ -566,14 +584,14 @@ where a.id=n.id and a.id='PPIDskBRH30AAGPtAsg';</screen>
566&lt;/VirtualHost&gt;</programlisting> 584&lt;/VirtualHost&gt;</programlisting>
567 </listitem> 585 </listitem>
568 </itemizedlist> 586 </itemizedlist>
569 </sect3> 587 </section>
570 <sect3> 588 <section>
571 <title>Using the same database for production and test</title> 589 <title>Using the same database for production and test</title>
572 <para>Although sub-optimal, it is not uncommon to use the same back-end database for the "production" webservers as well as the "test" webservers (budgetary constraints, rack-space 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.</para> 590 <para>Although sub-optimal, it is not uncommon to use the same back-end database for the "production" webservers as well as the "test" webservers (budgetary constraints, rack-space 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.</para>
573 <para>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:</para> 591 <para>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:</para>
574 <programlisting>delete from access_log where machine_id like 'test%';</programlisting> 592 <programlisting>delete from access_log where machine_id like 'test%';</programlisting>
575 </sect3> 593 </section>
576 <sect3 id="Sect.DelayedInsert"> 594 <section id="Sect.DelayedInsert">
577 <title id="Sect.DelayedInsert.title">Optimizing for a busy database</title> 595 <title id="Sect.DelayedInsert.title">Optimizing for a busy database</title>
578 <para>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 enable mod_log_sql for "delayed inserts," which are described as follows in the MySQL documentation.</para> 596 <para>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 enable mod_log_sql for "delayed inserts," which are described as follows in the MySQL documentation.</para>
579 <para>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.</para> 597 <para>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.</para>
@@ -589,16 +607,16 @@ where a.id=n.id and a.id='PPIDskBRH30AAGPtAsg';</screen>
589 <para>There is additional overhead for the server to handle a separate thread for each table on which you use INSERT DELAYED.</para> 607 <para>There is additional overhead for the server to handle a separate thread for each table on which you use INSERT DELAYED.</para>
590 </listitem> 608 </listitem>
591 </orderedlist> 609 </orderedlist>
592 <warning> 610 <note role="warning">
593 <para>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 FAQ entry <xref linkend="FAQ.DelayedInsert"/> -- you have been warned.</para> 611 <para>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 FAQ entry <xref linkend="FAQ.DelayedInsert"/> -- you have been warned.</para>
594 </warning> 612 </note>
595 <para>If you are experiencing issues which could be solved by delayed inserts, then set LogSqlDelayedInserts On in the <filename>httpd.conf</filename>. All regular INSERT statements are now INSERT DELAYED, and you should see no more blocking of the module.</para> 613 <para>If you are experiencing issues which could be solved by delayed inserts, then set LogSqlDelayedInserts On in the <filename>httpd.conf</filename>. All regular INSERT statements are now INSERT DELAYED, and you should see no more blocking of the module.</para>
596 </sect3> 614 </section>
597 </sect2> 615 </section>
598 <sect2 id="Sect.ConfigReference"> 616 <section id="Sect.ConfigReference">
599 <title id="Sect.ConfigReference.title">Configuration Directive Reference</title> 617 <title id="Sect.ConfigReference.title">Configuration Directive Reference</title>
600 <para>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.</para> 618 <para>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.</para>
601 <sect3> 619 <section>
602 <title>DataBase Configuration</title> 620 <title>DataBase Configuration</title>
603 <variablelist> 621 <variablelist>
604 <varlistentry> 622 <varlistentry>
@@ -651,7 +669,7 @@ where a.id=n.id and a.id='PPIDskBRH30AAGPtAsg';</screen>
651 </varlistentry> 669 </varlistentry>
652 </variablelist> 670 </variablelist>
653 <note> 671 <note>
654 <para>This is defined only once in the <filename moreinfo="none">httpd.conf</filename> file.</para> 672 <para>This is defined only once in the <filename>httpd.conf</filename> file.</para>
655 <para>This directive Must be defined for logging to be enabled.</para> 673 <para>This directive Must be defined for logging to be enabled.</para>
656 </note> 674 </note>
657 </listitem> 675 </listitem>
@@ -660,9 +678,9 @@ where a.id=n.id and a.id='PPIDskBRH30AAGPtAsg';</screen>
660 <term>LogSQLDBParam</term> 678 <term>LogSQLDBParam</term>
661 <listitem> 679 <listitem>
662 <cmdsynopsis sepchar=" "> 680 <cmdsynopsis sepchar=" ">
663 <command moreinfo="none">LogSQLDBParam</command> 681 <command>LogSQLDBParam</command>
664 <arg choice="req" rep="norepeat"><replaceable>parameter-name</replaceable></arg> 682 <arg choice="req"><replaceable>parameter-name</replaceable></arg>
665 <arg choice="req" rep="norepeat"><replaceable>value</replaceable></arg> 683 <arg choice="req"><replaceable>value</replaceable></arg>
666 </cmdsynopsis> 684 </cmdsynopsis>
667 <simpara>Example: LogSQLDBParam socketfile /var/lib/mysql/mysql.socket</simpara> 685 <simpara>Example: LogSQLDBParam socketfile /var/lib/mysql/mysql.socket</simpara>
668 <simpara>Context: main server config</simpara> 686 <simpara>Context: main server config</simpara>
@@ -676,8 +694,8 @@ where a.id=n.id and a.id='PPIDskBRH30AAGPtAsg';</screen>
676 <term>LogSQLCreateTables</term> 694 <term>LogSQLCreateTables</term>
677 <listitem> 695 <listitem>
678 <cmdsynopsis sepchar=" "> 696 <cmdsynopsis sepchar=" ">
679 <command moreinfo="none">LogSQLCreateTables</command> 697 <command>LogSQLCreateTables</command>
680 <arg choice="req" rep="norepeat">flag</arg> 698 <arg choice="req">flag</arg>
681 </cmdsynopsis> 699 </cmdsynopsis>
682 <simpara>Example: LogSQLCreateTables On</simpara> 700 <simpara>Example: LogSQLCreateTables On</simpara>
683 <simpara>Default: Off</simpara> 701 <simpara>Default: Off</simpara>
@@ -685,7 +703,7 @@ where a.id=n.id and a.id='PPIDskBRH30AAGPtAsg';</screen>
685 <para>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).</para> 703 <para>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).</para>
686 <para>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.</para> 704 <para>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.</para>
687 <note> 705 <note>
688 <para>This is defined only once in the <filename moreinfo="none">httpd.conf</filename> file.</para> 706 <para>This is defined only once in the <filename>httpd.conf</filename> file.</para>
689 </note> 707 </note>
690 </listitem> 708 </listitem>
691 </varlistentry> 709 </varlistentry>
@@ -693,8 +711,8 @@ where a.id=n.id and a.id='PPIDskBRH30AAGPtAsg';</screen>
693 <term>LogSQLForcePreserve</term> 711 <term>LogSQLForcePreserve</term>
694 <listitem> 712 <listitem>
695 <cmdsynopsis sepchar=" "> 713 <cmdsynopsis sepchar=" ">
696 <command moreinfo="none">LogSQLForcePreserve</command> 714 <command>LogSQLForcePreserve</command>
697 <arg choice="req" rep="norepeat">flag</arg> 715 <arg choice="req">flag</arg>
698 </cmdsynopsis> 716 </cmdsynopsis>
699 <simpara>Example: LogForcePreserve On</simpara> 717 <simpara>Example: LogForcePreserve On</simpara>
700 <simpara>Default: Off</simpara> 718 <simpara>Default: Off</simpara>
@@ -702,7 +720,7 @@ where a.id=n.id and a.id='PPIDskBRH30AAGPtAsg';</screen>
702 <para>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.</para> 720 <para>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.</para>
703 <para>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.</para> 721 <para>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.</para>
704 <note> 722 <note>
705 <para>This is defined only once in the <filename moreinfo="none">httpd.conf</filename> file.</para> 723 <para>This is defined only once in the <filename>httpd.conf</filename> file.</para>
706 </note> 724 </note>
707 </listitem> 725 </listitem>
708 </varlistentry> 726 </varlistentry>
@@ -719,7 +737,7 @@ where a.id=n.id and a.id='PPIDskBRH30AAGPtAsg';</screen>
719 <para>This option can be enabled to completely disable the preserve file fail back. This may be useful for servers where the file-system is read-only.</para> 737 <para>This option can be enabled to completely disable the preserve file fail back. This may be useful for servers where the file-system is read-only.</para>
720 <para>If the database is not available those log entries will be lost.</para> 738 <para>If the database is not available those log entries will be lost.</para>
721 <note> 739 <note>
722 <para>This is defined only once in the <filename moreinfo="none">httpd.conf</filename> file.</para> 740 <para>This is defined only once in the <filename>httpd.conf</filename> file.</para>
723 </note> 741 </note>
724 </listitem> 742 </listitem>
725 </varlistentry> 743 </varlistentry>
@@ -727,14 +745,14 @@ where a.id=n.id and a.id='PPIDskBRH30AAGPtAsg';</screen>
727 <term>LogSQLMachineID</term> 745 <term>LogSQLMachineID</term>
728 <listitem> 746 <listitem>
729 <cmdsynopsis sepchar=" "> 747 <cmdsynopsis sepchar=" ">
730 <command moreinfo="none">LogSQLMachineID</command> 748 <command>LogSQLMachineID</command>
731 <arg choice="req" rep="norepeat">machineID</arg> 749 <arg choice="req">machineID</arg>
732 </cmdsynopsis> 750 </cmdsynopsis>
733 <simpara>Example: LogSQLMachineID web01</simpara> 751 <simpara>Example: LogSQLMachineID web01</simpara>
734 <simpara>Context: main server config</simpara> 752 <simpara>Context: main server config</simpara>
735 <para>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 load-balancing 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.</para> 753 <para>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 load-balancing 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.</para>
736 <note> 754 <note>
737 <para>This is defined only once in the <filename moreinfo="none">httpd.conf</filename> file.</para> 755 <para>This is defined only once in the <filename>httpd.conf</filename> file.</para>
738 </note> 756 </note>
739 </listitem> 757 </listitem>
740 </varlistentry> 758 </varlistentry>
@@ -742,30 +760,30 @@ where a.id=n.id and a.id='PPIDskBRH30AAGPtAsg';</screen>
742 <term>LogSQlPreserveFile</term> 760 <term>LogSQlPreserveFile</term>
743 <listitem> 761 <listitem>
744 <cmdsynopsis sepchar=" "> 762 <cmdsynopsis sepchar=" ">
745 <command moreinfo="none">LogSQLPreserveFile</command> 763 <command>LogSQLPreserveFile</command>
746 <arg choice="req" rep="norepeat"><replaceable>filename</replaceable></arg> 764 <arg choice="req"><replaceable>filename</replaceable></arg>
747 </cmdsynopsis> 765 </cmdsynopsis>
748 <simpara>Example: LogSQLPreserveFile offline-preserve</simpara> 766 <simpara>Example: LogSQLPreserveFile offline-preserve</simpara>
749 <simpara>Default: /tmp/sql-preserve</simpara> 767 <simpara>Default: /tmp/sql-preserve</simpara>
750 <simpara>Context: virtual host</simpara> 768 <simpara>Context: virtual host</simpara>
751 <para>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:</para> 769 <para>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:</para>
752 <screen format="linespecific"># mysql -uadminuser -p mydbname &lt; /tmp/sql-preserve</screen> 770 <programlisting format="linespecific"># mysql -uadminuser -p mydbname &lt; /tmp/sql-preserve</programlisting>
753 <para>If you do not define LogSQLPreserveFile then all virtual servers will log to the same default preserve file (<filename moreinfo="none">/tmp/sql-preserve</filename>). 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.</para> 771 <para>If you do not define LogSQLPreserveFile then all virtual servers will log to the same default preserve file (<filename>/tmp/sql-preserve</filename>). 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.</para>
754 <para>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.</para> 772 <para>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.</para>
755 <para>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 &amp; group ID of the running Apache process (e.g. 'nobody' on many Linux distributions).</para> 773 <para>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 &amp; group ID of the running Apache process (e.g. 'nobody' on many Linux distributions).</para>
756 </listitem> 774 </listitem>
757 </varlistentry> 775 </varlistentry>
758 </variablelist> 776 </variablelist>
759 </sect3> 777 </section>
760 <sect3> 778 <section>
761 <title>Table Names</title> 779 <title>Table Names</title>
762 <variablelist> 780 <variablelist>
763 <varlistentry> 781 <varlistentry>
764 <term>LogSQLTransferLogTable</term> 782 <term>LogSQLTransferLogTable</term>
765 <listitem> 783 <listitem>
766 <cmdsynopsis sepchar=" "> 784 <cmdsynopsis sepchar=" ">
767 <command moreinfo="none">LogSQLTransferLogTable</command> 785 <command>LogSQLTransferLogTable</command>
768 <arg choice="req" rep="norepeat"><replaceable>table-name</replaceable></arg> 786 <arg choice="req"><replaceable>table-name</replaceable></arg>
769 </cmdsynopsis> 787 </cmdsynopsis>
770 <simpara>Example: LogSQLTransferLogTable access_log_table</simpara> 788 <simpara>Example: LogSQLTransferLogTable access_log_table</simpara>
771 <simpara>Context: virtual host</simpara> 789 <simpara>Context: virtual host</simpara>
@@ -780,8 +798,8 @@ where a.id=n.id and a.id='PPIDskBRH30AAGPtAsg';</screen>
780 <term>LogSQLCookieLogTable</term> 798 <term>LogSQLCookieLogTable</term>
781 <listitem> 799 <listitem>
782 <cmdsynopsis sepchar=" "> 800 <cmdsynopsis sepchar=" ">
783 <command moreinfo="none">LogSQLCookieLogTable</command> 801 <command>LogSQLCookieLogTable</command>
784 <arg choice="req" rep="norepeat"><replaceable></replaceable>table-name</arg> 802 <arg choice="req"><replaceable></replaceable>table-name</arg>
785 </cmdsynopsis> 803 </cmdsynopsis>
786 <simpara>Example: LogSQLCookieLogTable cookie_log</simpara> 804 <simpara>Example: LogSQLCookieLogTable cookie_log</simpara>
787 <simpara>Default: cookies</simpara> 805 <simpara>Default: cookies</simpara>
@@ -796,8 +814,8 @@ where a.id=n.id and a.id='PPIDskBRH30AAGPtAsg';</screen>
796 <term>LogSQLHeadersInLogTable</term> 814 <term>LogSQLHeadersInLogTable</term>
797 <listitem> 815 <listitem>
798 <cmdsynopsis sepchar=" "> 816 <cmdsynopsis sepchar=" ">
799 <command moreinfo="none">LogSQLHeadersInLogTable</command> 817 <command>LogSQLHeadersInLogTable</command>
800 <arg choice="req" rep="norepeat"><replaceable>table-name</replaceable></arg> 818 <arg choice="req"><replaceable>table-name</replaceable></arg>
801 </cmdsynopsis> 819 </cmdsynopsis>
802 <simpara>Example: LogSQLHeadersInLogTable headers</simpara> 820 <simpara>Example: LogSQLHeadersInLogTable headers</simpara>
803 <simpara>Default: headers_in</simpara> 821 <simpara>Default: headers_in</simpara>
@@ -812,8 +830,8 @@ where a.id=n.id and a.id='PPIDskBRH30AAGPtAsg';</screen>
812 <term>LogSQLHeadersOutLogTable</term> 830 <term>LogSQLHeadersOutLogTable</term>
813 <listitem> 831 <listitem>
814 <cmdsynopsis sepchar=" "> 832 <cmdsynopsis sepchar=" ">
815 <command moreinfo="none">LogSQLHeadersOutLogTable</command> 833 <command>LogSQLHeadersOutLogTable</command>
816 <arg choice="req" rep="norepeat"><replaceable>table-name</replaceable></arg> 834 <arg choice="req"><replaceable>table-name</replaceable></arg>
817 </cmdsynopsis> 835 </cmdsynopsis>
818 <simpara>Example: LogSQLHeadersOutLogTable headers</simpara> 836 <simpara>Example: LogSQLHeadersOutLogTable headers</simpara>
819 <simpara>Default: headers_out</simpara> 837 <simpara>Default: headers_out</simpara>
@@ -828,8 +846,8 @@ where a.id=n.id and a.id='PPIDskBRH30AAGPtAsg';</screen>
828 <term>LogSQLNotesLogTable</term> 846 <term>LogSQLNotesLogTable</term>
829 <listitem> 847 <listitem>
830 <cmdsynopsis sepchar=" "> 848 <cmdsynopsis sepchar=" ">
831 <command moreinfo="none">LogSQLNotesLogTable</command> 849 <command>LogSQLNotesLogTable</command>
832 <arg choice="req" rep="norepeat"><replaceable>table-name</replaceable></arg> 850 <arg choice="req"><replaceable>table-name</replaceable></arg>
833 </cmdsynopsis> 851 </cmdsynopsis>
834 <simpara>Example: LogSQLNotesLogTable notes-log</simpara> 852 <simpara>Example: LogSQLNotesLogTable notes-log</simpara>
835 <simpara>Default: notes</simpara> 853 <simpara>Default: notes</simpara>
@@ -844,8 +862,8 @@ where a.id=n.id and a.id='PPIDskBRH30AAGPtAsg';</screen>
844 <term>LogSQLMassVirtualHosting</term> 862 <term>LogSQLMassVirtualHosting</term>
845 <listitem> 863 <listitem>
846 <cmdsynopsis sepchar=" "> 864 <cmdsynopsis sepchar=" ">
847 <command moreinfo="none">LogSQLMassVirtualHosting</command> 865 <command>LogSQLMassVirtualHosting</command>
848 <arg choice="req" rep="norepeat">flag</arg> 866 <arg choice="req">flag</arg>
849 </cmdsynopsis> 867 </cmdsynopsis>
850 <simpara>Example: LogSQLMassVirtualHosting On</simpara> 868 <simpara>Example: LogSQLMassVirtualHosting On</simpara>
851 <simpara>Default: Off</simpara> 869 <simpara>Default: Off</simpara>
@@ -864,21 +882,21 @@ where a.id=n.id and a.id='PPIDskBRH30AAGPtAsg';</screen>
864 </itemizedlist> 882 </itemizedlist>
865 <para>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.</para> 883 <para>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.</para>
866 <note> 884 <note>
867 <para>This is defined only once in the <filename moreinfo="none">httpd.conf</filename> file.</para> 885 <para>This is defined only once in the <filename>httpd.conf</filename> file.</para>
868 </note> 886 </note>
869 </listitem> 887 </listitem>
870 </varlistentry> 888 </varlistentry>
871 </variablelist> 889 </variablelist>
872 </sect3> 890 </section>
873 <sect3> 891 <section>
874 <title>Configuring What Is logged</title> 892 <title>Configuring What Is logged</title>
875 <variablelist> 893 <variablelist>
876 <varlistentry id="Conf.LogSQLTransferLogFormat"> 894 <varlistentry id="Conf.LogSQLTransferLogFormat">
877 <term>LogSQLTransferLogFormat </term> 895 <term>LogSQLTransferLogFormat </term>
878 <listitem> 896 <listitem>
879 <cmdsynopsis sepchar=" "> 897 <cmdsynopsis sepchar=" ">
880 <command moreinfo="none">LogSQLTransferLogFormat</command> 898 <command>LogSQLTransferLogFormat</command>
881 <arg choice="req" rep="norepeat"><replaceable>format-string</replaceable></arg> 899 <arg choice="req"><replaceable>format-string</replaceable></arg>
882 </cmdsynopsis> 900 </cmdsynopsis>
883 <simpara>Example: LogSQLTransferLogFormat huSUTv</simpara> 901 <simpara>Example: LogSQLTransferLogFormat huSUTv</simpara>
884 <simpara>Default: AbHhmRSsTUuv</simpara> 902 <simpara>Default: AbHhmRSsTUuv</simpara>
@@ -924,7 +942,7 @@ where a.id=n.id and a.id='PPIDskBRH30AAGPtAsg';</screen>
924 <entry colname="5">32561</entry> 942 <entry colname="5">32561</entry>
925 </row> 943 </row>
926 <row> 944 <row>
927 <entry colname="1">c<xref linkend="Foot.LogCookie" xreflabel="[1]"/></entry> 945 <entry colname="1">c<xref linkend="Foot.LogCookie"/></entry>
928 <entry colname="2">Text of cookie</entry> 946 <entry colname="2">Text of cookie</entry>
929 <entry colname="3">cookie</entry> 947 <entry colname="3">cookie</entry>
930 <entry colname="4">varchar(255)</entry> 948 <entry colname="4">varchar(255)</entry>
@@ -967,7 +985,7 @@ where a.id=n.id and a.id='PPIDskBRH30AAGPtAsg';</screen>
967 </row> 985 </row>
968 <row> 986 <row>
969 <entry>M</entry> 987 <entry>M</entry>
970 <entry>Machine ID<xref linkend="Foot.MachineID" xreflabel="[2]"/></entry> 988 <entry>Machine ID<xref linkend="Foot.MachineID"/></entry>
971 <entry>machine_id</entry> 989 <entry>machine_id</entry>
972 <entry>varchar(25)</entry> 990 <entry>varchar(25)</entry>
973 <entry>web01</entry> 991 <entry>web01</entry>
@@ -1059,11 +1077,9 @@ where a.id=n.id and a.id='PPIDskBRH30AAGPtAsg';</screen>
1059 </tbody> 1077 </tbody>
1060 </tgroup> 1078 </tgroup>
1061 </table> 1079 </table>
1062 <note id="Foot.LogCookie"> 1080 <note>
1063 <simpara>[1] You must also specify LogSQLWhichCookie for this to take effect.</simpara> 1081 <simpara id="Foot.LogCookie">[1] You must also specify LogSQLWhichCookie for this to take effect.</simpara>
1064 </note> 1082 <simpara id="Foot.MachineID">[2] You must also specify LogSQLmachineID for this to take effect.</simpara>
1065 <note id="Foot.MachineID">
1066 <simpara>[2] You must also specify LogSQLmachineID for this to take effect.</simpara>
1067 </note> 1083 </note>
1068 <table> 1084 <table>
1069 <title>SSL LogFormat Parameters</title> 1085 <title>SSL LogFormat Parameters</title>
@@ -1113,12 +1129,12 @@ where a.id=n.id and a.id='PPIDskBRH30AAGPtAsg';</screen>
1113 <term>LogSQLRemhostIgnore</term> 1129 <term>LogSQLRemhostIgnore</term>
1114 <listitem> 1130 <listitem>
1115 <cmdsynopsis sepchar=" "> 1131 <cmdsynopsis sepchar=" ">
1116 <command moreinfo="none">LogSQLRemhostIgnore</command> 1132 <command>LogSQLRemhostIgnore</command>
1117 <arg choice="req" rep="repeat"><replaceable>hostname</replaceable></arg> 1133 <arg choice="req" rep="repeat"><replaceable>hostname</replaceable></arg>
1118 </cmdsynopsis> 1134 </cmdsynopsis>
1119 <simpara>Example: LogSQLRemhostIgnore localnet.com</simpara> 1135 <simpara>Example: LogSQLRemhostIgnore localnet.com</simpara>
1120 <simpara>Context: virtual host</simpara> 1136 <simpara>Context: virtual host</simpara>
1121 <para>Lists a series of strings that, if present in the REMOTE_HOST, will cause that request to <emphasis>not</emphasis> 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 <xref endterm="Sect.Ignore.title" linkend="Sect.Ignore"/> for some tips for using this directive.</para> 1137 <para>Lists a series of smortrings that, if present in the REMOTE_HOST, will cause that request to <emphasis>not</emphasis> 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 <xref endterm="Sect.Ignore.title" linkend="Sect.Ignore"/> for some tips for using this directive.</para>
1122 <para>Each string may contain a + or - prefix in a &lt;VirtualHost&gt; context and will cause those strings to be added (+) or removed (-) from the global configuration. Otherwise the global is completely ignored and overridden if defined in a &lt;VirtualHost&gt;</para> 1138 <para>Each string may contain a + or - prefix in a &lt;VirtualHost&gt; context and will cause those strings to be added (+) or removed (-) from the global configuration. Otherwise the global is completely ignored and overridden if defined in a &lt;VirtualHost&gt;</para>
1123 <para>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.</para> 1139 <para>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.</para>
1124 </listitem> 1140 </listitem>
@@ -1127,7 +1143,7 @@ where a.id=n.id and a.id='PPIDskBRH30AAGPtAsg';</screen>
1127 <term>LogSQLRequestAccept</term> 1143 <term>LogSQLRequestAccept</term>
1128 <listitem> 1144 <listitem>
1129 <cmdsynopsis sepchar=" "> 1145 <cmdsynopsis sepchar=" ">
1130 <command moreinfo="none">LogSQLRequestAccept</command> 1146 <command>LogSQLRequestAccept</command>
1131 <arg choice="req" rep="repeat"><replaceable>substring</replaceable></arg> 1147 <arg choice="req" rep="repeat"><replaceable>substring</replaceable></arg>
1132 </cmdsynopsis> 1148 </cmdsynopsis>
1133 <simpara>Example: LogSQLRequestAccept .html .php .jpg</simpara> 1149 <simpara>Example: LogSQLRequestAccept .html .php .jpg</simpara>
@@ -1144,7 +1160,7 @@ where a.id=n.id and a.id='PPIDskBRH30AAGPtAsg';</screen>
1144 <term>LogSQLRequestIgnore</term> 1160 <term>LogSQLRequestIgnore</term>
1145 <listitem> 1161 <listitem>
1146 <cmdsynopsis sepchar=" "> 1162 <cmdsynopsis sepchar=" ">
1147 <command moreinfo="none">LogSQLRequestIgnore</command> 1163 <command>LogSQLRequestIgnore</command>
1148 <arg choice="req" rep="repeat"><replaceable>substring</replaceable></arg> 1164 <arg choice="req" rep="repeat"><replaceable>substring</replaceable></arg>
1149 </cmdsynopsis> 1165 </cmdsynopsis>
1150 <simpara>Example: LogSQLRequestIgnore root.exe cmd.exe default.ida favicon.ico</simpara> 1166 <simpara>Example: LogSQLRequestIgnore root.exe cmd.exe default.ida favicon.ico</simpara>
@@ -1158,8 +1174,8 @@ where a.id=n.id and a.id='PPIDskBRH30AAGPtAsg';</screen>
1158 <term>LogSQLWhichCookie</term> 1174 <term>LogSQLWhichCookie</term>
1159 <listitem> 1175 <listitem>
1160 <cmdsynopsis sepchar=" "> 1176 <cmdsynopsis sepchar=" ">
1161 <command moreinfo="none">LogSQLWhichCookie</command> 1177 <command>LogSQLWhichCookie</command>
1162 <arg choice="req" rep="norepeat"><replaceable>cookiename</replaceable></arg> 1178 <arg choice="req"><replaceable>cookiename</replaceable></arg>
1163 </cmdsynopsis> 1179 </cmdsynopsis>
1164 <simpara>Example; LogSQLWhichCookie Clicks</simpara> 1180 <simpara>Example; LogSQLWhichCookie Clicks</simpara>
1165 <simpara>Context: virtual host</simpara> 1181 <simpara>Context: virtual host</simpara>
@@ -1175,7 +1191,7 @@ where a.id=n.id and a.id='PPIDskBRH30AAGPtAsg';</screen>
1175 <term>LogSQLWhichCookies</term> 1191 <term>LogSQLWhichCookies</term>
1176 <listitem> 1192 <listitem>
1177 <cmdsynopsis sepchar=" "> 1193 <cmdsynopsis sepchar=" ">
1178 <command moreinfo="none">LogSQLWhichCookies</command> 1194 <command>LogSQLWhichCookies</command>
1179 <arg choice="req" rep="repeat"><replaceable>cookie-name</replaceable></arg> 1195 <arg choice="req" rep="repeat"><replaceable>cookie-name</replaceable></arg>
1180 </cmdsynopsis> 1196 </cmdsynopsis>
1181 <simpara>Example: logSQLWhichCookies userlogin cookie1 cookie2</simpara> 1197 <simpara>Example: logSQLWhichCookies userlogin cookie1 cookie2</simpara>
@@ -1191,7 +1207,7 @@ where a.id=n.id and a.id='PPIDskBRH30AAGPtAsg';</screen>
1191 <term>LogSQLWhichHeadersIn</term> 1207 <term>LogSQLWhichHeadersIn</term>
1192 <listitem> 1208 <listitem>
1193 <cmdsynopsis sepchar=" "> 1209 <cmdsynopsis sepchar=" ">
1194 <command moreinfo="none">LogSQLWhichHeadersIn</command> 1210 <command>LogSQLWhichHeadersIn</command>
1195 <arg choice="req" rep="repeat"><replaceable>header-name</replaceable></arg> 1211 <arg choice="req" rep="repeat"><replaceable>header-name</replaceable></arg>
1196 </cmdsynopsis> 1212 </cmdsynopsis>
1197 <simpara>Example: LogSQLWhichHeadersIn UserAgent Accept-Encodeing Host</simpara> 1213 <simpara>Example: LogSQLWhichHeadersIn UserAgent Accept-Encodeing Host</simpara>
@@ -1207,7 +1223,7 @@ where a.id=n.id and a.id='PPIDskBRH30AAGPtAsg';</screen>
1207 <term>LogSQLWhichHeadersOut</term> 1223 <term>LogSQLWhichHeadersOut</term>
1208 <listitem> 1224 <listitem>
1209 <cmdsynopsis sepchar=" "> 1225 <cmdsynopsis sepchar=" ">
1210 <command moreinfo="none">LogSQLWhichHeadersOut</command> 1226 <command>LogSQLWhichHeadersOut</command>
1211 <arg choice="req" rep="repeat"><replaceable>header-name</replaceable></arg> 1227 <arg choice="req" rep="repeat"><replaceable>header-name</replaceable></arg>
1212 </cmdsynopsis> 1228 </cmdsynopsis>
1213 <simpara>Example: LogSQLWhichHeadersOut Expires Content-Type Cache-Control</simpara> 1229 <simpara>Example: LogSQLWhichHeadersOut Expires Content-Type Cache-Control</simpara>
@@ -1223,7 +1239,7 @@ where a.id=n.id and a.id='PPIDskBRH30AAGPtAsg';</screen>
1223 <term>LogSQLWhichNotes</term> 1239 <term>LogSQLWhichNotes</term>
1224 <listitem> 1240 <listitem>
1225 <cmdsynopsis sepchar=" "> 1241 <cmdsynopsis sepchar=" ">
1226 <command moreinfo="none">LogSQLWhichNotes</command> 1242 <command>LogSQLWhichNotes</command>
1227 <arg choice="req" rep="repeat"><replaceable>note-name</replaceable></arg> 1243 <arg choice="req" rep="repeat"><replaceable>note-name</replaceable></arg>
1228 </cmdsynopsis> 1244 </cmdsynopsis>
1229 <simpara>Example: LogSQLWhichNotes mod_gzip_result mod_gzip_ompression_ratio</simpara> 1245 <simpara>Example: LogSQLWhichNotes mod_gzip_result mod_gzip_ompression_ratio</simpara>
@@ -1236,16 +1252,16 @@ where a.id=n.id and a.id='PPIDskBRH30AAGPtAsg';</screen>
1236 </listitem> 1252 </listitem>
1237 </varlistentry> 1253 </varlistentry>
1238 </variablelist> 1254 </variablelist>
1239 </sect3> 1255 </section>
1240 <sect3> 1256 <section>
1241 <title>Deprecated Command</title> 1257 <title>Deprecated Commands</title>
1242 <variablelist> 1258 <variablelist>
1243 <varlistentry> 1259 <varlistentry>
1244 <term>LogSQLSocketFile [Deprecated]</term> 1260 <term>LogSQLSocketFile [Deprecated]</term>
1245 <listitem> 1261 <listitem>
1246 <cmdsynopsis sepchar=" "> 1262 <cmdsynopsis sepchar=" ">
1247 <command moreinfo="none">LogSQLSocketFile</command> 1263 <command>LogSQLSocketFile</command>
1248 <arg choice="req" rep="norepeat"><replaceable>filename</replaceable></arg> 1264 <arg choice="req"><replaceable>filename</replaceable></arg>
1249 </cmdsynopsis> 1265 </cmdsynopsis>
1250 <simpara>Example: LogSQLSocketFile /tmp/mysql.sock</simpara> 1266 <simpara>Example: LogSQLSocketFile /tmp/mysql.sock</simpara>
1251 <simpara>Default: (database specific)</simpara> 1267 <simpara>Default: (database specific)</simpara>
@@ -1255,7 +1271,7 @@ where a.id=n.id and a.id='PPIDskBRH30AAGPtAsg';</screen>
1255 <para>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.</para> 1271 <para>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.</para>
1256 <note> 1272 <note>
1257 <para>This directive is deprecated in favor of LogSQLDBParam socketfile [socketfilename]</para> 1273 <para>This directive is deprecated in favor of LogSQLDBParam socketfile [socketfilename]</para>
1258 <para>This is defined only once in the <filename moreinfo="none">httpd.conf</filename> file.</para> 1274 <para>This is defined only once in the <filename>httpd.conf</filename> file.</para>
1259 </note> 1275 </note>
1260 </listitem> 1276 </listitem>
1261 </varlistentry> 1277 </varlistentry>
@@ -1263,8 +1279,8 @@ where a.id=n.id and a.id='PPIDskBRH30AAGPtAsg';</screen>
1263 <term>LogSQLTCPPort [Deprecated]</term> 1279 <term>LogSQLTCPPort [Deprecated]</term>
1264 <listitem> 1280 <listitem>
1265 <cmdsynopsis sepchar=" "> 1281 <cmdsynopsis sepchar=" ">
1266 <command moreinfo="none">LogSQLTCPPort</command> 1282 <command>LogSQLTCPPort</command>
1267 <arg choice="req" rep="norepeat"><replaceable>port-number</replaceable></arg> 1283 <arg choice="req"><replaceable>port-number</replaceable></arg>
1268 </cmdsynopsis> 1284 </cmdsynopsis>
1269 <simpara>Example: LogSQLTCPPort 3309</simpara> 1285 <simpara>Example: LogSQLTCPPort 3309</simpara>
1270 <simpara>Default: (database specific)</simpara> 1286 <simpara>Default: (database specific)</simpara>
@@ -1273,7 +1289,7 @@ where a.id=n.id and a.id='PPIDskBRH30AAGPtAsg';</screen>
1273 <para>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.</para> 1289 <para>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.</para>
1274 <note> 1290 <note>
1275 <para>This directive is deprecated in favor of LogSQLDBParam tcpport [port-number]</para> 1291 <para>This directive is deprecated in favor of LogSQLDBParam tcpport [port-number]</para>
1276 <para>This is defined only once in the <filename moreinfo="none">httpd.conf</filename> file.</para> 1292 <para>This is defined only once in the <filename>httpd.conf</filename> file.</para>
1277 </note> 1293 </note>
1278 </listitem> 1294 </listitem>
1279 </varlistentry> 1295 </varlistentry>
@@ -1281,23 +1297,23 @@ where a.id=n.id and a.id='PPIDskBRH30AAGPtAsg';</screen>
1281 <term>LogSQLDatabase [Deprecated]</term> 1297 <term>LogSQLDatabase [Deprecated]</term>
1282 <listitem> 1298 <listitem>
1283 <cmdsynopsis sepchar=" "> 1299 <cmdsynopsis sepchar=" ">
1284 <command moreinfo="none">LogSQLDatabase</command> 1300 <command>LogSQLDatabase</command>
1285 <arg choice="req" rep="norepeat"><replaceable>database</replaceable></arg> 1301 <arg choice="req"><replaceable>database</replaceable></arg>
1286 </cmdsynopsis> 1302 </cmdsynopsis>
1287 <simpara>Example: LogSQLDatabase loggingdb</simpara> 1303 <simpara>Example: LogSQLDatabase loggingdb</simpara>
1288 <simpara>Context: main server config</simpara> 1304 <simpara>Context: main server config</simpara>
1289 <para>Defines the database that is used for logging. "database" must be a valid db on the MySQL host defined in LogSQLLoginInfo</para> 1305 <para>Defines the database that is used for logging. "database" must be a valid db on the MySQL host defined in LogSQLLoginInfo</para>
1290 <note> 1306 <note>
1291 <para>This directive is deprecated in favor of the URI form of LogSQLLoginInfo.</para> 1307 <para>This directive is deprecated in favor of the URI form of LogSQLLoginInfo.</para>
1292 <para>This is defined only once in the <filename moreinfo="none">httpd.conf</filename> file.</para> 1308 <para>This is defined only once in the <filename>httpd.conf</filename> file.</para>
1293 </note> 1309 </note>
1294 </listitem> 1310 </listitem>
1295 </varlistentry> 1311 </varlistentry>
1296 </variablelist> 1312 </variablelist>
1297 </sect3> 1313 </section>
1298 </sect2> 1314 </section>
1299 </sect1> 1315 </section>
1300 <sect1> 1316 <section id="Sect.FAQ">
1301 <title>FAQ</title> 1317 <title>FAQ</title>
1302 <qandaset> 1318 <qandaset>
1303 <qandadiv> 1319 <qandadiv>
@@ -1320,7 +1336,7 @@ where a.id=n.id and a.id='PPIDskBRH30AAGPtAsg';</screen>
1320 </listitem> 1336 </listitem>
1321 </itemizedlist> 1337 </itemizedlist>
1322 <para>For example, do you want to see all your 404's? Do this:</para> 1338 <para>For example, do you want to see all your 404's? Do this:</para>
1323 <screen>select remote_host,status,request_uri,bytes_sent,from_unixtime(time_stamp) from acc_log_tbl where status=404 order by time_stamp;</screen> 1339 <programlisting>select remote_host,status,request_uri,bytes_sent,from_unixtime(time_stamp) from acc_log_tbl where status=404 order by time_stamp;</programlisting>
1324 <table> 1340 <table>
1325 <title></title> 1341 <title></title>
1326 <tgroup cols="5"> 1342 <tgroup cols="5">
@@ -1371,7 +1387,7 @@ where a.id=n.id and a.id='PPIDskBRH30AAGPtAsg';</screen>
1371 </tgroup> 1387 </tgroup>
1372 </table> 1388 </table>
1373 <para>Or do you want to see how many bytes you've sent within a certain directory or site? Do this:</para> 1389 <para>Or do you want to see how many bytes you've sent within a certain directory or site? Do this:</para>
1374 <screen>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;</screen> 1390 <programlisting>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;</programlisting>
1375 <table> 1391 <table>
1376 <title></title> 1392 <title></title>
1377 <tgroup cols="3"> 1393 <tgroup cols="3">
@@ -1410,7 +1426,7 @@ where a.id=n.id and a.id='PPIDskBRH30AAGPtAsg';</screen>
1410 </tgroup> 1426 </tgroup>
1411 </table> 1427 </table>
1412 <para>Or maybe you want to see who's linking to you? Do this:</para> 1428 <para>Or maybe you want to see who's linking to you? Do this:</para>
1413 <screen>select count(referer) as num,referer from acc_log_tbl where request_uri='/mod_log_sql/' group by referer order by num desc;</screen> 1429 <programlisting>select count(referer) as num,referer from acc_log_tbl where request_uri='/mod_log_sql/' group by referer order by num desc;</programlisting>
1414 <table> 1430 <table>
1415 <title></title> 1431 <title></title>
1416 <tgroup cols="2"> 1432 <tgroup cols="2">
@@ -1451,10 +1467,10 @@ where a.id=n.id and a.id='PPIDskBRH30AAGPtAsg';</screen>
1451 </question> 1467 </question>
1452 <answer> 1468 <answer>
1453 <para>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.</para> 1469 <para>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.</para>
1454 <para>That being said, there are alternatives. PostgreSQL is probably MySQL's leading "competitor" in the free database world. There is also an excellent module available for Apache to permit logging to a PostgreSQL database, called<ulink url="http://www.digitalstratum.com/pglogd/">pgLOGd</ulink></para> 1470 <para>That being said, there are alternatives. PostgreSQL is probably MySQL's leading "competitor" in the free database world. There is also an excellent module available for Apache to permit logging to a PostgreSQL database, called <ulink url="http://www.digitalstratum.com/pglogd/">pgLOGd</ulink></para>
1455 </answer> 1471 </answer>
1456 <answer> 1472 <answer>
1457 <remark>Currently a database abstraction system is in the works to allow any database to be used with mod_log_sql.</remark> 1473 <note><para>Currently a database abstraction system is in the works to allow any database to be used with mod_log_sql.</para></note>
1458 </answer> 1474 </answer>
1459 </qandaentry> 1475 </qandaentry>
1460 <qandaentry> 1476 <qandaentry>
@@ -1555,7 +1571,7 @@ where a.id=n.id and a.id='PPIDskBRH30AAGPtAsg';</screen>
1555 <simpara>Another factor is preventing a connection to the database</simpara> 1571 <simpara>Another factor is preventing a connection to the database</simpara>
1556 </listitem> 1572 </listitem>
1557 </itemizedlist> 1573 </itemizedlist>
1558 <remark>It is improper to ask for help before you have followed these steps.</remark> 1574 <note><para>It is improper to ask for help before you have followed these steps.</para></note>
1559 <para>First examine the MySQL log that you established in step <xref linkend="Item.EnableLogging"/> of section <xref endterm="Sect.Preperation.title" linkend="Sect.Preperation"/>. Ensure that the INSERT statements are not being rejected because of a malformed table name or other typographical error. By enabling that log, you instructed MySQL to log every connection and command it receives -- if you see no INSERT attempts in the log, the module isn't successfully connecting to the database. If you see nothing at all in the log -- not even a record of your administrative connection attempts, then you did not enable the log correctly. If you do see INSERT attempts but they are failing, the log should tell you why.</para> 1575 <para>First examine the MySQL log that you established in step <xref linkend="Item.EnableLogging"/> of section <xref endterm="Sect.Preperation.title" linkend="Sect.Preperation"/>. Ensure that the INSERT statements are not being rejected because of a malformed table name or other typographical error. By enabling that log, you instructed MySQL to log every connection and command it receives -- if you see no INSERT attempts in the log, the module isn't successfully connecting to the database. If you see nothing at all in the log -- not even a record of your administrative connection attempts, then you did not enable the log correctly. If you do see INSERT attempts but they are failing, the log should tell you why.</para>
1560 <para>Second, confirm that your LogSQL* directives are all correct.</para> 1576 <para>Second, confirm that your LogSQL* directives are all correct.</para>
1561 <para>Third, examine the Apache error logs for messages from mod_log_sql; the module will offer hints as to why it cannot connect, etc. </para> 1577 <para>Third, examine the Apache error logs for messages from mod_log_sql; the module will offer hints as to why it cannot connect, etc. </para>
@@ -1586,10 +1602,10 @@ ErrorLog /var/log/httpd/server-messages </programlisting>
1586 <para>Jeremy Zawodny, a highly respected MySQL user and contributor to Linux Magazine, has this very helpful and highly appropriate article on tuning MySQL: <ulink url="http://jeremy.zawodny.com/blog/archives/000173.html">MySQL, Linux, and Thread Caching</ulink></para> 1602 <para>Jeremy Zawodny, a highly respected MySQL user and contributor to Linux Magazine, has this very helpful and highly appropriate article on tuning MySQL: <ulink url="http://jeremy.zawodny.com/blog/archives/000173.html">MySQL, Linux, and Thread Caching</ulink></para>
1587 </answer> 1603 </answer>
1588 <answer> 1604 <answer>
1589 <para><abbrev></abbrev>Please remember that mod_log_sql's overriding principle is performance -- that is what the target audience demands and expects. Other database logging solutions do not open and maintain many database connections, but their performance suffers drastically. For example, pgLOGd funnels all log connections through a separate daemon that connects to the database, but that bottlenecks the entire process. mod_log_sql achieves performance numbers an order of magnitude greater than the alternatives because it dispenses with the overhead associated with rapid connection cycling, and it does not attempt to shoehorn all the database traffic through a single extra daemon or proxy process.</para> 1605 <para>Please remember that mod_log_sql's overriding principle is performance -- that is what the target audience demands and expects. Other database logging solutions do not open and maintain many database connections, but their performance suffers drastically. For example, pgLOGd funnels all log connections through a separate daemon that connects to the database, but that bottlenecks the entire process. mod_log_sql achieves performance numbers an order of magnitude greater than the alternatives because it dispenses with the overhead associated with rapid connection cycling, and it does not attempt to shoehorn all the database traffic through a single extra daemon or proxy process.</para>
1590 </answer> 1606 </answer>
1591 <answer> 1607 <answer>
1592 <remark>Currently connection pooling is being implemented as part of the Database Abstraction layer to allow multiple httpd processes to share connections.</remark> 1608 <note><para>Currently connection pooling is being implemented as part of the Database Abstraction layer to allow multiple httpd processes to share connections.</para></note>
1593 </answer> 1609 </answer>
1594 </qandaentry> 1610 </qandaentry>
1595 <qandaentry> 1611 <qandaentry>
@@ -1598,10 +1614,10 @@ ErrorLog /var/log/httpd/server-messages </programlisting>
1598 </question> 1614 </question>
1599 <answer> 1615 <answer>
1600 <para>This message may appear every now and then in your Apache error log, especially on very lightly loaded servers. This does not 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 have not 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:</para> 1616 <para>This message may appear every now and then in your Apache error log, especially on very lightly loaded servers. This does not 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 have not 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:</para>
1601 <screen>[Tue Nov 12 19:04:10 2002] [error] mod_log_sql: first attempt failed, 1617 <programlisting>[Tue Nov 12 19:04:10 2002] [error] mod_log_sql: first attempt failed,
1602 API said: error 2013, Lost connection to MySQL server during query 1618 API said: error 2013, Lost connection to MySQL server during query
1603[Tue Nov 12 19:04:10 2002] [error] mod_log_sql: reconnect successful 1619[Tue Nov 12 19:04:10 2002] [error] mod_log_sql: reconnect successful
1604[Tue Nov 12 19:04:10 2002] [error] mod_log_sql: second attempt successful</screen> 1620[Tue Nov 12 19:04:10 2002] [error] mod_log_sql: second attempt successful</programlisting>
1605 <para>Reference: <ulink url="http://dev.mysql.com/doc/mysql/en/Gone_away.html">MySQL documentation</ulink></para> 1621 <para>Reference: <ulink url="http://dev.mysql.com/doc/mysql/en/Gone_away.html">MySQL documentation</ulink></para>
1606 </answer> 1622 </answer>
1607 </qandaentry> 1623 </qandaentry>
@@ -1657,8 +1673,8 @@ CookieName Clicks</programlisting>
1657 </example> 1673 </example>
1658 <example> 1674 <example>
1659 <title>"ab" commandline</title> 1675 <title>"ab" commandline</title>
1660 <screen format="linespecific">./ab -c 10 -t 20 -v 2 -C Clicks=ab_run 1676 <programlisting format="linespecific">./ab -c 10 -t 20 -v 2 -C Clicks=ab_run
1661http://www.hostname.com/target </screen> 1677http://www.hostname.com/target </programlisting>
1662 </example> 1678 </example>
1663 <para>( 10 concurrent requests; 20 second test; setting a cookie "Clicks=ab_run"; target = the mod_log_sql homepage. )</para> 1679 <para>( 10 concurrent requests; 20 second test; setting a cookie "Clicks=ab_run"; target = the mod_log_sql homepage. )</para>
1664 <para>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:</para> 1680 <para>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:</para>
@@ -1681,8 +1697,8 @@ http://www.hostname.com/target </screen>
1681 </listitem> 1697 </listitem>
1682 <listitem> 1698 <listitem>
1683 <simpara>When done with your runs, clean these many thousands of requests out of your database:</simpara> 1699 <simpara>When done with your runs, clean these many thousands of requests out of your database:</simpara>
1684 <screen>mysql&gt; delete from access_log where agent like 'ApacheBench%'; 1700 <programlisting>mysql&gt; delete from access_log where agent like 'ApacheBench%';
1685mysql&gt; optimize table access_log; </screen> 1701mysql&gt; optimize table access_log; </programlisting>
1686 </listitem> 1702 </listitem>
1687 </orderedlist> 1703 </orderedlist>
1688 </answer> 1704 </answer>
@@ -1763,7 +1779,7 @@ rm -f /var/log/httpd/templog</programlisting>
1763 <programlisting>LogFormat "%h %l %u %t \"%r\" %s %b \"%{Referer}i\" \"%{User-Agent}i\"" \"%{cookie}n\""</programlisting> 1779 <programlisting>LogFormat "%h %l %u %t \"%r\" %s %b \"%{Referer}i\" \"%{User-Agent}i\"" \"%{cookie}n\""</programlisting>
1764 <para>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.</para> 1780 <para>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.</para>
1765 <para>First make sure you have a column called "cookie" in the MySQL database to hold the cookies, which can be done as follows if you already have a working database:</para> 1781 <para>First make sure you have a column called "cookie" in the MySQL database to hold the cookies, which can be done as follows if you already have a working database:</para>
1766 <screen>mysql&gt; alter table acc_log_tbl add column cookie varchar(255);</screen> 1782 <programlisting>mysql&gt; alter table acc_log_tbl add column cookie varchar(255);</programlisting>
1767 <para>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:</para> 1783 <para>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:</para>
1768 <programlisting>&lt;VirtualHost 1.2.3.4&gt; 1784 <programlisting>&lt;VirtualHost 1.2.3.4&gt;
1769 CookieTracking on 1785 CookieTracking on
@@ -1776,7 +1792,7 @@ rm -f /var/log/httpd/templog</programlisting>
1776 <para>Recap: the 'c' character activates cookie logging, and the LogSQLWhichCookie directive chooses which cookie to log.</para> 1792 <para>Recap: the 'c' character activates cookie logging, and the LogSQLWhichCookie directive chooses which cookie to log.</para>
1777 <para>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.</para> 1793 <para>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.</para>
1778 <para>Perform some hits on your server and run a select</para> 1794 <para>Perform some hits on your server and run a select</para>
1779 <screen format="linespecific">mysql&gt; select request_uri,cookie from access_log where cookie is not null;</screen> 1795 <programlisting format="linespecific">mysql&gt; select request_uri,cookie from access_log where cookie is not null;</programlisting>
1780 <table> 1796 <table>
1781 <title></title> 1797 <title></title>
1782 <tgroup cols="2"> 1798 <tgroup cols="2">
@@ -1828,9 +1844,9 @@ rm -f /var/log/httpd/templog</programlisting>
1828 <para>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 maximum 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.</para> 1844 <para>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 maximum 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.</para>
1829 <para>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.</para> 1845 <para>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.</para>
1830 <para>You need to ensure that your database is set up to log the SSL data. Issue the following commands to MySQL if your access table does not already have them:</para> 1846 <para>You need to ensure that your database is set up to log the SSL data. Issue the following commands to MySQL if your access table does not already have them:</para>
1831 <screen>mysql&gt; alter table access_log add column ssl_cipher varchar(25); 1847 <programlisting>mysql&gt; alter table access_log add column ssl_cipher varchar(25);
1832mysql&gt; alter table access_log add column ssl_keysize smallint unsigned; 1848mysql&gt; alter table access_log add column ssl_keysize smallint unsigned;
1833mysql&gt; alter table access_log add column ssl_maxkeysize smallint unsigned;</screen> 1849mysql&gt; alter table access_log add column ssl_maxkeysize smallint unsigned;</programlisting>
1834 <para>Finally configure httpd.conf to activate the SSL fields. Note that this is only meaningful in a VirtualHost that is set up for SSL.</para> 1850 <para>Finally configure httpd.conf to activate the SSL fields. Note that this is only meaningful in a VirtualHost that is set up for SSL.</para>
1835 <programlisting>&lt;VirtualHost 1.2.3.4:443&gt; 1851 <programlisting>&lt;VirtualHost 1.2.3.4:443&gt;
1836 LogSQLTransferLogFormat AbHhmRSsTUuvcQqz 1852 LogSQLTransferLogFormat AbHhmRSsTUuvcQqz
@@ -1838,7 +1854,7 @@ mysql&gt; alter table access_log add column ssl_maxkeysize smallint unsigned;</s
1838 <para>You also need to make sure you have the mod_log_sql_ssl module loaded as well.</para> 1854 <para>You also need to make sure you have the mod_log_sql_ssl module loaded as well.</para>
1839 <para>The last three characters (Qqz) in the directive are the SSL ones; see section <xref linkend="Conf.LogSQLTransferLogFormat"/> in the directives documentation for details of the LogSQLTransferLogFormat directive.</para> 1855 <para>The last three characters (Qqz) in the directive are the SSL ones; see section <xref linkend="Conf.LogSQLTransferLogFormat"/> in the directives documentation for details of the LogSQLTransferLogFormat directive.</para>
1840 <para>Restart Apache, then perform some hits on your server. Then run the following select statement:</para> 1856 <para>Restart Apache, then perform some hits on your server. Then run the following select statement:</para>
1841 <screen>mysql&gt; select remote_host,request_uri,ssl_cipher,ssl_keysize,ssl_maxkeysize from access_log where ssl_cipher is not null;</screen> 1857 <programlisting>mysql&gt; select remote_host,request_uri,ssl_cipher,ssl_keysize,ssl_maxkeysize from access_log where ssl_cipher is not null;</programlisting>
1842 <table> 1858 <table>
1843 <title></title> 1859 <title></title>
1844 <tgroup cols="5"> 1860 <tgroup cols="5">
@@ -1885,5 +1901,5 @@ mysql&gt; alter table access_log add column ssl_maxkeysize smallint unsigned;</s
1885 </qandaentry> 1901 </qandaentry>
1886 </qandadiv> 1902 </qandadiv>
1887 </qandaset> 1903 </qandaset>
1888 </sect1> 1904 </section>
1889</article> 1905</article>