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