Date: Sun, 5 Sep 2010 05:18:24 GMT From: Charles Sprickman <css@morefoo.com> To: freebsd-gnats-submit@FreeBSD.org Subject: ports/150296: mod_log_sql2-dtc needs patching to update impression count Message-ID: <201009050518.o855IOSA096171@www.freebsd.org> Resent-Message-ID: <201009050520.o855KbCX095046@freefall.freebsd.org>
next in thread | raw e-mail | index | archive | help
>Number: 150296 >Category: ports >Synopsis: mod_log_sql2-dtc needs patching to update impression count >Confidential: no >Severity: serious >Priority: medium >Responsible: freebsd-ports-bugs >State: open >Quarter: >Keywords: >Date-Required: >Class: change-request >Submitter-Id: current-users >Arrival-Date: Sun Sep 05 05:20:02 UTC 2010 >Closed-Date: >Last-Modified: >Originator: Charles Sprickman >Release: 8.1 >Organization: >Environment: FreeBSD hc1.example.com 8.1-RELEASE FreeBSD 8.1-RELEASE #0: Fri Aug 27 23:53:20 EDT 2010 spork@m1.example.com:/usr/obj/usr/src/sys/HC8-64 i386 >Description: The mod_log_sql2-dtc apache sql logging module currently has a few bugs and there are various patches floating around to correct it. The problem is when it tries to increment the byte count on a request that transferred 0 bytes. The sql query generated looks like this: update dtc.http_accounting SET count_impressions = count_impressions+1, bytes_sent = bytes_sent+- WHERE domain='xxxxxxxxxx.com' AND vhost='www' AND month='11' AND year='2007'; The "bytes_sent+-" line is the problem. The latest patchset that a dtc contributor posted will not do an insert if there are 0 byte. This also incorporates a fix to the scoreboard. The patches work for me, and the author has been supplying these on the DTC mailing list and forums to DTC users. I'd simply like to get these into the official port so people need not hunt around for patches. As far as I can tell, this problem dates back to 2007 or so. If there are any questions, I'll work with the author to try and sort things out. I'd really, really like to do anything I can to help get this committed. This would also allow for http://www.freebsd.org/cgi/query-pr.cgi?pr=121134 to be closed. >How-To-Repeat: Install mod_log_sql2-dtc, configure apache to log via this module, watch the failed query log fill up. >Fix: See attached patches. Patch attached with submission follows: # This is a shell archive. Save it in a file, remove anything before # this line, and then unpack it by entering "sh file". Note, it may # create directories; files and directories will be owned by you and # have default permissions. # # This archive contains: # # . # ./patch-mod_log_sql.h.diff # ./patch-mod_log_sql_mysql.c # ./patch-mod_log_sql_pgsql.c # ./README # ./patch-mod_log_sql.c.diff # echo c - . mkdir -p . > /dev/null 2>&1 echo x - ./patch-mod_log_sql.h.diff sed 's/^X//' >./patch-mod_log_sql.h.diff << '33dbb60f286b078f6a23e231607bc5ca' X--- patch-mod_log_sql.h 2006-11-20 01:13:45.000000000 -0500 X+++ /home/spork/mod_log_sql2-dtc/files/patch-mod_log_sql.h 2010-08-20 15:55:42.000000000 -0400 X@@ -1,5 +1,5 @@ X---- mod_log_sql.h.orig Thu Feb 16 05:32:07 2006 X-+++ mod_log_sql.h Thu Feb 16 05:32:33 2006 X+--- ./mod_log_sql.h.orig 2007-08-06 00:03:47.000000000 +0800 X++++ ./mod_log_sql.h 2007-08-06 00:52:49.000000000 +0800 X @@ -67,7 +67,8 @@ X LOGSQL_TABLE_NOTES, X LOGSQL_TABLE_HEADERSOUT, X@@ -10,3 +10,12 @@ X } logsql_tabletype; X X /* All Tables */ X+@@ -96,6 +97,8 @@ X+ /* create a SQL table named table_name of table_type */ X+ logsql_table_ret (*create_table)(request_rec *r, logsql_dbconnection *db, X+ logsql_tabletype table_type, const char *table_name); X++ /* enclose an insert query with parameters to make it an insert ignore */ X++ const char * (*enclose_insert_ignore)(const char* insert_query,request_rec *r); X+ } logsql_dbdriver; X+ X+ LOGSQL_DECLARE(void) log_sql_register_driver(apr_pool_t *p, 33dbb60f286b078f6a23e231607bc5ca echo x - ./patch-mod_log_sql_mysql.c sed 's/^X//' >./patch-mod_log_sql_mysql.c << 'ae15cf4dfa69fdc3a47b16945c8a1924' X--- ./mod_log_sql_mysql.c.orig 2007-08-06 00:55:32.000000000 +0800 X+++ ./mod_log_sql_mysql.c 2007-08-06 00:52:59.000000000 +0800 X@@ -250,6 +250,10 @@ X return LOGSQL_TABLE_SUCCESS; X } X X+static char *make_mysql_ignore_insert(char * insert_query, request_rec *r) { X+ return apr_psprintf(r->pool, "insert ignore into %s ;",insert_query); X+} X+ X static const char *supported_drivers[] = {"mysql",NULL}; X static logsql_dbdriver mysql_driver = { X "mysql", X@@ -258,7 +262,8 @@ X log_sql_mysql_close, /* close DB connection */ X log_sql_mysql_escape, /* escape query */ X log_sql_mysql_query, /* insert query */ X- log_sql_mysql_create /* create table */ X+ log_sql_mysql_create, /* create table */ X+ make_mysql_ignore_insert /* insert ignore */ X }; X X LOGSQL_REGISTER(mysql) { ae15cf4dfa69fdc3a47b16945c8a1924 echo x - ./patch-mod_log_sql_pgsql.c sed 's/^X//' >./patch-mod_log_sql_pgsql.c << 'acead0aa1ae12c51d41cb95f486b2467' X--- ./mod_log_sql_pgsql.c.orig 2007-08-06 00:55:44.000000000 +0800 X+++ ./mod_log_sql_pgsql.c 2007-08-06 00:52:55.000000000 +0800 X@@ -235,6 +235,10 @@ X return LOGSQL_TABLE_SUCCESS; X } X X+static char *make_pgsql_ignore_insert(char * insert_query, request_rec *r) { X+ return apr_psprintf(r->pool, "begin ; insert into %s ; commit ;",insert_query); X+} X+ X static char *supported_drivers[] = {"pgsql",NULL}; X static logsql_dbdriver pgsql_driver = { X supported_drivers, X@@ -242,7 +246,8 @@ X log_sql_pgsql_close, /* close DB connection */ X log_sql_pgsql_escape, /* escape query */ X log_sql_pgsql_query, /* insert query */ X- log_sql_pgsql_create /* create table */ X+ log_sql_pgsql_create, /* create table */ X+ make_pgsql_ignore_insert /* insert ignore statement */ X }; X X LOGSQL_REGISTER(pgsql) { acead0aa1ae12c51d41cb95f486b2467 echo x - ./README sed 's/^X//' >./README << '3b98ea7052623e9ef9c9aad829989a61' Xdiffs for mod_log_sql2-dtc/files/: X Xpatch-mod_log_sql.c.diff Xpatch-mod_log_sql.h.diff X Xadditional patches for mod_log_sql2-dtc/files/: X Xpatch-mod_log_sql_mysql.c Xpatch-mod_log_sql_pgsql.c X XThese address an issue of incorrect byte count updates that generate Xinvalid sql queries when bytes sent is 0, and alter a scoreboard query Xto be an "insert ignore". X XPatches are from Fernando Kiernan, http://www.imagenesdigitales.com.ar/ 3b98ea7052623e9ef9c9aad829989a61 echo x - ./patch-mod_log_sql.c.diff sed 's/^X//' >./patch-mod_log_sql.c.diff << 'ab67a7058a75f13dc22240a2bd0d2847' X--- patch-mod_log_sql.c 2009-12-26 20:45:01.000000000 -0500 X+++ /home/spork/mod_log_sql2-dtc/files/patch-mod_log_sql.c 2010-08-20 16:11:02.000000000 -0400 X@@ -1,5 +1,5 @@ X---- ./mod_log_sql.c.orig 2006-11-07 02:43:23.000000000 +0000 X-+++ ./mod_log_sql.c 2009-12-27 01:39:36.463108543 +0000 X+--- mod_log_sql.c.orig 2006-11-06 23:43:23.000000000 -0300 X++++ mod_log_sql.c 2010-08-20 17:09:29.000000000 -0300 X @@ -37,6 +37,11 @@ X #define DEFAULT_HOUT_TABLE_NAME "headers_out" X #define DEFAULT_COOKIE_TABLE_NAME "cookies" X@@ -22,7 +22,18 @@ X } logsql_state; X X X-@@ -648,6 +656,9 @@ X+@@ -460,6 +468,10 @@ X+ } X+ } X+ X++static char* log_sql_insert_enclose(char* insert_close,request_rec *r) { X++ return global_config.driver->enclose_insert_ignore(insert_close,r); X++} X++ X+ /* post_config / module_init */ X+ #if defined(WITH_APACHE20) X+ static int log_sql_post_config(apr_pool_t *p, apr_pool_t *plog, apr_pool_t *ptemp, server_rec *s) X+@@ -648,6 +660,9 @@ X cls->hout_table_name = DEFAULT_HOUT_TABLE_NAME; X cls->cookie_table_name = DEFAULT_COOKIE_TABLE_NAME; X cls->preserve_file = DEFAULT_PRESERVE_FILE; X@@ -32,7 +43,7 @@ X X cls->transfer_ignore_list = apr_array_make(p, 1, sizeof(char *)); X cls->transfer_accept_list = apr_array_make(p, 1, sizeof(char *)); X-@@ -759,6 +770,15 @@ X+@@ -759,6 +774,15 @@ X if (child->cookie_table_name == DEFAULT_COOKIE_TABLE_NAME) X child->cookie_table_name = parent->cookie_table_name; X X@@ -48,7 +59,7 @@ X DO_MERGE_ARRAY(parent->transfer_ignore_list, child->transfer_ignore_list, subp); X DO_MERGE_ARRAY(parent->transfer_accept_list, child->transfer_accept_list, subp); X DO_MERGE_ARRAY(parent->remhost_ignore_list, child->remhost_ignore_list, subp); X-@@ -790,6 +810,11 @@ X+@@ -790,6 +814,10 @@ X const char *hout_tablename = cls->hout_table_name; X const char *hin_tablename = cls->hin_table_name; X const char *cookie_tablename = cls->cookie_table_name; X@@ -56,11 +67,10 @@ X + const char *scoreboard_subdomain = cls->scoreboard_subdomain; X + const char *scoreboard_table_name = cls->scoreboard_table_name; X + struct timeval now_time; X-+ X if (global_config.driver == NULL) { X return OK; X } X-@@ -1110,6 +1135,29 @@ X+@@ -1110,6 +1138,39 @@ X if (cookie_query) X safe_sql_insert(orig, LOGSQL_TABLE_COOKIES,cookie_tablename,cookie_query); X X@@ -69,28 +79,38 @@ X + { X + const char *score_query_insert; X + const char *score_query_update; X++ const char *score_query_insert_ignore; X + struct tm *ptr; X + time_t tm; X + char sql_month[60]; X + char sql_year[60]; X + char a[60]; X++ char* bytes_sent; X + X + tm = time(NULL); X + ptr = localtime(&tm); X + strftime(sql_month ,100 , "\%m",ptr); X + strftime(sql_year ,100 , "\%Y",ptr); X + X-+ score_query_insert = apr_psprintf(r->pool, "insert ignore into %s (domain,vhost,month,year,count_impressions) values ('%s','%s','%s','%s','0')" X++ score_query_insert = apr_psprintf(r->pool, " %s (domain,vhost,month,year,count_impressions) values ('%s','%s','%s','%s','0')" X + ,scoreboard_table_name,scoreboard_domain,scoreboard_subdomain,sql_month,sql_year); X-+ safe_sql_insert(orig,LOGSQL_TABLE_SCORE,"score_board",score_query_insert); X-+ score_query_update = apr_psprintf(r->pool, "update %s SET count_impressions = count_impressions+1, bytes_sent = bytes_sent+%s WHERE domain='%s' AND vhost='%s' AND month='%s' AND year='%s'" X-+ ,scoreboard_table_name,extract_bytes_sent(r, a),scoreboard_domain,scoreboard_subdomain,sql_month,sql_year); X-+ safe_sql_insert(orig,LOGSQL_TABLE_SCORE,"score_board",score_query_update); X++ // Add insert ignore into ... ; X++ // or begin; insert into ... ; commit; X++ // depending on the drive used X++ score_query_insert_ignore = log_sql_insert_enclose(score_query_insert,r); X++ X++ safe_sql_insert(orig,LOGSQL_TABLE_SCORE,"score_board",score_query_insert_ignore); X++ bytes_sent = extract_bytes_sent(r, a); X++ if (bytes_sent[0] != '-'){ X++ score_query_update = apr_psprintf(r->pool, "update %s SET count_impressions = count_impressions+1, bytes_sent = bytes_sent+%s WHERE domain='%s' AND vhost='%s' AND month='%s' AND year='%s'" X++ ,scoreboard_table_name,extract_bytes_sent(r, a),scoreboard_domain,scoreboard_subdomain,sql_month,sql_year); X++ safe_sql_insert(orig,LOGSQL_TABLE_SCORE,"score_board",score_query_update); X++ } X + } X return OK; X } X } X-@@ -1171,6 +1219,18 @@ X+@@ -1171,6 +1232,18 @@ X (void *)APR_OFFSETOF(logsql_state, cookie_table_name), RSRC_CONF, X "The database table that holds the cookie info") X , ab67a7058a75f13dc22240a2bd0d2847 exit >Release-Note: >Audit-Trail: >Unformatted:
Want to link to this message? Use this URL: <https://mail-archive.FreeBSD.org/cgi/mid.cgi?201009050518.o855IOSA096171>