summaryrefslogtreecommitdiffstats
path: root/Documentation
diff options
context:
space:
mode:
authorGravatar Edward Rudd 2004-03-04 05:41:12 +0000
committerGravatar Edward Rudd 2004-03-04 05:41:12 +0000
commitec60974ca79594150b33692683b2d8bb800a896f (patch)
tree233cd2d8f4b60b7654840aa6206271e134bd05af /Documentation
parentd28237cd2fff1922f86fb7576c22625f067d24e7 (diff)
complete DB separation from core module code.
99% updated documentation (needs 2 tables added) moved DB connection code for Apache 2 from open_logs to child_init. removed more #ifdef debug lines
Diffstat (limited to 'Documentation')
-rw-r--r--Documentation/manual.xml866
1 files changed, 445 insertions, 421 deletions
diff --git a/Documentation/manual.xml b/Documentation/manual.xml
index daafd69..5ff595e 100644
--- a/Documentation/manual.xml
+++ b/Documentation/manual.xml
@@ -47,14 +47,14 @@
47 </revision> 47 </revision>
48 </revhistory> 48 </revhistory>
49 </articleinfo> 49 </articleinfo>
50 <sect1 id="intro"> 50 <sect1>
51 <title>Introduction</title> 51 <title>Introduction</title>
52 <sect2> 52 <sect2>
53 <title>Summary</title> 53 <title>Summary</title>
54 <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 section <link linkend="WhyLogToSQL"></link> in the FAQ for further discussion and examples of the advantages to SQL.)</para> 54 <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>
55 <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> 55 <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>
56 </sect2> 56 </sect2>
57 <sect2 id="copyright"> 57 <sect2>
58 <title>Approach</title> 58 <title>Approach</title>
59 <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> 59 <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>
60 <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> 60 <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>
@@ -63,7 +63,7 @@
63 </sect2> 63 </sect2>
64 <sect2> 64 <sect2>
65 <title>What gets logged by default?</title> 65 <title>What gets logged by default?</title>
66 <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 <link endterm="Configuration.title" linkend="ConfigReference"></link>.</para> 66 <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>
67 </sect2> 67 </sect2>
68 <sect2> 68 <sect2>
69 <title>Miscellaneous Notes</title> 69 <title>Miscellaneous Notes</title>
@@ -123,8 +123,8 @@
123 <title>Platform Specific Notes</title> 123 <title>Platform Specific Notes</title>
124 <remark>This section is currently not applicable as the new autoconf setup should auto-detect everything for your server. The win32 compilation, however, is still on my todo list. This section needs to be overhauled so do not rely completely on any information presented in it. And anyone who compiles the mod_log_sql on these platforms, please report any issues/resolutions to compiling the module.</remark> 124 <remark>This section is currently not applicable as the new autoconf setup should auto-detect everything for your server. The win32 compilation, however, is still on my todo list. This section needs to be overhauled so do not rely completely on any information presented in it. And anyone who compiles the mod_log_sql on these platforms, please report any issues/resolutions to compiling the module.</remark>
125 <para>These installation documents assume a relatively modern GNU/Linux scenario. mod_log_sql has been ported to other platforms; following are notes on compiling the module for those platforms.</para> 125 <para>These installation documents assume a relatively modern GNU/Linux scenario. mod_log_sql has been ported to other platforms; following are notes on compiling the module for those platforms.</para>
126 <sect3 id="Solaris"> 126 <sect3 id="Sect.Solaris">
127 <title id="Solaris.title">Solaris</title> 127 <title id="Sect.Solaris.title">Solaris</title>
128 <para>The nanosleep() function used in mod_log_sql relies on linking aginst the librt library. Make the following alterations before proceeding: </para> 128 <para>The nanosleep() function used in mod_log_sql relies on linking aginst the librt library. Make the following alterations before proceeding: </para>
129 <orderedlist> 129 <orderedlist>
130 <listitem> 130 <listitem>
@@ -151,7 +151,7 @@
151 </sect3> 151 </sect3>
152 <sect3> 152 <sect3>
153 <title>Digital Unix</title> 153 <title>Digital Unix</title>
154 <para>Digital Unix, like Solaris, needs to be linked against librt; see section <link endterm="Solaris.title" linkend="Solaris"></link>. Here are further notes from a user successfully running the module on Digital Unix:</para> 154 <para>Digital Unix, like Solaris, needs to be linked against librt; see section <xref endterm="Sect.Solaris.title" linkend="Sect.Solaris"/>. Here are further notes from a user successfully running the module on Digital Unix:</para>
155 <para>Instead of trying to get the module to remember where the MySQL libraries were, I instead compiled apache with the information: LDFLAGS='-rpath /isp/mysql/lib/mysql' ./configure ... Everything worked as expected after that. (The error I got without this was "/sbin/loader: Fatal Error: cannot map libmysqlclient.so" ) Digital Unix (v4.0f, at least ) appears to follow the same requirements needed by Solaris, so simply adding librt to the module made it compile without errors. As for the warnings, here's the text: mod_log_sql.c: In function `extract_request_duration': mod_log_sql.c:292: warning: long int format, different type arg (arg 4) mod_log_sql.c: In function `extract_request_timestamp': mod_log_sql.c:497: warning: long int format, different type arg (arg 4) Poking around in the code, it looks like the compiler was complaining that what time() is returning doesn't play nicely with %ld by default. I just typecast them as (long)'s and the warnings went away ( not that the module wasn't working correctly without them ). The module works very well so far in testing... hasn't dropped a single log entry yet. </para> 155 <para>Instead of trying to get the module to remember where the MySQL libraries were, I instead compiled apache with the information: LDFLAGS='-rpath /isp/mysql/lib/mysql' ./configure ... Everything worked as expected after that. (The error I got without this was "/sbin/loader: Fatal Error: cannot map libmysqlclient.so" ) Digital Unix (v4.0f, at least ) appears to follow the same requirements needed by Solaris, so simply adding librt to the module made it compile without errors. As for the warnings, here's the text: mod_log_sql.c: In function `extract_request_duration': mod_log_sql.c:292: warning: long int format, different type arg (arg 4) mod_log_sql.c: In function `extract_request_timestamp': mod_log_sql.c:497: warning: long int format, different type arg (arg 4) Poking around in the code, it looks like the compiler was complaining that what time() is returning doesn't play nicely with %ld by default. I just typecast them as (long)'s and the warnings went away ( not that the module wasn't working correctly without them ). The module works very well so far in testing... hasn't dropped a single log entry yet. </para>
156 <para>My thanks to Jim Turner for permitting me to quote him here, and for being the first known user of mod_log_sql on Digital Unix</para> 156 <para>My thanks to Jim Turner for permitting me to quote him here, and for being the first known user of mod_log_sql on Digital Unix</para>
157 </sect3> 157 </sect3>
@@ -189,7 +189,7 @@ $ cd mod_log_sql-1.9</screen>
189 <listitem> 189 <listitem>
190 <para>This is the directory to search for the <filename>libmysqlclient</filename> library and the <application>MySQL</application> headers.</para> 190 <para>This is the directory to search for the <filename>libmysqlclient</filename> library and the <application>MySQL</application> headers.</para>
191 <para>The default is to search <filename>/usr/include</filename>, <filename>/usr/include/mysql</filename>, <filename>/usr/local/include</filename>, and <filename>/usr/local/include/mysql</filename> for <application>MySQL</application> headers.. And <filename>/usr/lib</filename>. <filename>/usr/lib/mysql</filename>, <filename>/usr/local/lib</filename>, and <filename>/usr/local/lin/mysql</filename> for the <application>MySQL</application> libraries.</para> 191 <para>The default is to search <filename>/usr/include</filename>, <filename>/usr/include/mysql</filename>, <filename>/usr/local/include</filename>, and <filename>/usr/local/include/mysql</filename> for <application>MySQL</application> headers.. And <filename>/usr/lib</filename>. <filename>/usr/lib/mysql</filename>, <filename>/usr/local/lib</filename>, and <filename>/usr/local/lin/mysql</filename> for the <application>MySQL</application> libraries.</para>
192 <para>Specifying this argument will search $directory/include and $directory/mysql for <application>MySQL</application> headers. And $directory/lib and $directory/lib/mysql for <application>MySQL</application> libraries.</para> 192 <para>Specifying this testargument will search $directory/include and $directory/mysql for <application>MySQL</application> headers. And $directory/lib and $directory/lib/mysql for <application>MySQL</application> libraries.</para>
193 </listitem> 193 </listitem>
194 </varlistentry> 194 </varlistentry>
195 <varlistentry> 195 <varlistentry>
@@ -264,12 +264,12 @@ AddModule mod_log_sql_ssl.c
264 </orderedlist> 264 </orderedlist>
265 </sect2> 265 </sect2>
266 </sect1> 266 </sect1>
267 <sect1 id="Configuration"> 267 <sect1 id="Sect.Configuration">
268 <title id="Configuration.title">Configuration</title> 268 <title id="Sect.Configuration.title">Configuration</title>
269 <sect2 id="Preperation"> 269 <sect2 id="Sect.Preperation">
270 <title id="Preperation.title">Preparing MySQL for logging</title> 270 <title id="Sect.Preperation.title">Preparing MySQL for logging</title>
271 <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> 271 <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>
272 <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 <link endterm="ConfigReference.title" linkend="ConfigReference"></link>.</para> 272 <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>
273 <orderedlist> 273 <orderedlist>
274 <listitem> 274 <listitem>
275 <para>mod_log_sql can make its own tables on-the-fly, or you can pre-make the tables by hand. The advantage of letting the module make the tables is ease-of-use, but for raw performance you will want to pre-make the tables in order to save some overhead. In this basic setup we'll just let the module create tables for us.</para> 275 <para>mod_log_sql can make its own tables on-the-fly, or you can pre-make the tables by hand. The advantage of letting the module make the tables is ease-of-use, but for raw performance you will want to pre-make the tables in order to save some overhead. In this basic setup we'll just let the module create tables for us.</para>
@@ -280,7 +280,7 @@ AddModule mod_log_sql_ssl.c
280Enter password: 280Enter password:
281mysql&gt; create database apachelogs;</screen> 281mysql&gt; create database apachelogs;</screen>
282 </listitem> 282 </listitem>
283 <listitem id="CreateTable"> 283 <listitem id="Item.CreateTable">
284 <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> 284 <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>
285 <screen>mysql&gt; use apachelogs 285 <screen>mysql&gt; use apachelogs
286Database changed 286Database changed
@@ -291,10 +291,10 @@ mysql&gt; source create_tables.sql</screen>
291 <screen>mysql&gt; grant insert,create on apachelogs.* to loguser@my.apachemachine.com identified by 'l0gger';</screen> 291 <screen>mysql&gt; grant insert,create on apachelogs.* to loguser@my.apachemachine.com identified by 'l0gger';</screen>
292 </listitem> 292 </listitem>
293 <listitem> 293 <listitem>
294 <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 <link endterm="CreateTable" linkend="CreateTable"></link> and use the following GRANT statement instead of the one above:</para> 294 <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>
295 <screen>mysql&gt; grant insert on apachelogs.* to loguser@my.apachemachine.com identified by 'l0gger';</screen> 295 <screen>mysql&gt; grant insert on apachelogs.* to loguser@my.apachemachine.com identified by 'l0gger';</screen>
296 </listitem> 296 </listitem>
297 <listitem id="EnableLogging"> 297 <listitem id="Item.EnableLogging">
298 <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> 298 <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>
299 <programlisting>log=/var/log/mysql-messages</programlisting> 299 <programlisting>log=/var/log/mysql-messages</programlisting>
300 <para>Then restart <application>MySQL</application></para> 300 <para>Then restart <application>MySQL</application></para>
@@ -342,7 +342,7 @@ LogSQLCreateTables on</programlisting>
342 <para>Visit your web site in a browser to trigger some hits, then confirm that the entries are being successfully logged:</para> 342 <para>Visit your web site in a browser to trigger some hits, then confirm that the entries are being successfully logged:</para>
343 <screen># mysql -hdbmachine.foo.com -umysqladmin -p -e "select * from access_log" apachelogs 343 <screen># mysql -hdbmachine.foo.com -umysqladmin -p -e "select * from access_log" apachelogs
344Enter password:</screen> 344Enter password:</screen>
345 <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 <link linkend="NothingLogged"></link> of the FAQ on how to debug and fix the situation.</para> 345 <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>
346 </listitem> 346 </listitem>
347 <listitem> 347 <listitem>
348 <para>You can now activate the advanced features of mod_log_sql, which are described in the next section.</para> 348 <para>You can now activate the advanced features of mod_log_sql, which are described in the next section.</para>
@@ -354,7 +354,7 @@ Enter password:</screen>
354 <sect3> 354 <sect3>
355 <title>Instructing the module what to log</title> 355 <title>Instructing the module what to log</title>
356 <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> 356 <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>
357 <para>After LogSQLTransferLogFormat you supply a string of characters that tell the module what information to log. In the configuration directive reference (section <link endterm="TLogFormat.title" linkend="TLogFormat"></link>) 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> 357 <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>
358 <programlisting>LogSQLTransferLogFormat hUS</programlisting> 358 <programlisting>LogSQLTransferLogFormat hUS</programlisting>
359 <para>But a more appropriate string to use is</para> 359 <para>But a more appropriate string to use is</para>
360 <programlisting>LogSQLTransferLogFormat AbHhmRSsTUuv</programlisting> 360 <programlisting>LogSQLTransferLogFormat AbHhmRSsTUuv</programlisting>
@@ -370,8 +370,8 @@ Enter password:</screen>
370 </listitem> 370 </listitem>
371 </itemizedlist> 371 </itemizedlist>
372 </sect3> 372 </sect3>
373 <sect3 id="Ignore"> 373 <sect3 id="Sect.Ignore">
374 <title id="Ignore.title">Instructing the module what NOT to log using filtering directives</title> 374 <title id="Sect.Ignore.title" xreflabel="Using Filtering Directives">Instructing the module what NOT to log using filtering directives</title>
375 <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> 375 <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>
376 <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> 376 <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>
377 <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> 377 <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>
@@ -438,8 +438,8 @@ LogSQLRequestIgnore statistics.html bluedot.jpg</programlisting>
438 <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> 438 <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>
439 <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> 439 <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>
440 </sect3> 440 </sect3>
441 <sect3 id="MultiTable"> 441 <sect3 id="Sect.MultiTable">
442 <title id="MultiTable.title">Logging many-to-one data in separate tables</title> 442 <title id="Sect.MultiTable.title">Logging many-to-one data in separate tables</title>
443 <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> 443 <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>
444 <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> 444 <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>
445 <table> 445 <table>
@@ -603,8 +603,8 @@ where a.id=n.id and a.id='PPIDskBRH30AAGPtAsg';</screen>
603 <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> 603 <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>
604 <programlisting>delete from access_log where machine_id like 'test%';</programlisting> 604 <programlisting>delete from access_log where machine_id like 'test%';</programlisting>
605 </sect3> 605 </sect3>
606 <sect3 id="DelayedInsert"> 606 <sect3 id="Sect.DelayedInsert">
607 <title id="DelayedInsert.title">Optimizing for a busy database</title> 607 <title id="Sect.DelayedInsert.title">Optimizing for a busy database</title>
608 <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> 608 <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>
609 <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> 609 <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>
610 <para>INSERT DELAYED only works with ISAM and MyISAM tables. Note that as MyISAM tables supports concurrent SELECT and INSERT, if there is no free blocks in the middle of the data file, you very seldom need to use INSERT DELAYED with MyISAM. </para> 610 <para>INSERT DELAYED only works with ISAM and MyISAM tables. Note that as MyISAM tables supports concurrent SELECT and INSERT, if there is no free blocks in the middle of the data file, you very seldom need to use INSERT DELAYED with MyISAM. </para>
@@ -620,397 +620,421 @@ where a.id=n.id and a.id='PPIDskBRH30AAGPtAsg';</screen>
620 </listitem> 620 </listitem>
621 </orderedlist> 621 </orderedlist>
622 <warning> 622 <warning>
623 <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 section <link linkend="DelayedInsertFAQ"></link> in the FAQ -- you have been warned.</para> 623 <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>
624 </warning> 624 </warning>
625 <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> 625 <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>
626 </sect3> 626 </sect3>
627 </sect2> 627 </sect2>
628 <sect2 id="ConfigReference"> 628 <sect2 id="Sect.ConfigReference">
629 <title id="ConfigReference.title">Configuration Directive Reference</title> 629 <title id="Sect.ConfigReference.title">Configuration Directive Reference</title>
630 <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> 630 <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>
631 <variablelist>
632 <varlistentry>
633 <term>LogSQLCookieLogTable</term>
634 <listitem>
635 <cmdsynopsis sepchar=" ">
636 <command>LogSQLCookieLogTable</command>
637 <arg choice="req" rep="norepeat"><replaceable></replaceable>table-name</arg>
638 </cmdsynopsis>
639 <simpara>Example: LogSQLCookieLogTable cookie_log</simpara>
640 <simpara>Default: cookies</simpara>
641 <simpara>Context: virtual host</simpara>
642 <para>Defines which table is used for logging of cookies. Working in conjunction with LogSQLWhichCookies, you can log many of each request's associated cookies to a separate table. For meaningful data retrieval the cookie table is keyed to the access table by the unique request ID supplied by the standard Apache module mod_unique_id.</para>
643 <note>
644 <para>You must create the table (see create-tables.sql, included in the package), or LogSQLCreateTables must be set to "on".</para>
645 </note>
646 </listitem>
647 </varlistentry>
648 <varlistentry>
649 <term>LogSQLCreateTables</term>
650 <listitem>
651 <cmdsynopsis>
652 <command>LogSQLCreateTables</command>
653 <arg choice="req">flag</arg>
654 </cmdsynopsis>
655 <simpara>Example: LogSQLCreateTables On</simpara>
656 <simpara>Default: Off</simpara>
657 <simpara>Context: main server config</simpara>
658 <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>
659 <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>
660 <note>
661 <para>This is defined only once in the <filename moreinfo="none">httpd.conf</filename> file.</para>
662 </note>
663 </listitem>
664 </varlistentry>
665 <varlistentry>
666 <term>LogSQLDatabase</term>
667 <listitem>
668 <cmdsynopsis>
669 <command>LogSQLDatabase</command>
670 <arg choice="req"><replaceable>database</replaceable></arg>
671 </cmdsynopsis>
672 <simpara>Example: LogSQLDatabase loggingdb</simpara>
673 <simpara>Context: main server config</simpara>
674 <para>Defines the database that is used for logging. "database" must be a valid db on the MySQL host defined in LogSQLLoginInfo</para>
675 <note>
676 <para>This is defined only once in the <filename>httpd.conf</filename> file.</para>
677 <para>This directive Must be defined for logging to be enabled.</para>
678 </note>
679 </listitem>
680 </varlistentry>
681 <varlistentry>
682 <term>LogForcePreserve</term>
683 <listitem>
684 <cmdsynopsis>
685 <command>LogSQLForcePreserve</command>
686 <arg choice="req">flag</arg>
687 </cmdsynopsis>
688 <simpara>Example: LogForcePreserve On</simpara>
689 <simpara>Default: Off</simpara>
690 <simpara>Context: main server config</simpara>
691 <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>
692 <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>
693 <note>
694 <para>This is defined only once in the <filename>httpd.conf</filename> file.</para>
695 </note>
696 </listitem>
697 </varlistentry>
698 <varlistentry>
699 <term>LogSQLHeadersInLogTable</term>
700 <listitem>
701 <cmdsynopsis>
702 <command>LogSQLHeadersInLogTable</command>
703 <arg choice="req"><replaceable>table-name</replaceable></arg>
704 </cmdsynopsis>
705 <simpara>Example: LogSQLHeadersInLogTable headers</simpara>
706 <simpara>Default: headers_in</simpara>
707 <simpara>Context: virtual host</simpara>
708 <para>Defines which table is used for logging of inbound headers. Working in conjunction with LogSQLWhichHeadersIn, you can log many of each request's associated headers to a separate table. For meaningful data retrieval the headers table is keyed to the access table by the unique request ID supplied by the standard Apache module mod_unique_id.</para>
709 <note>
710 <para>Note that you must create the table (see create-tables.sql, included in the package), or LogSQLCreateTables must be set to "on".</para>
711 </note>
712 </listitem>
713 </varlistentry>
714 <varlistentry>
715 <term>LogSQLHeadersOutLogTable</term>
716 <listitem>
717 <cmdsynopsis sepchar=" ">
718 <command moreinfo="none">LogSQLHeadersOutLogTable</command>
719 <arg choice="req" rep="norepeat"><replaceable>table-name</replaceable></arg>
720 </cmdsynopsis>
721 <simpara>Example: LogSQLHeadersOutLogTable headers</simpara>
722 <simpara>Default: headers_out</simpara>
723 <simpara>Context: virtual host</simpara>
724 <para>Defines which table is used for logging of outbound headers. Working in conjunction with LogSQLWhichHeadersOut, you can log many of each request's associated headers to a separate table. For meaningful data retrieval the headers table is keyed to the access table by the unique request ID supplied by the standard Apache module mod_unique_id.</para>
725 <note>
726 <para>Note that you must create the table (see create-tables.sql, included in the package), or LogSQLCreateTables must be set to "on".</para>
727 </note>
728 </listitem>
729 </varlistentry>
730 <varlistentry>
731 <term>LogSQLLoginInfo</term>
732 <listitem>
733 <cmdsynopsis>
734 <command>LogSQLLoginInfo</command>
735 <arg choice="req"><replaceable>host</replaceable></arg>
736 <arg choice="req"><replaceable>user</replaceable></arg>
737 <arg choice="req"><replaceable>password</replaceable></arg>
738 </cmdsynopsis>
739 <simpara>Example: LogSQLLoginInfo foobar.baz.com logwriter passw0rd</simpara>
740 <simpara>Context: main server config</simpara>
741 <para>Defines the general parameters of the MySQL host to which you will be logging. "host" is the hostname or IP address of the MySQL machine, and is simply "localhost" if the database lives on the same machine as Apache. "user" is the MySQL userid (not a Unix userid!) with INSERT privileges on the table defined in LogSQLTransferLogTable. "password" is that user's password.</para>
742 <note>
743 <para>This is defined only once in the <filename moreinfo="none">httpd.conf</filename> file.</para>
744 <para>This directive Must be defined for logging to be enabled.</para>
745 </note>
746 </listitem>
747 </varlistentry>
748 <varlistentry>
749 <term>LogSQLMachineID</term>
750 <listitem>
751 <cmdsynopsis>
752 <command>LogSQLMachineID</command>
753 <arg choice="req">machineID</arg>
754 </cmdsynopsis>
755 <simpara>Example: LogSQLMachineID web01</simpara>
756 <simpara>Context: main server config</simpara>
757 <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>
758 <note>
759 <para>This is defined only once in the <filename moreinfo="none">httpd.conf</filename> file.</para>
760 </note>
761 </listitem>
762 </varlistentry>
763 <varlistentry>
764 <term>LogSQLMassVirtualHosting</term>
765 <listitem>
766 <cmdsynopsis>
767 <command>LogSQLMassVirtualHosting</command>
768 <arg choice="req">flag</arg>
769 </cmdsynopsis>
770 <simpara>Example: LogSQLMassVirtualHosting On</simpara>
771 <simpara>Default: Off</simpara>
772 <simpara>Context: main server config</simpara>
773 <para>If you administer a site hosting many, many virtual hosts then this option will appeal to you. If you turn on LogSQLMassVirtualHosting then several things happen:</para>
774 <itemizedlist>
775 <listitem>
776 <para>the on-the-fly table creation feature is activated automatically</para>
777 </listitem>
778 <listitem>
779 <para>the transfer log table name is dynamically set from the virtual host's name after stripping out SQL-unfriendly characters (example: a virtual host www.grubbybaby.com gets logged to table access_www_grubbybaby_com)</para>
780 </listitem>
781 <listitem>
782 <para>which, in turn, means that each virtual host logs to its own segregated table. Because there is no data shared between virtual servers you can grant your users access to the tables they need; they will be unable to view others' data.</para>
783 </listitem>
784 </itemizedlist>
785 <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>
786 <note>
787 <para>This is defined only once in the <filename moreinfo="none">httpd.conf</filename> file.</para>
788 </note>
789 </listitem>
790 </varlistentry>
791 <varlistentry>
792 <term>LogSQLNotesLogTable</term>
793 <listitem>
794 <cmdsynopsis>
795 <command>LogSQLNotesLogTable</command>
796 <arg choice="req"><replaceable>table-name</replaceable></arg>
797 </cmdsynopsis>
798 <simpara>Example: LogSQLNotesLogTable notes-log</simpara>
799 <simpara>Default: notes</simpara>
800 <simpara>Context: virtual_host</simpara>
801 <para>Defines which table is used for logging of notes. Working in conjunction with LogSQLWhichNotes, you can log many of each request's associated notes to a separate table. For meaningful data retrieval the notes table is keyed to the access table by the unique request ID supplied by the standard Apache module mod_unique_id.</para>
802 <note>
803 <para>This table must be created (see create-tables.sql included in the package), or LogSQLCreateTables must be set to 'On'.</para>
804 </note>
805 </listitem>
806 </varlistentry>
807 <varlistentry>
808 <term>LogSQlPreserveFile</term>
809 <listitem>
810 <cmdsynopsis>
811 <command>LogSQLPreserveFile</command>
812 <arg choice="req"><replaceable>filename</replaceable></arg>
813 </cmdsynopsis>
814 <simpara>Example: LogSQLPreserveFile offline-preserve</simpara>
815 <simpara>Default: /tmp/sql-preserve</simpara>
816 <simpara>Context: virtual host</simpara>
817 <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>
818 <screen># mysql -uadminuser -p mydbname &lt; /tmp/sql-preserve</screen>
819 <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>
820 <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>
821 <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>
822 </listitem>
823 </varlistentry>
824 <varlistentry>
825 <term>LogSQLRemhostIgnore</term>
826 <listitem>
827 <cmdsynopsis>
828 <command>LogSQLRemhostIgnore</command>
829 <arg choice="req" rep="repeat"><replaceable>hostname</replaceable></arg>
830 </cmdsynopsis>
831 <simpara>Example: LogSQLRemhostIgnore localnet.com</simpara>
832 <simpara>Context: virtual host</simpara>
833 <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 <link endterm="Ignore.title" linkend="Ignore"></link> for some tips for using this directive.</para>
834 <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>
835 </listitem>
836 </varlistentry>
837 <varlistentry>
838 <term>LogSQLRequestAccept</term>
839 <listitem>
840 <cmdsynopsis>
841 <command>LogSQLRequestAccept</command>
842 <arg choice="req" rep="repeat"><replaceable>hostname</replaceable></arg>
843 </cmdsynopsis>
844 <simpara>Example: LogSQLRequestAccept .html .php .jpg</simpara>
845 <simpara>Default: if not specified, all requests are 'accepted'</simpara>
846 <simpara>Context: virtual host</simpara>
847 <para>Lists a series of strings that, if present in the URI, will permit that request to be considered for logging (depending on additional filtering by the "ignore" directives). Any request that fails to match one of the LogSQLRequestAccept entries will be discarded.</para>
848 <para>This directive is useful for cutting down on log clutter when you are certain that you only want to log certain kinds of requests, and just blanket-ignore everything else. See section <link endterm="Ignore.title" linkend="Ignore"></link> for some tips for using this directive.</para>
849 <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 URI using strstr(). The comparison is case sensitive.</para>
850 <para>This directive is completely optional. It is more general than LogSQLRequestIgnore and is evaluated before LogSQLRequestIgnore . If this directive is not used, <emphasis>all</emphasis> requests are accepted and passed on to the other filtering directives. Therefore, only use this directive if you have a specific reason to do so.</para>
851 </listitem>
852 </varlistentry>
853 <varlistentry>
854 <term>LogSQLRequestIgnore</term>
855 <listitem>
856 <cmdsynopsis>
857 <command>LogSQLRequestIgnore</command>
858 <arg choice="req" rep="repeat"><replaceable>hostname</replaceable></arg>
859 </cmdsynopsis>
860 <simpara>Example: LogSQLRequestIgnore root.exe cmd.exe default.ida favicon.ico</simpara>
861 <simpara>Context: virtual host</simpara>
862 <para>Lists a series of strings that, if present in the URI, 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 for certain objects. See section <link endterm="Ignore.title" linkend="Ignore"></link> for some tips for using this directive.</para>
863 <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 URI using strstr(). The comparison is case sensitive.</para>
864 </listitem>
865 </varlistentry>
866 <varlistentry>
867 <term>LogSQLSocketFile</term>
868 <listitem>
869 <cmdsynopsis>
870 <command>LogSQlSocketFile</command>
871 <arg choice="req"><replaceable>filename</replaceable></arg>
872 </cmdsynopsis>
873 <simpara>Example: LogSQLSocketFile /tmp/mysql.sock</simpara>
874 <simpara>Default: /var/lib/mysql/mysql.sock</simpara>
875 <simpara>Context: main server config</simpara>
876 <para>At Apache runtime you can specify the MySQL socket file to use. Set this once in your main server config to override the default value. This value is irrelevant if your database resides on a separate machine.</para>
877 <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>
878 <note>
879 <para>This is defined only once in the <filename moreinfo="none">httpd.conf</filename> file.</para>
880 </note>
881 </listitem>
882 </varlistentry>
883 <varlistentry>
884 <term>LogSQLTCPPort</term>
885 <listitem>
886 <cmdsynopsis>
887 <command>LogSQLTCPPort</command>
888 <arg choice="req"><replaceable>port-number</replaceable></arg>
889 </cmdsynopsis>
890 <simpara>Example: LogSQLTCPPort 3309</simpara>
891 <simpara>Default: 3306</simpara>
892 <simpara>Context: main server config</simpara>
893 <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>
894 <note>
895 <para>This is defined only once in the <filename moreinfo="none">httpd.conf</filename> file.</para>
896 </note>
897 </listitem>
898 </varlistentry>
899 <varlistentry id="TLogFormat">
900 <term id="TLogFormat.title">LogSQLTransferLogFormat </term>
901 <listitem>
902 <cmdsynopsis>
903 <command>LogSQLTransferLogFormat</command>
904 <arg choice="req"><replaceable>format-string</replaceable></arg>
905 </cmdsynopsis>
906 <simpara>Example: LogSQLTransferLogFormat huSUTv</simpara>
907 <simpara>Default: AbHhmRSsTUuv</simpara>
908 <simpara>Context: virtual host</simpara>
909 <para>Each character in the format-string defines an attribute of the request that you wish to log. The default logs the information required to create Combined Log Format logs, plus several extras. Here is the full list of allowable keys, which sometimes resemble their Apache counterparts, but do not always:</para>
910 <para>TODO: Insert Log Format Types Table</para>
911 </listitem>
912 </varlistentry>
913 <varlistentry>
914 <term>LogSQLTransferLogTable</term>
915 <listitem>
916 <cmdsynopsis>
917 <command>LogSQLTransferLogTable</command>
918 <arg choice="req"><replaceable>table-name</replaceable></arg>
919 </cmdsynopsis>
920 <simpara>Example: LogSQLTransferLogTable access_log_table</simpara>
921 <simpara>Context: virtual host</simpara>
922 <para>Defines which table is used for logging of Apache's transfers; this is analogous to Apache's TransferLog directive. table-name must be a valid table within the database defined in LogSQLDatabase.</para>
923 <para>This directive is <emphasis>not</emphasis> necessary if you declare LogSQLMassVirtualHosting On, since that directive activates dynamically-named tables. If you attempt to use LogSqlTransferlogTable at the same time a warning will be logged and it will be ignored, since LogSQLMassVirtualHosting takes priority.</para>
924 <note>
925 <para>Requires unless LogSQLMassVirtualHosting is set to On</para>
926 </note>
927 </listitem>
928 </varlistentry>
929 <varlistentry>
930 <term>LogSQLWhichCookie</term>
931 <listitem>
932 <cmdsynopsis>
933 <command>LogSQLWhichCookie</command>
934 <arg choice="req"><replaceable>cookiename</replaceable></arg>
935 </cmdsynopsis>
936 <simpara>Example; LogSQLWhichCookie Clicks</simpara>
937 <simpara>Context: virtual host</simpara>
938 <para>In HTTP, cookies have names to distinguish them from each other. Using mod_usertrack, for example, you can give your user-tracking cookies a name with the CookieName directive.</para>
939 <para>mod_log_sql allows you to log cookie information. LogSQL_WhichCookie tells mod_log_sql which cookie to log. This is necessary because you will usually be setting and receiving more than one cookie from a client.</para>
940 <note>
941 <para>You must include a 'c' character in LogSQLTransferLogFormat for this directive to take effect.</para>
942 <para>although this was origintally intended for people using mod_usertrack to create user-tracking cookies, you are not restricted in any way. You can choose which cookie you wish to log to the database - any cookie at all - and it does not necessarily have to have anything to do with mod_usertrack.</para>
943 </note>
944 </listitem>
945 </varlistentry>
946 <varlistentry>
947 <term>LogSQLWhichCookies</term>
948 <listitem>
949 <cmdsynopsis>
950 <command>LogSQLWhichCookies</command>
951 <arg choice="req" rep="repeat"><replaceable>cookie-name</replaceable></arg>
952 </cmdsynopsis>
953 <simpara>Example: logSQLWhichCookies userlogin cookie1 cookie2</simpara>
954 <simpara>Context: virtual host</simpara>
955 <para>Defines the list of cookies you would like logged. This works in conjunction with LogSQLCookieLogTable. This directive does <emphasis>not</emphasis> require any additional characters to be added to the LogSQLTransferLogFormat string. The feature is activated simply by including this directive, upon which you will begin populating the separate cookie table with data.</para>
956 <note>
957 <para>The table must be created (see create-tables.sql, included in the package), or LogSQLCreateTables must be set to 'On'.</para>
958 </note>
959 </listitem>
960 </varlistentry>
961 <varlistentry>
962 <term>LogSQLWhichHeadersIn</term>
963 <listitem>
964 <cmdsynopsis>
965 <command>LogSQLWhichHeadersIn</command>
966 <arg choice="req" rep="repeat"><replaceable>header-name</replaceable></arg>
967 </cmdsynopsis>
968 <simpara>Example: LogSQLWhichHeadersIn UserAgent Accept-Encodeing Host</simpara>
969 <simpara>Context: virtual host</simpara>
970 <para>Defines the list of inbound headers you would like logged. This works in conjunction with LogSQLHeadersInLogTable. This directive does not require any additional characters to be added to the LogSQLTransferLogFormat string. The feature is activated simply by including this directive, upon which you will begin populating the separate inbound-headers table with data.</para>
971 <note>
972 <para>The table must be created (see create-tables.sql, included in the package), or LogSQLCreateTables must be set to 'On'.</para>
973 </note>
974 </listitem>
975 </varlistentry>
976 <varlistentry>
977 <term>LogSQLWhichHeadersOut</term>
978 <listitem>
979 <cmdsynopsis>
980 <command>LogSQLWhichHeadersOut</command>
981 <arg choice="req" rep="repeat"><replaceable>header-name</replaceable></arg>
982 </cmdsynopsis>
983 <simpara>Example: LogSQLWhichHeadersOut Expires Content-Type Cache-Control</simpara>
984 <simpara>Context: virtual host</simpara>
985 <para>Defines the list of outbound headers you would like logged. This works in conjunction with LogSQLHeadersOutLogTable. This directive does not require any additional characters to be added to the LogSQLTransferLogFormat string. The feature is activated simply by including this directive, upon which you will begin populating the separate outbound-headers table with data.</para>
986 <note>
987 <para>The table must be created (see create-tables.sql, included in the package), or LogSQLCreateTables must be set to 'On'.</para>
988 </note>
989 </listitem>
990 </varlistentry>
991 <varlistentry>
992 <term>LogSQLWhichNotes</term>
993 <listitem>
994 <cmdsynopsis>
995 <command>LogSQLWhichNotes</command>
996 <arg choice="req" rep="repeat"><replaceable>note-name</replaceable></arg>
997 </cmdsynopsis>
998 <simpara>Example: LogSQLWhichNotes mod_gzip_result mod_gzip_ompression_ratio</simpara>
999 <simpara>Context: virtual host</simpara>
1000 <para>Defines the list of notes you would like logged. This works in conjunction with LogSQLNotesLogTable. This directive does not require any additional characters to be added to the LogSQLTransferLogFormat string. The feature is activated simply by including this directive, upon which you will begin populating the separate notes table with data.</para>
1001 <note>
1002 <para>The table must be created (see create-tables.sql, included in the package), or LogSQLCreateTables must be set to 'On'.</para>
1003 </note>
1004 </listitem>
1005 </varlistentry>
1006 </variablelist>
1007 <sect3> 631 <sect3>
1008 <title>DataBase Configuration</title> 632 <title>DataBase Configuration</title>
1009 <variablelist> 633 <variablelist>
1010 <varlistentry> 634 <varlistentry>
1011 <term></term> 635 <term>LogSQLLoginInfo</term>
636 <listitem>
637 <cmdsynopsis sepchar=" ">
638 <command moreinfo="none">LogSQLLoginInfo</command>
639 <arg choice="req" rep="norepeat"><replaceable>host</replaceable></arg>
640 <arg choice="req" rep="norepeat"><replaceable>user</replaceable></arg>
641 <arg choice="req" rep="norepeat"><replaceable>password</replaceable></arg>
642 </cmdsynopsis>
643 <simpara>Example: LogSQLLoginInfo foobar.baz.com logwriter passw0rd</simpara>
644 <simpara>Context: main server config</simpara>
645 <para>Defines the general parameters of the MySQL host to which you will be logging. "host" is the hostname or IP address of the MySQL machine, and is simply "localhost" if the database lives on the same machine as Apache. "user" is the MySQL userid (not a Unix userid!) with INSERT privileges on the table defined in LogSQLTransferLogTable. "password" is that user's password.</para>
646 <note>
647 <para>This is defined only once in the <filename moreinfo="none">httpd.conf</filename> file.</para>
648 <para>This directive Must be defined for logging to be enabled. Or the use of LogSQLDBParam</para>
649 </note>
650 </listitem>
651 </varlistentry>
652 <varlistentry>
653 <term>LogSQLDBParam</term>
654 <listitem>
655 <cmdsynopsis sepchar=" ">
656 <command moreinfo="none">LogSQLDBParam</command>
657 <arg choice="req" rep="norepeat"><replaceable>parameter-name</replaceable></arg>
658 <arg choice="req" rep="norepeat"><replaceable>value</replaceable></arg>
659 </cmdsynopsis>
660 <simpara>Example: LogSQLDBParam database loggingdb</simpara>
661 <simpara>Context: main server config</simpara>
662 <para>This is the new method of specifying Database connection credentials and settings. This can be used to define all Database specific options and allows for a specific database driver to define new configuration attributes without modifying the mod_log_sql core. This is the preferred configuration mechanism.</para>
663 <note>
664 <para>Each parameter-name may only be defined once.</para>
665 <para>Either this directive defining the minimum of 'database','host','user','passwd' or the use of LogSQlLoginInfo and LogSQLDatabase</para>
666 </note>
667 </listitem>
668 </varlistentry>
669 <varlistentry>
670 <term>LogSQLCreateTables</term>
671 <listitem>
672 <cmdsynopsis sepchar=" ">
673 <command moreinfo="none">LogSQLCreateTables</command>
674 <arg choice="req" rep="norepeat">flag</arg>
675 </cmdsynopsis>
676 <simpara>Example: LogSQLCreateTables On</simpara>
677 <simpara>Default: Off</simpara>
678 <simpara>Context: main server config</simpara>
679 <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>
680 <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>
681 <note>
682 <para>This is defined only once in the <filename moreinfo="none">httpd.conf</filename> file.</para>
683 </note>
684 </listitem>
685 </varlistentry>
686 <varlistentry>
687 <term>LogSQLSocketFile [Deprecated]</term>
688 <listitem>
689 <cmdsynopsis sepchar=" ">
690 <command moreinfo="none">LogSQLSocketFile</command>
691 <arg choice="req" rep="norepeat"><replaceable>filename</replaceable></arg>
692 </cmdsynopsis>
693 <simpara>Example: LogSQLSocketFile /tmp/mysql.sock</simpara>
694 <simpara>Default: (database specific)</simpara>
695 <simpara>Default (MySQL): /var/lib/mysql/mysql.sock</simpara>
696 <simpara>Context: main server config</simpara>
697 <para>At Apache runtime you can specify the MySQL socket file to use. Set this once in your main server config to override the default value. This value is irrelevant if your database resides on a separate machine.</para>
698 <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>
699 <note>
700 <para>This directive is deprecated in favor of LogSQLDBParam socketfile [socketfilename]</para>
701 <para>This is defined only once in the <filename moreinfo="none">httpd.conf</filename> file.</para>
702 </note>
703 </listitem>
704 </varlistentry>
705 <varlistentry>
706 <term>LogSQLTCPPort [Deprecated]</term>
707 <listitem>
708 <cmdsynopsis sepchar=" ">
709 <command moreinfo="none">LogSQLTCPPort</command>
710 <arg choice="req" rep="norepeat"><replaceable>port-number</replaceable></arg>
711 </cmdsynopsis>
712 <simpara>Example: LogSQLTCPPort 3309</simpara>
713 <simpara>Default: (database specific)</simpara>
714 <simpara>Default (MySQL): 3306</simpara>
715 <simpara>Context: main server config</simpara>
716 <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>
717 <note>
718 <para>This directive is deprecated in favor of LogSQLDBParam tcpport [port-number]</para>
719 <para>This is defined only once in the <filename moreinfo="none">httpd.conf</filename> file.</para>
720 </note>
721 </listitem>
722 </varlistentry>
723 <varlistentry>
724 <term>LogSQLDatabase [Deprecated]</term>
725 <listitem>
726 <cmdsynopsis sepchar=" ">
727 <command moreinfo="none">LogSQLDatabase</command>
728 <arg choice="req" rep="norepeat"><replaceable>database</replaceable></arg>
729 </cmdsynopsis>
730 <simpara>Example: LogSQLDatabase loggingdb</simpara>
731 <simpara>Context: main server config</simpara>
732 <para>Defines the database that is used for logging. "database" must be a valid db on the MySQL host defined in LogSQLLoginInfo</para>
733 <note>
734 <para>This directive is deprecated in favor of LogSQLDBParam database [database-name]</para>
735 <para>This is defined only once in the <filename moreinfo="none">httpd.conf</filename> file.</para>
736 <para>This directive Must be defined for logging to be enabled.</para>
737 </note>
738 </listitem>
739 </varlistentry>
740 <varlistentry>
741 <term>LogSQLForcePreserve</term>
742 <listitem>
743 <cmdsynopsis sepchar=" ">
744 <command moreinfo="none">LogSQLForcePreserve</command>
745 <arg choice="req" rep="norepeat">flag</arg>
746 </cmdsynopsis>
747 <simpara>Example: LogForcePreserve On</simpara>
748 <simpara>Default: Off</simpara>
749 <simpara>Context: main server config</simpara>
750 <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>
751 <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>
752 <note>
753 <para>This is defined only once in the <filename moreinfo="none">httpd.conf</filename> file.</para>
754 </note>
755 </listitem>
756 </varlistentry>
757 <varlistentry>
758 <term>LogSQLMachineID</term>
759 <listitem>
760 <cmdsynopsis sepchar=" ">
761 <command moreinfo="none">LogSQLMachineID</command>
762 <arg choice="req" rep="norepeat">machineID</arg>
763 </cmdsynopsis>
764 <simpara>Example: LogSQLMachineID web01</simpara>
765 <simpara>Context: main server config</simpara>
766 <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>
767 <note>
768 <para>This is defined only once in the <filename moreinfo="none">httpd.conf</filename> file.</para>
769 </note>
770 </listitem>
771 </varlistentry>
772 <varlistentry>
773 <term>LogSQlPreserveFile</term>
774 <listitem>
775 <cmdsynopsis sepchar=" ">
776 <command moreinfo="none">LogSQLPreserveFile</command>
777 <arg choice="req" rep="norepeat"><replaceable>filename</replaceable></arg>
778 </cmdsynopsis>
779 <simpara>Example: LogSQLPreserveFile offline-preserve</simpara>
780 <simpara>Default: /tmp/sql-preserve</simpara>
781 <simpara>Context: virtual host</simpara>
782 <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>
783 <screen format="linespecific"># mysql -uadminuser -p mydbname &lt; /tmp/sql-preserve</screen>
784 <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>
785 <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>
786 <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>
787 </listitem>
788 </varlistentry>
789 </variablelist>
790 </sect3>
791 <sect3>
792 <title>Table Names</title>
793 <variablelist>
794 <varlistentry>
795 <term>LogSQLTransferLogTable</term>
796 <listitem>
797 <cmdsynopsis sepchar=" ">
798 <command moreinfo="none">LogSQLTransferLogTable</command>
799 <arg choice="req" rep="norepeat"><replaceable>table-name</replaceable></arg>
800 </cmdsynopsis>
801 <simpara>Example: LogSQLTransferLogTable access_log_table</simpara>
802 <simpara>Context: virtual host</simpara>
803 <para>Defines which table is used for logging of Apache's transfers; this is analogous to Apache's TransferLog directive. table-name must be a valid table within the database defined in LogSQLDatabase.</para>
804 <para>This directive is <emphasis>not</emphasis> necessary if you declare LogSQLMassVirtualHosting On, since that directive activates dynamically-named tables. If you attempt to use LogSqlTransferlogTable at the same time a warning will be logged and it will be ignored, since LogSQLMassVirtualHosting takes priority.</para>
805 <note>
806 <para>Requires unless LogSQLMassVirtualHosting is set to On</para>
807 </note>
808 </listitem>
809 </varlistentry>
810 <varlistentry>
811 <term>LogSQLCookieLogTable</term>
812 <listitem>
813 <cmdsynopsis sepchar=" ">
814 <command moreinfo="none">LogSQLCookieLogTable</command>
815 <arg choice="req" rep="norepeat"><replaceable></replaceable>table-name</arg>
816 </cmdsynopsis>
817 <simpara>Example: LogSQLCookieLogTable cookie_log</simpara>
818 <simpara>Default: cookies</simpara>
819 <simpara>Context: virtual host</simpara>
820 <para>Defines which table is used for logging of cookies. Working in conjunction with LogSQLWhichCookies, you can log many of each request's associated cookies to a separate table. For meaningful data retrieval the cookie table is keyed to the access table by the unique request ID supplied by the standard Apache module mod_unique_id.</para>
821 <note>
822 <para>You must create the table (see create-tables.sql, included in the package), or LogSQLCreateTables must be set to "on".</para>
823 </note>
824 </listitem>
825 </varlistentry>
826 <varlistentry>
827 <term>LogSQLHeadersInLogTable</term>
828 <listitem>
829 <cmdsynopsis sepchar=" ">
830 <command moreinfo="none">LogSQLHeadersInLogTable</command>
831 <arg choice="req" rep="norepeat"><replaceable>table-name</replaceable></arg>
832 </cmdsynopsis>
833 <simpara>Example: LogSQLHeadersInLogTable headers</simpara>
834 <simpara>Default: headers_in</simpara>
835 <simpara>Context: virtual host</simpara>
836 <para>Defines which table is used for logging of inbound headers. Working in conjunction with LogSQLWhichHeadersIn, you can log many of each request's associated headers to a separate table. For meaningful data retrieval the headers table is keyed to the access table by the unique request ID supplied by the standard Apache module mod_unique_id.</para>
837 <note>
838 <para>Note that you must create the table (see create-tables.sql, included in the package), or LogSQLCreateTables must be set to "on".</para>
839 </note>
840 </listitem>
841 </varlistentry>
842 <varlistentry>
843 <term>LogSQLHeadersOutLogTable</term>
844 <listitem>
845 <cmdsynopsis sepchar=" ">
846 <command moreinfo="none">LogSQLHeadersOutLogTable</command>
847 <arg choice="req" rep="norepeat"><replaceable>table-name</replaceable></arg>
848 </cmdsynopsis>
849 <simpara>Example: LogSQLHeadersOutLogTable headers</simpara>
850 <simpara>Default: headers_out</simpara>
851 <simpara>Context: virtual host</simpara>
852 <para>Defines which table is used for logging of outbound headers. Working in conjunction with LogSQLWhichHeadersOut, you can log many of each request's associated headers to a separate table. For meaningful data retrieval the headers table is keyed to the access table by the unique request ID supplied by the standard Apache module mod_unique_id.</para>
853 <note>
854 <para>Note that you must create the table (see create-tables.sql, included in the package), or LogSQLCreateTables must be set to "on".</para>
855 </note>
856 </listitem>
857 </varlistentry>
858 <varlistentry>
859 <term>LogSQLNotesLogTable</term>
860 <listitem>
861 <cmdsynopsis sepchar=" ">
862 <command moreinfo="none">LogSQLNotesLogTable</command>
863 <arg choice="req" rep="norepeat"><replaceable>table-name</replaceable></arg>
864 </cmdsynopsis>
865 <simpara>Example: LogSQLNotesLogTable notes-log</simpara>
866 <simpara>Default: notes</simpara>
867 <simpara>Context: virtual_host</simpara>
868 <para>Defines which table is used for logging of notes. Working in conjunction with LogSQLWhichNotes, you can log many of each request's associated notes to a separate table. For meaningful data retrieval the notes table is keyed to the access table by the unique request ID supplied by the standard Apache module mod_unique_id.</para>
869 <note>
870 <para>This table must be created (see create-tables.sql included in the package), or LogSQLCreateTables must be set to 'On'.</para>
871 </note>
872 </listitem>
873 </varlistentry>
874 <varlistentry>
875 <term>LogSQLMassVirtualHosting</term>
876 <listitem>
877 <cmdsynopsis sepchar=" ">
878 <command moreinfo="none">LogSQLMassVirtualHosting</command>
879 <arg choice="req" rep="norepeat">flag</arg>
880 </cmdsynopsis>
881 <simpara>Example: LogSQLMassVirtualHosting On</simpara>
882 <simpara>Default: Off</simpara>
883 <simpara>Context: main server config</simpara>
884 <para>If you administer a site hosting many, many virtual hosts then this option will appeal to you. If you turn on LogSQLMassVirtualHosting then several things happen:</para>
885 <itemizedlist>
886 <listitem>
887 <para>the on-the-fly table creation feature is activated automatically</para>
888 </listitem>
889 <listitem>
890 <para>the transfer log table name is dynamically set from the virtual host's name after stripping out SQL-unfriendly characters (example: a virtual host www.grubbybaby.com gets logged to table access_www_grubbybaby_com)</para>
891 </listitem>
892 <listitem>
893 <para>which, in turn, means that each virtual host logs to its own segregated table. Because there is no data shared between virtual servers you can grant your users access to the tables they need; they will be unable to view others' data.</para>
894 </listitem>
895 </itemizedlist>
896 <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>
897 <note>
898 <para>This is defined only once in the <filename moreinfo="none">httpd.conf</filename> file.</para>
899 </note>
900 </listitem>
901 </varlistentry>
902 </variablelist>
903 </sect3>
904 <sect3>
905 <title>Configuring What Is logged</title>
906 <variablelist>
907 <varlistentry>
908 <term>LogSQLRemhostIgnore</term>
909 <listitem>
910 <cmdsynopsis sepchar=" ">
911 <command moreinfo="none">LogSQLRemhostIgnore</command>
912 <arg choice="req" rep="repeat"><replaceable>hostname</replaceable></arg>
913 </cmdsynopsis>
914 <simpara>Example: LogSQLRemhostIgnore localnet.com</simpara>
915 <simpara>Context: virtual host</simpara>
916 <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>
917 <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>
918 </listitem>
919 </varlistentry>
920 <varlistentry>
921 <term>LogSQLRequestAccept</term>
922 <listitem>
923 <cmdsynopsis sepchar=" ">
924 <command moreinfo="none">LogSQLRequestAccept</command>
925 <arg choice="req" rep="repeat"><replaceable>hostname</replaceable></arg>
926 </cmdsynopsis>
927 <simpara>Example: LogSQLRequestAccept .html .php .jpg</simpara>
928 <simpara>Default: if not specified, all requests are 'accepted'</simpara>
929 <simpara>Context: virtual host</simpara>
930 <para>Lists a series of strings that, if present in the URI, will permit that request to be considered for logging (depending on additional filtering by the "ignore" directives). Any request that fails to match one of the LogSQLRequestAccept entries will be discarded.</para>
931 <para>This directive is useful for cutting down on log clutter when you are certain that you only want to log certain kinds of requests, and just blanket-ignore everything else. See section <xref endterm="Sect.Ignore.title" linkend="Sect.Ignore"/> for some tips for using this directive.</para>
932 <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 URI using strstr(). The comparison is case sensitive.</para>
933 <para>This directive is completely optional. It is more general than LogSQLRequestIgnore and is evaluated before LogSQLRequestIgnore . If this directive is not used, <emphasis>all</emphasis> requests are accepted and passed on to the other filtering directives. Therefore, only use this directive if you have a specific reason to do so.</para>
934 </listitem>
935 </varlistentry>
936 <varlistentry>
937 <term>LogSQLRequestIgnore</term>
1012 <listitem> 938 <listitem>
1013 <para></para> 939 <cmdsynopsis sepchar=" ">
940 <command moreinfo="none">LogSQLRequestIgnore</command>
941 <arg choice="req" rep="repeat"><replaceable>hostname</replaceable></arg>
942 </cmdsynopsis>
943 <simpara>Example: LogSQLRequestIgnore root.exe cmd.exe default.ida favicon.ico</simpara>
944 <simpara>Context: virtual host</simpara>
945 <para>Lists a series of strings that, if present in the URI, 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 for certain objects. See section <xref endterm="Sect.Ignore.title" linkend="Sect.Ignore"/> for some tips for using this directive.</para>
946 <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 URI using strstr(). The comparison is case sensitive.</para>
947 </listitem>
948 </varlistentry>
949 <varlistentry id="Conf.LogSQLTransferLogFormat">
950 <term>LogSQLTransferLogFormat </term>
951 <listitem>
952 <cmdsynopsis sepchar=" ">
953 <command moreinfo="none">LogSQLTransferLogFormat</command>
954 <arg choice="req" rep="norepeat"><replaceable>format-string</replaceable></arg>
955 </cmdsynopsis>
956 <simpara>Example: LogSQLTransferLogFormat huSUTv</simpara>
957 <simpara>Default: AbHhmRSsTUuv</simpara>
958 <simpara>Context: virtual host</simpara>
959 <para>Each character in the format-string defines an attribute of the request that you wish to log. The default logs the information required to create Combined Log Format logs, plus several extras. Here is the full list of allowable keys, which sometimes resemble their Apache counterparts, but do not always:</para>
960 <para>TODO: Insert Log Format Types Table</para>
961 </listitem>
962 </varlistentry>
963 <varlistentry>
964 <term>LogSQLWhichCookie</term>
965 <listitem>
966 <cmdsynopsis sepchar=" ">
967 <command moreinfo="none">LogSQLWhichCookie</command>
968 <arg choice="req" rep="norepeat"><replaceable>cookiename</replaceable></arg>
969 </cmdsynopsis>
970 <simpara>Example; LogSQLWhichCookie Clicks</simpara>
971 <simpara>Context: virtual host</simpara>
972 <para>In HTTP, cookies have names to distinguish them from each other. Using mod_usertrack, for example, you can give your user-tracking cookies a name with the CookieName directive.</para>
973 <para>mod_log_sql allows you to log cookie information. LogSQL_WhichCookie tells mod_log_sql which cookie to log. This is necessary because you will usually be setting and receiving more than one cookie from a client.</para>
974 <note>
975 <para>You must include a 'c' character in LogSQLTransferLogFormat for this directive to take effect.</para>
976 <para>although this was origintally intended for people using mod_usertrack to create user-tracking cookies, you are not restricted in any way. You can choose which cookie you wish to log to the database - any cookie at all - and it does not necessarily have to have anything to do with mod_usertrack.</para>
977 </note>
978 </listitem>
979 </varlistentry>
980 <varlistentry>
981 <term>LogSQLWhichCookies</term>
982 <listitem>
983 <cmdsynopsis sepchar=" ">
984 <command moreinfo="none">LogSQLWhichCookies</command>
985 <arg choice="req" rep="repeat"><replaceable>cookie-name</replaceable></arg>
986 </cmdsynopsis>
987 <simpara>Example: logSQLWhichCookies userlogin cookie1 cookie2</simpara>
988 <simpara>Context: virtual host</simpara>
989 <para>Defines the list of cookies you would like logged. This works in conjunction with LogSQLCookieLogTable. This directive does <emphasis>not</emphasis> require any additional characters to be added to the LogSQLTransferLogFormat string. The feature is activated simply by including this directive, upon which you will begin populating the separate cookie table with data.</para>
990 <note>
991 <para>The table must be created (see create-tables.sql, included in the package), or LogSQLCreateTables must be set to 'On'.</para>
992 </note>
993 </listitem>
994 </varlistentry>
995 <varlistentry>
996 <term>LogSQLWhichHeadersIn</term>
997 <listitem>
998 <cmdsynopsis sepchar=" ">
999 <command moreinfo="none">LogSQLWhichHeadersIn</command>
1000 <arg choice="req" rep="repeat"><replaceable>header-name</replaceable></arg>
1001 </cmdsynopsis>
1002 <simpara>Example: LogSQLWhichHeadersIn UserAgent Accept-Encodeing Host</simpara>
1003 <simpara>Context: virtual host</simpara>
1004 <para>Defines the list of inbound headers you would like logged. This works in conjunction with LogSQLHeadersInLogTable. This directive does not require any additional characters to be added to the LogSQLTransferLogFormat string. The feature is activated simply by including this directive, upon which you will begin populating the separate inbound-headers table with data.</para>
1005 <note>
1006 <para>The table must be created (see create-tables.sql, included in the package), or LogSQLCreateTables must be set to 'On'.</para>
1007 </note>
1008 </listitem>
1009 </varlistentry>
1010 <varlistentry>
1011 <term>LogSQLWhichHeadersOut</term>
1012 <listitem>
1013 <cmdsynopsis sepchar=" ">
1014 <command moreinfo="none">LogSQLWhichHeadersOut</command>
1015 <arg choice="req" rep="repeat"><replaceable>header-name</replaceable></arg>
1016 </cmdsynopsis>
1017 <simpara>Example: LogSQLWhichHeadersOut Expires Content-Type Cache-Control</simpara>
1018 <simpara>Context: virtual host</simpara>
1019 <para>Defines the list of outbound headers you would like logged. This works in conjunction with LogSQLHeadersOutLogTable. This directive does not require any additional characters to be added to the LogSQLTransferLogFormat string. The feature is activated simply by including this directive, upon which you will begin populating the separate outbound-headers table with data.</para>
1020 <note>
1021 <para>The table must be created (see create-tables.sql, included in the package), or LogSQLCreateTables must be set to 'On'.</para>
1022 </note>
1023 </listitem>
1024 </varlistentry>
1025 <varlistentry>
1026 <term>LogSQLWhichNotes</term>
1027 <listitem>
1028 <cmdsynopsis sepchar=" ">
1029 <command moreinfo="none">LogSQLWhichNotes</command>
1030 <arg choice="req" rep="repeat"><replaceable>note-name</replaceable></arg>
1031 </cmdsynopsis>
1032 <simpara>Example: LogSQLWhichNotes mod_gzip_result mod_gzip_ompression_ratio</simpara>
1033 <simpara>Context: virtual host</simpara>
1034 <para>Defines the list of notes you would like logged. This works in conjunction with LogSQLNotesLogTable. This directive does not require any additional characters to be added to the LogSQLTransferLogFormat string. The feature is activated simply by including this directive, upon which you will begin populating the separate notes table with data.</para>
1035 <note>
1036 <para>The table must be created (see create-tables.sql, included in the package), or LogSQLCreateTables must be set to 'On'.</para>
1037 </note>
1014 </listitem> 1038 </listitem>
1015 </varlistentry> 1039 </varlistentry>
1016 </variablelist> 1040 </variablelist>
@@ -1022,7 +1046,7 @@ where a.id=n.id and a.id='PPIDskBRH30AAGPtAsg';</screen>
1022 <qandaset> 1046 <qandaset>
1023 <qandadiv> 1047 <qandadiv>
1024 <title>General module questions</title> 1048 <title>General module questions</title>
1025 <qandaentry id="WhyLogToSQL"> 1049 <qandaentry id="FAQ.WhyLogToSQL">
1026 <question> 1050 <question>
1027 <para>Why log to an SQL database?</para> 1051 <para>Why log to an SQL database?</para>
1028 </question> 1052 </question>
@@ -1212,7 +1236,7 @@ where a.id=n.id and a.id='PPIDskBRH30AAGPtAsg';</screen>
1212 <para>Does mod_log_sql work with Apache 2.x?</para> 1236 <para>Does mod_log_sql work with Apache 2.x?</para>
1213 </question> 1237 </question>
1214 <answer> 1238 <answer>
1215 <para>Yes. A port of mod_log_sql is available for Apache 2.x, and is currently available and maintained at <ulink url="http://www.outoforder.cc/projects/apache/">OutOfOrder.CC</ulink></para> 1239 <para>Yes. A port of mod_log_sql is available for Apache 2.x as of mod_log_sql 1.90</para>
1216 </answer> 1240 </answer>
1217 </qandaentry> 1241 </qandaentry>
1218 <qandaentry> 1242 <qandaentry>
@@ -1255,7 +1279,7 @@ where a.id=n.id and a.id='PPIDskBRH30AAGPtAsg';</screen>
1255 <para>PHP and mod_log_sql can be configured to happily coexist. The solution is to configure PHP to link against the real MySQL libraries: recompile PHP using --with-mysql=/your/path. Apache will run properly once the modules are all using the same version of the MySQL libraries.</para> 1279 <para>PHP and mod_log_sql can be configured to happily coexist. The solution is to configure PHP to link against the real MySQL libraries: recompile PHP using --with-mysql=/your/path. Apache will run properly once the modules are all using the same version of the MySQL libraries.</para>
1256 </answer> 1280 </answer>
1257 </qandaentry> 1281 </qandaentry>
1258 <qandaentry id="NothingLogged"> 1282 <qandaentry id="FAQ.NothingLogged">
1259 <question> 1283 <question>
1260 <para>Apache appears to start up fine, but nothing is getting logged in the database</para> 1284 <para>Apache appears to start up fine, but nothing is getting logged in the database</para>
1261 </question> 1285 </question>
@@ -1266,17 +1290,17 @@ where a.id=n.id and a.id='PPIDskBRH30AAGPtAsg';</screen>
1266 <simpara>Improper privileges set up in the MySQL database </simpara> 1290 <simpara>Improper privileges set up in the MySQL database </simpara>
1267 </listitem> 1291 </listitem>
1268 <listitem> 1292 <listitem>
1269 <simpara>You aren't hitting a VirtualHost that has a LogSQLTransferLogTable entry </simpara> 1293 <simpara>You are not hitting a VirtualHost that has a LogSQLTransferLogTable entry </simpara>
1270 </listitem> 1294 </listitem>
1271 <listitem> 1295 <listitem>
1272 <simpara>You didn't specify the right database host or login information</simpara> 1296 <simpara>You did not specify the right database host or login information</simpara>
1273 </listitem> 1297 </listitem>
1274 <listitem> 1298 <listitem>
1275 <simpara>Another factor is preventing a connection to the database</simpara> 1299 <simpara>Another factor is preventing a connection to the database</simpara>
1276 </listitem> 1300 </listitem>
1277 </itemizedlist> 1301 </itemizedlist>
1278 <remark>It is improper to ask for help before you have followed these steps.</remark> 1302 <remark>It is improper to ask for help before you have followed these steps.</remark>
1279 <para>First examine the MySQL log that you established in step <link linkend="EnableLogging"></link> of section <link linkend="Preperation"></link>. 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> 1303 <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>
1280 <para>Second, confirm that your LogSQL* directives are all correct.</para> 1304 <para>Second, confirm that your LogSQL* directives are all correct.</para>
1281 <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> 1305 <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>
1282 <para>The next thing to do is to change the LogLevel directive <emphasis>in the main server config as well as in each VirtualHost config:</emphasis></para> 1306 <para>The next thing to do is to change the LogLevel directive <emphasis>in the main server config as well as in each VirtualHost config:</emphasis></para>
@@ -1297,7 +1321,7 @@ ErrorLog /var/log/httpd/server-messages </programlisting>
1297 <para>My database cannot handle all the open connections from mod_log_sql, is there anything I can do?</para> 1321 <para>My database cannot handle all the open connections from mod_log_sql, is there anything I can do?</para>
1298 </question> 1322 </question>
1299 <answer> 1323 <answer>
1300 <para>The rule of thumb: if you have n webservers each configured to support y MaxClients, then your database must be able to handle n\times y simultenous connections in the worst case. Certainly you must use common sense, consider reasonable traffic expectations and structure things accordingly.</para> 1324 <para>The rule of thumb: if you have n webservers each configured to support y MaxClients, then your database must be able to handle n times y simultaneous connections in the worst case. Certainly you must use common sense, consider reasonable traffic expectations and structure things accordingly.</para>
1301 </answer> 1325 </answer>
1302 <answer> 1326 <answer>
1303 <para>Tweaking my.cnf to scale to high connection loads is imperative. But if hardware limitations prevent your MySQL server from gracefully handling the number of incoming connections, it would be beneficial to upgrade the memory or CPU on that server in order to handle the load. </para> 1327 <para>Tweaking my.cnf to scale to high connection loads is imperative. But if hardware limitations prevent your MySQL server from gracefully handling the number of incoming connections, it would be beneficial to upgrade the memory or CPU on that server in order to handle the load. </para>
@@ -1306,7 +1330,7 @@ ErrorLog /var/log/httpd/server-messages </programlisting>
1306 <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> 1330 <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>
1307 </answer> 1331 </answer>
1308 <answer> 1332 <answer>
1309 <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 doesn't attempt to shoehorn all the database traffic through a single extra daemon or proxy process.</para> 1333 <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>
1310 </answer> 1334 </answer>
1311 <answer> 1335 <answer>
1312 <remark>Currently connection pooling is being implemented as part of the Database Abstraction layer to allow multiple httpd processes to share connections.</remark> 1336 <remark>Currently connection pooling is being implemented as part of the Database Abstraction layer to allow multiple httpd processes to share connections.</remark>
@@ -1317,7 +1341,7 @@ ErrorLog /var/log/httpd/server-messages </programlisting>
1317 <para>Why do I occasionally see a "lost connection to MySQL server" message in my Apache error log?</para> 1341 <para>Why do I occasionally see a "lost connection to MySQL server" message in my Apache error log?</para>
1318 </question> 1342 </question>
1319 <answer> 1343 <answer>
1320 <para>This message may appear every now and then in your Apache error log, especially on very lightly loaded servers. This doesn't mean that anything is necessarily wrong. Within each httpd child process, mod_log_sql will open (and keep open) a connection to the MySQL server. MySQL, however, will close connections that haven't been used in a while; the default timeout is 8 hours. When this occurs, mod_log_sql will notice and re-open the connection. That event is what is being logged, and looks like this:</para> 1344 <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>
1321 <screen>[Tue Nov 12 19:04:10 2002] [error] mod_log_sql: first attempt failed, 1345 <screen>[Tue Nov 12 19:04:10 2002] [error] mod_log_sql: first attempt failed,
1322 API said: error 2013, Lost connection to MySQL server during query 1346 API said: error 2013, Lost connection to MySQL server during query
1323[Tue Nov 12 19:04:10 2002] [error] mod_log_sql: reconnect successful 1347[Tue Nov 12 19:04:10 2002] [error] mod_log_sql: reconnect successful
@@ -1429,8 +1453,8 @@ mysql&gt; optimize table access_log; </screen>
1429 <para>If you have exhausted all the tuning possibilities on your existing server, it is probably time you evaluated the benefits of clustering two or more webservers together in a load-balanced fashion. In fact, users of such a setup are mod_log_sql's target audience!</para> 1453 <para>If you have exhausted all the tuning possibilities on your existing server, it is probably time you evaluated the benefits of clustering two or more webservers together in a load-balanced fashion. In fact, users of such a setup are mod_log_sql's target audience!</para>
1430 </answer> 1454 </answer>
1431 </qandaentry> 1455 </qandaentry>
1432 <qandaentry id="DelayedInsertFAQ"> 1456 <qandaentry id="FAQ.DelayedInsert">
1433 <question id="DelayedInsertFAQ.title"> 1457 <question>
1434 <para>What is the issue with activating delayed inserts?</para> 1458 <para>What is the issue with activating delayed inserts?</para>
1435 </question> 1459 </question>
1436 <answer> 1460 <answer>
@@ -1449,7 +1473,7 @@ mysql&gt; optimize table access_log; </screen>
1449 <para>Instead of delayed inserts, you may wish to utilize InnoDB tables (instead of the standard MyISAM tables). InnoDB tables suppot row-level locking and are recommended for high-volume databases.</para> 1473 <para>Instead of delayed inserts, you may wish to utilize InnoDB tables (instead of the standard MyISAM tables). InnoDB tables suppot row-level locking and are recommended for high-volume databases.</para>
1450 </answer> 1474 </answer>
1451 <answer> 1475 <answer>
1452 <para>If after understanding these problems you still wish to enable delayed inserts, section <link linkend="DelayedInsert"></link> discusses how.</para> 1476 <para>If after understanding these problems you still wish to enable delayed inserts, section <xref endterm="Sect.DelayedInsert.title" linkend="Sect.DelayedInsert"/> discusses how.</para>
1453 </answer> 1477 </answer>
1454 </qandaentry> 1478 </qandaentry>
1455 </qandadiv> 1479 </qandadiv>
@@ -1474,7 +1498,7 @@ rm -f /var/log/httpd/templog</programlisting>
1474 <para>See? Easy.</para> 1498 <para>See? Easy.</para>
1475 </answer> 1499 </answer>
1476 </qandaentry> 1500 </qandaentry>
1477 <qandaentry id="QnACookie"> 1501 <qandaentry id="FAQ.Cookie">
1478 <question> 1502 <question>
1479 <para>How can I log mod_usertrack cookies?</para> 1503 <para>How can I log mod_usertrack cookies?</para>
1480 </question> 1504 </question>
@@ -1532,8 +1556,8 @@ rm -f /var/log/httpd/templog</programlisting>
1532 </question> 1556 </question>
1533 <answer> 1557 <answer>
1534 <para>As of version 1.17, you have a choice in how you want cookie logging handled.</para> 1558 <para>As of version 1.17, you have a choice in how you want cookie logging handled.</para>
1535 <para>If you are interested in logging only one cookie per request, follow the instructions in section <link endterm="QnACookie" linkend="QnACookie"></link> above. That cookie will be logged to a column in the regular access_log table, and the actual cookie you want to log is specified with LogSQLWhichCookie. Don't forget to specify the 'c' character in LogSQLTransferLogFormat.</para> 1559 <para>If you are interested in logging only one cookie per request, follow the instructions in FAQ entry <xref linkend="FAQ.Cookie"/> above. That cookie will be logged to a column in the regular access_log table, and the actual cookie you want to log is specified with LogSQLWhichCookie. Don't forget to specify the 'c' character in LogSQLTransferLogFormat.</para>
1536 <para>If, however, you need to log multiple cookies per request, you must employ the LogSQLWhichCookies (note the plural) directive. The cookies you specify will be logged to a separate table (as discussed in section <link endterm="MultiTable.title" linkend="MultiTable"></link>), and entries in that table will be linked to the regular access_log entries via the unique ID that is supplied by mod_unique_id. Without mod_unique_id the information will still be logged but you will be unable to correlate which cookies go with which access-requests. Furthermore, with LogSQLWhichCookies, you do not need to include the 'c' character in LogSQLTransferLogFormat.</para> 1560 <para>If, however, you need to log multiple cookies per request, you must employ the LogSQLWhichCookies (note the plural) directive. The cookies you specify will be logged to a separate table (as discussed in section <xref endterm="Sect.MultiTable.title" linkend="Sect.MultiTable"/>), and entries in that table will be linked to the regular access_log entries via the unique ID that is supplied by mod_unique_id. Without mod_unique_id the information will still be logged but you will be unable to correlate which cookies go with which access-requests. Furthermore, with LogSQLWhichCookies, you do not need to include the 'c' character in LogSQLTransferLogFormat.</para>
1537 <para>LogSQLWhichCookie and LogSQLWhichCookies can coexist without conflict because they operate on entireley different tables, but you're better off choosing the one you need.</para> 1561 <para>LogSQLWhichCookie and LogSQLWhichCookies can coexist without conflict because they operate on entireley different tables, but you're better off choosing the one you need.</para>
1538 </answer> 1562 </answer>
1539 </qandaentry> 1563 </qandaentry>
@@ -1556,7 +1580,7 @@ mysql&gt; alter table access_log add column ssl_maxkeysize smallint unsigned;</s
1556 LogSQLTransferLogFormat AbHhmRSsTUuvcQqz 1580 LogSQLTransferLogFormat AbHhmRSsTUuvcQqz
1557&lt;/VirtualHost&gt;</programlisting> 1581&lt;/VirtualHost&gt;</programlisting>
1558 <para>You also need to make sure you have the mod_log_sql_ssl module loaded as well.</para> 1582 <para>You also need to make sure you have the mod_log_sql_ssl module loaded as well.</para>
1559 <para>The last three characters (Qqz) in the directive are the SSL ones; see section <link linkend="TLogFormat"></link> in the directives documentation for details of the LogSQLTransferLogFormat directive.</para> 1583 <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>
1560 <para>Restart Apache, then perform some hits on your server. Then run the following select statement:</para> 1584 <para>Restart Apache, then perform some hits on your server. Then run the following select statement:</para>
1561 <screen>mysql&gt; select remote_host,request_uri,ssl_cipher,ssl_keysize,ssl_maxkeysize from access_log where ssl_cipher is not null;</screen> 1585 <screen>mysql&gt; select remote_host,request_uri,ssl_cipher,ssl_keysize,ssl_maxkeysize from access_log where ssl_cipher is not null;</screen>
1562 <table> 1586 <table>
generated by cgit (git 2.34.1) at 2024-11-10 07:08:04 +0000