Opsview / MYSQL statement help

nel

Hello continuing on from my determination to get opsview up and running i have now encounted yet another issue with the setup. Seriously it has been non stop issues. ive managed to fix many of them or find workarounds but i need some guidance on the SQL statements in one of the perl scripts.

Im at the stage where i am capturing netflow data (using flavio) but are now trying to run the "netflow_aggregate.pl" file to import the captured data into MYSQL. When i run the file ive enounted issues regarding missing columns in the tables created earlier following the documentation. now i get an error:

Table: 2008_12_23
Week: 1
WeekTable: W2008_1
MonthTable: M2008_12
YearTable: Y2008
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 'and 'endtime' <=' at line 1 at /usr/local/flavio/netflow_aggregate.pl line 114.
Unable to execute query:DBI::db=HASH(0x82e17f[IMG]https://us.v-cdn.net/6030959/uploads/images/smilies/icon_cool.gif[/IMG]->err, DBI::db=HASH(0x82e17f[IMG]https://us.v-cdn.net/6030959/uploads/images/smilies/icon_cool.gif[/IMG]->errstr

From the error it seems the problem is with the "and 'endtime'" part of the following SQL statement:

$statement = "DELETE from $weekTable where starttime >= $since and endtime <= $to";
$sth = $dbh->prepare($statement);
$sth->execute or die "Unable to execute query:$dbh->err, $dbh->errstr\n";
$sth->finish;
$statement = "DELETE from $monthTable where starttime >= $since and endtime <= $to";
$sth = $dbh->prepare($statement);
$sth->execute or die "Unable to execute query:$dbh->err, $dbh->errstr\n";
$sth->finish;
$statement = "DELETE from $yearTable where starttime >= $since and endtime <= $to";
$sth = $dbh->prepare($statement);
$sth->execute or die "Unable to execute query:$dbh->err, $dbh->errstr\n";
$sth->finish;

Ive had a look and the statement looks OK to me but to be honest i dont have any SQL experiance. Anyone got any ideas on this statement?

Thanks

liven

what value is in:

$since

If if has any special characters (particullary ' or " or `)you could be crashing the mysql statement.


Try to find out what the value it and run it manually bu subbing in the value.



Also if your going to mess with the code and data base back them both up first.

liven

also to see if there are any funny values in: $since


access the database manually and do a query something like:

mysql -u <PROBABLY ROOT> -p <PASSWORD>
use <WHATEVER THE DB IS CALLED>

select * from $wektable
select * from $since
select * from $to

look for weird values in the tables

you can also use the

SHOW TABLES;


and the

DESCRIBE <table_name>


to see what type of data should be in the table.


Finally you can try to use an editor that have syntax highlighting (VIM, edit plus, gvim etc...) to see if the syntax for the query and associated perl code are missing things.

That is the easiest off the top of my head stuff I can think of... There are tons of other things to try but its hard not having the device and code at your finger tips.

nel

well according to the perl script which you have to run to import netflow data into mysql it is:

my $to = $row[0];

my $since = $row[0];

One thing i would say is this perl script that is used in the documentation doesnt seem to create the tables for the days/months/years in question. wtf!!

Honestly cant believe the amount of hassle this is considering i am following the documentation they have provided.

Anyone else find this when installing opsview w/flavio?