www/121134: www/mod_log_sql2-dtc scoreboard problem

[Tommy Pham]

>Number:         121134
>Category:       www
>Synopsis:       www/mod_log_sql2-dtc scoreboard problem
>Confidential:   no
>Severity:       non-critical
>Priority:       low
>Responsible:    freebsd-www
>State:          open
>Quarter:        
>Keywords:       
>Date-Required:
>Class:          sw-bug
>Submitter-Id:   current-users
>Arrival-Date:   Wed Feb 27 00:00:05 UTC 2008
>Closed-Date:
>Last-Modified:
>Originator:     Tommy Pham
>Release:        FreeBSD 7.0-RC2 amd64
>Organization:
>Environment:
USER=tommy
LOGNAME=tommy
HOME=/root
MAIL=/var/mail/tommy
PATH=/sbin:/bin:/usr/sbin:/usr/bin:/usr/games:/usr/local/sbin:/usr/local/bin:/root/bin
TERM=xterm
FTP_PASSIVE_MODE=yes
BLOCKSIZE=K
SHELL=/bin/csh
SSH_CLIENT=192.168.1.15 1917 22
SSH_CONNECTION=192.168.1.15 1917 192.168.1.201 22
SSH_TTY=/dev/ttyp0
HOSTTYPE=FreeBSD
VENDOR=unknown
OSTYPE=FreeBSD
MACHTYPE=unknown
SHLVL=3
PWD=/usr/ports/www/mod_log_sql2-dtc/files
GROUP=wheel
HOST=www1.sointe.local
REMOTEHOST=
ACVS=anoncvs at anoncvs1.freebsd.org:/home/ncvs
EDITOR=vi
PAGER=more
>Description:
Is the port www/mod_log_sql2-dtc 's scoreboard broken?  I have mass
vhost on with adequate rights (insert, update, create, select) where
required - the apache error log has no error and preserve file is
empty.  Everything logs great except for the scoreboard.  It inserts:
`vhost`, `month`, `year`, `count_impressions` ok but failed to update
properly?

here's the scoreboard table sample data:

domain 	vhost 	month 	  year 	count_impressions 	bytes_sent
www 	default-domain.tld 	2 	2008 	2 	165632
www 	default-domain.tld 	2 	2008 	3 	248448
www 	default-domain.tld 	2 	2008 	1 	82816

is that how it's supposed to be?  Is it supposed to query for an
existing `domain`,`vhost`,`month`,`year`, before any inserts?  If the
required the data exists, it updates `count_impressions` and
`bytes_sent` right?  I thought the idea for this table is to see the #
of hits (ie count_impressions) and bandwidth used (bytes_sent) for the
month for each domain/vhost? If that's the case, then I think the
scoreboard is bugged because it's gonna fill up the scoreboard table
quickly with every hit/requests.

Here's the C code in file patch-mod_log_sql.c:
+                       score_query_insert = apr_psprintf(r->pool,
"insert ignore into %s (domain,vhost,month,year,count_impressions)
values ('%s','%s','%s','%s','0')"
+                              
,scoreboard_table_name,scoreboard_domain,scoreboard_subdomain,sql_month,sql_year);
+                      
safe_sql_insert(orig,LOGSQL_TABLE_SCORE,"score_board",score_query_insert);
+                       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'"
+                              
,scoreboard_table_name,extract_bytes_sent(r,
a),scoreboard_domain,scoreboard_subdomain,sql_month,sql_year);
+                      
safe_sql_insert(orig,LOGSQL_TABLE_SCORE,"score_board",score_query_update);

I don't see a select statement to check for existing data. :(  I
wouldn't mind fixing it and post the patch except my C coding sucks and
I don't know how use patch in FreeBSD :(

Also, the scoreboard table isn't created automatically with mass vhost
on with create rights.  I had to create a table named 'scoreboard' with
the these fields.

Field 	Type 	Null 	Key 	Default 	Extra
domain  varchar(20)  	YES  	MUL  	NULL
vhost 	varchar(20) 	YES 	  	NULL
month 	tinyint(3) unsigned 	YES 	  	0
year 	smallint(5) unsigned 	YES 	  	0
count_impressions 	int(10) unsigned 	YES 	  	0 
bytes_sent 	int(10) unsigned 	YES 	  	0

Running MySQL 5.1.23 + Apache 2.2.8.  Port is built w/o DBI.
>How-To-Repeat:

>Fix:


>Release-Note:
>Audit-Trail:
>Unformatted: