"Fossies" - the Fresh Open Source Software Archive 
"Fossies" - the Fresh Open Source Software Archive A hint: This file contains one or more very long lines, so maybe it is better readable using the pure text view mode that shows the contents as wrapped lines within the browser window.
1 <?xml version="1.0" encoding="UTF-8"?> 2 <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> 3 <html xmlns="http://www.w3.org/1999/xhtml"><head><meta http-equiv="Content-Type" content="text/html; charset=UTF-8" /><title>mod_log_sql Manual</title><meta name="generator" content="DocBook XSL Stylesheets V1.61.2" /></head><body><div class="article" lang="en" xml:lang="en"><div class="titlepage"><div><div><h1 class="title"><a id="id2865629"></a>mod_log_sql Manual</h1></div><div><div class="author"><h3 class="author"><span class="firstname">Edward</span> <span class="surname">Rudd</span></h3><span class="contrib">Conversion from Lyx to DocBook</span><span class="contrib">Current Maintainer</span></div></div><div><div class="author"><h3 class="author"><span class="firstname">Christopher</span> <span class="othername">B.</span> <span class="surname">Powell</span></h3><span class="contrib">Original documentation author.</span></div></div><div><p class="copyright">Copyright © 2001, 2002, 2003 Christopher B. Powell</p></div><div><p class="copyright">Copyright © 2004, 2005, 2006 Edward Rudd</p></div><div><div class="revhistory"><table border="1" width="100%" summary="Revision history"><tr><th align="left" valign="top" colspan="2"><b>Revision History</b></th></tr><tr><td align="left">Revision 1.5</td><td align="left">2006-11-04</td></tr><tr><td align="left" colspan="2">Added documentation about logio parameters and added DBParam Mysql driver parameters (including tabletype)</td></tr><tr><td align="left">Revision 1.4</td><td align="left">2006-02-13</td></tr><tr><td align="left" colspan="2">Added missing logformat types, switched to simplified docbook 1.1</td></tr><tr><td align="left">Revision 1.3</td><td align="left">2005-01-11</td></tr><tr><td align="left" colspan="2">Updated for mod_log_sql v1.100</td></tr><tr><td align="left">Revision 1.2</td><td align="left">2004-04-08</td></tr><tr><td align="left" colspan="2">Updated for mod_log_sql v1.97</td></tr><tr><td align="left">Revision 1.1</td><td align="left">2004-03-02</td></tr><tr><td align="left" colspan="2">Updated for mod_log_sql v1.96</td></tr><tr><td align="left">Revision 1.0</td><td align="left">2004-01-22</td></tr><tr><td align="left" colspan="2">Initial Conversion from Lyx to Docbook</td></tr></table></div></div></div><div></div><hr /></div><div class="toc"><p><b>Table of Contents</b></p><dl><dt><a href="#id2865807">Introduction</a></dt><dd><dl><dt><a href="#id2865817">Summary</a></dt><dt><a href="#id2865851">Approach</a></dt><dt><a href="#id2865905">What gets logged by default?</a></dt><dt><a href="#id2865930">Miscellaneous Notes</a></dt><dt><a href="#id2866024">Author / Maintainer</a></dt><dt><a href="#Sect.MailingLists">Mailing Lists</a></dt></dl></dd><dt><a href="#id2866116">Installation</a></dt><dd><dl><dt><a href="#id2866121">Requirements</a></dt><dt><a href="#id2866193">Compiling and Installing</a></dt></dl></dd><dt><a href="#Sect.Configuration">Configuration</a></dt><dd><dl><dt><a href="#Sect.Preperation"> 4 Preparing MySQL for logging 5 </a></dt><dt><a href="#id2867211">A very basic logging setup in Apache</a></dt><dt><a href="#id2867316">Testing the basic setup</a></dt><dt><a href="#id2867365">How to tune logging with run-time directives</a></dt><dt><a href="#id2867646">Advanced logging scenarios</a></dt><dt><a href="#Sect.ConfigReference"> 6 Configuration Directive Reference 7 </a></dt></dl></dd><dt><a href="#Sect.FAQ">FAQ</a></dt></dl></div><div class="section" lang="en" xml:lang="en"><div class="titlepage"><div><div><h2 class="title" style="clear: both"><a id="id2865807"></a>Introduction</h2></div></div><div></div></div><div class="section" lang="en" xml:lang="en"><div class="titlepage"><div><div><h3 class="title"><a id="id2865817"></a>Summary</h3></div></div><div></div></div><p> 8 This Apache module will permit you to log to a SQL database; it 9 can log each access request as well as data associated with each 10 request: cookies, notes, and inbound/outbound headers. Unlike 11 logging to a flat text file -- which is standard in Apache -- a 12 SQL-based log exhibits tremendous flexibility and power of data 13 extraction. (See FAQ entry 14 <a href="#FAQ.WhyLogToSQL">Q: 1.1</a> 15 for further discussion and examples of the advantages to SQL.) 16 </p><p> 17 This module can either replace or happily coexist with 18 mod_log_config, Apache's text file logging facility. In addition 19 to being more configurable than the standard module, mod_log_sql 20 is much more flexible. 21 </p></div><div class="section" lang="en" xml:lang="en"><div class="titlepage"><div><div><h3 class="title"><a id="id2865851"></a>Approach</h3></div></div><div></div></div><p> 22 This project was formerly known as "mod_log_mysql." It was 23 renamed "mod_log_sql" in order to reflect the project goal of 24 database in-specificity. The module currently supports MySQL, 25 but support for other database back-ends is underway. 26 </p><p> 27 In order to save speed and overhead, links are kept alive in 28 between queries. This module uses one dedicated SQL link per 29 httpd child, opened by each child process when it is born. Among 30 other things, this means that this module supports logging into 31 only one MySQL server, and for now, also, only one SQL database. 32 But that's a small tradeoff compared to the blinding speed of 33 this module. Error reporting is robust throughout the module and 34 will inform the administrator of database issues in the Apache 35 ErrorLog for the server/virtual server. 36 </p><p> 37 Virtual hosts are supported in the same manner they are in the 38 regular logging modules. The administrator defines some basic 39 'global' directives in the main server config, then defines more 40 specific 'local' directives inside each VirtualHost stanza. 41 </p><p> 42 A robust "preserve" capability has now been implemented. This 43 permits the module to preserve any failed INSERT commands to a 44 local file on its machine. In any situation that the database is 45 unavailable -- e.g. the network fails or the database host is 46 rebooted -- mod_log_sql will note this in the error log and 47 begin appending its log entries to the preserve file (which is 48 created with the user and group ID of the running Apache 49 process, e.g. "nobody/nobody" on many Linux installations). When 50 database availability returns, mod_log_sql seamlessly resumes 51 logging to it. When convenient for the sysadmin, he/she can 52 easily import the preserve file into the database because it is 53 simply a series of SQL insert statements. 54 </p></div><div class="section" lang="en" xml:lang="en"><div class="titlepage"><div><div><h3 class="title"><a id="id2865905"></a>What gets logged by default?</h3></div></div><div></div></div><p> 55 All the data that would be contained in the "Combined Log 56 Format" is logged by default, plus a little extra. Your best bet 57 is to begin by accepting this default, then later customize the 58 log configuration based on your needs. The documentation of the 59 run-time directives includes a full explanation of what you can 60 log, including examples -- see section 61 <a href="#Sect.ConfigReference"> 62 Configuration Directive Reference 63 </a> 64 . 65 </p></div><div class="section" lang="en" xml:lang="en"><div class="titlepage"><div><div><h3 class="title"><a id="id2865930"></a>Miscellaneous Notes</h3></div></div><div></div></div><div class="itemizedlist"><ul type="disc"><li><p> 66 Note which directives go in the 'main server config' and 67 which directives apply to the 'virtual host config'. This is 68 made clear in the directive documentation. 69 </p></li><li><p> 70 The 'time_stamp' field is stored in an UNSIGNED INTEGER 71 format, in the standard unix "seconds since the epoch" 72 format. This is superior to storing the access time as a 73 string due to size requirements: an UNSIGNED INT requires 4 74 bytes, whereas an Apache date string (e.g. 75 "18/Nov/2001:13:59:52 -0800") requires 26 bytes: those extra 76 22 bytes become significant when multiplied by thousands of 77 accesses on a busy server. Besides, an INT type is far more 78 flexible for comparisons, etc. 79 </p><p> 80 In MySQL 3.21 and above you can easily convert this to a 81 human readable format using from_unixtime(), e.g.: 82 </p><pre class="programlisting">SELECT remote_host,request_uri,from_unixtime(time_stamp) 83 FROM access_log;</pre><p> 84 The enclosed perl program "make_combined_log.pl" extracts 85 your access log in a format that is completely compatible 86 with the Combined Log Format. You can then feed this to your 87 favorite web log analysis tool. 88 </p></li><li><p> 89 The table's string values can be CHAR or VARCHAR, at a 90 length of your choice. VARCHAR is superior because it 91 truncates long strings; CHAR types are fixed-length and will 92 be padded with spaces, resulting in waste. Just like the 93 time_stamp issue described above, that kind of space waste 94 multiplies over thousands of records. 95 </p></li><li><p> 96 Be careful not to go overboard setting fields to NOT NULL. 97 If a field is marked NOT NULL then it must contain data in 98 the INSERT statement, or the INSERT will fail. These 99 mysterious failures can be quite frustrating and difficult 100 to debug. 101 </p></li><li><p> 102 When Apache logs a numeric field, it uses a '-' character to 103 mean "not applicable," e.g. the number of bytes returned on 104 a 304 (unchanged) request. Since '-' is an illegal character 105 in an SQL numeric field, such fields are assigned the value 106 0 instead of '-' which, of course, makes perfect sense 107 anyway. 108 </p></li></ul></div></div><div class="section" lang="en" xml:lang="en"><div class="titlepage"><div><div><h3 class="title"><a id="id2866024"></a>Author / Maintainer</h3></div></div><div></div></div><p> 109 The actual logging code was taken from the already existing flat 110 file text modules, so all that credit goes to the Apache 111 Software Foundation. 112 </p><p> 113 The MySQL routines and directives were added by Zeev Suraski 114 <bourbon@netvision.net.il>. 115 </p><p> 116 All changes from 1.06+ and the new documentation were added by 117 Chris Powell 118 <tt class="email"><<a href="mailto:chris <at> grubbybaby <dot> com">chris <at> grubbybaby <dot> com</a>></tt> 119 . It seems that the module had fallen into the "un-maintained" 120 category -- it had not been updated since 1998 -- so Chris 121 adopted it as the new maintainer. 122 </p><p> 123 In December of 2003, Edward Rudd 124 <tt class="email"><<a href="mailto:urkle <at> outoforder <dot> cc">urkle <at> outoforder <dot> cc</a>></tt> 125 porting the module to Apache 2.0, cleaning up the code, 126 converting the documentation to DocBook, optimizing the main 127 logging loop, and added the much anticipated database 128 abstraction layer. 129 </p><p> 130 As of February 2004, Chris Powell handed over maintenance of the 131 module over to Edward Rudd. So you should contact Edward Rudd 132 about the module from now on. 133 </p></div><div class="section" lang="en" xml:lang="en"><div class="titlepage"><div><div><h3 class="title"><a id="Sect.MailingLists"></a>Mailing Lists</h3></div></div><div></div></div><p> 134 A general discussion and support mailing list is provided for 135 mod_log_sq at lists.outoforder.cc. To subscribe to the mailing 136 list send a blank e-mail to 137 mod_log_sql-subscribe@lists.outoforder.cc. The list archives can 138 be accessed via Gmane.org's mailng list gateway via any new 139 reader 140 <a href="news://news.gmane.org/gmane.comp.apache.mod-log-sql" target="_top"> 141 news://news.gmane.org/gmane.comp.apache.mod-log-sql 142 </a> 143 , or via a web browser at 144 <a href="http://news.gmane.org/gmane.comp.apache.mod-log-sql" target="_top"> 145 http://news.gmane.org/gmane.comp.apache.mod-log-sql 146 </a> 147 . 148 </p></div></div><div class="section" lang="en" xml:lang="en"><div class="titlepage"><div><div><h2 class="title" style="clear: both"><a id="id2866116"></a>Installation</h2></div></div><div></div></div><div class="section" lang="en" xml:lang="en"><div class="titlepage"><div><div><h3 class="title"><a id="id2866121"></a>Requirements</h3></div></div><div></div></div><div class="itemizedlist"><ul type="disc"><li><p> 149 A compatible system. mod_log_sql was authored and tested on 150 systems based on Red Hat Linux (Red Hat, Mandrake), but the 151 module should easily adapt to any modern distribution. 152 mod_log_sql has also been ported successfully to Solaris and 153 FreeBSD. 154 </p></li><li><p> 155 Apache 1.3 or 2.0, 1.2 is no longer supported, but may still 156 compile. Ideally you should already have successfully 157 compiled Apache and understand the process, but this 158 document tries to make it simple for beginners. 159 </p></li><li><p> 160 The MySQL development headers. This package is called 161 different things on different distributions. For example, 162 Red Hat 6.x calls this RPM "MySQL-devel" whereas Mandrake 163 calls it "libmysql10-devel." Both MySQL 3.23.x and 4.x are 164 supported. 165 </p></li><li><p> 166 MySQL >= 3.23.15 configured, installed and running on 167 either localhost or an accessible networked machine. You 168 should already have a basic understanding of MySQL and how 169 it functions. 170 </p></li><li><p> 171 Optionally, if you want to be able to log SSL information 172 such as keysize or cipher, you need OpenSSL and mod_ssl 173 installed. 174 </p></li></ul></div></div><div class="section" lang="en" xml:lang="en"><div class="titlepage"><div><div><h3 class="title"><a id="id2866193"></a>Compiling and Installing</h3></div></div><div></div></div><div class="orderedlist"><ol type="1"><li><p>Unpack the archive into a working directory.</p><pre class="programlisting">$ tar -xzf mod_log_sql-1.94.tar.gz 175 $ cd mod_log_sql-1.94</pre></li><li><p>run configure to configure the source directory.</p><pre class="programlisting">$ ./configure</pre><p> 176 The 177 <tt class="filename">configure</tt> 178 script should automatically detect all the required 179 libraries and program if the are installed in standard 180 locations.. If it returns an error, here is a description of 181 the arguments you can specify when you run 182 <tt class="filename">configure</tt> 183 . 184 </p><div class="variablelist"><dl><dt><span class="term">--with-apxs=/usr/sbin/apxs</span></dt><dd><p> 185 This is the full path to the apxs binary, or the 186 directory which contains the program. This program is 187 part of the Apache 1.3 and 2.0 installation. 188 </p><p> 189 The default is to search 190 <tt class="filename">/usr/bin/apxs</tt> 191 and 192 <tt class="filename">/usr/sbin/apxs</tt> 193 . 194 </p><p> 195 Specifying a directory here will search 196 $directory/apxs, $directory/bin/apxs, and 197 $directory/sbin/apxs 198 </p><p> 199 If you have more than one version of Apache installed, 200 you need to specify the correct apxs binary for the 201 one you wish to compile for. 202 </p></dd><dt><span class="term">--with-mysql=/path/to/mysql</span></dt><dd><p> 203 This is the directory to search for the 204 <tt class="filename">libmysqlclient</tt> 205 library and the 206 <span class="application">MySQL</span> 207 headers. 208 </p><p> 209 The default is to search 210 <tt class="filename">/usr/include</tt> 211 , 212 <tt class="filename">/usr/include/mysql</tt> 213 , 214 <tt class="filename">/usr/local/include</tt> 215 , and 216 <tt class="filename">/usr/local/include/mysql</tt> 217 for 218 <span class="application">MySQL</span> 219 headers.. And 220 <tt class="filename">/usr/lib</tt> 221 . 222 <tt class="filename">/usr/lib/mysql</tt> 223 , 224 <tt class="filename">/usr/local/lib</tt> 225 , and 226 <tt class="filename">/usr/local/lin/mysql</tt> 227 for the 228 <span class="application">MySQL</span> 229 libraries. 230 </p><p> 231 Specifying this testargument will search 232 $directory/include and $directory/mysql for 233 <span class="application">MySQL</span> 234 headers. And $directory/lib and $directory/lib/mysql 235 for 236 <span class="application">MySQL</span> 237 libraries. 238 </p></dd><dt><span class="term">--enable-ssl</span></dt><dd><p> 239 Specifying this argument will enable the search for 240 mod_ssl and SSL headers, and if found will enable 241 compilation of SSL support into mod_log_sql. SSL 242 support is compiled into a separate module that can be 243 loaded after the main mod_log_sql. 244 </p></dd><dt><span class="term">--with-ssl-inc=/usr/include/openssl</span></dt><dd><p> 245 This is the path to the SSL toolkit header files that 246 were used to compile mod_ssl. If you want SSL support 247 you most likely need to specify this. 248 </p><p> 249 The default is to search 250 <tt class="filename">/usr/include</tt> 251 and 252 <tt class="filename">/usr/include/openssl</tt> 253 . 254 </p><p> 255 Specifying this argument will search that directory 256 for the SSL headers. 257 </p></dd><dt><span class="term">--with-db-inc=/usr/include/db1</span></dt><dd><p> 258 This argument is only needed when compiling SSL 259 support for Apache 1.3, and needs to be the directory 260 which contains the ndbm.h header file. You can find 261 this by using 262 </p><pre class="programlisting">$ locate ndbm.h 263 /usr/include/db1/ndbm.h 264 /usr/include/gdbm/ndbm.h</pre><p> 265 As far as I can tell, there is no difference as to 266 which you specify, but it should be the one that you 267 compiled mod_ssl with. 268 </p><p> 269 The default is 270 <tt class="filename">/usr/include/db1</tt> 271 , which should work on most systems. 272 </p></dd><dt><span class="term">--disable-apachetest</span></dt><dd><p> 273 This will disable the apache version test. However 274 there is a side affect if you specify this where I 275 will not be able to determine which version of Apache 276 you are compiling for. So don't specify this.. If you 277 are having troubles with the script detecting your 278 Apache version, then send a bug report along with your 279 system OS version and versions of related packages. 280 </p></dd><dt><span class="term">--disable-mysqltest</span></dt><dd><p> 281 This will disable the MySQL compile test. Specify this 282 if for some reason the test fail but you know you have 283 specified the correct directories. If mod_los_sql also 284 fails to compile report a bug along with your system 285 OS version and versions of related packages. 286 </p></dd></dl></div></li><li><p> 287 Now compile the module with GNU make. You may have to 288 specify gmake on some systems like FreeBSD. 289 </p><pre class="programlisting">$ gmake</pre></li><li><p> 290 If there were no errors, you can now install the module(s). 291 If you compiled as a non-root user you may need to switch 292 users with 293 <span class="application">su</span> 294 or 295 <span class="application">sudo</span> 296 . 297 </p><pre class="programlisting">$ su -c "gmake install" 298 Password:</pre></li><li><p> 299 Now edit your Apache configuration and load the modules. 300 </p><div class="note" style="margin-left: 0.5in; margin-right: 0.5in;"><h3 class="title">Note</h3><div class="itemizedlist"><ul type="disc"><li><p> 301 If you are loading the SSL logging module, you need to 302 make sure it is loaded after mod_ssl and mod_log_sql. 303 </p></li><li><p> 304 If you have previously used mod_log_sql version 1.18, 305 the name of the module has changed from sql_log_module 306 to log_sql_module (the first parameter to LoadModule) 307 </p></li><li><p> 308 If you are upgrading from any release earlier than 309 1.97 you need to add an extra LoadModule directive to 310 load the database driver (ie mysql). 311 </p></li></ul></div></div><div class="orderedlist"><ol type="a"><li><p> 312 Insert these lines to either the main 313 <tt class="filename">httpd.conf</tt> 314 or a file included via an include directive. 315 </p><pre class="programlisting">LoadModule log_sql_module modules/mod_log_sql.so 316 LoadModule log_sql_mysql_module modules/mod_log_sql_mysql.so 317 <IfModule mod_ssl.c> 318 LoadModule log_sql_ssl_module moduels/mod_log_sql_ssl.so 319 </IfModule></pre><div class="note" style="margin-left: 0.5in; margin-right: 0.5in;"><h3 class="title">Note</h3><p> 320 If you did not compile SSL support in mod_log_sql, do 321 not include the lines between the <IfModule> 322 directives. 323 </p></div></li><li><p> 324 If you are using Apache 1.3 you may need add these lines 325 later in the configuration. 326 </p><pre class="programlisting">AddModule mod_log_sql.c 327 AddModule mod_log_sql_mysql.c 328 <IfModule mod_ssl.c> 329 AddModule mod_log_sql_ssl.c 330 </IfModule></pre><div class="note" style="margin-left: 0.5in; margin-right: 0.5in;"><h3 class="title">Note</h3><p> 331 If you did not compile SSL support in mod_log_sql, do 332 not include the lines between the <IfModule> 333 directives. 334 </p></div></li></ol></div></li></ol></div></div></div><div class="section" lang="en" xml:lang="en"><div class="titlepage"><div><div><h2 class="title" style="clear: both"><a id="Sect.Configuration"></a>Configuration</h2></div></div><div></div></div><div class="section" lang="en" xml:lang="en"><div class="titlepage"><div><div><h3 class="title"><a id="Sect.Preperation"></a> 335 Preparing MySQL for logging 336 </h3></div></div><div></div></div><p> 337 You have to prepare the database to receive data from 338 <span class="application">mod_log_sql</span> 339 , and set up run-time directives in 340 <tt class="filename">httpd.conf</tt> 341 to control how and what 342 <span class="application">mod_log_sql</span> 343 logs. 344 </p><p> 345 This section will discuss how to get started with a basic 346 configuration. Full documentation of all available run-time 347 directives is available in section 348 <a href="#Sect.ConfigReference"> 349 Configuration Directive Reference 350 </a> 351 . 352 </p><div class="orderedlist"><ol type="1"><li><p> 353 mod_log_sql can make its own tables on-the-fly, or you can 354 pre-make the tables by hand. The advantage of letting the 355 module make the tables is ease-of-use, but for raw 356 performance you will want to pre-make the tables in order to 357 save some overhead. In this basic setup we'll just let the 358 module create tables for us. 359 </p></li><li><p> 360 We still need to have a logging database created and ready, 361 so run the MySQL command line client and create a database: 362 </p><pre class="programlisting"># mysql -uadmin -p 363 Enter password: 364 mysql> create database apachelogs;</pre></li><li><p><a id="Item.CreateTable"></a> 365 If you want to hand-create the tables, run the enclosed 366 'create-tables' SQL script as follows ("create_tables.sql" 367 needs to be in your current working directory). 368 </p><pre class="programlisting">mysql> use apachelogs 369 Database changed 370 mysql> source create_tables.sql</pre></li><li><p> 371 Create a specific 372 <span class="application">MySQL</span> 373 userid that 374 <span class="application">httpd</span> 375 will use to authenticate and enter data. This userid need 376 not be an actual Unix user. It is a userid internal to 377 <span class="application">MySQL</span> 378 with specific privileges. In the following example command, 379 "apachelogs" is the database, "loguser" is the userid to 380 create, "my.apachemachine.com" is the name of the Apache 381 machine, and "l0gger" is the password to assign. Choose 382 values that are different from these examples. 383 </p><pre class="programlisting">mysql> grant insert,create on apachelogs.* to loguser@my.apachemachine.com identified by 'l0gger';</pre></li><li><p> 384 You may be especially security-paranoid and want "loguser" 385 to not have "create" capability within the "apachelogs" 386 database. You can disable that privilege, but the cost is 387 that you will not be able to use the module's on-the-fly 388 table creation feature. If that cost is acceptable, 389 hand-create the tables as described in step 390 <a href="#Item.CreateTable">3</a> 391 and use the following GRANT statement instead of the one 392 above: 393 </p><pre class="programlisting">mysql> grant insert on apachelogs.* to loguser@my.apachemachine.com identified by 'l0gger';</pre></li><li><p><a id="Item.EnableLogging"></a> 394 Enable full logging of your 395 <span class="application">MySQL</span> 396 daemon (at least temporarily for debugging purposes) if you 397 don't do this already. Edit /etc/my.cnf and add the 398 following line to your [mysqld] section: 399 </p><pre class="programlisting">log=/var/log/mysql-messages</pre><p> 400 Then restart 401 <span class="application">MySQL</span> 402 </p><pre class="programlisting"># /etc/rc.d/init.d/mysql restart</pre></li></ol></div></div><div class="section" lang="en" xml:lang="en"><div class="titlepage"><div><div><h3 class="title"><a id="id2867211"></a>A very basic logging setup in Apache</h3></div></div><div></div></div><div class="orderedlist"><ol type="1"><li><p> 403 Tell the module what database to use and the appropriate 404 authentication information. 405 </p><p> 406 So, edit httpd.conf and insert the following lines somewhere 407 after any LoadModule / AddModule statements. Make sure these 408 statements are "global," i.e. not inside any VirtualHost 409 stanza. You will also note that you are embedding a password 410 in the file. Therefore you are advised to "chmod 660 411 httpd.conf" to prevent unauthorized regular users from 412 viewing your database user and password. 413 </p><p> 414 Use the 415 <span class="application">MySQL</span> 416 database called "apachelogs" running on "dbmachine.foo.com". 417 Use username "loguser" and password "l0gg3r" to authenticate 418 to the database. Permit the module create tables for us. 419 </p><div class="example"><a id="id2867249"></a><p class="title"><b>Example 1. Basic Example</b></p><pre class="programlisting">LogSQLLoginInfo mysql://loguser:l0gg3r@dbmachine.foo.com/apachelogs 420 LogSQLCreateTables on</pre></div><p> 421 If your database resides on localhost instead of another 422 host, specify the MySQL server's socket file as follows: 423 </p><pre class="programlisting">LogSQLDBParam socketfile /your/path/to/mysql.sock</pre><p> 424 If your database is listening on a port other than 3306, 425 specify the correct TCP port as follows: 426 </p><pre class="programlisting">LogSQLDBParam port 1234</pre></li><li><p> 427 The actual logging is set up on a virtual-host-by-host 428 basis. So, skip down to the virtual host you want to set up. 429 Instruct this virtual host to log entries to the table 430 "access_log" by inserting a LogSQLTransferLogTable 431 directive. (The LogSQLTransferLogTable directive is the 432 minimum required to log -- other directives that you will 433 learn about later simply tune the module's behavior.) 434 </p><pre class="programlisting"><VirtualHost 1.2.3.4> 435 [snip] 436 LogSQLTransferLogTable access_log 437 [snip] 438 </VirtualHost></pre></li><li><p>Restart apache.</p><pre class="programlisting"># /etc/rc.d/init.d/httpd stop 439 # /etc/rc.d/init.d/httpd start</pre></li></ol></div></div><div class="section" lang="en" xml:lang="en"><div class="titlepage"><div><div><h3 class="title"><a id="id2867316"></a>Testing the basic setup</h3></div></div><div></div></div><div class="orderedlist"><ol type="1"><li><p> 440 Visit your web site in a browser to trigger some hits, then 441 confirm that the entries are being successfully logged: 442 </p><pre class="programlisting"># mysql -hdbmachine.foo.com -umysqladmin -p -e "SELECT * FROM access_log" apachelogs 443 Enter password:</pre><p> 444 Several lines of output should follow, corresponding to your 445 hits on the site. You now have basic functionality. Don't 446 disable your regular Apache logs until you feel comfortable 447 that the database is behaving as you'd like and that things 448 are going well. If you do not see any entries in the 449 access_log, please consult section 450 <a href="#FAQ.NothingLogged">Q: 2.2</a> 451 of the FAQ on how to debug and fix the situation. 452 </p></li><li><p> 453 You can now activate the advanced features of mod_log_sql, 454 which are described in the next section. 455 </p></li></ol></div></div><div class="section" lang="en" xml:lang="en"><div class="titlepage"><div><div><h3 class="title"><a id="id2867365"></a>How to tune logging with run-time directives</h3></div></div><div></div></div><div class="section" lang="en" xml:lang="en"><div class="titlepage"><div><div><h4 class="title"><a id="id2867371"></a>Instructing the module what to log</h4></div></div><div></div></div><p> 456 The most basic directive for the module is 457 LogSQLTransferLogFormat, which tells the module which 458 information to send to the database; logging to the database 459 will not take place without it. Place a 460 LogSQLTransferLogFormat directive in the VirtualHost stanza of 461 each virtual host that you want to activate. 462 </p><p> 463 After LogSQLTransferLogFormat you supply a string of 464 characters that tell the module what information to log. In 465 the configuration directive reference (section 466 <a href="#Conf.LogSQLTransferLogFormat">LogSQLTransferLogFormat</a> 467 ) there is a table which clearly defines all the possible 468 things to log. Let's say you want to log only the "request 469 time," the "remote host," and the "request"; you'd use: 470 </p><pre class="programlisting">LogSQLTransferLogFormat hUS</pre><p>But a more appropriate string to use is</p><pre class="programlisting">LogSQLTransferLogFormat AbHhmRSsTUuv</pre><p> 471 which logs all the information required to be compatible with 472 the Combined Log Format (CLF). 473 </p><p> 474 If you don't choose to log everything that is available, 475 that's fine. Fields in the unused columns in your table will 476 simply contain NULL. 477 </p><p> 478 Some of the LogSQLTransferLogFormat characters require a 479 little extra configuration: 480 </p><div class="itemizedlist"><ul type="disc"><li><p> 481 If you specify 'c' to indicate that you want to log the 482 cookie value, you must also tell the module which cookie 483 you mean by using LogSQLWhichCookie -- after all, there 484 could be many cookies associated with a given request. 485 Fail to specify LogSQLWhichCookie, and no cookie 486 information at all will be logged. 487 </p></li><li><p> 488 If you specify 'M' to indicate that you want to log the 489 machine ID, you must also tell the module this machine's 490 identity using the LogSQLMachineID directive. Fail to 491 specify LogSQLMachineID, and a simple '-' character will 492 be logged in the machine_id column. 493 </p></li></ul></div></div><div class="section" lang="en" xml:lang="en"><div class="titlepage"><div><div><h4 class="title"><a id="Sect.Ignore"></a> 494 Instructing the module what NOT to log using filtering 495 directives 496 </h4></div></div><div></div></div><p> 497 One "accept" and two "ignore" directives allow you to 498 fine-tune what the module should not log. These are very handy 499 for keeping your database as uncluttered as possible and 500 keeping your statistics free of unneeded numbers. Think of 501 each one as a gatekeeper. 502 </p><p> 503 <span class="emphasis"><em> 504 It is important to remember that each of these three 505 directives is purely optional. mod_log_sql's default is to 506 log everything. 507 </em></span> 508 </p><p> 509 When a request comes in, the contents of LogSQLRequestAccept 510 are evaluated first. This optional, "blanket" directive lets 511 you specify that only certain things are to be accepted for 512 logging, and everything else discarded. Because it is 513 evaluated before LogSQLRequestIgnore and LogSQLRemhostIgnore 514 it can halt logging before those two filtering directives "get 515 their chance." 516 </p><p> 517 Once a request makes it past LogSQLRequestAccept, it still can 518 be excluded based on LogSQLRemhostIgnore and 519 LogSQLRequestIgnore. A good way to use LogSQLRemhostIgnore is 520 to prevent the module from logging the traffic that your 521 internal hosts generate. LogSQLRequestIgnore is great for 522 preventing things like requests for "favicon.ico" from 523 cluttering up your database, as well as excluding the various 524 requests that worms make, etc. 525 </p><p> 526 You can specify a series of strings after each directive. Do 527 not use any type of globbing or regular-expression syntax -- 528 each string is considered a match 529 <span class="emphasis"><em> 530 if it is a substring of the larger request or remote-host; 531 the comarison is case-sensitive 532 </em></span> 533 . This means that "LogSQLRemhostIgnore micro" will ignore 534 requests from "microsoft.com," "microworld.net," 535 "mymicroscope.org," etc. "LogSQLRequestIgnore gif" will 536 instruct the module to ignore requests for "leftbar.gif," 537 "bluedot.gif" and even "giftwrap.jpg" -- but "RED.GIF" and 538 "Tree.Gif" would still get logged because of case sensitivity. 539 </p><p>A summary of the decision flow:</p><div class="orderedlist"><ol type="1"><li><p> 540 If LogSQLRequestAccept exists and a request does not match 541 anything in that list, it is discarded. 542 </p></li><li><p> 543 If a request matches anything in the LogSQLRequestIgnore 544 list, it is discarded. 545 </p></li><li><p> 546 If a reqiest matches anything in the LogSQLRemhostIgnore 547 list, it is discarded. 548 </p></li><li><p>Otherwise the request is logged.</p></li></ol></div><p> 549 This means that you can have a series of directives similar to 550 the following: 551 </p><pre class="programlisting">LogSQLRequestAccept .html .gif .jpg 552 LogSQLRequestIgnore statistics.html bluedot.jpg</pre><p> 553 So the first line instructs the module to only log files with 554 html, gif and jpg suffixes; requests for "formail.cgi" and 555 "shopping-cart.pl" will never be considered for logging. 556 ("LeftArrow.JPG" will also never be considered for logging -- 557 remember, the comparison is case sensitive.) The second line 558 prunes the list further -- you never want to log requests for 559 those two objects. 560 </p><div class="note" style="margin-left: 0.5in; margin-right: 0.5in;"><h3 class="title">Note</h3><div class="itemizedlist"><ul type="disc"><li><p> 561 If you want to match all the hosts in your domain such 562 as "host1.corp.foo.com" and "server.dmz.foo.com", simply 563 specify: 564 </p><pre class="programlisting">LogSQLRemhostIgnore foo.com</pre></li><li><p> 565 A great way to catch the vast majority of worm-attack 566 requests and prevent them from being logged is to 567 specify: 568 </p><pre class="programlisting">LogSQLRequestIgnore root.exe cmd.exe default.ida</pre></li><li><p> 569 To prevent the logging of requests for common graphic 570 types, make sure to put a '.' before the suffix to avoid 571 matches that you didn't intend: 572 </p><pre class="programlisting">LogSQLRequestIgnore .gif .jpg</pre></li></ul></div></div></div></div><div class="section" lang="en" xml:lang="en"><div class="titlepage"><div><div><h3 class="title"><a id="id2867646"></a>Advanced logging scenarios</h3></div></div><div></div></div><div class="section" lang="en" xml:lang="en"><div class="titlepage"><div><div><h4 class="title"><a id="id2867652"></a>Using the module in an ISP environment</h4></div></div><div></div></div><p>mod_log_sql has three basic tiers of operation:</p><div class="orderedlist"><ol type="1"><li><p> 573 The administrator creates all necessary tables by hand and 574 configures each Apache VirtualHost by hand. 575 (LogSQLCreateTables Off) 576 </p></li><li><p> 577 The module is permitted to create necessary tables 578 on-the-fly, but the administrator configures each Apache 579 VirtualHost by hand. (LogSQLCreateTables On) 580 </p></li><li><p> 581 The module is permitted to create all necessary tables and 582 to make intelligent, on-the-fly configuration of each 583 VirtualHost. (LogSQLMassVirtualHosting On) 584 </p></li></ol></div><p> 585 Many users are happy to use the module in its most minimal 586 form: they hand-create any necessary tables (using 587 "create_tables.sql"), and they configure each VirtualHost by 588 hand to suit their needs. However, some administrators need 589 extra features due to a large and growing number of 590 VirtualHosts. The LogSQLMassVirtualHosting directive activates 591 module capabilities that make it far easier to manage an ISP 592 environment, or any situation characterized by a large and 593 varying number of virtual servers. 594 </p><div class="itemizedlist"><ul type="disc"><li><p> 595 the on-the-fly table creation feature is activated 596 automatically 597 </p></li><li><p> 598 the transfer log table name is dynamically set from the 599 virtual host's name (example: a virtual host 600 "www.grubbybaby.com" gets logged to table 601 "access_www_grubbybaby_com") 602 </p></li></ul></div><p> 603 There are numerous benefits. The admin will not need to create 604 new tables for every new VirtualHost. (Although the admin will 605 still need to drop the tables of virtual hosts that are 606 removed.) The admin will not need to set 607 LogSQLTransferLogTable for each virtual host -- it will be 608 configured automatically based on the host's name. Because 609 each virtual host will log to its own segregated table, data 610 about one virtual server will segregate from others; an admin 611 can grant users access to the tables they need, and they will 612 be unable to view data about another user's virtual host. 613 </p><p> 614 In an ISP scenario the admin is likely to have a cluster of 615 many front-end webservers logging to a back-end database. 616 mod_log_sql has a feature that permits analysis of how well 617 the web servers are loadbalancing: the LogSQLMachineID 618 directive. The administrator uses this directive to assign a 619 unique identifier to each machine in the web cluster, e.g. 620 "LogSQLMachineID web01," "LogSQLMachineID web02," etc. Used in 621 conjunction with the 'M' character in LogSQLTransferLogFormat, 622 each entry in the SQL log will include the machine ID of the 623 machine that created the entry. This permits the administrator 624 to count the entries made by each particular machine and 625 thereby analyze the front-end loadbalancing algorithm. 626 </p></div><div class="section" lang="en" xml:lang="en"><div class="titlepage"><div><div><h4 class="title"><a id="Sect.MultiTable"></a> 627 Logging many-to-one data in separate tables 628 </h4></div></div><div></div></div><p> 629 A given HTTP request can have a one-to-many relationship with 630 certain kinds of data. For example, a single HTTP request can 631 have 4 cookies, 3 headers and 5 "mod_gzip" notes associated 632 with it. mod_log_sql is capable of logging these relationships 633 due to the elegance of SQL relational data. 634 </p><p> 635 You already have a single table containing access requests. 636 One of the columns in that table is 'id' which is intended to 637 contain the unique request ID supplied by the standard Apache 638 module mod_unique_id -- all you need to do is compile in that 639 module and employ the LogSQLTransferLogFormat character 'I'. 640 Thereafter, each request gets a unique ID that can be thought 641 of as a primary key within the database, useful for joining 642 multiple tables. So let's envision several new tables: a notes 643 table, a cookies table, and a table for inbound and outbound 644 headers. 645 </p><div class="table"><a id="id2867801"></a><p class="title"><b>Table 1. <tblAcc>access_log</b></p><table summary="<tblAcc>access_log" border="1"><colgroup><col /><col /><col /><col /><col /><col /></colgroup><thead><tr><th>id</th><th>remote_host</th><th>request_uri</th><th>time_stamp</th><th>status</th><th>bytes_sent</th></tr></thead><tbody><tr><td>PPIDskBRH30AAGPtAsg</td><td>zerberus.aiacs.net</td><td>/mod_log_sql/index.html</td><td>1022493617</td><td>200</td><td>2215</td></tr></tbody></table></div><div class="table"><a id="id2867921"></a><p class="title"><b>Table 2. <tblNotes>notes_log</b></p><table summary="<tblNotes>notes_log" border="1"><colgroup><col /><col /><col /></colgroup><thead><tr><th>id</th><th>item</th><th>val</th></tr></thead><tbody><tr><td>PPIDskBRH30AAGPtAsg</td><td>mod_gzip_result</td><td>OK</td></tr><tr><td>PPIDskBRH30AAGPtAsg</td><td>mod_gzip_compression_ratio</td><td>69</td></tr></tbody></table></div><div class="table"><a id="id2868012"></a><p class="title"><b>Table 3. <tblHdr>headers_log</b></p><table summary="<tblHdr>headers_log" border="1"><colgroup><col /><col /><col /></colgroup><thead><tr><th>id</th><th>item</th><th>val</th></tr></thead><tbody><tr><td>PPIDskBRH30AAGPtAsg</td><td>Content-Type</td><td>text/html</td></tr><tr><td>PPIDskBRH30AAGPtAsg</td><td>Accept-Encoding</td><td>gzip, deflate</td></tr><tr><td>PPIDskBRH30AAGPtAsg</td><td>Expires</td><td>Tue, 28 May 2002 10:00:18 GMT</td></tr><tr><td>PPIDskBRH30AAGPtAsg</td><td>Cache-Control</td><td>max-age=86400</td></tr></tbody></table></div><p> 646 We have a certain request, and its unique ID is 647 "PPIDskBRH30AAGPtAsg". Within each separate table will be 648 multiple entries with that request ID: several cookie entries, 649 several header entries, etc. As you can see in tables 650 [tblAcc], [tblNotes] and [tblHdr], you have a one-to-many 651 relationship for request PPIDskBRH30AAGPtAsg: that one access 652 has two associated notes and four associated headers. You can 653 extract this data easily using the power of SQL's "select" 654 statement and table joins. To see the notes associated with a 655 particular request: 656 </p><pre class="programlisting">SELECT a.remote_host, a.request_uri, n.item, n.val 657 FROM access_log a JOIN notes_log n ON a.id=n.id 658 WHERE a.id='PPIDskBRH30AAGPtAsg';</pre><div class="table"><a id="id2868167"></a><p class="title"><b>Table 4. access_log joined to notes_log</b></p><table summary="access_log joined to notes_log" border="1"><colgroup><col /><col /><col /><col /></colgroup><thead><tr><th>remote_host</th><th>request_uri</th><th>item</th><th>val</th></tr></thead><tbody><tr><td>zerberus.aiacs.net</td><td>/mod_log_sql/index.html</td><td>mod_gzip_result</td><td>OK</td></tr><tr><td>zerberus.aiacs.net</td><td>/mod_log_sql/index.html</td><td>mod_gzip_compression_ratio</td><td>69</td></tr></tbody></table></div><p> 659 Naturally you can craft similar statements for the outboud 660 headers, inbound headers and cookies, all of which can live in 661 separate tables. Your statements are limited in power only by 662 your skill with SQL. 663 </p><p> 664 In order to use this capability of mod_log_sql, you must do 665 several things. 666 </p><div class="itemizedlist"><ul type="disc"><li><p> 667 Compile mod_unique_id into Apache (statically or as a 668 DSO). mod_log_sql employs the unique request ID that 669 mod_unique_id provides in order to key between the 670 separate tables. You can still log the data without 671 mod_unqiue_id, but it will be completely uncorrelated and 672 you will have no way to discern any meaning. 673 </p></li><li><p> 674 Create the appropriate tables. This will be done for you 675 if you permit mod_log_sql to create its own tables using 676 LogSQLCreateTables On, or if you use the enclosed 677 "create_tables.sql" script. 678 </p></li><li><p> 679 Create a SQL index on the "id" column. Without this index, 680 table joins will be deathly slow. I recommend you consult 681 the MySQL documentation on the proper way to create a 682 column index if you are not familiar with this operation. 683 </p></li><li><p> 684 Within each appropriate VirtualHost stanza, use the 685 LogSQLWhich* and LogSQL*LogTable directives to tell the 686 module what and where to log the data. In the following 687 example, I have overridden the name for the notes table 688 whereas I have left the other table names at their 689 defaults. I have then specified the cookies, headers and 690 notes that interest me. (And as you can see, these 691 directives do not require me to add any characters to 692 LogSQLTransferLogTable.) 693 </p><pre class="programlisting"><VirtualHost 216.231.36.128> 694 (snip) 695 LogSQLNotesLogTable notestable 696 LogSQLWhichCookies bluecookie redcookie greencookie 697 LogSQLWhichNotes mod_gzip_result mod_gzip_compression_ratio 698 LogSQLWhichHeadersOut Expires Content-Type Cache-Control 699 LogSQLWhichHeadersIn User-Agent Accept-Encoding Host 700 (snip) 701 </VirtualHost></pre></li></ul></div></div><div class="section" lang="en" xml:lang="en"><div class="titlepage"><div><div><h4 class="title"><a id="id2868363"></a>Using the same database for production and test</h4></div></div><div></div></div><p> 702 Although sub-optimal, it is not uncommon to use the same 703 back-end database for the "production" webservers as well as 704 the "test" webservers (budgetary constraints, rack-space 705 limits, etc.). Furthermore, an administrator in this situation 706 may be unable to use LogSQLRemhostIgnore to exclude requests 707 from the test servers -- perhaps the generated entries are 708 genuinely useful for analytical or QA purposes, but their 709 value after analysis is minimal. 710 </p><p> 711 It is wasteful and potentially confusing to permit this 712 internal test data to clutter the database, and a solution to 713 the problem is the proper use of the LogSQLMachineID 714 directive. Assume a scenario where the production webservers 715 have IDs like "web01," "web02," and so on -- and the test 716 webservers have IDs like "test01," "test02," etc. Because 717 entries in the log database are distinguished by their source 718 machine, an administrator may purge unneeded test data from 719 the access log as follows: 720 </p><pre class="programlisting">DELETE FROM access_log WHERE machine_id like 'test%';</pre></div><div class="section" lang="en" xml:lang="en"><div class="titlepage"><div><div><h4 class="title"><a id="Sect.DelayedInsert"></a> 721 Optimizing for a busy database 722 </h4></div></div><div></div></div><p> 723 A busy MySQL database will have SELECT statements running 724 concurrently with INSERT and UPDATE statements. A long-running 725 SELECT can in certain circumstances block INSERTs and 726 therefore block mod_log_sql. A workaround is to enable 727 mod_log_sql for "delayed inserts," which are described as 728 follows in the MySQL documentation. 729 </p><p> 730 The DELAYED option for the INSERT statement is a 731 MySQL-specific option that is very useful if you have clients 732 that can't wait for the INSERT to complete. This is a common 733 problem when you use MySQL for logging and you also 734 periodically run SELECT and UPDATE statements that take a long 735 time to complete. DELAYED was introduced in MySQL Version 736 3.22.15. It is a MySQL extension to ANSI SQL92. 737 </p><p> 738 INSERT DELAYED only works with ISAM and MyISAM tables. Note 739 that as MyISAM tables supports concurrent SELECT and INSERT, 740 if there is no free blocks in the middle of the data file, you 741 very seldom need to use INSERT DELAYED with MyISAM. 742 </p><p> 743 When you use INSERT DELAYED, the client will get an OK at once 744 and the row will be inserted when the table is not in use by 745 any other thread. 746 </p><p> 747 Another major benefit of using INSERT DELAYED is that inserts 748 from many clients are bundled together and written in one 749 block. This is much faster than doing many separate inserts. 750 </p><p>The general disadvantages of delayed inserts are</p><div class="orderedlist"><ol type="1"><li><p> 751 The queued rows are only stored in memory until they are 752 inserted into the table. If mysqld dies unexpectedly, any 753 queued rows that were not written to disk are lost. 754 </p></li><li><p> 755 There is additional overhead for the server to handle a 756 separate thread for each table on which you use INSERT 757 DELAYED. 758 </p></li></ol></div><div class="note" style="margin-left: 0.5in; margin-right: 0.5in;"><h3 class="title">Note</h3><p> 759 The MySQL documentation concludes, "This means that you 760 should only use INSERT DELAYED when you are really sure you 761 need it!" Furthermore, the current state of error return 762 from a failed INSERT DELAYED seems to be in flux, and may 763 behave in unpredictable ways between different MySQL 764 versions. See FAQ entry 765 <a href="#FAQ.DelayedInsert">Q: 3.4</a> 766 -- you have been warned. 767 </p></div><p> 768 If you are experiencing issues which could be solved by 769 delayed inserts, then set LogSqlDelayedInserts On in the 770 <tt class="filename">httpd.conf</tt> 771 . All regular INSERT statements are now INSERT DELAYED, and 772 you should see no more blocking of the module. 773 </p></div></div><div class="section" lang="en" xml:lang="en"><div class="titlepage"><div><div><h3 class="title"><a id="Sect.ConfigReference"></a> 774 Configuration Directive Reference 775 </h3></div></div><div></div></div><p> 776 It is imperative that you understand which directives are used 777 only once in the main server config, and which are used inside 778 VirtualHost stanzas and therefore multiple times within 779 httpd.conf. The "context" listed with each entry informs you of 780 this. 781 </p><div class="section" lang="en" xml:lang="en"><div class="titlepage"><div><div><h4 class="title"><a id="id2868539"></a>DataBase Configuration</h4></div></div><div></div></div><div class="variablelist"><dl><dt><span class="term">LogSQLLoginInfo</span></dt><dd><div class="cmdsynopsis"><p><tt class="command">LogSQLLoginInfo</tt> { 782 <i class="replaceable"><tt>connection URI</tt></i> 783 }</p></div><p> 784 Example: LogSQLLoginInfo 785 mysql://logwriter:passw0rd@foobar.baz.com/Apache_log 786 </p><p>Context: main server config</p><p> 787 Defines the basic connection URI to connect to the 788 database with. The format of the connection URI is 789 </p><p> 790 driver://username[:password]@hostname[:port]/database 791 </p><div class="variablelist"><dl><dt><span class="term">driver</span></dt><dd> 792 The database driver to use (mysql, pgsql, etc..) 793 </dd><dt><span class="term">username</span></dt><dd> 794 The database username to login with INSERT 795 privileges on the logging table defined in 796 LogSQLtransferLogTable. 797 </dd><dt><span class="term">password</span></dt><dd> 798 The password to use for username, and can be 799 omitted if there is no password. 800 </dd><dt><span class="term">hostname</span></dt><dd> 801 The hostname or Ip address of the Database 802 machine, ans is simple "localhost" if the database 803 lives on the same machine as Apache. 804 </dd><dt><span class="term">port</span></dt><dd> 805 Port on hostname to connect to the Database, if 806 not specified use the default port for the 807 database. 808 </dd><dt><span class="term">database</span></dt><dd> 809 The database to connect to on the server. 810 </dd></dl></div><div class="note" style="margin-left: 0.5in; margin-right: 0.5in;"><h3 class="title">Note</h3><p> 811 This is defined only once in the 812 <tt class="filename">httpd.conf</tt> 813 file. 814 </p><p> 815 This directive Must be defined for logging to be 816 enabled. 817 </p></div></dd><dt><span class="term">LogSQLDBParam</span></dt><dd><div class="cmdsynopsis"><p><tt class="command">LogSQLDBParam</tt> { 818 <i class="replaceable"><tt>parameter-name</tt></i> 819 } { 820 <i class="replaceable"><tt>value</tt></i> 821 }</p></div><p> 822 Example: LogSQLDBParam socketfile 823 /var/lib/mysql/mysql.socket 824 </p><p>Context: main server config</p><p> 825 This is the new method of specifying Database connection 826 credentials and settings. This is used to define 827 database driver specific options. For a list of options 828 read the documentation for each specific database 829 driver. 830 </p><div class="table"><a id="id2866443"></a><p class="title"><b>Table 5. MySQL Driver parameters</b></p><table summary="MySQL Driver parameters" border="1"><colgroup><col /><col /><col /><col /><col /></colgroup><thead><tr><th>Parameter</th><th>Meaning</th><th>Default</th><td class="auto-generated"> </td><td class="auto-generated"> </td></tr></thead><tbody><tr><td>hostname</td><td>MySQL Server hostname</td><td>none (use LogSQLLoginInfo to set)</td><td class="auto-generated"> </td><td class="auto-generated"> </td></tr><tr><td>username</td><td>The username to log in with</td><td>none (use LogSQLLoginInfo to set)</td><td class="auto-generated"> </td><td class="auto-generated"> </td></tr><tr><td>password</td><td>The password to use</td><td>none (use LogSQLLoginInfo to set)</td><td class="auto-generated"> </td><td class="auto-generated"> </td></tr><tr><td>database</td><td>Which database to connect to</td><td>none (use LogSQLLoginInfo to set)</td><td class="auto-generated"> </td><td class="auto-generated"> </td></tr><tr><td>port</td><td>The TCP port to connect to the MySQL server over</td><td>3306 (use LogSQLLoginInfo to set)</td><td class="auto-generated"> </td><td class="auto-generated"> </td></tr><tr><td>socketfile</td><td>The MySQL Unix socket file to use</td><td>none</td><td class="auto-generated"> </td><td class="auto-generated"> </td></tr><tr><td>tabletype</td><td>MySQL Table Engine to use</td><td>MySQL server default</td><td class="auto-generated"> </td><td class="auto-generated"> </td></tr></tbody></table></div><div class="note" style="margin-left: 0.5in; margin-right: 0.5in;"><h3 class="title">Note</h3><p> 831 Each parameter-name may only be defined once. 832 </p></div></dd><dt><span class="term">LogSQLCreateTables</span></dt><dd><div class="cmdsynopsis"><p><tt class="command">LogSQLCreateTables</tt> {flag}</p></div><p>Example: LogSQLCreateTables On</p><p>Default: Off</p><p>Context: main server config</p><p> 833 mod_log_sql has the ability to create its tables 834 on-the-fly. The advantage to this is convenience: you 835 don't have to execute any SQL by hand to prepare the 836 table. This is especially helpful for people with lots 837 of virtual hosts (who should also see the 838 LogSQLMassVirtualHosting directive). 839 </p><p> 840 There is a slight disadvantage: if you wish to activate 841 this feature, then the userid specified in 842 LogSQLLoginInfo must have CREATE privileges on the 843 database. In an absolutely paranoid, locked-down 844 situation you may only want to grant your mod_log_sql 845 user INSERT privileges on the database; in that 846 situation you are unable to take advantage of 847 LogSQLCreateTables. But most people -- even the very 848 security-conscious -- will find that granting CREATE on 849 the logging database is reasonable. 850 </p><div class="note" style="margin-left: 0.5in; margin-right: 0.5in;"><h3 class="title">Note</h3><p> 851 This is defined only once in the 852 <tt class="filename">httpd.conf</tt> 853 file. 854 </p></div></dd><dt><span class="term">LogSQLForcePreserve</span></dt><dd><div class="cmdsynopsis"><p><tt class="command">LogSQLForcePreserve</tt> {flag}</p></div><p>Example: LogForcePreserve On</p><p>Default: Off</p><p>Context: main server config</p><p> 855 You may need to perform debugging on your database and 856 specifically want mod_log_sql to make no attempts to log 857 to it. This directive instructs the module to send all 858 its log entries directly to the preserve file and to 859 make no database INSERT attempts. 860 </p><p> 861 This is presumably a directive for temporary use only; 862 it could be dangerous if you set it and forget it, as 863 all your entries will simply pile up in the preserve 864 file. 865 </p><div class="note" style="margin-left: 0.5in; margin-right: 0.5in;"><h3 class="title">Note</h3><p> 866 This is defined only once in the 867 <tt class="filename">httpd.conf</tt> 868 file. 869 </p></div></dd><dt><span class="term">LogSQLDisablePreserve</span></dt><dd><div class="cmdsynopsis"><p><tt class="command">LogSQLDisablePreserve</tt> {flag}</p></div><p>Example: LogDisablePreserve On</p><p>Default: Off</p><p>Context; main server config</p><p> 870 This option can be enabled to completely disable the 871 preserve file fail back. This may be useful for servers 872 where the file-system is read-only. 873 </p><p> 874 If the database is not available those log entries will 875 be lost. 876 </p><div class="note" style="margin-left: 0.5in; margin-right: 0.5in;"><h3 class="title">Note</h3><p> 877 This is defined only once in the 878 <tt class="filename">httpd.conf</tt> 879 file. 880 </p></div></dd><dt><span class="term">LogSQLMachineID</span></dt><dd><div class="cmdsynopsis"><p><tt class="command">LogSQLMachineID</tt> {machineID}</p></div><p>Example: LogSQLMachineID web01</p><p>Context: main server config</p><p> 881 If you have a farm of webservers then you may wish to 882 know which particular machine made each entry; this is 883 useful for analyzing your load-balancing methodology. 884 LogSQLMachineID permits you to distinguish each 885 machine's entries if you assign each machine its own 886 LogSQLMachineID: for example, the first webserver gets 887 ``LogSQLMachineID web01,'' the second gets 888 ``LogSQLMachineID web02,'' etc. 889 </p><div class="note" style="margin-left: 0.5in; margin-right: 0.5in;"><h3 class="title">Note</h3><p> 890 This is defined only once in the 891 <tt class="filename">httpd.conf</tt> 892 file. 893 </p></div></dd><dt><span class="term">LogSQlPreserveFile</span></dt><dd><div class="cmdsynopsis"><p><tt class="command">LogSQLPreserveFile</tt> { 894 <i class="replaceable"><tt>filename</tt></i> 895 }</p></div><p> 896 Example: LogSQLPreserveFile offline-preserve 897 </p><p>Default: /tmp/sql-preserve</p><p>Context: virtual host</p><p> 898 mod_log_sql writes queries to this local preserve file 899 in the event that it cannot reach the database, and thus 900 ensures that your high-availability web frontend does 901 not lose logs during a temporary database outage. This 902 could happen for a number of reasons: the database goes 903 offline, the network breaks, etc. You will not lose 904 entries since the module has this backup. The file 905 consists of a series of SQL statements that can be 906 imported into your database at your convenience; 907 furthermore, because the SQL queries contain the access 908 timestamps you do not need to worry about out-of-order 909 data after the import, which is done in a simple manner: 910 </p><pre class="programlisting"># mysql -uadminuser -p mydbname < /tmp/sql-preserve</pre><p> 911 If you do not define LogSQLPreserveFile then all virtual 912 servers will log to the same default preserve file ( 913 <tt class="filename">/tmp/sql-preserve</tt> 914 ). You can redefine this on a virtual-host basis in 915 order to segregate your preserve files if you desire. 916 Note that segregation is not usually necessary, as the 917 SQL statements that are written to the preserve file 918 already distinguish between different virtual hosts if 919 you include the 'v' character in your 920 LogSQLTransferLogFormat directive. It is only necessary 921 to segregate preserve-files by virualhost if you also 922 segregate access logs by virtualhost. 923 </p><p> 924 The module will log to Apache's ErrorLog when it notices 925 a database outage, and upon database return. You will 926 therefore know when the preserve file is being used, 927 although it is your responsibility to import the file. 928 </p><p> 929 The file does not need to be created in advance. It is 930 safe to remove or rename the file without interrupting 931 Apache, as the module closes the filehandle immediately 932 after completing the write. The file is created with the 933 user & group ID of the running Apache process (e.g. 934 'nobody' on many Linux distributions). 935 </p></dd></dl></div></div><div class="section" lang="en" xml:lang="en"><div class="titlepage"><div><div><h4 class="title"><a id="id2869064"></a>Table Names</h4></div></div><div></div></div><div class="variablelist"><dl><dt><span class="term">LogSQLTransferLogTable</span></dt><dd><div class="cmdsynopsis"><p><tt class="command">LogSQLTransferLogTable</tt> { 936 <i class="replaceable"><tt>table-name</tt></i> 937 }</p></div><p> 938 Example: LogSQLTransferLogTable access_log_table 939 </p><p>Context: virtual host</p><p> 940 Defines which table is used for logging of Apache's 941 transfers; this is analogous to Apache's TransferLog 942 directive. table-name must be a valid table within the 943 database defined in the LogSQLLoginInfo connection URI. 944 </p><p> 945 This directive is 946 <span class="emphasis"><em>not</em></span> 947 necessary if you declare LogSQLMassVirtualHosting On, 948 since that directive activates dynamically-named tables. 949 If you attempt to use LogSqlTransferlogTable at the same 950 time a warning will be logged and it will be ignored, 951 since LogSQLMassVirtualHosting takes priority. 952 </p><div class="note" style="margin-left: 0.5in; margin-right: 0.5in;"><h3 class="title">Note</h3><p> 953 Requires unless LogSQLMassVirtualHosting is set to On 954 </p></div></dd><dt><span class="term">LogSQLCookieLogTable</span></dt><dd><div class="cmdsynopsis"><p><tt class="command">LogSQLCookieLogTable</tt> { 955 <i class="replaceable"><tt></tt></i> 956 table-name 957 }</p></div><p> 958 Example: LogSQLCookieLogTable cookie_log 959 </p><p>Default: cookies</p><p>Context: virtual host</p><p> 960 Defines which table is used for logging of cookies. 961 Working in conjunction with LogSQLWhichCookies, you can 962 log many of each request's associated cookies to a 963 separate table. For meaningful data retrieval the cookie 964 table is keyed to the access table by the unique request 965 ID supplied by the standard Apache module mod_unique_id. 966 </p><div class="note" style="margin-left: 0.5in; margin-right: 0.5in;"><h3 class="title">Note</h3><p> 967 You must create the table (see create-tables.sql, 968 included in the package), or LogSQLCreateTables must 969 be set to "on". 970 </p></div></dd><dt><span class="term">LogSQLHeadersInLogTable</span></dt><dd><div class="cmdsynopsis"><p><tt class="command">LogSQLHeadersInLogTable</tt> { 971 <i class="replaceable"><tt>table-name</tt></i> 972 }</p></div><p> 973 Example: LogSQLHeadersInLogTable headers 974 </p><p>Default: headers_in</p><p>Context: virtual host</p><p> 975 Defines which table is used for logging of inbound 976 headers. Working in conjunction with 977 LogSQLWhichHeadersIn, you can log many of each request's 978 associated headers to a separate table. For meaningful 979 data retrieval the headers table is keyed to the access 980 table by the unique request ID supplied by the standard 981 Apache module mod_unique_id. 982 </p><div class="note" style="margin-left: 0.5in; margin-right: 0.5in;"><h3 class="title">Note</h3><p> 983 Note that you must create the table (see 984 create-tables.sql, included in the package), or 985 LogSQLCreateTables must be set to "on". 986 </p></div></dd><dt><span class="term">LogSQLHeadersOutLogTable</span></dt><dd><div class="cmdsynopsis"><p><tt class="command">LogSQLHeadersOutLogTable</tt> { 987 <i class="replaceable"><tt>table-name</tt></i> 988 }</p></div><p> 989 Example: LogSQLHeadersOutLogTable headers 990 </p><p>Default: headers_out</p><p>Context: virtual host</p><p> 991 Defines which table is used for logging of outbound 992 headers. Working in conjunction with 993 LogSQLWhichHeadersOut, you can log many of each 994 request's associated headers to a separate table. For 995 meaningful data retrieval the headers table is keyed to 996 the access table by the unique request ID supplied by 997 the standard Apache module mod_unique_id. 998 </p><div class="note" style="margin-left: 0.5in; margin-right: 0.5in;"><h3 class="title">Note</h3><p> 999 Note that you must create the table (see 1000 create-tables.sql, included in the package), or 1001 LogSQLCreateTables must be set to "on". 1002 </p></div></dd><dt><span class="term">LogSQLNotesLogTable</span></dt><dd><div class="cmdsynopsis"><p><tt class="command">LogSQLNotesLogTable</tt> { 1003 <i class="replaceable"><tt>table-name</tt></i> 1004 }</p></div><p>Example: LogSQLNotesLogTable notes-log</p><p>Default: notes</p><p>Context: virtual_host</p><p> 1005 Defines which table is used for logging of notes. 1006 Working in conjunction with LogSQLWhichNotes, you can 1007 log many of each request's associated notes to a 1008 separate table. For meaningful data retrieval the notes 1009 table is keyed to the access table by the unique request 1010 ID supplied by the standard Apache module mod_unique_id. 1011 </p><div class="note" style="margin-left: 0.5in; margin-right: 0.5in;"><h3 class="title">Note</h3><p> 1012 This table must be created (see create-tables.sql 1013 included in the package), or LogSQLCreateTables must 1014 be set to 'On'. 1015 </p></div></dd><dt><span class="term">LogSQLMassVirtualHosting</span></dt><dd><div class="cmdsynopsis"><p><tt class="command">LogSQLMassVirtualHosting</tt> {flag}</p></div><p>Example: LogSQLMassVirtualHosting On</p><p>Default: Off</p><p>Context: main server config</p><p> 1016 If you administer a site hosting many, many virtual 1017 hosts then this option will appeal to you. If you turn 1018 on LogSQLMassVirtualHosting then several things happen: 1019 </p><div class="itemizedlist"><ul type="disc"><li><p> 1020 the on-the-fly table creation feature is activated 1021 automatically 1022 </p></li><li><p> 1023 the transfer log table name is dynamically set from 1024 the virtual host's name after stripping out 1025 SQL-unfriendly characters (example: a virtual host 1026 www.grubbybaby.com gets logged to table 1027 access_www_grubbybaby_com) 1028 </p></li><li><p> 1029 which, in turn, means that each virtual host logs to 1030 its own segregated table. Because there is no data 1031 shared between virtual servers you can grant your 1032 users access to the tables they need; they will be 1033 unable to view others' data. 1034 </p></li></ul></div><p> 1035 This is a huge boost in convenience for sites with many 1036 virtual servers. Activating LogSQLMassVirtualHosting 1037 obviates the need to create every virtual server's table 1038 and provides more granular security possibilities. 1039 </p><div class="note" style="margin-left: 0.5in; margin-right: 0.5in;"><h3 class="title">Note</h3><p> 1040 This is defined only once in the 1041 <tt class="filename">httpd.conf</tt> 1042 file. 1043 </p></div></dd></dl></div></div><div class="section" lang="en" xml:lang="en"><div class="titlepage"><div><div><h4 class="title"><a id="id2869478"></a>Configuring What Is logged</h4></div></div><div></div></div><div class="variablelist"><dl><dt><a id="Conf.LogSQLTransferLogFormat"></a><span class="term">LogSQLTransferLogFormat</span></dt><dd><div class="cmdsynopsis"><p><tt class="command">LogSQLTransferLogFormat</tt> { 1044 <i class="replaceable"><tt>format-string</tt></i> 1045 }</p></div><p>Example: LogSQLTransferLogFormat huSUTv</p><p>Default: AbHhmRSsTUuv</p><p>Context: virtual host</p><p> 1046 Each character in the format-string defines an attribute 1047 of the request that you wish to log. The default logs 1048 the information required to create Combined Log Format 1049 logs, plus several extras. Here is the full list of 1050 allowable keys, which sometimes resemble their Apache 1051 counterparts, but do not always: 1052 </p><div class="table"><a id="id2869536"></a><p class="title"><b>Table 6. Core LogFormat parameters</b></p><table summary="Core LogFormat parameters" border="1"><colgroup><col /><col /><col /><col /><col /></colgroup><thead><tr><th>Symbol</th><th>Meaning</th><th>DB Field</th><th>Data Type</th><th>Example</th></tr></thead><tbody><tr><td>A</td><td>User Agent</td><td>agent</td><td>varchar(255)</td><td> 1053 Mozilla/4.0 (compat; MSIE 6.0; Windows) 1054 </td></tr><tr><td>a</td><td>CGi request arguments</td><td>request_args</td><td>varchar(255)</td><td> 1055 user=Smith&cart=1231&item=532 1056 </td></tr><tr><td>b</td><td>Bytes transfered</td><td>bytes_sent</td><td>int unsigned</td><td>32561</td></tr><tr><td> 1057 c 1058 <a href="#Foot.LogCookie">???</a></td><td>Text of cookie</td><td>cookie</td><td>varchar(255)</td><td> 1059 Apache=sdyn.fooonline.net 1300102700823 1060 </td></tr><tr><td>f</td><td>Local filename requested</td><td>request_file</td><td>varchar(255)</td><td>/var/www/html/books-cycroad.html</td></tr><tr><td>H</td><td>HTTP request_protocol</td><td>request_protocol</td><td>varchar(10)</td><td>HTTP/1.1</td></tr><tr><td>h</td><td>Name of remote host</td><td>remote_host</td><td>varchar(50)</td><td>blah.foobar.com</td></tr><tr><td>I</td><td>Request ID (from modd_unique_id)</td><td>id</td><td>char(19)</td><td>POlFcUBRH30AAALdBG8</td></tr><tr><td>l</td><td>Ident user info</td><td>remote_logname</td><td>varcgar(50)</td><td>bobby</td></tr><tr><td>M</td><td> 1061 Machine ID 1062 <a href="#Foot.MachineID">???</a></td><td>machine_id</td><td>varchar(25)</td><td>web01</td></tr><tr><td>m</td><td>HTTP request method</td><td>request_method</td><td>varchar(10)</td><td>GET</td></tr><tr><td>P</td><td>httpd cchild PID</td><td>child_pid</td><td>smallint unsigned</td><td>3215</td></tr><tr><td>p</td><td>http port</td><td>server_port</td><td>smallint unsigned</td><td>80</td></tr><tr><td>R</td><td>Referer</td><td>referer</td><td>varchar(255)</td><td> 1063 http://www.biglinks4u.com/linkpage.html 1064 </td></tr><tr><td>r</td><td>Request in full form</td><td>request_line</td><td>varchar(255)</td><td>GET /books-cycroad.html HTTP/1.1</td></tr><tr><td>S</td><td> 1065 Time of request in UNIX time_t format 1066 </td><td>time_stamp</td><td>int unsigned</td><td>1005598029</td></tr><tr><td>s</td><td>HTTP Response Code Status</td><td>status</td><td>smallint</td><td>200</td></tr><tr><td>T</td><td>Seconds to service request</td><td>request_duration</td><td>smallint unsigned</td><td>2</td></tr><tr><td>t</td><td>Time of request in human format</td><td>request_time</td><td>char(28)</td><td>[02/Dec/2001:15:01:26 -0800]</td></tr><tr><td>U</td><td>Request in simple form</td><td>request_uri</td><td>varchar(255)</td><td>/books-cycroad.html</td></tr><tr><td>u</td><td>User info from HTTP auth</td><td>remote_user</td><td>varchar(50)</td><td>bobby</td></tr><tr><td>v</td><td>Virtual host servicing the request</td><td>virtual_host</td><td>varchar(255)</td><td>www.foobar.com</td></tr><tr><td>V</td><td> 1067 requested Virtual host name (mass 1068 virtualhosting) 1069 </td><td>virtual_host</td><td>varchar(255)</td><td>www.foobar.org</td></tr></tbody></table></div><div class="note" style="margin-left: 0.5in; margin-right: 0.5in;"><h3 class="title">Note</h3><p><a id="Foot.LogCookie"></a> 1070 [1] You must also specify LogSQLWhichCookie for this 1071 to take effect. 1072 </p><p><a id="Foot.MachineID"></a> 1073 [2] You must also specify LogSQLmachineID for this to 1074 take effect. 1075 </p></div><div class="table"><a id="id2870165"></a><p class="title"><b>Table 7. SSL LogFormat Parameters</b></p><table summary="SSL LogFormat Parameters" border="1"><colgroup><col /><col /><col /><col /><col /></colgroup><thead><tr><th>Symbol</th><th>Meaning</th><th>DB Field</th><th>Data Type</th><th>Example</th></tr></thead><tbody><tr><td>z</td><td>SSL cipher used</td><td>ssl_cipher</td><td>varchar(25)</td><td>RC4-MD5</td></tr><tr><td>q</td><td> 1076 Keysize of the SSL connection 1077 </td><td>ssl_keysize</td><td>smallint unsigned</td><td>56</td></tr><tr><td>Q</td><td> 1078 maximum keysize supported 1079 </td><td>ssl_maxkeysize</td><td>smallint unsigned</td><td>128</td></tr></tbody></table></div><div class="table"><a id="id2870335"></a><p class="title"><b>Table 8. LogIO LogFormat Parameters</b></p><table summary="LogIO LogFormat Parameters" border="1"><colgroup><col /><col /><col /><col /><col /></colgroup><thead><tr><th>Symbol</th><th>Meaning</th><th>DB Field</th><th>Data Type</th><th>Example</th></tr></thead><tbody><tr><td>i</td><td>Number of actual Bytes transfered in with the request</td><td>bytes_in</td><td>int unsigned</td><td>505</td></tr><tr><td>o</td><td>Number of actual Bytes transfered out with the request</td><td>bytes_out</td><td>int unsigned</td><td>4168</td></tr></tbody></table></div></dd><dt><span class="term">LogSQLRemhostIgnore</span></dt><dd><div class="cmdsynopsis"><p><tt class="command">LogSQLRemhostIgnore</tt> { 1080 <i class="replaceable"><tt>hostname</tt></i> 1081 ...}</p></div><p> 1082 Example: LogSQLRemhostIgnore localnet.com 1083 </p><p>Context: virtual host</p><p> 1084 Lists a series of smortrings that, if present in the 1085 REMOTE_HOST, will cause that request to 1086 <span class="emphasis"><em>not</em></span> 1087 be logged. This directive is useful for cutting down on 1088 log clutter when you are certain that you want to ignore 1089 requests from certain hosts, such as your own internal 1090 network machines. See section 1091 <a href="#Sect.Ignore"> 1092 Instructing the module what NOT to log using filtering 1093 directives 1094 </a> 1095 for some tips for using this directive. 1096 </p><p> 1097 Each string may contain a + or - prefix in a 1098 <VirtualHost> context and will cause those strings 1099 to be added (+) or removed (-) from the global 1100 configuration. Otherwise the global is completely 1101 ignored and overridden if defined in a 1102 <VirtualHost> 1103 </p><p> 1104 Each string is separated by a space, and no regular 1105 expressions or globbing are allowed. Each string is 1106 evaluated as a substring of the REMOTE_HOST using 1107 strstr(). The comparison is case sensitive. 1108 </p></dd><dt><span class="term">LogSQLRequestAccept</span></dt><dd><div class="cmdsynopsis"><p><tt class="command">LogSQLRequestAccept</tt> { 1109 <i class="replaceable"><tt>substring</tt></i> 1110 ...}</p></div><p> 1111 Example: LogSQLRequestAccept .html .php .jpg 1112 </p><p> 1113 Default: if not specified, all requests are 'accepted' 1114 </p><p>Context: virtual host</p><p> 1115 Lists a series of strings that, if present in the URI, 1116 will permit that request to be considered for logging 1117 (depending on additional filtering by the "ignore" 1118 directives). Any request that fails to match one of the 1119 LogSQLRequestAccept entries will be discarded. 1120 </p><p> 1121 Each string may contain a + or - prefix in a 1122 <VirtualHost> context and will cause those strings 1123 to be added (+) or removed (-) from the global 1124 configuration. Otherwise the global is completely 1125 ignored and overridden if defined in a 1126 <VirtualHost> 1127 </p><p> 1128 This directive is useful for cutting down on log clutter 1129 when you are certain that you only want to log certain 1130 kinds of requests, and just blanket-ignore everything 1131 else. See section 1132 <a href="#Sect.Ignore"> 1133 Instructing the module what NOT to log using filtering 1134 directives 1135 </a> 1136 for some tips for using this directive. 1137 </p><p> 1138 Each string is separated by a space, and no regular 1139 expressions or globbing are allowed. Each string is 1140 evaluated as a substring of the URI using strstr(). The 1141 comparison is case sensitive. 1142 </p><p> 1143 This directive is completely optional. It is more 1144 general than LogSQLRequestIgnore and is evaluated before 1145 LogSQLRequestIgnore . If this directive is not used, 1146 <span class="emphasis"><em>all</em></span> 1147 requests are accepted and passed on to the other 1148 filtering directives. Therefore, only use this directive 1149 if you have a specific reason to do so. 1150 </p></dd><dt><span class="term">LogSQLRequestIgnore</span></dt><dd><div class="cmdsynopsis"><p><tt class="command">LogSQLRequestIgnore</tt> { 1151 <i class="replaceable"><tt>substring</tt></i> 1152 ...}</p></div><p> 1153 Example: LogSQLRequestIgnore root.exe cmd.exe 1154 default.ida favicon.ico 1155 </p><p>Context: virtual host</p><p> 1156 Lists a series of strings that, if present in the URI, 1157 will cause that request to 1158 <span class="emphasis"><em>NOT</em></span> 1159 be logged. This directive is useful for cutting down on 1160 log clutter when you are certain that you want to ignore 1161 requests for certain objects. See section 1162 <a href="#Sect.Ignore"> 1163 Instructing the module what NOT to log using filtering 1164 directives 1165 </a> 1166 for some tips for using this directive. 1167 </p><p> 1168 Each string may contain a + or - prefix in a 1169 <VirtualHost> context and will cause those strings 1170 to be added (+) or removed (-) from the global 1171 configuration. Otherwise the global is completely 1172 ignored and overridden if defined in a 1173 <VirtualHost> 1174 </p><p> 1175 Each string is separated by a space, and no regular 1176 expressions or globbing are allowed. Each string is 1177 evaluated as a substring of the URI using strstr(). The 1178 comparison is case sensitive. 1179 </p></dd><dt><span class="term">LogSQLWhichCookie</span></dt><dd><div class="cmdsynopsis"><p><tt class="command">LogSQLWhichCookie</tt> { 1180 <i class="replaceable"><tt>cookiename</tt></i> 1181 }</p></div><p>Example; LogSQLWhichCookie Clicks</p><p>Context: virtual host</p><p> 1182 In HTTP, cookies have names to distinguish them from 1183 each other. Using mod_usertrack, for example, you can 1184 give your user-tracking cookies a name with the 1185 CookieName directive. 1186 </p><p> 1187 mod_log_sql allows you to log cookie information. 1188 LogSQL_WhichCookie tells mod_log_sql which cookie to 1189 log. This is necessary because you will usually be 1190 setting and receiving more than one cookie from a 1191 client. 1192 </p><div class="note" style="margin-left: 0.5in; margin-right: 0.5in;"><h3 class="title">Note</h3><p> 1193 You must include a 'c' character in 1194 LogSQLTransferLogFormat for this directive to take 1195 effect. 1196 </p><p> 1197 although this was origintally intended for people 1198 using mod_usertrack to create user-tracking cookies, 1199 you are not restricted in any way. You can choose 1200 which cookie you wish to log to the database - any 1201 cookie at all - and it does not necessarily have to 1202 have anything to do with mod_usertrack. 1203 </p></div></dd><dt><span class="term">LogSQLWhichCookies</span></dt><dd><div class="cmdsynopsis"><p><tt class="command">LogSQLWhichCookies</tt> { 1204 <i class="replaceable"><tt>cookie-name</tt></i> 1205 ...}</p></div><p> 1206 Example: logSQLWhichCookies userlogin cookie1 cookie2 1207 </p><p>Context: virtual host</p><p> 1208 Defines the list of cookies you would like logged. This 1209 works in conjunction with LogSQLCookieLogTable. This 1210 directive does 1211 <span class="emphasis"><em>not</em></span> 1212 require any additional characters to be added to the 1213 LogSQLTransferLogFormat string. The feature is activated 1214 simply by including this directive, upon which you will 1215 begin populating the separate cookie table with data. 1216 </p><p> 1217 Each string may contain a + or - prefix in a 1218 <VirtualHost> context and will cause those strings 1219 to be added (+) or removed (-) from the global 1220 configuration. Otherwise the global is completely 1221 ignored and overridden if defined in a 1222 <VirtualHost> 1223 </p><div class="note" style="margin-left: 0.5in; margin-right: 0.5in;"><h3 class="title">Note</h3><p> 1224 The table must be created (see create-tables.sql, 1225 included in the package), or LogSQLCreateTables must 1226 be set to 'On'. 1227 </p></div></dd><dt><span class="term">LogSQLWhichHeadersIn</span></dt><dd><div class="cmdsynopsis"><p><tt class="command">LogSQLWhichHeadersIn</tt> { 1228 <i class="replaceable"><tt>header-name</tt></i> 1229 ...}</p></div><p> 1230 Example: LogSQLWhichHeadersIn User-Agent Accept-Encoding 1231 Host 1232 </p><p>Context: virtual host</p><p> 1233 Defines the list of inbound headers you would like 1234 logged. This works in conjunction with 1235 LogSQLHeadersInLogTable. This directive does not require 1236 any additional characters to be added to the 1237 LogSQLTransferLogFormat string. The feature is activated 1238 simply by including this directive, upon which you will 1239 begin populating the separate inbound-headers table with 1240 data. 1241 </p><p> 1242 Each string may contain a + or - prefix in a 1243 <VirtualHost> context and will cause those strings 1244 to be added (+) or removed (-) from the global 1245 configuration. Otherwise the global is completely 1246 ignored and overridden if defined in a 1247 <VirtualHost> 1248 </p><div class="note" style="margin-left: 0.5in; margin-right: 0.5in;"><h3 class="title">Note</h3><p> 1249 The table must be created (see create-tables.sql, 1250 included in the package), or LogSQLCreateTables must 1251 be set to 'On'. 1252 </p></div></dd><dt><span class="term">LogSQLWhichHeadersOut</span></dt><dd><div class="cmdsynopsis"><p><tt class="command">LogSQLWhichHeadersOut</tt> { 1253 <i class="replaceable"><tt>header-name</tt></i> 1254 ...}</p></div><p> 1255 Example: LogSQLWhichHeadersOut Expires Content-Type 1256 Cache-Control 1257 </p><p>Context: virtual host</p><p> 1258 Defines the list of outbound headers you would like 1259 logged. This works in conjunction with 1260 LogSQLHeadersOutLogTable. This directive does not 1261 require any additional characters to be added to the 1262 LogSQLTransferLogFormat string. The feature is activated 1263 simply by including this directive, upon which you will 1264 begin populating the separate outbound-headers table 1265 with data. 1266 </p><p> 1267 Each string may contain a + or - prefix in a 1268 <VirtualHost> context and will cause those strings 1269 to be added (+) or removed (-) from the global 1270 configuration. Otherwise the global is completely 1271 ignored and overridden if defined in a 1272 <VirtualHost> 1273 </p><div class="note" style="margin-left: 0.5in; margin-right: 0.5in;"><h3 class="title">Note</h3><p> 1274 The table must be created (see create-tables.sql, 1275 included in the package), or LogSQLCreateTables must 1276 be set to 'On'. 1277 </p></div></dd><dt><span class="term">LogSQLWhichNotes</span></dt><dd><div class="cmdsynopsis"><p><tt class="command">LogSQLWhichNotes</tt> { 1278 <i class="replaceable"><tt>note-name</tt></i> 1279 ...}</p></div><p> 1280 Example: LogSQLWhichNotes mod_gzip_result 1281 mod_gzip_ompression_ratio 1282 </p><p>Context: virtual host</p><p> 1283 Defines the list of notes you would like logged. This 1284 works in conjunction with LogSQLNotesLogTable. This 1285 directive does not require any additional characters to 1286 be added to the LogSQLTransferLogFormat string. The 1287 feature is activated simply by including this directive, 1288 upon which you will begin populating the separate notes 1289 table with data. 1290 </p><p> 1291 Each string may contain a + or - prefix in a 1292 <VirtualHost> context and will cause those strings 1293 to be added (+) or removed (-) from the global 1294 configuration. Otherwise the global is completely 1295 ignored and overridden if defined in a 1296 <VirtualHost> 1297 </p><div class="note" style="margin-left: 0.5in; margin-right: 0.5in;"><h3 class="title">Note</h3><p> 1298 The table must be created (see create-tables.sql, 1299 included in the package), or LogSQLCreateTables must 1300 be set to 'On'. 1301 </p></div></dd></dl></div></div><div class="section" lang="en" xml:lang="en"><div class="titlepage"><div><div><h4 class="title"><a id="id2871137"></a>Deprecated Commands</h4></div></div><div></div></div><div class="variablelist"><dl><dt><span class="term">LogSQLSocketFile [Deprecated]</span></dt><dd><div class="cmdsynopsis"><p><tt class="command">LogSQLSocketFile</tt> { 1302 <i class="replaceable"><tt>filename</tt></i> 1303 }</p></div><p> 1304 Example: LogSQLSocketFile /tmp/mysql.sock 1305 </p><p>Default: (database specific)</p><p> 1306 Default (MySQL): /var/lib/mysql/mysql.sock 1307 </p><p>Context: main server config</p><p> 1308 At Apache runtime you can specify the MySQL socket file 1309 to use. Set this once in your main server config to 1310 override the default value. This value is irrelevant if 1311 your database resides on a separate machine. 1312 </p><p> 1313 mod_log_sql will automatically employ the socket for db 1314 communications if the database resides on the local 1315 host. If the db resides on a separate host the module 1316 will automatically use TCP/IP. This is a function of the 1317 MySQL API and is not user-configurable. 1318 </p><div class="note" style="margin-left: 0.5in; margin-right: 0.5in;"><h3 class="title">Note</h3><p> 1319 This directive is deprecated in favor of LogSQLDBParam 1320 socketfile [socketfilename] 1321 </p><p> 1322 This is defined only once in the 1323 <tt class="filename">httpd.conf</tt> 1324 file. 1325 </p></div></dd><dt><span class="term">LogSQLTCPPort [Deprecated]</span></dt><dd><div class="cmdsynopsis"><p><tt class="command">LogSQLTCPPort</tt> { 1326 <i class="replaceable"><tt>port-number</tt></i> 1327 }</p></div><p>Example: LogSQLTCPPort 3309</p><p>Default: (database specific)</p><p>Default (MySQL): 3306</p><p>Context: main server config</p><p> 1328 Your database may listen on a different port than the 1329 default. If so, use this directive to instruct the 1330 module which port to use. This directive only applies if 1331 the database is on a different machine connected via 1332 TCP/IP. 1333 </p><div class="note" style="margin-left: 0.5in; margin-right: 0.5in;"><h3 class="title">Note</h3><p> 1334 This directive is deprecated in favor of LogSQLDBParam 1335 tcpport [port-number] 1336 </p><p> 1337 This is defined only once in the 1338 <tt class="filename">httpd.conf</tt> 1339 file. 1340 </p></div></dd><dt><span class="term">LogSQLDatabase [Deprecated]</span></dt><dd><div class="cmdsynopsis"><p><tt class="command">LogSQLDatabase</tt> { 1341 <i class="replaceable"><tt>database</tt></i> 1342 }</p></div><p>Example: LogSQLDatabase loggingdb</p><p>Context: main server config</p><p> 1343 Defines the database that is used for logging. 1344 "database" must be a valid db on the MySQL host defined 1345 in LogSQLLoginInfo 1346 </p><div class="note" style="margin-left: 0.5in; margin-right: 0.5in;"><h3 class="title">Note</h3><p> 1347 This directive is deprecated in favor of the URI form 1348 of LogSQLLoginInfo. 1349 </p><p> 1350 This is defined only once in the 1351 <tt class="filename">httpd.conf</tt> 1352 file. 1353 </p></div></dd></dl></div></div></div></div><div class="section" lang="en" xml:lang="en"><div class="titlepage"><div><div><h2 class="title" style="clear: both"><a id="Sect.FAQ"></a>FAQ</h2></div></div><div></div></div><div class="qandaset"><table border="0" summary="Q and A Set"><col align="left" width="1%" /><tbody><tr class="qandadiv"><td align="left" valign="top" colspan="2"><a id="id2871366"></a><h4 class="title"><a id="id2871366"></a>1. General module questions</h4></td></tr><tr class="toc" colspan="2"><td align="left" valign="top" colspan="2"><dl><dt>1.1. <a href="#FAQ.WhyLogToSQL">Why log to an SQL database?</a></dt><dt>1.2. <a href="#id2871890">Why use MySQL? Are there alternatives?</a></dt><dt>1.3. <a href="#id2871942">Is this code production-ready?</a></dt><dt>1.4. <a href="#id2871966">Who's using mod_log_sql?</a></dt><dt>1.5. <a href="#id2871991"> 1354 Why doesn't the module also replace the Apache ErrorLog? 1355 </a></dt><dt>1.6. <a href="#id2872035">Does mod_log_sql work with Apache 2.x?</a></dt><dt>1.7. <a href="#id2872054"> 1356 Does mod_log_sql connect to MySQL via TCP/IP or a socket? 1357 </a></dt><dt>1.8. <a href="#id2872116">I have discovered a bug. Who can I contact?</a></dt></dl></td></tr><tr class="question"><td align="left" valign="top"><a id="FAQ.WhyLogToSQL"></a><a id="id2871376"></a><b>1.1. </b></td><td align="left" valign="top"><p>Why log to an SQL database?</p></td></tr><tr class="answer"><td align="left" valign="top"><b></b></td><td align="left" valign="top"><p> 1358 To begin with, let's get it out of the way: logging to a 1359 database is not a panacea. But while there are 1360 complexities with this solution, the benefit can be 1361 substantial for certain classes of administrator or people 1362 with advanced requirements: 1363 </p><div class="itemizedlist"><ul type="disc"><li><p> 1364 Chores like log rotation go away, as you can DELETE 1365 records from the SQL database once they are no longer 1366 useful. For example, the excellent and popular 1367 log-analysis tool Webalizer (http://www.webalizer.com) 1368 does not need historic logs after it has processed 1369 them, enabling you to delete older logs. 1370 </p></li><li><p> 1371 People with clusters of web servers (for high 1372 availability) will benefit the most - all their 1373 webservers can log to a single SQL database. This 1374 obviates the need to collate/interleave the many 1375 separate logfiles, which can be / highly/ problematic. 1376 </p></li><li><p> 1377 People acquainted with the power of SQL SELECT 1378 statements will know the flexibility of the extraction 1379 possibilities at their fingertips. 1380 </p></li></ul></div><p> 1381 For example, do you want to see all your 404's? Do this: 1382 </p><pre class="programlisting">SELECT remote_host, status, request_uri, bytes_sent, from_unixtime(time_stamp) 1383 FROM acc_log_tbl WHERE status=404 ORDER BY time_stamp;</pre><div class="table"><a id="id2871444"></a><p class="title"><b>Table 9. </b></p><table summary="" border="1"><colgroup><col /><col /><col /><col /><col /></colgroup><thead><tr><th>remote_host</th><th>status</th><th>request_uri</th><th>bytes_sent</th><th>from_unixtime(time_stamp)</th></tr></thead><tbody><tr><td>marge.mmm.co.uk</td><td>404</td><td>/favicon.ico</td><td>321</td><td>2001-11-20 02:30:56</td></tr><tr><td>62.180.239.251</td><td>404</td><td>/favicon.ico</td><td>333</td><td>2001-11-20 02:45:25</td></tr><tr><td>212.234.12.66</td><td>404</td><td>/favicon.ico</td><td>321</td><td>2001-11-20 03:01:00</td></tr><tr><td>212.210.78.254</td><td>404</td><td>/favicon.ico</td><td>333</td><td>2001-11-20 03:26:05</td></tr></tbody></table></div><p> 1384 Or do you want to see how many bytes you've sent within a 1385 certain directory or site? Do this: 1386 </p><pre class="programlisting">SELECT request_uri,sum(bytes_sent) AS bytes, count(request_uri) AS howmany 1387 FROM acc_log_tbl 1388 WHERE request_uri LIKE '%mod_log_sql%' 1389 GROUP BY request_uri ORDER BY howmany DESC;</pre><div class="table"><a id="id2871645"></a><p class="title"><b>Table 10. </b></p><table summary="" border="1"><colgroup><col /><col /><col /></colgroup><thead><tr><th>request_uri</th><th>bytes</th><th>howmany</th></tr></thead><tbody><tr><td>/mod_log_sql/style_1.css</td><td>157396</td><td>1288</td></tr><tr><td>/mod_log_sql/</td><td>2514337</td><td>801</td></tr><tr><td> 1390 /mod_log_sql/mod_log_sql.tar.gz 1391 </td><td>9769312</td><td>456</td></tr><tr><td>/mod_log_sql/faq.html</td><td>5038728</td><td>436</td></tr></tbody></table></div><p> 1392 Or maybe you want to see who's linking to you? Do this: 1393 </p><pre class="programlisting">SELECT count(referer) AS num,referer 1394 FROM acc_log_tbl 1395 WHERE request_uri='/mod_log_sql/' 1396 GROUP BY referer ORDER BY num DESC;</pre><div class="table"><a id="id2871784"></a><p class="title"><b>Table 11. </b></p><table summary="" border="1"><colgroup><col /><col /></colgroup><thead><tr><th>num</th><th>referer</th></tr></thead><tbody><tr><td>271</td><td> 1397 http://freshmeat.net/projects/mod_log_sql/ 1398 </td></tr><tr><td>96</td><td> 1399 http://modules.apache.org/search?id=339 1400 </td></tr><tr><td>48</td><td>http://freshmeat.net/</td></tr><tr><td>8</td><td>http://freshmeat.net</td></tr></tbody></table></div><p> 1401 As you can see, there are myriad possibilities that can be 1402 constructed with the wonderful SQL SELECT statement. 1403 Logging to an SQL database can be really quite useful! 1404 </p></td></tr><tr class="question"><td align="left" valign="top"><a id="id2871890"></a><a id="id2871893"></a><b>1.2. </b></td><td align="left" valign="top"><p>Why use MySQL? Are there alternatives?</p></td></tr><tr class="answer"><td align="left" valign="top"><b></b></td><td align="left" valign="top"><p> 1405 MySQL is a robust, free, and very powerful 1406 production-quality database engine. It is well supported 1407 and comes with detailed documentation. Many 3rd-party 1408 software pacakges (e.g. Slashcode, the engine that powers 1409 Slashdot) run exclusively with MySQL. In other words, you 1410 will belong to a very robust and well-supported community 1411 by choosing MySQL. 1412 </p><p> 1413 That being said, there are alternatives. PostgreSQL is 1414 probably MySQL's leading "competitor" in the free database 1415 world. There is also an excellent module available for 1416 Apache to permit logging to a PostgreSQL database, called 1417 <a href="http://www.digitalstratum.com/pglogd/" target="_top"> 1418 pgLOGd 1419 </a> 1420 </p></td></tr><tr class="answer"><td align="left" valign="top"><b></b></td><td align="left" valign="top"><div class="note" style="margin-left: 0.5in; margin-right: 0.5in;"><h3 class="title">Note</h3><p> 1421 Currently a database abstraction system is in the works 1422 to allow any database to be used with mod_log_sql. 1423 </p></div></td></tr><tr class="question"><td align="left" valign="top"><a id="id2871942"></a><a id="id2871944"></a><b>1.3. </b></td><td align="left" valign="top"><p>Is this code production-ready?</p></td></tr><tr class="answer"><td align="left" valign="top"><b></b></td><td align="left" valign="top"><p> 1424 By all accounts it is. It is known to work without a 1425 problem on many-thousands-of-hits-per-day webservers. Does 1426 that mean it is 100% bug free? Well, no software is, but 1427 it is well-tested and believed to be fully compatible with 1428 production environments. (The usual disclaimers apply. 1429 This software is provided without warranty of any kind.) 1430 </p></td></tr><tr class="question"><td align="left" valign="top"><a id="id2871966"></a><a id="id2871969"></a><b>1.4. </b></td><td align="left" valign="top"><p>Who's using mod_log_sql?</p></td></tr><tr class="answer"><td align="left" valign="top"><b></b></td><td align="left" valign="top"><p> 1431 Good question! It would be great to find out! If you are a 1432 production-level mod_log_sql user, please contact eddie at 1433 <tt class="email"><<a href="mailto:urkle <at> outoforder <dot> cc">urkle <at> outoforder <dot> cc</a>></tt> 1434 so that you can be mentioned here. 1435 </p></td></tr><tr class="question"><td align="left" valign="top"><a id="id2871991"></a><a id="id2871993"></a><b>1.5. </b></td><td align="left" valign="top"><p> 1436 Why doesn't the module also replace the Apache ErrorLog? 1437 </p></td></tr><tr class="answer"><td align="left" valign="top"><b></b></td><td align="left" valign="top"><p> 1438 There are circumstances when that would be quite unwise -- 1439 for example, if Apache could not reach the MySQL server 1440 for some reason and needed to log that fact. Without a 1441 text-based error log you'd never know anything was wrong, 1442 because Apache would be trying to log a database 1443 connection error to the database... you get the point. 1444 </p></td></tr><tr class="answer"><td align="left" valign="top"><b></b></td><td align="left" valign="top"><p> 1445 Error logs are usually not very high-traffic and are 1446 really best left as text files on a web server machine. 1447 </p></td></tr><tr class="answer"><td align="left" valign="top"><b></b></td><td align="left" valign="top"><p> 1448 The Error log is free format text.. (no specified 1449 formatting what, so ever) which is rather difficult to 1450 nicely format for storing in a database. 1451 </p></td></tr><tr class="question"><td align="left" valign="top"><a id="id2872035"></a><a id="id2872037"></a><b>1.6. </b></td><td align="left" valign="top"><p>Does mod_log_sql work with Apache 2.x?</p></td></tr><tr class="answer"><td align="left" valign="top"><b></b></td><td align="left" valign="top"><p> 1452 Yes. A port of mod_log_sql is available for Apache 2.x as 1453 of mod_log_sql 1.90 1454 </p></td></tr><tr class="question"><td align="left" valign="top"><a id="id2872054"></a><a id="id2872056"></a><b>1.7. </b></td><td align="left" valign="top"><p> 1455 Does mod_log_sql connect to MySQL via TCP/IP or a socket? 1456 </p></td></tr><tr class="answer"><td align="left" valign="top"><b></b></td><td align="left" valign="top"><p>Quick answer, Yes.</p></td></tr><tr class="answer"><td align="left" valign="top"><b></b></td><td align="left" valign="top"><p> 1457 It depends! This is not determined by mod_log_sql. 1458 mod_log_sql relies on a connection command that is 1459 supplied in the MySQL API, and that command is somewhat 1460 intelligent. How it works: 1461 </p><div class="itemizedlist"><ul type="disc"><li> 1462 if the specified MySQL database is on the same 1463 machine, the connection command uses a socket to 1464 communicate with MySQL 1465 </li><li> 1466 if the specified MySQL database is on a different 1467 machine, mod_log_sql connects using TCP/IP. 1468 </li></ul></div><p> 1469 You don't have any control of which methodology is used. 1470 You can fine-tune some of the configuration, however. The 1471 LogSQLSocketFile runtime configuration directive overrides 1472 the default of "/var/lib/mysql/mysql.sock" for 1473 socket-based connections, whereas the LogSQLTCPPort 1474 command allows to you override the default TCP port of 1475 3306 for TCP/IP connections. 1476 </p></td></tr><tr class="question"><td align="left" valign="top"><a id="id2872116"></a><a id="id2872118"></a><b>1.8. </b></td><td align="left" valign="top"><p>I have discovered a bug. Who can I contact?</p></td></tr><tr class="answer"><td align="left" valign="top"><b></b></td><td align="left" valign="top"><p> 1477 Please contact Edward Rudd at 1478 <tt class="email"><<a href="mailto:urkle <at> outoforder <dot> cc">urkle <at> outoforder <dot> cc</a>></tt> 1479 , or post a message to the mod_log_sql 1480 <a href="#Sect.MailingLists">Mailing Lists</a> 1481 . Your comments, suggestions, bugfixes, bug catches, and 1482 usage testimonials are always welcome. As free software, 1483 mod_log_sql is intended to be a community effort -- any 1484 code contributions or other ideas will be fully and openly 1485 credited, of course. 1486 </p></td></tr><tr class="qandadiv"><td align="left" valign="top" colspan="2"><a id="id2872152"></a><h4 class="title"><a id="id2872152"></a>2. Problems</h4></td></tr><tr class="toc" colspan="2"><td align="left" valign="top" colspan="2"><dl><dt>2.1. <a href="#id2872158"> 1487 Apache segfaults or has other problems when using PHP and 1488 mod_log_sql 1489 </a></dt><dt>2.2. <a href="#FAQ.NothingLogged"> 1490 Apache appears to start up fine, but nothing is getting 1491 logged in the database 1492 </a></dt><dt>2.3. <a href="#id2872315"> 1493 Why do I get the message "insufficient configuration info 1494 to establish database link" in my Apache error log? 1495 </a></dt><dt>2.4. <a href="#id2872340"> 1496 My database cannot handle all the open connections from 1497 mod_log_sql, is there anything I can do? 1498 </a></dt><dt>2.5. <a href="#id2872429"> 1499 Why do I occasionally see a "lost connection to MySQL 1500 server" message in my Apache error log? 1501 </a></dt><dt>2.6. <a href="#id2872477"> 1502 Sometimes a single VirtualHost gets logged to two 1503 different tables (e.g. access_foo_com, 1504 access_www_foo_com). Or, accesses to an unqualified 1505 hostname (e.g. "http://intranet/index.html") get logged in 1506 separate tables. 1507 </a></dt></dl></td></tr><tr class="question"><td align="left" valign="top"><a id="id2872158"></a><a id="id2872160"></a><b>2.1. </b></td><td align="left" valign="top"><p> 1508 Apache segfaults or has other problems when using PHP and 1509 mod_log_sql 1510 </p></td></tr><tr class="answer"><td align="left" valign="top"><b></b></td><td align="left" valign="top"><p> 1511 This occurs if you compiled PHP with MySQL database 1512 support. PHP utilizes its internal, bundled MySQL 1513 libraries by default. These conflict with the "real" MySQL 1514 libraries linked by mod_log_sql, causing the segmentation 1515 fault. 1516 </p><p> 1517 PHP and mod_log_sql can be configured to happily coexist. 1518 The solution is to configure PHP to link against the real 1519 MySQL libraries: recompile PHP using 1520 --with-mysql=/your/path. Apache will run properly once the 1521 modules are all using the same version of the MySQL 1522 libraries. 1523 </p></td></tr><tr class="question"><td align="left" valign="top"><a id="FAQ.NothingLogged"></a><a id="id2872196"></a><b>2.2. </b></td><td align="left" valign="top"><p> 1524 Apache appears to start up fine, but nothing is getting 1525 logged in the database 1526 </p></td></tr><tr class="answer"><td align="left" valign="top"><b></b></td><td align="left" valign="top"><p> 1527 If you do not see any entries in the access_log, then 1528 something is preventing the inserts from happening. This 1529 could be caused by several things: 1530 </p><div class="itemizedlist"><ul type="disc"><li> 1531 Improper privileges set up in the MySQL database 1532 </li><li> 1533 You are not hitting a VirtualHost that has a 1534 LogSQLTransferLogTable entry 1535 </li><li> 1536 You did not specify the right database host or login 1537 information 1538 </li><li> 1539 Another factor is preventing a connection to the 1540 database 1541 </li></ul></div><div class="note" style="margin-left: 0.5in; margin-right: 0.5in;"><h3 class="title">Note</h3><p> 1542 It is improper to ask for help before you have followed 1543 these steps. 1544 </p></div><p> 1545 First examine the MySQL log that you established in step 1546 <a href="#Item.EnableLogging">6</a> 1547 of section 1548 <a href="#Sect.Preperation"> 1549 Preparing MySQL for logging 1550 </a> 1551 . Ensure that the INSERT statements are not being rejected 1552 because of a malformed table name or other typographical 1553 error. By enabling that log, you instructed MySQL to log 1554 every connection and command it receives -- if you see no 1555 INSERT attempts in the log, the module isn't successfully 1556 connecting to the database. If you see nothing at all in 1557 the log -- not even a record of your administrative 1558 connection attempts, then you did not enable the log 1559 correctly. If you do see INSERT attempts but they are 1560 failing, the log should tell you why. 1561 </p><p> 1562 Second, confirm that your LogSQL* directives are all 1563 correct. 1564 </p><p> 1565 Third, examine the Apache error logs for messages from 1566 mod_log_sql; the module will offer hints as to why it 1567 cannot connect, etc. 1568 </p><p> 1569 The next thing to do is to change the LogLevel directive 1570 <span class="emphasis"><em> 1571 in the main server config as well as in each VirtualHost 1572 config: 1573 </em></span> 1574 </p><pre class="programlisting">LogLevel debug 1575 ErrorLog /var/log/httpd/server-messages</pre></td></tr><tr class="question"><td align="left" valign="top"><a id="id2872315"></a><a id="id2872317"></a><b>2.3. </b></td><td align="left" valign="top"><p> 1576 Why do I get the message "insufficient configuration info 1577 to establish database link" in my Apache error log? 1578 </p></td></tr><tr class="answer"><td align="left" valign="top"><b></b></td><td align="left" valign="top"><p> 1579 At a minimum, LogSQLLoginInfo in the URl form and either 1580 LogSQLTableName or LogSQLMassVirtualHosting must be 1581 defined in order for the module to be able to establish a 1582 database link. If these are not defined or are incomplete 1583 you will receive this error message. 1584 </p></td></tr><tr class="question"><td align="left" valign="top"><a id="id2872340"></a><a id="id2872342"></a><b>2.4. </b></td><td align="left" valign="top"><p> 1585 My database cannot handle all the open connections from 1586 mod_log_sql, is there anything I can do? 1587 </p></td></tr><tr class="answer"><td align="left" valign="top"><b></b></td><td align="left" valign="top"><p> 1588 The rule of thumb: if you have n webservers each 1589 configured to support y MaxClients, then your database 1590 must be able to handle n times y simultaneous connections 1591 in the worst case. Certainly you must use common sense, 1592 consider reasonable traffic expectations and structure 1593 things accordingly. 1594 </p></td></tr><tr class="answer"><td align="left" valign="top"><b></b></td><td align="left" valign="top"><p> 1595 Tweaking my.cnf to scale to high connection loads is 1596 imperative. But if hardware limitations prevent your MySQL 1597 server from gracefully handling the number of incoming 1598 connections, it would be beneficial to upgrade the memory 1599 or CPU on that server in order to handle the load. 1600 </p></td></tr><tr class="answer"><td align="left" valign="top"><b></b></td><td align="left" valign="top"><p> 1601 Jeremy Zawodny, a highly respected MySQL user and 1602 contributor to Linux Magazine, has this very helpful and 1603 highly appropriate article on tuning MySQL: 1604 <a href="http://jeremy.zawodny.com/blog/archives/000173.html" target="_top"> 1605 MySQL, Linux, and Thread Caching 1606 </a> 1607 </p></td></tr><tr class="answer"><td align="left" valign="top"><b></b></td><td align="left" valign="top"><p> 1608 Please remember that mod_log_sql's overriding principle is 1609 performance -- that is what the target audience demands 1610 and expects. Other database logging solutions do not open 1611 and maintain many database connections, but their 1612 performance suffers drastically. For example, pgLOGd 1613 funnels all log connections through a separate daemon that 1614 connects to the database, but that bottlenecks the entire 1615 process. mod_log_sql achieves performance numbers an order 1616 of magnitude greater than the alternatives because it 1617 dispenses with the overhead associated with rapid 1618 connection cycling, and it does not attempt to shoehorn 1619 all the database traffic through a single extra daemon or 1620 proxy process. 1621 </p></td></tr><tr class="answer"><td align="left" valign="top"><b></b></td><td align="left" valign="top"><div class="note" style="margin-left: 0.5in; margin-right: 0.5in;"><h3 class="title">Note</h3><p> 1622 Currently connection pooling is being implemented as 1623 part of the Database Abstraction layer to allow multiple 1624 httpd processes to share connections. 1625 </p></div></td></tr><tr class="question"><td align="left" valign="top"><a id="id2872429"></a><a id="id2872431"></a><b>2.5. </b></td><td align="left" valign="top"><p> 1626 Why do I occasionally see a "lost connection to MySQL 1627 server" message in my Apache error log? 1628 </p></td></tr><tr class="answer"><td align="left" valign="top"><b></b></td><td align="left" valign="top"><p> 1629 This message may appear every now and then in your Apache 1630 error log, especially on very lightly loaded servers. This 1631 does not mean that anything is necessarily wrong. Within 1632 each httpd child process, mod_log_sql will open (and keep 1633 open) a connection to the MySQL server. MySQL, however, 1634 will close connections that have not been used in a while; 1635 the default timeout is 8 hours. When this occurs, 1636 mod_log_sql will notice and re-open the connection. That 1637 event is what is being logged, and looks like this: 1638 </p><pre class="programlisting">[Tue Nov 12 19:04:10 2002] [error] mod_log_sql: first attempt failed, 1639 API said: error 2013, Lost connection to MySQL server during query 1640 [Tue Nov 12 19:04:10 2002] [error] mod_log_sql: reconnect successful 1641 [Tue Nov 12 19:04:10 2002] [error] mod_log_sql: second attempt successful</pre><p> 1642 Reference: 1643 <a href="http://dev.mysql.com/doc/mysql/en/Gone_away.html" target="_top"> 1644 MySQL documentation 1645 </a> 1646 </p></td></tr><tr class="question"><td align="left" valign="top"><a id="id2872477"></a><a id="id2872479"></a><b>2.6. </b></td><td align="left" valign="top"><p> 1647 Sometimes a single VirtualHost gets logged to two 1648 different tables (e.g. access_foo_com, 1649 access_www_foo_com). Or, accesses to an unqualified 1650 hostname (e.g. "http://intranet/index.html") get logged in 1651 separate tables. 1652 </p></td></tr><tr class="answer"><td align="left" valign="top"><b></b></td><td align="left" valign="top"><p> 1653 Proper usage of the Apache runtime ServerName directive 1654 and the directive UseCanonicalName On (or DNS) are 1655 necessary to prevent this problem. "On" is the default for 1656 UseCanonicalName, and specifies that self-referential URLs 1657 are generated from the ServerName part of your 1658 VirtualHost: 1659 </p><p> 1660 With UseCanonicalName on (and in all versions prior to 1661 1.3) Apache will use the ServerName and Port directives to 1662 construct the canonical name for the server. With 1663 UseCanonicalName off Apache will form self-referential 1664 URLs using the hostname and port supplied by the client if 1665 any are supplied (otherwise it will use the canonical 1666 name, as defined above). [From 1667 <a href="http://httpd.apache.org/docs/mod/core.html#usecanonicalname" target="_top"> 1668 the Apache documentation 1669 </a> 1670 ] 1671 </p><p> 1672 The module inherits Apache's "knowledge" about the server 1673 name being accessed. As long as those two directives are 1674 properly configured, mod_log_sql will log to only one 1675 table per virtual host while using 1676 LogSQLMassVirtualHosting. 1677 </p></td></tr><tr class="qandadiv"><td align="left" valign="top" colspan="2"><a id="id2872534"></a><h4 class="title"><a id="id2872534"></a>3. Performance and Tuning</h4></td></tr><tr class="toc" colspan="2"><td align="left" valign="top" colspan="2"><dl><dt>3.1. <a href="#id2872540">How well does it perform?</a></dt><dt>3.2. <a href="#id2872724"> 1678 Do I need to be worried about all the running MySQL 1679 children? Will holding open n Apache-to-MySQL connections 1680 consume a lot of memory? 1681 </a></dt><dt>3.3. <a href="#id2872806"> 1682 My webserver cannot handle all the traffic that my site 1683 receives, is there anything I can do? 1684 </a></dt><dt>3.4. <a href="#FAQ.DelayedInsert"> 1685 What is the issue with activating delayed inserts? 1686 </a></dt></dl></td></tr><tr class="question"><td align="left" valign="top"><a id="id2872540"></a><a id="id2872542"></a><b>3.1. </b></td><td align="left" valign="top"><p>How well does it perform?</p></td></tr><tr class="answer"><td align="left" valign="top"><b></b></td><td align="left" valign="top"><p> 1687 mod_log_sql scales to very high loads. Apache 1.3.22 + 1688 mod_log_sql was benchmarked using the "ab" (Apache Bench) 1689 program that comes with the Apache distribution; here are 1690 the results. 1691 </p><div class="itemizedlist"><p class="title"><b>Overall configuration</b></p><ul type="disc"><li>Machine A: Apache webserver</li><li>Machine B: MySQL server</li><li> 1692 Machines A and B connected with 100Mbps Ethernet 1693 </li><li> 1694 Webserver: Celeron 400, 128MB RAM, IDE storage 1695 </li></ul></div><div class="example"><a id="id2872595"></a><p class="title"><b>Example 2. Apache configuration</b></p><pre class="programlisting">Timeout 300 1696 KeepAlive On 1697 MaxKeepAliveRequests 100 1698 KeepAliveTimeout 15 1699 MinSpareServers 5 1700 StartServers 10 1701 MaxSpareServers 15 1702 MaxClients 256 1703 MaxRequestsPerChild 5000 1704 LogSQLTransferLogFormat AbHhmRSsTUuvc 1705 LogSQLWhichCookie Clicks 1706 CookieTracking on 1707 CookieName Clicks</pre></div><div class="example"><a id="id2872610"></a><p class="title"><b>Example 3. "ab" commandline</b></p><pre class="programlisting">./ab -c 10 -t 20 -v 2 -C Clicks=ab_run http://www.hostname.com/target</pre></div><p> 1708 ( 10 concurrent requests; 20 second test; setting a cookie 1709 "Clicks=ab_run"; target = the mod_log_sql homepage. ) 1710 </p><p> 1711 Ten total ab runs were conducted: five with MySQL logging 1712 enabled, and five with all MySQL directives commented out 1713 of httpd.conf. Then each five were averaged. The results: 1714 </p><div class="itemizedlist"><ul type="disc"><li> 1715 Average of five runs employing MySQL and standard text 1716 logging: 1717 <span class="emphasis"><em> 1718 139.01 requests per second, zero errors. 1719 </em></span> 1720 </li><li> 1721 Average of five runs employing only standard text 1722 logging: 1723 <span class="emphasis"><em> 1724 139.96 requests per second, zero errors. 1725 </em></span> 1726 </li></ul></div><p> 1727 In other words, any rate-limiting effects on this 1728 particular hardware setup are not caused by MySQL. Note 1729 that although this very simple webserver setup is hardly 1730 cutting-edge -- it is, after all, a fairly small machine 1731 -- 139 requests per second equal over twelve million hits 1732 per day. 1733 </p><div class="orderedlist"><p class="title"><b> 1734 If you run this benchmark yourself, take note of three 1735 things: 1736 </b></p><ol type="1"><li> 1737 Use a target URL that is on your own webserver :-). 1738 </li><li> 1739 Wait until all your connections are closed out between 1740 runs; after several thousand requests your TCP/IP 1741 stack will be filled with hundreds of connections in 1742 TIME_WAIT that need to close. Do a "netstat -t|wc -l" 1743 on the webserver to see. If you don't wait, you can 1744 expect to see a lot of messages like "ip_conntrack: 1745 table full, dropping packet" in your logs. (This has 1746 nothing to do with mod_log_sql, this is simply the 1747 nature of the TCP/IP stack in the Linux kernel.) 1748 </li><li><p> 1749 When done with your runs, clean these many thousands 1750 of requests out of your database: 1751 </p><pre class="programlisting">mysql> delete from access_log where agent like 'ApacheBench%'; 1752 mysql> optimize table access_log;</pre></li></ol></div></td></tr><tr class="question"><td align="left" valign="top"><a id="id2872724"></a><a id="id2872726"></a><b>3.2. </b></td><td align="left" valign="top"><p> 1753 Do I need to be worried about all the running MySQL 1754 children? Will holding open n Apache-to-MySQL connections 1755 consume a lot of memory? 1756 </p></td></tr><tr class="answer"><td align="left" valign="top"><b></b></td><td align="left" valign="top"><p>Short answer: you shouldn't be worried.</p></td></tr><tr class="answer"><td align="left" valign="top"><b></b></td><td align="left" valign="top"><p> 1757 Long answer: you might be evaluating at the output of "ps 1758 -aufxw" and becoming alarmed at all the 7MB httpd 1759 processes or 22MB mysqld children that you see. Don't be 1760 alarmed. It's true that mod_log_sql opens and holds open 1761 many MySQL connections: each httpd child maintains one 1762 open database connection (and holds it open for 1763 performance reasons). Four webservers, each running 20 1764 Apache children, will hold open 80 MySQL connections, 1765 which means that your MySQL server needs to handle 80 1766 simultaneous connections. In truth, your MySQL server 1767 needs to handle far more than that if traffic to your 1768 website spikes and the Apache webservers spawn off an 1769 additional 30 children each... 1770 </p><p> 1771 Fortunately the cost reported by 'ps -aufxw' is deceptive. 1772 This is due to an OS memory-management feature called 1773 "copy-on-write." When you have a number of identical child 1774 processes (e.g. Apache, MySQL), it would appear in "ps" as 1775 though each one occupies a great deal of RAM -- as much as 1776 7MB per httpd child! In actuality each additional child 1777 only occupies a small bit of extra memory -- most of the 1778 memory pages are common to each child and therefore shared 1779 in a "read-only" fashion. The OS can get away with this 1780 because the majority of memory pages for one child are 1781 identical across all children. Instead of thinking of each 1782 child as a rubber stamp of the others, think of each child 1783 as a basket of links to a common memory area. 1784 </p><p> 1785 A memory page is only duplicated when it needs to be 1786 written to, hence "copy-on-write." The result is 1787 efficiency and decreased memory consumption. "ps" may 1788 report 7MB per child, but it might really only "cost" 900K 1789 of extra memory to add one more child. It is not correct 1790 to assume that 20 Apache children with a VSZ of 7MB each 1791 equals (2 x 7MB) of memory consumption -- the real answer 1792 is much, much lower. The same "copy-on-write" rules apply 1793 to all your MySQL children: 40 mysqld children @ 22MB each 1794 do not occupy 880MB of RAM. 1795 </p><p> 1796 The bottom line: although there is a cost to spawn extra 1797 httpd or mysqld children, that cost is not as great as 1798 "ps" would lead you to believe. 1799 </p></td></tr><tr class="question"><td align="left" valign="top"><a id="id2872806"></a><a id="id2872808"></a><b>3.3. </b></td><td align="left" valign="top"><p> 1800 My webserver cannot handle all the traffic that my site 1801 receives, is there anything I can do? 1802 </p></td></tr><tr class="answer"><td align="left" valign="top"><b></b></td><td align="left" valign="top"><p> 1803 If you have exhausted all the tuning possibilities on your 1804 existing server, it is probably time you evaluated the 1805 benefits of clustering two or more webservers together in 1806 a load-balanced fashion. In fact, users of such a setup 1807 are mod_log_sql's target audience! 1808 </p></td></tr><tr class="question"><td align="left" valign="top"><a id="FAQ.DelayedInsert"></a><a id="id2872835"></a><b>3.4. </b></td><td align="left" valign="top"><p> 1809 What is the issue with activating delayed inserts? 1810 </p></td></tr><tr class="answer"><td align="left" valign="top"><b></b></td><td align="left" valign="top"><p> 1811 INSERT DELAYED is a specific syntax to MySQL and is not 1812 supported by any other database. Ergo, why is it needed, 1813 and what MySQL deficiency is it working around? INSERT 1814 DELAYED is a kluge. 1815 </p></td></tr><tr class="answer"><td align="left" valign="top"><b></b></td><td align="left" valign="top"><p> 1816 The MySQL documentation is unclear whether INSERT DELAYED 1817 is even necessary for an optimized database. It says, "The 1818 DELAYED option for the INSERT statement is a 1819 MySQL-specific option that is very useful if you have 1820 clients that can't wait for the INSERT to complete." But 1821 then it goes on to say, "Note that as MyISAM tables 1822 supports concurrent SELECT and INSERT, if there is no free 1823 blocks in the middle of the data file, you very seldom 1824 need to use INSERT DELAYED with MyISAM." 1825 </p></td></tr><tr class="answer"><td align="left" valign="top"><b></b></td><td align="left" valign="top"><p> 1826 Because INSERT DELAYED returns without waiting for the 1827 data to be written, a hard kill of your MySQL database at 1828 the right (wrong?) moment could lose those logfile 1829 entries. 1830 </p></td></tr><tr class="answer"><td align="left" valign="top"><b></b></td><td align="left" valign="top"><p> 1831 As of MySQL version 3.23.52, the error return functions 1832 disagree after a failed INSERT DELAYED: mysql_errno() 1833 always returns 0, even if mysql_error() returns a textual 1834 error. I have reported this bug to the MySQL folks. 1835 However, we have no way of knowing what solution they will 1836 adopt to fix this, and with the worst case solution 1837 mod_log_sql would not be able to tell if anything went 1838 wrong with a delayed insert. 1839 </p></td></tr><tr class="answer"><td align="left" valign="top"><b></b></td><td align="left" valign="top"><p> 1840 Instead of delayed inserts, you may wish to utilize InnoDB 1841 tables (instead of the standard MyISAM tables). InnoDB 1842 tables suppot row-level locking and are recommended for 1843 high-volume databases. 1844 </p></td></tr><tr class="answer"><td align="left" valign="top"><b></b></td><td align="left" valign="top"><p> 1845 If after understanding these problems you still wish to 1846 enable delayed inserts, section 1847 <a href="#Sect.DelayedInsert"> 1848 Optimizing for a busy database 1849 </a> 1850 discusses how. 1851 </p></td></tr><tr class="qandadiv"><td align="left" valign="top" colspan="2"><a id="id2872926"></a><h4 class="title"><a id="id2872926"></a>4. "How do I...?" -- accomplishing certain tasks</h4></td></tr><tr class="toc" colspan="2"><td align="left" valign="top" colspan="2"><dl><dt>4.1. <a href="#id2872932"> 1852 How do I extract the data in a format that my analysis 1853 tool can understand? 1854 </a></dt><dt>4.2. <a href="#FAQ.Cookie">How can I log mod_usertrack cookies?</a></dt><dt>4.3. <a href="#id2873187"> 1855 What if I want to log more than one cookie? What is the 1856 difference between LogSQLWhichCookie and 1857 LogSQLWhichCookies? 1858 </a></dt><dt>4.4. <a href="#id2873254"> 1859 What are the SSL logging features, and how do I activate 1860 them? 1861 </a></dt></dl></td></tr><tr class="question"><td align="left" valign="top"><a id="id2872932"></a><a id="id2872934"></a><b>4.1. </b></td><td align="left" valign="top"><p> 1862 How do I extract the data in a format that my analysis 1863 tool can understand? 1864 </p></td></tr><tr class="answer"><td align="left" valign="top"><b></b></td><td align="left" valign="top"><p> 1865 mod_log_sql would be virtually useless if there weren't a 1866 way for you to extract the data from your database in a 1867 somewhat meaningful fashion. To that end there's a Perl 1868 script enclosed with the distribution. That script 1869 (make_combined_log.pl) is designed to extract N-many days 1870 worth of access logs and provide them in a Combined Log 1871 Format output. You can use this very tool right in 1872 /etc/crontab to extract logs on a regular basis so that 1873 your favorite web analysis tool can read them. Or you can 1874 examine the Perl code to construct your own custom tool. 1875 </p><p> 1876 For example, let's say that you want your web statistics 1877 updated once per day in the wee hours of the morning. A 1878 good way to accomplish that could be the following entries 1879 in /etc/crontab: 1880 </p><pre class="programlisting"># Generate the temporary apache logs from the MySQL database (for webalizer) 1881 05 04 * * * root make_combined_log.pl 1 www.grubbybaby.com > /var/log/temp01 1882 # Run webalizer on httpd log 1883 30 04 * * * root webalizer -c /etc/webalizer.conf; rm -f /var/log/temp01</pre><p> 1884 Or if you have a newer system that puts files in 1885 /etc/cron.daily etc., create a file called "webalizer" in 1886 the cron.daily subdirectory. Use the following as the 1887 contents of your file, and make sure to chmod 755 it when 1888 done. 1889 </p><pre class="programlisting">#!/bin/sh 1890 /usr/local/sbin/make_combined_log.pl 1 www.yourdomain.com > /var/log/httpd/templog 1891 /usr/local/bin/webalizer -q -c /etc/webalizer.conf 1892 rm -f /var/log/httpd/templog</pre><p>See? Easy.</p></td></tr><tr class="question"><td align="left" valign="top"><a id="FAQ.Cookie"></a><a id="id2873006"></a><b>4.2. </b></td><td align="left" valign="top"><p>How can I log mod_usertrack cookies?</p></td></tr><tr class="answer"><td align="left" valign="top"><b></b></td><td align="left" valign="top"><p> 1893 A number of people like to log mod_usertrack cookies in 1894 their Apache TransferLog to aid in understanding their 1895 visitors' clickstreams. This is accomplished, for example, 1896 with a statement as follows: 1897 </p><pre class="programlisting">LogFormat "%h %l %u %t \"%r\" %s %b \"%{Referer}i\" \"%{User-Agent}i\"" \"%{cookie}n\""</pre><p> 1898 Naturally it would be nice for mod_log_sql to permit the 1899 admin to log the cookie data as well, so as of version 1900 1.10 you can do this. You need to have already compiled 1901 mod_usertrack into httpd -- it's one of the standard 1902 Apache modules. 1903 </p><p> 1904 First make sure you have a column called "cookie" in the 1905 MySQL database to hold the cookies, which can be done as 1906 follows if you already have a working database: 1907 </p><pre class="programlisting">mysql> alter table acc_log_tbl add column cookie varchar(255);</pre><p> 1908 Next configure your server to set usertracking cookies as 1909 follows, and make sure you include the new 'c' directive 1910 in your LogSQLTransferLogFormat, which activates cookie 1911 logging. Here's an example: 1912 </p><pre class="programlisting"><VirtualHost 1.2.3.4> 1913 CookieTracking on 1914 CookieStyle Cookie 1915 CookieName Foobar 1916 LogSQLTransferLogFormat huSUsbTvRAc 1917 LogSQLWhichCookie Foobar 1918 </VirtualHost></pre><p> 1919 The first three lines configure mod_usertrack to create a 1920 COOKIE (RFC 2109) format cookie called Foobar. The last 1921 two lines tell mod_log_sql to log cookies named Foobar. 1922 You have to choose which cookie to log because more than 1923 one cookie can/will be sent to the server by the client. 1924 </p><p> 1925 Recap: the 'c' character activates cookie logging, and the 1926 LogSQLWhichCookie directive chooses which cookie to log. 1927 </p><p> 1928 FYI, you are advised NOT to use CookieStyle Cookie2 -- it 1929 seems that even newer browsers (IE 5.5, etc.) have trouble 1930 with the new COOKIE2 (RFC 2965) format. Just stick with 1931 the standard COOKIE format and you'll be fine. 1932 </p><p> 1933 Perform some hits on your server and run a select 1934 </p><pre class="programlisting">SELECT request_uri,cookie 1935 FROM access_log 1936 WHERE cookie IS NOT NULL;</pre><div class="table"><a id="id2873098"></a><p class="title"><b>Table 12. </b></p><table summary="" border="1"><colgroup><col /><col /></colgroup><thead><tr><th>request_uri</th><th>cookie</th></tr></thead><tbody><tr><td>/mod_log_sql/</td><td> 1937 ool-18e4.dyn.optonline.net.130051007102700823 1938 </td></tr><tr><td>/mod_log_sql/usa.gif</td><td> 1939 ool-18e4.dyn.optonline.net.130051007102700823 1940 </td></tr><tr><td>/mod_log_sql/style_1.css</td><td> 1941 ool-18e4.dyn.optonline.net.130051007102700823 1942 </td></tr></tbody></table></div></td></tr><tr class="question"><td align="left" valign="top"><a id="id2873187"></a><a id="id2873189"></a><b>4.3. </b></td><td align="left" valign="top"><p> 1943 What if I want to log more than one cookie? What is the 1944 difference between LogSQLWhichCookie and 1945 LogSQLWhichCookies? 1946 </p></td></tr><tr class="answer"><td align="left" valign="top"><b></b></td><td align="left" valign="top"><p> 1947 As of version 1.17, you have a choice in how you want 1948 cookie logging handled. 1949 </p><p> 1950 If you are interested in logging only one cookie per 1951 request, follow the instructions in FAQ entry 1952 <a href="#FAQ.Cookie">Q: 4.2</a> 1953 above. That cookie will be logged to a column in the 1954 regular access_log table, and the actual cookie you want 1955 to log is specified with LogSQLWhichCookie. Don't forget 1956 to specify the 'c' character in LogSQLTransferLogFormat. 1957 </p><p> 1958 If, however, you need to log multiple cookies per request, 1959 you must employ the LogSQLWhichCookies (note the plural) 1960 directive. The cookies you specify will be logged to a 1961 separate table (as discussed in section 1962 <a href="#Sect.MultiTable"> 1963 Logging many-to-one data in separate tables 1964 </a> 1965 ), and entries in that table will be linked to the regular 1966 access_log entries via the unique ID that is supplied by 1967 mod_unique_id. Without mod_unique_id the information will 1968 still be logged but you will be unable to correlate which 1969 cookies go with which access-requests. Furthermore, with 1970 LogSQLWhichCookies, you do not need to include the 'c' 1971 character in LogSQLTransferLogFormat. 1972 </p><p> 1973 LogSQLWhichCookie and LogSQLWhichCookies can coexist 1974 without conflict because they operate on entireley 1975 different tables, but you're better off choosing the one 1976 you need. 1977 </p></td></tr><tr class="question"><td align="left" valign="top"><a id="id2873254"></a><a id="id2873256"></a><b>4.4. </b></td><td align="left" valign="top"><p> 1978 What are the SSL logging features, and how do I activate 1979 them? 1980 </p></td></tr><tr class="answer"><td align="left" valign="top"><b></b></td><td align="left" valign="top"><div class="note" style="margin-left: 0.5in; margin-right: 0.5in;"><h3 class="title">Note</h3><p> 1981 You do not need to compile SSL support into mod_log_sql 1982 in order to simply use it with a secure site. You only 1983 need to compile SSL support into mod_log_sql if you want 1984 to log SSL-specific data such as the cipher type used, 1985 or the keysize that was negotiated. If that information 1986 is unimportant to you, you can ignore this FAQ. 1987 </p></div><p> 1988 By adding certain characters to your 1989 LogSQLTransferLogFormat string you can tell mod_log_sql to 1990 log the SSL cipher, the SSL keysize of the connection, and 1991 the maximum keysize that was available. This would let you 1992 tell, for example, which clients were using only 1993 export-grade security to access your secure software area. 1994 </p><p> 1995 You can compile mod_log_sql with SSL logging support if 1996 you have the right packages installed. If you already have 1997 an SSL-enabled Apache then you by definition have the 1998 correct packages already installed: OpenSSL and mod_ssl. 1999 </p><p> 2000 You need to ensure that your database is set up to log the 2001 SSL data. Issue the following commands to MySQL if your 2002 access table does not already have them: 2003 </p><pre class="programlisting">mysql> alter table access_log add column ssl_cipher varchar(25); 2004 mysql> alter table access_log add column ssl_keysize smallint unsigned; 2005 mysql> alter table access_log add column ssl_maxkeysize smallint unsigned;</pre><p> 2006 Finally configure httpd.conf to activate the SSL fields. 2007 Note that this is only meaningful in a VirtualHost that is 2008 set up for SSL. 2009 </p><pre class="programlisting"><VirtualHost 1.2.3.4:443> 2010 LogSQLTransferLogFormat AbHhmRSsTUuvcQqz 2011 </VirtualHost></pre><p> 2012 You also need to make sure you have the mod_log_sql_ssl 2013 module loaded as well. 2014 </p><p> 2015 The last three characters (Qqz) in the directive are the 2016 SSL ones; see section 2017 <a href="#Conf.LogSQLTransferLogFormat">LogSQLTransferLogFormat</a> 2018 in the directives documentation for details of the 2019 LogSQLTransferLogFormat directive. 2020 </p><p> 2021 Restart Apache, then perform some hits on your server. 2022 Then run the following select statement: 2023 </p><pre class="programlisting">SELECT remote_host,request_uri,ssl_cipher,ssl_keysize,ssl_maxkeysize 2024 FROM access_log 2025 WHERE ssl_cipher IS NOT NULL;</pre><div class="table"><a id="id2873358"></a><p class="title"><b>Table 13. </b></p><table summary="" border="1"><colgroup><col /><col /><col /><col /><col /></colgroup><thead><tr><th>remote_host</th><th>request_uri</th><th>ssl_cipher</th><th>ssl_keysize</th><th>ssl_maxkeysize</th></tr></thead><tbody><tr><td>216.192.52.4</td><td>/dir/somefile.html</td><td>RC4-MD5</td><td>128</td><td>128</td></tr><tr><td>216.192.52.4</td><td>/dir/somefile.gif</td><td>RC4-MD5</td><td>128</td><td>128</td></tr><tr><td>216.192.52.4</td><td>/dir/somefile.jpg</td><td>RC4-MD5</td><td>128</td><td>128</td></tr></tbody></table></div></td></tr></tbody></table></div></div></div></body></html>