summaryrefslogtreecommitdiffstats
path: root/docs/node4.html
diff options
context:
space:
mode:
Diffstat (limited to 'docs/node4.html')
-rw-r--r--docs/node4.html2014
1 files changed, 2014 insertions, 0 deletions
diff --git a/docs/node4.html b/docs/node4.html
new file mode 100644
index 0000000..287333c
--- /dev/null
+++ b/docs/node4.html
@@ -0,0 +1,2014 @@
1<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2 Final//EN">
2
3<!--Converted with LaTeX2HTML 2002-1 (1.68)
4original version by: Nikos Drakos, CBLU, University of Leeds
5* revised and updated by: Marcus Hennecke, Ross Moore, Herb Swan
6* with significant contributions from:
7 Jens Lippmann, Marek Rouchal, Martin Wilck and others -->
8<HTML>
9<HEAD>
10<TITLE>3 Configuration</TITLE>
11<META NAME="description" CONTENT="3 Configuration">
12<META NAME="keywords" CONTENT="documentation">
13<META NAME="resource-type" CONTENT="document">
14<META NAME="distribution" CONTENT="global">
15
16<META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=iso-8859-1">
17<META NAME="Generator" CONTENT="LaTeX2HTML v2002-1">
18<META HTTP-EQUIV="Content-Style-Type" CONTENT="text/css">
19
20<LINK REL="STYLESHEET" HREF="documentation.css">
21
22<LINK REL="next" HREF="node5.html">
23<LINK REL="previous" HREF="node3.html">
24<LINK REL="up" HREF="documentation.html">
25<LINK REL="next" HREF="node5.html">
26</HEAD>
27
28<BODY >
29<!--Navigation Panel-->
30<A NAME="tex2html172"
31 HREF="node5.html">
32<IMG WIDTH="37" HEIGHT="24" ALIGN="BOTTOM" BORDER="0" ALT="next" SRC="next.png"></A>
33<A NAME="tex2html168"
34 HREF="documentation.html">
35<IMG WIDTH="26" HEIGHT="24" ALIGN="BOTTOM" BORDER="0" ALT="up" SRC="up.png"></A>
36<A NAME="tex2html162"
37 HREF="node3.html">
38<IMG WIDTH="63" HEIGHT="24" ALIGN="BOTTOM" BORDER="0" ALT="previous" SRC="prev.png"></A>
39<A NAME="tex2html170"
40 HREF="node1.html">
41<IMG WIDTH="65" HEIGHT="24" ALIGN="BOTTOM" BORDER="0" ALT="contents" SRC="contents.png"></A>
42<BR>
43<B> Next:</B> <A NAME="tex2html173"
44 HREF="node5.html">4 FAQ</A>
45<B> Up:</B> <A NAME="tex2html169"
46 HREF="documentation.html">Installing and Running mod_log_sql</A>
47<B> Previous:</B> <A NAME="tex2html163"
48 HREF="node3.html">2 Installation</A>
49 &nbsp; <B> <A NAME="tex2html171"
50 HREF="node1.html">Contents</A></B>
51<BR>
52<BR>
53<!--End of Navigation Panel-->
54<!--Table of Child-Links-->
55<A NAME="CHILD_LINKS"><STRONG>Subsections</STRONG></A>
56
57<UL>
58<LI><A NAME="tex2html174"
59 HREF="node4.html#SECTION00041000000000000000">3.1 Preparing MySQL for logging</A>
60<LI><A NAME="tex2html175"
61 HREF="node4.html#SECTION00042000000000000000">3.2 A very basic logging setup in Apache</A>
62<LI><A NAME="tex2html176"
63 HREF="node4.html#SECTION00043000000000000000">3.3 Testing the basic setup</A>
64<LI><A NAME="tex2html177"
65 HREF="node4.html#SECTION00044000000000000000">3.4 How to tune logging with run-time directives</A>
66<UL>
67<LI><A NAME="tex2html178"
68 HREF="node4.html#SECTION00044100000000000000">3.4.1 Instructing the module what to log</A>
69<LI><A NAME="tex2html179"
70 HREF="node4.html#SECTION00044200000000000000">3.4.2 Instructing the module what NOT to log using filtering
71directives</A>
72</UL>
73<BR>
74<LI><A NAME="tex2html180"
75 HREF="node4.html#SECTION00045000000000000000">3.5 Advanced logging scenarios</A>
76<UL>
77<LI><A NAME="tex2html181"
78 HREF="node4.html#SECTION00045100000000000000">3.5.1 Using the module in an ISP environment</A>
79<LI><A NAME="tex2html182"
80 HREF="node4.html#SECTION00045200000000000000">3.5.2 Logging many-to-one data in separate tables</A>
81<LI><A NAME="tex2html183"
82 HREF="node4.html#SECTION00045300000000000000">3.5.3 Using the same database for production and test</A>
83<LI><A NAME="tex2html184"
84 HREF="node4.html#SECTION00045400000000000000">3.5.4 Optimizing for a busy database</A>
85</UL>
86<BR>
87<LI><A NAME="tex2html185"
88 HREF="node4.html#SECTION00046000000000000000">3.6 Configuration directive reference</A>
89<UL>
90<LI><A NAME="tex2html186"
91 HREF="node4.html#SECTION00046100000000000000">3.6.1 LogSQLCookieLogTable</A>
92<LI><A NAME="tex2html187"
93 HREF="node4.html#SECTION00046200000000000000">3.6.2 LogSQLCreateTables</A>
94<LI><A NAME="tex2html188"
95 HREF="node4.html#SECTION00046300000000000000">3.6.3 LogSQLDatabase </A>
96<LI><A NAME="tex2html189"
97 HREF="node4.html#SECTION00046400000000000000">3.6.4 LogSQLForcePreserve</A>
98<LI><A NAME="tex2html190"
99 HREF="node4.html#SECTION00046500000000000000">3.6.5 LogSQLHeadersInLogTable</A>
100<LI><A NAME="tex2html191"
101 HREF="node4.html#SECTION00046600000000000000">3.6.6 LogSQLHeadersOutLogTable</A>
102<LI><A NAME="tex2html192"
103 HREF="node4.html#SECTION00046700000000000000">3.6.7 LogSQLLoginInfo </A>
104<LI><A NAME="tex2html193"
105 HREF="node4.html#SECTION00046800000000000000">3.6.8 LogSQLMachineID</A>
106<LI><A NAME="tex2html194"
107 HREF="node4.html#SECTION00046900000000000000">3.6.9 LogSQLMassVirtualHosting</A>
108<LI><A NAME="tex2html195"
109 HREF="node4.html#SECTION000461000000000000000">3.6.10 LogSQLNotesLogTable</A>
110<LI><A NAME="tex2html196"
111 HREF="node4.html#SECTION000461100000000000000">3.6.11 LogSQLPreserveFile</A>
112<LI><A NAME="tex2html197"
113 HREF="node4.html#SECTION000461200000000000000">3.6.12 LogSQLRemhostIgnore</A>
114<LI><A NAME="tex2html198"
115 HREF="node4.html#SECTION000461300000000000000">3.6.13 LogSQLRequestAccept</A>
116<LI><A NAME="tex2html199"
117 HREF="node4.html#SECTION000461400000000000000">3.6.14 LogSQLRequestIgnore</A>
118<LI><A NAME="tex2html200"
119 HREF="node4.html#SECTION000461500000000000000">3.6.15 LogSQLSocketFile </A>
120<LI><A NAME="tex2html201"
121 HREF="node4.html#SECTION000461600000000000000">3.6.16 LogSQLTCPPort</A>
122<LI><A NAME="tex2html202"
123 HREF="node4.html#SECTION000461700000000000000">3.6.17 LogSQLTransferLogFormat </A>
124<LI><A NAME="tex2html203"
125 HREF="node4.html#SECTION000461800000000000000">3.6.18 LogSQLTransferLogTable</A>
126<LI><A NAME="tex2html204"
127 HREF="node4.html#SECTION000461900000000000000">3.6.19 LogSQLWhichCookie</A>
128<LI><A NAME="tex2html205"
129 HREF="node4.html#SECTION000462000000000000000">3.6.20 LogSQLWhichCookies</A>
130<LI><A NAME="tex2html206"
131 HREF="node4.html#SECTION000462100000000000000">3.6.21 LogSQLWhichHeadersIn</A>
132<LI><A NAME="tex2html207"
133 HREF="node4.html#SECTION000462200000000000000">3.6.22 LogSQLWhichHeadersOut</A>
134<LI><A NAME="tex2html208"
135 HREF="node4.html#SECTION000462300000000000000">3.6.23 LogSQLWhichNotes</A>
136</UL></UL>
137<!--End of Table of Child-Links-->
138<HR>
139
140<H1><A NAME="SECTION00040000000000000000"></A><A NAME="sec:Configuration"></A>
141<BR>
1423 Configuration
143</H1>
144
145<P>
146
147<H2><A NAME="SECTION00041000000000000000"></A><A NAME="sub:PrepDb"></A>
148<BR>
1493.1 Preparing MySQL for logging
150</H2>
151
152<P>
153You have to prepare the database to receive data from mod_log_sql,
154and set up run-time directives in httpd.conf to control how and what
155mod_log_sql logs.
156
157<P>
158This section will discuss how to get started with a basic config.
159Full documentation of all available run-time directives is available
160in section <A HREF="node4.html#sec:ConfRef">3.6</A>.
161
162<P>
163
164<OL>
165<LI>mod_log_sql can make its own tables on-the-fly, or you can pre-make
166the tables by hand. The advantage of letting the module make the tables
167is ease-of-use, but for raw performance you will want to pre-make
168the tables in order to save some overhead. In this basic setup we'll
169just let the module create tables for us.
170</LI>
171<LI>We still need to have a logging database created and ready, so run
172the MySQL command line client and create a database:
173
174<P>
175
176<DL COMPACT>
177<DT>
178<DD>#&nbsp;mysql&nbsp;-uadmin&nbsp;-pmypassword&nbsp;
179
180<P>
181Enter&nbsp;password:
182
183<P>
184mysql&gt;&nbsp;create&nbsp;database&nbsp;apachelogs;
185</DD>
186</DL>
187</LI>
188<LI><A NAME="part:CrTbl"></A>If you want to hand-create the tables, run the
189enclosed 'create-tables' SQL script as follows:
190
191<P>
192
193<DL COMPACT>
194<DT>
195<DD>mysql&gt;&nbsp;source&nbsp;create_tables.sql
196</DD>
197</DL>
198</LI>
199<LI>Create a specific MySQL userid that httpd will use to authenticate
200and enter data. This userid need not be an actual Unix user. It is
201a userid internal to MySQL with specific privileges. In the following
202example command, &#34;apachelogs&#34; is the database, &#34;loguser&#34;
203is the userid to create, &#34;my.apachemachine.com&#34;
204is the name of the Apache machine, and &#34;l0gger&#34;
205is the password to assign. Choose values that are different from these
206examples.
207
208<P>
209
210<DL COMPACT>
211<DT>
212<DD>mysql&gt;&nbsp;grant&nbsp;insert,create&nbsp;on&nbsp;apachelogs.*&nbsp;to&nbsp;loguser@my.apachemachine.com
213
214<P>
215identified&nbsp;by&nbsp;'l0gger';
216</DD>
217</DL>
218</LI>
219<LI>You may be especially security-paranoid and want &#34;loguser&#34;
220to <I>not</I> have &#34;create&#34; capability within the
221&#34;apachelogs&#34; database. You can disable that privilege,
222but the cost is that you will not be able to use the module's on-the-fly
223table creation feature. If that cost is acceptable, hand-create the
224tables as described in step <A HREF="node4.html#part:CrTbl">3</A> and use the following
225GRANT statement instead of the one above:
226
227<P>
228
229<DL COMPACT>
230<DT>
231<DD>mysql&gt;&nbsp;grant&nbsp;insert&nbsp;on&nbsp;apachelogs.*&nbsp;to&nbsp;loguser@my.apachemachine.com
232
233<P>
234identified&nbsp;by&nbsp;'l0gger';
235</DD>
236</DL>
237</LI>
238<LI><A NAME="step:EnaLog"></A>Enable full logging of your MySQL daemon (at least
239temporarily for debugging purposes) if you don't do this already.
240Edit /etc/my.cnf and add the following line to your [mysqld] section:
241
242<P>
243
244<DL COMPACT>
245<DT>
246<DD>log=/var/log/mysql-messages
247</DD>
248</DL>Then restart MySQL.
249
250<P>
251
252<DL COMPACT>
253<DT>
254<DD>#&nbsp;/etc/rc.d/init.d/mysql&nbsp;restart
255</DD>
256</DL>
257</LI>
258</OL>
259
260<P>
261
262<H2><A NAME="SECTION00042000000000000000">
2633.2 A very basic logging setup in Apache</A>
264</H2>
265
266<P>
267
268<OL>
269<LI>Tell the module what database to use and the appropriate authentication
270information.
271
272<P>
273So, edit httpd.conf and insert the following lines somewhere after
274any LoadModule / AddModule statements. <I>Make sure these statements
275are ``global,'' i.e. not inside any VirtualHost stanza</I>. You will
276also note that you are embedding a password in the file. Therefore
277you are advised to ``chmod 660 httpd.conf'' to prevent unauthorized
278regular users from viewing your database user and password.
279
280<P>
281<B>Example</B>: Use the MySQL database called &#34;apachelogs&#34;
282running on &#34;dbmachine.foo.com&#34;. Use username &#34;loguser&#34;
283and password &#34;l0gg3r&#34; to authenticate to the database.
284Permit the module create tables for us.
285
286<P>
287
288<DL COMPACT>
289<DT>
290<DD>LogSQLLoginInfo&nbsp;dbmachine.foo.com&nbsp;loguser&nbsp;l0gg3r&nbsp;
291
292<P>
293LogSQLDatabase&nbsp;apachelogs
294
295<P>
296LogSQLCreateTables&nbsp;on
297</DD>
298</DL>If your database resides on localhost instead of another host, specify
299the MySQL server's socket file as follows:
300
301<P>
302
303<DL COMPACT>
304<DT>
305<DD>LogSQLSocketFile&nbsp;/your/path/to/mysql.sock
306</DD>
307</DL>If your database is listening on a port other than 3306, specify the
308correct TCP port as follows:
309
310<P>
311
312<DL COMPACT>
313<DT>
314<DD>LogSQLTCPPort&nbsp;1234
315</DD>
316</DL>
317</LI>
318<LI>The actual logging is set up on a virtual-host-by-host basis. So,
319skip down to the virtual host you want to set up. Instruct this virtual
320host to log entries to the table ``access_log'' by inserting
321a L<SMALL>OG</SMALL>SQLT<SMALL>RANSFER</SMALL>L<SMALL>OG</SMALL>T<SMALL>ABLE</SMALL> directive. (The L<SMALL>OG</SMALL>SQLT<SMALL>RANSFER</SMALL>L<SMALL>OG</SMALL>T<SMALL>ABLE</SMALL>
322directive is the minimum required to log - other directives that
323you'll learn about later simply tune the module's behavior.)
324
325<P>
326
327<DL COMPACT>
328<DT>
329<DD>&lt;VirtualHost&nbsp;1.2.3.4&gt;
330
331<P>
332&nbsp;[snip]
333
334<P>
335&nbsp;LogSQLTransferLogTable&nbsp;access_log
336
337<P>
338&nbsp;[snip]
339
340<P>
341&lt;/VirtualHost&gt;
342</DD>
343</DL>
344</LI>
345<LI>Restart apache.
346
347<P>
348
349<DL COMPACT>
350<DT>
351<DD>#&nbsp;/etc/rc.d/init.d/httpd&nbsp;stop
352
353<P>
354#&nbsp;/etc/rc.d/init.d/httpd&nbsp;start
355</DD>
356</DL>
357</LI>
358</OL>
359
360<P>
361
362<H2><A NAME="SECTION00043000000000000000">
3633.3 Testing the basic setup</A>
364</H2>
365
366<P>
367
368<OL>
369<LI>Visit your web site in a browser to trigger some hits, then confirm
370that the entries are being successfully logged:
371
372<P>
373
374<DL COMPACT>
375<DT>
376<DD>#&nbsp;mysql&nbsp;-hdbmachine.foo.com&nbsp;-umysqladmin&nbsp;-p&nbsp;-e&nbsp;&#34;select&nbsp;*&nbsp;from&nbsp;access_log&#34;&nbsp;apachelogs&nbsp;
377
378<P>
379Enter&nbsp;password:
380</DD>
381</DL>Several lines of output should follow, corresponding to your hits
382on the site. You now have basic functionality. Don't disable your
383regular Apache logs until you feel comfortable that the database is
384behaving as you'd like and that things are going well. If you do not
385see any entries in the access_log, please consult section <A HREF="node5.html#faq:NothingLogged">4.2.2</A>
386of the FAQ on how to debug and fix the situation.
387
388<P>
389</LI>
390<LI>You can now activate the advanced features of mod_log_sql, which
391are described in the next section.
392</LI>
393</OL>
394
395<P>
396
397<H2><A NAME="SECTION00044000000000000000">
3983.4 How to tune logging with run-time directives</A>
399</H2>
400
401<P>
402
403<H3><A NAME="SECTION00044100000000000000">
4043.4.1 Instructing the module what to log</A>
405</H3>
406
407<P>
408The most basic directive for the module is L<SMALL>OG</SMALL>SQLT<SMALL>RANSFER</SMALL>L<SMALL>OG</SMALL>F<SMALL>ORMAT</SMALL>,
409which tells the module which information to send to the database;
410logging to the database will not take place without it. Place a L<SMALL>OG</SMALL>SQLT<SMALL>RANSFER</SMALL>L<SMALL>OG</SMALL>F<SMALL>ORMAT</SMALL>
411directive in the VirtualHost stanza of each virtual host that you
412want to activate.
413
414<P>
415After L<SMALL>OG</SMALL>SQLT<SMALL>RANSFER</SMALL>L<SMALL>OG</SMALL>F<SMALL>ORMAT</SMALL> you supply a string of characters
416that tell the module what information to log. In the configuration
417directive reference (section <A HREF="node4.html#sub:Frmat">3.6.17</A>) there is a table which
418clearly defines all the possible things to log. Let's say you want
419to log only the ``request time,'' the ``remote host,'' and
420the ``request''; you'd use:
421
422<P>
423
424<DL COMPACT>
425<DT>
426<DD>LogSQLTransferLogFormat&nbsp;hUS
427</DD>
428</DL>But a more appropriate string to use is
429
430<P>
431
432<DL COMPACT>
433<DT>
434<DD>LogSQLTransferLogFormat&nbsp;AbHhmRSsTUuv
435</DD>
436</DL>which logs all the information required to be compatible with the
437Combined Log Format (CLF).
438
439<P>
440If you don't choose to log everything that is available, that's fine.
441Fields in the unused columns in your table will simply contain NULL.
442
443<P>
444Some of the L<SMALL>OG</SMALL>SQLT<SMALL>RANSFER</SMALL>L<SMALL>OG</SMALL>F<SMALL>ORMAT</SMALL> characters require a
445little extra configuration:
446
447<P>
448
449<UL>
450<LI>If you specify 'c' to indicate that you want to log the cookie value,
451you must also tell the module which cookie you mean by using L<SMALL>OG</SMALL>SQLW<SMALL>HICH</SMALL>C<SMALL>OOKIE</SMALL>
452- after all, there could be many cookies associated with a given
453request. Fail to specify L<SMALL>OG</SMALL>SQLW<SMALL>HICH</SMALL>C<SMALL>OOKIE</SMALL>, and no cookie
454information at all will be logged.
455</LI>
456<LI>If you specify 'M' to indicate that you want to log the machine ID,
457you must also tell the module this machine's identity using the L<SMALL>OG</SMALL>SQLM<SMALL>ACHINE</SMALL>ID
458directive. Fail to specify L<SMALL>OG</SMALL>SQLM<SMALL>ACHINE</SMALL>ID, and a simple
459'-' character will be logged in the machine_id column.
460</LI>
461</UL>
462
463<P>
464
465<H3><A NAME="SECTION00044200000000000000"></A><A NAME="sub:Ignore"></A>
466<BR>
4673.4.2 Instructing the module what NOT to log using filtering
468directives
469</H3>
470
471<P>
472One ``accept'' and two ``ignore'' directives allow you to
473fine-tune what the module should not log. These are very handy for
474keeping your database as uncluttered as possible and keeping your
475statistics free of unneeded numbers. Think of each one as a gatekeeper.
476
477<P>
478<I>It is important to remember that each of these three directives
479is purely optional. mod_log_sql's default is to log everything. </I>
480
481<P>
482When a request comes in, the contents of L<SMALL>OG</SMALL>SQLR<SMALL>EQUEST</SMALL>A<SMALL>CCEPT</SMALL>
483are evaluated first. This optional, ``blanket'' directive lets
484you specify that only certain things are to be accepted for logging,
485and everything else discarded. Because it is evaluated before L<SMALL>OG</SMALL>SQLR<SMALL>EQUEST</SMALL>I<SMALL>GNORE</SMALL>
486and L<SMALL>OG</SMALL>SQLR<SMALL>EMHOST</SMALL>I<SMALL>GNORE</SMALL> it can halt logging before those
487two filtering directives ``get their chance.''
488
489<P>
490Once a request makes it past L<SMALL>OG</SMALL>SQLR<SMALL>EQUEST</SMALL>A<SMALL>CCEPT</SMALL>, it still
491can be excluded based on L<SMALL>OG</SMALL>SQLR<SMALL>EMHOST</SMALL>I<SMALL>GNORE</SMALL> and L<SMALL>OG</SMALL>SQLR<SMALL>EQUEST</SMALL>I<SMALL>GNORE</SMALL>.
492A good way to use L<SMALL>OG</SMALL>SQLR<SMALL>EMHOST</SMALL>I<SMALL>GNORE</SMALL> is to prevent the module
493from logging the traffic that your internal hosts generate. L<SMALL>OG</SMALL>SQLR<SMALL>EQUEST</SMALL>I<SMALL>GNORE</SMALL>
494is great for preventing things like requests for ``favicon.ico''
495from cluttering up your database, as well as excluding the various
496requests that worms make, etc.
497
498<P>
499You can specify a series of strings after each directive. Do not use
500any type of globbing or regular-expression syntax - each string is
501considered a match <I>if it is a substring of the larger request
502or remote-host; the comarison is case-sensitive.</I> This means that
503``L<SMALL>OG</SMALL>SQLR<SMALL>EMHOST</SMALL>I<SMALL>GNORE</SMALL> micro'' will ignore requests from
504``microsoft.com,'' ``microworld.net,'' ``mymicroscope.org,''
505etc. ``L<SMALL>OG</SMALL>SQLR<SMALL>EQUEST</SMALL>I<SMALL>GNORE</SMALL> gif'' will instruct the module
506to ignore requests for ``leftbar.gif,'' ``bluedot.gif'' and
507even ``giftwrap.jpg'' - but ``RED.GIF'' and ``Tree.Gif''
508would still get logged because of case sensitivity.
509
510<P>
511A summary of the decision flow:
512
513<P>
514
515<OL>
516<LI>If L<SMALL>OG</SMALL>SQLR<SMALL>EQUEST</SMALL>A<SMALL>CCEPT</SMALL> exists and a request does not match
517anything in that list, it is discarded.
518</LI>
519<LI>If a request matches anything in the L<SMALL>OG</SMALL>SQLR<SMALL>EQUEST</SMALL>I<SMALL>GNORE</SMALL>
520list, it is discarded.
521</LI>
522<LI>If a reqiest matches anything in the L<SMALL>OG</SMALL>SQLR<SMALL>EMHOST</SMALL>I<SMALL>GNORE</SMALL>
523list, it is discarded.
524</LI>
525<LI>Otherwise the request is logged.
526</LI>
527</OL>
528This means that you can have a series of directives similar to the
529following:
530
531<P>
532
533<DL COMPACT>
534<DT>
535<DD>LogSQLRequestAccept&nbsp;*.html&nbsp;*.gif&nbsp;*.jpg
536
537<P>
538LogSQLRequestIgnore&nbsp;statistics.html&nbsp;bluedot.jpg
539</DD>
540</DL>So the first line instructs the module to <B>only</B> log files
541with html, gif and jpg suffixes; requests for ``formail.cgi''
542and ``shopping-cart.pl'' will never be considered for logging.
543(``LeftArrow.JPG'' will also never be considered for logging -
544remember, the comparison is <B>case sensitive</B>.) The second line
545prunes the list further - you never want to log requests for those
546two objects.
547
548<P>
549Tip: if you want to match all the hosts in your domain such as ``host1.corp.foo.com''
550and ``server.dmz.foo.com'', simply specify:
551
552<P>
553
554<DL COMPACT>
555<DT>
556<DD>LogSQLRemhostIgnore&nbsp;foo.com
557</DD>
558</DL>Tip: a great way to catch the vast majority of worm-attack requests
559and prevent them from being logged is to specify:
560
561<P>
562
563<DL COMPACT>
564<DT>
565<DD>LogSQLRequestIgnore&nbsp;root.exe&nbsp;cmd.exe&nbsp;default.ida
566</DD>
567</DL>Tip: to prevent the logging of requests for common graphic types,
568make sure to put a '.' before the suffix to avoid matches that you
569didn't intend:
570
571<P>
572
573<DL COMPACT>
574<DT>
575<DD>LogSQLRequestIgnore&nbsp;.gif&nbsp;.jpg
576</DD>
577</DL>
578<P>
579
580<H2><A NAME="SECTION00045000000000000000">
5813.5 Advanced logging scenarios</A>
582</H2>
583
584<P>
585
586<H3><A NAME="SECTION00045100000000000000">
5873.5.1 Using the module in an ISP environment</A>
588</H3>
589
590<P>
591mod_log_sql has three basic tiers of operation:
592
593<P>
594
595<OL>
596<LI>The administrator creates all necessary tables by hand and configures
597each Apache VirtualHost by hand. (L<SMALL>OG</SMALL>SQLC<SMALL>REATE</SMALL>T<SMALL>ABLES </SMALL>O<SMALL>FF</SMALL>)
598</LI>
599<LI>The module is permitted to create necessary tables on-the-fly, but
600the administrator configures each Apache VirtualHost by hand. (L<SMALL>OG</SMALL>SQLC<SMALL>REATE</SMALL>T<SMALL>ABLES
601</SMALL>O<SMALL>N</SMALL>)
602</LI>
603<LI>The module is permitted to create all necessary tables and to make
604intelligent, on-the-fly configuration of each VirtualHost. (L<SMALL>OG</SMALL>SQLM<SMALL>ASS</SMALL>V<SMALL>IRTUAL</SMALL>H<SMALL>OSTING
605</SMALL>O<SMALL>N</SMALL>)
606</LI>
607</OL>
608Many users are happy to use the module in its most minimal form: they
609hand-create any necessary tables (using ``create_tables.sql''),
610and they configure each VirtualHost by hand to suit their needs. However,
611some administrators need extra features due to a large and growing
612number of VirtualHosts. The L<SMALL>OG</SMALL>SQLM<SMALL>ASS</SMALL>V<SMALL>IRTUAL</SMALL>H<SMALL>OSTING</SMALL> directive
613activates module capabilities that make it far easier to manage an
614ISP environment, or any situation characterized by a large and varying
615number of virtual servers:
616
617<P>
618
619<UL>
620<LI>the on-the-fly table creation feature is activated automatically
621</LI>
622<LI>the transfer log table name is dynamically set from the virtual host's
623name (example: a virtual host ``www.grubbybaby.com'' gets logged
624to table ``access_www_grubbybaby_com'')
625</LI>
626</UL>
627There are numerous benefits. The admin will not need to create new
628tables for every new VirtualHost. (Although the admin will still need
629to drop the tables of virtual hosts that are removed.) The admin will
630not need to set L<SMALL>OG</SMALL>SQLT<SMALL>RANSFER</SMALL>L<SMALL>OG</SMALL>T<SMALL>ABLE</SMALL> for each virtual host
631- it will be configured automatically based on the host's name. Because
632each virtual host will log to its own segregated table, data about
633one virtual server will segregate from others; an admin can grant
634users access to the tables they need, and they will be unable to view
635data about another user's virtual host.
636
637<P>
638In an ISP scenario the admin is likely to have a cluster of many front-end
639webservers logging to a back-end database. mod_log_sql has a feature
640that permits analysis of how well the web servers are loadbalancing:
641the L<SMALL>OG</SMALL>SQLM<SMALL>ACHINE</SMALL>ID directive. The administrator uses this
642directive to assign a unique identifier to each machine in the web
643cluster, e.g. ``L<SMALL>OG</SMALL>SQLM<SMALL>ACHINE</SMALL>ID web01,'' ``L<SMALL>OG</SMALL>SQLM<SMALL>ACHINE</SMALL>ID
644web02,'' etc. Used in conjunction with the 'M' character in L<SMALL>OG</SMALL>SQLT<SMALL>RANSFER</SMALL>L<SMALL>OG</SMALL>F<SMALL>ORMAT</SMALL>,
645each entry in the SQL log will include the machine ID of the machine
646that created the entry. This permits the administrator to count the
647entries made by each particular machine and thereby analyze the front-end
648loadbalancing algorithm.
649
650<P>
651
652<H3><A NAME="SECTION00045200000000000000"></A><A NAME="secMulTable"></A>
653<BR>
6543.5.2 Logging many-to-one data in separate tables
655</H3>
656
657<P>
658A given HTTP request can have a one-to-many relationship with certain
659kinds of data. For example, a single HTTP request can have 4 cookies,
6603 headers and 5 ``mod_gzip'' notes associated with it. mod_log_sql
661is capable of logging these relationships due to the elegance of SQL
662relational data.
663
664<P>
665You already have a single table containing access requests. One of
666the columns in that table is 'id' which is intended to contain the
667unique request ID supplied by the standard Apache module mod_unique_id
668- all you need to do is compile in that module and employ the L<SMALL>OG</SMALL>SQLT<SMALL>RANSFER</SMALL>L<SMALL>OG</SMALL>F<SMALL>ORMAT</SMALL>
669character 'I'. Thereafter, each request gets a unique ID that can
670be thought of as a primary key within the database, useful for joining
671multiple tables. So let's envision several new tables: a notes table,
672a cookies table, and a table for inbound and outbound headers.
673
674<P>
675<BR><P></P>
676<DIV ALIGN="CENTER">
677
678<P>
679
680<P>
681<DIV ALIGN="CENTER">
682<A NAME="958"></A>
683<TABLE CELLPADDING=3 BORDER="1">
684<CAPTION><STRONG>Table 1:</STRONG>
685access_log</CAPTION>
686<TR><TD ALIGN="LEFT">id</TD>
687<TD ALIGN="LEFT">remote_host</TD>
688<TD ALIGN="LEFT">request_uri</TD>
689<TD ALIGN="LEFT">time_stamp</TD>
690<TD ALIGN="LEFT">status</TD>
691<TD ALIGN="LEFT">bytes_sent</TD>
692</TR>
693<TR><TD ALIGN="LEFT">PPIDskBRH30AAGPtAsg</TD>
694<TD ALIGN="LEFT">zerberus.aiacs.net</TD>
695<TD ALIGN="LEFT">/mod_log_sql/index.html</TD>
696<TD ALIGN="LEFT">1022493617</TD>
697<TD ALIGN="LEFT">200</TD>
698<TD ALIGN="LEFT">2215</TD>
699</TR>
700</TABLE>
701</DIV>
702</DIV>
703<BR>
704<BR><P></P>
705<DIV ALIGN="CENTER">
706
707<P>
708
709<P>
710<DIV ALIGN="CENTER">
711<A NAME="959"></A>
712<TABLE CELLPADDING=3 BORDER="1">
713<CAPTION><STRONG>Table 2:</STRONG>
714notes_log</CAPTION>
715<TR><TD ALIGN="LEFT">id</TD>
716<TD ALIGN="LEFT">item</TD>
717<TD ALIGN="LEFT">val</TD>
718</TR>
719<TR><TD ALIGN="LEFT">PPIDskBRH30AAGPtAsg</TD>
720<TD ALIGN="LEFT">mod_gzip_result</TD>
721<TD ALIGN="LEFT">OK</TD>
722</TR>
723<TR><TD ALIGN="LEFT">PPIDskBRH30AAGPtAsg</TD>
724<TD ALIGN="LEFT">mod_gzip_compression_ratio</TD>
725<TD ALIGN="LEFT">69</TD>
726</TR>
727</TABLE>
728</DIV>
729</DIV>
730<BR>
731
732<P>
733<BR><P></P>
734<DIV ALIGN="CENTER">
735
736<P>
737
738<P>
739<DIV ALIGN="CENTER">
740<A NAME="960"></A>
741<TABLE CELLPADDING=3 BORDER="1">
742<CAPTION><STRONG>Table 3:</STRONG>
743headers_log</CAPTION>
744<TR><TD ALIGN="LEFT">id</TD>
745<TD ALIGN="LEFT">item</TD>
746<TD ALIGN="LEFT">val</TD>
747</TR>
748<TR><TD ALIGN="LEFT">PPIDskBRH30AAGPtAsg</TD>
749<TD ALIGN="LEFT">Content-Type</TD>
750<TD ALIGN="LEFT">text/html</TD>
751</TR>
752<TR><TD ALIGN="LEFT">PPIDskBRH30AAGPtAsg</TD>
753<TD ALIGN="LEFT">Accept-Encoding</TD>
754<TD ALIGN="LEFT">gzip, deflate</TD>
755</TR>
756<TR><TD ALIGN="LEFT">PPIDskBRH30AAGPtAsg</TD>
757<TD ALIGN="LEFT">Expires</TD>
758<TD ALIGN="LEFT">Tue, 28 May 2002 10:00:18 GMT</TD>
759</TR>
760<TR><TD ALIGN="LEFT">PPIDskBRH30AAGPtAsg</TD>
761<TD ALIGN="LEFT">Cache-Control</TD>
762<TD ALIGN="LEFT">max-age=86400</TD>
763</TR>
764</TABLE>
765</DIV>
766</DIV>
767<BR>
768
769<P>
770We have a certain request, and its unique ID is ``PPIDskBRH30AAGPtAsg''.
771Within each separate table will be multiple entries with that request
772ID: several cookie entries, several header entries, etc. As you can
773see in tables <A HREF="#tblAcc">1</A>, <A HREF="#tblNotes">2</A> and <A HREF="#tblHdr">3</A>, you
774have a one-to-many relationship for request PPIDskBRH30AAGPtAsg: that
775one access has two associated notes and four associated headers. You
776can extract this data easily using the power of SQL's ``select''
777statement and table joins. To see the notes associated with a particular
778request:
779
780<P>
781
782<DL COMPACT>
783<DT>
784<DD>select&nbsp;a.remote_host,&nbsp;a.request_uri,&nbsp;n.item,&nbsp;n.val&nbsp;from&nbsp;access_log&nbsp;a,&nbsp;notes_log&nbsp;n
785
786<P>
787where&nbsp;a.id=n.id&nbsp;and&nbsp;a.id='PPIDskBRH30AAGPtAsg';
788
789<P>
790</DD>
791</DL>
792<DIV ALIGN="CENTER">
793<TABLE CELLPADDING=3 BORDER="1">
794<TR><TD ALIGN="LEFT">remote_host</TD>
795<TD ALIGN="LEFT">request_uri</TD>
796<TD ALIGN="LEFT">item</TD>
797<TD ALIGN="LEFT">val</TD>
798</TR>
799<TR><TD ALIGN="LEFT">zerberus.aiacs.net</TD>
800<TD ALIGN="LEFT">/mod_log_sql/index.html</TD>
801<TD ALIGN="LEFT">mod_gzip_result</TD>
802<TD ALIGN="LEFT">OK</TD>
803</TR>
804<TR><TD ALIGN="LEFT">zerberus.aiacs.net</TD>
805<TD ALIGN="LEFT">/mod_log_sql/index.html</TD>
806<TD ALIGN="LEFT">mod_gzip_compression_ratio</TD>
807<TD ALIGN="LEFT">69</TD>
808</TR>
809</TABLE>
810</DIV>
811
812<P>
813
814<DL COMPACT>
815<DT>
816<DD><P>
817</DD>
818</DL>Naturally you can craft similar statements for the outboud headers,
819inbound headers and cookies, all of which can live in separate tables.
820Your statements are limited in power only by your skill with SQL.
821
822<P>
823In order to use this capability of mod_log_sql, you must do several
824things:
825
826<P>
827
828<UL>
829<LI>Compile mod_unique_id into Apache (statically or as a DSO). mod_log_sql
830employs the unique request ID that mod_unique_id provides in order
831to key between the separate tables. You can still log the data without
832mod_unqiue_id, but it will be completely uncorrelated and you will
833have no way to discern any meaning.
834</LI>
835<LI>Create the appropriate tables. This will be done for you if you permit
836mod_log_sql to create its own tables using L<SMALL>OG</SMALL>SQLC<SMALL>REATE</SMALL>T<SMALL>ABLES
837</SMALL>O<SMALL>N</SMALL>, or if you use the enclosed ``create_tables.sql'' script.
838</LI>
839<LI>Create a SQL index on the ``id'' column. Without this index, table
840joins will be deathly slow. I recommend you consult the MySQL documentation
841on the proper way to create a column index if you are not familiar
842with this operation.
843</LI>
844<LI>Within each appropriate VirtualHost stanza, use the L<SMALL>OG</SMALL>SQLW<SMALL>HICH</SMALL>*
845and L<SMALL>OG</SMALL>SQL*L<SMALL>OG</SMALL>T<SMALL>ABLE</SMALL> directives to tell the module what
846and where to log the data. In the following example, I have overridden
847the name for the notes table whereas I have left the other table names
848at their defaults. I have then specified the cookies, headers and
849notes that interest me. (And as you can see, these directives do not
850require me to add any characters to L<SMALL>OG</SMALL>SQLT<SMALL>RANSFER</SMALL>L<SMALL>OG</SMALL>T<SMALL>ABLE.)</SMALL>
851</LI>
852</UL>
853
854<DL COMPACT>
855<DT>
856<DD>&lt;VirtualHost&nbsp;216.231.36.128&gt;
857
858<P>
859&nbsp;&nbsp;(snip)
860
861<P>
862&nbsp;&nbsp;LogSQLNotesLogTable&nbsp;notestable
863
864<P>
865&nbsp;&nbsp;LogSQLWhichCookies&nbsp;bluecookie&nbsp;redcookie&nbsp;greencookie&nbsp;
866
867<P>
868&nbsp;&nbsp;LogSQLWhichNotes&nbsp;mod_gzip_result&nbsp;mod_gzip_compression_ratio
869
870<P>
871&nbsp;&nbsp;LogSQLWhichHeadersOut&nbsp;Expires&nbsp;Content-Type&nbsp;Cache-Control&nbsp;
872
873<P>
874&nbsp;&nbsp;LogSQLWhichHeadersIn&nbsp;UserAgent&nbsp;Accept-Encoding&nbsp;Host
875
876<P>
877&nbsp;&nbsp;(snip)
878
879<P>
880&lt;/VirtualHost&gt;
881</DD>
882</DL>
883<P>
884
885<H3><A NAME="SECTION00045300000000000000">
8863.5.3 Using the same database for production and test</A>
887</H3>
888
889<P>
890Although suboptimal, it is not uncommon to use the same backend database
891for the ``production'' webservers as well as the ``test''
892webservers (budgetary constraints, rackspace limits, etc.). Furthermore,
893an administrator in this situation may be unable to use L<SMALL>OG</SMALL>SQLR<SMALL>EMHOST</SMALL>I<SMALL>GNORE</SMALL>
894to exclude requests from the test servers - perhaps the generated
895entries are genuinely useful for analytical or QA purposes, but their
896value after analysis is minimal.
897
898<P>
899It is wasteful and potentially confusing to permit this internal test
900data to clutter the database, and a solution to the problem is the
901proper use of the L<SMALL>OG</SMALL>SQLM<SMALL>ACHINE</SMALL>ID directive. Assume a scenario
902where the production webservers have IDs like ``web01,'' ``web02,''
903and so on - and the test webservers have IDs like ``test01,''
904``test02,'' etc. Because entries in the log database are distinguished
905by their source machine, an administrator may purge unneeded test
906data from the access log as follows:
907
908<P>
909
910<DL COMPACT>
911<DT>
912<DD>delete&nbsp;from&nbsp;access_log&nbsp;where&nbsp;machine_id&nbsp;like&nbsp;'test%';
913</DD>
914</DL>
915<P>
916
917<H3><A NAME="SECTION00045400000000000000"></A><A NAME="sub:DelayedIns"></A>
918<BR>
9193.5.4 Optimizing for a busy database
920</H3>
921
922<P>
923A busy MySQL database will have SELECT statements running concurrently
924with INSERT and UPDATE statements. A long-running SELECT can in certain
925circumstances block INSERTs and therefore block mod_log_sql. A workaround
926is to compile mod_log_sql for ``delayed inserts,'' which are
927described as follows in the MySQL documentation:
928
929<P>
930<BLOCKQUOTE>
931The DELAYED option for the INSERT statement is a MySQL-specific option
932that is very useful if you have clients that can't wait for the INSERT
933to complete. This is a common problem when you use MySQL for logging
934and you also periodically run SELECT and UPDATE statements that take
935a long time to complete. DELAYED was introduced in MySQL Version 3.22.15.
936It is a MySQL extension to ANSI SQL92.
937</BLOCKQUOTE>
938<P>
939<BLOCKQUOTE>INSERT DELAYED only works with ISAM and MyISAM tables. Note that as
940MyISAM tables supports concurrent SELECT and INSERT, if there is no
941free blocks in the middle of the data file, you very seldom need to
942use INSERT DELAYED with MyISAM.
943</BLOCKQUOTE>
944<P>
945<BLOCKQUOTE>When you use INSERT DELAYED, the client will get an OK at once and
946the row will be inserted when the table is not in use by any other
947thread.
948</BLOCKQUOTE>
949<P>
950<BLOCKQUOTE>Another major benefit of using INSERT DELAYED is that inserts from
951many clients are bundled together and written in one block. This is
952much faster than doing many separate inserts.
953
954</BLOCKQUOTE>
955The general disadvantages of delayed inserts are:
956
957<P>
958
959<OL>
960<LI>The queued rows are only stored in memory until they are inserted
961into the table. If mysqld dies unexpectedly, any queued rows that
962weren't written to disk are lost.
963</LI>
964<LI>There is additional overhead for the server to handle a separate thread
965for each table on which you use INSERT DELAYED.
966</LI>
967</OL>
968<B>The MySQL documentation concludes, ``This means that you
969should only use INSERT DELAYED when you are really sure you need it!''
970Furthermore, the current state of error return from a failed INSERT
971DELAYED seems to be in flux, and may behave in unpredictable ways
972between different MySQL versions. See section <A HREF="node5.html#sub:DelayedInsFAQ">4.3.4</A>
973in the FAQ - you have been warned.</B>
974
975<P>
976If you are experiencing issues which could be solved by delayed inserts,
977uncomment the #MYSQLDELAYED line in the Makefile by removing the
978# that is in front of it. Recompile and reinstall your module. All
979regular INSERT statements are now INSERT DELAYED, and you should see
980no more blocking of the module.
981
982<P>
983
984<H2><A NAME="SECTION00046000000000000000"></A><A NAME="sec:ConfRef"></A>
985<BR>
9863.6 Configuration directive reference
987</H2>
988
989<P>
990It is imperative that you understand which directives are used <I>only
991once</I> in the main server config, and which are used inside VirtualHost
992stanzas and therefore multiple times within httpd.conf. The ``context''
993listed with each entry informs you of this.
994
995<P>
996
997<H3><A NAME="SECTION00046100000000000000">
9983.6.1 LogSQLCookieLogTable</A>
999</H3>
1000
1001<P>
1002
1003<DL COMPACT>
1004<DT>
1005<DD>Syntax:&nbsp;LogSQLCookieLogTable&nbsp;table-name&nbsp;
1006
1007<P>
1008Example:&nbsp;LogSQLCookieLogTable&nbsp;cookie_log
1009
1010<P>
1011Default:&nbsp;cookies
1012
1013<P>
1014Context:&nbsp;virtual&nbsp;host
1015</DD>
1016</DL>Defines which table is used for logging of cookies. Working in conjunction
1017with L<SMALL>OG</SMALL>SQLW<SMALL>HICH</SMALL>C<SMALL>OOKIES</SMALL>, you can log many of each request's
1018associated cookies to a separate table. For meaningful data retrieval
1019the cookie table is keyed to the access table by the unique request
1020ID supplied by the standard Apache module mod_unique_id.
1021
1022<P>
1023Note that you must create the table (see create-tables.sql, included
1024in the package), or L<SMALL>OG</SMALL>SQLC<SMALL>REATE</SMALL>T<SMALL>ABLES</SMALL> must be set to ``on''.
1025
1026<P>
1027
1028<H3><A NAME="SECTION00046200000000000000">
10293.6.2 LogSQLCreateTables</A>
1030</H3>
1031
1032<P>
1033
1034<DL COMPACT>
1035<DT>
1036<DD>Syntax:&nbsp;LogSQLCreateTables&nbsp;flag
1037
1038<P>
1039Example:&nbsp;LogSQLCreateTables&nbsp;On&nbsp;
1040
1041<P>
1042Default:&nbsp;Off&nbsp;
1043
1044<P>
1045Context:&nbsp;main&nbsp;server&nbsp;config
1046</DD>
1047</DL>mod_log_sql has the ability to create its tables on-the-fly. The
1048advantage to this is convenience: you don't have to execute any SQL
1049by hand to prepare the table. This is especially helpful for people
1050with lots of virtual hosts (who should also see the L<SMALL>OG</SMALL>SQLM<SMALL>ASS</SMALL>V<SMALL>IRTUAL</SMALL>H<SMALL>OSTING</SMALL>
1051directive).
1052
1053<P>
1054There is a slight disadvantage: if you wish to activate this feature,
1055then the userid specified in L<SMALL>OG</SMALL>SQLL<SMALL>OGIN</SMALL>I<SMALL>NFO</SMALL> must have CREATE
1056privileges on the database. In an absolutely paranoid, locked-down
1057situation you may only want to grant your mod_log_sql user INSERT
1058privileges on the database; in that situation you are unable to take
1059advantage of L<SMALL>OG</SMALL>SQLC<SMALL>REATE</SMALL>T<SMALL>ABLES</SMALL>. But most people - even
1060the very security-conscious - will find that granting CREATE on the
1061logging database is reasonable.
1062
1063<P>
1064This is defined only once in the httpd.conf file.
1065
1066<P>
1067
1068<H3><A NAME="SECTION00046300000000000000">
10693.6.3 LogSQLDatabase </A>
1070</H3>
1071
1072<P>
1073
1074<DL COMPACT>
1075<DT>
1076<DD><B>MANDATORY</B>
1077
1078<P>
1079Syntax:&nbsp;LogSQLDatabase&nbsp;database&nbsp;
1080
1081<P>
1082Example:&nbsp;LogSQLDatabase&nbsp;loggingdb&nbsp;
1083
1084<P>
1085Context:&nbsp;main&nbsp;server&nbsp;config
1086</DD>
1087</DL>Defines the database that is used for logging. ``database'' must
1088be a valid db on the MySQL host defined in L<SMALL>OG</SMALL>SQLL<SMALL>OGIN</SMALL>I<SMALL>NFO</SMALL>.
1089
1090<P>
1091This is defined only once in the httpd.conf file.
1092
1093<P>
1094
1095<H3><A NAME="SECTION00046400000000000000">
10963.6.4 LogSQLForcePreserve</A>
1097</H3>
1098
1099<P>
1100
1101<DL COMPACT>
1102<DT>
1103<DD>Syntax:&nbsp;LogSQLForcePreserve&nbsp;Flag
1104
1105<P>
1106Example:&nbsp;LogSQLPreserveFile&nbsp;on
1107
1108<P>
1109Default:&nbsp;off
1110
1111<P>
1112Context:&nbsp;main&nbsp;server&nbsp;config
1113</DD>
1114</DL>You may need to perform debugging on your database and specifically
1115want mod_log_sql to make no attempts to log to it. This directive
1116instructs the module to send all its log entries directly to the preserve
1117file and to make no database INSERT attempts.
1118
1119<P>
1120This is presumably a directive for temporary use only; it could be
1121dangerous if you set it and forget it, as all your entries will simply
1122pile up in the preserve file.
1123
1124<P>
1125This is defined only once in the httpd.conf file.
1126
1127<P>
1128
1129<H3><A NAME="SECTION00046500000000000000">
11303.6.5 LogSQLHeadersInLogTable</A>
1131</H3>
1132
1133<P>
1134
1135<DL COMPACT>
1136<DT>
1137<DD>Syntax:&nbsp;LogSQLHeadersInLogTable&nbsp;table-name&nbsp;
1138
1139<P>
1140Example:&nbsp;LogSQLHeadersInLogTable&nbsp;headers
1141
1142<P>
1143Default:&nbsp;headers_in
1144
1145<P>
1146Context:&nbsp;virtual&nbsp;host
1147</DD>
1148</DL>Defines which table is used for logging of inbound headers. Working
1149in conjunction with L<SMALL>OG</SMALL>SQLW<SMALL>HICH</SMALL>H<SMALL>EADERS</SMALL>I<SMALL>N</SMALL>, you can log many
1150of each request's associated headers to a separate table. For meaningful
1151data retrieval the headers table is keyed to the access table by the
1152unique request ID supplied by the standard Apache module mod_unique_id.
1153
1154<P>
1155Note that you must create the table (see create-tables.sql, included
1156in the package), or L<SMALL>OG</SMALL>SQLC<SMALL>REATE</SMALL>T<SMALL>ABLES</SMALL> must be set to ``on''.
1157
1158<P>
1159
1160<H3><A NAME="SECTION00046600000000000000">
11613.6.6 LogSQLHeadersOutLogTable</A>
1162</H3>
1163
1164<P>
1165
1166<DL COMPACT>
1167<DT>
1168<DD>Syntax:&nbsp;LogSQLHeadersOutLogTable&nbsp;table-name&nbsp;
1169
1170<P>
1171Example:&nbsp;LogSQLHeadersOutLogTable&nbsp;headers
1172
1173<P>
1174Default:&nbsp;headers_out
1175
1176<P>
1177Context:&nbsp;virtual&nbsp;host
1178</DD>
1179</DL>Defines which table is used for logging of outbound headers. Working
1180in conjunction with L<SMALL>OG</SMALL>SQLW<SMALL>HICH</SMALL>H<SMALL>EADERS</SMALL>O<SMALL>UT</SMALL>, you can log many
1181of each request's associated headers to a separate table. For meaningful
1182data retrieval the headers table is keyed to the access table by the
1183unique request ID supplied by the standard Apache module mod_unique_id.
1184
1185<P>
1186Note that you must create the table (see create-tables.sql, included
1187in the package), or L<SMALL>OG</SMALL>SQLC<SMALL>REATE</SMALL>T<SMALL>ABLES</SMALL> must be set to ``on''.
1188
1189<P>
1190
1191<H3><A NAME="SECTION00046700000000000000">
11923.6.7 LogSQLLoginInfo </A>
1193</H3>
1194
1195<P>
1196
1197<DL COMPACT>
1198<DT>
1199<DD><B>MANDATORY</B>&nbsp;
1200
1201<P>
1202Syntax:&nbsp;LogSQLLoginInfo&nbsp;host&nbsp;user&nbsp;password
1203
1204<P>
1205Example:&nbsp;LogSQLLoginInfo&nbsp;foobar.baz.com&nbsp;logwriter&nbsp;passw0rd&nbsp;
1206
1207<P>
1208Context:&nbsp;main&nbsp;server&nbsp;config
1209</DD>
1210</DL>Defines the general parameters of the MySQL host to which you will
1211be logging. ``host'' is the hostname or IP address of the MySQL
1212machine, and is simply ``localhost'' if the database lives on
1213the same machine as Apache. ``user'' is the MySQL userid (not
1214a Unix userid!) with INSERT privileges on the table defined in L<SMALL>OG</SMALL>SQLT<SMALL>RANSFER</SMALL>L<SMALL>OG</SMALL>T<SMALL>ABLE</SMALL>.
1215``password'' is that user's password.
1216
1217<P>
1218This is defined only once in the httpd.conf file.
1219
1220<P>
1221
1222<H3><A NAME="SECTION00046800000000000000">
12233.6.8 LogSQLMachineID</A>
1224</H3>
1225
1226<P>
1227
1228<DL COMPACT>
1229<DT>
1230<DD>Syntax:&nbsp;LogSQLMachineID&nbsp;somename
1231
1232<P>
1233Example:&nbsp;LogSQLMachineID&nbsp;web01
1234
1235<P>
1236Context:&nbsp;main&nbsp;server&nbsp;config
1237</DD>
1238</DL>If you have a farm of webservers then you may wish to know which particular
1239machine made each entry; this is useful for analyzing your loadbalancing
1240methodology. L<SMALL>OG</SMALL>SQLM<SMALL>ACHINE</SMALL>ID permits you to distinguish each
1241machine's entries if you assign each machine its own L<SMALL>OG</SMALL>SQLM<SMALL>ACHINE</SMALL>ID:
1242for example, the first webserver gets ``L<SMALL>OG</SMALL>SQLM<SMALL>ACHINE</SMALL>ID
1243web01,'' the second gets ``L<SMALL>OG</SMALL>SQLM<SMALL>ACHINE</SMALL>ID web02,''
1244etc.
1245
1246<P>
1247This is defined only once in the httpd.conf file.
1248
1249<P>
1250
1251<H3><A NAME="SECTION00046900000000000000">
12523.6.9 LogSQLMassVirtualHosting</A>
1253</H3>
1254
1255<P>
1256
1257<DL COMPACT>
1258<DT>
1259<DD>Syntax:&nbsp;LogSQLMassVirtualHosting&nbsp;flag&nbsp;
1260
1261<P>
1262Example:&nbsp;LogSQLMassVirtualHosting&nbsp;On&nbsp;
1263
1264<P>
1265Default:&nbsp;Off&nbsp;
1266
1267<P>
1268Context:&nbsp;main&nbsp;server&nbsp;config
1269</DD>
1270</DL>If you administer a site hosting many, many virtual hosts then this
1271option will appeal to you. If you turn on L<SMALL>OG</SMALL>SQLM<SMALL>ASS</SMALL>V<SMALL>IRTUAL</SMALL>H<SMALL>OSTING</SMALL>
1272then several things happen:
1273
1274<P>
1275
1276<UL>
1277<LI>the on-the-fly table creation feature is activated automatically
1278</LI>
1279<LI>the transfer log table name is dynamically set from the virtual host's
1280name after stripping out SQL-unfriendly characters (example: a virtual
1281host www.grubbybaby.com gets logged to table access_www_grubbybaby_com)
1282</LI>
1283<LI>which, in turn, means that each virtual host logs to its own segregated
1284table. Because there is no data shared between virtual servers you
1285can grant your users access to the tables they need; they will be
1286unable to view others' data.
1287</LI>
1288</UL>
1289This is a huge boost in convenience for sites with many virtual servers.
1290Activating L<SMALL>OG</SMALL>SQLM<SMALL>ASS</SMALL>V<SMALL>IRTUAL</SMALL>H<SMALL>OSTING</SMALL> obviates the need to
1291create every virtual server's table and provides more granular security
1292possibilities.
1293
1294<P>
1295You are advised to investigate the use of Apache's U<SMALL>SE</SMALL>C<SMALL>ANONICAL</SMALL>N<SMALL>AME
1296</SMALL>O<SMALL>N</SMALL> directive with this directive in order to ensure that each virtual
1297host maps to one table namespace.
1298
1299<P>
1300This is defined only once in the httpd.conf file.
1301
1302<P>
1303
1304<H3><A NAME="SECTION000461000000000000000">
13053.6.10 LogSQLNotesLogTable</A>
1306</H3>
1307
1308<P>
1309
1310<DL COMPACT>
1311<DT>
1312<DD>Syntax:&nbsp;LogSQLNotesLogTable&nbsp;table-name&nbsp;
1313
1314<P>
1315Example:&nbsp;LogSQLNotesLogTable&nbsp;notes_log
1316
1317<P>
1318Default:&nbsp;notes
1319
1320<P>
1321Context:&nbsp;virtual&nbsp;host&nbsp;
1322</DD>
1323</DL>Defines which table is used for logging of notes. Working in conjunction
1324with L<SMALL>OG</SMALL>SQLW<SMALL>HICH</SMALL>N<SMALL>OTES</SMALL>, you can log many of each request's
1325associated notes to a separate table. For meaningful data retrieval
1326the notes table is keyed to the access table by the unique request
1327ID supplied by the standard Apache module mod_unique_id.
1328
1329<P>
1330Note that you must create the table (see create-tables.sql, included
1331in the package), or L<SMALL>OG</SMALL>SQLC<SMALL>REATE</SMALL>T<SMALL>ABLES</SMALL> must be set to ``on''.
1332
1333<P>
1334
1335<H3><A NAME="SECTION000461100000000000000">
13363.6.11 LogSQLPreserveFile</A>
1337</H3>
1338
1339<P>
1340
1341<DL COMPACT>
1342<DT>
1343<DD>Syntax:&nbsp;LogSQLPreserveFile&nbsp;filename&nbsp;
1344
1345<P>
1346Example:&nbsp;LogSQLPreserveFile&nbsp;offline-preserve&nbsp;
1347
1348<P>
1349Default:&nbsp;/tmp/sql-preserve
1350
1351<P>
1352Context:&nbsp;virtual&nbsp;host
1353</DD>
1354</DL>mod_log_sql writes queries to this local preserve file in the event
1355that it cannot reach the database, and thus ensures that your high-availability
1356web frontend does not lose logs during a temporary database outage.
1357This could happen for a number of reasons: the database goes offline,
1358the network breaks, etc. You will not lose entries since the module
1359has this backup. The file consists of a series of SQL statements that
1360can be imported into your database at your convenience; furthermore,
1361because the SQL queries contain the access timestamps you do not need
1362to worry about out-of-order data after the import, which is done in
1363a simple manner:
1364
1365<P>
1366
1367<DL COMPACT>
1368<DT>
1369<DD>#&nbsp;mysql&nbsp;-uadminuser&nbsp;-p&nbsp;mydbname&nbsp;&lt;&nbsp;/tmp/sql-preserve
1370</DD>
1371</DL>If you do not define L<SMALL>OG</SMALL>SQLP<SMALL>RESERVE</SMALL>F<SMALL>ILE</SMALL> then all virtual
1372servers will log to the same default preserve file (/tmp/sql-preserve).
1373You can redefine this on a virtual-host basis in order to segregate
1374your preserve files if you desire. Note that segregation is not usually
1375necessary, as the SQL statements that are written to the preserve
1376file already distinguish between different virtual hosts if you include
1377the 'v' character in your L<SMALL>OG</SMALL>SQLT<SMALL>RANSFER</SMALL>L<SMALL>OG</SMALL>F<SMALL>ORMAT</SMALL> directive.
1378It is only necessary to segregate preserve-files by virualhost if
1379you also segregate access logs by virtualhost.
1380
1381<P>
1382The module will log to Apache's E<SMALL>RROR</SMALL>L<SMALL>OG</SMALL> when it notices
1383a database outage, and upon database return. You will therefore know
1384when the preserve file is being used, although it is your responsibility
1385to import the file.
1386
1387<P>
1388The file does not need to be created in advance. It is safe to remove
1389or rename the file without interrupting Apache, as the module closes
1390the filehandle immediately after completing the write. The file is
1391created with the user &amp; group ID of the running Apache process (e.g.
1392'nobody' on many Linux distributions).
1393
1394<P>
1395
1396<H3><A NAME="SECTION000461200000000000000">
13973.6.12 LogSQLRemhostIgnore</A>
1398</H3>
1399
1400<P>
1401
1402<DL COMPACT>
1403<DT>
1404<DD>Syntax:&nbsp;LogSQLRemhostIgnore&nbsp;host1&nbsp;host2&nbsp;host3&nbsp;...&nbsp;hostN&nbsp;
1405
1406<P>
1407Example:&nbsp;LogSQLRemhostIgnore&nbsp;localnet.com&nbsp;
1408
1409<P>
1410Context:&nbsp;virtual&nbsp;host
1411</DD>
1412</DL>Lists a series of strings that, if present in the REMOTE_HOST, will
1413cause that request to <B>not</B> be logged. This directive is useful
1414for cutting down on log clutter when you are certain that you want
1415to ignore requests from certain hosts, such as your own internal network
1416machines. See section <A HREF="node4.html#sub:Ignore">3.4.2</A> for some tips for using this
1417directive.
1418
1419<P>
1420Each string is separated by a space, and no regular expressions or
1421globbing are allowed. Each string is evaluated as a substring of the
1422REMOTE_HOST using strstr(). The comparison is case sensitive.
1423
1424<P>
1425
1426<H3><A NAME="SECTION000461300000000000000">
14273.6.13 LogSQLRequestAccept</A>
1428</H3>
1429
1430<P>
1431
1432<DL COMPACT>
1433<DT>
1434<DD>Syntax:&nbsp;LogSQLRequestAccept&nbsp;req1&nbsp;req2&nbsp;req3&nbsp;...&nbsp;reqN&nbsp;
1435
1436<P>
1437Example:&nbsp;LogSQLRequestAccept&nbsp;.html&nbsp;.php&nbsp;.jpg
1438
1439<P>
1440Default:&nbsp;if&nbsp;not&nbsp;specified,&nbsp;all&nbsp;requests&nbsp;are&nbsp;``accepted''
1441
1442<P>
1443Context:&nbsp;virtual&nbsp;host
1444</DD>
1445</DL>Lists a series of strings that, if present in the URI, will permit
1446that request to be considered for logging (depending on additional
1447filtering by the ``ignore'' directives). Any request that fails
1448to match one of the L<SMALL>OG</SMALL>SQLR<SMALL>EQUEST</SMALL>A<SMALL>CCEPT</SMALL> entries will be discarded.
1449
1450<P>
1451This directive is useful for cutting down on log clutter when you
1452are certain that you only want to log certain kinds of requests, and
1453just blanket-ignore everything else. See section <A HREF="node4.html#sub:Ignore">3.4.2</A>
1454for some tips for using this directive.
1455
1456<P>
1457Each string is separated by a space, and no regular expressions or
1458globbing are allowed. Each string is evaluated as a substring of the
1459URI using strstr(). The comparison is case sensitive.
1460
1461<P>
1462This directive is completely optional. It is more general than L<SMALL>OG</SMALL>SQLR<SMALL>EQUEST</SMALL>I<SMALL>GNORE</SMALL>
1463and is evaluated before L<SMALL>OG</SMALL>SQLR<SMALL>EQUEST</SMALL>I<SMALL>GNORE</SMALL>. If
1464this directive is not used, <B>all</B> requests are accepted and
1465passed on to the other filtering directives. Therefore, only use this
1466directive if you have a specific reason to do so.
1467
1468<P>
1469
1470<H3><A NAME="SECTION000461400000000000000">
14713.6.14 LogSQLRequestIgnore</A>
1472</H3>
1473
1474<P>
1475
1476<DL COMPACT>
1477<DT>
1478<DD>Syntax:&nbsp;LogSQLRequestIgnore&nbsp;req1&nbsp;req2&nbsp;req3&nbsp;...&nbsp;reqN&nbsp;
1479
1480<P>
1481Example:&nbsp;LogSQLRequestIgnore&nbsp;root.exe&nbsp;cmd.exe&nbsp;default.ida&nbsp;favicon.ico&nbsp;
1482
1483<P>
1484Context:&nbsp;virtual&nbsp;host
1485</DD>
1486</DL>Lists a series of strings that, if present in the URI, will cause
1487that request to <B>NOT</B> be logged. This directive is
1488useful for cutting down on log clutter when you are certain that you
1489want to ignore requests for certain objects. See section <A HREF="node4.html#sub:Ignore">3.4.2</A>
1490for some tips for using this directive.
1491
1492<P>
1493Each string is separated by a space, and no regular expressions or
1494globbing are allowed. Each string is evaluated as a substring of the
1495URI using strstr(). The comparison is case sensitive.
1496
1497<P>
1498
1499<H3><A NAME="SECTION000461500000000000000">
15003.6.15 LogSQLSocketFile </A>
1501</H3>
1502
1503<P>
1504
1505<DL COMPACT>
1506<DT>
1507<DD>Syntax:&nbsp;LogSQLSocketFile&nbsp;filename&nbsp;
1508
1509<P>
1510Example:&nbsp;LogSQLSocketFile&nbsp;/tmp/mysql.sock&nbsp;
1511
1512<P>
1513Default:&nbsp;/var/lib/mysql/mysql.sock&nbsp;
1514
1515<P>
1516Context:&nbsp;main&nbsp;server&nbsp;config
1517</DD>
1518</DL>At Apache runtime you can specify the MySQL socket file to use. Set
1519this once in your main server config to override the default value.
1520This value is irrelevant if your database resides on a separate machine.
1521
1522<P>
1523mod_log_sql will automatically employ the socket for db communications
1524if the database resides on the local host. If the db resides on a
1525separate host the module will automatically use TCP/IP. This is a
1526function of the MySQL API and is not user-configurable.
1527
1528<P>
1529This is defined only once in the httpd.conf file.
1530
1531<P>
1532
1533<H3><A NAME="SECTION000461600000000000000">
15343.6.16 LogSQLTCPPort</A>
1535</H3>
1536
1537<P>
1538
1539<DL COMPACT>
1540<DT>
1541<DD>Syntax:&nbsp;LogSQLTCPPort&nbsp;portnumber
1542
1543<P>
1544Example:&nbsp;LogSQLTCPPort&nbsp;3309
1545
1546<P>
1547Default:&nbsp;3306
1548
1549<P>
1550Context:&nbsp;main&nbsp;server&nbsp;config
1551</DD>
1552</DL>Your database may listen on a different port than the default. If
1553so, use this directive to instruct the module which port to use. This
1554directive only applies if the database is on a different machine connected
1555via TCP/IP.
1556
1557<P>
1558This is defined only once in the httpd.conf file.
1559
1560<P>
1561
1562<H3><A NAME="SECTION000461700000000000000"></A><A NAME="sub:Frmat"></A>
1563<BR>
15643.6.17 LogSQLTransferLogFormat
1565</H3>
1566
1567<P>
1568
1569<DL COMPACT>
1570<DT>
1571<DD>Syntax:&nbsp;LogSQLTransferLogFormat&nbsp;format-string&nbsp;
1572
1573<P>
1574Example:&nbsp;LogSQLTransferLogFormat&nbsp;huSUTv&nbsp;
1575
1576<P>
1577Default:&nbsp;AbHhmRSsTUuv&nbsp;
1578
1579<P>
1580Context:&nbsp;virtual&nbsp;host
1581</DD>
1582</DL>Each character in the format-string defines an attribute of the request
1583that you wish to log. The default logs the information required to
1584create Combined Log Format logs, plus several extras. Here is the
1585full list of allowable keys, which sometimes resemble their Apache
1586counterparts, but do not always:
1587
1588<P>
1589<BLOCKQUOTE>
1590<TABLE CELLPADDING=3 BORDER="1">
1591<TR><TD ALIGN="CENTER">&nbsp;</TD>
1592<TH ALIGN="LEFT"><B><FONT SIZE="-1">What is this?</FONT></B></TH>
1593<TH ALIGN="LEFT"><B><FONT SIZE="-1">Data field</FONT></B></TH>
1594<TH ALIGN="LEFT"><B><FONT SIZE="-1">Column type</FONT></B></TH>
1595<TH ALIGN="LEFT"><B><FONT SIZE="-1">Example</FONT></B></TH>
1596</TR>
1597<TR><TD ALIGN="CENTER"><FONT SIZE="-1">A</FONT></TD>
1598<TD ALIGN="LEFT"><FONT SIZE="-1">User agent</FONT></TD>
1599<TD ALIGN="LEFT"><FONT SIZE="-1">agent</FONT></TD>
1600<TD ALIGN="LEFT"><FONT SIZE="-1">varchar(255)</FONT></TD>
1601<TD ALIGN="LEFT"><FONT SIZE="-1">Mozilla/4.0 (compat; MSIE 6.0; Windows)</FONT></TD>
1602</TR>
1603<TR><TD ALIGN="CENTER">a</TD>
1604<TD ALIGN="LEFT">CGI request arguments</TD>
1605<TD ALIGN="LEFT">request_args</TD>
1606<TD ALIGN="LEFT">varchar(255)</TD>
1607<TD ALIGN="LEFT">user=Smith&amp;cart=1231&amp;item=532</TD>
1608</TR>
1609<TR><TD ALIGN="CENTER"><FONT SIZE="-1">b</FONT></TD>
1610<TD ALIGN="LEFT"><FONT SIZE="-1">Bytes transfered</FONT></TD>
1611<TD ALIGN="LEFT"><FONT SIZE="-1">bytes_sent</FONT></TD>
1612<TD ALIGN="LEFT"><FONT SIZE="-1">int unsigned</FONT></TD>
1613<TD ALIGN="LEFT"><FONT SIZE="-1">32561</FONT></TD>
1614</TR>
1615<TR><TD ALIGN="CENTER"><FONT SIZE="-1">c</FONT></TD>
1616<TD ALIGN="LEFT"><FONT SIZE="-1">Text of cookie <IMG
1617 WIDTH="13" HEIGHT="21" ALIGN="BOTTOM" BORDER="0"
1618 SRC="img1.png"
1619 ALT="$^{\textrm{1}}$"></FONT></TD>
1620<TD ALIGN="LEFT"><FONT SIZE="-1">cookie</FONT></TD>
1621<TD ALIGN="LEFT"><FONT SIZE="-1">varchar(255)</FONT></TD>
1622<TD ALIGN="LEFT"><FONT SIZE="-1">Apache=sdyn.fooonline.net.1300102700823</FONT></TD>
1623</TR>
1624<TR><TD ALIGN="CENTER"><FONT SIZE="-1">H</FONT></TD>
1625<TD ALIGN="LEFT"><FONT SIZE="-1">HTTP request protocol</FONT></TD>
1626<TD ALIGN="LEFT"><FONT SIZE="-1">request_protocol</FONT></TD>
1627<TD ALIGN="LEFT"><FONT SIZE="-1">varchar(10)</FONT></TD>
1628<TD ALIGN="LEFT"><FONT SIZE="-1">HTTP/1.1</FONT></TD>
1629</TR>
1630<TR><TD ALIGN="CENTER"><FONT SIZE="-1">h</FONT></TD>
1631<TD ALIGN="LEFT"><FONT SIZE="-1">Name of remote host</FONT></TD>
1632<TD ALIGN="LEFT"><FONT SIZE="-1">remote_host</FONT></TD>
1633<TD ALIGN="LEFT"><FONT SIZE="-1">varchar(50)</FONT></TD>
1634<TD ALIGN="LEFT"><FONT SIZE="-1">blah.foobar.com</FONT></TD>
1635</TR>
1636<TR><TD ALIGN="CENTER"><FONT SIZE="-1">I</FONT></TD>
1637<TD ALIGN="LEFT"><FONT SIZE="-1">Request ID (from mod_unique_id)</FONT></TD>
1638<TD ALIGN="LEFT"><FONT SIZE="-1">id</FONT></TD>
1639<TD ALIGN="LEFT"><FONT SIZE="-1">char(19)</FONT></TD>
1640<TD ALIGN="LEFT"><FONT SIZE="-1">POlFcUBRH30AAALdBG8</FONT></TD>
1641</TR>
1642<TR><TD ALIGN="CENTER"><FONT SIZE="-1">l</FONT></TD>
1643<TD ALIGN="LEFT"><FONT SIZE="-1">Ident user info</FONT></TD>
1644<TD ALIGN="LEFT"><FONT SIZE="-1">remote_logname</FONT></TD>
1645<TD ALIGN="LEFT"><FONT SIZE="-1">varchar(50)</FONT></TD>
1646<TD ALIGN="LEFT"><FONT SIZE="-1">bobby</FONT></TD>
1647</TR>
1648<TR><TD ALIGN="CENTER"><FONT SIZE="-1">M</FONT></TD>
1649<TD ALIGN="LEFT"><FONT SIZE="-1">Machine ID <IMG
1650 WIDTH="13" HEIGHT="21" ALIGN="BOTTOM" BORDER="0"
1651 SRC="img2.png"
1652 ALT="$^{\textrm{2}}$"></FONT></TD>
1653<TD ALIGN="LEFT"><FONT SIZE="-1">machine_id</FONT></TD>
1654<TD ALIGN="LEFT"><FONT SIZE="-1">varchar(25)</FONT></TD>
1655<TD ALIGN="LEFT"><FONT SIZE="-1">web01</FONT></TD>
1656</TR>
1657<TR><TD ALIGN="CENTER"><FONT SIZE="-1">m</FONT></TD>
1658<TD ALIGN="LEFT"><FONT SIZE="-1">HTTP request method</FONT></TD>
1659<TD ALIGN="LEFT"><FONT SIZE="-1">request_method</FONT></TD>
1660<TD ALIGN="LEFT"><FONT SIZE="-1">varchar(6)</FONT></TD>
1661<TD ALIGN="LEFT"><FONT SIZE="-1">GET</FONT></TD>
1662</TR>
1663<TR><TD ALIGN="CENTER"><FONT SIZE="-1">P</FONT></TD>
1664<TD ALIGN="LEFT"><FONT SIZE="-1">httpd child PID</FONT></TD>
1665<TD ALIGN="LEFT"><FONT SIZE="-1">child_pid</FONT></TD>
1666<TD ALIGN="LEFT"><FONT SIZE="-1">smallint unsigned</FONT></TD>
1667<TD ALIGN="LEFT"><FONT SIZE="-1">3215</FONT></TD>
1668</TR>
1669<TR><TD ALIGN="CENTER"><FONT SIZE="-1">p</FONT></TD>
1670<TD ALIGN="LEFT"><FONT SIZE="-1">httpd port</FONT></TD>
1671<TD ALIGN="LEFT"><FONT SIZE="-1">server_port</FONT></TD>
1672<TD ALIGN="LEFT"><FONT SIZE="-1">smallint unsigned</FONT></TD>
1673<TD ALIGN="LEFT"><FONT SIZE="-1">80</FONT></TD>
1674</TR>
1675<TR><TD ALIGN="CENTER"><FONT SIZE="-1">R</FONT></TD>
1676<TD ALIGN="LEFT"><FONT SIZE="-1">Referer</FONT></TD>
1677<TD ALIGN="LEFT"><FONT SIZE="-1">referer</FONT></TD>
1678<TD ALIGN="LEFT"><FONT SIZE="-1">varchar(255)</FONT></TD>
1679<TD ALIGN="LEFT"><FONT SIZE="-1">http://www.biglinks4u.com/linkpage.html</FONT></TD>
1680</TR>
1681<TR><TD ALIGN="CENTER"><FONT SIZE="-1">r</FONT></TD>
1682<TD ALIGN="LEFT"><FONT SIZE="-1">Request in full form</FONT></TD>
1683<TD ALIGN="LEFT"><FONT SIZE="-1">request_line</FONT></TD>
1684<TD ALIGN="LEFT"><FONT SIZE="-1">varchar(255)</FONT></TD>
1685<TD ALIGN="LEFT"><FONT SIZE="-1">GET /books-cycroad.html HTTP/1.1</FONT></TD>
1686</TR>
1687<TR><TD ALIGN="CENTER"><FONT SIZE="-1">S</FONT></TD>
1688<TD ALIGN="LEFT"><FONT SIZE="-1">Time of request in UNIX format</FONT></TD>
1689<TD ALIGN="LEFT"><FONT SIZE="-1">time_stamp</FONT></TD>
1690<TD ALIGN="LEFT"><FONT SIZE="-1">int unsigned</FONT></TD>
1691<TD ALIGN="LEFT"><FONT SIZE="-1">1005598029</FONT></TD>
1692</TR>
1693<TR><TD ALIGN="CENTER"><FONT SIZE="-1">s</FONT></TD>
1694<TD ALIGN="LEFT"><FONT SIZE="-1">HTTP status of request</FONT></TD>
1695<TD ALIGN="LEFT"><FONT SIZE="-1">status</FONT></TD>
1696<TD ALIGN="LEFT"><FONT SIZE="-1">smallint unsigned</FONT></TD>
1697<TD ALIGN="LEFT"><FONT SIZE="-1">404</FONT></TD>
1698</TR>
1699<TR><TD ALIGN="CENTER"><FONT SIZE="-1">T</FONT></TD>
1700<TD ALIGN="LEFT"><FONT SIZE="-1">Seconds to service request</FONT></TD>
1701<TD ALIGN="LEFT"><FONT SIZE="-1">request_duration</FONT></TD>
1702<TD ALIGN="LEFT"><FONT SIZE="-1">smallint unsigned</FONT></TD>
1703<TD ALIGN="LEFT"><FONT SIZE="-1">2</FONT></TD>
1704</TR>
1705<TR><TD ALIGN="CENTER"><FONT SIZE="-1">t</FONT></TD>
1706<TD ALIGN="LEFT"><FONT SIZE="-1">Time of request in human format</FONT></TD>
1707<TD ALIGN="LEFT"><FONT SIZE="-1">request_time</FONT></TD>
1708<TD ALIGN="LEFT"><FONT SIZE="-1">char(28)</FONT></TD>
1709<TD ALIGN="LEFT"><FONT SIZE="-1">[02/Dec/2001:15:01:26 -0800]</FONT></TD>
1710</TR>
1711<TR><TD ALIGN="CENTER"><FONT SIZE="-1">U</FONT></TD>
1712<TD ALIGN="LEFT"><FONT SIZE="-1">Request in simple form</FONT></TD>
1713<TD ALIGN="LEFT"><FONT SIZE="-1">request_uri</FONT></TD>
1714<TD ALIGN="LEFT"><FONT SIZE="-1">varchar(255)</FONT></TD>
1715<TD ALIGN="LEFT"><FONT SIZE="-1">/books-cycroad.html</FONT></TD>
1716</TR>
1717<TR><TD ALIGN="CENTER"><FONT SIZE="-1">u</FONT></TD>
1718<TD ALIGN="LEFT"><FONT SIZE="-1">User info from HTTP auth</FONT></TD>
1719<TD ALIGN="LEFT"><FONT SIZE="-1">remote_user</FONT></TD>
1720<TD ALIGN="LEFT"><FONT SIZE="-1">varchar(50)</FONT></TD>
1721<TD ALIGN="LEFT"><FONT SIZE="-1">bobby</FONT></TD>
1722</TR>
1723<TR><TD ALIGN="CENTER"><FONT SIZE="-1">v</FONT></TD>
1724<TD ALIGN="LEFT"><FONT SIZE="-1">Virtual host servicing the request</FONT></TD>
1725<TD ALIGN="LEFT"><FONT SIZE="-1">virtual_host</FONT></TD>
1726<TD ALIGN="LEFT"><FONT SIZE="-1">varchar(50)</FONT></TD>
1727<TD ALIGN="LEFT"><FONT SIZE="-1">www.foobar.com</FONT></TD>
1728</TR>
1729</TABLE></BLOCKQUOTE>
1730<P>
1731<BLOCKQUOTE>
1732</BLOCKQUOTE>
1733<P>
1734<BLOCKQUOTE><IMG
1735 WIDTH="13" HEIGHT="21" ALIGN="BOTTOM" BORDER="0"
1736 SRC="img1.png"
1737 ALT="$^{\textrm{1}}$"> You must also specify L<SMALL>OG</SMALL>SQLW<SMALL>HICH</SMALL>C<SMALL>OOKIE</SMALL>
1738for this to take effect.
1739</BLOCKQUOTE>
1740<P>
1741<BLOCKQUOTE><IMG
1742 WIDTH="13" HEIGHT="21" ALIGN="BOTTOM" BORDER="0"
1743 SRC="img2.png"
1744 ALT="$^{\textrm{2}}$"> You must also specify L<SMALL>OG</SMALL>SQLM<SMALL>ACHINE</SMALL>ID for
1745this to take effect.
1746
1747</BLOCKQUOTE>
1748If you have compiled mod_log_sql with SSL logging capability, you
1749also can use these:
1750
1751<P>
1752<BLOCKQUOTE>
1753<TABLE CELLPADDING=3 BORDER="1">
1754<TR><TD ALIGN="CENTER">&nbsp;</TD>
1755<TH ALIGN="LEFT"><B>What is this?</B></TH>
1756<TH ALIGN="LEFT"><B>Data field</B></TH>
1757<TH ALIGN="LEFT"><B>Column Type</B></TH>
1758<TH ALIGN="LEFT"><B>Example</B></TH>
1759</TR>
1760<TR><TD ALIGN="CENTER">z</TD>
1761<TD ALIGN="LEFT">SSL cipher used</TD>
1762<TD ALIGN="LEFT">ssl_cipher</TD>
1763<TD ALIGN="LEFT">varchar(25)</TD>
1764<TD ALIGN="LEFT">RC4-MD5</TD>
1765</TR>
1766<TR><TD ALIGN="CENTER">q</TD>
1767<TD ALIGN="LEFT">Keysize of the SSL connection</TD>
1768<TD ALIGN="LEFT">ssl_keysize</TD>
1769<TD ALIGN="LEFT">smallint unsigned</TD>
1770<TD ALIGN="LEFT">56</TD>
1771</TR>
1772<TR><TD ALIGN="CENTER">Q</TD>
1773<TD ALIGN="LEFT">Maximum keysize supported</TD>
1774<TD ALIGN="LEFT">ssl_maxkeysize</TD>
1775<TD ALIGN="LEFT">smallint unsigned</TD>
1776<TD ALIGN="LEFT">128</TD>
1777</TR>
1778</TABLE>
1779</BLOCKQUOTE>
1780
1781<P>
1782
1783<H3><A NAME="SECTION000461800000000000000">
17843.6.18 LogSQLTransferLogTable</A>
1785</H3>
1786
1787<P>
1788
1789<DL COMPACT>
1790<DT>
1791<DD><B>MANDATORY&nbsp;(unless</B>&nbsp;<B>L<SMALL>OG</SMALL>SQLM<SMALL>ASS</SMALL>V<SMALL>IRTUAL</SMALL>H<SMALL>OSTING</SMALL></B>&nbsp;<B>is&nbsp;``on'')</B>
1792
1793<P>
1794Syntax:&nbsp;LogSQLTransferLogTable&nbsp;table-name&nbsp;
1795
1796<P>
1797Example:&nbsp;LogSQLTransferLogTable&nbsp;access_log_table&nbsp;
1798
1799<P>
1800Context:&nbsp;virtual&nbsp;host
1801</DD>
1802</DL>Defines which table is used for logging of Apache's transfers; this
1803is analogous to Apache's TransferLog directive. table-name must be
1804a valid table within the database defined in L<SMALL>OG</SMALL>SQLD<SMALL>ATABASE</SMALL>.
1805
1806<P>
1807This directive is not necessary if you declare L<SMALL>OG</SMALL>SQLM<SMALL>ASS</SMALL>V<SMALL>IRTUAL</SMALL>H<SMALL>OSTING
1808</SMALL>O<SMALL>N</SMALL>, since that directive activates dynamically-named tables. If you
1809attempt to use L<SMALL>OG</SMALL>SQLT<SMALL>RANSFER</SMALL>L<SMALL>OG</SMALL>T<SMALL>ABLE</SMALL> at the same time a
1810warning will be logged and it will be ignored, since L<SMALL>OG</SMALL>SQLM<SMALL>ASS</SMALL>V<SMALL>IRTUAL</SMALL>H<SMALL>OSTING</SMALL>
1811takes priority.
1812
1813<P>
1814
1815<H3><A NAME="SECTION000461900000000000000">
18163.6.19 LogSQLWhichCookie</A>
1817</H3>
1818
1819<P>
1820
1821<DL COMPACT>
1822<DT>
1823<DD>Syntax:&nbsp;LogSQLWhichCookie&nbsp;cookiename&nbsp;
1824
1825<P>
1826Example:&nbsp;LogSQLWhichCookie&nbsp;Clicks
1827
1828<P>
1829Default:&nbsp;None
1830
1831<P>
1832Context:&nbsp;virtual&nbsp;host
1833</DD>
1834</DL>In HTTP, cookies have names to distinguish them from each other. Using
1835mod_usertrack, for example, you can give your user-tracking cookies
1836a name with the CookieName directive.
1837
1838<P>
1839You must include a 'c' character in L<SMALL>OG</SMALL>SQLT<SMALL>RANSFER</SMALL>L<SMALL>OG</SMALL>F<SMALL>ORMAT</SMALL>
1840for this directive to take effect; once you specify 'c', L<SMALL>OG</SMALL>SQLW<SMALL>HICH</SMALL>C<SMALL>OOKIE</SMALL>
1841tells mod_log_sql which cookie to log. This is necessary because
1842you will usually be setting and receiving more than one cookie from
1843a client; this cookie designates which one to log.
1844
1845<P>
1846Note: although this was intended for people who are using mod_usertrack
1847to set user-tracking cookies, you aren't restricted in any way. You
1848can choose which cookie you wish to log to the database -any cookie
1849at all - and it doesn't necessarily have to have anything to do with
1850mod_usertrack.
1851
1852<P>
1853
1854<H3><A NAME="SECTION000462000000000000000">
18553.6.20 LogSQLWhichCookies</A>
1856</H3>
1857
1858<P>
1859
1860<DL COMPACT>
1861<DT>
1862<DD>Syntax:&nbsp;LogSQLWhichCookies&nbsp;cookie1&nbsp;cookie2&nbsp;...&nbsp;cookieN
1863
1864<P>
1865Example:&nbsp;LogSQLWhichCookies&nbsp;userlogin&nbsp;foobar&nbsp;foobaz
1866
1867<P>
1868Default:&nbsp;None
1869
1870<P>
1871Context:&nbsp;virtual&nbsp;host
1872</DD>
1873</DL>Defines the list of cookies you would like logged. This works in conjunction
1874with L<SMALL>OG</SMALL>SQLC<SMALL>OOKIE</SMALL>L<SMALL>OG</SMALL>T<SMALL>ABLE</SMALL>. This directive does not require
1875any additional characters to be added to the L<SMALL>OG</SMALL>SQLT<SMALL>RANSFER</SMALL>L<SMALL>OG</SMALL>F<SMALL>ORMAT</SMALL>
1876string. The feature is activated simply by including this directive,
1877upon which you will begin populating the separate cookie table with
1878data.
1879
1880<P>
1881Note that you must have already created the table (see create-tables.sql,
1882included in the package), or L<SMALL>OG</SMALL>SQLC<SMALL>REATE</SMALL>T<SMALL>ABLES</SMALL> must be set
1883to ``on''.
1884
1885<P>
1886
1887<H3><A NAME="SECTION000462100000000000000">
18883.6.21 LogSQLWhichHeadersIn</A>
1889</H3>
1890
1891<P>
1892
1893<DL COMPACT>
1894<DT>
1895<DD>Syntax:&nbsp;LogSQLWhichHeadersIn&nbsp;item1&nbsp;item2&nbsp;...&nbsp;itemN
1896
1897<P>
1898Example:&nbsp;LogSQLWhichHeadersIn&nbsp;UserAgent&nbsp;Accept-Encoding&nbsp;Host
1899
1900<P>
1901Default:&nbsp;None
1902
1903<P>
1904Context:&nbsp;virtual&nbsp;host
1905</DD>
1906</DL>Defines the list of inbound headers you would like logged. This works
1907in conjunction with L<SMALL>OG</SMALL>SQLH<SMALL>EADERS</SMALL>I<SMALL>N</SMALL>L<SMALL>OG</SMALL>T<SMALL>ABLE</SMALL>. This directive
1908does not require any additional characters to be added to the L<SMALL>OG</SMALL>SQLT<SMALL>RANSFER</SMALL>L<SMALL>OG</SMALL>F<SMALL>ORMAT</SMALL>
1909string. The feature is activated simply by including this directive,
1910upon which you will begin populating the separate inbound-headers
1911table with data.
1912
1913<P>
1914Note that you must have already created the table (see create-tables.sql,
1915included in the package), or L<SMALL>OG</SMALL>SQLC<SMALL>REATE</SMALL>T<SMALL>ABLES</SMALL> must be set
1916to ``on''.
1917
1918<P>
1919
1920<H3><A NAME="SECTION000462200000000000000">
19213.6.22 LogSQLWhichHeadersOut</A>
1922</H3>
1923
1924<P>
1925
1926<DL COMPACT>
1927<DT>
1928<DD>Syntax:&nbsp;LogSQLWhichHeadersOut&nbsp;item1&nbsp;item2&nbsp;...&nbsp;itemN
1929
1930<P>
1931Example:&nbsp;LogSQLWhichHeadersOut&nbsp;Expires&nbsp;Content-Type&nbsp;Cache-Control
1932
1933<P>
1934Default:&nbsp;None
1935
1936<P>
1937Context:&nbsp;virtual&nbsp;host
1938</DD>
1939</DL>Defines the list of outbound headers you would like logged. This works
1940in conjunction with L<SMALL>OG</SMALL>SQLH<SMALL>EADERS</SMALL>O<SMALL>UT</SMALL>L<SMALL>OG</SMALL>T<SMALL>ABLE</SMALL>. This directive
1941does not require any additional characters to be added to the L<SMALL>OG</SMALL>SQLT<SMALL>RANSFER</SMALL>L<SMALL>OG</SMALL>F<SMALL>ORMAT</SMALL>
1942string. The feature is activated simply by including this directive,
1943upon which you will begin populating the separate outbound-headers
1944table with data.
1945
1946<P>
1947Note that you must have already created the table (see create-tables.sql,
1948included in the package), or L<SMALL>OG</SMALL>SQLC<SMALL>REATE</SMALL>T<SMALL>ABLES</SMALL> must be set
1949to ``on''.
1950
1951<P>
1952
1953<H3><A NAME="SECTION000462300000000000000">
19543.6.23 LogSQLWhichNotes</A>
1955</H3>
1956
1957<P>
1958
1959<DL COMPACT>
1960<DT>
1961<DD>Syntax:&nbsp;LogSQLWhichNotes&nbsp;item1&nbsp;item2&nbsp;...&nbsp;itemN
1962
1963<P>
1964Example:&nbsp;LogSQLWhichNotes&nbsp;mod_gzip_result&nbsp;mod_gzip_compression_ratio
1965
1966<P>
1967Default:&nbsp;None
1968
1969<P>
1970Context:&nbsp;virtual&nbsp;host
1971</DD>
1972</DL>Defines the list of notes you would like logged. This works in conjunction
1973with L<SMALL>OG</SMALL>SQLN<SMALL>OTES</SMALL>L<SMALL>OG</SMALL>T<SMALL>ABLE</SMALL>. This directive does not require
1974any additional characters to be added to the L<SMALL>OG</SMALL>SQLT<SMALL>RANSFER</SMALL>L<SMALL>OG</SMALL>F<SMALL>ORMAT</SMALL>
1975string. The feature is activated simply by including this directive,
1976upon which you will begin populating the separate notes table with
1977data.
1978
1979<P>
1980Note that you must have already created the table (see create-tables.sql,
1981included in the package), or L<SMALL>OG</SMALL>SQLC<SMALL>REATE</SMALL>T<SMALL>ABLES</SMALL> must be set
1982to ``on''.
1983
1984<P>
1985<HR>
1986<!--Navigation Panel-->
1987<A NAME="tex2html172"
1988 HREF="node5.html">
1989<IMG WIDTH="37" HEIGHT="24" ALIGN="BOTTOM" BORDER="0" ALT="next" SRC="next.png"></A>
1990<A NAME="tex2html168"
1991 HREF="documentation.html">
1992<IMG WIDTH="26" HEIGHT="24" ALIGN="BOTTOM" BORDER="0" ALT="up" SRC="up.png"></A>
1993<A NAME="tex2html162"
1994 HREF="node3.html">
1995<IMG WIDTH="63" HEIGHT="24" ALIGN="BOTTOM" BORDER="0" ALT="previous" SRC="prev.png"></A>
1996<A NAME="tex2html170"
1997 HREF="node1.html">
1998<IMG WIDTH="65" HEIGHT="24" ALIGN="BOTTOM" BORDER="0" ALT="contents" SRC="contents.png"></A>
1999<BR>
2000<B> Next:</B> <A NAME="tex2html173"
2001 HREF="node5.html">4 FAQ</A>
2002<B> Up:</B> <A NAME="tex2html169"
2003 HREF="documentation.html">Installing and Running mod_log_sql</A>
2004<B> Previous:</B> <A NAME="tex2html163"
2005 HREF="node3.html">2 Installation</A>
2006 &nbsp; <B> <A NAME="tex2html171"
2007 HREF="node1.html">Contents</A></B>
2008<!--End of Navigation Panel-->
2009<ADDRESS>
2010Chris Powell
20112002-12-18
2012</ADDRESS>
2013</BODY>
2014</HTML>
generated by cgit (git 2.34.1) at 2024-11-21 19:18:20 +0000