summaryrefslogtreecommitdiffstats
path: root/Documentation
diff options
context:
space:
mode:
Diffstat (limited to 'Documentation')
-rw-r--r--Documentation/README13
-rw-r--r--Documentation/documentation.lyx7033
2 files changed, 0 insertions, 7046 deletions
diff --git a/Documentation/README b/Documentation/README
deleted file mode 100644
index 349b0ac..0000000
--- a/Documentation/README
+++ /dev/null
@@ -1,13 +0,0 @@
1$Id: README,v 1.2 2002/12/10 20:37:29 helios Exp $
2
3The "original" document is the LyX file "documentation.lyx" -- all other
4files here are derived from it. The most visually accurate document
5and the one I'd recommend reading is the PostScript (.ps) file, although
6a whole host of formats are made available here to suit your preference.
7
8To read the HTML docs, open HTML/documentation.html in your browser.
9To read the .dvi file, use Xdvi or kdvi.
10To read the .ps file, use gv or kghostview.
11To read the .lyx file, use LyX.
12To read the .txt file, use less.
13
diff --git a/Documentation/documentation.lyx b/Documentation/documentation.lyx
deleted file mode 100644
index a678315..0000000
--- a/Documentation/documentation.lyx
+++ /dev/null
@@ -1,7033 +0,0 @@
1#LyX 1.2 created this file. For more info see http://www.lyx.org/
2\lyxformat 220
3\textclass article
4\language english
5\inputencoding default
6\fontscheme default
7\graphics default
8\float_placement !htbp
9\paperfontsize 10
10\spacing single
11\papersize letterpaper
12\paperpackage a4
13\use_geometry 1
14\use_amsmath 0
15\use_natbib 0
16\use_numerical_citations 0
17\paperorientation portrait
18\leftmargin 1in
19\topmargin 0.5in
20\rightmargin 1in
21\bottommargin 0.65in
22\secnumdepth 3
23\tocdepth 3
24\paragraph_separation indent
25\defskip medskip
26\quotes_language english
27\quotes_times 2
28\papercolumns 1
29\papersides 1
30\paperpagestyle default
31
32\layout Title
33\added_space_top vfill \added_space_bottom vfill
34Installing and Running mod_log_sql
35\layout Author
36
37Christopher Powell, <chris@grubbybaby.com>
38\layout Standard
39\pagebreak_bottom
40
41\begin_inset LatexCommand \tableofcontents{}
42
43\end_inset
44
45
46\layout Section
47
48Introduction
49\layout Subsection
50
51Homepage
52\layout LyX-Code
53
54http://www.grubbybaby.com/mod_log_sql/
55\layout Subsection
56
57Summary
58\layout Standard
59
60This Apache module will permit you to log to a SQL database; it can log
61 each access request as well as data associated with each request: cookies,
62 notes, and inbound/outbound headers.
63 Unlike logging to a flat text file -- which is standard in Apache -- a
64 SQL-based log exhibits tremendous flexibility and power of data extraction.
65 (See section
66\begin_inset LatexCommand \ref{sub:why}
67
68\end_inset
69
70 in the FAQ for further discussion and examples of the advantages to SQL.)
71\layout Standard
72
73This module can either replace or happily coexist with mod_log_config, Apache's
74 text file logging facility.
75 In addition to being more configurable than the standard module, mod_log_sql
76 is much more flexible.
77\layout Subsection
78
79Approach
80\layout Standard
81
82This project was formerly known as
83\begin_inset Quotes eld
84\end_inset
85
86mod_log_mysql.
87\begin_inset Quotes erd
88\end_inset
89
90 It was renamed
91\begin_inset Quotes eld
92\end_inset
93
94mod_log_sql
95\begin_inset Quotes erd
96\end_inset
97
98 in order to reflect the project goal of database-inspecificity.
99 The module currently supports MySQL, but support for other database backends
100 is underway.
101\layout Standard
102
103In order to save speed and overhead, links are kept alive in between queries.
104 This module uses one dedicated SQL link per httpd child, opened by each
105 child process when it is born.
106 Among other things, this means that this module supports logging into only
107 one MySQL server, and for now, also, only one SQL database.
108 But that's a small tradeoff compared to the blinding speed of this module.
109 Error reporting is robust throughout the module and will inform the administrat
110or of database issues in the Apache
111\noun on
112ErrorLog
113\noun default
114 for the server/virtual server.
115\layout Standard
116
117Virtual hosts are supported in the same manner they are in the regular logging
118 modules.
119 The administrator defines some basic 'global' directives in the main server
120 config, then defines more specific 'local' directives inside each VirtualHost
121 stanza.
122\layout Standard
123
124A robust "preserve" capability has now been implemented.
125 This permits the module to preserve any failed INSERT commands to a local
126 file on its machine.
127 In any situation that the database is unavailable -- e.g.
128 the network fails or the database host is rebooted -- mod_log_sql will
129 note this in the error log and begin appending its log entries to the preserve
130 file (which is created with the user & group ID of the running Apache process,
131 e.g.
132 "nobody/nobody" on many Linux installations).
133 When database availablity returns, mod_log_sql seamlessly resumes logging
134 to it.
135 When convenient for the sysadmin, he/she can easily import the preserve
136 file into the database because it is simply a series of SQL insert statements.
137\layout Subsection
138
139What gets logged by default?
140\layout Standard
141
142All the data that would be contained in the "Combined Log Format" is logged
143 by default, plus a little extra.
144 Your best bet is to begin by accepting this default, then later customize
145 the log configuration based on your needs.
146\layout Standard
147
148The documentation of the run-time directives includes a full explanation
149 of what you can log, including examples -- see section
150\begin_inset LatexCommand \ref{sec:ConfRef}
151
152\end_inset
153
154.
155\layout Subsection
156
157Miscellaneous Notes
158\layout Itemize
159
160Note which directives go in the 'main server config' and which directives
161 apply to the 'virtual host config'.
162 This is made clear in the directive documentation.
163\layout Itemize
164
165The 'time_stamp' field is stored in an UNSIGNED INTEGER column, in the standard
166 unix
167\begin_inset Quotes eld
168\end_inset
169
170seconds since the epoch
171\begin_inset Quotes erd
172\end_inset
173
174 format.
175 This is superior to storing the access time as a string due to size requirement
176s: an UNSIGNED INT requires 4 bytes, whereas an Apache date string -- e.g.
177 "18/Nov/2001:13:59:52 -0800" -- requires 26 bytes: those extra 22 bytes
178 become significant when multiplied by thousands of accesses on a busy server.
179 Besides, an INT type is far more flexible for comparisons, etc.
180\begin_deeper
181\layout Standard
182
183In MySQL 3.21 and above you can easily convert this to a human readable format
184 using from_unixtime(), e.g.:
185\layout LyX-Code
186
187select remote_host,request_uri,from_unixtime(time_stamp) from access_log;
188\layout Standard
189
190The enclosed perl program
191\begin_inset Quotes eld
192\end_inset
193
194make_combined_log.pl
195\begin_inset Quotes srd
196\end_inset
197
198 extracts your access log in a format that is completely compatible with
199 the Combined Log Format.
200 You can then feed this to your favorite web log analysis tool.
201\end_deeper
202\layout Itemize
203
204The table's string values can be CHAR or VARCHAR, at a length of your choice.
205 VARCHAR is superior because it truncates long strings; CHAR types are fixed-len
206gth and will be padded with spaces, resulting in waste.
207 Just like the time_stamp issue described above, that kind of space waste
208 multiplies over thousands of records.
209\layout Itemize
210
211Be careful not to go overboard setting fields to NOT NULL.
212 If a field is marked NOT NULL then it must contain data in the INSERT statement
213, or the INSERT will fail.
214 These mysterious failures can be quite frustrating and difficult to debug.
215\layout Itemize
216
217When Apache logs a numeric field, it uses a '-' character to mean
218\begin_inset Quotes eld
219\end_inset
220
221not applicable,
222\begin_inset Quotes erd
223\end_inset
224
225 e.g.
226 the number of bytes returned on a 304 (unchanged) request.
227 Since '-' is an illegal character in an SQL numeric field, such fields
228 are assigned the value 0 instead of '-' which, of course, makes perfect
229 sense anyway.
230\layout Subsection
231
232Author / Maintainer
233\layout Standard
234
235The actual logging code was taken from the already existing flat file text
236 modules, so all that credit goes to the Apache Server group.
237\layout Standard
238
239The MySQL routines and directives were added by Zeev Suraski <bourbon@netvision.n
240et.il>.
241\layout Standard
242
243All changes from 1.06+ and the new documentation were added by Chris Powell
244 <chris@grubbybaby.com>.
245 It seems that the module had fallen into the "unmaintained" category --
246 it hadn't been updated since 1998 -- so Chris adopted it as the new maintainer.
247\layout Section
248
249Installation
250\layout Subsection
251
252Requirements
253\layout Itemize
254
255A compatible system.
256 mod_log_sql was authored and tested on systems based on Red Hat Linux (Red
257 Hat, Mandrake), but the module should easily adapt to any modern distribution.
258 mod_log_sql has also been ported successfully to Solaris and FreeBSD.
259\layout Itemize
260
261Apache 1.2 or 1.3.
262 Ideally you should already have successfully compiled Apache and understand
263 the process, but this document tries to make it simple for beginners.
264\layout Itemize
265
266The MySQL development headers.
267 This package is called different things on different distros.
268 For example, Red Hat 6.x calls this RPM
269\begin_inset Quotes eld
270\end_inset
271
272MySQL-devel
273\begin_inset Quotes erd
274\end_inset
275
276 whereas Mandrake calls it
277\begin_inset Quotes eld
278\end_inset
279
280libmysql10-devel.
281\begin_inset Quotes erd
282\end_inset
283
284
285\layout Itemize
286
287MySQL >= 3.23.15 configured, installed and running on either localhost or
288 an accessible networked machine.
289 You should already have a basic understanding of MySQL and how it functions.
290\layout Itemize
291
292Optionally, if you want to be able to log SSL information such as keysize
293 or cipher, you need OpenSSL and mod_ssl installed.
294\layout Subsection
295
296Platform-specific notes
297\layout Standard
298
299These installation documents assume a relatively modern GNU/Linux scenario.
300 mod_log_sql has been ported to other platforms; following are notes on
301 compiling the module for those platforms.
302\layout Subsubsection
303
304Solaris
305\layout Standard
306
307The nanosleep() function used in mod_log_sql relies on linking aginst the
308 librt library.
309 Make the following alterations before proceeding:
310\layout Enumerate
311
312In Makefile, search for the string
313\begin_inset Quotes eld
314\end_inset
315
316-lmysqlclient -lz
317\begin_inset Quotes erd
318\end_inset
319
320 and change it to read
321\begin_inset Quotes eld
322\end_inset
323
324-lmysqlclient -lz -lrt
325\begin_inset Quotes erd
326\end_inset
327
328
329\layout Enumerate
330
331In part
332\begin_inset LatexCommand \ref{step:Linking}
333
334\end_inset
335
336 of section
337\begin_inset LatexCommand \ref{sec:Static}
338
339\end_inset
340
341 below, change
342\begin_inset Quotes eld
343\end_inset
344
345-lmysqlclient -lm -lz
346\begin_inset Quotes erd
347\end_inset
348
349 to read
350\begin_inset Quotes eld
351\end_inset
352
353-lmysqlclient -lm -lz -lrt
354\begin_inset Quotes erd
355\end_inset
356
357
358\layout Subsubsection
359
360BSD
361\layout Standard
362
363No notes are available at present, but they are desired.
364 If you have successfully ported mod_log_sql to BSD,
365\emph on
366please
367\emph default
368contact
369\begin_inset LatexCommand \url[the maintaniner, Chris Powell]{(chris@grubbybaby.com)}
370
371\end_inset
372
373 and help fill in this section.
374\layout Subsubsection
375
376Win32
377\layout Standard
378
379No notes are available at present, but they are desired.
380 If you have successfully ported mod_log_sql to Win32,
381\emph on
382please
383\emph default
384contact
385\begin_inset LatexCommand \url[the maintaniner, Chris Powell]{(chris@grubbybaby.com)}
386
387\end_inset
388
389 and help fill in this section.
390\layout Subsection
391
392Do I want a DSO or a static module?
393\layout Standard
394
395You need to know the answer to this question before you proceed.
396 The answer is pretty straightforward: what have you done in the past? If
397 you like all your Apache modules to be dynamic, then you should keep doing
398 that.
399 If you're more of an old-school type and prefer to compile the modules
400 right into apache, do that.
401 Both methods work equally well.
402\layout Standard
403
404FWIW, the DSO method is more modern and increasing in popularity because
405 apxs takes care of a lot of dirty little details for you.
406 As you'll see below, the static-module method is a little more complex.
407\layout Subsection
408
409Installation as an Apache DSO (Preferred)
410\layout Enumerate
411
412Perform all the following steps as root so that you have install privs,
413 etc.
414 Unpack the archive into a working directory.
415\begin_deeper
416\layout LyX-Code
417
418# tar zxf mod_log_sql.tar.gz -C /usr/local/src
419\layout LyX-Code
420
421# cd /usr/local/src/mod_log_sql
422\end_deeper
423\layout Enumerate
424
425Edit Makefile and change the values of the variables in the first section.
426
427\begin_deeper
428\layout Enumerate
429
430These paths are
431\series bold
432necessary:
433\begin_deeper
434\layout Description
435
436APACHEINSTALLED: the location where you installed Apache -- usually /usr/local/a
437pache, 'locate apxs' can help you find it.
438\layout Description
439
440APACHEHEADERS: The location of your Apache header files, find using 'locate
441 httpd.h'
442\layout Description
443
444MYSQLLIBRARIES: The location of your MySQL libraries, find using 'locate
445 libmysqlclient.so'
446\layout Description
447
448MYSQLHEADERS: The location of your MySQL header files, find using 'locate
449 mysql.h'
450\end_deeper
451\layout Enumerate
452
453
454\series bold
455Optional
456\series default
457: if you compiled mod_ssl for Apache and want to log SSL data such as 'keysize'
458 and 'cipher type':
459\begin_deeper
460\layout Description
461
462MODSSLHEADERS: the location of your mod_ssl header files, find using 'locate
463 mod_ssl.h'
464\layout Description
465
466DB1HEADERS: the location of your db1 header files, find using 'locate ndbm.h'
467\end_deeper
468\layout Standard
469
470You do
471\series bold
472not
473\series default
474need to compile SSL support into mod_log_sql in order to simply use it with
475 a secure site.
476 You only need to compile SSL support into mod_log_sql
477\series bold
478if you want to log SSL-specific data
479\series default
480such as the cipher type.
481\end_deeper
482\layout Enumerate
483
484IMPORTANT: If you are not logging SSL info, comment out MODSSLHDRS by putting
485 a # character in front of it:
486\begin_deeper
487\layout LyX-Code
488
489#MODSSLHDRS=/usr/include/...
490\end_deeper
491\layout Enumerate
492
493Instruct apxs to compile the module as a DSO.
494\begin_deeper
495\layout LyX-Code
496
497# make dso
498\layout Standard
499
500You should see output similar to the following:
501\layout LyX-Code
502
503/usr/local/Apache/bin/apxs -Wc,-O2 -Wc,-Wall -Wc,-DEAPI -c -I/usr/...
504\layout LyX-Code
505
506gcc -DLINUX=22 -DNO_DBM_REWRITEMAP -DMOD_SSL=208111 -DUSE_HS...
507
508\layout LyX-Code
509
510gcc -shared -o mod_log_sql.so mod_log_sql.o -Wc,-O2 -Wc,-Wall -Wc...
511\layout Standard
512
513You should see no errors and have a new file called "mod_log_sql.so" in your
514 directory.
515\end_deeper
516\layout Enumerate
517
518Instruct apxs to install the DSO.
519\begin_deeper
520\layout LyX-Code
521
522# make dsoinstall
523\layout Standard
524
525You should see output similar to the following:
526\layout LyX-Code
527
528/usr/local/Apache/bin/apxs -i mod_log_sql.so
529\layout LyX-Code
530
531cp mod_log_sql.so /usr/local/Apache/libexec/mod_log_sql.so
532\layout LyX-Code
533
534chmod 755 /usr/local/Apache/libexec/mod_log_sql.so
535\end_deeper
536\layout Enumerate
537
538Load and activate the module in httpd.conf:
539\begin_deeper
540\layout Enumerate
541
542Insert this line in the same area as other logging modules, e.g.
543 near
544\begin_inset Quotes eld
545\end_inset
546
547LoadModule config_log_module
548\begin_inset Quotes erd
549\end_inset
550
551:
552\begin_deeper
553\layout LyX-Code
554
555LoadModule sql_log_module libexec/mod_log_sql.so
556\end_deeper
557\layout Enumerate
558
559Insert this line in the same area as other logging modules, e.g.
560 near
561\begin_inset Quotes eld
562\end_inset
563
564AddModule mod_log_config.c
565\begin_inset Quotes erd
566\end_inset
567
568:
569\begin_deeper
570\layout LyX-Code
571
572AddModule mod_log_sql.c
573\end_deeper
574\end_deeper
575\layout Enumerate
576
577Module ordering within httpd.conf is important if you are logging SSL information.
578 Please ensure that
579\begin_deeper
580\layout LyX-Code
581
582LoadModule ssl_module libexec/libssl.so
583\layout Standard
584
585comes before
586\layout LyX-Code
587
588LoadModule sql_log_module libexec/mod_log_sql.so
589\layout Standard
590
591in your httpd.conf file.
592 If they are out of order, simply cut-and-paste the
593\begin_inset Quotes eld
594\end_inset
595
596ssl_module
597\begin_inset Quotes erd
598\end_inset
599
600 section so that it is at the top.
601 If you do not, you will get this error when you start Apache:
602\layout LyX-Code
603
604/usr/local/apache/libexec/mod_log_mysql.so: undefined symbol: ssl_var_lookup
605\layout LyX-Code
606
607/usr/local/apache/bin/apachectl startssl: httpd could not be started
608\layout Standard
609
610(mod_log_sql has a dependency on mod_ssl for SSL symbols.
611 If the statements are out of order, mod_log_sql cannot recognize those
612 symbols.)
613\layout Standard
614
615Now skip below to section
616\begin_inset LatexCommand \ref{sec:Configuration}
617
618\end_inset
619
620,
621\series bold
622Configuration
623\series default
624.
625\end_deeper
626\layout Subsection
627
628
629\begin_inset LatexCommand \label{sec:Static}
630
631\end_inset
632
633Installation as a static module compiled into httpd
634\layout Enumerate
635
636Perform all the following steps as root so that you have install privs,
637 etc.
638\layout Enumerate
639
640Unpack the archive into a working directory.
641\begin_deeper
642\layout LyX-Code
643
644# tar zxf mod_log_sql.tar.gz -C /usr/local/src
645\layout LyX-Code
646
647# cd /usr/local/src/mod_log_sql
648\end_deeper
649\layout Enumerate
650
651
652\begin_inset LatexCommand \label{step:editMF}
653
654\end_inset
655
656Edit Makefile and change the values of the variables in the first section.
657
658\begin_deeper
659\layout Enumerate
660
661These are
662\series bold
663necessary:
664\begin_deeper
665\layout Description
666
667APACHEINSTALLED: the location where you installed Apache -- usually /usr/local/a
668pache, 'locate apxs' can help you find it.
669\layout Description
670
671APACHESOURCE: the location of your Apache
672\series bold
673sources
674\series default
675, find using 'locate ABOUT_APACHE'
676\layout Description
677
678APACHEHEADERS: the location of your Apache header files, find using 'locate
679 httpd.h'
680\layout Description
681
682MYSQLLIBRARIES: the location of your MySQL libraries, find using 'locate
683 libmysqlclient.so'
684\layout Description
685
686MYSQLHEADERS: the location of your MySQL header files, find using 'locate
687 mysql.h'
688\end_deeper
689\layout Enumerate
690
691
692\series bold
693Optional
694\series default
695: if you compiled mod_ssl for Apache and want to log SSL data such as 'keysize'
696 and 'cipher type':
697\begin_deeper
698\layout Description
699
700MODSSLHEADERS: the location of your mod_ssl header files, find using 'locate
701 mod_ssl.h'
702\layout Description
703
704DB1HEADERS: the location of your db1 header files, find using 'locate ndbm.h'
705\end_deeper
706\layout Standard
707
708You do
709\series bold
710not
711\series default
712need to compile SSL support into mod_log_sql in order to simply use it with
713 a secure site.
714 You only need to compile SSL support into mod_log_sql
715\series bold
716if you want to log SSL-specific data
717\series default
718 such as the cipher type.
719\end_deeper
720\layout Enumerate
721
722IMPORTANT: If you are not logging SSL info, comment out MODSSLHDRS by putting
723 a # character in front of it:
724\begin_deeper
725\layout LyX-Code
726
727#MODSSLHDRS=/usr/include/...
728\end_deeper
729\layout Enumerate
730
731Compile the module.
732\begin_deeper
733\layout LyX-Code
734
735# make static
736\layout Standard
737
738You should see output similar to the following:
739\layout LyX-Code
740
741gcc -fpic -O2 -Wall -I/usr/local/Apache/include -I/usr/include/mysql -I/usr/lo...
742\layout Standard
743
744You should see no errors and have a new file called "mod_log_sql.o" in your
745 directory.
746\end_deeper
747\layout Enumerate
748
749Install the module.
750\begin_deeper
751\layout LyX-Code
752
753# make statinstall
754\end_deeper
755\layout Enumerate
756
757Change to your Apache source directory.
758\begin_deeper
759\layout LyX-Code
760
761# cd /usr/local/src/apache-1.3.22/src
762\end_deeper
763\layout Enumerate
764
765Re-compile your httpd binary as follows.
766\begin_deeper
767\layout Enumerate
768
769
770\begin_inset LatexCommand \label{step:Linking}
771
772\end_inset
773
774Make these changes to Configuration.apaci:
775\begin_deeper
776\layout Itemize
777
778Append the following string to the EXTRA_LIBS= line.
779 ("/usr/lib/mysql" is from step
780\begin_inset LatexCommand \ref{step:editMF}
781
782\end_inset
783
784, and is where your MySQL libraries live):
785\layout LyX-Code
786
787-L/usr/lib/mysql -lmysqlclient -lm -lz
788\layout Itemize
789
790Find the mod_log_config.o line, and insert this line immediately after it:
791\layout LyX-Code
792
793AddModule modules/sql/mod_log_sql.o
794\end_deeper
795\layout Enumerate
796
797# cp Configuration.apaci Configuration
798\layout Enumerate
799
800# ./Configure
801\layout Enumerate
802
803# make
804\layout Enumerate
805
806# strip httpd
807\end_deeper
808\layout Enumerate
809
810Test your new apache binary:
811\begin_deeper
812\layout LyX-Code
813
814# ./httpd -l
815\layout Standard
816
817You should see something like:
818\layout LyX-Code
819
820Compiled-in modules:
821\layout LyX-Code
822
823http_core.c
824\layout LyX-Code
825
826mod_log_sql.c <-- That's the line you're looking for.
827\layout LyX-Code
828
829mod_env.c
830\layout LyX-Code
831
832mod_log_config.c
833\layout LyX-Code
834
835mod_mime.c
836\layout LyX-Code
837
838mod_negotiation.c
839\layout LyX-Code
840
841etc...
842\end_deeper
843\layout Enumerate
844
845Install your httpd binary.
846 Copy it over your old httpd binary, wherever it lives.
847 You can and should rename your old httpd first so that you can easily revert
848 to that working version in case of bugs with the new version.
849\begin_deeper
850\layout LyX-Code
851
852# /etc/rc.d/init.d/httpd stop
853\layout LyX-Code
854
855# mv /usr/local/Apache/bin/httpd ~/httpd-save
856\layout LyX-Code
857
858# cp -f ./httpd /usr/local/Apache/bin/
859\end_deeper
860\layout Section
861
862
863\begin_inset LatexCommand \label{sec:Configuration}
864
865\end_inset
866
867Configuration
868\layout Subsection
869
870
871\begin_inset LatexCommand \label{sub:PrepDb}
872
873\end_inset
874
875Preparing MySQL for logging
876\layout Standard
877
878You have to prepare the database to receive data from mod_log_sql, and set
879 up run-time directives in httpd.conf to control how and what mod_log_sql
880 logs.
881\layout Standard
882
883This section will discuss how to get started with a basic config.
884 Full documentation of all available run-time directives is available in
885 section
886\begin_inset LatexCommand \ref{sec:ConfRef}
887
888\end_inset
889
890.
891\layout Enumerate
892
893mod_log_sql can make its own tables on-the-fly, or you can pre-make the
894 tables by hand.
895 The advantage of letting the module make the tables is ease-of-use, but
896 for raw performance you will want to pre-make the tables in order to save
897 some overhead.
898 In this basic setup we'll just let the module create tables for us.
899\layout Enumerate
900
901We still need to have a logging database created and ready, so run the MySQL
902 command line client and create a database:
903\begin_deeper
904\layout LyX-Code
905
906# mysql -uadmin -pmypassword
907\layout LyX-Code
908
909Enter password:
910\layout LyX-Code
911
912mysql> create database apachelogs;
913\end_deeper
914\layout Enumerate
915
916
917\begin_inset LatexCommand \label{part:CrTbl}
918
919\end_inset
920
921If you want to hand-create the tables, run the enclosed 'create-tables'
922 SQL script as follows:
923\begin_deeper
924\layout LyX-Code
925
926mysql> source create_tables.sql
927\end_deeper
928\layout Enumerate
929
930Create a specific MySQL userid that httpd will use to authenticate and enter
931 data.
932 This userid need not be an actual Unix user.
933 It is a userid internal to MySQL with specific privileges.
934 In the following example command, "apachelogs" is the database, "loguser"
935 is the userid to create, "my.apachemachine.com" is the name of the Apache
936 machine, and "l0gger" is the password to assign.
937 Choose values that are different from these examples.
938\begin_deeper
939\layout LyX-Code
940
941mysql> grant insert,create on apachelogs.* to loguser@my.apachemachine.com
942\layout LyX-Code
943
944identified by 'l0gger';
945\end_deeper
946\layout Enumerate
947
948You may be especially security-paranoid and want "loguser" to
949\emph on
950not
951\emph default
952have "create" capability within the "apachelogs" database.
953 You can disable that privilege, but the cost is that you will not be able
954 to use the module's on-the-fly table creation feature.
955 If that cost is acceptable, hand-create the tables as described in step
956
957\begin_inset LatexCommand \ref{part:CrTbl}
958
959\end_inset
960
961 and use the following GRANT statement instead of the one above:
962\begin_deeper
963\layout LyX-Code
964
965mysql> grant insert on apachelogs.* to loguser@my.apachemachine.com
966\layout LyX-Code
967
968identified by 'l0gger';
969\end_deeper
970\layout Enumerate
971
972
973\begin_inset LatexCommand \label{step:EnaLog}
974
975\end_inset
976
977Enable full logging of your MySQL daemon (at least temporarily for debugging
978 purposes) if you don't do this already.
979 Edit /etc/my.cnf and add the following line to your [mysqld] section:
980\begin_deeper
981\layout LyX-Code
982
983log=/var/log/mysql-messages
984\layout Standard
985
986Then restart MySQL.
987\layout LyX-Code
988
989# /etc/rc.d/init.d/mysql restart
990\end_deeper
991\layout Subsection
992
993A very basic logging setup in Apache
994\layout Enumerate
995
996Tell the module what database to use and the appropriate authentication
997 information.
998\begin_deeper
999\layout Standard
1000
1001So, edit httpd.conf and insert the following lines somewhere after any LoadModule
1002 / AddModule statements.
1003
1004\emph on
1005Make sure these statements are
1006\begin_inset Quotes eld
1007\end_inset
1008
1009global,
1010\begin_inset Quotes erd
1011\end_inset
1012
1013 i.e.
1014 not inside any VirtualHost stanza
1015\emph default
1016.
1017 You will also note that you are embedding a password in the file.
1018 Therefore you are advised to
1019\begin_inset Quotes eld
1020\end_inset
1021
1022chmod 660 httpd.conf
1023\begin_inset Quotes erd
1024\end_inset
1025
1026 to prevent unauthorized regular users from viewing your database user and
1027 password.
1028\layout Standard
1029
1030
1031\series bold
1032Example
1033\series default
1034: Use the MySQL database called "apachelogs" running on "dbmachine.foo.com".
1035 Use username "loguser" and password "l0gg3r" to authenticate to the database.
1036 Permit the module create tables for us.
1037\layout LyX-Code
1038
1039LogSQLLoginInfo dbmachine.foo.com loguser l0gg3r
1040\layout LyX-Code
1041
1042LogSQLDatabase apachelogs
1043\layout LyX-Code
1044
1045LogSQLCreateTables on
1046\layout Standard
1047
1048If your database resides on localhost instead of another host, specify the
1049 MySQL server's socket file as follows:
1050\layout LyX-Code
1051
1052LogSQLSocketFile /your/path/to/mysql.sock
1053\layout Standard
1054
1055If your database is listening on a port other than 3306, specify the correct
1056 TCP port as follows:
1057\layout LyX-Code
1058
1059LogSQLTCPPort 1234
1060\end_deeper
1061\layout Enumerate
1062
1063The actual logging is set up on a virtual-host-by-host basis.
1064 So, skip down to the virtual host you want to set up.
1065 Instruct this virtual host to log entries to the table
1066\begin_inset Quotes eld
1067\end_inset
1068
1069access_log
1070\begin_inset Quotes srd
1071\end_inset
1072
1073 by inserting a
1074\noun on
1075LogSQLTransferLogTable
1076\noun default
1077 directive.
1078 (The
1079\noun on
1080LogSQLTransferLogTable
1081\noun default
1082 directive is the minimum required to log -- other directives that you'll
1083 learn about later simply tune the module's behavior.)
1084\begin_deeper
1085\layout LyX-Code
1086
1087<VirtualHost 1.2.3.4>
1088\layout LyX-Code
1089
1090 [snip]
1091\layout LyX-Code
1092
1093 LogSQLTransferLogTable access_log
1094\layout LyX-Code
1095
1096 [snip]
1097\layout LyX-Code
1098
1099</VirtualHost>
1100\end_deeper
1101\layout Enumerate
1102
1103Restart apache.
1104\begin_deeper
1105\layout LyX-Code
1106
1107# /etc/rc.d/init.d/httpd stop
1108\layout LyX-Code
1109
1110# /etc/rc.d/init.d/httpd start
1111\end_deeper
1112\layout Subsection
1113
1114Testing the basic setup
1115\layout Enumerate
1116
1117Visit your web site in a browser to trigger some hits, then confirm that
1118 the entries are being successfully logged:
1119\begin_deeper
1120\layout LyX-Code
1121
1122# mysql -hdbmachine.foo.com -umysqladmin -p -e "select * from access_log"
1123 apachelogs
1124\layout LyX-Code
1125
1126Enter password:
1127\layout Standard
1128
1129Several lines of output should follow, corresponding to your hits on the
1130 site.
1131 You now have basic functionality.
1132 Don't disable your regular Apache logs until you feel comfortable that
1133 the database is behaving as you'd like and that things are going well.
1134 If you do not see any entries in the access_log, please consult section
1135
1136\begin_inset LatexCommand \ref{faq:NothingLogged}
1137
1138\end_inset
1139
1140 of the FAQ on how to debug and fix the situation.
1141\end_deeper
1142\layout Enumerate
1143
1144You can now activate the advanced features of mod_log_sql, which are described
1145 in the next section.
1146\layout Subsection
1147
1148How to tune logging with run-time directives
1149\layout Subsubsection
1150
1151Instructing the module what to log
1152\layout Standard
1153
1154The most basic directive for the module is
1155\noun on
1156LogSQLTransferLogFormat
1157\noun default
1158, which tells the module which information to send to the database; logging
1159 to the database will not take place without it.
1160 Place a
1161\noun on
1162LogSQLTransferLogFormat
1163\noun default
1164 directive in the VirtualHost stanza of each virtual host that you want
1165 to activate.
1166\layout Standard
1167
1168After
1169\noun on
1170LogSQLTransferLogFormat
1171\noun default
1172 you supply a string of characters that tell the module what information
1173 to log.
1174 In the configuration directive reference (section
1175\begin_inset LatexCommand \ref{sub:Frmat}
1176
1177\end_inset
1178
1179) there is a table which clearly defines all the possible things to log.
1180 Let's say you want to log only the
1181\begin_inset Quotes eld
1182\end_inset
1183
1184request time,
1185\begin_inset Quotes erd
1186\end_inset
1187
1188 the
1189\begin_inset Quotes eld
1190\end_inset
1191
1192remote host,
1193\begin_inset Quotes erd
1194\end_inset
1195
1196 and the
1197\begin_inset Quotes eld
1198\end_inset
1199
1200request
1201\begin_inset Quotes erd
1202\end_inset
1203
1204; you'd use:
1205\layout LyX-Code
1206
1207LogSQLTransferLogFormat hUS
1208\layout Standard
1209
1210But a more appropriate string to use is
1211\layout LyX-Code
1212
1213LogSQLTransferLogFormat AbHhmRSsTUuv
1214\layout Standard
1215
1216which logs all the information required to be compatible with the Combined
1217 Log Format (CLF).
1218\layout Standard
1219
1220If you don't choose to log everything that is available, that's fine.
1221 Fields in the unused columns in your table will simply contain NULL.
1222\layout Standard
1223
1224Some of the
1225\noun on
1226LogSQLTransferLogFormat
1227\noun default
1228 characters require a little extra configuration:
1229\layout Itemize
1230
1231If you specify 'c' to indicate that you want to log the cookie value, you
1232 must also tell the module which cookie you mean by using
1233\noun on
1234LogSQLWhichCookie
1235\noun default
1236 -- after all, there could be many cookies associated with a given request.
1237 Fail to specify
1238\noun on
1239LogSQLWhichCookie
1240\noun default
1241, and no cookie information at all will be logged.
1242
1243\layout Itemize
1244
1245If you specify 'M' to indicate that you want to log the machine ID, you
1246 must also tell the module this machine's identity using the
1247\noun on
1248LogSQLMachineID
1249\noun default
1250 directive.
1251 Fail to specify
1252\noun on
1253LogSQLMachineID
1254\noun default
1255, and a simple '-' character will be logged in the machine_id column.
1256\layout Subsubsection
1257
1258
1259\begin_inset LatexCommand \label{sub:Ignore}
1260
1261\end_inset
1262
1263Instructing the module what NOT to log using filtering directives
1264\layout Standard
1265
1266One
1267\begin_inset Quotes eld
1268\end_inset
1269
1270accept
1271\begin_inset Quotes erd
1272\end_inset
1273
1274 and two
1275\begin_inset Quotes eld
1276\end_inset
1277
1278ignore
1279\begin_inset Quotes srd
1280\end_inset
1281
1282 directives allow you to fine-tune what the module should not log.
1283 These are very handy for keeping your database as uncluttered as possible
1284 and keeping your statistics free of unneeded numbers.
1285 Think of each one as a gatekeeper.
1286\layout Standard
1287
1288
1289\emph on
1290It is important to remember that each of these three directives is purely
1291 optional.
1292 mod_log_sql's default is to log everything.
1293
1294\layout Standard
1295
1296When a request comes in, the contents of
1297\noun on
1298LogSQLRequestAccept
1299\noun default
1300are evaluated first.
1301 This optional,
1302\begin_inset Quotes eld
1303\end_inset
1304
1305blanket
1306\begin_inset Quotes erd
1307\end_inset
1308
1309 directive lets you specify that only certain things are to be accepted
1310 for logging, and everything else discarded.
1311 Because it is evaluated before
1312\noun on
1313LogSQLRequestIgnore
1314\noun default
1315and
1316\noun on
1317LogSQLRemhostIgnore
1318\noun default
1319it can halt logging before those two filtering directives
1320\begin_inset Quotes eld
1321\end_inset
1322
1323get their chance.
1324\begin_inset Quotes erd
1325\end_inset
1326
1327
1328\layout Standard
1329
1330Once a request makes it past
1331\noun on
1332LogSQLRequestAccept
1333\noun default
1334, it still can be excluded based on
1335\noun on
1336LogSQLRemhostIgnore
1337\noun default
1338 and
1339\noun on
1340LogSQLRequestIgnore
1341\noun default
1342.
1343 A good way to use
1344\noun on
1345LogSQLRemhostIgnore
1346\noun default
1347 is to prevent the module from logging the traffic that your internal hosts
1348 generate.
1349
1350\noun on
1351LogSQLRequestIgnore
1352\noun default
1353 is great for preventing things like requests for
1354\begin_inset Quotes eld
1355\end_inset
1356
1357favicon.ico
1358\begin_inset Quotes erd
1359\end_inset
1360
1361 from cluttering up your database, as well as excluding the various requests
1362 that worms make, etc.
1363\layout Standard
1364
1365You can specify a series of strings after each directive.
1366 Do not use any type of globbing or regular-expression syntax -- each string
1367 is considered a match
1368\emph on
1369 if it is a substring of the larger request or remote-host; the comarison
1370 is case-sensitive.
1371
1372\emph default
1373 This means that
1374\noun on
1375
1376\begin_inset Quotes eld
1377\end_inset
1378
1379LogSQLRemhostIgnore
1380\noun default
1381 micro
1382\begin_inset Quotes erd
1383\end_inset
1384
1385 will ignore requests from
1386\begin_inset Quotes eld
1387\end_inset
1388
1389microsoft.com,
1390\begin_inset Quotes erd
1391\end_inset
1392
1393
1394\begin_inset Quotes eld
1395\end_inset
1396
1397microworld.net,
1398\begin_inset Quotes erd
1399\end_inset
1400
1401
1402\begin_inset Quotes eld
1403\end_inset
1404
1405mymicroscope.org,
1406\begin_inset Quotes erd
1407\end_inset
1408
1409 etc.
1410
1411\begin_inset Quotes eld
1412\end_inset
1413
1414
1415\noun on
1416LogSQLRequestIgnore
1417\noun default
1418 gif
1419\begin_inset Quotes erd
1420\end_inset
1421
1422 will instruct the module to ignore requests for
1423\begin_inset Quotes eld
1424\end_inset
1425
1426leftbar.gif,
1427\begin_inset Quotes erd
1428\end_inset
1429
1430
1431\begin_inset Quotes eld
1432\end_inset
1433
1434bluedot.gif
1435\begin_inset Quotes erd
1436\end_inset
1437
1438 and even
1439\begin_inset Quotes eld
1440\end_inset
1441
1442giftwrap.jpg
1443\begin_inset Quotes erd
1444\end_inset
1445
1446 -- but
1447\begin_inset Quotes eld
1448\end_inset
1449
1450RED.GIF
1451\begin_inset Quotes erd
1452\end_inset
1453
1454 and
1455\begin_inset Quotes eld
1456\end_inset
1457
1458Tree.Gif
1459\begin_inset Quotes erd
1460\end_inset
1461
1462 would still get logged because of case sensitivity.
1463\layout Standard
1464
1465A summary of the decision flow:
1466\layout Enumerate
1467
1468If
1469\noun on
1470LogSQLRequestAccept
1471\noun default
1472 exists and a request does not match anything in that list, it is discarded.
1473\layout Enumerate
1474
1475If a request matches anything in the
1476\noun on
1477LogSQLRequestIgnore
1478\noun default
1479list, it is discarded.
1480\layout Enumerate
1481
1482If a reqiest matches anything in the
1483\noun on
1484LogSQLRemhostIgnore
1485\noun default
1486 list, it is discarded.
1487\layout Enumerate
1488
1489Otherwise the request is logged.
1490\layout Standard
1491
1492This means that you can have a series of directives similar to the following:
1493\layout LyX-Code
1494
1495LogSQLRequestAccept *.html *.gif *.jpg
1496\layout LyX-Code
1497
1498LogSQLRequestIgnore statistics.html bluedot.jpg
1499\layout Standard
1500
1501So the first line instructs the module to
1502\series bold
1503only
1504\series default
1505 log files with html, gif and jpg suffixes; requests for
1506\begin_inset Quotes eld
1507\end_inset
1508
1509formail.cgi
1510\begin_inset Quotes erd
1511\end_inset
1512
1513 and
1514\begin_inset Quotes eld
1515\end_inset
1516
1517shopping-cart.pl
1518\begin_inset Quotes erd
1519\end_inset
1520
1521 will never be considered for logging.
1522 (
1523\begin_inset Quotes eld
1524\end_inset
1525
1526LeftArrow.JPG
1527\begin_inset Quotes erd
1528\end_inset
1529
1530 will also never be considered for logging -- remember, the comparison is
1531
1532\series bold
1533case sensitive
1534\series default
1535.) The second line prunes the list further -- you never want to log requests
1536 for those two objects.
1537\layout Standard
1538
1539Tip: if you want to match all the hosts in your domain such as
1540\begin_inset Quotes eld
1541\end_inset
1542
1543host1.corp.foo.com
1544\begin_inset Quotes srd
1545\end_inset
1546
1547 and
1548\begin_inset Quotes eld
1549\end_inset
1550
1551server.dmz.foo.com
1552\begin_inset Quotes srd
1553\end_inset
1554
1555, simply specify:
1556\layout LyX-Code
1557
1558LogSQLRemhostIgnore foo.com
1559\layout Standard
1560
1561Tip: a great way to catch the vast majority of worm-attack requests and
1562 prevent them from being logged is to specify:
1563\layout LyX-Code
1564
1565LogSQLRequestIgnore root.exe cmd.exe default.ida
1566\layout Standard
1567
1568Tip: to prevent the logging of requests for common graphic types, make sure
1569 to put a '.' before the suffix to avoid matches that you didn't intend:
1570\layout LyX-Code
1571
1572LogSQLRequestIgnore .gif .jpg
1573\layout Subsection
1574
1575Advanced logging scenarios
1576\layout Subsubsection
1577
1578Using the module in an ISP environment
1579\layout Standard
1580
1581mod_log_sql has three basic tiers of operation:
1582\layout Enumerate
1583
1584The administrator creates all necessary tables by hand and configures each
1585 Apache VirtualHost by hand.
1586 (
1587\noun on
1588LogSQLCreateTables Off
1589\noun default
1590)
1591\layout Enumerate
1592
1593The module is permitted to create necessary tables on-the-fly, but the administr
1594ator configures each Apache VirtualHost by hand.
1595 (
1596\noun on
1597LogSQLCreateTables On
1598\noun default
1599)
1600\layout Enumerate
1601
1602The module is permitted to create all necessary tables and to make intelligent,
1603 on-the-fly configuration of each VirtualHost.
1604 (
1605\noun on
1606LogSQLMassVirtualHosting On
1607\noun default
1608)
1609\layout Standard
1610
1611Many users are happy to use the module in its most minimal form: they hand-creat
1612e any necessary tables (using
1613\begin_inset Quotes eld
1614\end_inset
1615
1616create_tables.sql
1617\begin_inset Quotes erd
1618\end_inset
1619
1620), and they configure each VirtualHost by hand to suit their needs.
1621 However, some administrators need extra features due to a large and growing
1622 number of VirtualHosts.
1623 The
1624\noun on
1625LogSQLMassVirtualHosting
1626\noun default
1627 directive activates module capabilities that make it far easier to manage
1628 an ISP environment, or any situation characterized by a large and varying
1629 number of virtual servers:
1630\layout Itemize
1631
1632the on-the-fly table creation feature is activated automatically
1633\layout Itemize
1634
1635the transfer log table name is dynamically set from the virtual host's name
1636 (example: a virtual host
1637\begin_inset Quotes eld
1638\end_inset
1639
1640www.grubbybaby.com
1641\begin_inset Quotes erd
1642\end_inset
1643
1644 gets logged to table
1645\begin_inset Quotes eld
1646\end_inset
1647
1648access_www_grubbybaby_com
1649\begin_inset Quotes erd
1650\end_inset
1651
1652)
1653\layout Standard
1654
1655There are numerous benefits.
1656 The admin will not need to create new tables for every new VirtualHost.
1657 (Although the admin will still need to drop the tables of virtual hosts
1658 that are removed.) The admin will not need to set
1659\noun on
1660LogSQLTransferLogTable
1661\noun default
1662 for each virtual host -- it will be configured automatically based on the
1663 host's name.
1664 Because each virtual host will log to its own segregated table, data about
1665 one virtual server will segregate from others; an admin can grant users
1666 access to the tables they need, and they will be unable to view data about
1667 another user's virtual host.
1668\layout Standard
1669
1670In an ISP scenario the admin is likely to have a cluster of many front-end
1671 webservers logging to a back-end database.
1672 mod_log_sql has a feature that permits analysis of how well the web servers
1673 are loadbalancing: the
1674\noun on
1675LogSQLMachineID
1676\noun default
1677 directive.
1678 The administrator uses this directive to assign a unique identifier to
1679 each machine in the web cluster, e.g.
1680
1681\begin_inset Quotes eld
1682\end_inset
1683
1684
1685\noun on
1686LogSQLMachineID
1687\noun default
1688 web01,
1689\begin_inset Quotes erd
1690\end_inset
1691
1692
1693\begin_inset Quotes eld
1694\end_inset
1695
1696
1697\noun on
1698LogSQLMachineID
1699\noun default
1700 web02,
1701\begin_inset Quotes erd
1702\end_inset
1703
1704 etc.
1705 Used in conjunction with the 'M' character in
1706\noun on
1707LogSQLTransferLogFormat
1708\noun default
1709, each entry in the SQL log will include the machine ID of the machine that
1710 created the entry.
1711 This permits the administrator to count the entries made by each particular
1712 machine and thereby analyze the front-end loadbalancing algorithm.
1713\layout Subsubsection
1714
1715
1716\begin_inset LatexCommand \label{secMulTable}
1717
1718\end_inset
1719
1720Logging many-to-one data in separate tables
1721\layout Standard
1722
1723A given HTTP request can have a one-to-many relationship with certain kinds
1724 of data.
1725 For example, a single HTTP request can have 4 cookies, 3 headers and 5
1726
1727\begin_inset Quotes eld
1728\end_inset
1729
1730mod_gzip
1731\begin_inset Quotes erd
1732\end_inset
1733
1734 notes associated with it.
1735 mod_log_sql is capable of logging these relationships due to the elegance
1736 of SQL relational data.
1737\layout Standard
1738
1739You already have a single table containing access requests.
1740 One of the columns in that table is 'id' which is intended to contain the
1741 unique request ID supplied by the standard Apache module mod_unique_id
1742 -- all you need to do is compile in that module and employ the
1743\noun on
1744LogSQLTransferLogFormat
1745\noun default
1746 character 'I'.
1747 Thereafter, each request gets a unique ID that can be thought of as a primary
1748 key within the database, useful for joining multiple tables.
1749 So let's envision several new tables: a notes table, a cookies table, and
1750 a table for inbound and outbound headers.
1751
1752\layout Standard
1753
1754
1755\begin_inset Float table
1756wide false
1757collapsed false
1758
1759\layout Caption
1760
1761
1762\begin_inset LatexCommand \label{tblAcc}
1763
1764\end_inset
1765
1766access_log
1767\layout Standard
1768\align center
1769
1770\begin_inset Tabular
1771<lyxtabular version="3" rows="2" columns="6">
1772<features>
1773<column alignment="left" valignment="top" leftline="true" width="0pt">
1774<column alignment="left" valignment="top" leftline="true" width="0pt">
1775<column alignment="left" valignment="top" leftline="true" rightline="true" width="0pt">
1776<column alignment="left" valignment="top" rightline="true" width="0pt">
1777<column alignment="left" valignment="top" rightline="true" width="0pt">
1778<column alignment="left" valignment="top" rightline="true" width="0pt">
1779<row topline="true" bottomline="true">
1780<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
1781\begin_inset Text
1782
1783\layout Standard
1784
1785id
1786\end_inset
1787</cell>
1788<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
1789\begin_inset Text
1790
1791\layout Standard
1792
1793remote_host
1794\end_inset
1795</cell>
1796<cell alignment="center" valignment="top" topline="true" leftline="true" rightline="true" usebox="none">
1797\begin_inset Text
1798
1799\layout Standard
1800
1801request_uri
1802\end_inset
1803</cell>
1804<cell alignment="center" valignment="top" topline="true" leftline="true" rightline="true" usebox="none">
1805\begin_inset Text
1806
1807\layout Standard
1808
1809time_stamp
1810\end_inset
1811</cell>
1812<cell alignment="center" valignment="top" topline="true" leftline="true" rightline="true" usebox="none">
1813\begin_inset Text
1814
1815\layout Standard
1816
1817status
1818\end_inset
1819</cell>
1820<cell alignment="center" valignment="top" topline="true" leftline="true" rightline="true" usebox="none">
1821\begin_inset Text
1822
1823\layout Standard
1824
1825bytes_sent
1826\end_inset
1827</cell>
1828</row>
1829<row topline="true" bottomline="true">
1830<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
1831\begin_inset Text
1832
1833\layout Standard
1834
1835PPIDskBRH30AAGPtAsg
1836\end_inset
1837</cell>
1838<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
1839\begin_inset Text
1840
1841\layout Standard
1842
1843zerberus.aiacs.net
1844\end_inset
1845</cell>
1846<cell alignment="center" valignment="top" topline="true" leftline="true" rightline="true" usebox="none">
1847\begin_inset Text
1848
1849\layout Standard
1850
1851/mod_log_sql/index.html
1852\end_inset
1853</cell>
1854<cell alignment="center" valignment="top" topline="true" leftline="true" rightline="true" usebox="none">
1855\begin_inset Text
1856
1857\layout Standard
1858
18591022493617
1860\end_inset
1861</cell>
1862<cell alignment="center" valignment="top" topline="true" leftline="true" rightline="true" usebox="none">
1863\begin_inset Text
1864
1865\layout Standard
1866
1867200
1868\end_inset
1869</cell>
1870<cell alignment="center" valignment="top" topline="true" leftline="true" rightline="true" usebox="none">
1871\begin_inset Text
1872
1873\layout Standard
1874
18752215
1876\end_inset
1877</cell>
1878</row>
1879</lyxtabular>
1880
1881\end_inset
1882
1883
1884\end_inset
1885
1886
1887\begin_inset Float table
1888wide false
1889collapsed false
1890
1891\layout Caption
1892
1893
1894\begin_inset LatexCommand \label{tblNotes}
1895
1896\end_inset
1897
1898notes_log
1899\layout Standard
1900\align center
1901
1902\begin_inset Tabular
1903<lyxtabular version="3" rows="3" columns="3">
1904<features>
1905<column alignment="left" valignment="top" leftline="true" width="0pt">
1906<column alignment="left" valignment="top" leftline="true" width="0pt">
1907<column alignment="left" valignment="top" leftline="true" rightline="true" width="0pt">
1908<row topline="true" bottomline="true">
1909<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
1910\begin_inset Text
1911
1912\layout Standard
1913
1914id
1915\end_inset
1916</cell>
1917<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
1918\begin_inset Text
1919
1920\layout Standard
1921
1922item
1923\end_inset
1924</cell>
1925<cell alignment="center" valignment="top" topline="true" leftline="true" rightline="true" usebox="none">
1926\begin_inset Text
1927
1928\layout Standard
1929
1930val
1931\end_inset
1932</cell>
1933</row>
1934<row topline="true">
1935<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
1936\begin_inset Text
1937
1938\layout Standard
1939
1940PPIDskBRH30AAGPtAsg
1941\end_inset
1942</cell>
1943<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
1944\begin_inset Text
1945
1946\layout Standard
1947
1948mod_gzip_result
1949\end_inset
1950</cell>
1951<cell alignment="center" valignment="top" topline="true" leftline="true" rightline="true" usebox="none">
1952\begin_inset Text
1953
1954\layout Standard
1955
1956OK
1957\end_inset
1958</cell>
1959</row>
1960<row topline="true" bottomline="true">
1961<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
1962\begin_inset Text
1963
1964\layout Standard
1965
1966PPIDskBRH30AAGPtAsg
1967\end_inset
1968</cell>
1969<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
1970\begin_inset Text
1971
1972\layout Standard
1973
1974mod_gzip_compression_ratio
1975\end_inset
1976</cell>
1977<cell alignment="center" valignment="top" topline="true" leftline="true" rightline="true" usebox="none">
1978\begin_inset Text
1979
1980\layout Standard
1981
198269
1983\end_inset
1984</cell>
1985</row>
1986</lyxtabular>
1987
1988\end_inset
1989
1990
1991\end_inset
1992
1993
1994\layout Standard
1995
1996
1997\begin_inset Float table
1998wide false
1999collapsed false
2000
2001\layout Caption
2002
2003
2004\begin_inset LatexCommand \label{tblHdr}
2005
2006\end_inset
2007
2008headers_log
2009\layout Standard
2010\align center
2011
2012\begin_inset Tabular
2013<lyxtabular version="3" rows="5" columns="3">
2014<features>
2015<column alignment="left" valignment="top" leftline="true" width="0pt">
2016<column alignment="left" valignment="top" leftline="true" width="0pt">
2017<column alignment="left" valignment="top" leftline="true" rightline="true" width="0pt">
2018<row topline="true" bottomline="true">
2019<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
2020\begin_inset Text
2021
2022\layout Standard
2023
2024id
2025\end_inset
2026</cell>
2027<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
2028\begin_inset Text
2029
2030\layout Standard
2031
2032item
2033\end_inset
2034</cell>
2035<cell alignment="center" valignment="top" topline="true" leftline="true" rightline="true" usebox="none">
2036\begin_inset Text
2037
2038\layout Standard
2039
2040val
2041\end_inset
2042</cell>
2043</row>
2044<row topline="true">
2045<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
2046\begin_inset Text
2047
2048\layout Standard
2049
2050PPIDskBRH30AAGPtAsg
2051\end_inset
2052</cell>
2053<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
2054\begin_inset Text
2055
2056\layout Standard
2057
2058Content-Type
2059\end_inset
2060</cell>
2061<cell alignment="center" valignment="top" topline="true" leftline="true" rightline="true" usebox="none">
2062\begin_inset Text
2063
2064\layout Standard
2065
2066text/html
2067\end_inset
2068</cell>
2069</row>
2070<row topline="true">
2071<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
2072\begin_inset Text
2073
2074\layout Standard
2075
2076PPIDskBRH30AAGPtAsg
2077\end_inset
2078</cell>
2079<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
2080\begin_inset Text
2081
2082\layout Standard
2083
2084Accept-Encoding
2085\end_inset
2086</cell>
2087<cell alignment="center" valignment="top" topline="true" leftline="true" rightline="true" usebox="none">
2088\begin_inset Text
2089
2090\layout Standard
2091
2092gzip, deflate
2093\end_inset
2094</cell>
2095</row>
2096<row topline="true">
2097<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
2098\begin_inset Text
2099
2100\layout Standard
2101
2102PPIDskBRH30AAGPtAsg
2103\end_inset
2104</cell>
2105<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
2106\begin_inset Text
2107
2108\layout Standard
2109
2110Expires
2111\end_inset
2112</cell>
2113<cell alignment="center" valignment="top" topline="true" leftline="true" rightline="true" usebox="none">
2114\begin_inset Text
2115
2116\layout Standard
2117
2118Tue, 28 May 2002 10:00:18 GMT
2119\end_inset
2120</cell>
2121</row>
2122<row topline="true" bottomline="true">
2123<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
2124\begin_inset Text
2125
2126\layout Standard
2127
2128PPIDskBRH30AAGPtAsg
2129\end_inset
2130</cell>
2131<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
2132\begin_inset Text
2133
2134\layout Standard
2135
2136Cache-Control
2137\end_inset
2138</cell>
2139<cell alignment="center" valignment="top" topline="true" leftline="true" rightline="true" usebox="none">
2140\begin_inset Text
2141
2142\layout Standard
2143
2144max-age=86400
2145\end_inset
2146</cell>
2147</row>
2148</lyxtabular>
2149
2150\end_inset
2151
2152
2153\end_inset
2154
2155
2156\layout Standard
2157
2158We have a certain request, and its unique ID is
2159\begin_inset Quotes eld
2160\end_inset
2161
2162PPIDskBRH30AAGPtAsg
2163\begin_inset Quotes erd
2164\end_inset
2165
2166.
2167 Within each separate table will be multiple entries with that request ID:
2168 several cookie entries, several header entries, etc.
2169 As you can see in tables
2170\begin_inset LatexCommand \ref{tblAcc}
2171
2172\end_inset
2173
2174,
2175\begin_inset LatexCommand \ref{tblNotes}
2176
2177\end_inset
2178
2179 and
2180\begin_inset LatexCommand \ref{tblHdr}
2181
2182\end_inset
2183
2184, you have a one-to-many relationship for request PPIDskBRH30AAGPtAsg: that
2185 one access has two associated notes and four associated headers.
2186 You can extract this data easily using the power of SQL's
2187\begin_inset Quotes eld
2188\end_inset
2189
2190select
2191\begin_inset Quotes erd
2192\end_inset
2193
2194 statement and table joins.
2195 To see the notes associated with a particular request:
2196\layout LyX-Code
2197
2198select a.remote_host, a.request_uri, n.item, n.val from access_log a, notes_log
2199 n
2200\layout LyX-Code
2201
2202where a.id=n.id and a.id='PPIDskBRH30AAGPtAsg';
2203\layout LyX-Code
2204
2205\layout Standard
2206\align center
2207
2208\begin_inset Tabular
2209<lyxtabular version="3" rows="3" columns="4">
2210<features>
2211<column alignment="left" valignment="top" leftline="true" width="0pt">
2212<column alignment="left" valignment="top" leftline="true" width="0pt">
2213<column alignment="left" valignment="top" leftline="true" width="0pt">
2214<column alignment="left" valignment="top" leftline="true" rightline="true" width="0pt">
2215<row topline="true" bottomline="true">
2216<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
2217\begin_inset Text
2218
2219\layout Standard
2220
2221remote_host
2222\end_inset
2223</cell>
2224<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
2225\begin_inset Text
2226
2227\layout Standard
2228
2229request_uri
2230\end_inset
2231</cell>
2232<cell alignment="center" valignment="top" topline="true" leftline="true" rightline="true" usebox="none">
2233\begin_inset Text
2234
2235\layout Standard
2236
2237item
2238\end_inset
2239</cell>
2240<cell alignment="center" valignment="top" topline="true" leftline="true" rightline="true" usebox="none">
2241\begin_inset Text
2242
2243\layout Standard
2244
2245val
2246\end_inset
2247</cell>
2248</row>
2249<row topline="true">
2250<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
2251\begin_inset Text
2252
2253\layout Standard
2254
2255zerberus.aiacs.net
2256\end_inset
2257</cell>
2258<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
2259\begin_inset Text
2260
2261\layout Standard
2262
2263/mod_log_sql/index.html
2264\end_inset
2265</cell>
2266<cell alignment="center" valignment="top" topline="true" leftline="true" rightline="true" usebox="none">
2267\begin_inset Text
2268
2269\layout Standard
2270
2271mod_gzip_result
2272\end_inset
2273</cell>
2274<cell alignment="center" valignment="top" topline="true" leftline="true" rightline="true" usebox="none">
2275\begin_inset Text
2276
2277\layout Standard
2278
2279OK
2280\end_inset
2281</cell>
2282</row>
2283<row topline="true" bottomline="true">
2284<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
2285\begin_inset Text
2286
2287\layout Standard
2288
2289zerberus.aiacs.net
2290\end_inset
2291</cell>
2292<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
2293\begin_inset Text
2294
2295\layout Standard
2296
2297/mod_log_sql/index.html
2298\end_inset
2299</cell>
2300<cell alignment="center" valignment="top" topline="true" leftline="true" rightline="true" usebox="none">
2301\begin_inset Text
2302
2303\layout Standard
2304
2305mod_gzip_compression_ratio
2306\end_inset
2307</cell>
2308<cell alignment="center" valignment="top" topline="true" leftline="true" rightline="true" usebox="none">
2309\begin_inset Text
2310
2311\layout Standard
2312
231369
2314\end_inset
2315</cell>
2316</row>
2317</lyxtabular>
2318
2319\end_inset
2320
2321
2322\layout LyX-Code
2323
2324\layout Standard
2325
2326Naturally you can craft similar statements for the outboud headers, inbound
2327 headers and cookies, all of which can live in separate tables.
2328 Your statements are limited in power only by your skill with SQL.
2329\layout Standard
2330
2331In order to use this capability of mod_log_sql, you must do several things:
2332\layout Itemize
2333
2334Compile mod_unique_id into Apache (statically or as a DSO).
2335 mod_log_sql employs the unique request ID that mod_unique_id provides in
2336 order to key between the separate tables.
2337 You can still log the data without mod_unqiue_id, but it will be completely
2338 uncorrelated and you will have no way to discern any meaning.
2339\layout Itemize
2340
2341Create the appropriate tables.
2342 This will be done for you if you permit mod_log_sql to create its own tables
2343 using
2344\noun on
2345LogSQLCreateTables On
2346\noun default
2347, or if you use the enclosed
2348\begin_inset Quotes eld
2349\end_inset
2350
2351create_tables.sql
2352\begin_inset Quotes erd
2353\end_inset
2354
2355 script.
2356\layout Itemize
2357
2358Create a SQL index on the
2359\begin_inset Quotes eld
2360\end_inset
2361
2362id
2363\begin_inset Quotes erd
2364\end_inset
2365
2366 column.
2367 Without this index, table joins will be deathly slow.
2368 I recommend you consult the MySQL documentation on the proper way to create
2369 a column index if you are not familiar with this operation.
2370\layout Itemize
2371
2372Within each appropriate VirtualHost stanza, use the
2373\noun on
2374 LogSQLWhich*
2375\noun default
2376 and
2377\noun on
2378LogSQL*LogTable
2379\noun default
2380 directives to tell the module what and where to log the data.
2381 In the following example, I have overridden the name for the notes table
2382 whereas I have left the other table names at their defaults.
2383 I have then specified the cookies, headers and notes that interest me.
2384 (And as you can see, these directives do not require me to add any characters
2385 to
2386\noun on
2387LogSQLTransferLogTable.)
2388\layout LyX-Code
2389
2390<VirtualHost 216.231.36.128>
2391\layout LyX-Code
2392
2393 (snip)
2394\layout LyX-Code
2395
2396 LogSQLNotesLogTable notestable
2397\layout LyX-Code
2398
2399 LogSQLWhichCookies bluecookie redcookie greencookie
2400\layout LyX-Code
2401
2402 LogSQLWhichNotes mod_gzip_result mod_gzip_compression_ratio
2403\layout LyX-Code
2404
2405 LogSQLWhichHeadersOut Expires Content-Type Cache-Control
2406\layout LyX-Code
2407
2408 LogSQLWhichHeadersIn UserAgent Accept-Encoding Host
2409\layout LyX-Code
2410
2411 (snip)
2412\layout LyX-Code
2413
2414</VirtualHost>
2415\layout Subsubsection
2416
2417Using the same database for production and test
2418\layout Standard
2419
2420Although suboptimal, it is not uncommon to use the same backend database
2421 for the
2422\begin_inset Quotes eld
2423\end_inset
2424
2425production
2426\begin_inset Quotes erd
2427\end_inset
2428
2429 webservers as well as the
2430\begin_inset Quotes eld
2431\end_inset
2432
2433test
2434\begin_inset Quotes erd
2435\end_inset
2436
2437 webservers (budgetary constraints, rackspace limits, etc.).
2438 Furthermore, an administrator in this situation may be unable to use
2439\noun on
2440LogSQLRemhostIgnore
2441\noun default
2442to exclude requests from the test servers -- perhaps the generated entries
2443 are genuinely useful for analytical or QA purposes, but their value after
2444 analysis is minimal.
2445\layout Standard
2446
2447It is wasteful and potentially confusing to permit this internal test data
2448 to clutter the database, and a solution to the problem is the proper use
2449 of the
2450\noun on
2451LogSQLMachineID
2452\noun default
2453directive.
2454 Assume a scenario where the production webservers have IDs like
2455\begin_inset Quotes eld
2456\end_inset
2457
2458web01,
2459\begin_inset Quotes erd
2460\end_inset
2461
2462
2463\begin_inset Quotes eld
2464\end_inset
2465
2466web02,
2467\begin_inset Quotes erd
2468\end_inset
2469
2470 and so on -- and the test webservers have IDs like
2471\begin_inset Quotes eld
2472\end_inset
2473
2474test01,
2475\begin_inset Quotes erd
2476\end_inset
2477
2478
2479\begin_inset Quotes eld
2480\end_inset
2481
2482test02,
2483\begin_inset Quotes erd
2484\end_inset
2485
2486 etc.
2487 Because entries in the log database are distinguished by their source machine,
2488 an administrator may purge unneeded test data from the access log as follows:
2489\layout LyX-Code
2490
2491delete from access_log where machine_id like 'test%';
2492\layout Subsubsection
2493
2494
2495\begin_inset LatexCommand \label{sub:DelayedIns}
2496
2497\end_inset
2498
2499Optimizing for a busy database
2500\layout Standard
2501
2502A busy MySQL database will have SELECT statements running concurrently with
2503 INSERT and UPDATE statements.
2504 A long-running SELECT can in certain circumstances block INSERTs and therefore
2505 block mod_log_sql.
2506 A workaround is to compile mod_log_sql for
2507\begin_inset Quotes eld
2508\end_inset
2509
2510delayed inserts,
2511\begin_inset Quotes erd
2512\end_inset
2513
2514 which are described as follows in the MySQL documentation:
2515\layout Quote
2516
2517The DELAYED option for the INSERT statement is a MySQL-specific option that
2518 is very useful if you have clients that can't wait for the INSERT to complete.
2519 This is a common problem when you use MySQL for logging and you also periodical
2520ly run SELECT and UPDATE statements that take a long time to complete.
2521 DELAYED was introduced in MySQL Version 3.22.15.
2522 It is a MySQL extension to ANSI SQL92.
2523\layout Quote
2524
2525INSERT DELAYED only works with ISAM and MyISAM tables.
2526 Note that as MyISAM tables supports concurrent SELECT and INSERT, if there
2527 is no free blocks in the middle of the data file, you very seldom need
2528 to use INSERT DELAYED with MyISAM.
2529
2530\layout Quote
2531
2532When you use INSERT DELAYED, the client will get an OK at once and the row
2533 will be inserted when the table is not in use by any other thread.
2534\layout Quote
2535
2536Another major benefit of using INSERT DELAYED is that inserts from many
2537 clients are bundled together and written in one block.
2538 This is much faster than doing many separate inserts.
2539
2540\layout Standard
2541
2542The general disadvantages of delayed inserts are:
2543\layout Enumerate
2544
2545The queued rows are only stored in memory until they are inserted into the
2546 table.
2547 If mysqld dies unexpectedly, any queued rows that weren't written to disk
2548 are lost.
2549\layout Enumerate
2550
2551There is additional overhead for the server to handle a separate thread
2552 for each table on which you use INSERT DELAYED.
2553\layout Standard
2554
2555
2556\series bold
2557The MySQL documentation concludes,
2558\begin_inset Quotes eld
2559\end_inset
2560
2561This means that you should only use INSERT DELAYED when you are really sure
2562 you need it!
2563\begin_inset Quotes erd
2564\end_inset
2565
2566 Furthermore, the current state of error return from a failed INSERT DELAYED
2567 seems to be in flux, and may behave in unpredictable ways between different
2568 MySQL versions.
2569 See section
2570\begin_inset LatexCommand \ref{sub:DelayedInsFAQ}
2571
2572\end_inset
2573
2574 in the FAQ -- you have been warned.
2575\layout Standard
2576
2577If you are experiencing issues which could be solved by delayed inserts,
2578 uncomment the #MYSQLDELAYED line in the Makefile by removing the # that
2579 is in front of it.
2580 Recompile and reinstall your module.
2581 All regular INSERT statements are now INSERT DELAYED, and you should see
2582 no more blocking of the module.
2583\layout Subsection
2584
2585
2586\begin_inset LatexCommand \label{sec:ConfRef}
2587
2588\end_inset
2589
2590Configuration directive reference
2591\layout Standard
2592
2593It is imperative that you understand which directives are used
2594\emph on
2595only once
2596\emph default
2597in the main server config, and which are used inside VirtualHost stanzas
2598 and therefore multiple times within httpd.conf.
2599 The
2600\begin_inset Quotes eld
2601\end_inset
2602
2603context
2604\begin_inset Quotes srd
2605\end_inset
2606
2607 listed with each entry informs you of this.
2608\layout Subsubsection
2609
2610LogSQLCookieLogTable
2611\layout LyX-Code
2612
2613Syntax: LogSQLCookieLogTable table-name
2614\layout LyX-Code
2615
2616Example: LogSQLCookieLogTable cookie_log
2617\layout LyX-Code
2618
2619Default: cookies
2620\layout LyX-Code
2621
2622Context: virtual host
2623\layout Standard
2624
2625Defines which table is used for logging of cookies.
2626 Working in conjunction with
2627\noun on
2628LogSQLWhichCookies
2629\noun default
2630, you can log many of each request's associated cookies to a separate table.
2631 For meaningful data retrieval the cookie table is keyed to the access table
2632 by the unique request ID supplied by the standard Apache module mod_unique_id.
2633\layout Standard
2634
2635Note that you must create the table (see create-tables.sql, included in the
2636 package), or
2637\noun on
2638LogSQLCreateTables
2639\noun default
2640 must be set to
2641\begin_inset Quotes eld
2642\end_inset
2643
2644on
2645\begin_inset Quotes erd
2646\end_inset
2647
2648.
2649\layout Subsubsection
2650
2651LogSQLCreateTables
2652\layout LyX-Code
2653
2654Syntax: LogSQLCreateTables flag
2655\layout LyX-Code
2656
2657Example: LogSQLCreateTables On
2658\layout LyX-Code
2659
2660Default: Off
2661\layout LyX-Code
2662
2663Context: main server config
2664\layout Standard
2665
2666mod_log_sql has the ability to create its tables on-the-fly.
2667 The advantage to this is convenience: you don't have to execute any SQL
2668 by hand to prepare the table.
2669 This is especially helpful for people with lots of virtual hosts (who should
2670 also see the
2671\noun on
2672LogSQLMassVirtualHosting
2673\noun default
2674 directive).
2675\layout Standard
2676
2677There is a slight disadvantage: if you wish to activate this feature, then
2678 the userid specified in
2679\noun on
2680LogSQLLoginInfo
2681\noun default
2682 must have CREATE privileges on the database.
2683 In an absolutely paranoid, locked-down situation you may only want to grant
2684 your mod_log_sql user INSERT privileges on the database; in that situation
2685 you are unable to take advantage of
2686\noun on
2687LogSQLCreateTables
2688\noun default
2689.
2690 But most people -- even the very security-conscious -- will find that granting
2691 CREATE on the logging database is reasonable.
2692\layout Standard
2693
2694This is defined only once in the httpd.conf file.
2695\layout Subsubsection
2696
2697LogSQLDatabase
2698\layout LyX-Code
2699
2700
2701\series bold
2702MANDATORY
2703\layout LyX-Code
2704
2705Syntax: LogSQLDatabase database
2706\layout LyX-Code
2707
2708Example: LogSQLDatabase loggingdb
2709\layout LyX-Code
2710
2711Context: main server config
2712\layout Standard
2713
2714Defines the database that is used for logging.
2715
2716\begin_inset Quotes eld
2717\end_inset
2718
2719database
2720\begin_inset Quotes erd
2721\end_inset
2722
2723 must be a valid db on the MySQL host defined in
2724\noun on
2725LogSQLLoginInfo
2726\noun default
2727.
2728
2729\layout Standard
2730
2731This is defined only once in the httpd.conf file.
2732\layout Subsubsection
2733
2734LogSQLForcePreserve
2735\layout LyX-Code
2736
2737Syntax: LogSQLForcePreserve Flag
2738\layout LyX-Code
2739
2740Example: LogSQLPreserveFile on
2741\layout LyX-Code
2742
2743Default: off
2744\layout LyX-Code
2745
2746Context: main server config
2747\layout Standard
2748
2749You may need to perform debugging on your database and specifically want
2750 mod_log_sql to make no attempts to log to it.
2751 This directive instructs the module to send all its log entries directly
2752 to the preserve file and to make no database INSERT attempts.
2753\layout Standard
2754
2755This is presumably a directive for temporary use only; it could be dangerous
2756 if you set it and forget it, as all your entries will simply pile up in
2757 the preserve file.
2758\layout Standard
2759
2760This is defined only once in the httpd.conf file.
2761\layout Subsubsection
2762
2763LogSQLHeadersInLogTable
2764\layout LyX-Code
2765
2766Syntax: LogSQLHeadersInLogTable table-name
2767\layout LyX-Code
2768
2769Example: LogSQLHeadersInLogTable headers
2770\layout LyX-Code
2771
2772Default: headers_in
2773\layout LyX-Code
2774
2775Context: virtual host
2776\layout Standard
2777
2778Defines which table is used for logging of inbound headers.
2779 Working in conjunction with
2780\noun on
2781LogSQLWhichHeadersIn
2782\noun default
2783, you can log many of each request's associated headers to a separate table.
2784 For meaningful data retrieval the headers table is keyed to the access
2785 table by the unique request ID supplied by the standard Apache module mod_uniqu
2786e_id.
2787\layout Standard
2788
2789Note that you must create the table (see create-tables.sql, included in the
2790 package), or
2791\noun on
2792LogSQLCreateTables
2793\noun default
2794 must be set to
2795\begin_inset Quotes eld
2796\end_inset
2797
2798on
2799\begin_inset Quotes erd
2800\end_inset
2801
2802.
2803\layout Subsubsection
2804
2805LogSQLHeadersOutLogTable
2806\layout LyX-Code
2807
2808Syntax: LogSQLHeadersOutLogTable table-name
2809\layout LyX-Code
2810
2811Example: LogSQLHeadersOutLogTable headers
2812\layout LyX-Code
2813
2814Default: headers_out
2815\layout LyX-Code
2816
2817Context: virtual host
2818\layout Standard
2819
2820Defines which table is used for logging of outbound headers.
2821 Working in conjunction with
2822\noun on
2823LogSQLWhichHeadersOut
2824\noun default
2825, you can log many of each request's associated headers to a separate table.
2826 For meaningful data retrieval the headers table is keyed to the access
2827 table by the unique request ID supplied by the standard Apache module mod_uniqu
2828e_id.
2829\layout Standard
2830
2831Note that you must create the table (see create-tables.sql, included in the
2832 package), or
2833\noun on
2834LogSQLCreateTables
2835\noun default
2836 must be set to
2837\begin_inset Quotes eld
2838\end_inset
2839
2840on
2841\begin_inset Quotes erd
2842\end_inset
2843
2844.
2845\layout Subsubsection
2846
2847LogSQLLoginInfo
2848\layout LyX-Code
2849
2850
2851\series bold
2852MANDATORY
2853\series default
2854
2855\layout LyX-Code
2856
2857Syntax: LogSQLLoginInfo host user password
2858\layout LyX-Code
2859
2860Example: LogSQLLoginInfo foobar.baz.com logwriter passw0rd
2861\layout LyX-Code
2862
2863Context: main server config
2864\layout Standard
2865
2866Defines the general parameters of the MySQL host to which you will be logging.
2867
2868\begin_inset Quotes eld
2869\end_inset
2870
2871host
2872\begin_inset Quotes erd
2873\end_inset
2874
2875 is the hostname or IP address of the MySQL machine, and is simply
2876\begin_inset Quotes eld
2877\end_inset
2878
2879localhost
2880\begin_inset Quotes erd
2881\end_inset
2882
2883 if the database lives on the same machine as Apache.
2884
2885\begin_inset Quotes eld
2886\end_inset
2887
2888user
2889\begin_inset Quotes erd
2890\end_inset
2891
2892 is the MySQL userid (not a Unix userid!) with INSERT privileges on the
2893 table defined in
2894\noun on
2895LogSQLTransferLogTable
2896\noun default
2897.
2898
2899\begin_inset Quotes eld
2900\end_inset
2901
2902password
2903\begin_inset Quotes erd
2904\end_inset
2905
2906 is that user's password.
2907
2908\layout Standard
2909
2910This is defined only once in the httpd.conf file.
2911\layout Subsubsection
2912
2913LogSQLMachineID
2914\layout LyX-Code
2915
2916Syntax: LogSQLMachineID somename
2917\layout LyX-Code
2918
2919Example: LogSQLMachineID web01
2920\layout LyX-Code
2921
2922Context: main server config
2923\layout Standard
2924
2925If you have a farm of webservers then you may wish to know which particular
2926 machine made each entry; this is useful for analyzing your loadbalancing
2927 methodology.
2928
2929\noun on
2930LogSQLMachineID
2931\noun default
2932 permits you to distinguish each machine's entries if you assign each machine
2933 its own
2934\noun on
2935LogSQLMachineID
2936\noun default
2937: for example, the first webserver gets
2938\begin_inset Quotes eld
2939\end_inset
2940
2941
2942\noun on
2943LogSQLMachineID
2944\noun default
2945 web01,
2946\begin_inset Quotes erd
2947\end_inset
2948
2949 the second gets
2950\begin_inset Quotes eld
2951\end_inset
2952
2953
2954\noun on
2955LogSQLMachineID
2956\noun default
2957 web02,
2958\begin_inset Quotes erd
2959\end_inset
2960
2961 etc.
2962\layout Standard
2963
2964This is defined only once in the httpd.conf file.
2965\layout Subsubsection
2966
2967LogSQLMassVirtualHosting
2968\layout LyX-Code
2969
2970Syntax: LogSQLMassVirtualHosting flag
2971\layout LyX-Code
2972
2973Example: LogSQLMassVirtualHosting On
2974\layout LyX-Code
2975
2976Default: Off
2977\layout LyX-Code
2978
2979Context: main server config
2980\layout Standard
2981
2982If you administer a site hosting many, many virtual hosts then this option
2983 will appeal to you.
2984 If you turn on
2985\noun on
2986LogSQLMassVirtualHosting
2987\noun default
2988 then several things happen:
2989\layout Itemize
2990
2991the on-the-fly table creation feature is activated automatically
2992\layout Itemize
2993
2994the transfer log table name is dynamically set from the virtual host's name
2995 after stripping out SQL-unfriendly characters (example: a virtual host
2996 www.grubbybaby.com gets logged to table access_www_grubbybaby_com)
2997\layout Itemize
2998
2999which, in turn, means that each virtual host logs to its own segregated
3000 table.
3001 Because there is no data shared between virtual servers you can grant your
3002 users access to the tables they need; they will be unable to view others'
3003 data.
3004\layout Standard
3005
3006This is a huge boost in convenience for sites with many virtual servers.
3007 Activating
3008\noun on
3009LogSQLMassVirtualHosting
3010\noun default
3011 obviates the need to create every virtual server's table and provides more
3012 granular security possibilities.
3013\layout Standard
3014
3015You are advised to investigate the use of Apache's
3016\noun on
3017UseCanonicalName On
3018\noun default
3019directive with this directive in order to ensure that each virtual host
3020 maps to one table namespace.
3021\layout Standard
3022
3023This is defined only once in the httpd.conf file.
3024
3025\layout Subsubsection
3026
3027LogSQLNotesLogTable
3028\layout LyX-Code
3029
3030Syntax: LogSQLNotesLogTable table-name
3031\layout LyX-Code
3032
3033Example: LogSQLNotesLogTable notes_log
3034\layout LyX-Code
3035
3036Default: notes
3037\layout LyX-Code
3038
3039Context: virtual host
3040\layout Standard
3041
3042Defines which table is used for logging of notes.
3043 Working in conjunction with
3044\noun on
3045LogSQLWhichNotes
3046\noun default
3047, you can log many of each request's associated notes to a separate table.
3048 For meaningful data retrieval the notes table is keyed to the access table
3049 by the unique request ID supplied by the standard Apache module mod_unique_id.
3050\layout Standard
3051
3052Note that you must create the table (see create-tables.sql, included in the
3053 package), or
3054\noun on
3055LogSQLCreateTables
3056\noun default
3057 must be set to ``on''.
3058
3059\layout Subsubsection
3060
3061LogSQLPreserveFile
3062\layout LyX-Code
3063
3064Syntax: LogSQLPreserveFile filename
3065\layout LyX-Code
3066
3067Example: LogSQLPreserveFile offline-preserve
3068\layout LyX-Code
3069
3070Default: /tmp/sql-preserve
3071\layout LyX-Code
3072
3073Context: virtual host
3074\layout Standard
3075
3076mod_log_sql writes queries to this local preserve file in the event that
3077 it cannot reach the database, and thus ensures that your high-availability
3078 web frontend does not lose logs during a temporary database outage.
3079 This could happen for a number of reasons: the database goes offline, the
3080 network breaks, etc.
3081 You will not lose entries since the module has this backup.
3082 The file consists of a series of SQL statements that can be imported into
3083 your database at your convenience; furthermore, because the SQL queries
3084 contain the access timestamps you do not need to worry about out-of-order
3085 data after the import, which is done in a simple manner:
3086\layout LyX-Code
3087
3088# mysql -uadminuser -p mydbname < /tmp/sql-preserve
3089\layout Standard
3090
3091If you do not define
3092\noun on
3093LogSQLPreserveFile
3094\noun default
3095 then all virtual servers will log to the same default preserve file (/tmp/sql-p
3096reserve).
3097 You can redefine this on a virtual-host basis in order to segregate your
3098 preserve files if you desire.
3099 Note that segregation is not usually necessary, as the SQL statements that
3100 are written to the preserve file already distinguish between different
3101 virtual hosts if you include the 'v' character in your
3102\noun on
3103LogSQLTransferLogFormat
3104\noun default
3105 directive.
3106 It is only necessary to segregate preserve-files by virualhost if you also
3107 segregate access logs by virtualhost.
3108\layout Standard
3109
3110The module will log to Apache's
3111\noun on
3112ErrorLog
3113\noun default
3114 when it notices a database outage, and upon database return.
3115 You will therefore know when the preserve file is being used, although
3116 it is your responsibility to import the file.
3117\layout Standard
3118
3119The file does not need to be created in advance.
3120 It is safe to remove or rename the file without interrupting Apache, as
3121 the module closes the filehandle immediately after completing the write.
3122 The file is created with the user & group ID of the running Apache process
3123 (e.g.
3124 'nobody' on many Linux distributions).
3125\layout Subsubsection
3126
3127LogSQLRemhostIgnore
3128\layout LyX-Code
3129
3130Syntax: LogSQLRemhostIgnore host1 host2 host3 ...
3131 hostN
3132\layout LyX-Code
3133
3134Example: LogSQLRemhostIgnore localnet.com
3135\layout LyX-Code
3136
3137Context: virtual host
3138\layout Standard
3139
3140Lists a series of strings that, if present in the REMOTE_HOST, will cause
3141 that request to
3142\series bold
3143not
3144\series default
3145 be logged.
3146 This directive is useful for cutting down on log clutter when you are certain
3147 that you want to ignore requests from certain hosts, such as your own internal
3148 network machines.
3149 See section
3150\begin_inset LatexCommand \ref{sub:Ignore}
3151
3152\end_inset
3153
3154 for some tips for using this directive.
3155\layout Standard
3156
3157Each string is separated by a space, and no regular expressions or globbing
3158 are allowed.
3159 Each string is evaluated as a substring of the REMOTE_HOST using strstr().
3160 The comparison is case sensitive.
3161\layout Subsubsection
3162
3163LogSQLRequestAccept
3164\layout LyX-Code
3165
3166Syntax: LogSQLRequestAccept req1 req2 req3 ...
3167 reqN
3168\layout LyX-Code
3169
3170Example: LogSQLRequestAccept .html .php .jpg
3171\layout LyX-Code
3172
3173Default: if not specified, all requests are
3174\begin_inset Quotes eld
3175\end_inset
3176
3177accepted
3178\begin_inset Quotes erd
3179\end_inset
3180
3181
3182\layout LyX-Code
3183
3184Context: virtual host
3185\layout Standard
3186
3187Lists a series of strings that, if present in the URI, will permit that
3188 request to be
3189\series bold
3190
3191\series default
3192considered for logging (depending on additional filtering by the
3193\begin_inset Quotes eld
3194\end_inset
3195
3196ignore
3197\begin_inset Quotes erd
3198\end_inset
3199
3200 directives).
3201 Any request that fails to match one of the
3202\noun on
3203LogSQLRequestAccept
3204\noun default
3205entries will be discarded.
3206\layout Standard
3207
3208This directive is useful for cutting down on log clutter when you are certain
3209 that you only want to log certain kinds of requests, and just blanket-ignore
3210 everything else.
3211 See section
3212\begin_inset LatexCommand \ref{sub:Ignore}
3213
3214\end_inset
3215
3216 for some tips for using this directive.
3217\layout Standard
3218
3219Each string is separated by a space, and no regular expressions or globbing
3220 are allowed.
3221 Each string is evaluated as a substring of the URI using strstr().
3222 The comparison is case sensitive.
3223\layout Standard
3224
3225This directive is completely optional.
3226 It is more general than
3227\noun on
3228LogSQLRequestIgnore
3229\noun default
3230and
3231\noun on
3232
3233\noun default
3234is evaluated before
3235\noun on
3236 LogSQLRequestIgnore
3237\noun default
3238.
3239 If this directive is not used,
3240\series bold
3241all
3242\series default
3243 requests are accepted and passed on to the other filtering directives.
3244 Therefore, only use this directive if you have a specific reason to do
3245 so.
3246\layout Subsubsection
3247
3248LogSQLRequestIgnore
3249\layout LyX-Code
3250
3251Syntax: LogSQLRequestIgnore req1 req2 req3 ...
3252 reqN
3253\layout LyX-Code
3254
3255Example: LogSQLRequestIgnore root.exe cmd.exe default.ida favicon.ico
3256\layout LyX-Code
3257
3258Context: virtual host
3259\layout Standard
3260
3261Lists a series of strings that, if present in the URI, will cause that request
3262 to
3263\series bold
3264NOT
3265\series default
3266 be
3267\series bold
3268
3269\series default
3270logged.
3271 This directive is useful for cutting down on log clutter when you are certain
3272 that you want to ignore requests for certain objects.
3273 See section
3274\begin_inset LatexCommand \ref{sub:Ignore}
3275
3276\end_inset
3277
3278 for some tips for using this directive.
3279\layout Standard
3280
3281Each string is separated by a space, and no regular expressions or globbing
3282 are allowed.
3283 Each string is evaluated as a substring of the URI using strstr().
3284 The comparison is case sensitive.
3285\layout Subsubsection
3286
3287LogSQLSocketFile
3288\layout LyX-Code
3289
3290Syntax: LogSQLSocketFile filename
3291\layout LyX-Code
3292
3293Example: LogSQLSocketFile /tmp/mysql.sock
3294\layout LyX-Code
3295
3296Default: /var/lib/mysql/mysql.sock
3297\layout LyX-Code
3298
3299Context: main server config
3300\layout Standard
3301
3302At Apache runtime you can specify the MySQL socket file to use.
3303 Set this once in your main server config to override the default value.
3304 This value is irrelevant if your database resides on a separate machine.
3305\layout Standard
3306
3307mod_log_sql will automatically employ the socket for db communications if
3308 the database resides on the local host.
3309 If the db resides on a separate host the module will automatically use
3310 TCP/IP.
3311 This is a function of the MySQL API and is not user-configurable.
3312\layout Standard
3313
3314This is defined only once in the httpd.conf file.
3315\layout Subsubsection
3316
3317LogSQLTCPPort
3318\layout LyX-Code
3319
3320Syntax: LogSQLTCPPort portnumber
3321\layout LyX-Code
3322
3323Example: LogSQLTCPPort 3309
3324\layout LyX-Code
3325
3326Default: 3306
3327\layout LyX-Code
3328
3329Context: main server config
3330\layout Standard
3331
3332Your database may listen on a different port than the default.
3333 If so, use this directive to instruct the module which port to use.
3334 This directive only applies if the database is on a different machine connected
3335 via TCP/IP.
3336\layout Standard
3337
3338This is defined only once in the httpd.conf file.
3339\layout Subsubsection
3340
3341
3342\begin_inset LatexCommand \label{sub:Frmat}
3343
3344\end_inset
3345
3346LogSQLTransferLogFormat
3347\layout LyX-Code
3348
3349Syntax: LogSQLTransferLogFormat format-string
3350\layout LyX-Code
3351
3352Example: LogSQLTransferLogFormat huSUTv
3353\layout LyX-Code
3354
3355Default: AbHhmRSsTUuv
3356\layout LyX-Code
3357
3358Context: virtual host
3359\layout Standard
3360
3361Each character in the format-string defines an attribute of the request
3362 that you wish to log.
3363 The default logs the information required to create Combined Log Format
3364 logs, plus several extras.
3365 Here is the full list of allowable keys, which sometimes resemble their
3366 Apache counterparts, but do not always:
3367\layout Quote
3368
3369
3370\size footnotesize
3371
3372\begin_inset Tabular
3373<lyxtabular version="3" rows="22" columns="5">
3374<features>
3375<column alignment="center" valignment="top" leftline="true" rightline="true" width="0pt">
3376<column alignment="left" valignment="top" width="0pt">
3377<column alignment="left" valignment="top" leftline="true" rightline="true" width="0pt">
3378<column alignment="left" valignment="top" width="0pt">
3379<column alignment="left" valignment="top" leftline="true" rightline="true" width="0pt">
3380<row topline="true" bottomline="true" endhead="true">
3381<cell alignment="center" valignment="top" topline="true" bottomline="true" leftline="true" usebox="none">
3382\begin_inset Text
3383
3384\layout Standard
3385
3386\end_inset
3387</cell>
3388<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
3389\begin_inset Text
3390
3391\layout Standard
3392
3393
3394\series bold
3395\size footnotesize
3396What is this?
3397\end_inset
3398</cell>
3399<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
3400\begin_inset Text
3401
3402\layout Standard
3403
3404
3405\series bold
3406\size footnotesize
3407Data field
3408\end_inset
3409</cell>
3410<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
3411\begin_inset Text
3412
3413\layout Standard
3414
3415
3416\series bold
3417\size footnotesize
3418Column type
3419\end_inset
3420</cell>
3421<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
3422\begin_inset Text
3423
3424\layout Standard
3425
3426
3427\series bold
3428\size footnotesize
3429Example
3430\end_inset
3431</cell>
3432</row>
3433<row topline="true">
3434<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
3435\begin_inset Text
3436
3437\layout Standard
3438
3439
3440\size footnotesize
3441A
3442\end_inset
3443</cell>
3444<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
3445\begin_inset Text
3446
3447\layout Standard
3448
3449
3450\size footnotesize
3451User agent
3452\end_inset
3453</cell>
3454<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
3455\begin_inset Text
3456
3457\layout Standard
3458
3459
3460\size footnotesize
3461agent
3462\end_inset
3463</cell>
3464<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
3465\begin_inset Text
3466
3467\layout Standard
3468
3469
3470\size footnotesize
3471varchar(255)
3472\end_inset
3473</cell>
3474<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
3475\begin_inset Text
3476
3477\layout Standard
3478
3479
3480\size footnotesize
3481Mozilla/4.0 (compat; MSIE 6.0; Windows)
3482\end_inset
3483</cell>
3484</row>
3485<row topline="true">
3486<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
3487\begin_inset Text
3488
3489\layout Standard
3490
3491a
3492\end_inset
3493</cell>
3494<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
3495\begin_inset Text
3496
3497\layout Standard
3498
3499CGI request arguments
3500\end_inset
3501</cell>
3502<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
3503\begin_inset Text
3504
3505\layout Standard
3506
3507request_args
3508\end_inset
3509</cell>
3510<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
3511\begin_inset Text
3512
3513\layout Standard
3514
3515varchar(255)
3516\end_inset
3517</cell>
3518<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
3519\begin_inset Text
3520
3521\layout Standard
3522
3523user=Smith&cart=1231&item=532
3524\end_inset
3525</cell>
3526</row>
3527<row topline="true">
3528<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
3529\begin_inset Text
3530
3531\layout Standard
3532
3533
3534\size footnotesize
3535b
3536\end_inset
3537</cell>
3538<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
3539\begin_inset Text
3540
3541\layout Standard
3542
3543
3544\size footnotesize
3545Bytes transfered
3546\end_inset
3547</cell>
3548<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
3549\begin_inset Text
3550
3551\layout Standard
3552
3553
3554\size footnotesize
3555bytes_sent
3556\end_inset
3557</cell>
3558<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
3559\begin_inset Text
3560
3561\layout Standard
3562
3563
3564\size footnotesize
3565int unsigned
3566\end_inset
3567</cell>
3568<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
3569\begin_inset Text
3570
3571\layout Standard
3572
3573
3574\size footnotesize
357532561
3576\end_inset
3577</cell>
3578</row>
3579<row topline="true">
3580<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
3581\begin_inset Text
3582
3583\layout Standard
3584
3585
3586\size footnotesize
3587c
3588\end_inset
3589</cell>
3590<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
3591\begin_inset Text
3592
3593\layout Standard
3594
3595
3596\size footnotesize
3597Text of cookie
3598\begin_inset Formula $^{\textrm{1}}$
3599\end_inset
3600
3601
3602\end_inset
3603</cell>
3604<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
3605\begin_inset Text
3606
3607\layout Standard
3608
3609
3610\size footnotesize
3611cookie
3612\end_inset
3613</cell>
3614<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
3615\begin_inset Text
3616
3617\layout Standard
3618
3619
3620\size footnotesize
3621varchar(255)
3622\end_inset
3623</cell>
3624<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
3625\begin_inset Text
3626
3627\layout Standard
3628
3629
3630\size footnotesize
3631Apache=sdyn.fooonline.net.1300102700823
3632\end_inset
3633</cell>
3634</row>
3635<row topline="true">
3636<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
3637\begin_inset Text
3638
3639\layout Standard
3640
3641
3642\size footnotesize
3643H
3644\end_inset
3645</cell>
3646<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
3647\begin_inset Text
3648
3649\layout Standard
3650
3651
3652\size footnotesize
3653HTTP request protocol
3654\end_inset
3655</cell>
3656<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
3657\begin_inset Text
3658
3659\layout Standard
3660
3661
3662\size footnotesize
3663request_protocol
3664\end_inset
3665</cell>
3666<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
3667\begin_inset Text
3668
3669\layout Standard
3670
3671
3672\size footnotesize
3673varchar(10)
3674\end_inset
3675</cell>
3676<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
3677\begin_inset Text
3678
3679\layout Standard
3680
3681
3682\size footnotesize
3683HTTP/1.1
3684\end_inset
3685</cell>
3686</row>
3687<row topline="true">
3688<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
3689\begin_inset Text
3690
3691\layout Standard
3692
3693
3694\size footnotesize
3695h
3696\end_inset
3697</cell>
3698<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
3699\begin_inset Text
3700
3701\layout Standard
3702
3703
3704\size footnotesize
3705Name of remote host
3706\end_inset
3707</cell>
3708<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
3709\begin_inset Text
3710
3711\layout Standard
3712
3713
3714\size footnotesize
3715remote_host
3716\end_inset
3717</cell>
3718<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
3719\begin_inset Text
3720
3721\layout Standard
3722
3723
3724\size footnotesize
3725varchar(50)
3726\end_inset
3727</cell>
3728<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
3729\begin_inset Text
3730
3731\layout Standard
3732
3733
3734\size footnotesize
3735blah.foobar.com
3736\end_inset
3737</cell>
3738</row>
3739<row topline="true">
3740<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
3741\begin_inset Text
3742
3743\layout Standard
3744
3745
3746\size footnotesize
3747I
3748\end_inset
3749</cell>
3750<cell alignment="left" valignment="top" topline="true" leftline="true" usebox="none">
3751\begin_inset Text
3752
3753\layout Standard
3754
3755
3756\size footnotesize
3757Request ID (from mod_unique_id)
3758\end_inset
3759</cell>
3760<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
3761\begin_inset Text
3762
3763\layout Standard
3764
3765
3766\size footnotesize
3767id
3768\end_inset
3769</cell>
3770<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
3771\begin_inset Text
3772
3773\layout Standard
3774
3775
3776\size footnotesize
3777char(19)
3778\end_inset
3779</cell>
3780<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
3781\begin_inset Text
3782
3783\layout Standard
3784
3785
3786\size footnotesize
3787POlFcUBRH30AAALdBG8
3788\end_inset
3789</cell>
3790</row>
3791<row topline="true">
3792<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
3793\begin_inset Text
3794
3795\layout Standard
3796
3797
3798\size footnotesize
3799l
3800\end_inset
3801</cell>
3802<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
3803\begin_inset Text
3804
3805\layout Standard
3806
3807
3808\size footnotesize
3809Ident user info
3810\end_inset
3811</cell>
3812<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
3813\begin_inset Text
3814
3815\layout Standard
3816
3817
3818\size footnotesize
3819remote_logname
3820\end_inset
3821</cell>
3822<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
3823\begin_inset Text
3824
3825\layout Standard
3826
3827
3828\size footnotesize
3829varchar(50)
3830\end_inset
3831</cell>
3832<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
3833\begin_inset Text
3834
3835\layout Standard
3836
3837
3838\size footnotesize
3839bobby
3840\end_inset
3841</cell>
3842</row>
3843<row topline="true">
3844<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
3845\begin_inset Text
3846
3847\layout Standard
3848
3849
3850\size footnotesize
3851M
3852\end_inset
3853</cell>
3854<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
3855\begin_inset Text
3856
3857\layout Standard
3858
3859
3860\size footnotesize
3861Machine ID
3862\begin_inset Formula $^{\textrm{2}}$
3863\end_inset
3864
3865
3866\end_inset
3867</cell>
3868<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
3869\begin_inset Text
3870
3871\layout Standard
3872
3873
3874\size footnotesize
3875machine_id
3876\end_inset
3877</cell>
3878<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
3879\begin_inset Text
3880
3881\layout Standard
3882
3883
3884\size footnotesize
3885varchar(25)
3886\end_inset
3887</cell>
3888<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
3889\begin_inset Text
3890
3891\layout Standard
3892
3893
3894\size footnotesize
3895web01
3896\end_inset
3897</cell>
3898</row>
3899<row topline="true">
3900<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
3901\begin_inset Text
3902
3903\layout Standard
3904
3905
3906\size footnotesize
3907m
3908\end_inset
3909</cell>
3910<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
3911\begin_inset Text
3912
3913\layout Standard
3914
3915
3916\size footnotesize
3917HTTP request method
3918\end_inset
3919</cell>
3920<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
3921\begin_inset Text
3922
3923\layout Standard
3924
3925
3926\size footnotesize
3927request_method
3928\end_inset
3929</cell>
3930<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
3931\begin_inset Text
3932
3933\layout Standard
3934
3935
3936\size footnotesize
3937varchar(6)
3938\end_inset
3939</cell>
3940<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
3941\begin_inset Text
3942
3943\layout Standard
3944
3945
3946\size footnotesize
3947GET
3948\end_inset
3949</cell>
3950</row>
3951<row topline="true">
3952<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
3953\begin_inset Text
3954
3955\layout Standard
3956
3957
3958\size footnotesize
3959P
3960\end_inset
3961</cell>
3962<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
3963\begin_inset Text
3964
3965\layout Standard
3966
3967
3968\size footnotesize
3969httpd child PID
3970\end_inset
3971</cell>
3972<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
3973\begin_inset Text
3974
3975\layout Standard
3976
3977
3978\size footnotesize
3979child_pid
3980\end_inset
3981</cell>
3982<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
3983\begin_inset Text
3984
3985\layout Standard
3986
3987
3988\size footnotesize
3989smallint unsigned
3990\end_inset
3991</cell>
3992<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
3993\begin_inset Text
3994
3995\layout Standard
3996
3997
3998\size footnotesize
39993215
4000\end_inset
4001</cell>
4002</row>
4003<row topline="true">
4004<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
4005\begin_inset Text
4006
4007\layout Standard
4008
4009
4010\size footnotesize
4011p
4012\end_inset
4013</cell>
4014<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
4015\begin_inset Text
4016
4017\layout Standard
4018
4019
4020\size footnotesize
4021httpd port
4022\end_inset
4023</cell>
4024<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
4025\begin_inset Text
4026
4027\layout Standard
4028
4029
4030\size footnotesize
4031server_port
4032\end_inset
4033</cell>
4034<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
4035\begin_inset Text
4036
4037\layout Standard
4038
4039
4040\size footnotesize
4041smallint unsigned
4042\end_inset
4043</cell>
4044<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
4045\begin_inset Text
4046
4047\layout Standard
4048
4049
4050\size footnotesize
405180
4052\end_inset
4053</cell>
4054</row>
4055<row topline="true">
4056<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
4057\begin_inset Text
4058
4059\layout Standard
4060
4061
4062\size footnotesize
4063R
4064\end_inset
4065</cell>
4066<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
4067\begin_inset Text
4068
4069\layout Standard
4070
4071
4072\size footnotesize
4073Referer
4074\end_inset
4075</cell>
4076<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
4077\begin_inset Text
4078
4079\layout Standard
4080
4081
4082\size footnotesize
4083referer
4084\end_inset
4085</cell>
4086<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
4087\begin_inset Text
4088
4089\layout Standard
4090
4091
4092\size footnotesize
4093varchar(255)
4094\end_inset
4095</cell>
4096<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
4097\begin_inset Text
4098
4099\layout Standard
4100
4101
4102\size footnotesize
4103http://www.biglinks4u.com/linkpage.html
4104\end_inset
4105</cell>
4106</row>
4107<row topline="true">
4108<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
4109\begin_inset Text
4110
4111\layout Standard
4112
4113
4114\size footnotesize
4115r
4116\end_inset
4117</cell>
4118<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
4119\begin_inset Text
4120
4121\layout Standard
4122
4123
4124\size footnotesize
4125Request in full form
4126\end_inset
4127</cell>
4128<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
4129\begin_inset Text
4130
4131\layout Standard
4132
4133
4134\size footnotesize
4135request_line
4136\end_inset
4137</cell>
4138<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
4139\begin_inset Text
4140
4141\layout Standard
4142
4143
4144\size footnotesize
4145varchar(255)
4146\end_inset
4147</cell>
4148<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
4149\begin_inset Text
4150
4151\layout Standard
4152
4153
4154\size footnotesize
4155GET /books-cycroad.html HTTP/1.1
4156\end_inset
4157</cell>
4158</row>
4159<row topline="true">
4160<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
4161\begin_inset Text
4162
4163\layout Standard
4164
4165
4166\size footnotesize
4167S
4168\end_inset
4169</cell>
4170<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
4171\begin_inset Text
4172
4173\layout Standard
4174
4175
4176\size footnotesize
4177Time of request in UNIX format
4178\end_inset
4179</cell>
4180<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
4181\begin_inset Text
4182
4183\layout Standard
4184
4185
4186\size footnotesize
4187time_stamp
4188\end_inset
4189</cell>
4190<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
4191\begin_inset Text
4192
4193\layout Standard
4194
4195
4196\size footnotesize
4197int unsigned
4198\end_inset
4199</cell>
4200<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
4201\begin_inset Text
4202
4203\layout Standard
4204
4205
4206\size footnotesize
42071005598029
4208\end_inset
4209</cell>
4210</row>
4211<row topline="true">
4212<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
4213\begin_inset Text
4214
4215\layout Standard
4216
4217
4218\size footnotesize
4219s
4220\end_inset
4221</cell>
4222<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
4223\begin_inset Text
4224
4225\layout Standard
4226
4227
4228\size footnotesize
4229HTTP status of request
4230\end_inset
4231</cell>
4232<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
4233\begin_inset Text
4234
4235\layout Standard
4236
4237
4238\size footnotesize
4239status
4240\end_inset
4241</cell>
4242<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
4243\begin_inset Text
4244
4245\layout Standard
4246
4247
4248\size footnotesize
4249smallint unsigned
4250\end_inset
4251</cell>
4252<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
4253\begin_inset Text
4254
4255\layout Standard
4256
4257
4258\size footnotesize
4259404
4260\end_inset
4261</cell>
4262</row>
4263<row topline="true">
4264<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
4265\begin_inset Text
4266
4267\layout Standard
4268
4269
4270\size footnotesize
4271T
4272\end_inset
4273</cell>
4274<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
4275\begin_inset Text
4276
4277\layout Standard
4278
4279
4280\size footnotesize
4281Seconds to service request
4282\end_inset
4283</cell>
4284<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
4285\begin_inset Text
4286
4287\layout Standard
4288
4289
4290\size footnotesize
4291request_duration
4292\end_inset
4293</cell>
4294<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
4295\begin_inset Text
4296
4297\layout Standard
4298
4299
4300\size footnotesize
4301smallint unsigned
4302\end_inset
4303</cell>
4304<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
4305\begin_inset Text
4306
4307\layout Standard
4308
4309
4310\size footnotesize
43112
4312\end_inset
4313</cell>
4314</row>
4315<row topline="true">
4316<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
4317\begin_inset Text
4318
4319\layout Standard
4320
4321
4322\size footnotesize
4323t
4324\end_inset
4325</cell>
4326<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
4327\begin_inset Text
4328
4329\layout Standard
4330
4331
4332\size footnotesize
4333Time of request in human format
4334\end_inset
4335</cell>
4336<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
4337\begin_inset Text
4338
4339\layout Standard
4340
4341
4342\size footnotesize
4343request_time
4344\end_inset
4345</cell>
4346<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
4347\begin_inset Text
4348
4349\layout Standard
4350
4351
4352\size footnotesize
4353char(28)
4354\end_inset
4355</cell>
4356<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
4357\begin_inset Text
4358
4359\layout Standard
4360
4361
4362\size footnotesize
4363[02/Dec/2001:15:01:26 -0800]
4364\end_inset
4365</cell>
4366</row>
4367<row topline="true">
4368<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
4369\begin_inset Text
4370
4371\layout Standard
4372
4373
4374\size footnotesize
4375U
4376\end_inset
4377</cell>
4378<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
4379\begin_inset Text
4380
4381\layout Standard
4382
4383
4384\size footnotesize
4385Request in simple form
4386\end_inset
4387</cell>
4388<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
4389\begin_inset Text
4390
4391\layout Standard
4392
4393
4394\size footnotesize
4395request_uri
4396\end_inset
4397</cell>
4398<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
4399\begin_inset Text
4400
4401\layout Standard
4402
4403
4404\size footnotesize
4405varchar(255)
4406\end_inset
4407</cell>
4408<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
4409\begin_inset Text
4410
4411\layout Standard
4412
4413
4414\size footnotesize
4415/books-cycroad.html
4416\end_inset
4417</cell>
4418</row>
4419<row topline="true">
4420<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
4421\begin_inset Text
4422
4423\layout Standard
4424
4425
4426\size footnotesize
4427u
4428\end_inset
4429</cell>
4430<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
4431\begin_inset Text
4432
4433\layout Standard
4434
4435
4436\size footnotesize
4437User info from HTTP auth
4438\end_inset
4439</cell>
4440<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
4441\begin_inset Text
4442
4443\layout Standard
4444
4445
4446\size footnotesize
4447remote_user
4448\end_inset
4449</cell>
4450<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
4451\begin_inset Text
4452
4453\layout Standard
4454
4455
4456\size footnotesize
4457varchar(50)
4458\end_inset
4459</cell>
4460<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
4461\begin_inset Text
4462
4463\layout Standard
4464
4465
4466\size footnotesize
4467bobby
4468\end_inset
4469</cell>
4470</row>
4471<row topline="true" bottomline="true">
4472<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
4473\begin_inset Text
4474
4475\layout Standard
4476
4477
4478\size footnotesize
4479v
4480\end_inset
4481</cell>
4482<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
4483\begin_inset Text
4484
4485\layout Standard
4486
4487
4488\size footnotesize
4489Virtual host servicing the request
4490\end_inset
4491</cell>
4492<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
4493\begin_inset Text
4494
4495\layout Standard
4496
4497
4498\size footnotesize
4499virtual_host
4500\end_inset
4501</cell>
4502<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
4503\begin_inset Text
4504
4505\layout Standard
4506
4507
4508\size footnotesize
4509varchar(50)
4510\end_inset
4511</cell>
4512<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
4513\begin_inset Text
4514
4515\layout Standard
4516
4517
4518\size footnotesize
4519www.foobar.com
4520\end_inset
4521</cell>
4522</row>
4523</lyxtabular>
4524
4525\end_inset
4526
4527
4528\layout Quote
4529
4530
4531\begin_inset Formula $^{\textrm{1}}$
4532\end_inset
4533
4534 You must also specify
4535\noun on
4536LogSQLWhichCookie
4537\noun default
4538for this to take effect.
4539\layout Quote
4540
4541
4542\begin_inset Formula $^{\textrm{2}}$
4543\end_inset
4544
4545 You must also specify
4546\noun on
4547LogSQLMachineID
4548\noun default
4549 for this to take effect.
4550\layout Standard
4551
4552If you have compiled mod_log_sql with SSL logging capability, you also can
4553 use these:
4554\layout Quote
4555
4556
4557\begin_inset Tabular
4558<lyxtabular version="3" rows="4" columns="5">
4559<features>
4560<column alignment="center" valignment="top" leftline="true" width="0pt">
4561<column alignment="left" valignment="top" leftline="true" width="0pt">
4562<column alignment="left" valignment="top" leftline="true" width="0pt">
4563<column alignment="left" valignment="top" leftline="true" width="0pt">
4564<column alignment="left" valignment="top" leftline="true" rightline="true" width="0pt">
4565<row topline="true" bottomline="true">
4566<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
4567\begin_inset Text
4568
4569\layout Standard
4570
4571\end_inset
4572</cell>
4573<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
4574\begin_inset Text
4575
4576\layout Standard
4577
4578
4579\series bold
4580What is this?
4581\end_inset
4582</cell>
4583<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
4584\begin_inset Text
4585
4586\layout Standard
4587
4588
4589\series bold
4590Data field
4591\end_inset
4592</cell>
4593<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
4594\begin_inset Text
4595
4596\layout Standard
4597
4598
4599\series bold
4600Column Type
4601\end_inset
4602</cell>
4603<cell alignment="center" valignment="top" topline="true" leftline="true" rightline="true" usebox="none">
4604\begin_inset Text
4605
4606\layout Standard
4607
4608
4609\series bold
4610Example
4611\end_inset
4612</cell>
4613</row>
4614<row topline="true">
4615<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
4616\begin_inset Text
4617
4618\layout Standard
4619
4620z
4621\end_inset
4622</cell>
4623<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
4624\begin_inset Text
4625
4626\layout Standard
4627
4628SSL cipher used
4629\end_inset
4630</cell>
4631<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
4632\begin_inset Text
4633
4634\layout Standard
4635
4636ssl_cipher
4637\end_inset
4638</cell>
4639<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
4640\begin_inset Text
4641
4642\layout Standard
4643
4644varchar(25)
4645\end_inset
4646</cell>
4647<cell alignment="center" valignment="top" topline="true" leftline="true" rightline="true" usebox="none">
4648\begin_inset Text
4649
4650\layout Standard
4651
4652RC4-MD5
4653\end_inset
4654</cell>
4655</row>
4656<row topline="true">
4657<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
4658\begin_inset Text
4659
4660\layout Standard
4661
4662q
4663\end_inset
4664</cell>
4665<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
4666\begin_inset Text
4667
4668\layout Standard
4669
4670Keysize of the SSL connection
4671\end_inset
4672</cell>
4673<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
4674\begin_inset Text
4675
4676\layout Standard
4677
4678ssl_keysize
4679\end_inset
4680</cell>
4681<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
4682\begin_inset Text
4683
4684\layout Standard
4685
4686smallint unsigned
4687\end_inset
4688</cell>
4689<cell alignment="center" valignment="top" topline="true" leftline="true" rightline="true" usebox="none">
4690\begin_inset Text
4691
4692\layout Standard
4693
469456
4695\end_inset
4696</cell>
4697</row>
4698<row topline="true" bottomline="true">
4699<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
4700\begin_inset Text
4701
4702\layout Standard
4703
4704Q
4705\end_inset
4706</cell>
4707<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
4708\begin_inset Text
4709
4710\layout Standard
4711
4712Maximum keysize supported
4713\end_inset
4714</cell>
4715<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
4716\begin_inset Text
4717
4718\layout Standard
4719
4720ssl_maxkeysize
4721\end_inset
4722</cell>
4723<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
4724\begin_inset Text
4725
4726\layout Standard
4727
4728smallint unsigned
4729\end_inset
4730</cell>
4731<cell alignment="center" valignment="top" topline="true" leftline="true" rightline="true" usebox="none">
4732\begin_inset Text
4733
4734\layout Standard
4735
4736128
4737\end_inset
4738</cell>
4739</row>
4740</lyxtabular>
4741
4742\end_inset
4743
4744
4745\layout Subsubsection
4746
4747LogSQLTransferLogTable
4748\layout LyX-Code
4749
4750
4751\series bold
4752MANDATORY (unless
4753\noun on
4754LogSQLMassVirtualHosting
4755\noun default
4756 is
4757\begin_inset Quotes eld
4758\end_inset
4759
4760on
4761\begin_inset Quotes erd
4762\end_inset
4763
4764)
4765\layout LyX-Code
4766
4767Syntax: LogSQLTransferLogTable table-name
4768\layout LyX-Code
4769
4770Example: LogSQLTransferLogTable access_log_table
4771\layout LyX-Code
4772
4773Context: virtual host
4774\layout Standard
4775
4776Defines which table is used for logging of Apache's transfers; this is analogous
4777 to Apache's TransferLog directive.
4778 table-name must be a valid table within the database defined in
4779\noun on
4780LogSQLDatabase
4781\noun default
4782.
4783\layout Standard
4784
4785This directive is not necessary if you declare
4786\noun on
4787LogSQLMassVirtualHosting On
4788\noun default
4789, since that directive activates dynamically-named tables.
4790 If you attempt to use
4791\noun on
4792LogSQLTransferLogTable
4793\noun default
4794 at the same time a warning will be logged and it will be ignored, since
4795
4796\noun on
4797LogSQLMassVirtualHosting
4798\noun default
4799 takes priority.
4800\layout Subsubsection
4801
4802LogSQLWhichCookie
4803\layout LyX-Code
4804
4805Syntax: LogSQLWhichCookie cookiename
4806\layout LyX-Code
4807
4808Example: LogSQLWhichCookie Clicks
4809\layout LyX-Code
4810
4811Default: None
4812\layout LyX-Code
4813
4814Context: virtual host
4815\layout Standard
4816
4817In HTTP, cookies have names to distinguish them from each other.
4818 Using mod_usertrack, for example, you can give your user-tracking cookies
4819 a name with the CookieName directive.
4820\layout Standard
4821
4822You must include a 'c' character in
4823\noun on
4824LogSQLTransferLogFormat
4825\noun default
4826 for this directive to take effect; once you specify 'c',
4827\noun on
4828LogSQLWhichCookie
4829\noun default
4830 tells mod_log_sql which cookie to log.
4831 This is necessary because you will usually be setting and receiving more
4832 than one cookie from a client; this cookie designates which one to log.
4833\layout Standard
4834
4835Note: although this was intended for people who are using mod_usertrack
4836 to set user-tracking cookies, you aren't restricted in any way.
4837 You can choose which cookie you wish to log to the database --any cookie
4838 at all -- and it doesn't necessarily have to have anything to do with mod_usert
4839rack.
4840\layout Subsubsection
4841
4842LogSQLWhichCookies
4843\layout LyX-Code
4844
4845Syntax: LogSQLWhichCookies cookie1 cookie2 ...
4846 cookieN
4847\layout LyX-Code
4848
4849Example: LogSQLWhichCookies userlogin foobar foobaz
4850\layout LyX-Code
4851
4852Default: None
4853\layout LyX-Code
4854
4855Context: virtual host
4856\layout Standard
4857
4858Defines the list of cookies you would like logged.
4859 This works in conjunction with
4860\noun on
4861LogSQLCookieLogTable
4862\noun default
4863.
4864 This directive does not require any additional characters to be added to
4865 the
4866\noun on
4867LogSQLTransferLogFormat
4868\noun default
4869 string.
4870 The feature is activated simply by including this directive, upon which
4871 you will begin populating the separate cookie table with data.
4872\layout Standard
4873
4874Note that you must have already created the table (see create-tables.sql,
4875 included in the package), or
4876\noun on
4877LogSQLCreateTables
4878\noun default
4879 must be set to
4880\begin_inset Quotes eld
4881\end_inset
4882
4883on
4884\begin_inset Quotes erd
4885\end_inset
4886
4887.
4888\layout Subsubsection
4889
4890LogSQLWhichHeadersIn
4891\layout LyX-Code
4892
4893Syntax: LogSQLWhichHeadersIn item1 item2 ...
4894 itemN
4895\layout LyX-Code
4896
4897Example: LogSQLWhichHeadersIn UserAgent Accept-Encoding Host
4898\layout LyX-Code
4899
4900Default: None
4901\layout LyX-Code
4902
4903Context: virtual host
4904\layout Standard
4905
4906Defines the list of inbound headers you would like logged.
4907 This works in conjunction with
4908\noun on
4909 LogSQLHeadersInLogTable
4910\noun default
4911.
4912 This directive does not require any additional characters to be added to
4913 the
4914\noun on
4915LogSQLTransferLogFormat
4916\noun default
4917 string.
4918 The feature is activated simply by including this directive, upon which
4919 you will begin populating the separate inbound-headers table with data.
4920\layout Standard
4921
4922Note that you must have already created the table (see create-tables.sql,
4923 included in the package), or
4924\noun on
4925LogSQLCreateTables
4926\noun default
4927 must be set to
4928\begin_inset Quotes eld
4929\end_inset
4930
4931on
4932\begin_inset Quotes erd
4933\end_inset
4934
4935.
4936\layout Subsubsection
4937
4938LogSQLWhichHeadersOut
4939\layout LyX-Code
4940
4941Syntax: LogSQLWhichHeadersOut item1 item2 ...
4942 itemN
4943\layout LyX-Code
4944
4945Example: LogSQLWhichHeadersOut Expires Content-Type Cache-Control
4946\layout LyX-Code
4947
4948Default: None
4949\layout LyX-Code
4950
4951Context: virtual host
4952\layout Standard
4953
4954Defines the list of outbound headers you would like logged.
4955 This works in conjunction with
4956\noun on
4957LogSQLHeadersOutLogTable
4958\noun default
4959.
4960 This directive does not require any additional characters to be added to
4961 the
4962\noun on
4963LogSQLTransferLogFormat
4964\noun default
4965 string.
4966 The feature is activated simply by including this directive, upon which
4967 you will begin populating the separate outbound-headers table with data.
4968\layout Standard
4969
4970Note that you must have already created the table (see create-tables.sql,
4971 included in the package), or
4972\noun on
4973LogSQLCreateTables
4974\noun default
4975 must be set to
4976\begin_inset Quotes eld
4977\end_inset
4978
4979on
4980\begin_inset Quotes erd
4981\end_inset
4982
4983.
4984\layout Subsubsection
4985
4986LogSQLWhichNotes
4987\layout LyX-Code
4988
4989Syntax: LogSQLWhichNotes item1 item2 ...
4990 itemN
4991\layout LyX-Code
4992
4993Example: LogSQLWhichNotes mod_gzip_result mod_gzip_compression_ratio
4994\layout LyX-Code
4995
4996Default: None
4997\layout LyX-Code
4998
4999Context: virtual host
5000\layout Standard
5001
5002Defines the list of notes you would like logged.
5003 This works in conjunction with
5004\noun on
5005LogSQLNotesLogTable
5006\noun default
5007.
5008 This directive does not require any additional characters to be added to
5009 the
5010\noun on
5011LogSQLTransferLogFormat
5012\noun default
5013 string.
5014 The feature is activated simply by including this directive, upon which
5015 you will begin populating the separate notes table with data.
5016\layout Standard
5017
5018Note that you must have already created the table (see create-tables.sql,
5019 included in the package), or
5020\noun on
5021LogSQLCreateTables
5022\noun default
5023 must be set to
5024\begin_inset Quotes eld
5025\end_inset
5026
5027on
5028\begin_inset Quotes erd
5029\end_inset
5030
5031.
5032\layout Section
5033
5034FAQ
5035\layout Subsection
5036
5037General module questions
5038\layout Subsubsection
5039
5040
5041\begin_inset LatexCommand \label{sub:why}
5042
5043\end_inset
5044
5045Why log to an SQL database?
5046\layout Standard
5047
5048To begin with, let's get it out of the way: logging to a database is not
5049 a panacea.
5050 But while there are complexities with this solution, the benefit can be
5051 substantial for certain classes of administrator or people with advanced
5052 requirements:
5053\layout Itemize
5054
5055Chores like log rotation go away, as you can DELETE records from the SQL
5056 database once they are no longer useful.
5057 For example, the excellent and popular log-analysis tool
5058\begin_inset LatexCommand \url[Webalizer]{(http://www.webalizer.com)}
5059
5060\end_inset
5061
5062 does not need historic logs after it has processed them, enabling you to
5063 delete older logs.
5064\layout Itemize
5065
5066People with clusters of web servers (for high availability) will benefit
5067 the most -- all their webservers can log to a single SQL database.
5068 This obviates the need to collate/interleave the many separate logfiles,
5069 which can be / highly/ problematic.
5070
5071\layout Itemize
5072
5073People acquainted with the power of SQL SELECT statements will know the
5074 flexibility of the extraction possibilities at their fingertips.
5075\layout Standard
5076
5077For example, do you want to see all your 404's? Do this:
5078\layout LyX-Code
5079
5080select remote_host,status,request_uri,bytes_sent,from_unixtime(time_stamp)
5081\layout LyX-Code
5082
5083from acc_log_tbl where status=404 order by time_stamp;
5084\layout LyX-Code
5085
5086\layout Standard
5087\align center
5088
5089\begin_inset Tabular
5090<lyxtabular version="3" rows="5" columns="5">
5091<features>
5092<column alignment="left" valignment="top" leftline="true" width="0pt">
5093<column alignment="left" valignment="top" leftline="true" width="0pt">
5094<column alignment="left" valignment="top" leftline="true" width="0pt">
5095<column alignment="left" valignment="top" leftline="true" width="0pt">
5096<column alignment="left" valignment="top" leftline="true" rightline="true" width="0pt">
5097<row topline="true" bottomline="true">
5098<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
5099\begin_inset Text
5100
5101\layout Standard
5102
5103remote_host
5104\end_inset
5105</cell>
5106<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
5107\begin_inset Text
5108
5109\layout Standard
5110
5111status
5112\end_inset
5113</cell>
5114<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
5115\begin_inset Text
5116
5117\layout Standard
5118
5119request_uri
5120\end_inset
5121</cell>
5122<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
5123\begin_inset Text
5124
5125\layout Standard
5126
5127bytes_sent
5128\end_inset
5129</cell>
5130<cell alignment="center" valignment="top" topline="true" leftline="true" rightline="true" usebox="none">
5131\begin_inset Text
5132
5133\layout Standard
5134
5135from_unixtime(time_stamp)
5136\end_inset
5137</cell>
5138</row>
5139<row topline="true">
5140<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
5141\begin_inset Text
5142
5143\layout Standard
5144
5145marge.mmm.co.uk
5146\end_inset
5147</cell>
5148<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
5149\begin_inset Text
5150
5151\layout Standard
5152
5153404
5154\end_inset
5155</cell>
5156<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
5157\begin_inset Text
5158
5159\layout Standard
5160
5161/favicon.ico
5162\end_inset
5163</cell>
5164<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
5165\begin_inset Text
5166
5167\layout Standard
5168
5169321
5170\end_inset
5171</cell>
5172<cell alignment="center" valignment="top" topline="true" leftline="true" rightline="true" usebox="none">
5173\begin_inset Text
5174
5175\layout Standard
5176
51772001-11-20 02:30:56
5178\end_inset
5179</cell>
5180</row>
5181<row topline="true">
5182<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
5183\begin_inset Text
5184
5185\layout Standard
5186
518762.180.239.251
5188\end_inset
5189</cell>
5190<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
5191\begin_inset Text
5192
5193\layout Standard
5194
5195404
5196\end_inset
5197</cell>
5198<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
5199\begin_inset Text
5200
5201\layout Standard
5202
5203/favicon.ico
5204\end_inset
5205</cell>
5206<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
5207\begin_inset Text
5208
5209\layout Standard
5210
5211333
5212\end_inset
5213</cell>
5214<cell alignment="center" valignment="top" topline="true" leftline="true" rightline="true" usebox="none">
5215\begin_inset Text
5216
5217\layout Standard
5218
52192001-11-20 02:45:25
5220\end_inset
5221</cell>
5222</row>
5223<row topline="true">
5224<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
5225\begin_inset Text
5226
5227\layout Standard
5228
5229212.234.12.66
5230\end_inset
5231</cell>
5232<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
5233\begin_inset Text
5234
5235\layout Standard
5236
5237404
5238\end_inset
5239</cell>
5240<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
5241\begin_inset Text
5242
5243\layout Standard
5244
5245/favicon.ico
5246\end_inset
5247</cell>
5248<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
5249\begin_inset Text
5250
5251\layout Standard
5252
5253321
5254\end_inset
5255</cell>
5256<cell alignment="center" valignment="top" topline="true" leftline="true" rightline="true" usebox="none">
5257\begin_inset Text
5258
5259\layout Standard
5260
52612001-11-20 03:01:00
5262\end_inset
5263</cell>
5264</row>
5265<row topline="true" bottomline="true">
5266<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
5267\begin_inset Text
5268
5269\layout Standard
5270
5271212.210.78.254
5272\end_inset
5273</cell>
5274<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
5275\begin_inset Text
5276
5277\layout Standard
5278
5279404
5280\end_inset
5281</cell>
5282<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
5283\begin_inset Text
5284
5285\layout Standard
5286
5287/favicon.ico
5288\end_inset
5289</cell>
5290<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
5291\begin_inset Text
5292
5293\layout Standard
5294
5295333
5296\end_inset
5297</cell>
5298<cell alignment="center" valignment="top" topline="true" leftline="true" rightline="true" usebox="none">
5299\begin_inset Text
5300
5301\layout Standard
5302
53032001-11-20 03:26:05
5304\end_inset
5305</cell>
5306</row>
5307</lyxtabular>
5308
5309\end_inset
5310
5311
5312\layout LyX-Code
5313
5314\layout Standard
5315
5316Or do you want to see how many bytes you've sent within a certain directory
5317 or site? Do this:
5318\layout LyX-Code
5319
5320select request_uri,sum(bytes_sent) as bytes,count(request_uri) as howmany
5321 from
5322\layout LyX-Code
5323
5324acc_log_tbl where request_uri like '%mod_log_sql%' group by request_uri
5325 order
5326\layout LyX-Code
5327
5328by howmany desc;
5329\layout LyX-Code
5330
5331\layout Standard
5332\align center
5333
5334\begin_inset Tabular
5335<lyxtabular version="3" rows="5" columns="3">
5336<features>
5337<column alignment="left" valignment="top" leftline="true" width="0pt">
5338<column alignment="left" valignment="top" leftline="true" width="0pt">
5339<column alignment="left" valignment="top" leftline="true" rightline="true" width="0pt">
5340<row topline="true" bottomline="true">
5341<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
5342\begin_inset Text
5343
5344\layout Standard
5345
5346request_uri
5347\end_inset
5348</cell>
5349<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
5350\begin_inset Text
5351
5352\layout Standard
5353
5354bytes
5355\end_inset
5356</cell>
5357<cell alignment="center" valignment="top" topline="true" leftline="true" rightline="true" usebox="none">
5358\begin_inset Text
5359
5360\layout Standard
5361
5362howmany
5363\end_inset
5364</cell>
5365</row>
5366<row topline="true">
5367<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
5368\begin_inset Text
5369
5370\layout Standard
5371
5372/mod_log_sql/style_1.css
5373\end_inset
5374</cell>
5375<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
5376\begin_inset Text
5377
5378\layout Standard
5379
5380157396
5381\end_inset
5382</cell>
5383<cell alignment="center" valignment="top" topline="true" leftline="true" rightline="true" usebox="none">
5384\begin_inset Text
5385
5386\layout Standard
5387
53881288
5389\end_inset
5390</cell>
5391</row>
5392<row topline="true">
5393<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
5394\begin_inset Text
5395
5396\layout Standard
5397
5398/mod_log_sql/
5399\end_inset
5400</cell>
5401<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
5402\begin_inset Text
5403
5404\layout Standard
5405
54062514337
5407\end_inset
5408</cell>
5409<cell alignment="center" valignment="top" topline="true" leftline="true" rightline="true" usebox="none">
5410\begin_inset Text
5411
5412\layout Standard
5413
5414801
5415\end_inset
5416</cell>
5417</row>
5418<row topline="true">
5419<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
5420\begin_inset Text
5421
5422\layout Standard
5423
5424/mod_log_sql/mod_log_sql.tar.gz
5425\end_inset
5426</cell>
5427<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
5428\begin_inset Text
5429
5430\layout Standard
5431
54329769312
5433\end_inset
5434</cell>
5435<cell alignment="center" valignment="top" topline="true" leftline="true" rightline="true" usebox="none">
5436\begin_inset Text
5437
5438\layout Standard
5439
5440456
5441\end_inset
5442</cell>
5443</row>
5444<row topline="true" bottomline="true">
5445<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
5446\begin_inset Text
5447
5448\layout Standard
5449
5450/mod_log_sql/faq.html
5451\end_inset
5452</cell>
5453<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
5454\begin_inset Text
5455
5456\layout Standard
5457
54585038728
5459\end_inset
5460</cell>
5461<cell alignment="center" valignment="top" topline="true" leftline="true" rightline="true" usebox="none">
5462\begin_inset Text
5463
5464\layout Standard
5465
5466436
5467\end_inset
5468</cell>
5469</row>
5470</lyxtabular>
5471
5472\end_inset
5473
5474
5475\layout LyX-Code
5476
5477\layout Standard
5478
5479Or maybe you want to see who's linking to you? Do this:
5480\layout LyX-Code
5481
5482select count(referer) as num,referer from acc_log_tbl where
5483\layout LyX-Code
5484
5485request_uri='/mod_log_sql/' group by referer order by num desc;
5486\layout LyX-Code
5487
5488\layout Standard
5489\align center
5490
5491\begin_inset Tabular
5492<lyxtabular version="3" rows="5" columns="2">
5493<features>
5494<column alignment="left" valignment="top" leftline="true" width="0pt">
5495<column alignment="left" valignment="top" leftline="true" rightline="true" width="0pt">
5496<row topline="true" bottomline="true">
5497<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
5498\begin_inset Text
5499
5500\layout Standard
5501
5502num
5503\end_inset
5504</cell>
5505<cell alignment="center" valignment="top" topline="true" leftline="true" rightline="true" usebox="none">
5506\begin_inset Text
5507
5508\layout Standard
5509
5510referer
5511\end_inset
5512</cell>
5513</row>
5514<row topline="true">
5515<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
5516\begin_inset Text
5517
5518\layout Standard
5519
5520271
5521\end_inset
5522</cell>
5523<cell alignment="center" valignment="top" topline="true" leftline="true" rightline="true" usebox="none">
5524\begin_inset Text
5525
5526\layout Standard
5527
5528http://freshmeat.net/projects/mod_log_sql/
5529\end_inset
5530</cell>
5531</row>
5532<row topline="true">
5533<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
5534\begin_inset Text
5535
5536\layout Standard
5537
553896
5539\end_inset
5540</cell>
5541<cell alignment="center" valignment="top" topline="true" leftline="true" rightline="true" usebox="none">
5542\begin_inset Text
5543
5544\layout Standard
5545
5546http://modules.apache.org/search?id=339
5547\end_inset
5548</cell>
5549</row>
5550<row topline="true">
5551<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
5552\begin_inset Text
5553
5554\layout Standard
5555
555648
5557\end_inset
5558</cell>
5559<cell alignment="center" valignment="top" topline="true" leftline="true" rightline="true" usebox="none">
5560\begin_inset Text
5561
5562\layout Standard
5563
5564http://freshmeat.net/
5565\end_inset
5566</cell>
5567</row>
5568<row topline="true" bottomline="true">
5569<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
5570\begin_inset Text
5571
5572\layout Standard
5573
55748
5575\end_inset
5576</cell>
5577<cell alignment="center" valignment="top" topline="true" leftline="true" rightline="true" usebox="none">
5578\begin_inset Text
5579
5580\layout Standard
5581
5582http://freshmeat.net
5583\end_inset
5584</cell>
5585</row>
5586</lyxtabular>
5587
5588\end_inset
5589
5590
5591\layout LyX-Code
5592
5593\layout Standard
5594
5595As you can see, there are myriad possibilities that can be constructed with
5596 the wonderful SQL SELECT statement.
5597 Logging to an SQL database can be really quite useful!
5598\layout Subsubsection
5599
5600Why use MySQL? Are there alternatives?
5601\layout Standard
5602
5603MySQL is a robust, free, and very powerful production-quality database engine.
5604 It is well supported and comes with detailed documentation.
5605 Many 3rd-party software pacakges (e.g.
5606 Slashcode, the engine that powers Slashdot) run exclusively with MySQL.
5607 In other words, you will belong to a very robust and well-supported community
5608 by choosing MySQL.
5609\layout Standard
5610
5611That being said, there are alternatives.
5612 PostgreSQL is probably MySQL's leading "competitor" in the free database
5613 world.
5614 There is also an excellent module available for Apache to permit logging
5615 to a PostgreSQL database, called
5616\begin_inset LatexCommand \url[pgLOGd]{(http://www.digitalstratum.com/pglogd/)}
5617
5618\end_inset
5619
5620.
5621\layout Subsubsection
5622
5623Is this code production-ready?
5624\layout Standard
5625
5626By all accounts it is.
5627 It is known to work without a problem on many-thousands-of-hits-per-day
5628 webservers.
5629 Does that mean it is 100% bug free? Well, no software is.
5630 But it is well-tested and believed to be fully compatible with production
5631 environments.
5632 (The usual disclaimers apply.
5633 This software is provided without warranty of any kind.)
5634\layout Subsubsection
5635
5636Who's using mod_log_sql?
5637\layout Standard
5638
5639Good question! It would be great to find out! If you are a production-level
5640 mod_log_sql user, please contact
5641\begin_inset LatexCommand \url[the maintainer, Chris Powell]{(chris@grubbybaby.com)}
5642
5643\end_inset
5644
5645 so that you can be mentioned here.
5646\layout Subsubsection
5647
5648Why doesn't the module also replace the Apache ErrorLog?
5649\layout Standard
5650
5651There are circumstances when that would be quite unwise -- for example,
5652 if Apache could not reach the MySQL server for some reason and needed to
5653 log that fact.
5654 Without a text-based error log you'd never know anything was wrong, because
5655 Apache would be trying to log a database connection error to the database...
5656 you get the point.
5657\layout Standard
5658
5659Error logs are usually not very high-traffic and are really best left as
5660 text files on a web server machine.
5661\layout Subsubsection
5662
5663Does mod_log_sql work with Apache 2.x?
5664\layout Standard
5665
5666As of this writing, no.
5667 The Apache Group significantly altered the module API with the release
5668 of Apache 2.0.
5669 All modules written for 1.3, including mod_log_sql, will not work with 2.0.
5670\layout Standard
5671
5672mod_log_sql will eventually be ported to Apache 2.x, but not immediately.
5673 It is going to take some time, and there are other features that have higher
5674 priority.
5675 Please sign up for the announcements list (on the main website) or monitor
5676 the website for updates to learn when the port (and other releases) are
5677 available.
5678\layout Standard
5679
5680<OPINION>If you're a *NIX user, stick with Apache 1.3.x for now.
5681 Major modules like mod_ssl and PHP are not even ready for 2.0 yet, and the
5682 main benefits in 2.0 are for Win32 users anyway.
5683 Apache 1.3.x is rock-stable and performs equally well on *NIX as 2.0.</OPINION>
5684\layout Subsubsection
5685
5686Does mod_log_sql connect to MySQL via TCP/IP or a socket?
5687\layout Standard
5688
5689It depends! This is not determined by mod_log_sql.
5690 mod_log_sql relies on a connection command that is supplied in the MySQL
5691 API, and that command is somewhat intelligent.
5692 How it works:
5693\layout Itemize
5694
5695if the specified MySQL database is on the same machine, the connection command
5696 uses a socket to communicate with MySQL
5697\layout Itemize
5698
5699if the specified MySQL database is on a different machine, mod_log_sql connects
5700 using TCP/IP.
5701
5702\layout Standard
5703
5704You don't have any control of which methodology is used.
5705 You can fine-tune some of the configuration, however.
5706 The
5707\noun on
5708LogSQLSocketFile
5709\noun default
5710 runtime configuration directive overrides the default of
5711\begin_inset Quotes eld
5712\end_inset
5713
5714/var/lib/mysql/mysql.sock
5715\begin_inset Quotes erd
5716\end_inset
5717
5718 for socket-based connections, whereas the
5719\noun on
5720LogSQLTCPPort
5721\noun default
5722 command allows to you override the default TCP port of 3306 for TCP/IP
5723 connections.
5724\layout Subsubsection
5725
5726I have discovered a bug.
5727 Who can I contact?
5728\layout Standard
5729
5730Please contact
5731\begin_inset LatexCommand \url[the maintainer]{(chris@grubbybaby.com)}
5732
5733\end_inset
5734
5735! Your comments, suggestions, bugfixes, bug catches, and usage testimonials
5736 are always welcome.
5737 As free software, mod_log_sql is intended to be a community effort -- any
5738 code contributions or other ideas will be fully and openly credited, of
5739 course.
5740\layout Subsection
5741
5742Problems
5743\layout Subsubsection
5744
5745Apache segfaults when using PHP and mod_log_sql
5746\layout Standard
5747
5748This occurs if you compiled PHP with MySQL database support.
5749 PHP utilizes its internal, bundled MySQL libraries by default.
5750 These conflict with the
5751\begin_inset Quotes eld
5752\end_inset
5753
5754real
5755\begin_inset Quotes erd
5756\end_inset
5757
5758 MySQL libraries linked by mod_log_sql, causing the segmentation fault.
5759
5760\layout Standard
5761
5762The solution is to configure PHP to link against the real MySQL libraries
5763 and recompile mod_php.
5764 Apache will run properly once the modules are all using the same version
5765 of the MySQL libraries.
5766\layout Subsubsection
5767
5768
5769\begin_inset LatexCommand \label{faq:NothingLogged}
5770
5771\end_inset
5772
5773Apache appears to start up fine, but nothing is getting logged in the database
5774\layout Standard
5775
5776If you do not see any entries in the access_log, then something is preventing
5777 the inserts from happening.
5778 This could be caused by several things:
5779\layout Itemize
5780
5781Improper privileges set up in the MySQL database
5782\layout Itemize
5783
5784You aren't hitting a VirtualHost that has a LogSQLTransferLogTable entry
5785
5786\layout Itemize
5787
5788You didn't specify the right database host or login information
5789\layout Itemize
5790
5791Another factor is preventing a connection to the database
5792\layout Standard
5793
5794Important: it is improper to ask for help before you have followed these
5795 steps.
5796\layout Standard
5797
5798First examine the MySQL log that you established in step
5799\begin_inset LatexCommand \ref{step:EnaLog}
5800
5801\end_inset
5802
5803 of section
5804\begin_inset LatexCommand \ref{sub:PrepDb}
5805
5806\end_inset
5807
5808.
5809 Ensure that the INSERT statements are not being rejected because of a malformed
5810 table name or other typographical error.
5811 By enabling that log, you instructed MySQL to log every connection and
5812 command it receives -- if you see no INSERT attempts in the log, the module
5813 isn't successfully connecting to the database.
5814 If you see nothing at all in the log -- not even a record of your administrativ
5815e connection attempts, then you did not enable the log correctly.
5816 If you do see INSERT attempts but they are failing, the log should tell
5817 you why.
5818\layout Standard
5819
5820Second, confirm that your
5821\noun on
5822LogSQL*
5823\noun default
5824 directives are all correct.
5825\layout Standard
5826
5827Third, examine the Apache
5828\noun on
5829
5830\noun default
5831error logs for messages from mod_log_sql; the module will offer hints as
5832 to why it cannot connect, etc.
5833
5834\layout Standard
5835
5836The next thing to do is recompile the module with debugging output activated.
5837 change the "#undef DEBUG" on line 8 of mod_log_sql.c to "#define DEBUG"
5838 and recompile/reinstall.
5839 The module will now output copious notes about what it is doing, and this
5840 will help you (and the maintainer) solve the problem.
5841 In order to see the debugging messages, ensure that you make them visible
5842 using the
5843\noun on
5844LogLevel
5845\noun default
5846directive
5847\series bold
5848in the main server config as well as in each
5849\noun on
5850VirtualHost
5851\noun default
5852config:
5853\layout LyX-Code
5854
5855LogLevel debug
5856\layout LyX-Code
5857
5858ErrorLog /var/log/httpd/server-messages
5859\layout Subsubsection
5860
5861Why do I get the message
5862\begin_inset Quotes eld
5863\end_inset
5864
5865insufficient configuration info to establish database link
5866\begin_inset Quotes erd
5867\end_inset
5868
5869 in my Apache error log?
5870\layout Standard
5871
5872At a minimum,
5873\noun on
5874LogSQLDatabase
5875\noun default
5876 and
5877\noun on
5878LogSQLLoginInfo
5879\noun default
5880must be defined in order for the module to be able to establish a database
5881 link.
5882 If these are not defined or are incomplete you will receive this error
5883 message.
5884\layout Subsubsection
5885
5886My database cannot handle all the open connections from mod_log_sql, is
5887 there anything I can do?
5888\layout Standard
5889
5890The rule of thumb: if you have
5891\emph on
5892n
5893\emph default
5894webservers each configured to support
5895\emph on
5896y
5897\emph default
5898
5899\noun on
5900MaxClients
5901\noun default
5902, then your database must be able to handle
5903\begin_inset Formula $n\times y$
5904\end_inset
5905
5906 simultenous connections
5907\emph on
5908in the worst case.
5909
5910\emph default
5911Certainly you must use common sense, consider reasonable traffic expectations
5912 and structure things accordingly.
5913\layout Standard
5914
5915Tweaking my.cnf to scale to high connection loads is imperative.
5916 But if hardware limitations prevent your MySQL server from gracefully handling
5917 the number of incoming connections, it would be beneficial to upgrade the
5918 memory or CPU on that server in order to handle the load.
5919
5920\layout Standard
5921
5922Jeremy Zawodny, a highly respected MySQL user and contributor to Linux Magazine,
5923 has this very helpful and highly appropriate article on tuning MySQL:
5924\begin_inset LatexCommand \url{http://jeremy.zawodny.com/blog/archives/000173.html}
5925
5926\end_inset
5927
5928
5929\layout Standard
5930
5931Please remember that mod_log_sql's overriding principle is
5932\series bold
5933performance
5934\series default
5935 -- that is what the target audience demands and expects.
5936 Other database logging solutions do not open and maintain many database
5937 connections, but their performance suffers drastically.
5938 For example, pgLOGd funnels all log connections through a separate daemon
5939 that connects to the database, but that bottlenecks the entire process.
5940 mod_log_sql achieves performance numbers an order of magnitude greater
5941 than the alternatives because it dispenses with the overhead associated
5942 with rapid connection cycling, and it doesn't attempt to shoehorn all the
5943 database traffic through a single extra daemon or proxy process.
5944\layout Subsubsection
5945
5946Why do I occasionally see a
5947\begin_inset Quotes eld
5948\end_inset
5949
5950lost connection to MySQL server
5951\begin_inset Quotes erd
5952\end_inset
5953
5954 message in my Apache error log?
5955\layout Standard
5956
5957This message may appear every now and then in your Apache error log, especially
5958 on very lightly loaded servers.
5959 This doesn't mean that anything is necessarily wrong.
5960 Within each httpd child process, mod_log_sql will open (and keep open)
5961 a connection to the MySQL server.
5962 MySQL, however, will close connections that haven't been used in a while;
5963 the default timeout is 8 hours.
5964 When this occurs, mod_log_sql will notice and re-open the connection.
5965 That event is what is being logged, and looks like this:
5966\layout LyX-Code
5967
5968[Tue Nov 12 19:04:10 2002] [error] mod_log_sql: first attempt failed,
5969\layout LyX-Code
5970
5971 API said: error 2013, Lost connection to MySQL server during query
5972\layout LyX-Code
5973
5974[Tue Nov 12 19:04:10 2002] [error] mod_log_sql: reconnect successful
5975\layout LyX-Code
5976
5977[Tue Nov 12 19:04:10 2002] [error] mod_log_sql: second attempt successful
5978\layout Standard
5979
5980Reference:
5981\begin_inset LatexCommand \url[MySQL documentation]{(http://www.mysql.com/documentation/mysql/bychapter/manual_Problems.html#Gone_away)}
5982
5983\end_inset
5984
5985
5986\layout Subsection
5987
5988Performance and Tuning
5989\layout Subsubsection
5990
5991How well does it perform?
5992\layout Standard
5993
5994mod_log_sql scales to very high loads.
5995 Apache 1.3.22 + mod_log_sql was benchmarked using the "ab" (Apache Bench)
5996 program that comes with the Apache distribution; here are the results.
5997\layout Standard
5998
5999Overall configuration:
6000\layout Itemize
6001
6002Machine A: Apache webserver
6003\layout Itemize
6004
6005Machine B: MySQL server
6006\layout Itemize
6007
6008Machines A and B connected with 100Mbps Ethernet
6009\layout Itemize
6010
6011Webserver: Celeron 400, 128 MB RAM, IDE storage
6012\layout Standard
6013
6014Apache configuration:
6015\layout LyX-Code
6016
6017Timeout 300
6018\layout LyX-Code
6019
6020KeepAlive On
6021\layout LyX-Code
6022
6023MaxKeepAliveRequests 100
6024\layout LyX-Code
6025
6026KeepAliveTimeout 15
6027\layout LyX-Code
6028
6029MinSpareServers 5
6030\layout LyX-Code
6031
6032StartServers 10
6033\layout LyX-Code
6034
6035MaxSpareServers 15
6036\layout LyX-Code
6037
6038MaxClients 256
6039\layout LyX-Code
6040
6041MaxRequestsPerChild 5000
6042\layout LyX-Code
6043
6044LogSQLTransferLogFormat AbHhmRSsTUuvc
6045\layout LyX-Code
6046
6047LogSQLWhichCookie Clicks
6048\layout LyX-Code
6049
6050CookieTracking on
6051\layout LyX-Code
6052
6053CookieName Clicks
6054\layout Standard
6055
6056"ab" commandline:
6057\layout LyX-Code
6058
6059./ab -c 10 -t 20 -v 2 -C Clicks=ab_run http://www.hostname.com/target
6060\layout Standard
6061
6062( 10 concurrent requests; 20 second test; setting a cookie "Clicks=ab_run";
6063 target = the mod_log_sql homepage.
6064 )
6065\layout Standard
6066
6067Ten total ab runs were conducted: five with MySQL logging enabled, and five
6068 with all MySQL directives commented out of httpd.conf.
6069 Then each five were averaged.
6070 The results:
6071\layout Itemize
6072
6073Average of five runs employing MySQL
6074\emph on
6075and
6076\emph default
6077 standard text logging:
6078\series bold
6079139.01 requests per second, zero errors
6080\series default
6081.
6082\layout Itemize
6083
6084Average of five runs employing
6085\emph on
6086only
6087\emph default
6088 standard text logging:
6089\series bold
6090139.96 requests per second, zero errors
6091\series default
6092.
6093\layout Standard
6094
6095In other words, any rate-limiting effects on this particular hardware setup
6096 are not caused by MySQL.
6097 Note that although this very simple webserver setup is hardly cutting-edge
6098 -- it is, after all, a fairly small machine -- 139 requests per second
6099 equal over
6100\emph on
6101twelve million hits per day.
6102\layout Standard
6103
6104If you run this benchmark yourself, take note of three things:
6105\layout Enumerate
6106
6107Use a target URL that is on your own webserver :-).
6108
6109\layout Enumerate
6110
6111Wait until all your connections are closed out between runs; after several
6112 thousand requests your TCP/IP stack will be filled with hundreds of connections
6113 in TIME_WAIT that need to close.
6114 Do a "netstat -t|wc -l" on the webserver to see.
6115 If you don't wait, you can expect to see a lot of messages like "ip_conntrack:
6116 table full, dropping packet" in your logs.
6117 (This has nothing to do with mod_log_sql, this is simply the nature of
6118 the TCP/IP stack in the Linux kernel.)
6119\layout Enumerate
6120
6121When done with your runs, clean these many thousands of requests out of
6122 your database:
6123\layout LyX-Code
6124
6125mysql> delete from access_log where agent like 'ApacheBench%';
6126\layout LyX-Code
6127
6128mysql> optimize table access_log;
6129\layout Subsubsection
6130
6131Do I need to be worried about all the running MySQL children? Will holding
6132 open
6133\emph on
6134n
6135\emph default
6136 Apache-to-MySQL connections consume a lot of memory?
6137\layout Standard
6138
6139Short answer: you shouldn't be worried.
6140\layout Standard
6141
6142Long answer: you might be evaluating at the output of
6143\begin_inset Quotes eld
6144\end_inset
6145
6146ps -aufxw
6147\begin_inset Quotes erd
6148\end_inset
6149
6150 and becoming alarmed at all the 7MB httpd processes or 22MB mysqld children
6151 that you see.
6152 Don't be alarmed
6153\emph on
6154.
6155
6156\emph default
6157 It's true that mod_log_sql opens and holds open many MySQL connections:
6158 each httpd child maintains one open database connection (and holds it open
6159 for performance reasons).
6160 Four webservers, each running 20 Apache children, will hold open 80 MySQL
6161 connections, which means that your MySQL server needs to handle 80 simultaneous
6162 connections.
6163 In truth, your MySQL server needs to handle far more than that if traffic
6164 to your website spikes and the Apache webservers spawn off an additional
6165 30 children each...
6166\layout Standard
6167
6168Fortunately the cost reported by 'ps -aufxw' is deceptive.
6169 This is due to an OS memory-management feature called
6170\begin_inset Quotes eld
6171\end_inset
6172
6173copy-on-write.
6174\begin_inset Quotes erd
6175\end_inset
6176
6177 When you have a number of identical child processes (e.g.
6178 Apache, MySQL), it would appear in
6179\begin_inset Quotes eld
6180\end_inset
6181
6182ps
6183\begin_inset Quotes erd
6184\end_inset
6185
6186 as though each one occupies a great deal of RAM -- as much as 7MB per httpd
6187 child! In actuality each additional child only occupies a small bit of
6188 extra memory -- most of the memory pages are common to each child and therefore
6189 shared in a
6190\begin_inset Quotes eld
6191\end_inset
6192
6193read-only
6194\begin_inset Quotes erd
6195\end_inset
6196
6197 fashion.
6198 The OS can get away with this because the majority of memory pages for
6199 one child are identical across all children.
6200 Instead of thinking of each child as a rubber stamp of the others, think
6201 of each child as a basket of links to a common memory area.
6202\layout Standard
6203
6204A memory page is only duplicated when it needs to be written to, hence
6205\begin_inset Quotes eld
6206\end_inset
6207
6208copy-on-write.
6209\begin_inset Quotes erd
6210\end_inset
6211
6212 The result is efficiency and decreased memory consumption.
6213
6214\begin_inset Quotes eld
6215\end_inset
6216
6217ps
6218\begin_inset Quotes erd
6219\end_inset
6220
6221 may report 7MB per child, but it might really only
6222\begin_inset Quotes eld
6223\end_inset
6224
6225cost
6226\begin_inset Quotes erd
6227\end_inset
6228
6229 900K of extra memory to add one more child.
6230 It is
6231\series bold
6232not
6233\emph on
6234
6235\emph default
6236correct
6237\series default
6238 to assume that 20 Apache children with a VSZ of 7MB each equals
6239\begin_inset Formula $(20\times 7MB)$
6240\end_inset
6241
6242 of memory consumption -- the real answer is much, much lower.
6243 The same
6244\begin_inset Quotes eld
6245\end_inset
6246
6247copy-on-write
6248\begin_inset Quotes erd
6249\end_inset
6250
6251 rules apply to all your MySQL children: 40 mysqld children @ 22MB each
6252
6253\series bold
6254do not
6255\series default
6256 occupy 880MB of RAM.
6257\layout Standard
6258
6259The bottom line: although there is a cost to spawn extra httpd or mysqld
6260 children, that cost is not as great as
6261\begin_inset Quotes eld
6262\end_inset
6263
6264ps
6265\begin_inset Quotes erd
6266\end_inset
6267
6268 would lead you to believe.
6269\layout Subsubsection
6270
6271My webserver cannot handle all the traffic that my site receives, is there
6272 anything I can do?
6273\layout Standard
6274
6275If you have exhausted all the tuning possibilities on your existing server,
6276 it is probably time you evaluated the benefits of clustering two or more
6277 webservers together in a load-balanced fashion.
6278 In fact, users of such a setup are mod_log_sql's target audience!
6279\layout Subsubsection
6280
6281
6282\begin_inset LatexCommand \label{sub:DelayedInsFAQ}
6283
6284\end_inset
6285
6286What is the issue with activating delayed inserts?
6287\layout Standard
6288
6289There are several.
6290\layout Enumerate
6291
6292INSERT DELAYED is a specific syntax to MySQL and is not supported by any
6293 other database.
6294 Ergo, why is it needed, and what MySQL deficiency is it working around?
6295 INSERT DELAYED is a kluge.
6296\layout Enumerate
6297
6298The MySQL documentation is unclear whether INSERT DELAYED is even necessary
6299 for an optimized database.
6300 It says,
6301\begin_inset Quotes eld
6302\end_inset
6303
6304The DELAYED option for the INSERT statement is a MySQL-specific option that
6305 is very useful if you have clients that can't wait for the INSERT to complete.
6306\begin_inset Quotes erd
6307\end_inset
6308
6309 But then it goes on to say,
6310\begin_inset Quotes eld
6311\end_inset
6312
6313Note that as MyISAM tables supports concurrent SELECT and INSERT, if there
6314 is no free blocks in the middle of the data file, you very seldom need
6315 to use INSERT DELAYED with MyISAM.
6316\begin_inset Quotes erd
6317\end_inset
6318
6319
6320\layout Enumerate
6321
6322Because INSERT DELAYED returns without waiting for the data to be written,
6323 a hard kill of your MySQL database at the right (wrong?) moment could lose
6324 those logfile entries.
6325\layout Enumerate
6326
6327As of MySQL version 3.23.52, the error return functions disagree after a failed
6328 INSERT DELAYED: mysql_errno() always returns 0, even if mysql_error() returns
6329 a textual error.
6330 I have reported this bug to the MySQL folks.
6331 However, we have no way of knowing what solution they will adopt to fix
6332 this, and with the worst case solution mod_log_sql would not be able to
6333 tell if anything went wrong with a delayed insert.
6334\layout Standard
6335
6336Instead of delayed inserts, you may wish to utilize InnoDB tables (instead
6337 of the standard MyISAM tables).
6338 InnoDB tables suppot row-level locking and are recommended for high-volume
6339 databases.
6340\layout Standard
6341
6342If after understanding these problems you still wish to enable delayed inserts,
6343 section
6344\begin_inset LatexCommand \ref{sub:DelayedIns}
6345
6346\end_inset
6347
6348 discusses how.
6349\layout Subsection
6350
6351
6352\begin_inset Quotes eld
6353\end_inset
6354
6355How do I...?
6356\begin_inset Quotes erd
6357\end_inset
6358
6359 -- accomplishing certain tasks
6360\layout Subsubsection
6361
6362I am using LogSQLMassVirtualHosting, and sometimes a single VirtualHost
6363 gets logged to two different tables.
6364 How do I prevent that?
6365\layout Standard
6366
6367Proper usage of the Apache runtime
6368\noun on
6369ServerName
6370\noun default
6371 directive and the directive
6372\noun on
6373UseCanonicalName On
6374\noun default
6375(or
6376\noun on
6377DNS
6378\noun default
6379) are necessary to prevent this problem.
6380
6381\begin_inset Quotes eld
6382\end_inset
6383
6384On
6385\begin_inset Quotes erd
6386\end_inset
6387
6388 is the default for
6389\noun on
6390UseCanonicalName
6391\noun default
6392, and specifies that self-referential URLs are generated from the
6393\noun on
6394ServerName
6395\noun default
6396part of your VirtualHost:
6397\layout Quote
6398
6399With UseCanonicalName on (and in all versions prior to 1.3) Apache will use
6400 the ServerName and Port directives to construct the canonical name for
6401 the server.
6402 With UseCanonicalName off Apache will form self-referential URLs using
6403 the hostname and port supplied by the client if any are supplied (otherwise
6404 it will use the canonical name, as defined above).
6405 [From
6406\begin_inset LatexCommand \url[the Apache documentation]{http://httpd.apache.org/docs/mod/core.html#usecanonicalname}
6407
6408\end_inset
6409
6410]
6411\layout Standard
6412
6413The module inherits Apache's
6414\begin_inset Quotes eld
6415\end_inset
6416
6417knowledge
6418\begin_inset Quotes erd
6419\end_inset
6420
6421 about the server name being accessed.
6422 As long as those two directives are properly configured, mod_log_sql will
6423 log to only one table per virtual host while using
6424\noun on
6425LogSQLMassVirtualHosting
6426\noun default
6427.
6428\layout Subsubsection
6429
6430How do I extract the data in a format that my analysis tool can understand?
6431\layout Standard
6432
6433mod_log_sql would be virtually useless if there weren't a way for you to
6434 extract the data from your database in a somewhat meaningful fashion.
6435 To that end there's a Perl script enclosed with the distribution.
6436 That script (make_combined_log.pl) is designed to extract N-many days worth
6437 of access logs and provide them in a Combined Log Format output.
6438 You can use this very tool right in /etc/crontab to extract logs on a regular
6439 basis so that your favorite web analysis tool can read them.
6440 Or you can examine the Perl code to construct your own custom tool.
6441\layout Standard
6442
6443For example, let's say that you want your web statistics updated once per
6444 day in the wee hours of the morning.
6445 A good way to accomplish that could be the following entries in /etc/crontab:
6446\layout LyX-Code
6447
6448# Generate the temporary apache logs from the MySQL database (for webalizer)
6449
6450\layout LyX-Code
6451
645205 04 * * * root make_combined_log.pl 1 www.grubbybaby.com > /var/log/temp01
6453\layout LyX-Code
6454
6455# Run webalizer on httpd log
6456\layout LyX-Code
6457
645830 04 * * * root webalizer -c /etc/webalizer.conf; rm -f /var/log/temp01
6459\layout Standard
6460
6461Or if you have a newer system that puts files in /etc/cron.daily etc., create
6462 a file called
6463\begin_inset Quotes eld
6464\end_inset
6465
6466webalizer
6467\begin_inset Quotes srd
6468\end_inset
6469
6470 in the cron.daily subdirectory.
6471 Use the following as the contents of your file, and make sure to chmod
6472 755 it when done.
6473\layout LyX-Code
6474
6475#!/bin/sh
6476\layout LyX-Code
6477
6478/usr/local/sbin/make_combined_log.pl 1 www.yourdomain.com > /var/log/httpd/templog
6479\layout LyX-Code
6480
6481/usr/local/bin/webalizer -q -c /etc/webalizer.conf
6482\layout LyX-Code
6483
6484rm -f /var/log/httpd/templog
6485\layout Standard
6486
6487See? Easy.
6488\layout Subsubsection
6489
6490
6491\begin_inset LatexCommand \label{sec:cookie}
6492
6493\end_inset
6494
6495How can I log mod_usertrack cookies?
6496\layout Standard
6497
6498A number of people like to log mod_usertrack cookies in their Apache TransferLog
6499 to aid in understanding their visitors' clickstreams.
6500 This is accomplished, for example, with a statement as follows:
6501\layout LyX-Code
6502
6503LogFormat "%h %l %u %t
6504\backslash
6505"%r
6506\backslash
6507" %s %b
6508\backslash
6509"%{Referer}i
6510\backslash
6511"
6512\backslash
6513"%{User-Agent}i
6514\backslash
6515""
6516\backslash
6517"%{cookie}n
6518\backslash
6519""
6520\layout Standard
6521
6522Naturally it would be nice for mod_log_sql to permit the admin to log the
6523 cookie data as well, so as of version 1.10 you can do this.
6524 You need to have already compiled mod_usertrack into httpd -- it's one
6525 of the standard Apache modules.
6526\layout Standard
6527
6528First make sure you have a column called "cookie" in the MySQL database
6529 to hold the cookies, which can be done as follows if you already have a
6530 working database:
6531\layout LyX-Code
6532
6533alter table acc_log_tbl add column cookie varchar(255);
6534\layout Standard
6535
6536Next configure your server to set usertracking cookies as follows, and make
6537 sure you include the new 'c' directive in your
6538\noun on
6539LogSQLTransferLogFormat
6540\noun default
6541, which activates cookie logging.
6542 Here's an example:
6543\layout LyX-Code
6544
6545<VirtualHost 1.2.3.4>
6546\layout LyX-Code
6547
6548 CookieTracking on
6549\layout LyX-Code
6550
6551 CookieStyle Cookie
6552\layout LyX-Code
6553
6554 CookieName Foobar
6555\layout LyX-Code
6556
6557 LogSQLTransferLogFormat huSUsbTvRAc
6558\layout LyX-Code
6559
6560 LogSQLWhichCookie Foobar
6561\layout LyX-Code
6562
6563</VirtualHost>
6564\layout Standard
6565
6566The first three lines configure mod_usertrack to create a COOKIE (RFC 2109)
6567 format cookie called Foobar.
6568 The last two lines tell mod_log_sql to log cookies named Foobar.
6569 You have to choose which cookie to log because more than one cookie can/will
6570 be sent to the server by the client.
6571\layout Standard
6572
6573Recap: the 'c' character
6574\emph on
6575activates
6576\emph default
6577 cookie logging, and the
6578\noun on
6579LogSQLWhichCookie
6580\noun default
6581 directive
6582\emph on
6583chooses
6584\emph default
6585which cookie to log.
6586\layout Standard
6587
6588FYI, you are advised NOT to use
6589\noun on
6590CookieStyle Cookie2
6591\noun default
6592 -- it seems that even newer browsers (IE 5.5, etc.) have trouble with the
6593 new COOKIE2 (RFC 2965) format.
6594 Just stick with the standard COOKIE format and you'll be fine.
6595\layout Standard
6596
6597Perform some hits on your server and run a select:
6598\layout LyX-Code
6599
6600mysql> select request_uri,cookie from access_log where cookie is not null;
6601\layout LyX-Code
6602
6603\layout Standard
6604\align center
6605
6606\begin_inset Tabular
6607<lyxtabular version="3" rows="4" columns="2">
6608<features>
6609<column alignment="left" valignment="top" leftline="true" width="0pt">
6610<column alignment="left" valignment="top" leftline="true" rightline="true" width="0pt">
6611<row topline="true" bottomline="true">
6612<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
6613\begin_inset Text
6614
6615\layout Standard
6616
6617request_uri
6618\end_inset
6619</cell>
6620<cell alignment="center" valignment="top" topline="true" leftline="true" rightline="true" usebox="none">
6621\begin_inset Text
6622
6623\layout Standard
6624
6625cookie
6626\end_inset
6627</cell>
6628</row>
6629<row topline="true">
6630<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
6631\begin_inset Text
6632
6633\layout Standard
6634
6635/mod_log_sql/
6636\end_inset
6637</cell>
6638<cell alignment="center" valignment="top" topline="true" leftline="true" rightline="true" usebox="none">
6639\begin_inset Text
6640
6641\layout Standard
6642
6643ool-18e4.dyn.optonline.net.130051007102700823
6644\end_inset
6645</cell>
6646</row>
6647<row topline="true">
6648<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
6649\begin_inset Text
6650
6651\layout Standard
6652
6653/mod_log_sql/usa.gif
6654\end_inset
6655</cell>
6656<cell alignment="center" valignment="top" topline="true" leftline="true" rightline="true" usebox="none">
6657\begin_inset Text
6658
6659\layout Standard
6660
6661ool-18e4.dyn.optonline.net.130051007102700823
6662\end_inset
6663</cell>
6664</row>
6665<row topline="true" bottomline="true">
6666<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
6667\begin_inset Text
6668
6669\layout Standard
6670
6671/mod_log_sql/style_1.css
6672\end_inset
6673</cell>
6674<cell alignment="center" valignment="top" topline="true" leftline="true" rightline="true" usebox="none">
6675\begin_inset Text
6676
6677\layout Standard
6678
6679ool-18e4.dyn.optonline.net.130051007102700823
6680\end_inset
6681</cell>
6682</row>
6683</lyxtabular>
6684
6685\end_inset
6686
6687
6688\layout LyX-Code
6689
6690\layout Subsubsection
6691
6692What if I want to log more than one cookie? What is the difference between
6693 LogSQLWhichCookie and LogSQLWhichCookies?
6694\layout Standard
6695
6696As of version 1.17, you have a choice in how you want cookie logging handled.
6697\layout Standard
6698
6699If you are interested in logging only one cookie per request, follow the
6700 instructions in section
6701\begin_inset LatexCommand \ref{sec:cookie}
6702
6703\end_inset
6704
6705 above.
6706 That cookie will be logged to a column in the regular access_log table,
6707 and the actual cookie you want to log is specified with
6708\noun on
6709 LogSQLWhichCookie
6710\noun default
6711.
6712 Don't forget to specify the 'c' character in
6713\noun on
6714LogSQLTransferLogFormat
6715\noun default
6716.
6717\layout Standard
6718
6719If, however, you need to log multiple cookies per request, you must employ
6720 the
6721\noun on
6722LogSQLWhichCookies
6723\noun default
6724 (note the plural) directive.
6725 The cookies you specify will be logged to a separate table (as discussed
6726 in section
6727\begin_inset LatexCommand \ref{secMulTable}
6728
6729\end_inset
6730
6731), and entries in that table will be linked to the regular access_log entries
6732 via the unique ID that is supplied by mod_unique_id.
6733 Without mod_unique_id the information will still be logged but you will
6734 be unable to correlate which cookies go with which access-requests.
6735 Furthermore, with
6736\noun on
6737LogSQLWhichCookies
6738\noun default
6739, you do
6740\series bold
6741not
6742\series default
6743 need to include the 'c' character in
6744\noun on
6745LogSQLTransferLogFormat
6746\noun default
6747.
6748\layout Standard
6749
6750
6751\noun on
6752LogSQLWhichCookie
6753\noun default
6754 and
6755\noun on
6756LogSQLWhichCookies
6757\noun default
6758 can coexist without conflict because they operate on entireley different
6759 tables, but you're better off choosing the one you need.
6760\layout Subsubsection
6761
6762What are the SSL logging features, and how do I activate them?
6763\layout Standard
6764
6765Note: you do
6766\series bold
6767not
6768\series default
6769need to compile SSL support into mod_log_sql in order to simply use it with
6770 a secure site.
6771 You only need to compile SSL support into mod_log_sql if you want to log
6772 SSL-specific data such as the cipher type used, or the keysize that was
6773 negotiated.
6774 If that information is unimportant to you, you can ignore this FAQ.
6775\layout Standard
6776
6777By adding certain characters to your
6778\noun on
6779LogSQLTransferLogFormat
6780\noun default
6781 string you can tell mod_log_sql to log the SSL cipher, the SSL keysize
6782 of the connection, and the maximum keysize that was available.
6783 This would let you tell, for example, which clients were using only export-grad
6784e security to access your secure software area.
6785\layout Standard
6786
6787You can compile mod_log_sql with SSL logging support if you have the right
6788 packages installed.
6789 If you already have an SSL-enabled Apache then you by definition have the
6790 correct packages already installed: OpenSSL and mod_ssl.
6791\layout Standard
6792
6793You need to ensure that your database is set up to log the SSL data.
6794 Issue the following commands to MySQL if your access table does not already
6795 have them:
6796\layout LyX-Code
6797
6798alter table access_log add column ssl_cipher varchar(25);
6799\layout LyX-Code
6800
6801alter table access_log add column ssl_keysize smallint unsigned;
6802\layout LyX-Code
6803
6804alter table access_log add column ssl_maxkeysize smallint unsigned;
6805\layout Standard
6806
6807Finally configure httpd.conf to activate the SSL fields.
6808 Note that this is only meaningful in a VirtualHost that is set up for SSL.
6809\layout LyX-Code
6810
6811<VirtualHost 1.2.3.4:443>
6812\layout LyX-Code
6813
6814 LogSQLTransferLogFormat AbHhmRSsTUuvc
6815\series bold
6816Qqz
6817\series default
6818
6819\layout LyX-Code
6820
6821</VirtualHost>
6822\layout Standard
6823
6824The last three characters (Qqz) in the directive are the SSL ones; see section
6825
6826\begin_inset LatexCommand \ref{sub:Frmat}
6827
6828\end_inset
6829
6830 in the directives documentation for details of the
6831\noun on
6832LogSQLTransferLogFormat
6833\noun default
6834 directive.
6835\layout Standard
6836
6837Restart Apache, then perform some hits on your server.
6838 Then run the following select statement:
6839\layout LyX-Code
6840
6841mysql> select remote_host,request_uri,ssl_cipher,ssl_keysize,ssl_maxkeysize
6842\layout LyX-Code
6843
6844from access_log where ssl_cipher is not null;
6845\layout LyX-Code
6846
6847\layout Standard
6848\align center
6849
6850\begin_inset Tabular
6851<lyxtabular version="3" rows="4" columns="5">
6852<features>
6853<column alignment="left" valignment="top" leftline="true" width="0pt">
6854<column alignment="left" valignment="top" leftline="true" width="0pt">
6855<column alignment="left" valignment="top" leftline="true" width="0pt">
6856<column alignment="left" valignment="top" leftline="true" width="0pt">
6857<column alignment="left" valignment="top" leftline="true" rightline="true" width="0pt">
6858<row topline="true" bottomline="true">
6859<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
6860\begin_inset Text
6861
6862\layout Standard
6863
6864remote_host
6865\end_inset
6866</cell>
6867<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
6868\begin_inset Text
6869
6870\layout Standard
6871
6872request_uri
6873\end_inset
6874</cell>
6875<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
6876\begin_inset Text
6877
6878\layout Standard
6879
6880ssl_cipher
6881\end_inset
6882</cell>
6883<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
6884\begin_inset Text
6885
6886\layout Standard
6887
6888ssl_keysize
6889\end_inset
6890</cell>
6891<cell alignment="center" valignment="top" topline="true" leftline="true" rightline="true" usebox="none">
6892\begin_inset Text
6893
6894\layout Standard
6895
6896ssl_maxkeysize
6897\end_inset
6898</cell>
6899</row>
6900<row topline="true">
6901<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
6902\begin_inset Text
6903
6904\layout Standard
6905
6906216.190.52.4
6907\end_inset
6908</cell>
6909<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
6910\begin_inset Text
6911
6912\layout Standard
6913
6914/dir/somefile.html
6915\end_inset
6916</cell>
6917<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
6918\begin_inset Text
6919
6920\layout Standard
6921
6922RC4-MD5
6923\end_inset
6924</cell>
6925<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
6926\begin_inset Text
6927
6928\layout Standard
6929
6930128
6931\end_inset
6932</cell>
6933<cell alignment="center" valignment="top" topline="true" leftline="true" rightline="true" usebox="none">
6934\begin_inset Text
6935
6936\layout Standard
6937
6938128
6939\end_inset
6940</cell>
6941</row>
6942<row topline="true">
6943<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
6944\begin_inset Text
6945
6946\layout Standard
6947
6948216.190.52.4
6949\end_inset
6950</cell>
6951<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
6952\begin_inset Text
6953
6954\layout Standard
6955
6956/dir/somefile.gif
6957\end_inset
6958</cell>
6959<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
6960\begin_inset Text
6961
6962\layout Standard
6963
6964RC4-MD5
6965\end_inset
6966</cell>
6967<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
6968\begin_inset Text
6969
6970\layout Standard
6971
6972128
6973\end_inset
6974</cell>
6975<cell alignment="center" valignment="top" topline="true" leftline="true" rightline="true" usebox="none">
6976\begin_inset Text
6977
6978\layout Standard
6979
6980128
6981\end_inset
6982</cell>
6983</row>
6984<row topline="true" bottomline="true">
6985<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
6986\begin_inset Text
6987
6988\layout Standard
6989
6990216.190.52.4
6991\end_inset
6992</cell>
6993<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
6994\begin_inset Text
6995
6996\layout Standard
6997
6998/dir/somefile.jpg
6999\end_inset
7000</cell>
7001<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
7002\begin_inset Text
7003
7004\layout Standard
7005
7006RC4-MD5
7007\end_inset
7008</cell>
7009<cell alignment="center" valignment="top" topline="true" leftline="true" usebox="none">
7010\begin_inset Text
7011
7012\layout Standard
7013
7014128
7015\end_inset
7016</cell>
7017<cell alignment="center" valignment="top" topline="true" leftline="true" rightline="true" usebox="none">
7018\begin_inset Text
7019
7020\layout Standard
7021
7022128
7023\end_inset
7024</cell>
7025</row>
7026</lyxtabular>
7027
7028\end_inset
7029
7030
7031\layout LyX-Code
7032
7033\the_end
generated by cgit (git 2.34.1) at 2026-11-12 09:38:01 +0000