1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
|
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2 Final//EN">
<!--Converted with LaTeX2HTML 2002-1 (1.68)
original version by: Nikos Drakos, CBLU, University of Leeds
* revised and updated by: Marcus Hennecke, Ross Moore, Herb Swan
* with significant contributions from:
Jens Lippmann, Marek Rouchal, Martin Wilck and others -->
<HTML>
<HEAD>
<TITLE>1 Introduction</TITLE>
<META NAME="description" CONTENT="1 Introduction">
<META NAME="keywords" CONTENT="documentation">
<META NAME="resource-type" CONTENT="document">
<META NAME="distribution" CONTENT="global">
<META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=iso-8859-1">
<META NAME="Generator" CONTENT="LaTeX2HTML v2002-1">
<META HTTP-EQUIV="Content-Style-Type" CONTENT="text/css">
<LINK REL="STYLESHEET" HREF="documentation.css">
<LINK REL="next" HREF="node3.html">
<LINK REL="previous" HREF="node1.html">
<LINK REL="up" HREF="documentation.html">
<LINK REL="next" HREF="node3.html">
</HEAD>
<BODY >
<!--Navigation Panel-->
<A NAME="tex2html134"
HREF="node3.html">
<IMG WIDTH="37" HEIGHT="24" ALIGN="BOTTOM" BORDER="0" ALT="next" SRC="next.png"></A>
<A NAME="tex2html130"
HREF="documentation.html">
<IMG WIDTH="26" HEIGHT="24" ALIGN="BOTTOM" BORDER="0" ALT="up" SRC="up.png"></A>
<A NAME="tex2html124"
HREF="node1.html">
<IMG WIDTH="63" HEIGHT="24" ALIGN="BOTTOM" BORDER="0" ALT="previous" SRC="prev.png"></A>
<A NAME="tex2html132"
HREF="node1.html">
<IMG WIDTH="65" HEIGHT="24" ALIGN="BOTTOM" BORDER="0" ALT="contents" SRC="contents.png"></A>
<BR>
<B> Next:</B> <A NAME="tex2html135"
HREF="node3.html">2 Installation</A>
<B> Up:</B> <A NAME="tex2html131"
HREF="documentation.html">Installing and Running mod_log_sql</A>
<B> Previous:</B> <A NAME="tex2html125"
HREF="node1.html">Contents</A>
<B> <A NAME="tex2html133"
HREF="node1.html">Contents</A></B>
<BR>
<BR>
<!--End of Navigation Panel-->
<!--Table of Child-Links-->
<A NAME="CHILD_LINKS"><STRONG>Subsections</STRONG></A>
<UL>
<LI><A NAME="tex2html136"
HREF="node2.html#SECTION00021000000000000000">1.1 Homepage </A>
<LI><A NAME="tex2html137"
HREF="node2.html#SECTION00022000000000000000">1.2 Summary</A>
<LI><A NAME="tex2html138"
HREF="node2.html#SECTION00023000000000000000">1.3 Approach</A>
<LI><A NAME="tex2html139"
HREF="node2.html#SECTION00024000000000000000">1.4 What gets logged by default? </A>
<LI><A NAME="tex2html140"
HREF="node2.html#SECTION00025000000000000000">1.5 Miscellaneous Notes</A>
<LI><A NAME="tex2html141"
HREF="node2.html#SECTION00026000000000000000">1.6 Author / Maintainer</A>
</UL>
<!--End of Table of Child-Links-->
<HR>
<H1><A NAME="SECTION00020000000000000000">
1 Introduction</A>
</H1>
<P>
<H2><A NAME="SECTION00021000000000000000">
1.1 Homepage </A>
</H2>
<P>
<DL COMPACT>
<DT>
<DD>http://www.grubbybaby.com/mod_log_sql/
</DD>
</DL>
<P>
<H2><A NAME="SECTION00022000000000000000">
1.2 Summary</A>
</H2>
<P>
This Apache module will permit you to log to a SQL database; it can
log each access request as well as data associated with each request:
cookies, notes, and inbound/outbound headers. Unlike logging to a
flat text file - which is standard in Apache - a SQL-based log exhibits
tremendous flexibility and power of data extraction. (See section
<A HREF="node5.html#sub:why">4.1.1</A> in the FAQ for further discussion and examples of the
advantages to SQL.)
<P>
This module can either replace or happily coexist with mod_log_config,
Apache's text file logging facility. In addition to being more configurable
than the standard module, mod_log_sql is much more flexible.
<P>
<H2><A NAME="SECTION00023000000000000000">
1.3 Approach</A>
</H2>
<P>
This project was formerly known as ``mod_log_mysql.'' It was
renamed ``mod_log_sql'' in order to reflect the project goal
of database-inspecificity. The module currently supports MySQL, but
support for other database backends is underway.
<P>
In order to save speed and overhead, links are kept alive in between
queries. This module uses one dedicated SQL link per httpd child,
opened by each child process when it is born. Among other things,
this means that this module supports logging into only one MySQL server,
and for now, also, only one SQL database. But that's a small tradeoff
compared to the blinding speed of this module. Error reporting is
robust throughout the module and will inform the administrator of
database issues in the Apache E<SMALL>RROR</SMALL>L<SMALL>OG</SMALL> for the server/virtual
server.
<P>
Virtual hosts are supported in the same manner they are in the regular
logging modules. The administrator defines some basic 'global' directives
in the main server config, then defines more specific 'local' directives
inside each VirtualHost stanza.
<P>
A robust "preserve" capability has now been implemented.
This permits the module to preserve any failed INSERT commands to
a local file on its machine. In any situation that the database is
unavailable - e.g. the network fails or the database host is rebooted
- mod_log_sql will note this in the error log and begin appending
its log entries to the preserve file (which is created with the user
& group ID of the running Apache process, e.g. "nobody/nobody"
on many Linux installations). When database availablity returns, mod_log_sql
seamlessly resumes logging to it. When convenient for the sysadmin,
he/she can easily import the preserve file into the database because
it is simply a series of SQL insert statements.
<P>
<H2><A NAME="SECTION00024000000000000000">
1.4 What gets logged by default? </A>
</H2>
<P>
All the data that would be contained in the "Combined Log
Format" is logged by default, plus a little extra. Your best
bet is to begin by accepting this default, then later customize the
log configuration based on your needs.
<P>
The documentation of the run-time directives includes a full explanation
of what you can log, including examples - see section <A HREF="node4.html#sec:ConfRef">3.6</A>.
<P>
<H2><A NAME="SECTION00025000000000000000">
1.5 Miscellaneous Notes</A>
</H2>
<P>
<UL>
<LI>Note which directives go in the 'main server config' and which directives
apply to the 'virtual host config'. This is made clear in the directive
documentation.
</LI>
<LI>The 'time_stamp' field is stored in an UNSIGNED INTEGER column, in
the standard unix ``seconds since the epoch'' format. This is
superior to storing the access time as a string due to size requirements:
an UNSIGNED INT requires 4 bytes, whereas an Apache date string -
e.g. "18/Nov/2001:13:59:52 -0800" - requires 26
bytes: those extra 22 bytes become significant when multiplied by
thousands of accesses on a busy server. Besides, an INT type is far
more flexible for comparisons, etc.
<P>
In MySQL 3.21 and above you can easily convert this to a human readable
format using from_unixtime(), e.g.:
<P>
<DL COMPACT>
<DT>
<DD>select remote_host,request_uri,from_unixtime(time_stamp) from access_log;
</DD>
</DL>The enclosed perl program ``make_combined_log.pl'' extracts
your access log in a format that is completely compatible with the
Combined Log Format. You can then feed this to your favorite web log
analysis tool.
<P>
</LI>
<LI>The table's string values can be CHAR or VARCHAR, at a length of your
choice. VARCHAR is superior because it truncates long strings; CHAR
types are fixed-length and will be padded with spaces, resulting in
waste. Just like the time_stamp issue described above, that kind
of space waste multiplies over thousands of records.
</LI>
<LI>Be careful not to go overboard setting fields to NOT NULL. If a field
is marked NOT NULL then it must contain data in the INSERT statement,
or the INSERT will fail. These mysterious failures can be quite frustrating
and difficult to debug.
</LI>
<LI>When Apache logs a numeric field, it uses a '-' character to mean
``not applicable,'' e.g. the number of bytes returned on a 304
(unchanged) request. Since '-' is an illegal character in an SQL numeric
field, such fields are assigned the value 0 instead of '-' which,
of course, makes perfect sense anyway.
</LI>
</UL>
<P>
<H2><A NAME="SECTION00026000000000000000">
1.6 Author / Maintainer</A>
</H2>
<P>
The actual logging code was taken from the already existing flat file
text modules, so all that credit goes to the Apache Server group.
<P>
The MySQL routines and directives were added by Zeev Suraski <bourbon@netvision.net.il>.
<P>
All changes from 1.06+ and the new documentation were added by Chris
Powell <chris@grubbybaby.com>. It seems that the module had fallen
into the "unmaintained" category - it hadn't been
updated since 1998 - so Chris adopted it as the new maintainer.
<P>
<HR>
<!--Navigation Panel-->
<A NAME="tex2html134"
HREF="node3.html">
<IMG WIDTH="37" HEIGHT="24" ALIGN="BOTTOM" BORDER="0" ALT="next" SRC="next.png"></A>
<A NAME="tex2html130"
HREF="documentation.html">
<IMG WIDTH="26" HEIGHT="24" ALIGN="BOTTOM" BORDER="0" ALT="up" SRC="up.png"></A>
<A NAME="tex2html124"
HREF="node1.html">
<IMG WIDTH="63" HEIGHT="24" ALIGN="BOTTOM" BORDER="0" ALT="previous" SRC="prev.png"></A>
<A NAME="tex2html132"
HREF="node1.html">
<IMG WIDTH="65" HEIGHT="24" ALIGN="BOTTOM" BORDER="0" ALT="contents" SRC="contents.png"></A>
<BR>
<B> Next:</B> <A NAME="tex2html135"
HREF="node3.html">2 Installation</A>
<B> Up:</B> <A NAME="tex2html131"
HREF="documentation.html">Installing and Running mod_log_sql</A>
<B> Previous:</B> <A NAME="tex2html125"
HREF="node1.html">Contents</A>
<B> <A NAME="tex2html133"
HREF="node1.html">Contents</A></B>
<!--End of Navigation Panel-->
<ADDRESS>
Chris Powell
2002-12-18
</ADDRESS>
</BODY>
</HTML>
|
