Default installation with instructions followed to the letter produces errors in apache logs

[Jure Sah]

Ubuntu Server 10.04 LTS AMD64
libapache2-mod-log-sql 1.100-14build1
mysql-server 5.1.41-3ubuntu12.6
apache2 2.2.14-5ubuntu8

Following the instructions in /usr/share/doc/libapache2-mod-log-sql/manual.html with the basic settings for MySQL logging of a few vHosts, I got:

[Wed Aug 11 22:32:57 2010] [error] mysql_query returned (1)
[Wed Aug 11 22:32:57 2010] [error] table does not exist, preserving query
[Wed Aug 11 22:32:57 2010] [error] table doesn't exist...creating now
[Wed Aug 11 22:32:57 2010] [error] failed to create table: score_board

Remedied by manually executing the "create table score_board" lines from /usr/share/doc/libapache2-mod-log-sql/create_tables.sql (some of the lines there are not really useful if your webserver has more than one vhost).

Also got:
[Wed Aug 11 22:32:59 2010] [error] (13)Permission denied: attempted append of local preserve file '/etc/apache2/logs/mod_log_sql-preserve' but failed.

Remedied by giving non-root access to /etc/apache2/logs clearly.

[Jure Sah (dustwolfy)]

Further information relevant to these bugs:
http://www.howtoforge.com/forums/showthread.php?t=34255

[Jure Sah (dustwolfy)]

Still generates errors in apache logs until:

CREATE TABLE IF NOT EXISTS `scoreboard` (
  `id` int(14) NOT NULL auto_increment,
  `vhost` varchar(50) NOT NULL default '',
  `bytes_sent` int(14) NOT NULL default '0',
  `count_hosts` int(12) NOT NULL default '0',
  `count_visits` int(12) NOT NULL default '0',
  `count_status_200` int(12) NOT NULL default '0',
  `count_status_404` int(12) NOT NULL default '0',
  `count_impressions` int(18) NOT NULL default '0',
  `last_run` int(14) NOT NULL default '0',
  `month` int(4) NOT NULL default '0',
  `year` int(4) NOT NULL default '0',
  `domain` varchar(50) NOT NULL default '',
  `bytes_receive` int(14) NOT NULL default '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `vhost` (`vhost`,`month`,`year`,`domain`)
) TYPE=MyISAM;

...is used.

[czappi (news-czaplinski)]

Ubuntu Server 10.04 LTS INTEL
libapache2-mod-log-sql 1.100-14build1
mysql-server 5.1.41-3ubuntu12.6
apache2 2.2.14-5ubuntu8

[Tue Nov 23 13:20:41 2010] [error] (1)Operation not permitted: child attempted but failed to create one or more tables for 'url_fom_my_server', preserving query
[Tue Nov 23 13:20:41 2010] [error] mysql_query returned (1)
[Tue Nov 23 13:20:41 2010] [error] table does not exist, preserving query
[Tue Nov 23 13:20:41 2010] [error] table doesn't exist...creating now
[Tue Nov 23 13:20:41 2010] [error] failed to create table: score_board

[Aleksander Jerič (ninjattt)]

Package: libapache2-mod-log-sql
Architecture: i386
Version: 1.100-14build1
Ubuntu server 11.04

I had to add LogSQLPreserveFile directive with appropriate location with 666 file permission.
LogSQLPreserveFile /var/log/mod_log_sql-preserve

/usr/share/doc/libapache2-mod-log-sql/create_tables.sql has wrong table name "score_board" to work out of the box.
I had to manually rename to "scoreboard" in order to work properly.

[Aleksander Jerič (ninjattt)]

For the info: in source code libapache-mod-log-sql/debian/patches/001-mod_log_sql.c.dpatch
line 17: +#define DEFAULT_SCOREBOARD_TABLE_NAME "scoreboard"
should probably be: +#define DEFAULT_SCOREBOARD_TABLE_NAME "score_board"

And some mysql messages to confirm
36 Query insert ignore into scoreboard (domain,vhost,month,year,count_impressions) values ('www','default-domain.tld','06','2011','0')
36 Query create table if not exists `score_board

[Launchpad Janitor (janitor)]

Status changed to 'Confirmed' because the bug affects multiple users.