summaryrefslogtreecommitdiffstatsabout
diff options
context:
space:
mode:
-rw-r--r--CHANGELOG23
-rw-r--r--Documentation/documentation.lyx1715
-rw-r--r--Makefile4
-rw-r--r--create_tables.sql6
-rw-r--r--mod_log_sql.c324
5 files changed, 1279 insertions, 793 deletions
diff --git a/CHANGELOG b/CHANGELOG
index f18509c..66151e7 100644
--- a/CHANGELOG
+++ b/CHANGELOG
@@ -1,4 +1,4 @@
1$Id: CHANGELOG,v 1.17 2002/11/14 22:52:53 helios Exp $ 1$Id: CHANGELOG,v 1.18 2002/12/10 19:43:21 helios Exp $
2 2
3TODO: 3TODO:
4* Rethink documentation flow and rewrite? 4* Rethink documentation flow and rewrite?
@@ -15,11 +15,28 @@ TODO:
15* socket-based middleman daemon with configurable conns? 15* socket-based middleman daemon with configurable conns?
16* ignore by cookie 16* ignore by cookie
17 17
18
19CHANGES: 18CHANGES:
20 19
211.17: 201.18:
21* Delayed inserts (a MySQL extension) are now available at compile-time.
22 THIS IS UGLY because there seems to be a bug in the way MySQL returns
23 errors on failed INSERT DELAYED queries. See the FAQ.
24* Reworked the Makefile a tad.
25* Added stdlib.h to the includes, some systems need it for atoi(). Added
26 string.h to the includes just to be safe.
27* Reworked table creation (again). Inserts are attempted and tables
28 are made only on ER_NO_SUCH_TABLE. This should solve all the multiple-
29 creation-attempt issues as well as reduce overhead.
30* safe_mysql_query reworked; it now can be counted on to preserve_entry()
31 if the query doesn't succeed. (Before, preserve_entry() was called
32 separately.)
33* Always return SIGPIPE to handler before leaving safe_mysql_query().
34* New directive LogSQLRequestAccept, another filter for excluding things
35 from being logged.
36* Alphabetized the directives in the reference section of the docs.
37* Reorganized the FAQ by section.
22 38
391.17:
23* Renamed the module mod_log_sql to conform to the project goal of 40* Renamed the module mod_log_sql to conform to the project goal of
24 database-inspecificity. 41 database-inspecificity.
25* Added capability of logging Notes field. This is useful for folks using 42* 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
1254\layout Standard 1254\layout Standard
1255 1255
1256If you don't choose to log everything that is available, that's fine. 1256If you don't choose to log everything that is available, that's fine.
1257 The unused columns in your table will have simply contain NULL and do not 1257 Fields in the unused columns in your table will simply contain NULL.
1258 cause issues.
1259\layout Standard 1258\layout Standard
1260 1259
1261Some of the 1260Some of the
@@ -1297,10 +1296,18 @@ LogSQLMachineID
1297 1296
1298\end_inset 1297\end_inset
1299 1298
1300Instructing the module what NOT to log 1299Instructing the module what NOT to log using filtering directives
1301\layout Standard 1300\layout Standard
1302 1301
1303Two 1302One
1303\begin_inset Quotes eld
1304\end_inset
1305
1306accept
1307\begin_inset Quotes erd
1308\end_inset
1309
1310 and two
1304\begin_inset Quotes eld 1311\begin_inset Quotes eld
1305\end_inset 1312\end_inset
1306 1313
@@ -1308,12 +1315,59 @@ ignore
1308\begin_inset Quotes srd 1315\begin_inset Quotes srd
1309\end_inset 1316\end_inset
1310 1317
1311 directives allow you to specify things that the module should not log. 1318 directives allow you to fine-tune what the module should not log.
1312 These are very handy for keeping your database as uncluttered as possible 1319 These are very handy for keeping your database as uncluttered as possible
1313 and keeping your statistics free of unneeded numbers. 1320 and keeping your statistics free of unneeded numbers.
1321 Think of each one as a gatekeeper.
1314\layout Standard 1322\layout Standard
1315 1323
1316You are able to tell the module what to exclude based on 1324
1325\emph on
1326It is important to remember that each of these three directives is purely
1327 optional.
1328 mod_log_sql's default is to log everything.
1329
1330\layout Standard
1331
1332When a request comes in, the contents of
1333\noun on
1334LogSQLRequestAccept
1335\noun default
1336are evaluated first.
1337 This optional,
1338\begin_inset Quotes eld
1339\end_inset
1340
1341blanket
1342\begin_inset Quotes erd
1343\end_inset
1344
1345 directive lets you specify that only certain things are to be accepted
1346 for logging, and everything else discarded.
1347 Because it is evaluated before
1348\noun on
1349LogSQLRequestIgnore
1350\noun default
1351and
1352\noun on
1353LogSQLRemhostIgnore
1354\noun default
1355it can halt logging before those two filtering directives
1356\begin_inset Quotes eld
1357\end_inset
1358
1359get their chance.
1360\begin_inset Quotes erd
1361\end_inset
1362
1363
1364\layout Standard
1365
1366Once a request makes it past
1367\noun on
1368LogSQLRequestAccept
1369\noun default
1370, it still can be excluded based on
1317\noun on 1371\noun on
1318LogSQLRemhostIgnore 1372LogSQLRemhostIgnore
1319\noun default 1373\noun default
@@ -1348,7 +1402,7 @@ You can specify a series of strings after each directive.
1348 Do not use any type of globbing or regular-expression syntax -- each string 1402 Do not use any type of globbing or regular-expression syntax -- each string
1349 is considered a match 1403 is considered a match
1350\emph on 1404\emph on
1351 if it is a substring of the larger request or remote-host, and the comarison 1405 if it is a substring of the larger request or remote-host; the comarison
1352 is case-sensitive. 1406 is case-sensitive.
1353 1407
1354\emph default 1408\emph default
@@ -1441,7 +1495,81 @@ Tree.Gif
1441\begin_inset Quotes erd 1495\begin_inset Quotes erd
1442\end_inset 1496\end_inset
1443 1497
1444 would still get logged. 1498 would still get logged because of case sensitivity.
1499\layout Standard
1500
1501A summary of the decision flow:
1502\layout Enumerate
1503
1504If
1505\noun on
1506LogSQLRequestAccept
1507\noun default
1508 exists and a request does not match anything in that list, it is discarded.
1509\layout Enumerate
1510
1511If a request matches anything in the
1512\noun on
1513LogSQLRequestIgnore
1514\noun default
1515list, it is discarded.
1516\layout Enumerate
1517
1518If a reqiest matches anything in the
1519\noun on
1520LogSQLRemhostIgnore
1521\noun default
1522 list, it is discarded.
1523\layout Enumerate
1524
1525Otherwise the request is logged.
1526\layout Standard
1527
1528This means that you can have a series of directives similar to the following:
1529\layout LyX-Code
1530
1531LogSQLRequestAccept *.html *.gif *.jpg
1532\layout LyX-Code
1533
1534LogSQLRequestIgnore statistics.html bluedot.jpg
1535\layout Standard
1536
1537So the first line instructs the module to
1538\series bold
1539only
1540\series default
1541 log files with html, gif and jpg suffixes; requests for
1542\begin_inset Quotes eld
1543\end_inset
1544
1545formail.cgi
1546\begin_inset Quotes erd
1547\end_inset
1548
1549 and
1550\begin_inset Quotes eld
1551\end_inset
1552
1553shopping-cart.pl
1554\begin_inset Quotes erd
1555\end_inset
1556
1557 will never be considered for logging.
1558 (
1559\begin_inset Quotes eld
1560\end_inset
1561
1562LeftArrow.JPG
1563\begin_inset Quotes erd
1564\end_inset
1565
1566 will also never be considered for logging -- remember, the comparison is
1567
1568\series bold
1569case sensitive
1570\series default
1571.) The second line prunes the list further -- you never want to log requests
1572 for those two objects.
1445\layout Standard 1573\layout Standard
1446 1574
1447Tip: if you want to match all the hosts in your domain such as 1575Tip: if you want to match all the hosts in your domain such as
@@ -1620,83 +1748,6 @@ LogSQLTransferLogFormat
1620 machine and thereby analyze the front-end loadbalancing algorithm. 1748 machine and thereby analyze the front-end loadbalancing algorithm.
1621\layout Subsubsection 1749\layout Subsubsection
1622 1750
1623Using the same database for production and test
1624\layout Standard
1625
1626Although suboptimal, it is not uncommon to use the same backend database
1627 for the
1628\begin_inset Quotes eld
1629\end_inset
1630
1631production
1632\begin_inset Quotes erd
1633\end_inset
1634
1635 webservers as well as the
1636\begin_inset Quotes eld
1637\end_inset
1638
1639test
1640\begin_inset Quotes erd
1641\end_inset
1642
1643 webservers (budgetary constraints, rackspace limits, etc.).
1644 Furthermore, an administrator in this situation may be unable to use
1645\noun on
1646LogSQLRemhostIgnore
1647\noun default
1648to exclude requests from the test servers -- perhaps the generated entries
1649 are genuinely useful for analytical or QA purposes, but their value after
1650 analysis is minimal.
1651\layout Standard
1652
1653It is wasteful and potentially confusing to permit this internal test data
1654 to clutter the database, and a solution to the problem is the proper use
1655 of the
1656\noun on
1657LogSQLMachineID
1658\noun default
1659directive.
1660 Assume a scenario where the production webservers have IDs like
1661\begin_inset Quotes eld
1662\end_inset
1663
1664web01,
1665\begin_inset Quotes erd
1666\end_inset
1667
1668
1669\begin_inset Quotes eld
1670\end_inset
1671
1672web02,
1673\begin_inset Quotes erd
1674\end_inset
1675
1676 and so on -- and the test webservers have IDs like
1677\begin_inset Quotes eld
1678\end_inset
1679
1680test01,
1681\begin_inset Quotes erd
1682\end_inset
1683
1684
1685\begin_inset Quotes eld
1686\end_inset
1687
1688test02,
1689\begin_inset Quotes erd
1690\end_inset
1691
1692 etc.
1693 Because entries in the log database are distinguished by their source machine,
1694 an administrator may purge unneeded test data from the access log as follows:
1695\layout LyX-Code
1696
1697delete from access_log where machine_id like 'test%';
1698\layout Subsubsection
1699
1700 1751
1701\begin_inset LatexCommand \label{secMulTable} 1752\begin_inset LatexCommand \label{secMulTable}
1702 1753
@@ -2399,13 +2450,95 @@ LogSQLTransferLogTable.)
2399</VirtualHost> 2450</VirtualHost>
2400\layout Subsubsection 2451\layout Subsubsection
2401 2452
2453Using the same database for production and test
2454\layout Standard
2455
2456Although suboptimal, it is not uncommon to use the same backend database
2457 for the
2458\begin_inset Quotes eld
2459\end_inset
2460
2461production
2462\begin_inset Quotes erd
2463\end_inset
2464
2465 webservers as well as the
2466\begin_inset Quotes eld
2467\end_inset
2468
2469test
2470\begin_inset Quotes erd
2471\end_inset
2472
2473 webservers (budgetary constraints, rackspace limits, etc.).
2474 Furthermore, an administrator in this situation may be unable to use
2475\noun on
2476LogSQLRemhostIgnore
2477\noun default
2478to exclude requests from the test servers -- perhaps the generated entries
2479 are genuinely useful for analytical or QA purposes, but their value after
2480 analysis is minimal.
2481\layout Standard
2482
2483It is wasteful and potentially confusing to permit this internal test data
2484 to clutter the database, and a solution to the problem is the proper use
2485 of the
2486\noun on
2487LogSQLMachineID
2488\noun default
2489directive.
2490 Assume a scenario where the production webservers have IDs like
2491\begin_inset Quotes eld
2492\end_inset
2493
2494web01,
2495\begin_inset Quotes erd
2496\end_inset
2497
2498
2499\begin_inset Quotes eld
2500\end_inset
2501
2502web02,
2503\begin_inset Quotes erd
2504\end_inset
2505
2506 and so on -- and the test webservers have IDs like
2507\begin_inset Quotes eld
2508\end_inset
2509
2510test01,
2511\begin_inset Quotes erd
2512\end_inset
2513
2514
2515\begin_inset Quotes eld
2516\end_inset
2517
2518test02,
2519\begin_inset Quotes erd
2520\end_inset
2521
2522 etc.
2523 Because entries in the log database are distinguished by their source machine,
2524 an administrator may purge unneeded test data from the access log as follows:
2525\layout LyX-Code
2526
2527delete from access_log where machine_id like 'test%';
2528\layout Subsubsection
2529
2530
2531\begin_inset LatexCommand \label{sub:DelayedIns}
2532
2533\end_inset
2534
2402Optimizing for a busy database 2535Optimizing for a busy database
2403\layout Standard 2536\layout Standard
2404 2537
2405A busy MySQL database will have SELECT statements running concurrently with 2538A busy MySQL database will have SELECT statements running concurrently with
2406 INSERT and UPDATE statements. 2539 INSERT and UPDATE statements.
2407 A long-running SELECT can block INSERTs, therefore will block mod_log_sql. 2540 A long-running SELECT can block INSERTs, therefore will block mod_log_sql.
2408 This can be solved by compiling mod_log_sql for 2541 This may be solved by compiling mod_log_sql for
2409\begin_inset Quotes eld 2542\begin_inset Quotes eld
2410\end_inset 2543\end_inset
2411 2544
@@ -2454,6 +2587,8 @@ There is additional overhead for the server to handle a separate thread
2454 for each table on which you use INSERT DELAYED. 2587 for each table on which you use INSERT DELAYED.
2455\layout Standard 2588\layout Standard
2456 2589
2590
2591\series bold
2457The MySQL documentation concludes, 2592The MySQL documentation concludes,
2458\begin_inset Quotes eld 2593\begin_inset Quotes eld
2459\end_inset 2594\end_inset
@@ -2463,7 +2598,15 @@ This means that you should only use INSERT DELAYED when you are really sure
2463\begin_inset Quotes erd 2598\begin_inset Quotes erd
2464\end_inset 2599\end_inset
2465 2600
2601 Furthermore, the current state of error return from a failed INSERT DELAYED
2602 seems to be in flux, and may behave in unpredictable ways between different
2603 MySQL versions.
2604 See section
2605\begin_inset LatexCommand \ref{sub:DelayedInsFAQ}
2606
2607\end_inset
2466 2608
2609 in the FAQ -- you have been warned.
2467\layout Standard 2610\layout Standard
2468 2611
2469If you are experiencing issues which could be solved by delayed inserts, 2612If you are experiencing issues which could be solved by delayed inserts,
@@ -2499,246 +2642,297 @@ context
2499 listed with each entry informs you of this. 2642 listed with each entry informs you of this.
2500\layout Subsubsection 2643\layout Subsubsection
2501 2644
2502LogSQLDatabase 2645LogSQLCookieLogTable
2503\layout LyX-Code 2646\layout LyX-Code
2504 2647
2505 2648Syntax: LogSQLCookieLogTable table-name
2506\series bold
2507MANDATORY
2508\layout LyX-Code 2649\layout LyX-Code
2509 2650
2510Syntax: LogSQLDatabase database 2651Example: LogSQLCookieLogTable cookie_log
2511\layout LyX-Code 2652\layout LyX-Code
2512 2653
2513Example: LogSQLDatabase loggingdb 2654Default: cookies
2514\layout LyX-Code 2655\layout LyX-Code
2515 2656
2516Context: main server config 2657Context: virtual host
2517\layout Quote 2658\layout Standard
2518 2659
2519Defines the database that is used for logging. 2660Defines which table is used for logging of cookies.
2520 2661 Working in conjunction with
2662\noun on
2663LogSQLWhichCookies
2664\noun default
2665, you can log many of each request's associated cookies to a separate table.
2666 For meaningful data retrieval the cookie table is keyed to the access table
2667 by the unique request ID supplied by the standard Apache module mod_unique_id.
2668\layout Standard
2669
2670Note that you must create the table (see create-tables.sql, included in the
2671 package), or
2672\noun on
2673LogSQLCreateTables
2674\noun default
2675 must be set to
2521\begin_inset Quotes eld 2676\begin_inset Quotes eld
2522\end_inset 2677\end_inset
2523 2678
2524database 2679on
2525\begin_inset Quotes erd 2680\begin_inset Quotes erd
2526\end_inset 2681\end_inset
2527 2682
2528 must be a valid db on the MySQL host defined in 2683.
2684\layout Subsubsection
2685
2686LogSQLCreateTables
2687\layout LyX-Code
2688
2689Syntax: LogSQLCreateTables flag
2690\layout LyX-Code
2691
2692Example: LogSQLCreateTables On
2693\layout LyX-Code
2694
2695Default: Off
2696\layout LyX-Code
2697
2698Context: main server config
2699\layout Standard
2700
2701mod_log_sql has the ability to create its tables on-the-fly.
2702 The advantage to this is convenience: you don't have to execute any SQL
2703 by hand to prepare the table.
2704 This is especially helpful for people with lots of virtual hosts (who should
2705 also see the
2706\noun on
2707LogSQLMassVirtualHosting
2708\noun default
2709 directive).
2710\layout Standard
2711
2712There is a slight disadvantage: if you wish to activate this feature, then
2713 the userid specified in
2529\noun on 2714\noun on
2530LogSQLLoginInfo 2715LogSQLLoginInfo
2531\noun default 2716\noun default
2717 must have CREATE privileges on the database.
2718 In an absolutely paranoid, locked-down situation you may only want to grant
2719 your mod_log_sql user INSERT privileges on the database; in that situation
2720 you are unable to take advantage of
2721\noun on
2722LogSQLCreateTables
2723\noun default
2532. 2724.
2533 2725 But most people -- even the very security-conscious -- will find that granting
2534\layout Quote 2726 CREATE on the logging database is reasonable.
2727\layout Standard
2535 2728
2536This is defined only once in the httpd.conf file. 2729This is defined only once in the httpd.conf file.
2537\layout Subsubsection 2730\layout Subsubsection
2538 2731
2539LogSQLLoginInfo 2732LogSQLDatabase
2540\layout LyX-Code 2733\layout LyX-Code
2541 2734
2542 2735
2543\series bold 2736\series bold
2544MANDATORY 2737MANDATORY
2545\series default
2546
2547\layout LyX-Code 2738\layout LyX-Code
2548 2739
2549Syntax: LogSQLLoginInfo host user password 2740Syntax: LogSQLDatabase database
2550\layout LyX-Code 2741\layout LyX-Code
2551 2742
2552Example: LogSQLLoginInfo foobar.baz.com logwriter passw0rd 2743Example: LogSQLDatabase loggingdb
2553\layout LyX-Code 2744\layout LyX-Code
2554 2745
2555Context: main server config 2746Context: main server config
2556\layout Quote 2747\layout Standard
2557
2558Defines the general parameters of the MySQL host to which you will be logging.
2559
2560\begin_inset Quotes eld
2561\end_inset
2562
2563host
2564\begin_inset Quotes erd
2565\end_inset
2566 2748
2567 is the hostname or IP address of the MySQL machine. 2749Defines the database that is used for logging.
2568 2750
2569\begin_inset Quotes eld 2751\begin_inset Quotes eld
2570\end_inset 2752\end_inset
2571 2753
2572user 2754database
2573\begin_inset Quotes erd 2755\begin_inset Quotes erd
2574\end_inset 2756\end_inset
2575 2757
2576 is the MySQL userid (not a Unix userid!) with INSERT privileges on the 2758 must be a valid db on the MySQL host defined in
2577 table defined in
2578\noun on 2759\noun on
2579LogSQLTransferLogTable 2760LogSQLLoginInfo
2580\noun default 2761\noun default
2581. 2762.
2582 2763
2583\begin_inset Quotes eld 2764\layout Standard
2584\end_inset
2585
2586password
2587\begin_inset Quotes erd
2588\end_inset
2589
2590 is that user's password.
2591
2592\layout Quote
2593 2765
2594This is defined only once in the httpd.conf file. 2766This is defined only once in the httpd.conf file.
2595\layout Subsubsection 2767\layout Subsubsection
2596 2768
2597LogSQLTCPPort 2769LogSQLForcePreserve
2598\layout LyX-Code 2770\layout LyX-Code
2599 2771
2600Syntax: LogSQLTCPPort portnumber 2772Syntax: LogSQLForcePreserve Flag
2601\layout LyX-Code 2773\layout LyX-Code
2602 2774
2603Example: LogSQLTCPPort 3309 2775Example: LogSQLPreserveFile on
2604\layout LyX-Code 2776\layout LyX-Code
2605 2777
2606Default: 3306 2778Default: off
2607\layout LyX-Code 2779\layout LyX-Code
2608 2780
2609Context: main server config 2781Context: main server config
2610\layout Quote 2782\layout Standard
2611 2783
2612Your database may listen on a different port than the default. 2784You may need to perform debugging on your database and specifically want
2613 If so, use this directive to instruct the module which port to use. 2785 mod_log_sql to make no attempts to log to it.
2614 This directive only applies if the database is on a different machine connected 2786 This directive instructs the module to send all its log entries directly
2615 via TCP/IP. 2787 to the preserve file and to make no database INSERT attempts.
2616\layout Quote 2788\layout Standard
2789
2790This is presumably a directive for temporary use only; it could be dangerous
2791 if you set it and forget it, as all your entries will simply pile up in
2792 the preserve file.
2793\layout Standard
2617 2794
2618This is defined only once in the httpd.conf file. 2795This is defined only once in the httpd.conf file.
2619\layout Subsubsection 2796\layout Subsubsection
2620 2797
2621LogSQLSocketFile 2798LogSQLHeadersInLogTable
2622\layout LyX-Code 2799\layout LyX-Code
2623 2800
2624Syntax: LogSQLSocketFile filename 2801Syntax: LogSQLHeadersInLogTable table-name
2625\layout LyX-Code 2802\layout LyX-Code
2626 2803
2627Example: LogSQLSocketFile /tmp/mysql.sock 2804Example: LogSQLHeadersInLogTable headers
2628\layout LyX-Code 2805\layout LyX-Code
2629 2806
2630Default: /var/lib/mysql/mysql.sock 2807Default: headers_in
2631\layout LyX-Code 2808\layout LyX-Code
2632 2809
2633Context: main server config 2810Context: virtual host
2634\layout Quote 2811\layout Standard
2635 2812
2636At Apache runtime you can specify the MySQL socket file to use. 2813Defines which table is used for logging of inbound headers.
2637 Set this once in your main server config to override the default value. 2814 Working in conjunction with
2638 This value is irrelevant if your database resides on a separate machine. 2815\noun on
2639\layout Quote 2816LogSQLWhichHeadersIn
2817\noun default
2818, you can log many of each request's associated headers to a separate table.
2819 For meaningful data retrieval the headers table is keyed to the access
2820 table by the unique request ID supplied by the standard Apache module mod_uniqu
2821e_id.
2822\layout Standard
2640 2823
2641mod_log_sql will automatically employ the socket for db communications if 2824Note that you must create the table (see create-tables.sql, included in the
2642 the database resides on the local host. 2825 package), or
2643 If the db resides on a separate host the module will automatically use 2826\noun on
2644 TCP/IP. 2827LogSQLCreateTables
2645 This is a function of the MySQL API and is not user-configurable. 2828\noun default
2646\layout Quote 2829 must be set to
2830\begin_inset Quotes eld
2831\end_inset
2647 2832
2648This is defined only once in the httpd.conf file. 2833on
2834\begin_inset Quotes erd
2835\end_inset
2836
2837.
2649\layout Subsubsection 2838\layout Subsubsection
2650 2839
2651LogSQLPreserveFile 2840LogSQLHeadersOutLogTable
2652\layout LyX-Code 2841\layout LyX-Code
2653 2842
2654Syntax: LogSQLPreserveFile filename 2843Syntax: LogSQLHeadersOutLogTable table-name
2655\layout LyX-Code 2844\layout LyX-Code
2656 2845
2657Example: LogSQLPreserveFile offline-preserve 2846Example: LogSQLHeadersOutLogTable headers
2658\layout LyX-Code 2847\layout LyX-Code
2659 2848
2660Default: /tmp/sql-preserve 2849Default: headers_out
2661\layout LyX-Code 2850\layout LyX-Code
2662 2851
2663Context: virtual host 2852Context: virtual host
2664\layout Quote 2853\layout Standard
2665
2666mod_log_sql writes queries to this local preserve file in the event that
2667 it cannot reach the database, and thus ensures that your high-availability
2668 web frontend does not lose logs during a temporary database outage.
2669 This could happen for a number of reasons: the database goes offline, the
2670 network breaks, etc.
2671 You will not lose entries since the module has this backup.
2672 The file consists of a series of SQL statements that can be imported into
2673 your database at your convenience; furthermore, because the SQL queries
2674 contain the access timestamps you do not need to worry about out-of-order
2675 data after the import, which is done in a simple manner:
2676\layout LyX-Code
2677
2678# mysql -uadminuser -p mydbname < /tmp/sql-preserve
2679\layout Quote
2680 2854
2681If you do not define 2855Defines which table is used for logging of outbound headers.
2682\noun on 2856 Working in conjunction with
2683LogSQLPreserveFile
2684\noun default
2685 then all virtual servers will log to the same default preserve file (/tmp/sql-p
2686reserve).
2687 You can redefine this on a virtual-host basis in order to segregate your
2688 preserve files if you desire.
2689 Note that segregation is not usually necessary, as the SQL statements that
2690 are written to the preserve file already distinguish between different
2691 virtual hosts if you include the 'v' character in your
2692\noun on 2857\noun on
2693LogSQLTransferLogFormat 2858LogSQLWhichHeadersOut
2694\noun default 2859\noun default
2695 directive. 2860, you can log many of each request's associated headers to a separate table.
2696 It is only necessary to segregate preserve-files by virualhost if you also 2861 For meaningful data retrieval the headers table is keyed to the access
2697 segregate access logs by virtualhost. 2862 table by the unique request ID supplied by the standard Apache module mod_uniqu
2698\layout Quote 2863e_id.
2864\layout Standard
2699 2865
2700The module will log to Apache's 2866Note that you must create the table (see create-tables.sql, included in the
2867 package), or
2701\noun on 2868\noun on
2702ErrorLog 2869LogSQLCreateTables
2703\noun default 2870\noun default
2704 when it notices a database outage, and upon database return. 2871 must be set to
2705 You will therefore know when the preserve file is being used, although 2872\begin_inset Quotes eld
2706 it is your responsibility to import the file. 2873\end_inset
2707\layout Quote
2708 2874
2709The file does not need to be created in advance. 2875on
2710 It is safe to remove or rename the file without interrupting Apache, as 2876\begin_inset Quotes erd
2711 the module closes the filehandle immediately after completing the write. 2877\end_inset
2712 The file is created with the user & group ID of the running Apache process 2878
2713 (e.g. 2879.
2714 'nobody' on many Linux distributions).
2715\layout Subsubsection 2880\layout Subsubsection
2716 2881
2717LogSQLForcePreserve 2882LogSQLLoginInfo
2718\layout LyX-Code 2883\layout LyX-Code
2719 2884
2720Syntax: LogSQLForcePreserve Flag 2885
2886\series bold
2887MANDATORY
2888\series default
2889
2721\layout LyX-Code 2890\layout LyX-Code
2722 2891
2723Example: LogSQLPreserveFile on 2892Syntax: LogSQLLoginInfo host user password
2724\layout LyX-Code 2893\layout LyX-Code
2725 2894
2726Default: off 2895Example: LogSQLLoginInfo foobar.baz.com logwriter passw0rd
2727\layout LyX-Code 2896\layout LyX-Code
2728 2897
2729Context: main server config 2898Context: main server config
2730\layout Quote 2899\layout Standard
2731 2900
2732You may need to perform debugging on your database and specifically want 2901Defines the general parameters of the MySQL host to which you will be logging.
2733 mod_log_sql to make no attempts to log to it. 2902
2734 This directive instructs the module to send all its log entries directly 2903\begin_inset Quotes eld
2735 to the preserve file and to make no database INSERT attempts. 2904\end_inset
2736\layout Quote
2737 2905
2738This is presumably a directive for temporary use only; it could be dangerous 2906host
2739 if you set it and forget it, as all your entries will simply pile up in 2907\begin_inset Quotes erd
2740 the preserve file. 2908\end_inset
2741\layout Quote 2909
2910 is the hostname or IP address of the MySQL machine.
2911
2912\begin_inset Quotes eld
2913\end_inset
2914
2915user
2916\begin_inset Quotes erd
2917\end_inset
2918
2919 is the MySQL userid (not a Unix userid!) with INSERT privileges on the
2920 table defined in
2921\noun on
2922LogSQLTransferLogTable
2923\noun default
2924.
2925
2926\begin_inset Quotes eld
2927\end_inset
2928
2929password
2930\begin_inset Quotes erd
2931\end_inset
2932
2933 is that user's password.
2934
2935\layout Standard
2742 2936
2743This is defined only once in the httpd.conf file. 2937This is defined only once in the httpd.conf file.
2744\layout Subsubsection 2938\layout Subsubsection
@@ -2753,7 +2947,7 @@ Example: LogSQLMachineID web01
2753\layout LyX-Code 2947\layout LyX-Code
2754 2948
2755Context: main server config 2949Context: main server config
2756\layout Quote 2950\layout Standard
2757 2951
2758If you have a farm of webservers then you may wish to know which particular 2952If you have a farm of webservers then you may wish to know which particular
2759 machine made each entry; this is useful for analyzing your loadbalancing 2953 machine made each entry; this is useful for analyzing your loadbalancing
@@ -2792,53 +2986,7 @@ LogSQLMachineID
2792\end_inset 2986\end_inset
2793 2987
2794 etc. 2988 etc.
2795\layout Quote 2989\layout Standard
2796
2797This is defined only once in the httpd.conf file.
2798\layout Subsubsection
2799
2800LogSQLCreateTables
2801\layout LyX-Code
2802
2803Syntax: LogSQLCreateTables flag
2804\layout LyX-Code
2805
2806Example: LogSQLCreateTables On
2807\layout LyX-Code
2808
2809Default: Off
2810\layout LyX-Code
2811
2812Context: main server config
2813\layout Quote
2814
2815mod_log_sql has the ability to create its tables on-the-fly.
2816 The advantage to this is convenience: you don't have to execute any SQL
2817 by hand to prepare the table.
2818 This is especially helpful for people with lots of virtual hosts (who should
2819 also see the
2820\noun on
2821LogSQLMassVirtualHosting
2822\noun default
2823 directive).
2824\layout Quote
2825
2826There is a slight disadvantage: if you wish to activate this feature, then
2827 the userid specified in
2828\noun on
2829LogSQLLoginInfo
2830\noun default
2831 must have CREATE privileges on the database.
2832 In an absolutely paranoid, locked-down situation you may only want to grant
2833 your mod_log_sql user INSERT privileges on the database; in that situation
2834 you are unable to take advantage of
2835\noun on
2836LogSQLCreateTables
2837\noun default
2838.
2839 But most people -- even the very security-conscious -- will find that granting
2840 CREATE on the logging database is reasonable.
2841\layout Quote
2842 2990
2843This is defined only once in the httpd.conf file. 2991This is defined only once in the httpd.conf file.
2844\layout Subsubsection 2992\layout Subsubsection
@@ -2856,7 +3004,7 @@ Default: Off
2856\layout LyX-Code 3004\layout LyX-Code
2857 3005
2858Context: main server config 3006Context: main server config
2859\layout Quote 3007\layout Standard
2860 3008
2861If you administer a site hosting many, many virtual hosts then this option 3009If you administer a site hosting many, many virtual hosts then this option
2862 will appeal to you. 3010 will appeal to you.
@@ -2865,7 +3013,6 @@ If you administer a site hosting many, many virtual hosts then this option
2865LogSQLMassVirtualHosting 3013LogSQLMassVirtualHosting
2866\noun default 3014\noun default
2867 then several things happen: 3015 then several things happen:
2868\begin_deeper
2869\layout Itemize 3016\layout Itemize
2870 3017
2871the on-the-fly table creation feature is activated automatically 3018the 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
2881 Because there is no data shared between virtual servers you can grant your 3028 Because there is no data shared between virtual servers you can grant your
2882 users access to the tables they need; they will be unable to view others' 3029 users access to the tables they need; they will be unable to view others'
2883 data. 3030 data.
2884\end_deeper 3031\layout Standard
2885\layout Quote
2886 3032
2887This is a huge boost in convenience for sites with many virtual servers. 3033This is a huge boost in convenience for sites with many virtual servers.
2888 Activating 3034 Activating
@@ -2891,64 +3037,239 @@ LogSQLMassVirtualHosting
2891\noun default 3037\noun default
2892 obviates the need to create every virtual server's table and provides more 3038 obviates the need to create every virtual server's table and provides more
2893 granular security possibilities. 3039 granular security possibilities.
2894\layout Quote 3040\layout Standard
3041
3042You are advised to investigate the use of Apache's
3043\noun on
3044UseCanonicalName On
3045\noun default
3046directive with this directive in order to ensure that each virtual host
3047 maps to one table namespace.
3048\layout Standard
2895 3049
2896This is defined only once in the httpd.conf file. 3050This is defined only once in the httpd.conf file.
3051
2897\layout Subsubsection 3052\layout Subsubsection
2898 3053
2899LogSQLTransferLogTable 3054LogSQLNotesLogTable
3055\layout LyX-Code
3056
3057Syntax: LogSQLNotesLogTable table-name
2900\layout LyX-Code 3058\layout LyX-Code
2901 3059
3060Example: LogSQLNotesLogTable notes_log
3061\layout LyX-Code
2902 3062
2903\series bold 3063Default: notes
2904MANDATORY (unless 3064\layout LyX-Code
3065
3066Context: virtual host
3067\layout Standard
3068
3069Defines which table is used for logging of notes.
3070 Working in conjunction with
2905\noun on 3071\noun on
2906LogSQLMassVirtualHosting 3072LogSQLWhichNotes
2907\noun default 3073\noun default
2908 is 3074, you can log many of each request's associated notes to a separate table.
2909\begin_inset Quotes eld 3075 For meaningful data retrieval the notes table is keyed to the access table
2910\end_inset 3076 by the unique request ID supplied by the standard Apache module mod_unique_id.
3077\layout Standard
2911 3078
2912on 3079Note that you must create the table (see create-tables.sql, included in the
2913\begin_inset Quotes erd 3080 package), or
2914\end_inset 3081\noun on
3082LogSQLCreateTables
3083\noun default
3084 must be set to ``on''.
3085
3086\layout Subsubsection
2915 3087
2916) 3088LogSQLPreserveFile
2917\layout LyX-Code 3089\layout LyX-Code
2918 3090
2919Syntax: LogSQLTransferLogTable table-name 3091Syntax: LogSQLPreserveFile filename
2920\layout LyX-Code 3092\layout LyX-Code
2921 3093
2922Example: LogSQLTransferLogTable access_log_table 3094Example: LogSQLPreserveFile offline-preserve
3095\layout LyX-Code
3096
3097Default: /tmp/sql-preserve
2923\layout LyX-Code 3098\layout LyX-Code
2924 3099
2925Context: virtual host 3100Context: virtual host
2926\layout Quote 3101\layout Standard
2927 3102
2928Defines which table is used for logging of Apache's transfers; this is analogous 3103mod_log_sql writes queries to this local preserve file in the event that
2929 to Apache's TransferLog directive. 3104 it cannot reach the database, and thus ensures that your high-availability
2930 table-name must be a valid table within the database defined in 3105 web frontend does not lose logs during a temporary database outage.
3106 This could happen for a number of reasons: the database goes offline, the
3107 network breaks, etc.
3108 You will not lose entries since the module has this backup.
3109 The file consists of a series of SQL statements that can be imported into
3110 your database at your convenience; furthermore, because the SQL queries
3111 contain the access timestamps you do not need to worry about out-of-order
3112 data after the import, which is done in a simple manner:
3113\layout LyX-Code
3114
3115# mysql -uadminuser -p mydbname < /tmp/sql-preserve
3116\layout Standard
3117
3118If you do not define
2931\noun on 3119\noun on
2932LogSQLDatabase 3120LogSQLPreserveFile
2933\noun default 3121\noun default
2934. 3122 then all virtual servers will log to the same default preserve file (/tmp/sql-p
2935\layout Quote 3123reserve).
3124 You can redefine this on a virtual-host basis in order to segregate your
3125 preserve files if you desire.
3126 Note that segregation is not usually necessary, as the SQL statements that
3127 are written to the preserve file already distinguish between different
3128 virtual hosts if you include the 'v' character in your
3129\noun on
3130LogSQLTransferLogFormat
3131\noun default
3132 directive.
3133 It is only necessary to segregate preserve-files by virualhost if you also
3134 segregate access logs by virtualhost.
3135\layout Standard
2936 3136
2937This directive is not necessary if you declare 3137The module will log to Apache's
2938\noun on 3138\noun on
2939LogSQLMassVirtualHosting On 3139ErrorLog
2940\noun default 3140\noun default
2941, since that directive activates dynamically-named tables. 3141 when it notices a database outage, and upon database return.
2942 If you attempt to use 3142 You will therefore know when the preserve file is being used, although
3143 it is your responsibility to import the file.
3144\layout Standard
3145
3146The file does not need to be created in advance.
3147 It is safe to remove or rename the file without interrupting Apache, as
3148 the module closes the filehandle immediately after completing the write.
3149 The file is created with the user & group ID of the running Apache process
3150 (e.g.
3151 'nobody' on many Linux distributions).
3152\layout Subsubsection
3153
3154LogSQLRemhostIgnore
3155\layout LyX-Code
3156
3157Syntax: LogSQLRemhostIgnore host1 host2 host3 ...
3158 hostN
3159\layout LyX-Code
3160
3161Example: LogSQLRemhostIgnore localnet.com
3162\layout LyX-Code
3163
3164Context: virtual host
3165\layout Standard
3166
3167Lists a series of strings that, if present in the REMOTE_HOST, will cause
3168 that request to
3169\series bold
3170not
3171\series default
3172 be logged.
3173 This directive is useful for cutting down on log clutter when you are certain
3174 that you want to ignore requests from certain hosts, such as your own internal
3175 network machines.
3176 See section
3177\begin_inset LatexCommand \ref{sub:Ignore}
3178
3179\end_inset
3180
3181 for some tips for using this directive.
3182\layout Standard
3183
3184Each string is separated by a space, and no regular expressions or globbing
3185 are allowed.
3186 Each string is evaluated as a substring of the REMOTE_HOST using strstr().
3187 The comparison is case sensitive.
3188\layout Subsubsection
3189
3190LogSQLRequestAccept
3191\layout LyX-Code
3192
3193Syntax: LogSQLRequestAccept req1 req2 req3 ...
3194 reqN
3195\layout LyX-Code
3196
3197Example: LogSQLRequestAccept .html .php .jpg
3198\layout LyX-Code
3199
3200Default: if not specified, all requests are
3201\begin_inset Quotes eld
3202\end_inset
3203
3204accepted
3205\begin_inset Quotes erd
3206\end_inset
3207
3208
3209\layout LyX-Code
3210
3211Context: virtual host
3212\layout Standard
3213
3214Lists a series of strings that, if present in the URI, will permit that
3215 request to be
3216\series bold
3217
3218\series default
3219considered for logging (depending on additional filtering by the
3220\begin_inset Quotes eld
3221\end_inset
3222
3223ignore
3224\begin_inset Quotes erd
3225\end_inset
3226
3227 directives).
3228 Any request that fails to match one of the
2943\noun on 3229\noun on
2944LogSQLTransferLogTable 3230LogSQLRequestAccept
2945\noun default 3231\noun default
2946 at the same time a warning will be logged and it will be ignored, since 3232entries will be discarded.
3233\layout Standard
3234
3235This directive is useful for cutting down on log clutter when you are certain
3236 that you only want to log certain kinds of requests, and just blanket-ignore
3237 everything else.
3238 See section
3239\begin_inset LatexCommand \ref{sub:Ignore}
3240
3241\end_inset
3242
3243 for some tips for using this directive.
3244\layout Standard
3245
3246Each string is separated by a space, and no regular expressions or globbing
3247 are allowed.
3248 Each string is evaluated as a substring of the URI using strstr().
3249 The comparison is case sensitive.
3250\layout Standard
3251
3252This directive is completely optional.
3253 It is more general than
3254\noun on
3255LogSQLRequestIgnore
3256\noun default
3257and
3258\noun on
2947 3259
3260\noun default
3261is evaluated before
2948\noun on 3262\noun on
2949LogSQLMassVirtualHosting 3263 LogSQLRequestIgnore
2950\noun default 3264\noun default
2951 takes priority. 3265.
3266 If this directive is not used,
3267\series bold
3268all
3269\series default
3270 requests are accepted and passed on to the other filtering directives.
3271 Therefore, only use this directive if you have a specific reason to do
3272 so.
2952\layout Subsubsection 3273\layout Subsubsection
2953 3274
2954LogSQLRequestIgnore 3275LogSQLRequestIgnore
@@ -2962,7 +3283,7 @@ Example: LogSQLRequestIgnore root.exe cmd.exe default.ida favicon.ico
2962\layout LyX-Code 3283\layout LyX-Code
2963 3284
2964Context: virtual host 3285Context: virtual host
2965\layout Quote 3286\layout Standard
2966 3287
2967Lists a series of strings that, if present in the URI, will cause that request 3288Lists a series of strings that, if present in the URI, will cause that request
2968 to 3289 to
@@ -2982,7 +3303,7 @@ logged.
2982\end_inset 3303\end_inset
2983 3304
2984 for some tips for using this directive. 3305 for some tips for using this directive.
2985\layout Quote 3306\layout Standard
2986 3307
2987Each string is separated by a space, and no regular expressions or globbing 3308Each string is separated by a space, and no regular expressions or globbing
2988 are allowed. 3309 are allowed.
@@ -2990,40 +3311,58 @@ Each string is separated by a space, and no regular expressions or globbing
2990 The comparison is case sensitive. 3311 The comparison is case sensitive.
2991\layout Subsubsection 3312\layout Subsubsection
2992 3313
2993LogSQLRemhostIgnore 3314LogSQLSocketFile
2994\layout LyX-Code 3315\layout LyX-Code
2995 3316
2996Syntax: LogSQLRemhostIgnore host1 host2 host3 ... 3317Syntax: LogSQLSocketFile filename
2997 hostN
2998\layout LyX-Code 3318\layout LyX-Code
2999 3319
3000Example: LogSQLRemhostIgnore localnet.com 3320Example: LogSQLSocketFile /tmp/mysql.sock
3001\layout LyX-Code 3321\layout LyX-Code
3002 3322
3003Context: virtual host 3323Default: /var/lib/mysql/mysql.sock
3004\layout Quote 3324\layout LyX-Code
3005 3325
3006Lists a series of strings that, if present in the REMOTE_HOST, will cause 3326Context: main server config
3007 that request to 3327\layout Standard
3008\series bold
3009NOT
3010\series default
3011 be logged.
3012 This directive is useful for cutting down on log clutter when you are certain
3013 that you want to ignore requests from certain hosts, such as your own internal
3014 network machines.
3015 See section
3016\begin_inset LatexCommand \ref{sub:Ignore}
3017 3328
3018\end_inset 3329At Apache runtime you can specify the MySQL socket file to use.
3330 Set this once in your main server config to override the default value.
3331 This value is irrelevant if your database resides on a separate machine.
3332\layout Standard
3019 3333
3020 for some tips for using this directive. 3334mod_log_sql will automatically employ the socket for db communications if
3021\layout Quote 3335 the database resides on the local host.
3336 If the db resides on a separate host the module will automatically use
3337 TCP/IP.
3338 This is a function of the MySQL API and is not user-configurable.
3339\layout Standard
3022 3340
3023Each string is separated by a space, and no regular expressions or globbing 3341This is defined only once in the httpd.conf file.
3024 are allowed. 3342\layout Subsubsection
3025 Each string is evaluated as a substring of the REMOTE_HOST using strstr(). 3343
3026 The comparison is case sensitive. 3344LogSQLTCPPort
3345\layout LyX-Code
3346
3347Syntax: LogSQLTCPPort portnumber
3348\layout LyX-Code
3349
3350Example: LogSQLTCPPort 3309
3351\layout LyX-Code
3352
3353Default: 3306
3354\layout LyX-Code
3355
3356Context: main server config
3357\layout Standard
3358
3359Your database may listen on a different port than the default.
3360 If so, use this directive to instruct the module which port to use.
3361 This directive only applies if the database is on a different machine connected
3362 via TCP/IP.
3363\layout Standard
3364
3365This is defined only once in the httpd.conf file.
3027\layout Subsubsection 3366\layout Subsubsection
3028 3367
3029 3368
@@ -3044,7 +3383,7 @@ Default: AbHhmRSsTUuv
3044\layout LyX-Code 3383\layout LyX-Code
3045 3384
3046Context: virtual host 3385Context: virtual host
3047\layout Quote 3386\layout Standard
3048 3387
3049Each character in the format-string defines an attribute of the request 3388Each character in the format-string defines an attribute of the request
3050 that you wish to log. 3389 that you wish to log.
@@ -4193,7 +4532,7 @@ for this to take effect.
4193LogSQLMachineID 4532LogSQLMachineID
4194\noun default 4533\noun default
4195 for this to take effect. 4534 for this to take effect.
4196\layout Quote 4535\layout Standard
4197 4536
4198If you have compiled mod_log_sql with SSL logging capability, you also can 4537If you have compiled mod_log_sql with SSL logging capability, you also can
4199 use these: 4538 use these:
@@ -4390,6 +4729,61 @@ smallint unsigned
4390 4729
4391\layout Subsubsection 4730\layout Subsubsection
4392 4731
4732LogSQLTransferLogTable
4733\layout LyX-Code
4734
4735
4736\series bold
4737MANDATORY (unless
4738\noun on
4739LogSQLMassVirtualHosting
4740\noun default
4741 is
4742\begin_inset Quotes eld
4743\end_inset
4744
4745on
4746\begin_inset Quotes erd
4747\end_inset
4748
4749)
4750\layout LyX-Code
4751
4752Syntax: LogSQLTransferLogTable table-name
4753\layout LyX-Code
4754
4755Example: LogSQLTransferLogTable access_log_table
4756\layout LyX-Code
4757
4758Context: virtual host
4759\layout Standard
4760
4761Defines which table is used for logging of Apache's transfers; this is analogous
4762 to Apache's TransferLog directive.
4763 table-name must be a valid table within the database defined in
4764\noun on
4765LogSQLDatabase
4766\noun default
4767.
4768\layout Standard
4769
4770This directive is not necessary if you declare
4771\noun on
4772LogSQLMassVirtualHosting On
4773\noun default
4774, since that directive activates dynamically-named tables.
4775 If you attempt to use
4776\noun on
4777LogSQLTransferLogTable
4778\noun default
4779 at the same time a warning will be logged and it will be ignored, since
4780
4781\noun on
4782LogSQLMassVirtualHosting
4783\noun default
4784 takes priority.
4785\layout Subsubsection
4786
4393LogSQLWhichCookie 4787LogSQLWhichCookie
4394\layout LyX-Code 4788\layout LyX-Code
4395 4789
@@ -4403,12 +4797,12 @@ Default: None
4403\layout LyX-Code 4797\layout LyX-Code
4404 4798
4405Context: virtual host 4799Context: virtual host
4406\layout Quote 4800\layout Standard
4407 4801
4408In HTTP, cookies have names to distinguish them from each other. 4802In HTTP, cookies have names to distinguish them from each other.
4409 Using mod_usertrack, for example, you can give your user-tracking cookies 4803 Using mod_usertrack, for example, you can give your user-tracking cookies
4410 a name with the CookieName directive. 4804 a name with the CookieName directive.
4411\layout Quote 4805\layout Standard
4412 4806
4413You must include a 'c' character in 4807You must include a 'c' character in
4414\noun on 4808\noun on
@@ -4421,7 +4815,7 @@ LogSQLWhichCookie
4421 tells mod_log_sql which cookie to log. 4815 tells mod_log_sql which cookie to log.
4422 This is necessary because you will usually be setting and receiving more 4816 This is necessary because you will usually be setting and receiving more
4423 than one cookie from a client; this cookie designates which one to log. 4817 than one cookie from a client; this cookie designates which one to log.
4424\layout Quote 4818\layout Standard
4425 4819
4426Note: although this was intended for people who are using mod_usertrack 4820Note: although this was intended for people who are using mod_usertrack
4427 to set user-tracking cookies, you aren't restricted in any way. 4821 to set user-tracking cookies, you aren't restricted in any way.
@@ -4444,7 +4838,7 @@ Default: None
4444\layout LyX-Code 4838\layout LyX-Code
4445 4839
4446Context: virtual host 4840Context: virtual host
4447\layout Quote 4841\layout Standard
4448 4842
4449Defines the list of cookies you would like logged. 4843Defines the list of cookies you would like logged.
4450 This works in conjunction with 4844 This works in conjunction with
@@ -4460,7 +4854,7 @@ LogSQLTransferLogFormat
4460 string. 4854 string.
4461 The feature is activated simply by including this directive, upon which 4855 The feature is activated simply by including this directive, upon which
4462 you will begin populating the separate cookie table with data. 4856 you will begin populating the separate cookie table with data.
4463\layout Quote 4857\layout Standard
4464 4858
4465Note that you must have already created the table (see create-tables.sql, 4859Note that you must have already created the table (see create-tables.sql,
4466 included in the package), or 4860 included in the package), or
@@ -4492,7 +4886,7 @@ Default: None
4492\layout LyX-Code 4886\layout LyX-Code
4493 4887
4494Context: virtual host 4888Context: virtual host
4495\layout Quote 4889\layout Standard
4496 4890
4497Defines the list of inbound headers you would like logged. 4891Defines the list of inbound headers you would like logged.
4498 This works in conjunction with 4892 This works in conjunction with
@@ -4508,7 +4902,7 @@ LogSQLTransferLogFormat
4508 string. 4902 string.
4509 The feature is activated simply by including this directive, upon which 4903 The feature is activated simply by including this directive, upon which
4510 you will begin populating the separate inbound-headers table with data. 4904 you will begin populating the separate inbound-headers table with data.
4511\layout Quote 4905\layout Standard
4512 4906
4513Note that you must have already created the table (see create-tables.sql, 4907Note that you must have already created the table (see create-tables.sql,
4514 included in the package), or 4908 included in the package), or
@@ -4540,7 +4934,7 @@ Default: None
4540\layout LyX-Code 4934\layout LyX-Code
4541 4935
4542Context: virtual host 4936Context: virtual host
4543\layout Quote 4937\layout Standard
4544 4938
4545Defines the list of outbound headers you would like logged. 4939Defines the list of outbound headers you would like logged.
4546 This works in conjunction with 4940 This works in conjunction with
@@ -4556,7 +4950,7 @@ LogSQLTransferLogFormat
4556 string. 4950 string.
4557 The feature is activated simply by including this directive, upon which 4951 The feature is activated simply by including this directive, upon which
4558 you will begin populating the separate outbound-headers table with data. 4952 you will begin populating the separate outbound-headers table with data.
4559\layout Quote 4953\layout Standard
4560 4954
4561Note that you must have already created the table (see create-tables.sql, 4955Note that you must have already created the table (see create-tables.sql,
4562 included in the package), or 4956 included in the package), or
@@ -4588,7 +4982,7 @@ Default: None
4588\layout LyX-Code 4982\layout LyX-Code
4589 4983
4590Context: virtual host 4984Context: virtual host
4591\layout Quote 4985\layout Standard
4592 4986
4593Defines the list of notes you would like logged. 4987Defines the list of notes you would like logged.
4594 This works in conjunction with 4988 This works in conjunction with
@@ -4604,7 +4998,7 @@ LogSQLTransferLogFormat
4604 string. 4998 string.
4605 The feature is activated simply by including this directive, upon which 4999 The feature is activated simply by including this directive, upon which
4606 you will begin populating the separate notes table with data. 5000 you will begin populating the separate notes table with data.
4607\layout Quote 5001\layout Standard
4608 5002
4609Note that you must have already created the table (see create-tables.sql, 5003Note that you must have already created the table (see create-tables.sql,
4610 included in the package), or 5004 included in the package), or
@@ -4620,177 +5014,14 @@ on
4620\end_inset 5014\end_inset
4621 5015
4622. 5016.
4623\layout Subsubsection
4624
4625LogSQLCookieLogTable
4626\layout LyX-Code
4627
4628Syntax: LogSQLCookieLogTable table-name
4629\layout LyX-Code
4630
4631Example: LogSQLCookieLogTable cookie_log
4632\layout LyX-Code
4633
4634Default: cookies
4635\layout LyX-Code
4636
4637Context: virtual host
4638\layout Quote
4639
4640Defines which table is used for logging of cookies.
4641 Working in conjunction with
4642\noun on
4643LogSQLWhichCookies
4644\noun default
4645, you can log many of each request's associated cookies to a separate table.
4646 For meaningful data retrieval the cookie table is keyed to the access table
4647 by the unique request ID supplied by the standard Apache module mod_unique_id.
4648\layout Quote
4649
4650Note that you must create the table (see create-tables.sql, included in the
4651 package), or
4652\noun on
4653LogSQLCreateTables
4654\noun default
4655 must be set to
4656\begin_inset Quotes eld
4657\end_inset
4658
4659on
4660\begin_inset Quotes erd
4661\end_inset
4662
4663.
4664\layout Subsubsection
4665
4666LogSQLHeadersInLogTable
4667\layout LyX-Code
4668
4669Syntax: LogSQLHeadersInLogTable table-name
4670\layout LyX-Code
4671
4672Example: LogSQLHeadersInLogTable headers
4673\layout LyX-Code
4674
4675Default: headers_in
4676\layout LyX-Code
4677
4678Context: virtual host
4679\layout Quote
4680
4681Defines which table is used for logging of inbound headers.
4682 Working in conjunction with
4683\noun on
4684LogSQLWhichHeadersIn
4685\noun default
4686, you can log many of each request's associated headers to a separate table.
4687 For meaningful data retrieval the headers table is keyed to the access
4688 table by the unique request ID supplied by the standard Apache module mod_uniqu
4689e_id.
4690\layout Quote
4691
4692Note that you must create the table (see create-tables.sql, included in the
4693 package), or
4694\noun on
4695LogSQLCreateTables
4696\noun default
4697 must be set to
4698\begin_inset Quotes eld
4699\end_inset
4700
4701on
4702\begin_inset Quotes erd
4703\end_inset
4704
4705.
4706\layout Subsubsection
4707
4708LogSQLHeadersOutLogTable
4709\layout LyX-Code
4710
4711Syntax: LogSQLHeadersOutLogTable table-name
4712\layout LyX-Code
4713
4714Example: LogSQLHeadersOutLogTable headers
4715\layout LyX-Code
4716
4717Default: headers_out
4718\layout LyX-Code
4719
4720Context: virtual host
4721\layout Quote
4722
4723Defines which table is used for logging of outbound headers.
4724 Working in conjunction with
4725\noun on
4726LogSQLWhichHeadersOut
4727\noun default
4728, you can log many of each request's associated headers to a separate table.
4729 For meaningful data retrieval the headers table is keyed to the access
4730 table by the unique request ID supplied by the standard Apache module mod_uniqu
4731e_id.
4732\layout Quote
4733
4734Note that you must create the table (see create-tables.sql, included in the
4735 package), or
4736\noun on
4737LogSQLCreateTables
4738\noun default
4739 must be set to
4740\begin_inset Quotes eld
4741\end_inset
4742
4743on
4744\begin_inset Quotes erd
4745\end_inset
4746
4747.
4748\layout Subsubsection
4749
4750LogSQLNotesLogTable
4751\layout LyX-Code
4752
4753Syntax: LogSQLNotesLogTable table-name
4754\layout LyX-Code
4755
4756Example: LogSQLNotesLogTable notes_log
4757\layout LyX-Code
4758
4759Default: notes
4760\layout LyX-Code
4761
4762Context: virtual host
4763\layout Quote
4764
4765Defines which table is used for logging of notes.
4766 Working in conjunction with
4767\noun on
4768LogSQLWhichNotes
4769\noun default
4770, you can log many of each request's associated notes to a separate table.
4771 For meaningful data retrieval the notes table is keyed to the access table
4772 by the unique request ID supplied by the standard Apache module mod_unique_id.
4773\layout Quote
4774
4775Note that you must create the table (see create-tables.sql, included in the
4776 package), or
4777\noun on
4778LogSQLCreateTables
4779\noun default
4780 must be set to
4781\begin_inset Quotes eld
4782\end_inset
4783
4784on
4785\begin_inset Quotes erd
4786\end_inset
4787
4788.
4789\layout Section 5017\layout Section
4790 5018
4791FAQ 5019FAQ
4792\layout Subsection 5020\layout Subsection
4793 5021
5022General module questions
5023\layout Subsubsection
5024
4794 5025
4795\begin_inset LatexCommand \label{sub:why} 5026\begin_inset LatexCommand \label{sub:why}
4796 5027
@@ -5349,7 +5580,7 @@ http://freshmeat.net
5349As you can see, there are myriad possibilities that can be constructed with 5580As you can see, there are myriad possibilities that can be constructed with
5350 the wonderful SQL SELECT statement. 5581 the wonderful SQL SELECT statement.
5351 Logging to an SQL database can be really quite useful! 5582 Logging to an SQL database can be really quite useful!
5352\layout Subsection 5583\layout Subsubsection
5353 5584
5354Why use MySQL? Are there alternatives? 5585Why use MySQL? Are there alternatives?
5355\layout Standard 5586\layout Standard
@@ -5372,7 +5603,7 @@ That being said, there are alternatives.
5372\end_inset 5603\end_inset
5373 5604
5374. 5605.
5375\layout Subsection 5606\layout Subsubsection
5376 5607
5377Is this code production-ready? 5608Is this code production-ready?
5378\layout Standard 5609\layout Standard
@@ -5381,12 +5612,121 @@ By all accounts it is.
5381 It is known to work without a problem on many-thousands-of-hits-per-day 5612 It is known to work without a problem on many-thousands-of-hits-per-day
5382 webservers. 5613 webservers.
5383 Does that mean it is 100% bug free? Well, no software is. 5614 Does that mean it is 100% bug free? Well, no software is.
5384 But it is well-tested and /believed/ to be fully compatible with production 5615 But it is well-tested and believed to be fully compatible with production
5385 environments. 5616 environments.
5386 (The usual disclaimers apply. 5617 (The usual disclaimers apply.
5387 This software is provided without warranty of any kind.) 5618 This software is provided without warranty of any kind.)
5619\layout Subsubsection
5620
5621Who's using mod_log_sql?
5622\layout Standard
5623
5624Good question! It would be great to find out! If you are a production-level
5625 mod_log_sql user, please contact
5626\begin_inset LatexCommand \url[the maintainer, Chris Powell]{(chris@grubbybaby.com)}
5627
5628\end_inset
5629
5630 so that you can be mentioned here.
5631\layout Subsubsection
5632
5633Why doesn't the module also replace the Apache ErrorLog?
5634\layout Standard
5635
5636There are circumstances when that would be quite unwise -- for example,
5637 if Apache could not reach the MySQL server for some reason and needed to
5638 log that fact.
5639 Without a text-based error log you'd never know anything was wrong, because
5640 Apache would be trying to log a database connection error to the database...
5641 you get the point.
5642\layout Standard
5643
5644Error logs are usually not very high-traffic and are really best left as
5645 text files on a web server machine.
5646\layout Subsubsection
5647
5648Does mod_log_sql work with Apache 2.x?
5649\layout Standard
5650
5651As of this writing, no.
5652 The Apache Group significantly altered the module API with the release
5653 of Apache 2.0.
5654 All modules written for 1.3, including mod_log_sql, will not work with 2.0.
5655\layout Standard
5656
5657mod_log_sql will eventually be ported to Apache 2.x, but not immediately.
5658 It is going to take some time, and there are other features that have higher
5659 priority.
5660 Please sign up for the announcements list (on the main website) or monitor
5661 the website for updates to learn when the port (and other releases) are
5662 available.
5663\layout Standard
5664
5665<OPINION>If you're a *NIX user, stick with Apache 1.3.x for now.
5666 Major modules like mod_ssl and PHP are not even ready for 2.0 yet, and the
5667 main benefits in 2.0 are for Win32 users anyway.
5668 Apache 1.3.x is rock-stable and performs equally well on *NIX as 2.0.</OPINION>
5669\layout Subsubsection
5670
5671Does mod_log_sql connect to MySQL via TCP/IP or a socket?
5672\layout Standard
5673
5674It depends! This is not determined by mod_log_sql.
5675 mod_log_sql relies on a connection command that is supplied in the MySQL
5676 API, and that command is somewhat intelligent.
5677 How it works:
5678\layout Itemize
5679
5680if the specified MySQL database is on the same machine, the connection command
5681 uses a socket to communicate with MySQL
5682\layout Itemize
5683
5684if the specified MySQL database is on a different machine, mod_log_sql connects
5685 using TCP/IP.
5686
5687\layout Standard
5688
5689You don't have any control of which methodology is used.
5690 You can fine-tune some of the configuration, however.
5691 The
5692\noun on
5693LogSQLSocketFile
5694\noun default
5695 runtime configuration directive overrides the default of
5696\begin_inset Quotes eld
5697\end_inset
5698
5699/var/lib/mysql/mysql.sock
5700\begin_inset Quotes erd
5701\end_inset
5702
5703 for socket-based connections, whereas the
5704\noun on
5705LogSQLTCPPort
5706\noun default
5707 command allows to you override the default TCP port of 3306 for TCP/IP
5708 connections.
5709\layout Subsubsection
5710
5711I have discovered a bug.
5712 Who can I contact?
5713\layout Standard
5714
5715Please contact
5716\begin_inset LatexCommand \url[the maintainer]{(chris@grubbybaby.com)}
5717
5718\end_inset
5719
5720! Your comments, suggestions, bugfixes, bug catches, and usage testimonials
5721 are always welcome.
5722 As free software, mod_log_sql is intended to be a community effort -- any
5723 code contributions or other ideas will be fully and openly credited, of
5724 course.
5388\layout Subsection 5725\layout Subsection
5389 5726
5727Performance and Tuning
5728\layout Subsubsection
5729
5390How well does it perform? 5730How well does it perform?
5391\layout Standard 5731\layout Standard
5392 5732
@@ -5525,14 +5865,14 @@ mysql> delete from access_log where agent like 'ApacheBench%';
5525\layout LyX-Code 5865\layout LyX-Code
5526 5866
5527mysql> optimize table access_log; 5867mysql> optimize table access_log;
5528\layout Subsection 5868\layout Subsubsection
5529 5869
5530Do I need to be worried about all the running MySQL children? Will holding 5870Do I need to be worried about all the running MySQL children? Will holding
5531 open 5871 open
5532\emph on 5872\emph on
5533n 5873n
5534\emph default 5874\emph default
5535 Apache -> MySQL connections consume a lot of memory? 5875 Apache-to-MySQL connections consume a lot of memory?
5536\layout Standard 5876\layout Standard
5537 5877
5538Short answer: you shouldn't be worried. 5878Short answer: you shouldn't be worried.
@@ -5564,8 +5904,8 @@ ps -aufxw
5564 30 children each... 5904 30 children each...
5565\layout Standard 5905\layout Standard
5566 5906
5567Fortunately the cost reported by 'ps -aufxw' is indeed deceptive due to 5907Fortunately the cost reported by 'ps -aufxw' is deceptive.
5568 an OS memory-management feature called 5908 This is due to an OS memory-management feature called
5569\begin_inset Quotes eld 5909\begin_inset Quotes eld
5570\end_inset 5910\end_inset
5571 5911
@@ -5573,7 +5913,6 @@ copy-on-write.
5573\begin_inset Quotes erd 5913\begin_inset Quotes erd
5574\end_inset 5914\end_inset
5575 5915
5576 This is a memory-management technique used by Unix-based systems.
5577 When you have a number of identical child processes (e.g. 5916 When you have a number of identical child processes (e.g.
5578 Apache, MySQL), it would appear in 5917 Apache, MySQL), it would appear in
5579\begin_inset Quotes eld 5918\begin_inset Quotes eld
@@ -5597,7 +5936,8 @@ read-only
5597 fashion. 5936 fashion.
5598 The OS can get away with this because the majority of memory pages for 5937 The OS can get away with this because the majority of memory pages for
5599 one child are identical across all children. 5938 one child are identical across all children.
5600 5939 Instead of thinking of each child as a rubber stamp of the others, think
5940 of each child as a basket of links to a common memory area.
5601\layout Standard 5941\layout Standard
5602 5942
5603A memory page is only duplicated when it needs to be written to, hence 5943A memory page is only duplicated when it needs to be written to, hence
@@ -5655,9 +5995,17 @@ do not
5655 occupy 880MB of RAM. 5995 occupy 880MB of RAM.
5656\layout Standard 5996\layout Standard
5657 5997
5658The bottom line: although tangible, there is not much memory cost associated 5998The bottom line: although there is a cost to spawn extra httpd or mysqld
5659 with spawning off extra httpd or mysqld children. 5999 children, that cost is not as great as
5660\layout Subsection 6000\begin_inset Quotes eld
6001\end_inset
6002
6003ps
6004\begin_inset Quotes erd
6005\end_inset
6006
6007 would lead you to believe.
6008\layout Subsubsection
5661 6009
5662My database cannot handle all the open connections from mod_log_sql, is 6010My database cannot handle all the open connections from mod_log_sql, is
5663 there anything I can do? 6011 there anything I can do?
@@ -5708,7 +6056,7 @@ performance
5708 than the alternatives because it dispenses with the overhead associated 6056 than the alternatives because it dispenses with the overhead associated
5709 with rapid connection cycling, and it doesn't attempt to shoehorn all the 6057 with rapid connection cycling, and it doesn't attempt to shoehorn all the
5710 database traffic through a single extra daemon or proxy process. 6058 database traffic through a single extra daemon or proxy process.
5711\layout Subsection 6059\layout Subsubsection
5712 6060
5713My webservers cannot handle all the traffic that my site receives, is there 6061My webservers cannot handle all the traffic that my site receives, is there
5714 anything I can do? 6062 anything I can do?
@@ -5718,20 +6066,193 @@ If you have exhausted all the tuning possibilities on your existing server,
5718 it is probably time you evaluated the benefits of clustering two or more 6066 it is probably time you evaluated the benefits of clustering two or more
5719 webservers together in a load-balanced fashion. 6067 webservers together in a load-balanced fashion.
5720 In fact, users of such a setup are mod_log_sql's target audience! 6068 In fact, users of such a setup are mod_log_sql's target audience!
5721\layout Subsection 6069\layout Subsubsection
5722 6070
5723Who's using mod_log_sql? 6071Why do I occasionally see a
6072\begin_inset Quotes eld
6073\end_inset
6074
6075lost connection to MySQL server
6076\begin_inset Quotes erd
6077\end_inset
6078
6079 message in my error-log?
5724\layout Standard 6080\layout Standard
5725 6081
5726Good question! It would be great to find out! If you are a production-level 6082This message may appear every now and then in your Apache error log, especially
5727 mod_log_sql user, please contact 6083 on very lightly loaded servers.
5728\begin_inset LatexCommand \url[the maintainer, Chris Powell]{(chris@grubbybaby.com)} 6084 This doesn't mean that anything is necessarily wrong.
6085 Within each httpd child process, mod_log_sql will open (and keep open)
6086 a connection to the MySQL server.
6087 MySQL, however, will close connections that haven't been used in a while;
6088 the default timeout is 8 hours.
6089 When this occurs, mod_log_sql will notice and re-open the connection.
6090 That event is what is being logged, and looks like this:
6091\layout LyX-Code
6092
6093[Tue Nov 12 19:04:10 2002] [error] mod_log_sql: first attempt failed,
6094\layout LyX-Code
6095
6096 API said: error 2013, Lost connection to MySQL server during query
6097\layout LyX-Code
6098
6099[Tue Nov 12 19:04:10 2002] [error] mod_log_sql: reconnect successful
6100\layout LyX-Code
6101
6102[Tue Nov 12 19:04:10 2002] [error] mod_log_sql: second attempt successful
6103\layout Standard
6104
6105Reference:
6106\begin_inset LatexCommand \url[MySQL documentation]{(http://www.mysql.com/documentation/mysql/bychapter/manual_Problems.html#Gone_away)}
5729 6107
5730\end_inset 6108\end_inset
5731 6109
5732 so that you can be mentioned here. 6110
6111\layout Subsubsection
6112
6113
6114\begin_inset LatexCommand \label{sub:DelayedInsFAQ}
6115
6116\end_inset
6117
6118What is the issue with activating delayed inserts?
6119\layout Standard
6120
6121There are several.
6122\layout Enumerate
6123
6124INSERT DELAYED is a specific syntax to MySQL and is not supported by any
6125 other database.
6126 Ergo, why is it needed, and what MySQL deficiency is it working around?
6127 INSERT DELAYED is a kluge.
6128\layout Enumerate
6129
6130The MySQL documentation is unclear whether INSERT DELAYED is even necessary
6131 for an optimized database.
6132 It says,
6133\begin_inset Quotes eld
6134\end_inset
6135
6136The DELAYED option for the INSERT statement is a MySQL-specific option that
6137 is very useful if you have clients that can't wait for the INSERT to complete.
6138\begin_inset Quotes erd
6139\end_inset
6140
6141 But then it goes on to say,
6142\begin_inset Quotes eld
6143\end_inset
6144
6145Note that as MyISAM tables supports concurrent SELECT and INSERT, if there
6146 is no free blocks in the middle of the data file, you very seldom need
6147 to use INSERT DELAYED with MyISAM.
6148\begin_inset Quotes erd
6149\end_inset
6150
6151
6152\layout Enumerate
6153
6154Because INSERT DELAYED returns without waiting for the data to be written,
6155 a hard kill of your MySQL database at the right (wrong?) moment could lose
6156 those logfile entries.
6157\layout Enumerate
6158
6159As of MySQL version 3.23.52, the error return functions disagree after a failed
6160 INSERT DELAYED: mysql_errno() always returns 0, even if mysql_error() returns
6161 a textual error.
6162 I have reported this bug to the MySQL folks.
6163 However, we have no way of knowing what solution they will adopt to fix
6164 this, and with the worst case solution mod_log_sql would not be able to
6165 tell if anything went wrong with a delayed insert.
6166\layout Standard
6167
6168If after understanding these problems you still wish to enable delayed inserts,
6169 section
6170\begin_inset LatexCommand \ref{sub:DelayedIns}
6171
6172\end_inset
6173
6174 discusses how.
5733\layout Subsection 6175\layout Subsection
5734 6176
6177
6178\begin_inset Quotes eld
6179\end_inset
6180
6181How do I...?
6182\begin_inset Quotes erd
6183\end_inset
6184
6185 -- accomplishing certain tasks
6186\layout Subsubsection
6187
6188I am using LogSQLMassVirtualHosting, and sometimes a single VirtualHost
6189 gets logged to two different tables.
6190 How do I prevent that?
6191\layout Standard
6192
6193Proper usage of the Apache runtime
6194\noun on
6195ServerName
6196\noun default
6197 directive and the directive
6198\noun on
6199UseCanonicalName On
6200\noun default
6201(or
6202\noun on
6203DNS
6204\noun default
6205) are necessary to prevent this problem.
6206
6207\begin_inset Quotes eld
6208\end_inset
6209
6210On
6211\begin_inset Quotes erd
6212\end_inset
6213
6214 is the default for
6215\noun on
6216UseCanonicalName
6217\noun default
6218, and specifies that self-referential URLs are generated from the
6219\noun on
6220ServerName
6221\noun default
6222part of your VirtualHost:
6223\layout Quote
6224
6225With UseCanonicalName on (and in all versions prior to 1.3) Apache will use
6226 the ServerName and Port directives to construct the canonical name for
6227 the server.
6228 With UseCanonicalName off Apache will form self-referential URLs using
6229 the hostname and port supplied by the client if any are supplied (otherwise
6230 it will use the canonical name, as defined above).
6231 [From
6232\begin_inset LatexCommand \url[the Apache documentation]{http://httpd.apache.org/docs/mod/core.html#usecanonicalname}
6233
6234\end_inset
6235
6236]
6237\layout Standard
6238
6239The module inherits Apache's
6240\begin_inset Quotes eld
6241\end_inset
6242
6243knowledge
6244\begin_inset Quotes erd
6245\end_inset
6246
6247 about the server name being accessed.
6248 As long as those two directives are properly configured, mod_log_sql will
6249 log to only one table per virtual host while using
6250\noun on
6251LogSQLMassVirtualHosting
6252\noun default
6253.
6254\layout Subsubsection
6255
5735How do I extract the data in a format that my analysis tool can understand? 6256How do I extract the data in a format that my analysis tool can understand?
5736\layout Standard 6257\layout Standard
5737 6258
@@ -5790,22 +6311,7 @@ rm -f /var/log/httpd/templog
5790\layout Standard 6311\layout Standard
5791 6312
5792See? Easy. 6313See? Easy.
5793\layout Subsection 6314\layout Subsubsection
5794
5795Why doesn't the module also replace the Apache ErrorLog?
5796\layout Standard
5797
5798There are circumstances when that would be quite unwise -- for example,
5799 if Apache could not reach the MySQL server for some reason and needed to
5800 log that fact.
5801 Without a text-based error log you'd never know anything was wrong, because
5802 Apache would be trying to log a database connection error to the database...
5803 you get the point.
5804\layout Standard
5805
5806Error logs are usually not very high-traffic and are really best left as
5807 text files on a web server machine.
5808\layout Subsection
5809 6315
5810 6316
5811\begin_inset LatexCommand \label{sec:cookie} 6317\begin_inset LatexCommand \label{sec:cookie}
@@ -6007,7 +6513,7 @@ ool-18e4.dyn.optonline.net.130051007102700823
6007 6513
6008\layout LyX-Code 6514\layout LyX-Code
6009 6515
6010\layout Subsection 6516\layout Subsubsection
6011 6517
6012What if I want to log more than one cookie? What is the difference between 6518What if I want to log more than one cookie? What is the difference between
6013 LogSQLWhichCookie and LogSQLWhichCookies? 6519 LogSQLWhichCookie and LogSQLWhichCookies?
@@ -6077,7 +6583,7 @@ LogSQLWhichCookies
6077\noun default 6583\noun default
6078 can coexist without conflict because they operate on entireley different 6584 can coexist without conflict because they operate on entireley different
6079 tables, but you're better off choosing the one you need. 6585 tables, but you're better off choosing the one you need.
6080\layout Subsection 6586\layout Subsubsection
6081 6587
6082What are the SSL logging features, and how do I activate them? 6588What are the SSL logging features, and how do I activate them?
6083\layout Standard 6589\layout Standard
@@ -6091,6 +6597,7 @@ need to compile SSL support into mod_log_sql in order to simply use it with
6091 You only need to compile SSL support into mod_log_sql if you want to log 6597 You only need to compile SSL support into mod_log_sql if you want to log
6092 SSL-specific data such as the cipher type used, or the keysize that was 6598 SSL-specific data such as the cipher type used, or the keysize that was
6093 negotiated. 6599 negotiated.
6600 If that information is unimportant to you, you can ignore this FAQ.
6094\layout Standard 6601\layout Standard
6095 6602
6096By adding certain characters to your 6603By adding certain characters to your
@@ -6349,128 +6856,4 @@ RC4-MD5
6349 6856
6350\layout LyX-Code 6857\layout LyX-Code
6351 6858
6352\layout Subsection
6353
6354Does mod_log_sql connect to MySQL via TCP/IP or a socket?
6355\layout Standard
6356
6357It depends! This is not determined by mod_log_sql.
6358 mod_log_sql relies on a connection command that is supplied in the MySQL
6359 API, and that command is somewhat intelligent.
6360 How it works:
6361\layout Itemize
6362
6363if the specified MySQL database is on the same machine, the connection command
6364 uses a socket to communicate with MySQL
6365\layout Itemize
6366
6367if the specified MySQL database is on a different machine, mod_log_sql connects
6368 using TCP/IP.
6369
6370\layout Standard
6371
6372You don't have any control of which methodology is used.
6373 You can fine-tune some of the configuration, however.
6374 The
6375\noun on
6376LogSQLSocketFile
6377\noun default
6378 runtime configuration directive overrides the default of
6379\begin_inset Quotes eld
6380\end_inset
6381
6382/var/lib/mysql/mysql.sock
6383\begin_inset Quotes erd
6384\end_inset
6385
6386 for socket-based connections, whereas the
6387\noun on
6388LogSQLTCPPort
6389\noun default
6390 command allows to you override the default TCP port of 3306 for TCP/IP
6391 connections.
6392\layout Subsection
6393
6394Why do I occasionally see a
6395\begin_inset Quotes eld
6396\end_inset
6397
6398lost connection to MySQL server
6399\begin_inset Quotes erd
6400\end_inset
6401
6402 message in my error-log?
6403\layout Standard
6404
6405This message may appear every now and then in your Apache error log, especially
6406 on very lightly loaded servers.
6407 This doesn't mean that anything is necessarily wrong.
6408 Within each httpd child process, mod_log_sql will open (and keep open)
6409 a connection to the MySQL server.
6410 MySQL, however, will close connections that haven't been used in a while;
6411 the default timeout is 8 hours.
6412 When this occurs, mod_log_sql will notice and re-open the connection.
6413 That event is what is being logged, and looks like this:
6414\layout LyX-Code
6415
6416[Tue Nov 12 19:04:10 2002] [error] mod_log_sql: first attempt failed,
6417\layout LyX-Code
6418
6419 API said: error 2013, Lost connection to MySQL server during query
6420\layout LyX-Code
6421
6422[Tue Nov 12 19:04:10 2002] [error] mod_log_sql: reconnect successful
6423\layout LyX-Code
6424
6425[Tue Nov 12 19:04:10 2002] [error] mod_log_sql: second attempt successful
6426
6427\layout LyX-Code
6428
6429\layout Standard
6430
6431Reference:
6432\begin_inset LatexCommand \url[MySQL documentation]{(http://www.mysql.com/documentation/mysql/bychapter/manual_Problems.html#Gone_away)}
6433
6434\end_inset
6435
6436
6437\layout Subsection
6438
6439Does mod_log_sql work with Apache 2.x?
6440\layout Standard
6441
6442As of this writing, no.
6443 The Apache Group significantly altered the module API with the release
6444 of Apache 2.0.
6445 All modules written for 1.3, including mod_log_sql, will not work with 2.0.
6446\layout Standard
6447
6448mod_log_sql will eventually be ported to Apache 2.x, but not immediately.
6449 It is going to take some time, and there are other features that have higher
6450 priority.
6451 Please sign up for the announcements list (on the main website) or monitor
6452 the website for updates to learn when the port (and other releases) are
6453 available.
6454\layout Standard
6455
6456<OPINION>If you're a *NIX user, stick with Apache 1.3.x for now.
6457 Major modules like mod_ssl and PHP are not even ready for 2.0 yet, and the
6458 main benefits in 2.0 are for Win32 users anyway.
6459 Apache 1.3.x is rock-stable and performs equally well on *NIX as 2.0.</OPINION>
6460\layout Subsection
6461
6462I have discovered a bug.
6463 Who can I contact?
6464\layout Standard
6465
6466Please contact
6467\begin_inset LatexCommand \url[the maintainer]{(chris@grubbybaby.com)}
6468
6469\end_inset
6470
6471! Your comments, suggestions, bugfixes, bug catches, and usage testimonials
6472 are always welcome.
6473 As free software, mod_log_sql is intended to be a community effort -- any
6474 code contributions or other ideas will be fully and openly credited, of
6475 course.
6476\the_end 6859\the_end
diff --git a/Makefile b/Makefile
index d2effa0..6f69b50 100644
--- a/Makefile
+++ b/Makefile
@@ -1,4 +1,4 @@
1# $Id: Makefile,v 1.17 2002/11/27 07:13:58 helios Exp $ 1# $Id: Makefile,v 1.18 2002/12/10 19:43:21 helios Exp $
2 2
3########################################################################### 3###########################################################################
4# Important: 4# Important:
@@ -13,7 +13,7 @@ MYSQLLIBS = /usr/lib
13MYSQLHDRS = /usr/include/mysql 13MYSQLHDRS = /usr/include/mysql
14 14
15#MODSSLHDRS = /usr/local/src/apache_1.3.27-dso/src/modules/ssl 15#MODSSLHDRS = /usr/local/src/apache_1.3.27-dso/src/modules/ssl
16DB1HDRS = /usr/include/db1 16#DB1HDRS = /usr/include/db1
17 17
18########################################################################### 18###########################################################################
19# Don't uncomment this without reading the "Optimizing for a busy database" 19# 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 @@
1create table access_log ( 1create table access_log (
2 id char(19) , 2 id char(19) ,
3 agent varchar(255) , 3 agent varchar(255) ,
4 bytes_sent int unsigned , 4 bytes_sent int unsigned ,
5 child_pid smallint unsigned, 5 child_pid smallint unsigned,
6 cookie varchar(255), 6 cookie varchar(255),
7 machine_id varchar(25), 7 machine_id varchar(25),
8 request_file varchar(255), 8 request_file varchar(255),
9 referer varchar(255) , 9 referer varchar(255) ,
10 remote_host varchar(50) , 10 remote_host varchar(50) ,
@@ -47,4 +47,4 @@ create table cookies (
47 id char(19), 47 id char(19),
48 item varchar(80), 48 item varchar(80),
49 val varchar(80) 49 val varchar(80)
50); \ No newline at end of file 50);
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 @@
1/* $Id: mod_log_sql.c,v 1.19 2002/11/27 07:13:58 helios Exp $ */ 1/* $Id: mod_log_sql.c,v 1.20 2002/12/10 19:43:21 helios Exp $ */
2 2
3/* --------* 3/* --------*
4 * DEFINES * 4 * DEFINES *
5 * --------*/ 5 * --------*/
6 6
7/* The enduser may wish to modify this */ 7/* The enduser may wish to modify this */
8#define DEBUG 8#undef DEBUG
9 9
10/* The enduser won't modify these */ 10/* The enduser won't modify these */
11#define MYSQL_ERROR(mysql) ((mysql)?(mysql_error(mysql)):"MySQL server has gone away") 11#define MYSQL_ERROR(mysql) ((mysql)?(mysql_error(mysql)):"MySQL server has gone away")
@@ -20,11 +20,13 @@
20#include <time.h> 20#include <time.h>
21#include <stdio.h> 21#include <stdio.h>
22#include <stdlib.h> 22#include <stdlib.h>
23#include <string.h>
23#include "httpd.h" 24#include "httpd.h"
24#include "http_config.h" 25#include "http_config.h"
25#include "http_log.h" 26#include "http_log.h"
26#include "http_core.h" 27#include "http_core.h"
27#include "mysql.h" 28#include "mysql.h"
29#include "mysqld_error.h"
28 30
29#if MODULE_MAGIC_NUMBER >= 19980324 /* M_M_N is defined in /usr/local/Apache/include/ap_mmn.h, 19990320 as of this writing. */ 31#if MODULE_MAGIC_NUMBER >= 19980324 /* M_M_N is defined in /usr/local/Apache/include/ap_mmn.h, 19990320 as of this writing. */
30 #include "ap_compat.h" 32 #include "ap_compat.h"
@@ -73,8 +75,8 @@ typedef const char *(*item_key_func) (request_rec *, char *);
73 * Each child process has its own segregated copy of this structure. 75 * Each child process has its own segregated copy of this structure.
74 */ 76 */
75typedef struct { 77typedef struct {
76 int table_made;
77 array_header *transfer_ignore_list; 78 array_header *transfer_ignore_list;
79 array_header *transfer_accept_list;
78 array_header *remhost_ignore_list; 80 array_header *remhost_ignore_list;
79 array_header *notes_list; 81 array_header *notes_list;
80 array_header *hout_list; 82 array_header *hout_list;
@@ -94,6 +96,9 @@ typedef struct {
94/* -----------------* 96/* -----------------*
95 * HELPER FUNCTIONS * 97 * HELPER FUNCTIONS *
96 * -----------------*/ 98 * -----------------*/
99
100int safe_create_tables(log_sql_state *cls, request_rec *r);
101
97static char *format_integer(pool *p, int i) 102static char *format_integer(pool *p, int i)
98{ 103{
99 char dummy[40]; 104 char dummy[40];
@@ -484,7 +489,7 @@ static const char *extract_request_timestamp(request_rec *r, char *a)
484{ 489{
485 char tstr[32]; 490 char tstr[32];
486 491
487 snprintf(tstr, 32, "%ld", time(NULL)); 492 ap_snprintf(tstr, 32, "%ld", time(NULL));
488 return pstrdup(r->pool, tstr); 493 return pstrdup(r->pool, tstr);
489} 494}
490 495
@@ -665,73 +670,123 @@ void preserve_entry(request_rec *r, const char *query)
665/* Parms: request record, SQL insert statement */ 670/* Parms: request record, SQL insert statement */
666/* Returns: 0 (OK) on success */ 671/* Returns: 0 (OK) on success */
667/* 1 if have no log handle */ 672/* 1 if have no log handle */
668/* actual MySQL return code on error */ 673/* 2 if insert delayed failed (kluge) */
674/* the actual MySQL return code on error */
669/*-----------------------------------------------------*/ 675/*-----------------------------------------------------*/
670unsigned int safe_mysql_query(request_rec *r, const char *query) 676unsigned int safe_mysql_query(request_rec *r, const char *query)
671{ 677{
672 unsigned int retval; 678 int retval;
673 unsigned int real_error;
674 struct timespec delay, remainder; 679 struct timespec delay, remainder;
675 int ret; 680 int ret;
676 void (*handler) (int); 681 void (*handler) (int);
677 682 log_sql_state *cls;
683 unsigned int real_error;
684 #ifdef WANT_DELAYED_MYSQL_INSERT
685 char *real_error_str;
686 #endif
678 687
679 /* A failed mysql_query() may send a SIGPIPE, so we ignore that signal momentarily. */ 688 /* A failed mysql_query() may send a SIGPIPE, so we ignore that signal momentarily. */
680 handler = signal(SIGPIPE, SIG_IGN); 689 handler = signal(SIGPIPE, SIG_IGN);
681 690
682 /* First attempt for the query */ 691 /* First attempt for the query */
683 if (mysql_log != NULL) 692 if (!mysql_log) {
684 retval = mysql_query(mysql_log, query); 693 signal(SIGPIPE, handler);
685 else 694 return 1;
686 return 1; 695 } else if (!(retval = mysql_query(mysql_log, query))) {
696 signal(SIGPIPE, handler);
697 return 0;
698 }
687 699
688 if ( retval != 0 ) 700 /* If we ran the query and it returned an error, try to be robust.
689 { 701 * (After all, the module thought it had a valid mysql_log connection but the query
690 /* If we ran the query and it returned an error, try to be robust. 702 * could have failed for a number of reasons, so we have to be extra-safe and check.) */
691 * (After all, the module thought it had a valid mysql_log connection but the query 703 #ifdef WANT_DELAYED_MYSQL_INSERT
692 * could have failed for a number of reasons, so we have to be extra-safe and check.) */ 704 real_error_str = MYSQL_ERROR(mysql_log);
693 705 #else
694 log_sql_state *cls = get_module_config(r->server->module_config, &sql_log_module); 706 real_error = mysql_errno(mysql_log);
695 707 #endif
696 real_error = mysql_errno(mysql_log); /* What really happened? */ 708
697 709 /* Check to see if the error is "nonexistent table" */
698 /* Something went wrong, so start by trying to restart the db link. */ 710 #ifdef WANT_DELAYED_MYSQL_INSERT
699 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)); 711 if ((strstr(real_error_str, "Table")) && (strstr(real_error_str,"doesn't exist"))) {
700 mysql_close(mysql_log); 712 #else
701 mysql_log = NULL; 713 if (real_error == ER_NO_SUCH_TABLE) {
702 open_logdb_link(r->server); 714 #endif
703 715 if (create_tables) {
704 if (mysql_log == NULL) { /* still unable to link */ 716 ap_log_error(APLOG_MARK,ERRLEVEL,r->server,"mod_log_sql: table doesn't exist...creating now");
705 signal(SIGPIPE, handler); 717 cls = get_module_config(r->server->module_config, &sql_log_module);
706 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."); 718 if (safe_create_tables(cls, r)) {
707 ap_log_error(APLOG_MARK,ERRLEVEL,r->server,"mod_log_sql: log entries are being preserved in %s", cls->preserve_file); 719 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));
708 return 1; 720 preserve_entry(r, query);
709 } else 721 retval = mysql_errno(mysql_log);
710 ap_log_error(APLOG_MARK,ERRLEVEL,r->server,"mod_log_sql: reconnect successful"); 722 } else {
711 723 ap_log_error(APLOG_MARK,ERRLEVEL,r->server,"mod_log_sql: tables successfully created - retrying query");
712 /* First sleep for a tiny amount of time. */ 724 if (mysql_query(mysql_log, query)) {
713 delay.tv_sec = 0; 725 ap_log_error(APLOG_MARK,ERRLEVEL,r->server,"mod_log_sql: giving up, preserving query");
714 delay.tv_nsec = 250000000; /* max is 999999999 (nine nines) */ 726 preserve_entry(r, query);
715 ret = nanosleep(&delay, &remainder); 727 retval = mysql_errno(mysql_log);
716 if (ret && errno != EINTR) 728 } else
717 ap_log_error(APLOG_MARK,ERRLEVEL,r->server,"mod_log_sql: nanosleep unsuccessful"); 729 ap_log_error(APLOG_MARK,ERRLEVEL,r->server,"mod_log_sql: query successful after table creation");
718 730 retval = 0;
719 /* Then make our second attempt */ 731 }
720 retval = mysql_query(mysql_log,query); 732 } else {
721 733 ap_log_error(APLOG_MARK,ERRLEVEL,r->server,"mod_log_sql, table doesn't exist, creation denied by configuration, preserving query");
722 /* If this one also failed, log that and append to our local offline file */ 734 preserve_entry(r, query);
723 if ( retval != 0 ) 735 retval = ER_NO_SUCH_TABLE;
724 { 736 }
725 real_error = mysql_errno(mysql_log); 737 /* Restore SIGPIPE to its original handler function */
726 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)); 738 signal(SIGPIPE, handler);
727 retval = real_error; 739 return retval;
728 } else
729 ap_log_error(APLOG_MARK,ERRLEVEL,r->server,"mod_log_sql: second attempt successful");
730 } 740 }
731 741
742 /* Handle all other types of errors */
743
744 cls = get_module_config(r->server->module_config, &sql_log_module);
745
746 /* Something went wrong, so start by trying to restart the db link. */
747 #ifdef WANT_DELAYED_MYSQL_INSERT
748 real_error = 2;
749 #else
750 real_error = mysql_errno(mysql_log);
751 #endif
752 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));
753 mysql_close(mysql_log);
754 mysql_log = NULL;
755 open_logdb_link(r->server);
756
757 if (mysql_log == NULL) { /* still unable to link */
758 signal(SIGPIPE, handler);
759 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.");
760 ap_log_error(APLOG_MARK,ERRLEVEL,r->server,"mod_log_sql: log entries are being preserved in %s", cls->preserve_file);
761 return 1;
762 } else
763 ap_log_error(APLOG_MARK,ERRLEVEL,r->server,"mod_log_sql: db reconnect successful");
764
765 /* First sleep for a tiny amount of time. */
766 delay.tv_sec = 0;
767 delay.tv_nsec = 250000000; /* max is 999999999 (nine nines) */
768 ret = nanosleep(&delay, &remainder);
769 if (ret && errno != EINTR)
770 ap_log_error(APLOG_MARK,ERRLEVEL,r->server,"mod_log_sql: nanosleep unsuccessful");
771
772 /* Then make our second attempt */
773 retval = mysql_query(mysql_log,query);
774
775 /* If this one also failed, log that and append to our local offline file */
776 if (retval) {
777 #ifdef WANT_DELAYED_MYSQL_INSERT
778 real_error = 2;
779 #else
780 real_error = mysql_errno(mysql_log);
781 #endif
782 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));
783 preserve_entry(r, query);
784 retval = real_error;
785 } else
786 ap_log_error(APLOG_MARK,ERRLEVEL,r->server,"mod_log_sql: second attempt successful");
787
732 /* Restore SIGPIPE to its original handler function */ 788 /* Restore SIGPIPE to its original handler function */
733 signal(SIGPIPE, handler); 789 signal(SIGPIPE, handler);
734
735 return retval; 790 return retval;
736} 791}
737 792
@@ -811,35 +866,29 @@ int safe_create_tables(log_sql_state *cls, request_rec *r)
811 #endif 866 #endif
812 867
813 /* Assume that things worked unless told otherwise */ 868 /* Assume that things worked unless told otherwise */
814 cls->table_made = 1;
815 retval = 0; 869 retval = 0;
816 870
817 if ((create_results = safe_mysql_query(r, create_access)) != 0) { 871 if ((create_results = safe_mysql_query(r, create_access))) {
818 cls->table_made = 0;
819 ap_log_error(APLOG_MARK,ERRLEVEL,r->server,"mod_log_sql: failed to create access table"); 872 ap_log_error(APLOG_MARK,ERRLEVEL,r->server,"mod_log_sql: failed to create access table");
820 retval = create_results; 873 retval = create_results;
821 } 874 }
822 875
823 if ((create_results = safe_mysql_query(r, create_notes)) != 0) { 876 if ((create_results = safe_mysql_query(r, create_notes))) {
824 cls->table_made = 0;
825 ap_log_error(APLOG_MARK,ERRLEVEL,r->server,"mod_log_sql: failed to create notes table"); 877 ap_log_error(APLOG_MARK,ERRLEVEL,r->server,"mod_log_sql: failed to create notes table");
826 retval = create_results; 878 retval = create_results;
827 } 879 }
828 880
829 if ((create_results = safe_mysql_query(r, create_hin)) != 0) { 881 if ((create_results = safe_mysql_query(r, create_hin))) {
830 cls->table_made = 0;
831 ap_log_error(APLOG_MARK,ERRLEVEL,r->server,"mod_log_sql: failed to create header_out table"); 882 ap_log_error(APLOG_MARK,ERRLEVEL,r->server,"mod_log_sql: failed to create header_out table");
832 retval = create_results; 883 retval = create_results;
833 } 884 }
834 885
835 if ((create_results = safe_mysql_query(r, create_hout)) != 0) { 886 if ((create_results = safe_mysql_query(r, create_hout))) {
836 cls->table_made = 0;
837 ap_log_error(APLOG_MARK,ERRLEVEL,r->server,"mod_log_sql: failed to create header_in table"); 887 ap_log_error(APLOG_MARK,ERRLEVEL,r->server,"mod_log_sql: failed to create header_in table");
838 retval = create_results; 888 retval = create_results;
839 } 889 }
840 890
841 if ((create_results = safe_mysql_query(r, create_cookies)) != 0) { 891 if ((create_results = safe_mysql_query(r, create_cookies))) {
842 cls->table_made = 0;
843 ap_log_error(APLOG_MARK,ERRLEVEL,r->server,"mod_log_sql: failed to create cookies table"); 892 ap_log_error(APLOG_MARK,ERRLEVEL,r->server,"mod_log_sql: failed to create cookies table");
844 retval = create_results; 893 retval = create_results;
845 } 894 }
@@ -872,8 +921,8 @@ const char *set_log_sql_machine_id(cmd_parms *parms, void *dummy, char *arg)
872 921
873const char *set_log_sql_create(cmd_parms *parms, void *dummy, int flag) 922const char *set_log_sql_create(cmd_parms *parms, void *dummy, int flag)
874{ 923{
875 if (massvirtual != 0) 924 if (massvirtual)
876 ap_log_error(APLOG_MARK,WARNINGLEVEL,parms->server,"mod_log_sql: do not set LogSQLCreateTables when LogSQLMassVirtualHosting is On. Ignoring."); 925 ap_log_error(APLOG_MARK,WARNINGLEVEL,parms->server,"mod_log_sql: do not set LogSQLCreateTables when LogSQLMassVirtualHosting is On. Ignoring.");
877 else 926 else
878 create_tables = ( flag ? 1 : 0); 927 create_tables = ( flag ? 1 : 0);
879 return NULL; 928 return NULL;
@@ -994,6 +1043,16 @@ const char *set_log_sql_tcp_port(cmd_parms *parms, void *dummy, char *arg)
994 return NULL; 1043 return NULL;
995} 1044}
996 1045
1046const char *add_log_sql_transfer_accept(cmd_parms *parms, void *dummy, char *arg)
1047{
1048 char **addme;
1049 log_sql_state *cls = get_module_config(parms->server->module_config, &sql_log_module);
1050
1051 addme = push_array(cls->transfer_accept_list);
1052 *addme = pstrdup(cls->transfer_accept_list->pool, arg);
1053 return NULL;
1054}
1055
997const char *add_log_sql_transfer_ignore(cmd_parms *parms, void *dummy, char *arg) 1056const char *add_log_sql_transfer_ignore(cmd_parms *parms, void *dummy, char *arg)
998{ 1057{
999 char **addme; 1058 char **addme;
@@ -1072,8 +1131,9 @@ const char *add_log_sql_cookie(cmd_parms *parms, void *dummy, char *arg)
1072 */ 1131 */
1073static void log_sql_child_init(server_rec *s, pool *p) 1132static void log_sql_child_init(server_rec *s, pool *p)
1074{ 1133{
1075 int retval; 1134 int retval;
1076 1135
1136 /* Open a link to the database */
1077 retval = open_logdb_link(s); 1137 retval = open_logdb_link(s);
1078 if (retval == 0) 1138 if (retval == 0)
1079 ap_log_error(APLOG_MARK,ERRLEVEL,s,"mod_log_sql: child spawned but unable to open database link"); 1139 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)
1140 cls->preserve_file = "/tmp/sql-preserve"; 1200 cls->preserve_file = "/tmp/sql-preserve";
1141 1201
1142 cls->transfer_ignore_list = make_array(p, 1, sizeof(char *)); 1202 cls->transfer_ignore_list = make_array(p, 1, sizeof(char *));
1203 cls->transfer_accept_list = make_array(p, 1, sizeof(char *));
1143 cls->remhost_ignore_list = make_array(p, 1, sizeof(char *)); 1204 cls->remhost_ignore_list = make_array(p, 1, sizeof(char *));
1144 cls->notes_list = make_array(p, 1, sizeof(char *)); 1205 cls->notes_list = make_array(p, 1, sizeof(char *));
1145 cls->hin_list = make_array(p, 1, sizeof(char *)); 1206 cls->hin_list = make_array(p, 1, sizeof(char *));
1146 cls->hout_list = make_array(p, 1, sizeof(char *)); 1207 cls->hout_list = make_array(p, 1, sizeof(char *));
1147 cls->cookie_list = make_array(p, 1, sizeof(char *)); 1208 cls->cookie_list = make_array(p, 1, sizeof(char *));
1148 cls->table_made = 0;
1149 cls->cookie_name = NULL; 1209 cls->cookie_name = NULL;
1150 1210
1151 return (void *) cls; 1211 return (void *) cls;
@@ -1177,6 +1237,9 @@ command_rec log_sql_cmds[] = {
1177 {"LogSQLMachineID", set_log_sql_machine_id, NULL, RSRC_CONF, TAKE1, 1237 {"LogSQLMachineID", set_log_sql_machine_id, NULL, RSRC_CONF, TAKE1,
1178 "Machine ID that the module will log, useful in web clusters to differentiate machines"} 1238 "Machine ID that the module will log, useful in web clusters to differentiate machines"}
1179 , 1239 ,
1240 {"LogSQLRequestAccept", add_log_sql_transfer_accept, NULL, RSRC_CONF, ITERATE,
1241 "List of URIs to accept for logging. Accesses that don't match will not be logged"}
1242 ,
1180 {"LogSQLRequestIgnore", add_log_sql_transfer_ignore, NULL, RSRC_CONF, ITERATE, 1243 {"LogSQLRequestIgnore", add_log_sql_transfer_ignore, NULL, RSRC_CONF, ITERATE,
1181 "List of URIs to ignore. Accesses that match will not be logged to database"} 1244 "List of URIs to ignore. Accesses that match will not be logged to database"}
1182 , 1245 ,
@@ -1240,18 +1303,18 @@ int log_sql_transaction(request_rec *orig)
1240 /* We handle mass virtual hosting differently. Dynamically determine the name 1303 /* We handle mass virtual hosting differently. Dynamically determine the name
1241 * of the table from the virtual server's name, and flag it for creation. 1304 * of the table from the virtual server's name, and flag it for creation.
1242 */ 1305 */
1243 if ( massvirtual == 1 ) { 1306 if (massvirtual) {
1244 char *access_base = "access_"; 1307 char *access_base = "access_";
1245 char *notes_base = "notes_"; 1308 char *notes_base = "notes_";
1246 char *hout_base = "headout_"; 1309 char *hout_base = "headout_";
1247 char *hin_base = "headin_"; 1310 char *hin_base = "headin_";
1248 char *cookie_base = "cookies_"; 1311 char *cookie_base = "cookies_";
1249 char *a_tablename; 1312 char *a_tablename;
1250 char *n_tablename; 1313 char *n_tablename;
1251 char *i_tablename; 1314 char *i_tablename;
1252 char *o_tablename; 1315 char *o_tablename;
1253 char *c_tablename; 1316 char *c_tablename;
1254 int i; 1317 unsigned int i;
1255 1318
1256 /* Find memory long enough to hold the table name + \0. */ 1319 /* Find memory long enough to hold the table name + \0. */
1257 a_tablename = ap_pstrcat(orig->pool, access_base, ap_get_server_name(orig), NULL); 1320 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)
1308 const char *unique_id; 1371 const char *unique_id;
1309 const char *formatted_item; 1372 const char *formatted_item;
1310 int i, j, length; 1373 int i, j, length;
1311 int result; 1374 int proceed;
1312 1375
1313 for (r = orig; r->next; r = r->next) { 1376 for (r = orig; r->next; r = r->next) {
1314 continue; 1377 continue;
@@ -1317,16 +1380,29 @@ int log_sql_transaction(request_rec *orig)
1317 /* The following is a stolen upsetting mess of pointers, I'm sorry. 1380 /* The following is a stolen upsetting mess of pointers, I'm sorry.
1318 * Anyone with the motiviation and/or the time should feel free 1381 * Anyone with the motiviation and/or the time should feel free
1319 * to make this cleaner. :) */ 1382 * to make this cleaner. :) */
1320 ptrptr2 = (char **) (cls->transfer_ignore_list->elts + (cls->transfer_ignore_list->nelts * cls->transfer_ignore_list->elt_size)); 1383 ptrptr2 = (char **) (cls->transfer_accept_list->elts + (cls->transfer_accept_list->nelts * cls->transfer_accept_list->elt_size));
1384
1385 /* Go through each element of the accept list and compare it to the
1386 * request_uri. If we don't get a match, return without logging */
1387 if ((r->uri) && (cls->transfer_accept_list->nelts)) {
1388 proceed = 0;
1389 for (ptrptr = (char **) cls->transfer_accept_list->elts; ptrptr < ptrptr2; ptrptr = (char **) ((char *) ptrptr + cls->transfer_accept_list->elt_size))
1390 if (strstr(r->uri, *ptrptr)) {
1391 proceed = 1;
1392 break;
1393 }
1394 if (!proceed)
1395 return OK;
1396 }
1321 1397
1322 /* Go through each element of the ignore list and compare it to the 1398 /* Go through each element of the ignore list and compare it to the
1323 * request_uri. If we get a match, return without logging */ 1399 * request_uri. If we get a match, return without logging */
1400 ptrptr2 = (char **) (cls->transfer_ignore_list->elts + (cls->transfer_ignore_list->nelts * cls->transfer_ignore_list->elt_size));
1324 if (r->uri) { 1401 if (r->uri) {
1325 for (ptrptr = (char **) cls->transfer_ignore_list->elts; ptrptr < ptrptr2; ptrptr = (char **) ((char *) ptrptr + cls->transfer_ignore_list->elt_size)) { 1402 for (ptrptr = (char **) cls->transfer_ignore_list->elts; ptrptr < ptrptr2; ptrptr = (char **) ((char *) ptrptr + cls->transfer_ignore_list->elt_size))
1326 if (strstr(r->uri, *ptrptr)) { 1403 if (strstr(r->uri, *ptrptr)) {
1327 return OK; 1404 return OK;
1328 } 1405 }
1329 }
1330 } 1406 }
1331 1407
1332 /* Go through each element of the ignore list and compare it to the 1408 /* Go through each element of the ignore list and compare it to the
@@ -1334,11 +1410,10 @@ int log_sql_transaction(request_rec *orig)
1334 ptrptr2 = (char **) (cls->remhost_ignore_list->elts + (cls->remhost_ignore_list->nelts * cls->remhost_ignore_list->elt_size)); 1410 ptrptr2 = (char **) (cls->remhost_ignore_list->elts + (cls->remhost_ignore_list->nelts * cls->remhost_ignore_list->elt_size));
1335 thehost = get_remote_host(r->connection, r->per_dir_config, REMOTE_NAME); 1411 thehost = get_remote_host(r->connection, r->per_dir_config, REMOTE_NAME);
1336 if (thehost) { 1412 if (thehost) {
1337 for (ptrptr = (char **) cls->remhost_ignore_list->elts; ptrptr < ptrptr2; ptrptr = (char **) ((char *) ptrptr + cls->remhost_ignore_list->elt_size)) { 1413 for (ptrptr = (char **) cls->remhost_ignore_list->elts; ptrptr < ptrptr2; ptrptr = (char **) ((char *) ptrptr + cls->remhost_ignore_list->elt_size))
1338 if (strstr(thehost, *ptrptr)) { 1414 if (strstr(thehost, *ptrptr)) {
1339 return OK; 1415 return OK;
1340 } 1416 }
1341 }
1342 } 1417 }
1343 1418
1344 length = strlen(cls->transfer_log_format); 1419 length = strlen(cls->transfer_log_format);
@@ -1530,7 +1605,7 @@ int log_sql_transaction(request_rec *orig)
1530 if (mysql_log == NULL) { 1605 if (mysql_log == NULL) {
1531 /* Unable to re-establish a DB link, so assume that it's really 1606 /* Unable to re-establish a DB link, so assume that it's really
1532 * gone and send the entry to the preserve file instead. 1607 * gone and send the entry to the preserve file instead.
1533 * This short-circuits safe_mysql_query during a db outage and therefore 1608 * This short-circuits safe_mysql_query() during a db outage and therefore
1534 * we don't keep logging the db error over and over. 1609 * we don't keep logging the db error over and over.
1535 */ 1610 */
1536 preserve_entry(orig, access_query); 1611 preserve_entry(orig, access_query);
@@ -1554,52 +1629,20 @@ int log_sql_transaction(request_rec *orig)
1554 /* ---> So as of here we have a non-null value of mysql_log. <--- */ 1629 /* ---> So as of here we have a non-null value of mysql_log. <--- */
1555 /* ---> i.e. we have a good MySQL connection. <--- */ 1630 /* ---> i.e. we have a good MySQL connection. <--- */
1556 1631
1557
1558 /* Make the tables if we're supposed to. */
1559 if ((cls->table_made != 1) && (create_tables != 0)) {
1560 result = safe_create_tables(cls, orig);
1561 if (result != 0)
1562 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));
1563 else
1564 ap_log_error(APLOG_MARK,NOTICELEVEL,orig->server,"mod_log_sql: no problems creating tables for %s", ap_get_server_name(orig));
1565 }
1566
1567 /* Make the access-table insert */ 1632 /* Make the access-table insert */
1568 result = safe_mysql_query(orig, access_query); 1633 safe_mysql_query(orig, access_query);
1569
1570 /* It failed, but NOT because table didn't exist */
1571 if ( (result != 0) && (result != 1146) )
1572 preserve_entry(orig,access_query);
1573
1574 /* It failed because table didn't exist */
1575 if (result == 1146) {
1576 ap_log_error(APLOG_MARK,ERRLEVEL,orig->server,"mod_log_sql: hmm, table didn't yet exist; creating");
1577 result = safe_create_tables(cls, orig);
1578 if (result != 0) {
1579 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));
1580 preserve_entry(orig,access_query);
1581 } else {
1582 ap_log_error(APLOG_MARK,ERRLEVEL,orig->server,"mod_log_sql: table successfully created, query will now be retried");
1583 result = safe_mysql_query(orig, access_query);
1584 if (result != 0 ) {
1585 ap_log_error(APLOG_MARK,ERRLEVEL,orig->server,"mod_log_sql: giving up, preserving query");
1586 preserve_entry(orig,access_query);
1587 return OK;
1588 }
1589 }
1590 }
1591 1634
1592 /* Log the optional notes, headers, etc. */ 1635 /* Log the optional notes, headers, etc. */
1593 if ( note_query != NULL ) 1636 if (note_query)
1594 safe_mysql_query(orig, note_query); 1637 safe_mysql_query(orig, note_query);
1595 1638
1596 if ( hout_query != NULL ) 1639 if (hout_query)
1597 safe_mysql_query(orig, hout_query); 1640 safe_mysql_query(orig, hout_query);
1598 1641
1599 if ( hin_query != NULL ) 1642 if (hin_query)
1600 safe_mysql_query(orig, hin_query); 1643 safe_mysql_query(orig, hin_query);
1601 1644
1602 if ( cookie_query != NULL ) 1645 if (cookie_query)
1603 safe_mysql_query(orig, cookie_query); 1646 safe_mysql_query(orig, cookie_query);
1604 1647
1605 return OK; 1648 return OK;
@@ -1634,3 +1677,46 @@ module sql_log_module = {
1634#endif 1677#endif
1635 1678
1636}; 1679};
1680
1681
1682
1683/* Make the tables if we're supposed to. */
1684/*
1685if ((cls->table_made != 1) && (create_tables != 0)) {
1686 result = safe_create_tables(cls, orig);
1687 if (result != 0)
1688 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));
1689 else
1690 ap_log_error(APLOG_MARK,NOTICELEVEL,orig->server,"mod_log_sql: no problems creating tables for %s", ap_get_server_name(orig));
1691}
1692*/
1693
1694/* It failed, but NOT because table didn't exist */
1695/*
1696if ( (result) && (result != ER_NO_SUCH_TABLE) )
1697 preserve_entry(orig,access_query);
1698*/
1699
1700/* It failed because table didn't exist */
1701/*
1702if (result == ER_NO_SUCH_TABLE)
1703 if (create_tables) {
1704 ap_log_error(APLOG_MARK,ERRLEVEL,orig->server,"mod_log_sql: access table doesn't exist...creating now");
1705 if ((result = safe_create_tables(cls, orig))) {
1706 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));
1707 preserve_entry(orig,access_query);
1708 } else {
1709 ap_log_error(APLOG_MARK,ERRLEVEL,orig->server,"mod_log_sql: table(s) successfully created - retrying insert");
1710 if ((result = safe_mysql_query(orig, access_query))) {
1711 ap_log_error(APLOG_MARK,ERRLEVEL,orig->server,"mod_log_sql: giving up, preserving query");
1712 preserve_entry(orig,access_query);
1713 return OK;
1714 } else
1715 ap_log_error(APLOG_MARK_ERRLEVEL,orig->server,"mod_log_sql: insert successful after table creation");
1716 }
1717 } else {
1718 ap_log_error(APLOG_MARK,ERRLEVEL,orig->server,"mod_log_sql: access table doesn't exist...not allowed to create, giving up, preserving query");
1719 preserve_entry(orig,access_query);
1720 return OK;
1721 }
1722*/