ftpStats 1.8.7 discontinued, looking for replacement

[dajm]

Hello,

Since ftpStats 1.8.7 is not supported anymore, and it seems broken on alot of new systems (so it isn't just broken for me) I'm looking for a replacement, but the problem is that ftpStats was the only software that could read pureFTPds special logfiles in "stats" format.

First time I ran the script it worked somewhat, and then it just started to complain each time I ran.


root@dolly:~/ftpstats/MySQL# ./ftpstats.pl </var/log/pure-ftpd/transfer-stats.log
INSERT INTO xfers.static (
Username, xferTime, xferDur, xferFile, xferSize, xferDirn, xferIP) VALUES

DBD::mysql::st execute failed: Not unique table/alias: 'xfers' at ./ftpstats.pl line 250, <STDIN> line 1388.

MySQL error, trying again in 5 secs

DBD::mysql::st execute failed: Not unique table/alias: 'xfers' at ./ftpstats.pl line 253, <STDIN> line 1388.

MySQL error on:
CREATE TABLE xfers (
xferID MEDIUMINT UNSIGNED AUTO_INCREMENT PRIMARY KEY NOT NULL,
Username CHAR(16) NOT NULL,
xferTime DATETIME NOT NULL,
xferDur MEDIUMINT UNSIGNED NOT NULL,
xferFile CHAR(255) NOT NULL,
xferSize INT UNSIGNED NOT NULL,
xferDirn ENUM('Up','Dn') NOT NULL,
xferCmpl ENUM('COMP','INCM','ABOR','PERM','NENT','MISC'),
xferIP INT UNSIGNED NOT NULL
) TYPE=MERGE UNION=(`xfers`,`xfers_d333_2006`,`xfers_d334_2006` ,`xfers_d335_2006`,`xfers_d336_2006`,xfers_d337_20 06)
Returned:
Not unique table/alias: 'xfers'

Added 1388 transfers
root@dolly:~/ftpstats/MySQL#


Any help would be appreciated.

[fast@ais42.net]

Repair SQL create table statement in script

...TYPE=MERGE ...

with

...ENGINE=MERGE ...

[c3man]

Hi!

I've got the same Problem!

I have change TYPE=MERGE with ENGINE=MERGE in the ftpstats.pl but it didn't work.

Everytime I ran ftpstats.pl < /var/log/pure-ftpd.log it writes the complete logfile to the SQL Server.

Please help us!

Greetings
C3man

[sju]

I think I got the same error as you. The problem seems to have something to do with a new Mysql version. The problem was that the existing-tables list had `-characters in it. ftpstats.pl doesn't expect these characters. This worked for me me:
After

# get a list of tables so we know what to create later
$existing_tables = join ' ', $db_conn->tables;

Add this:

# Fix for newer mysql versions removing `
$existing_tables =~ s/\`//g;


This removes the `characters in the existing-tables list.

[c3man]

Hello sju,

It works fine on my linux Server! Thanks!!!

Greatings

c3man

[Martin-22]

Hi, i am running on Gutsy Gibbon, and i have problem with proper running. Even if I tried solutions mentioned here, every time, when i run script "process-all-logs.sh" i get this error message:

root@jabber:~/ftpStats/MySQL# ./process-all-logs.sh
Reading /var/log/pure-ftpd/transfer1.log (481 lines).. DBD::mysql::st execute failed: You have an error in your SQL syntax; check the
manual that corresponds to your MySQL server version for the right syntax to use near '.xfers_d272_2007,ftpstats.xfers_d273_2007,ftpstat s.xfers_d279_2007,ftpstats.xfer' at line 11 at /home/martin/ftpStats/MySQL//ftpstats.pl line 245, <STDIN> line 481 (#1)
DBD::mysql::st execute failed: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '.xfers_d272_2007,ftpstats.xfers_d273_2007,ftpstat s.xfers_d279_2007,ftpstats.xfer' at line 11 at /home/martin/ftpStats/MySQL//ftpstats.pl line 245, <STDIN> line 481.
MySQL error, trying again in 5 secs
DBD::mysql::st execute failed: You have an error in your SQL syntax; check the
manual that corresponds to your MySQL server version for the right syntax to use near '.xfers_d272_2007,ftpstats.xfers_d273_2007,ftpstat s.xfers_d279_2007,ftpstats.xfer' at line 11 at /home/martin/ftpStats/MySQL//ftpstats.pl line 248, <STDIN> line 481 (#1)

Use of uninitialized value in concatenation (.) or string at
/home/martin/ftpStats/MySQL//ftpstats.pl line 251, <STDIN> line 481 (#2)
(W uninitialized) An undefined value was used as if it were already
defined. It was interpreted as a "" or a 0, but maybe it was a mistake.
To suppress this warning assign a defined value to your variables.

To help you figure out what was undefined, perl tells you what operation
you used the undefined value in. Note, however, that perl optimizes your
program and the operation displayed in the warning may not necessarily
appear literally in your program. For example, "that $foo" is
usually optimized into "that " . $foo, and the warning will refer to
the concatenation (.) operator, even though there is no . in your
program.

MySQL error on:
CREATE TABLE xfers (
xferID MEDIUMINT UNSIGNED AUTO_INCREMENT PRIMARY KEY NOT NULL,
Username CHAR(16) NOT NULL,
xferTime DATETIME NOT NULL,
xferDur MEDIUMINT UNSIGNED NOT NULL,
xferFile CHAR(255) NOT NULL,
xferSize INT UNSIGNED NOT NULL,
xferDirn ENUM('Up','Dn') NOT NULL,
xferCmpl ENUM('COMP','INCM','ABOR','PERM','NENT','MISC'),
xferIP INT UNSIGNED NOT NULL
) ENGINE=MERGE UNION=(ftpstats.ftpstats.xfers_d272_2007,ftpstats. xfers_d273_2007,ftpstats.xfers_d279_2007,ftpstats. xfers_d280_2007,ftpstats.xfers_d285_2007,ftpstats. xfers_d287_2007)
Returned:
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '.xfers_d272_2007,ftpstats.xfers_d273_2007,ftpstat s.xfers_d279_2007,ftpstats.xfer' at line 11 (#1)
Added 0 transfers


Thank for your help.

[Errow]

Using:
Ubuntu 7.10 Server Edition
mysql Ver 14.12 Distrib 5.0.45, for pc-linux-gnu (i486) using readline 5.2

I had to fix this script once more:
After

# get a list of tables so we know what to create later
$existing_tables = join ' ', $db_conn->tables;

# Fix for newer mysql versions removing `
$existing_tables =~ s/\`//g;


Add this

# Removing database name
$existing_tables =~ s/ftpstats.//g;