From 478975f1c8767c7ae2abb284ecda41185df32f11 Mon Sep 17 00:00:00 2001 From: Christopher Powell Date: Tue, 10 Dec 2002 19:43:21 +0000 Subject: Changes on route to 1.18 release (better table creation logic and workarounds for delayed insert kluge). --- CHANGELOG | 23 +- Documentation/documentation.lyx | 2069 +++++++++++++++++++++++---------------- Makefile | 4 +- create_tables.sql | 6 +- mod_log_sql.c | 324 +++--- 5 files changed, 1456 insertions(+), 970 deletions(-) diff --git a/CHANGELOG b/CHANGELOG index f18509c..66151e7 100644 --- a/CHANGELOG +++ b/CHANGELOG @@ -1,4 +1,4 @@ -$Id: CHANGELOG,v 1.17 2002/11/14 22:52:53 helios Exp $ +$Id: CHANGELOG,v 1.18 2002/12/10 19:43:21 helios Exp $ TODO: * Rethink documentation flow and rewrite? @@ -15,11 +15,28 @@ TODO: * socket-based middleman daemon with configurable conns? * ignore by cookie - CHANGES: -1.17: +1.18: +* Delayed inserts (a MySQL extension) are now available at compile-time. + THIS IS UGLY because there seems to be a bug in the way MySQL returns + errors on failed INSERT DELAYED queries. See the FAQ. +* Reworked the Makefile a tad. +* Added stdlib.h to the includes, some systems need it for atoi(). Added + string.h to the includes just to be safe. +* Reworked table creation (again). Inserts are attempted and tables + are made only on ER_NO_SUCH_TABLE. This should solve all the multiple- + creation-attempt issues as well as reduce overhead. +* safe_mysql_query reworked; it now can be counted on to preserve_entry() + if the query doesn't succeed. (Before, preserve_entry() was called + separately.) +* Always return SIGPIPE to handler before leaving safe_mysql_query(). +* New directive LogSQLRequestAccept, another filter for excluding things + from being logged. +* Alphabetized the directives in the reference section of the docs. +* Reorganized the FAQ by section. +1.17: * Renamed the module mod_log_sql to conform to the project goal of database-inspecificity. * Added capability of logging Notes field. This is useful for folks using diff --git a/Documentation/documentation.lyx b/Documentation/documentation.lyx index 50fceba..69f09ae 100644 --- a/Documentation/documentation.lyx +++ b/Documentation/documentation.lyx @@ -1254,8 +1254,7 @@ which logs all the information required to be compatible with the Combined \layout Standard If you don't choose to log everything that is available, that's fine. - The unused columns in your table will have simply contain NULL and do not - cause issues. + Fields in the unused columns in your table will simply contain NULL. \layout Standard Some of the @@ -1297,10 +1296,18 @@ LogSQLMachineID \end_inset -Instructing the module what NOT to log +Instructing the module what NOT to log using filtering directives \layout Standard -Two +One +\begin_inset Quotes eld +\end_inset + +accept +\begin_inset Quotes erd +\end_inset + + and two \begin_inset Quotes eld \end_inset @@ -1308,12 +1315,59 @@ ignore \begin_inset Quotes srd \end_inset - directives allow you to specify things that the module should not log. + directives allow you to fine-tune what the module should not log. These are very handy for keeping your database as uncluttered as possible and keeping your statistics free of unneeded numbers. + Think of each one as a gatekeeper. +\layout Standard + + +\emph on +It is important to remember that each of these three directives is purely + optional. + mod_log_sql's default is to log everything. + +\layout Standard + +When a request comes in, the contents of +\noun on +LogSQLRequestAccept +\noun default +are evaluated first. + This optional, +\begin_inset Quotes eld +\end_inset + +blanket +\begin_inset Quotes erd +\end_inset + + directive lets you specify that only certain things are to be accepted + for logging, and everything else discarded. + Because it is evaluated before +\noun on +LogSQLRequestIgnore +\noun default +and +\noun on +LogSQLRemhostIgnore +\noun default +it can halt logging before those two filtering directives +\begin_inset Quotes eld +\end_inset + +get their chance. +\begin_inset Quotes erd +\end_inset + + \layout Standard -You are able to tell the module what to exclude based on +Once a request makes it past +\noun on +LogSQLRequestAccept +\noun default +, it still can be excluded based on \noun on LogSQLRemhostIgnore \noun default @@ -1348,7 +1402,7 @@ You can specify a series of strings after each directive. Do not use any type of globbing or regular-expression syntax -- each string is considered a match \emph on - if it is a substring of the larger request or remote-host, and the comarison + if it is a substring of the larger request or remote-host; the comarison is case-sensitive. \emph default @@ -1441,7 +1495,81 @@ Tree.Gif \begin_inset Quotes erd \end_inset - would still get logged. + would still get logged because of case sensitivity. +\layout Standard + +A summary of the decision flow: +\layout Enumerate + +If +\noun on +LogSQLRequestAccept +\noun default + exists and a request does not match anything in that list, it is discarded. +\layout Enumerate + +If a request matches anything in the +\noun on +LogSQLRequestIgnore +\noun default +list, it is discarded. +\layout Enumerate + +If a reqiest matches anything in the +\noun on +LogSQLRemhostIgnore +\noun default + list, it is discarded. +\layout Enumerate + +Otherwise the request is logged. +\layout Standard + +This means that you can have a series of directives similar to the following: +\layout LyX-Code + +LogSQLRequestAccept *.html *.gif *.jpg +\layout LyX-Code + +LogSQLRequestIgnore statistics.html bluedot.jpg +\layout Standard + +So the first line instructs the module to +\series bold +only +\series default + log files with html, gif and jpg suffixes; requests for +\begin_inset Quotes eld +\end_inset + +formail.cgi +\begin_inset Quotes erd +\end_inset + + and +\begin_inset Quotes eld +\end_inset + +shopping-cart.pl +\begin_inset Quotes erd +\end_inset + + will never be considered for logging. + ( +\begin_inset Quotes eld +\end_inset + +LeftArrow.JPG +\begin_inset Quotes erd +\end_inset + + will also never be considered for logging -- remember, the comparison is + +\series bold +case sensitive +\series default +.) The second line prunes the list further -- you never want to log requests + for those two objects. \layout Standard Tip: if you want to match all the hosts in your domain such as @@ -1620,83 +1748,6 @@ LogSQLTransferLogFormat machine and thereby analyze the front-end loadbalancing algorithm. \layout Subsubsection -Using the same database for production and test -\layout Standard - -Although suboptimal, it is not uncommon to use the same backend database - for the -\begin_inset Quotes eld -\end_inset - -production -\begin_inset Quotes erd -\end_inset - - webservers as well as the -\begin_inset Quotes eld -\end_inset - -test -\begin_inset Quotes erd -\end_inset - - webservers (budgetary constraints, rackspace limits, etc.). - Furthermore, an administrator in this situation may be unable to use -\noun on -LogSQLRemhostIgnore -\noun default -to exclude requests from the test servers -- perhaps the generated entries - are genuinely useful for analytical or QA purposes, but their value after - analysis is minimal. -\layout Standard - -It is wasteful and potentially confusing to permit this internal test data - to clutter the database, and a solution to the problem is the proper use - of the -\noun on -LogSQLMachineID -\noun default -directive. - Assume a scenario where the production webservers have IDs like -\begin_inset Quotes eld -\end_inset - -web01, -\begin_inset Quotes erd -\end_inset - - -\begin_inset Quotes eld -\end_inset - -web02, -\begin_inset Quotes erd -\end_inset - - and so on -- and the test webservers have IDs like -\begin_inset Quotes eld -\end_inset - -test01, -\begin_inset Quotes erd -\end_inset - - -\begin_inset Quotes eld -\end_inset - -test02, -\begin_inset Quotes erd -\end_inset - - etc. - Because entries in the log database are distinguished by their source machine, - an administrator may purge unneeded test data from the access log as follows: -\layout LyX-Code - -delete from access_log where machine_id like 'test%'; -\layout Subsubsection - \begin_inset LatexCommand \label{secMulTable} @@ -2399,13 +2450,95 @@ LogSQLTransferLogTable.) \layout Subsubsection +Using the same database for production and test +\layout Standard + +Although suboptimal, it is not uncommon to use the same backend database + for the +\begin_inset Quotes eld +\end_inset + +production +\begin_inset Quotes erd +\end_inset + + webservers as well as the +\begin_inset Quotes eld +\end_inset + +test +\begin_inset Quotes erd +\end_inset + + webservers (budgetary constraints, rackspace limits, etc.). + Furthermore, an administrator in this situation may be unable to use +\noun on +LogSQLRemhostIgnore +\noun default +to exclude requests from the test servers -- perhaps the generated entries + are genuinely useful for analytical or QA purposes, but their value after + analysis is minimal. +\layout Standard + +It is wasteful and potentially confusing to permit this internal test data + to clutter the database, and a solution to the problem is the proper use + of the +\noun on +LogSQLMachineID +\noun default +directive. + Assume a scenario where the production webservers have IDs like +\begin_inset Quotes eld +\end_inset + +web01, +\begin_inset Quotes erd +\end_inset + + +\begin_inset Quotes eld +\end_inset + +web02, +\begin_inset Quotes erd +\end_inset + + and so on -- and the test webservers have IDs like +\begin_inset Quotes eld +\end_inset + +test01, +\begin_inset Quotes erd +\end_inset + + +\begin_inset Quotes eld +\end_inset + +test02, +\begin_inset Quotes erd +\end_inset + + etc. + Because entries in the log database are distinguished by their source machine, + an administrator may purge unneeded test data from the access log as follows: +\layout LyX-Code + +delete from access_log where machine_id like 'test%'; +\layout Subsubsection + + +\begin_inset LatexCommand \label{sub:DelayedIns} + +\end_inset + Optimizing for a busy database \layout Standard A busy MySQL database will have SELECT statements running concurrently with INSERT and UPDATE statements. A long-running SELECT can block INSERTs, therefore will block mod_log_sql. - This can be solved by compiling mod_log_sql for + This may be solved by compiling mod_log_sql for \begin_inset Quotes eld \end_inset @@ -2454,6 +2587,8 @@ There is additional overhead for the server to handle a separate thread for each table on which you use INSERT DELAYED. \layout Standard + +\series bold The MySQL documentation concludes, \begin_inset Quotes eld \end_inset @@ -2463,7 +2598,15 @@ This means that you should only use INSERT DELAYED when you are really sure \begin_inset Quotes erd \end_inset + Furthermore, the current state of error return from a failed INSERT DELAYED + seems to be in flux, and may behave in unpredictable ways between different + MySQL versions. + See section +\begin_inset LatexCommand \ref{sub:DelayedInsFAQ} + +\end_inset + in the FAQ -- you have been warned. \layout Standard If you are experiencing issues which could be solved by delayed inserts, @@ -2499,246 +2642,297 @@ context listed with each entry informs you of this. \layout Subsubsection -LogSQLDatabase +LogSQLCookieLogTable \layout LyX-Code +Syntax: LogSQLCookieLogTable table-name +\layout LyX-Code -\series bold -MANDATORY +Example: LogSQLCookieLogTable cookie_log \layout LyX-Code -Syntax: LogSQLDatabase database +Default: cookies \layout LyX-Code -Example: LogSQLDatabase loggingdb -\layout LyX-Code +Context: virtual host +\layout Standard -Context: main server config -\layout Quote +Defines which table is used for logging of cookies. + Working in conjunction with +\noun on +LogSQLWhichCookies +\noun default +, you can log many of each request's associated cookies to a separate table. + For meaningful data retrieval the cookie table is keyed to the access table + by the unique request ID supplied by the standard Apache module mod_unique_id. +\layout Standard -Defines the database that is used for logging. - +Note that you must create the table (see create-tables.sql, included in the + package), or +\noun on +LogSQLCreateTables +\noun default + must be set to \begin_inset Quotes eld \end_inset -database +on \begin_inset Quotes erd \end_inset - must be a valid db on the MySQL host defined in +. +\layout Subsubsection + +LogSQLCreateTables +\layout LyX-Code + +Syntax: LogSQLCreateTables flag +\layout LyX-Code + +Example: LogSQLCreateTables On +\layout LyX-Code + +Default: Off +\layout LyX-Code + +Context: main server config +\layout Standard + +mod_log_sql has the ability to create its tables on-the-fly. + The advantage to this is convenience: you don't have to execute any SQL + by hand to prepare the table. + This is especially helpful for people with lots of virtual hosts (who should + also see the +\noun on +LogSQLMassVirtualHosting +\noun default + directive). +\layout Standard + +There is a slight disadvantage: if you wish to activate this feature, then + the userid specified in \noun on LogSQLLoginInfo +\noun default + must have CREATE privileges on the database. + In an absolutely paranoid, locked-down situation you may only want to grant + your mod_log_sql user INSERT privileges on the database; in that situation + you are unable to take advantage of +\noun on +LogSQLCreateTables \noun default . - -\layout Quote + But most people -- even the very security-conscious -- will find that granting + CREATE on the logging database is reasonable. +\layout Standard This is defined only once in the httpd.conf file. \layout Subsubsection -LogSQLLoginInfo +LogSQLDatabase \layout LyX-Code \series bold MANDATORY -\series default - \layout LyX-Code -Syntax: LogSQLLoginInfo host user password +Syntax: LogSQLDatabase database \layout LyX-Code -Example: LogSQLLoginInfo foobar.baz.com logwriter passw0rd +Example: LogSQLDatabase loggingdb \layout LyX-Code Context: main server config -\layout Quote - -Defines the general parameters of the MySQL host to which you will be logging. - -\begin_inset Quotes eld -\end_inset - -host -\begin_inset Quotes erd -\end_inset +\layout Standard - is the hostname or IP address of the MySQL machine. +Defines the database that is used for logging. \begin_inset Quotes eld \end_inset -user +database \begin_inset Quotes erd \end_inset - is the MySQL userid (not a Unix userid!) with INSERT privileges on the - table defined in + must be a valid db on the MySQL host defined in \noun on -LogSQLTransferLogTable +LogSQLLoginInfo \noun default . -\begin_inset Quotes eld -\end_inset - -password -\begin_inset Quotes erd -\end_inset - - is that user's password. - -\layout Quote +\layout Standard This is defined only once in the httpd.conf file. \layout Subsubsection -LogSQLTCPPort +LogSQLForcePreserve \layout LyX-Code -Syntax: LogSQLTCPPort portnumber +Syntax: LogSQLForcePreserve Flag \layout LyX-Code -Example: LogSQLTCPPort 3309 +Example: LogSQLPreserveFile on \layout LyX-Code -Default: 3306 +Default: off \layout LyX-Code Context: main server config -\layout Quote +\layout Standard -Your database may listen on a different port than the default. - If so, use this directive to instruct the module which port to use. - This directive only applies if the database is on a different machine connected - via TCP/IP. -\layout Quote +You may need to perform debugging on your database and specifically want + mod_log_sql to make no attempts to log to it. + This directive instructs the module to send all its log entries directly + to the preserve file and to make no database INSERT attempts. +\layout Standard + +This is presumably a directive for temporary use only; it could be dangerous + if you set it and forget it, as all your entries will simply pile up in + the preserve file. +\layout Standard This is defined only once in the httpd.conf file. \layout Subsubsection -LogSQLSocketFile +LogSQLHeadersInLogTable \layout LyX-Code -Syntax: LogSQLSocketFile filename +Syntax: LogSQLHeadersInLogTable table-name \layout LyX-Code -Example: LogSQLSocketFile /tmp/mysql.sock +Example: LogSQLHeadersInLogTable headers \layout LyX-Code -Default: /var/lib/mysql/mysql.sock +Default: headers_in \layout LyX-Code -Context: main server config -\layout Quote +Context: virtual host +\layout Standard -At Apache runtime you can specify the MySQL socket file to use. - Set this once in your main server config to override the default value. - This value is irrelevant if your database resides on a separate machine. -\layout Quote +Defines which table is used for logging of inbound headers. + Working in conjunction with +\noun on +LogSQLWhichHeadersIn +\noun default +, you can log many of each request's associated headers to a separate table. + For meaningful data retrieval the headers table is keyed to the access + table by the unique request ID supplied by the standard Apache module mod_uniqu +e_id. +\layout Standard -mod_log_sql will automatically employ the socket for db communications if - the database resides on the local host. - If the db resides on a separate host the module will automatically use - TCP/IP. - This is a function of the MySQL API and is not user-configurable. -\layout Quote +Note that you must create the table (see create-tables.sql, included in the + package), or +\noun on +LogSQLCreateTables +\noun default + must be set to +\begin_inset Quotes eld +\end_inset -This is defined only once in the httpd.conf file. +on +\begin_inset Quotes erd +\end_inset + +. \layout Subsubsection -LogSQLPreserveFile +LogSQLHeadersOutLogTable \layout LyX-Code -Syntax: LogSQLPreserveFile filename +Syntax: LogSQLHeadersOutLogTable table-name \layout LyX-Code -Example: LogSQLPreserveFile offline-preserve +Example: LogSQLHeadersOutLogTable headers \layout LyX-Code -Default: /tmp/sql-preserve +Default: headers_out \layout LyX-Code Context: virtual host -\layout Quote - -mod_log_sql writes queries to this local preserve file in the event that - it cannot reach the database, and thus ensures that your high-availability - web frontend does not lose logs during a temporary database outage. - This could happen for a number of reasons: the database goes offline, the - network breaks, etc. - You will not lose entries since the module has this backup. - The file consists of a series of SQL statements that can be imported into - your database at your convenience; furthermore, because the SQL queries - contain the access timestamps you do not need to worry about out-of-order - data after the import, which is done in a simple manner: -\layout LyX-Code - -# mysql -uadminuser -p mydbname < /tmp/sql-preserve -\layout Quote +\layout Standard -If you do not define -\noun on -LogSQLPreserveFile -\noun default - then all virtual servers will log to the same default preserve file (/tmp/sql-p -reserve). - You can redefine this on a virtual-host basis in order to segregate your - preserve files if you desire. - Note that segregation is not usually necessary, as the SQL statements that - are written to the preserve file already distinguish between different - virtual hosts if you include the 'v' character in your +Defines which table is used for logging of outbound headers. + Working in conjunction with \noun on -LogSQLTransferLogFormat +LogSQLWhichHeadersOut \noun default - directive. - It is only necessary to segregate preserve-files by virualhost if you also - segregate access logs by virtualhost. -\layout Quote +, you can log many of each request's associated headers to a separate table. + For meaningful data retrieval the headers table is keyed to the access + table by the unique request ID supplied by the standard Apache module mod_uniqu +e_id. +\layout Standard -The module will log to Apache's +Note that you must create the table (see create-tables.sql, included in the + package), or \noun on -ErrorLog +LogSQLCreateTables \noun default - when it notices a database outage, and upon database return. - You will therefore know when the preserve file is being used, although - it is your responsibility to import the file. -\layout Quote + must be set to +\begin_inset Quotes eld +\end_inset -The file does not need to be created in advance. - It is safe to remove or rename the file without interrupting Apache, as - the module closes the filehandle immediately after completing the write. - The file is created with the user & group ID of the running Apache process - (e.g. - 'nobody' on many Linux distributions). +on +\begin_inset Quotes erd +\end_inset + +. \layout Subsubsection -LogSQLForcePreserve +LogSQLLoginInfo \layout LyX-Code -Syntax: LogSQLForcePreserve Flag + +\series bold +MANDATORY +\series default + \layout LyX-Code -Example: LogSQLPreserveFile on +Syntax: LogSQLLoginInfo host user password \layout LyX-Code -Default: off +Example: LogSQLLoginInfo foobar.baz.com logwriter passw0rd \layout LyX-Code Context: main server config -\layout Quote +\layout Standard -You may need to perform debugging on your database and specifically want - mod_log_sql to make no attempts to log to it. - This directive instructs the module to send all its log entries directly - to the preserve file and to make no database INSERT attempts. -\layout Quote +Defines the general parameters of the MySQL host to which you will be logging. + +\begin_inset Quotes eld +\end_inset -This is presumably a directive for temporary use only; it could be dangerous - if you set it and forget it, as all your entries will simply pile up in - the preserve file. -\layout Quote +host +\begin_inset Quotes erd +\end_inset + + is the hostname or IP address of the MySQL machine. + +\begin_inset Quotes eld +\end_inset + +user +\begin_inset Quotes erd +\end_inset + + is the MySQL userid (not a Unix userid!) with INSERT privileges on the + table defined in +\noun on +LogSQLTransferLogTable +\noun default +. + +\begin_inset Quotes eld +\end_inset + +password +\begin_inset Quotes erd +\end_inset + + is that user's password. + +\layout Standard This is defined only once in the httpd.conf file. \layout Subsubsection @@ -2753,7 +2947,7 @@ Example: LogSQLMachineID web01 \layout LyX-Code Context: main server config -\layout Quote +\layout Standard If you have a farm of webservers then you may wish to know which particular machine made each entry; this is useful for analyzing your loadbalancing @@ -2791,54 +2985,8 @@ LogSQLMachineID \begin_inset Quotes erd \end_inset - etc. -\layout Quote - -This is defined only once in the httpd.conf file. -\layout Subsubsection - -LogSQLCreateTables -\layout LyX-Code - -Syntax: LogSQLCreateTables flag -\layout LyX-Code - -Example: LogSQLCreateTables On -\layout LyX-Code - -Default: Off -\layout LyX-Code - -Context: main server config -\layout Quote - -mod_log_sql has the ability to create its tables on-the-fly. - The advantage to this is convenience: you don't have to execute any SQL - by hand to prepare the table. - This is especially helpful for people with lots of virtual hosts (who should - also see the -\noun on -LogSQLMassVirtualHosting -\noun default - directive). -\layout Quote - -There is a slight disadvantage: if you wish to activate this feature, then - the userid specified in -\noun on -LogSQLLoginInfo -\noun default - must have CREATE privileges on the database. - In an absolutely paranoid, locked-down situation you may only want to grant - your mod_log_sql user INSERT privileges on the database; in that situation - you are unable to take advantage of -\noun on -LogSQLCreateTables -\noun default -. - But most people -- even the very security-conscious -- will find that granting - CREATE on the logging database is reasonable. -\layout Quote + etc. +\layout Standard This is defined only once in the httpd.conf file. \layout Subsubsection @@ -2856,7 +3004,7 @@ Default: Off \layout LyX-Code Context: main server config -\layout Quote +\layout Standard If you administer a site hosting many, many virtual hosts then this option will appeal to you. @@ -2865,7 +3013,6 @@ If you administer a site hosting many, many virtual hosts then this option LogSQLMassVirtualHosting \noun default then several things happen: -\begin_deeper \layout Itemize the on-the-fly table creation feature is activated automatically @@ -2881,8 +3028,7 @@ which, in turn, means that each virtual host logs to its own segregated Because there is no data shared between virtual servers you can grant your users access to the tables they need; they will be unable to view others' data. -\end_deeper -\layout Quote +\layout Standard This is a huge boost in convenience for sites with many virtual servers. Activating @@ -2891,64 +3037,239 @@ LogSQLMassVirtualHosting \noun default obviates the need to create every virtual server's table and provides more granular security possibilities. -\layout Quote +\layout Standard + +You are advised to investigate the use of Apache's +\noun on +UseCanonicalName On +\noun default +directive with this directive in order to ensure that each virtual host + maps to one table namespace. +\layout Standard This is defined only once in the httpd.conf file. + \layout Subsubsection -LogSQLTransferLogTable +LogSQLNotesLogTable \layout LyX-Code +Syntax: LogSQLNotesLogTable table-name +\layout LyX-Code -\series bold -MANDATORY (unless +Example: LogSQLNotesLogTable notes_log +\layout LyX-Code + +Default: notes +\layout LyX-Code + +Context: virtual host +\layout Standard + +Defines which table is used for logging of notes. + Working in conjunction with \noun on -LogSQLMassVirtualHosting +LogSQLWhichNotes \noun default - is -\begin_inset Quotes eld -\end_inset +, you can log many of each request's associated notes to a separate table. + For meaningful data retrieval the notes table is keyed to the access table + by the unique request ID supplied by the standard Apache module mod_unique_id. +\layout Standard -on -\begin_inset Quotes erd -\end_inset +Note that you must create the table (see create-tables.sql, included in the + package), or +\noun on +LogSQLCreateTables +\noun default + must be set to ``on''. + +\layout Subsubsection -) +LogSQLPreserveFile \layout LyX-Code -Syntax: LogSQLTransferLogTable table-name +Syntax: LogSQLPreserveFile filename \layout LyX-Code -Example: LogSQLTransferLogTable access_log_table +Example: LogSQLPreserveFile offline-preserve +\layout LyX-Code + +Default: /tmp/sql-preserve \layout LyX-Code Context: virtual host -\layout Quote +\layout Standard -Defines which table is used for logging of Apache's transfers; this is analogous - to Apache's TransferLog directive. - table-name must be a valid table within the database defined in +mod_log_sql writes queries to this local preserve file in the event that + it cannot reach the database, and thus ensures that your high-availability + web frontend does not lose logs during a temporary database outage. + This could happen for a number of reasons: the database goes offline, the + network breaks, etc. + You will not lose entries since the module has this backup. + The file consists of a series of SQL statements that can be imported into + your database at your convenience; furthermore, because the SQL queries + contain the access timestamps you do not need to worry about out-of-order + data after the import, which is done in a simple manner: +\layout LyX-Code + +# mysql -uadminuser -p mydbname < /tmp/sql-preserve +\layout Standard + +If you do not define \noun on -LogSQLDatabase +LogSQLPreserveFile \noun default -. -\layout Quote + then all virtual servers will log to the same default preserve file (/tmp/sql-p +reserve). + You can redefine this on a virtual-host basis in order to segregate your + preserve files if you desire. + Note that segregation is not usually necessary, as the SQL statements that + are written to the preserve file already distinguish between different + virtual hosts if you include the 'v' character in your +\noun on +LogSQLTransferLogFormat +\noun default + directive. + It is only necessary to segregate preserve-files by virualhost if you also + segregate access logs by virtualhost. +\layout Standard -This directive is not necessary if you declare +The module will log to Apache's \noun on -LogSQLMassVirtualHosting On +ErrorLog \noun default -, since that directive activates dynamically-named tables. - If you attempt to use + when it notices a database outage, and upon database return. + You will therefore know when the preserve file is being used, although + it is your responsibility to import the file. +\layout Standard + +The file does not need to be created in advance. + It is safe to remove or rename the file without interrupting Apache, as + the module closes the filehandle immediately after completing the write. + The file is created with the user & group ID of the running Apache process + (e.g. + 'nobody' on many Linux distributions). +\layout Subsubsection + +LogSQLRemhostIgnore +\layout LyX-Code + +Syntax: LogSQLRemhostIgnore host1 host2 host3 ... + hostN +\layout LyX-Code + +Example: LogSQLRemhostIgnore localnet.com +\layout LyX-Code + +Context: virtual host +\layout Standard + +Lists a series of strings that, if present in the REMOTE_HOST, will cause + that request to +\series bold +not +\series default + be logged. + This directive is useful for cutting down on log clutter when you are certain + that you want to ignore requests from certain hosts, such as your own internal + network machines. + See section +\begin_inset LatexCommand \ref{sub:Ignore} + +\end_inset + + for some tips for using this directive. +\layout Standard + +Each string is separated by a space, and no regular expressions or globbing + are allowed. + Each string is evaluated as a substring of the REMOTE_HOST using strstr(). + The comparison is case sensitive. +\layout Subsubsection + +LogSQLRequestAccept +\layout LyX-Code + +Syntax: LogSQLRequestAccept req1 req2 req3 ... + reqN +\layout LyX-Code + +Example: LogSQLRequestAccept .html .php .jpg +\layout LyX-Code + +Default: if not specified, all requests are +\begin_inset Quotes eld +\end_inset + +accepted +\begin_inset Quotes erd +\end_inset + + +\layout LyX-Code + +Context: virtual host +\layout Standard + +Lists a series of strings that, if present in the URI, will permit that + request to be +\series bold + +\series default +considered for logging (depending on additional filtering by the +\begin_inset Quotes eld +\end_inset + +ignore +\begin_inset Quotes erd +\end_inset + + directives). + Any request that fails to match one of the \noun on -LogSQLTransferLogTable +LogSQLRequestAccept \noun default - at the same time a warning will be logged and it will be ignored, since +entries will be discarded. +\layout Standard + +This directive is useful for cutting down on log clutter when you are certain + that you only want to log certain kinds of requests, and just blanket-ignore + everything else. + See section +\begin_inset LatexCommand \ref{sub:Ignore} + +\end_inset + + for some tips for using this directive. +\layout Standard + +Each string is separated by a space, and no regular expressions or globbing + are allowed. + Each string is evaluated as a substring of the URI using strstr(). + The comparison is case sensitive. +\layout Standard + +This directive is completely optional. + It is more general than +\noun on +LogSQLRequestIgnore +\noun default +and +\noun on +\noun default +is evaluated before \noun on -LogSQLMassVirtualHosting + LogSQLRequestIgnore \noun default - takes priority. +. + If this directive is not used, +\series bold +all +\series default + requests are accepted and passed on to the other filtering directives. + Therefore, only use this directive if you have a specific reason to do + so. \layout Subsubsection LogSQLRequestIgnore @@ -2962,7 +3283,7 @@ Example: LogSQLRequestIgnore root.exe cmd.exe default.ida favicon.ico \layout LyX-Code Context: virtual host -\layout Quote +\layout Standard Lists a series of strings that, if present in the URI, will cause that request to @@ -2982,7 +3303,7 @@ logged. \end_inset for some tips for using this directive. -\layout Quote +\layout Standard Each string is separated by a space, and no regular expressions or globbing are allowed. @@ -2990,40 +3311,58 @@ Each string is separated by a space, and no regular expressions or globbing The comparison is case sensitive. \layout Subsubsection -LogSQLRemhostIgnore +LogSQLSocketFile \layout LyX-Code -Syntax: LogSQLRemhostIgnore host1 host2 host3 ... - hostN +Syntax: LogSQLSocketFile filename \layout LyX-Code -Example: LogSQLRemhostIgnore localnet.com +Example: LogSQLSocketFile /tmp/mysql.sock \layout LyX-Code -Context: virtual host -\layout Quote +Default: /var/lib/mysql/mysql.sock +\layout LyX-Code -Lists a series of strings that, if present in the REMOTE_HOST, will cause - that request to -\series bold -NOT -\series default - be logged. - This directive is useful for cutting down on log clutter when you are certain - that you want to ignore requests from certain hosts, such as your own internal - network machines. - See section -\begin_inset LatexCommand \ref{sub:Ignore} +Context: main server config +\layout Standard + +At Apache runtime you can specify the MySQL socket file to use. + Set this once in your main server config to override the default value. + This value is irrelevant if your database resides on a separate machine. +\layout Standard + +mod_log_sql will automatically employ the socket for db communications if + the database resides on the local host. + If the db resides on a separate host the module will automatically use + TCP/IP. + This is a function of the MySQL API and is not user-configurable. +\layout Standard + +This is defined only once in the httpd.conf file. +\layout Subsubsection + +LogSQLTCPPort +\layout LyX-Code -\end_inset +Syntax: LogSQLTCPPort portnumber +\layout LyX-Code - for some tips for using this directive. -\layout Quote +Example: LogSQLTCPPort 3309 +\layout LyX-Code -Each string is separated by a space, and no regular expressions or globbing - are allowed. - Each string is evaluated as a substring of the REMOTE_HOST using strstr(). - The comparison is case sensitive. +Default: 3306 +\layout LyX-Code + +Context: main server config +\layout Standard + +Your database may listen on a different port than the default. + If so, use this directive to instruct the module which port to use. + This directive only applies if the database is on a different machine connected + via TCP/IP. +\layout Standard + +This is defined only once in the httpd.conf file. \layout Subsubsection @@ -3044,7 +3383,7 @@ Default: AbHhmRSsTUuv \layout LyX-Code Context: virtual host -\layout Quote +\layout Standard Each character in the format-string defines an attribute of the request that you wish to log. @@ -4193,7 +4532,7 @@ for this to take effect. LogSQLMachineID \noun default for this to take effect. -\layout Quote +\layout Standard If you have compiled mod_log_sql with SSL logging capability, you also can use these: @@ -4390,84 +4729,16 @@ smallint unsigned \layout Subsubsection -LogSQLWhichCookie -\layout LyX-Code - -Syntax: LogSQLWhichCookie cookiename -\layout LyX-Code - -Example: LogSQLWhichCookie Clicks -\layout LyX-Code - -Default: None -\layout LyX-Code - -Context: virtual host -\layout Quote - -In HTTP, cookies have names to distinguish them from each other. - Using mod_usertrack, for example, you can give your user-tracking cookies - a name with the CookieName directive. -\layout Quote - -You must include a 'c' character in -\noun on -LogSQLTransferLogFormat -\noun default - for this directive to take effect; once you specify 'c', -\noun on -LogSQLWhichCookie -\noun default - tells mod_log_sql which cookie to log. - This is necessary because you will usually be setting and receiving more - than one cookie from a client; this cookie designates which one to log. -\layout Quote - -Note: although this was intended for people who are using mod_usertrack - to set user-tracking cookies, you aren't restricted in any way. - You can choose which cookie you wish to log to the database --any cookie - at all -- and it doesn't necessarily have to have anything to do with mod_usert -rack. -\layout Subsubsection - -LogSQLWhichCookies -\layout LyX-Code - -Syntax: LogSQLWhichCookies cookie1 cookie2 ... - cookieN -\layout LyX-Code - -Example: LogSQLWhichCookies userlogin foobar foobaz -\layout LyX-Code - -Default: None +LogSQLTransferLogTable \layout LyX-Code -Context: virtual host -\layout Quote - -Defines the list of cookies you would like logged. - This works in conjunction with -\noun on -LogSQLCookieLogTable -\noun default -. - This directive does not require any additional characters to be added to - the -\noun on -LogSQLTransferLogFormat -\noun default - string. - The feature is activated simply by including this directive, upon which - you will begin populating the separate cookie table with data. -\layout Quote -Note that you must have already created the table (see create-tables.sql, - included in the package), or +\series bold +MANDATORY (unless \noun on -LogSQLCreateTables +LogSQLMassVirtualHosting \noun default - must be set to + is \begin_inset Quotes eld \end_inset @@ -4475,180 +4746,118 @@ on \begin_inset Quotes erd \end_inset -. -\layout Subsubsection - -LogSQLWhichHeadersIn -\layout LyX-Code - -Syntax: LogSQLWhichHeadersIn item1 item2 ... - itemN +) \layout LyX-Code -Example: LogSQLWhichHeadersIn UserAgent Accept-Encoding Host +Syntax: LogSQLTransferLogTable table-name \layout LyX-Code -Default: None +Example: LogSQLTransferLogTable access_log_table \layout LyX-Code Context: virtual host -\layout Quote - -Defines the list of inbound headers you would like logged. - This works in conjunction with -\noun on - LogSQLHeadersInLogTable -\noun default -. - This directive does not require any additional characters to be added to - the -\noun on -LogSQLTransferLogFormat -\noun default - string. - The feature is activated simply by including this directive, upon which - you will begin populating the separate inbound-headers table with data. -\layout Quote +\layout Standard -Note that you must have already created the table (see create-tables.sql, - included in the package), or +Defines which table is used for logging of Apache's transfers; this is analogous + to Apache's TransferLog directive. + table-name must be a valid table within the database defined in \noun on -LogSQLCreateTables +LogSQLDatabase \noun default - must be set to -\begin_inset Quotes eld -\end_inset - -on -\begin_inset Quotes erd -\end_inset - . -\layout Subsubsection - -LogSQLWhichHeadersOut -\layout LyX-Code - -Syntax: LogSQLWhichHeadersOut item1 item2 ... - itemN -\layout LyX-Code - -Example: LogSQLWhichHeadersOut Expires Content-Type Cache-Control -\layout LyX-Code - -Default: None -\layout LyX-Code - -Context: virtual host -\layout Quote +\layout Standard -Defines the list of outbound headers you would like logged. - This works in conjunction with +This directive is not necessary if you declare \noun on -LogSQLHeadersOutLogTable +LogSQLMassVirtualHosting On \noun default -. - This directive does not require any additional characters to be added to - the +, since that directive activates dynamically-named tables. + If you attempt to use \noun on -LogSQLTransferLogFormat +LogSQLTransferLogTable \noun default - string. - The feature is activated simply by including this directive, upon which - you will begin populating the separate outbound-headers table with data. -\layout Quote - -Note that you must have already created the table (see create-tables.sql, - included in the package), or + at the same time a warning will be logged and it will be ignored, since + \noun on -LogSQLCreateTables +LogSQLMassVirtualHosting \noun default - must be set to -\begin_inset Quotes eld -\end_inset - -on -\begin_inset Quotes erd -\end_inset - -. + takes priority. \layout Subsubsection -LogSQLWhichNotes +LogSQLWhichCookie \layout LyX-Code -Syntax: LogSQLWhichNotes item1 item2 ... - itemN +Syntax: LogSQLWhichCookie cookiename \layout LyX-Code -Example: LogSQLWhichNotes mod_gzip_result mod_gzip_compression_ratio +Example: LogSQLWhichCookie Clicks \layout LyX-Code Default: None \layout LyX-Code Context: virtual host -\layout Quote +\layout Standard -Defines the list of notes you would like logged. - This works in conjunction with -\noun on -LogSQLNotesLogTable -\noun default -. - This directive does not require any additional characters to be added to - the +In HTTP, cookies have names to distinguish them from each other. + Using mod_usertrack, for example, you can give your user-tracking cookies + a name with the CookieName directive. +\layout Standard + +You must include a 'c' character in \noun on LogSQLTransferLogFormat \noun default - string. - The feature is activated simply by including this directive, upon which - you will begin populating the separate notes table with data. -\layout Quote - -Note that you must have already created the table (see create-tables.sql, - included in the package), or + for this directive to take effect; once you specify 'c', \noun on -LogSQLCreateTables +LogSQLWhichCookie \noun default - must be set to -\begin_inset Quotes eld -\end_inset - -on -\begin_inset Quotes erd -\end_inset + tells mod_log_sql which cookie to log. + This is necessary because you will usually be setting and receiving more + than one cookie from a client; this cookie designates which one to log. +\layout Standard -. +Note: although this was intended for people who are using mod_usertrack + to set user-tracking cookies, you aren't restricted in any way. + You can choose which cookie you wish to log to the database --any cookie + at all -- and it doesn't necessarily have to have anything to do with mod_usert +rack. \layout Subsubsection -LogSQLCookieLogTable +LogSQLWhichCookies \layout LyX-Code -Syntax: LogSQLCookieLogTable table-name +Syntax: LogSQLWhichCookies cookie1 cookie2 ... + cookieN \layout LyX-Code -Example: LogSQLCookieLogTable cookie_log +Example: LogSQLWhichCookies userlogin foobar foobaz \layout LyX-Code -Default: cookies +Default: None \layout LyX-Code Context: virtual host -\layout Quote +\layout Standard -Defines which table is used for logging of cookies. - Working in conjunction with +Defines the list of cookies you would like logged. + This works in conjunction with \noun on -LogSQLWhichCookies +LogSQLCookieLogTable \noun default -, you can log many of each request's associated cookies to a separate table. - For meaningful data retrieval the cookie table is keyed to the access table - by the unique request ID supplied by the standard Apache module mod_unique_id. -\layout Quote +. + This directive does not require any additional characters to be added to + the +\noun on +LogSQLTransferLogFormat +\noun default + string. + The feature is activated simply by including this directive, upon which + you will begin populating the separate cookie table with data. +\layout Standard -Note that you must create the table (see create-tables.sql, included in the - package), or +Note that you must have already created the table (see create-tables.sql, + included in the package), or \noun on LogSQLCreateTables \noun default @@ -4663,34 +4872,40 @@ on . \layout Subsubsection -LogSQLHeadersInLogTable +LogSQLWhichHeadersIn \layout LyX-Code -Syntax: LogSQLHeadersInLogTable table-name +Syntax: LogSQLWhichHeadersIn item1 item2 ... + itemN \layout LyX-Code -Example: LogSQLHeadersInLogTable headers +Example: LogSQLWhichHeadersIn UserAgent Accept-Encoding Host \layout LyX-Code -Default: headers_in +Default: None \layout LyX-Code Context: virtual host -\layout Quote +\layout Standard -Defines which table is used for logging of inbound headers. - Working in conjunction with +Defines the list of inbound headers you would like logged. + This works in conjunction with \noun on -LogSQLWhichHeadersIn + LogSQLHeadersInLogTable \noun default -, you can log many of each request's associated headers to a separate table. - For meaningful data retrieval the headers table is keyed to the access - table by the unique request ID supplied by the standard Apache module mod_uniqu -e_id. -\layout Quote +. + This directive does not require any additional characters to be added to + the +\noun on +LogSQLTransferLogFormat +\noun default + string. + The feature is activated simply by including this directive, upon which + you will begin populating the separate inbound-headers table with data. +\layout Standard -Note that you must create the table (see create-tables.sql, included in the - package), or +Note that you must have already created the table (see create-tables.sql, + included in the package), or \noun on LogSQLCreateTables \noun default @@ -4705,34 +4920,40 @@ on . \layout Subsubsection -LogSQLHeadersOutLogTable +LogSQLWhichHeadersOut \layout LyX-Code -Syntax: LogSQLHeadersOutLogTable table-name +Syntax: LogSQLWhichHeadersOut item1 item2 ... + itemN \layout LyX-Code -Example: LogSQLHeadersOutLogTable headers +Example: LogSQLWhichHeadersOut Expires Content-Type Cache-Control \layout LyX-Code -Default: headers_out +Default: None \layout LyX-Code Context: virtual host -\layout Quote +\layout Standard -Defines which table is used for logging of outbound headers. - Working in conjunction with +Defines the list of outbound headers you would like logged. + This works in conjunction with \noun on -LogSQLWhichHeadersOut +LogSQLHeadersOutLogTable \noun default -, you can log many of each request's associated headers to a separate table. - For meaningful data retrieval the headers table is keyed to the access - table by the unique request ID supplied by the standard Apache module mod_uniqu -e_id. -\layout Quote +. + This directive does not require any additional characters to be added to + the +\noun on +LogSQLTransferLogFormat +\noun default + string. + The feature is activated simply by including this directive, upon which + you will begin populating the separate outbound-headers table with data. +\layout Standard -Note that you must create the table (see create-tables.sql, included in the - package), or +Note that you must have already created the table (see create-tables.sql, + included in the package), or \noun on LogSQLCreateTables \noun default @@ -4747,33 +4968,40 @@ on . \layout Subsubsection -LogSQLNotesLogTable +LogSQLWhichNotes \layout LyX-Code -Syntax: LogSQLNotesLogTable table-name +Syntax: LogSQLWhichNotes item1 item2 ... + itemN \layout LyX-Code -Example: LogSQLNotesLogTable notes_log +Example: LogSQLWhichNotes mod_gzip_result mod_gzip_compression_ratio \layout LyX-Code -Default: notes +Default: None \layout LyX-Code Context: virtual host -\layout Quote +\layout Standard -Defines which table is used for logging of notes. - Working in conjunction with +Defines the list of notes you would like logged. + This works in conjunction with \noun on -LogSQLWhichNotes +LogSQLNotesLogTable \noun default -, you can log many of each request's associated notes to a separate table. - For meaningful data retrieval the notes table is keyed to the access table - by the unique request ID supplied by the standard Apache module mod_unique_id. -\layout Quote +. + This directive does not require any additional characters to be added to + the +\noun on +LogSQLTransferLogFormat +\noun default + string. + The feature is activated simply by including this directive, upon which + you will begin populating the separate notes table with data. +\layout Standard -Note that you must create the table (see create-tables.sql, included in the - package), or +Note that you must have already created the table (see create-tables.sql, + included in the package), or \noun on LogSQLCreateTables \noun default @@ -4791,6 +5019,9 @@ on FAQ \layout Subsection +General module questions +\layout Subsubsection + \begin_inset LatexCommand \label{sub:why} @@ -5307,85 +5538,194 @@ http://modules.apache.org/search?id=339 \layout Standard -48 -\end_inset - - -\begin_inset Text - +48 +\end_inset + + +\begin_inset Text + +\layout Standard + +http://freshmeat.net/ +\end_inset + + + + +\begin_inset Text + +\layout Standard + +8 +\end_inset + + +\begin_inset Text + +\layout Standard + +http://freshmeat.net +\end_inset + + + + +\end_inset + + +\layout LyX-Code + +\layout Standard + +As you can see, there are myriad possibilities that can be constructed with + the wonderful SQL SELECT statement. + Logging to an SQL database can be really quite useful! +\layout Subsubsection + +Why use MySQL? Are there alternatives? +\layout Standard + +MySQL is a robust, free, and very powerful production-quality database engine. + It is well supported and comes with detailed documentation. + Many 3rd-party software pacakges (e.g. + Slashcode, the engine that powers Slashdot) run exclusively with MySQL. + In other words, you will belong to a very robust and well-supported community + by choosing MySQL. +\layout Standard + +That being said, there are alternatives. + PostgreSQL is probably MySQL's leading "competitor" in the free database + world. + There is also an excellent module available for Apache to permit logging + to a PostgreSQL database, called +\begin_inset LatexCommand \url[pgLOGd]{(http://www.digitalstratum.com/pglogd/)} + +\end_inset + +. +\layout Subsubsection + +Is this code production-ready? +\layout Standard + +By all accounts it is. + It is known to work without a problem on many-thousands-of-hits-per-day + webservers. + Does that mean it is 100% bug free? Well, no software is. + But it is well-tested and believed to be fully compatible with production + environments. + (The usual disclaimers apply. + This software is provided without warranty of any kind.) +\layout Subsubsection + +Who's using mod_log_sql? +\layout Standard + +Good question! It would be great to find out! If you are a production-level + mod_log_sql user, please contact +\begin_inset LatexCommand \url[the maintainer, Chris Powell]{(chris@grubbybaby.com)} + +\end_inset + + so that you can be mentioned here. +\layout Subsubsection + +Why doesn't the module also replace the Apache ErrorLog? +\layout Standard + +There are circumstances when that would be quite unwise -- for example, + if Apache could not reach the MySQL server for some reason and needed to + log that fact. + Without a text-based error log you'd never know anything was wrong, because + Apache would be trying to log a database connection error to the database... + you get the point. +\layout Standard + +Error logs are usually not very high-traffic and are really best left as + text files on a web server machine. +\layout Subsubsection + +Does mod_log_sql work with Apache 2.x? +\layout Standard + +As of this writing, no. + The Apache Group significantly altered the module API with the release + of Apache 2.0. + All modules written for 1.3, including mod_log_sql, will not work with 2.0. +\layout Standard + +mod_log_sql will eventually be ported to Apache 2.x, but not immediately. + It is going to take some time, and there are other features that have higher + priority. + Please sign up for the announcements list (on the main website) or monitor + the website for updates to learn when the port (and other releases) are + available. \layout Standard -http://freshmeat.net/ -\end_inset - - - - -\begin_inset Text +If you're a *NIX user, stick with Apache 1.3.x for now. + Major modules like mod_ssl and PHP are not even ready for 2.0 yet, and the + main benefits in 2.0 are for Win32 users anyway. + Apache 1.3.x is rock-stable and performs equally well on *NIX as 2.0. +\layout Subsubsection +Does mod_log_sql connect to MySQL via TCP/IP or a socket? \layout Standard -8 -\end_inset - - -\begin_inset Text +It depends! This is not determined by mod_log_sql. + mod_log_sql relies on a connection command that is supplied in the MySQL + API, and that command is somewhat intelligent. + How it works: +\layout Itemize + +if the specified MySQL database is on the same machine, the connection command + uses a socket to communicate with MySQL +\layout Itemize +if the specified MySQL database is on a different machine, mod_log_sql connects + using TCP/IP. + \layout Standard -http://freshmeat.net +You don't have any control of which methodology is used. + You can fine-tune some of the configuration, however. + The +\noun on +LogSQLSocketFile +\noun default + runtime configuration directive overrides the default of +\begin_inset Quotes eld \end_inset - - - +/var/lib/mysql/mysql.sock +\begin_inset Quotes erd \end_inset + for socket-based connections, whereas the +\noun on +LogSQLTCPPort +\noun default + command allows to you override the default TCP port of 3306 for TCP/IP + connections. +\layout Subsubsection -\layout LyX-Code - -\layout Standard - -As you can see, there are myriad possibilities that can be constructed with - the wonderful SQL SELECT statement. - Logging to an SQL database can be really quite useful! -\layout Subsection - -Why use MySQL? Are there alternatives? -\layout Standard - -MySQL is a robust, free, and very powerful production-quality database engine. - It is well supported and comes with detailed documentation. - Many 3rd-party software pacakges (e.g. - Slashcode, the engine that powers Slashdot) run exclusively with MySQL. - In other words, you will belong to a very robust and well-supported community - by choosing MySQL. +I have discovered a bug. + Who can I contact? \layout Standard -That being said, there are alternatives. - PostgreSQL is probably MySQL's leading "competitor" in the free database - world. - There is also an excellent module available for Apache to permit logging - to a PostgreSQL database, called -\begin_inset LatexCommand \url[pgLOGd]{(http://www.digitalstratum.com/pglogd/)} +Please contact +\begin_inset LatexCommand \url[the maintainer]{(chris@grubbybaby.com)} \end_inset -. +! Your comments, suggestions, bugfixes, bug catches, and usage testimonials + are always welcome. + As free software, mod_log_sql is intended to be a community effort -- any + code contributions or other ideas will be fully and openly credited, of + course. \layout Subsection -Is this code production-ready? -\layout Standard - -By all accounts it is. - It is known to work without a problem on many-thousands-of-hits-per-day - webservers. - Does that mean it is 100% bug free? Well, no software is. - But it is well-tested and /believed/ to be fully compatible with production - environments. - (The usual disclaimers apply. - This software is provided without warranty of any kind.) -\layout Subsection +Performance and Tuning +\layout Subsubsection How well does it perform? \layout Standard @@ -5525,14 +5865,14 @@ mysql> delete from access_log where agent like 'ApacheBench%'; \layout LyX-Code mysql> optimize table access_log; -\layout Subsection +\layout Subsubsection Do I need to be worried about all the running MySQL children? Will holding open \emph on n \emph default - Apache -> MySQL connections consume a lot of memory? + Apache-to-MySQL connections consume a lot of memory? \layout Standard Short answer: you shouldn't be worried. @@ -5564,8 +5904,8 @@ ps -aufxw 30 children each... \layout Standard -Fortunately the cost reported by 'ps -aufxw' is indeed deceptive due to - an OS memory-management feature called +Fortunately the cost reported by 'ps -aufxw' is deceptive. + This is due to an OS memory-management feature called \begin_inset Quotes eld \end_inset @@ -5573,7 +5913,6 @@ copy-on-write. \begin_inset Quotes erd \end_inset - This is a memory-management technique used by Unix-based systems. When you have a number of identical child processes (e.g. Apache, MySQL), it would appear in \begin_inset Quotes eld @@ -5597,7 +5936,8 @@ read-only fashion. The OS can get away with this because the majority of memory pages for one child are identical across all children. - + Instead of thinking of each child as a rubber stamp of the others, think + of each child as a basket of links to a common memory area. \layout Standard A memory page is only duplicated when it needs to be written to, hence @@ -5638,99 +5978,280 @@ correct \begin_inset Formula $(20\times 7MB)$ \end_inset - of memory consumption -- the real answer is much, much lower. - The same + of memory consumption -- the real answer is much, much lower. + The same +\begin_inset Quotes eld +\end_inset + +copy-on-write +\begin_inset Quotes erd +\end_inset + + rules apply to all your MySQL children: 40 mysqld children @ 22MB each + +\series bold +do not +\series default + occupy 880MB of RAM. +\layout Standard + +The bottom line: although there is a cost to spawn extra httpd or mysqld + children, that cost is not as great as +\begin_inset Quotes eld +\end_inset + +ps +\begin_inset Quotes erd +\end_inset + + would lead you to believe. +\layout Subsubsection + +My database cannot handle all the open connections from mod_log_sql, is + there anything I can do? +\layout Standard + +The rule of thumb: if you have +\emph on +n +\emph default +webservers each configured to support +\emph on +y +\emph default + +\noun on +MaxClients +\noun default +, then your database must be able to handle +\begin_inset Formula $n\times y$ +\end_inset + + simultenous connections +\emph on +in the worst case. + +\emph default +Certainly you must use common sense, consider reasonable traffic expectations + and structure things accordingly. +\layout Standard + +Tweaking my.cnf to scale to high connection loads is imperative. + But if hardware limitations prevent your MySQL server from gracefully handling + the number of incoming connections, it would be beneficial to upgrade the + memory or CPU on that server in order to handle the load. + +\layout Standard + +Please remember that mod_log_sql's overriding principle is +\series bold +performance +\series default + -- that is what the target audience demands and expects. + Other database logging solutions do not open and maintain many database + connections, but their performance suffers drastically. + For example, pgLOGd funnels all log connections through a separate daemon + that connects to the database, but that bottlenecks the entire process. + mod_log_sql achieves performance numbers an order of magnitude greater + than the alternatives because it dispenses with the overhead associated + with rapid connection cycling, and it doesn't attempt to shoehorn all the + database traffic through a single extra daemon or proxy process. +\layout Subsubsection + +My webservers cannot handle all the traffic that my site receives, is there + anything I can do? +\layout Standard + +If you have exhausted all the tuning possibilities on your existing server, + it is probably time you evaluated the benefits of clustering two or more + webservers together in a load-balanced fashion. + In fact, users of such a setup are mod_log_sql's target audience! +\layout Subsubsection + +Why do I occasionally see a +\begin_inset Quotes eld +\end_inset + +lost connection to MySQL server +\begin_inset Quotes erd +\end_inset + + message in my error-log? +\layout Standard + +This message may appear every now and then in your Apache error log, especially + on very lightly loaded servers. + This doesn't mean that anything is necessarily wrong. + Within each httpd child process, mod_log_sql will open (and keep open) + a connection to the MySQL server. + MySQL, however, will close connections that haven't been used in a while; + the default timeout is 8 hours. + When this occurs, mod_log_sql will notice and re-open the connection. + That event is what is being logged, and looks like this: +\layout LyX-Code + +[Tue Nov 12 19:04:10 2002] [error] mod_log_sql: first attempt failed, +\layout LyX-Code + + API said: error 2013, Lost connection to MySQL server during query +\layout LyX-Code + +[Tue Nov 12 19:04:10 2002] [error] mod_log_sql: reconnect successful +\layout LyX-Code + +[Tue Nov 12 19:04:10 2002] [error] mod_log_sql: second attempt successful +\layout Standard + +Reference: +\begin_inset LatexCommand \url[MySQL documentation]{(http://www.mysql.com/documentation/mysql/bychapter/manual_Problems.html#Gone_away)} + +\end_inset + + +\layout Subsubsection + + +\begin_inset LatexCommand \label{sub:DelayedInsFAQ} + +\end_inset + +What is the issue with activating delayed inserts? +\layout Standard + +There are several. +\layout Enumerate + +INSERT DELAYED is a specific syntax to MySQL and is not supported by any + other database. + Ergo, why is it needed, and what MySQL deficiency is it working around? + INSERT DELAYED is a kluge. +\layout Enumerate + +The MySQL documentation is unclear whether INSERT DELAYED is even necessary + for an optimized database. + It says, +\begin_inset Quotes eld +\end_inset + +The DELAYED option for the INSERT statement is a MySQL-specific option that + is very useful if you have clients that can't wait for the INSERT to complete. +\begin_inset Quotes erd +\end_inset + + But then it goes on to say, \begin_inset Quotes eld \end_inset -copy-on-write +Note that as MyISAM tables supports concurrent SELECT and INSERT, if there + is no free blocks in the middle of the data file, you very seldom need + to use INSERT DELAYED with MyISAM. \begin_inset Quotes erd \end_inset - rules apply to all your MySQL children: 40 mysqld children @ 22MB each - -\series bold -do not -\series default - occupy 880MB of RAM. + +\layout Enumerate + +Because INSERT DELAYED returns without waiting for the data to be written, + a hard kill of your MySQL database at the right (wrong?) moment could lose + those logfile entries. +\layout Enumerate + +As of MySQL version 3.23.52, the error return functions disagree after a failed + INSERT DELAYED: mysql_errno() always returns 0, even if mysql_error() returns + a textual error. + I have reported this bug to the MySQL folks. + However, we have no way of knowing what solution they will adopt to fix + this, and with the worst case solution mod_log_sql would not be able to + tell if anything went wrong with a delayed insert. \layout Standard -The bottom line: although tangible, there is not much memory cost associated - with spawning off extra httpd or mysqld children. +If after understanding these problems you still wish to enable delayed inserts, + section +\begin_inset LatexCommand \ref{sub:DelayedIns} + +\end_inset + + discusses how. \layout Subsection -My database cannot handle all the open connections from mod_log_sql, is - there anything I can do? -\layout Standard -The rule of thumb: if you have -\emph on -n -\emph default -webservers each configured to support -\emph on -y -\emph default - -\noun on -MaxClients -\noun default -, then your database must be able to handle -\begin_inset Formula $n\times y$ +\begin_inset Quotes eld \end_inset - simultenous connections -\emph on -in the worst case. - -\emph default -Certainly you must use common sense, consider reasonable traffic expectations - and structure things accordingly. +How do I...? +\begin_inset Quotes erd +\end_inset + + -- accomplishing certain tasks +\layout Subsubsection + +I am using LogSQLMassVirtualHosting, and sometimes a single VirtualHost + gets logged to two different tables. + How do I prevent that? \layout Standard -Tweaking my.cnf to scale to high connection loads is imperative. - But if hardware limitations prevent your MySQL server from gracefully handling - the number of incoming connections, it would be beneficial to upgrade the - memory or CPU on that server in order to handle the load. +Proper usage of the Apache runtime +\noun on +ServerName +\noun default + directive and the directive +\noun on +UseCanonicalName On +\noun default +(or +\noun on +DNS +\noun default +) are necessary to prevent this problem. -\layout Standard +\begin_inset Quotes eld +\end_inset -Please remember that mod_log_sql's overriding principle is -\series bold -performance -\series default - -- that is what the target audience demands and expects. - Other database logging solutions do not open and maintain many database - connections, but their performance suffers drastically. - For example, pgLOGd funnels all log connections through a separate daemon - that connects to the database, but that bottlenecks the entire process. - mod_log_sql achieves performance numbers an order of magnitude greater - than the alternatives because it dispenses with the overhead associated - with rapid connection cycling, and it doesn't attempt to shoehorn all the - database traffic through a single extra daemon or proxy process. -\layout Subsection +On +\begin_inset Quotes erd +\end_inset -My webservers cannot handle all the traffic that my site receives, is there - anything I can do? -\layout Standard + is the default for +\noun on +UseCanonicalName +\noun default +, and specifies that self-referential URLs are generated from the +\noun on +ServerName +\noun default +part of your VirtualHost: +\layout Quote -If you have exhausted all the tuning possibilities on your existing server, - it is probably time you evaluated the benefits of clustering two or more - webservers together in a load-balanced fashion. - In fact, users of such a setup are mod_log_sql's target audience! -\layout Subsection +With UseCanonicalName on (and in all versions prior to 1.3) Apache will use + the ServerName and Port directives to construct the canonical name for + the server. + With UseCanonicalName off Apache will form self-referential URLs using + the hostname and port supplied by the client if any are supplied (otherwise + it will use the canonical name, as defined above). + [From +\begin_inset LatexCommand \url[the Apache documentation]{http://httpd.apache.org/docs/mod/core.html#usecanonicalname} -Who's using mod_log_sql? +\end_inset + +] \layout Standard -Good question! It would be great to find out! If you are a production-level - mod_log_sql user, please contact -\begin_inset LatexCommand \url[the maintainer, Chris Powell]{(chris@grubbybaby.com)} +The module inherits Apache's +\begin_inset Quotes eld +\end_inset +knowledge +\begin_inset Quotes erd \end_inset - so that you can be mentioned here. -\layout Subsection + about the server name being accessed. + As long as those two directives are properly configured, mod_log_sql will + log to only one table per virtual host while using +\noun on +LogSQLMassVirtualHosting +\noun default +. +\layout Subsubsection How do I extract the data in a format that my analysis tool can understand? \layout Standard @@ -5790,22 +6311,7 @@ rm -f /var/log/httpd/templog \layout Standard See? Easy. -\layout Subsection - -Why doesn't the module also replace the Apache ErrorLog? -\layout Standard - -There are circumstances when that would be quite unwise -- for example, - if Apache could not reach the MySQL server for some reason and needed to - log that fact. - Without a text-based error log you'd never know anything was wrong, because - Apache would be trying to log a database connection error to the database... - you get the point. -\layout Standard - -Error logs are usually not very high-traffic and are really best left as - text files on a web server machine. -\layout Subsection +\layout Subsubsection \begin_inset LatexCommand \label{sec:cookie} @@ -6007,7 +6513,7 @@ ool-18e4.dyn.optonline.net.130051007102700823 \layout LyX-Code -\layout Subsection +\layout Subsubsection What if I want to log more than one cookie? What is the difference between LogSQLWhichCookie and LogSQLWhichCookies? @@ -6077,7 +6583,7 @@ LogSQLWhichCookies \noun default can coexist without conflict because they operate on entireley different tables, but you're better off choosing the one you need. -\layout Subsection +\layout Subsubsection What are the SSL logging features, and how do I activate them? \layout Standard @@ -6091,6 +6597,7 @@ need to compile SSL support into mod_log_sql in order to simply use it with You only need to compile SSL support into mod_log_sql if you want to log SSL-specific data such as the cipher type used, or the keysize that was negotiated. + If that information is unimportant to you, you can ignore this FAQ. \layout Standard By adding certain characters to your @@ -6349,128 +6856,4 @@ RC4-MD5 \layout LyX-Code -\layout Subsection - -Does mod_log_sql connect to MySQL via TCP/IP or a socket? -\layout Standard - -It depends! This is not determined by mod_log_sql. - mod_log_sql relies on a connection command that is supplied in the MySQL - API, and that command is somewhat intelligent. - How it works: -\layout Itemize - -if the specified MySQL database is on the same machine, the connection command - uses a socket to communicate with MySQL -\layout Itemize - -if the specified MySQL database is on a different machine, mod_log_sql connects - using TCP/IP. - -\layout Standard - -You don't have any control of which methodology is used. - You can fine-tune some of the configuration, however. - The -\noun on -LogSQLSocketFile -\noun default - runtime configuration directive overrides the default of -\begin_inset Quotes eld -\end_inset - -/var/lib/mysql/mysql.sock -\begin_inset Quotes erd -\end_inset - - for socket-based connections, whereas the -\noun on -LogSQLTCPPort -\noun default - command allows to you override the default TCP port of 3306 for TCP/IP - connections. -\layout Subsection - -Why do I occasionally see a -\begin_inset Quotes eld -\end_inset - -lost connection to MySQL server -\begin_inset Quotes erd -\end_inset - - message in my error-log? -\layout Standard - -This message may appear every now and then in your Apache error log, especially - on very lightly loaded servers. - This doesn't mean that anything is necessarily wrong. - Within each httpd child process, mod_log_sql will open (and keep open) - a connection to the MySQL server. - MySQL, however, will close connections that haven't been used in a while; - the default timeout is 8 hours. - When this occurs, mod_log_sql will notice and re-open the connection. - That event is what is being logged, and looks like this: -\layout LyX-Code - -[Tue Nov 12 19:04:10 2002] [error] mod_log_sql: first attempt failed, -\layout LyX-Code - - API said: error 2013, Lost connection to MySQL server during query -\layout LyX-Code - -[Tue Nov 12 19:04:10 2002] [error] mod_log_sql: reconnect successful -\layout LyX-Code - -[Tue Nov 12 19:04:10 2002] [error] mod_log_sql: second attempt successful - -\layout LyX-Code - -\layout Standard - -Reference: -\begin_inset LatexCommand \url[MySQL documentation]{(http://www.mysql.com/documentation/mysql/bychapter/manual_Problems.html#Gone_away)} - -\end_inset - - -\layout Subsection - -Does mod_log_sql work with Apache 2.x? -\layout Standard - -As of this writing, no. - The Apache Group significantly altered the module API with the release - of Apache 2.0. - All modules written for 1.3, including mod_log_sql, will not work with 2.0. -\layout Standard - -mod_log_sql will eventually be ported to Apache 2.x, but not immediately. - It is going to take some time, and there are other features that have higher - priority. - Please sign up for the announcements list (on the main website) or monitor - the website for updates to learn when the port (and other releases) are - available. -\layout Standard - -If you're a *NIX user, stick with Apache 1.3.x for now. - Major modules like mod_ssl and PHP are not even ready for 2.0 yet, and the - main benefits in 2.0 are for Win32 users anyway. - Apache 1.3.x is rock-stable and performs equally well on *NIX as 2.0. -\layout Subsection - -I have discovered a bug. - Who can I contact? -\layout Standard - -Please contact -\begin_inset LatexCommand \url[the maintainer]{(chris@grubbybaby.com)} - -\end_inset - -! Your comments, suggestions, bugfixes, bug catches, and usage testimonials - are always welcome. - As free software, mod_log_sql is intended to be a community effort -- any - code contributions or other ideas will be fully and openly credited, of - course. \the_end diff --git a/Makefile b/Makefile index d2effa0..6f69b50 100644 --- a/Makefile +++ b/Makefile @@ -1,4 +1,4 @@ -# $Id: Makefile,v 1.17 2002/11/27 07:13:58 helios Exp $ +# $Id: Makefile,v 1.18 2002/12/10 19:43:21 helios Exp $ ########################################################################### # Important: @@ -13,7 +13,7 @@ MYSQLLIBS = /usr/lib MYSQLHDRS = /usr/include/mysql #MODSSLHDRS = /usr/local/src/apache_1.3.27-dso/src/modules/ssl -DB1HDRS = /usr/include/db1 +#DB1HDRS = /usr/include/db1 ########################################################################### # Don't uncomment this without reading the "Optimizing for a busy database" diff --git a/create_tables.sql b/create_tables.sql index 430d1a7..bc19a10 100644 --- a/create_tables.sql +++ b/create_tables.sql @@ -1,10 +1,10 @@ create table access_log ( - id char(19) , + id char(19) , agent varchar(255) , bytes_sent int unsigned , child_pid smallint unsigned, cookie varchar(255), - machine_id varchar(25), + machine_id varchar(25), request_file varchar(255), referer varchar(255) , remote_host varchar(50) , @@ -47,4 +47,4 @@ create table cookies ( id char(19), item varchar(80), val varchar(80) -); \ No newline at end of file +); diff --git a/mod_log_sql.c b/mod_log_sql.c index 3fc7ace..117111b 100644 --- a/mod_log_sql.c +++ b/mod_log_sql.c @@ -1,11 +1,11 @@ -/* $Id: mod_log_sql.c,v 1.19 2002/11/27 07:13:58 helios Exp $ */ +/* $Id: mod_log_sql.c,v 1.20 2002/12/10 19:43:21 helios Exp $ */ /* --------* * DEFINES * * --------*/ /* The enduser may wish to modify this */ -#define DEBUG +#undef DEBUG /* The enduser won't modify these */ #define MYSQL_ERROR(mysql) ((mysql)?(mysql_error(mysql)):"MySQL server has gone away") @@ -20,11 +20,13 @@ #include #include #include +#include #include "httpd.h" #include "http_config.h" #include "http_log.h" #include "http_core.h" #include "mysql.h" +#include "mysqld_error.h" #if MODULE_MAGIC_NUMBER >= 19980324 /* M_M_N is defined in /usr/local/Apache/include/ap_mmn.h, 19990320 as of this writing. */ #include "ap_compat.h" @@ -73,8 +75,8 @@ typedef const char *(*item_key_func) (request_rec *, char *); * Each child process has its own segregated copy of this structure. */ typedef struct { - int table_made; array_header *transfer_ignore_list; + array_header *transfer_accept_list; array_header *remhost_ignore_list; array_header *notes_list; array_header *hout_list; @@ -94,6 +96,9 @@ typedef struct { /* -----------------* * HELPER FUNCTIONS * * -----------------*/ + +int safe_create_tables(log_sql_state *cls, request_rec *r); + static char *format_integer(pool *p, int i) { char dummy[40]; @@ -484,7 +489,7 @@ static const char *extract_request_timestamp(request_rec *r, char *a) { char tstr[32]; - snprintf(tstr, 32, "%ld", time(NULL)); + ap_snprintf(tstr, 32, "%ld", time(NULL)); return pstrdup(r->pool, tstr); } @@ -665,73 +670,123 @@ void preserve_entry(request_rec *r, const char *query) /* Parms: request record, SQL insert statement */ /* Returns: 0 (OK) on success */ /* 1 if have no log handle */ -/* actual MySQL return code on error */ +/* 2 if insert delayed failed (kluge) */ +/* the actual MySQL return code on error */ /*-----------------------------------------------------*/ unsigned int safe_mysql_query(request_rec *r, const char *query) { - unsigned int retval; - unsigned int real_error; + int retval; struct timespec delay, remainder; int ret; void (*handler) (int); - + log_sql_state *cls; + unsigned int real_error; + #ifdef WANT_DELAYED_MYSQL_INSERT + char *real_error_str; + #endif /* A failed mysql_query() may send a SIGPIPE, so we ignore that signal momentarily. */ handler = signal(SIGPIPE, SIG_IGN); /* First attempt for the query */ - if (mysql_log != NULL) - retval = mysql_query(mysql_log, query); - else - return 1; + if (!mysql_log) { + signal(SIGPIPE, handler); + return 1; + } else if (!(retval = mysql_query(mysql_log, query))) { + signal(SIGPIPE, handler); + return 0; + } - if ( retval != 0 ) - { - /* If we ran the query and it returned an error, try to be robust. - * (After all, the module thought it had a valid mysql_log connection but the query - * could have failed for a number of reasons, so we have to be extra-safe and check.) */ - - log_sql_state *cls = get_module_config(r->server->module_config, &sql_log_module); - - real_error = mysql_errno(mysql_log); /* What really happened? */ - - /* Something went wrong, so start by trying to restart the db link. */ - ap_log_error(APLOG_MARK,ERRLEVEL,r->server,"mod_log_sql: first attempt failed, API said: error %d, %s", real_error, MYSQL_ERROR(mysql_log)); - mysql_close(mysql_log); - mysql_log = NULL; - open_logdb_link(r->server); - - if (mysql_log == NULL) { /* still unable to link */ - signal(SIGPIPE, handler); - ap_log_error(APLOG_MARK,ERRLEVEL,r->server,"mod_log_sql: reconnect failed, unable to reach database. SQL logging stopped until child regains a db connection."); - ap_log_error(APLOG_MARK,ERRLEVEL,r->server,"mod_log_sql: log entries are being preserved in %s", cls->preserve_file); - return 1; - } else - ap_log_error(APLOG_MARK,ERRLEVEL,r->server,"mod_log_sql: reconnect successful"); - - /* First sleep for a tiny amount of time. */ - delay.tv_sec = 0; - delay.tv_nsec = 250000000; /* max is 999999999 (nine nines) */ - ret = nanosleep(&delay, &remainder); - if (ret && errno != EINTR) - ap_log_error(APLOG_MARK,ERRLEVEL,r->server,"mod_log_sql: nanosleep unsuccessful"); - - /* Then make our second attempt */ - retval = mysql_query(mysql_log,query); - - /* If this one also failed, log that and append to our local offline file */ - if ( retval != 0 ) - { - real_error = mysql_errno(mysql_log); - ap_log_error(APLOG_MARK,ERRLEVEL,r->server,"mod_log_sql: second attempt failed, API said: error %d, %s", real_error, MYSQL_ERROR(mysql_log)); - retval = real_error; - } else - ap_log_error(APLOG_MARK,ERRLEVEL,r->server,"mod_log_sql: second attempt successful"); + /* If we ran the query and it returned an error, try to be robust. + * (After all, the module thought it had a valid mysql_log connection but the query + * could have failed for a number of reasons, so we have to be extra-safe and check.) */ + #ifdef WANT_DELAYED_MYSQL_INSERT + real_error_str = MYSQL_ERROR(mysql_log); + #else + real_error = mysql_errno(mysql_log); + #endif + + /* Check to see if the error is "nonexistent table" */ + #ifdef WANT_DELAYED_MYSQL_INSERT + if ((strstr(real_error_str, "Table")) && (strstr(real_error_str,"doesn't exist"))) { + #else + if (real_error == ER_NO_SUCH_TABLE) { + #endif + if (create_tables) { + ap_log_error(APLOG_MARK,ERRLEVEL,r->server,"mod_log_sql: table doesn't exist...creating now"); + cls = get_module_config(r->server->module_config, &sql_log_module); + if (safe_create_tables(cls, r)) { + ap_log_error(APLOG_MARK,ERRLEVEL,r->server,"mod_log_sql: child attempted but failed to create one or more tables for %s, preserving query", ap_get_server_name(r)); + preserve_entry(r, query); + retval = mysql_errno(mysql_log); + } else { + ap_log_error(APLOG_MARK,ERRLEVEL,r->server,"mod_log_sql: tables successfully created - retrying query"); + if (mysql_query(mysql_log, query)) { + ap_log_error(APLOG_MARK,ERRLEVEL,r->server,"mod_log_sql: giving up, preserving query"); + preserve_entry(r, query); + retval = mysql_errno(mysql_log); + } else + ap_log_error(APLOG_MARK,ERRLEVEL,r->server,"mod_log_sql: query successful after table creation"); + retval = 0; + } + } else { + ap_log_error(APLOG_MARK,ERRLEVEL,r->server,"mod_log_sql, table doesn't exist, creation denied by configuration, preserving query"); + preserve_entry(r, query); + retval = ER_NO_SUCH_TABLE; + } + /* Restore SIGPIPE to its original handler function */ + signal(SIGPIPE, handler); + return retval; } + /* Handle all other types of errors */ + + cls = get_module_config(r->server->module_config, &sql_log_module); + + /* Something went wrong, so start by trying to restart the db link. */ + #ifdef WANT_DELAYED_MYSQL_INSERT + real_error = 2; + #else + real_error = mysql_errno(mysql_log); + #endif + ap_log_error(APLOG_MARK,ERRLEVEL,r->server,"mod_log_sql: first attempt failed, API said: error %d, \"%s\"", real_error, MYSQL_ERROR(mysql_log)); + mysql_close(mysql_log); + mysql_log = NULL; + open_logdb_link(r->server); + + if (mysql_log == NULL) { /* still unable to link */ + signal(SIGPIPE, handler); + ap_log_error(APLOG_MARK,ERRLEVEL,r->server,"mod_log_sql: reconnect failed, unable to reach database. SQL logging stopped until child regains a db connection."); + ap_log_error(APLOG_MARK,ERRLEVEL,r->server,"mod_log_sql: log entries are being preserved in %s", cls->preserve_file); + return 1; + } else + ap_log_error(APLOG_MARK,ERRLEVEL,r->server,"mod_log_sql: db reconnect successful"); + + /* First sleep for a tiny amount of time. */ + delay.tv_sec = 0; + delay.tv_nsec = 250000000; /* max is 999999999 (nine nines) */ + ret = nanosleep(&delay, &remainder); + if (ret && errno != EINTR) + ap_log_error(APLOG_MARK,ERRLEVEL,r->server,"mod_log_sql: nanosleep unsuccessful"); + + /* Then make our second attempt */ + retval = mysql_query(mysql_log,query); + + /* If this one also failed, log that and append to our local offline file */ + if (retval) { + #ifdef WANT_DELAYED_MYSQL_INSERT + real_error = 2; + #else + real_error = mysql_errno(mysql_log); + #endif + ap_log_error(APLOG_MARK,ERRLEVEL,r->server,"mod_log_sql: second attempt failed, API said: error %d, \"%s\" -- preserving", real_error, MYSQL_ERROR(mysql_log)); + preserve_entry(r, query); + retval = real_error; + } else + ap_log_error(APLOG_MARK,ERRLEVEL,r->server,"mod_log_sql: second attempt successful"); + /* Restore SIGPIPE to its original handler function */ signal(SIGPIPE, handler); - return retval; } @@ -811,35 +866,29 @@ int safe_create_tables(log_sql_state *cls, request_rec *r) #endif /* Assume that things worked unless told otherwise */ - cls->table_made = 1; retval = 0; - if ((create_results = safe_mysql_query(r, create_access)) != 0) { - cls->table_made = 0; + if ((create_results = safe_mysql_query(r, create_access))) { ap_log_error(APLOG_MARK,ERRLEVEL,r->server,"mod_log_sql: failed to create access table"); retval = create_results; } - if ((create_results = safe_mysql_query(r, create_notes)) != 0) { - cls->table_made = 0; + if ((create_results = safe_mysql_query(r, create_notes))) { ap_log_error(APLOG_MARK,ERRLEVEL,r->server,"mod_log_sql: failed to create notes table"); retval = create_results; } - if ((create_results = safe_mysql_query(r, create_hin)) != 0) { - cls->table_made = 0; + if ((create_results = safe_mysql_query(r, create_hin))) { ap_log_error(APLOG_MARK,ERRLEVEL,r->server,"mod_log_sql: failed to create header_out table"); retval = create_results; } - if ((create_results = safe_mysql_query(r, create_hout)) != 0) { - cls->table_made = 0; + if ((create_results = safe_mysql_query(r, create_hout))) { ap_log_error(APLOG_MARK,ERRLEVEL,r->server,"mod_log_sql: failed to create header_in table"); retval = create_results; } - if ((create_results = safe_mysql_query(r, create_cookies)) != 0) { - cls->table_made = 0; + if ((create_results = safe_mysql_query(r, create_cookies))) { ap_log_error(APLOG_MARK,ERRLEVEL,r->server,"mod_log_sql: failed to create cookies table"); retval = create_results; } @@ -872,8 +921,8 @@ const char *set_log_sql_machine_id(cmd_parms *parms, void *dummy, char *arg) const char *set_log_sql_create(cmd_parms *parms, void *dummy, int flag) { - if (massvirtual != 0) - ap_log_error(APLOG_MARK,WARNINGLEVEL,parms->server,"mod_log_sql: do not set LogSQLCreateTables when LogSQLMassVirtualHosting is On. Ignoring."); + if (massvirtual) + ap_log_error(APLOG_MARK,WARNINGLEVEL,parms->server,"mod_log_sql: do not set LogSQLCreateTables when LogSQLMassVirtualHosting is On. Ignoring."); else create_tables = ( flag ? 1 : 0); return NULL; @@ -994,6 +1043,16 @@ const char *set_log_sql_tcp_port(cmd_parms *parms, void *dummy, char *arg) return NULL; } +const char *add_log_sql_transfer_accept(cmd_parms *parms, void *dummy, char *arg) +{ + char **addme; + log_sql_state *cls = get_module_config(parms->server->module_config, &sql_log_module); + + addme = push_array(cls->transfer_accept_list); + *addme = pstrdup(cls->transfer_accept_list->pool, arg); + return NULL; +} + const char *add_log_sql_transfer_ignore(cmd_parms *parms, void *dummy, char *arg) { char **addme; @@ -1072,8 +1131,9 @@ const char *add_log_sql_cookie(cmd_parms *parms, void *dummy, char *arg) */ static void log_sql_child_init(server_rec *s, pool *p) { - int retval; + int retval; + /* Open a link to the database */ retval = open_logdb_link(s); if (retval == 0) ap_log_error(APLOG_MARK,ERRLEVEL,s,"mod_log_sql: child spawned but unable to open database link"); @@ -1140,12 +1200,12 @@ void *log_sql_make_state(pool *p, server_rec *s) cls->preserve_file = "/tmp/sql-preserve"; cls->transfer_ignore_list = make_array(p, 1, sizeof(char *)); + cls->transfer_accept_list = make_array(p, 1, sizeof(char *)); cls->remhost_ignore_list = make_array(p, 1, sizeof(char *)); cls->notes_list = make_array(p, 1, sizeof(char *)); cls->hin_list = make_array(p, 1, sizeof(char *)); cls->hout_list = make_array(p, 1, sizeof(char *)); cls->cookie_list = make_array(p, 1, sizeof(char *)); - cls->table_made = 0; cls->cookie_name = NULL; return (void *) cls; @@ -1177,6 +1237,9 @@ command_rec log_sql_cmds[] = { {"LogSQLMachineID", set_log_sql_machine_id, NULL, RSRC_CONF, TAKE1, "Machine ID that the module will log, useful in web clusters to differentiate machines"} , + {"LogSQLRequestAccept", add_log_sql_transfer_accept, NULL, RSRC_CONF, ITERATE, + "List of URIs to accept for logging. Accesses that don't match will not be logged"} + , {"LogSQLRequestIgnore", add_log_sql_transfer_ignore, NULL, RSRC_CONF, ITERATE, "List of URIs to ignore. Accesses that match will not be logged to database"} , @@ -1240,18 +1303,18 @@ int log_sql_transaction(request_rec *orig) /* We handle mass virtual hosting differently. Dynamically determine the name * of the table from the virtual server's name, and flag it for creation. */ - if ( massvirtual == 1 ) { + if (massvirtual) { char *access_base = "access_"; - char *notes_base = "notes_"; - char *hout_base = "headout_"; - char *hin_base = "headin_"; + char *notes_base = "notes_"; + char *hout_base = "headout_"; + char *hin_base = "headin_"; char *cookie_base = "cookies_"; char *a_tablename; char *n_tablename; char *i_tablename; char *o_tablename; char *c_tablename; - int i; + unsigned int i; /* Find memory long enough to hold the table name + \0. */ a_tablename = ap_pstrcat(orig->pool, access_base, ap_get_server_name(orig), NULL); @@ -1308,7 +1371,7 @@ int log_sql_transaction(request_rec *orig) const char *unique_id; const char *formatted_item; int i, j, length; - int result; + int proceed; for (r = orig; r->next; r = r->next) { continue; @@ -1317,16 +1380,29 @@ int log_sql_transaction(request_rec *orig) /* The following is a stolen upsetting mess of pointers, I'm sorry. * Anyone with the motiviation and/or the time should feel free * to make this cleaner. :) */ - ptrptr2 = (char **) (cls->transfer_ignore_list->elts + (cls->transfer_ignore_list->nelts * cls->transfer_ignore_list->elt_size)); + ptrptr2 = (char **) (cls->transfer_accept_list->elts + (cls->transfer_accept_list->nelts * cls->transfer_accept_list->elt_size)); + + /* Go through each element of the accept list and compare it to the + * request_uri. If we don't get a match, return without logging */ + if ((r->uri) && (cls->transfer_accept_list->nelts)) { + proceed = 0; + for (ptrptr = (char **) cls->transfer_accept_list->elts; ptrptr < ptrptr2; ptrptr = (char **) ((char *) ptrptr + cls->transfer_accept_list->elt_size)) + if (strstr(r->uri, *ptrptr)) { + proceed = 1; + break; + } + if (!proceed) + return OK; + } /* Go through each element of the ignore list and compare it to the * request_uri. If we get a match, return without logging */ + ptrptr2 = (char **) (cls->transfer_ignore_list->elts + (cls->transfer_ignore_list->nelts * cls->transfer_ignore_list->elt_size)); if (r->uri) { - for (ptrptr = (char **) cls->transfer_ignore_list->elts; ptrptr < ptrptr2; ptrptr = (char **) ((char *) ptrptr + cls->transfer_ignore_list->elt_size)) { + for (ptrptr = (char **) cls->transfer_ignore_list->elts; ptrptr < ptrptr2; ptrptr = (char **) ((char *) ptrptr + cls->transfer_ignore_list->elt_size)) if (strstr(r->uri, *ptrptr)) { return OK; } - } } /* Go through each element of the ignore list and compare it to the @@ -1334,11 +1410,10 @@ int log_sql_transaction(request_rec *orig) ptrptr2 = (char **) (cls->remhost_ignore_list->elts + (cls->remhost_ignore_list->nelts * cls->remhost_ignore_list->elt_size)); thehost = get_remote_host(r->connection, r->per_dir_config, REMOTE_NAME); if (thehost) { - for (ptrptr = (char **) cls->remhost_ignore_list->elts; ptrptr < ptrptr2; ptrptr = (char **) ((char *) ptrptr + cls->remhost_ignore_list->elt_size)) { + for (ptrptr = (char **) cls->remhost_ignore_list->elts; ptrptr < ptrptr2; ptrptr = (char **) ((char *) ptrptr + cls->remhost_ignore_list->elt_size)) if (strstr(thehost, *ptrptr)) { return OK; } - } } length = strlen(cls->transfer_log_format); @@ -1530,7 +1605,7 @@ int log_sql_transaction(request_rec *orig) if (mysql_log == NULL) { /* Unable to re-establish a DB link, so assume that it's really * gone and send the entry to the preserve file instead. - * This short-circuits safe_mysql_query during a db outage and therefore + * This short-circuits safe_mysql_query() during a db outage and therefore * we don't keep logging the db error over and over. */ preserve_entry(orig, access_query); @@ -1554,52 +1629,20 @@ int log_sql_transaction(request_rec *orig) /* ---> So as of here we have a non-null value of mysql_log. <--- */ /* ---> i.e. we have a good MySQL connection. <--- */ - - /* Make the tables if we're supposed to. */ - if ((cls->table_made != 1) && (create_tables != 0)) { - result = safe_create_tables(cls, orig); - if (result != 0) - ap_log_error(APLOG_MARK,ERRLEVEL,orig->server,"mod_log_sql: child attempted but failed to create one or more tables for %s", ap_get_server_name(orig)); - else - ap_log_error(APLOG_MARK,NOTICELEVEL,orig->server,"mod_log_sql: no problems creating tables for %s", ap_get_server_name(orig)); - } - /* Make the access-table insert */ - result = safe_mysql_query(orig, access_query); - - /* It failed, but NOT because table didn't exist */ - if ( (result != 0) && (result != 1146) ) - preserve_entry(orig,access_query); - - /* It failed because table didn't exist */ - if (result == 1146) { - ap_log_error(APLOG_MARK,ERRLEVEL,orig->server,"mod_log_sql: hmm, table didn't yet exist; creating"); - result = safe_create_tables(cls, orig); - if (result != 0) { - ap_log_error(APLOG_MARK,ERRLEVEL,orig->server,"mod_log_sql: child attempted but failed to create one or more tables for %s, preserving query", ap_get_server_name(orig)); - preserve_entry(orig,access_query); - } else { - ap_log_error(APLOG_MARK,ERRLEVEL,orig->server,"mod_log_sql: table successfully created, query will now be retried"); - result = safe_mysql_query(orig, access_query); - if (result != 0 ) { - ap_log_error(APLOG_MARK,ERRLEVEL,orig->server,"mod_log_sql: giving up, preserving query"); - preserve_entry(orig,access_query); - return OK; - } - } - } + safe_mysql_query(orig, access_query); /* Log the optional notes, headers, etc. */ - if ( note_query != NULL ) + if (note_query) safe_mysql_query(orig, note_query); - if ( hout_query != NULL ) + if (hout_query) safe_mysql_query(orig, hout_query); - if ( hin_query != NULL ) + if (hin_query) safe_mysql_query(orig, hin_query); - if ( cookie_query != NULL ) + if (cookie_query) safe_mysql_query(orig, cookie_query); return OK; @@ -1634,3 +1677,46 @@ module sql_log_module = { #endif }; + + + +/* Make the tables if we're supposed to. */ +/* +if ((cls->table_made != 1) && (create_tables != 0)) { + result = safe_create_tables(cls, orig); + if (result != 0) + ap_log_error(APLOG_MARK,ERRLEVEL,orig->server,"mod_log_sql: child attempted but failed to create one or more tables for %s", ap_get_server_name(orig)); + else + ap_log_error(APLOG_MARK,NOTICELEVEL,orig->server,"mod_log_sql: no problems creating tables for %s", ap_get_server_name(orig)); +} +*/ + +/* It failed, but NOT because table didn't exist */ +/* +if ( (result) && (result != ER_NO_SUCH_TABLE) ) + preserve_entry(orig,access_query); +*/ + +/* It failed because table didn't exist */ +/* +if (result == ER_NO_SUCH_TABLE) + if (create_tables) { + ap_log_error(APLOG_MARK,ERRLEVEL,orig->server,"mod_log_sql: access table doesn't exist...creating now"); + if ((result = safe_create_tables(cls, orig))) { + ap_log_error(APLOG_MARK,ERRLEVEL,orig->server,"mod_log_sql: child attempted but failed to create one or more tables for %s, preserving query", ap_get_server_name(orig)); + preserve_entry(orig,access_query); + } else { + ap_log_error(APLOG_MARK,ERRLEVEL,orig->server,"mod_log_sql: table(s) successfully created - retrying insert"); + if ((result = safe_mysql_query(orig, access_query))) { + ap_log_error(APLOG_MARK,ERRLEVEL,orig->server,"mod_log_sql: giving up, preserving query"); + preserve_entry(orig,access_query); + return OK; + } else + ap_log_error(APLOG_MARK_ERRLEVEL,orig->server,"mod_log_sql: insert successful after table creation"); + } + } else { + ap_log_error(APLOG_MARK,ERRLEVEL,orig->server,"mod_log_sql: access table doesn't exist...not allowed to create, giving up, preserving query"); + preserve_entry(orig,access_query); + return OK; + } +*/ -- cgit