Hi List,
I'm new to pmacct in general but have not been able to figure out my current
issue and have read every pmacct-discussion related thread I could find as well
as RTFM several times through.
First off, I'm running pmacct v1.6.1 on CentOS7 (3.10.0-327.36.3.el7.x86_64)
with MariaDB (5.5.50). I have the basis of my desired functional system using
the following:
nfacctd.conf:
interface: eno16777984
aggregate: post_nat_src_host, post_nat_src_port, src_host, src_port,
timestamp_start
nfacctd_port: 2055
sql_host: localhost
sql_user: pmacct
sql_passwd: <pass removed>
sql_db: pmacct
sql_table: cgn
sql_optimize_clauses: true
sql_dont_try_update: true
sql_use_copy: true
sql_refresh_time: 120
nfacctd_time_new: true
plugins: mysql
cgn.schema:
use pmacct;
create table cgn (
post_nat_ip_src CHAR(15) NOT NULL,
post_nat_port_src INT(2) UNSIGNED NOT NULL,
ip_src CHAR(15) NOT NULL,
src_port INT(2) UNSIGNED NOT NULL,
timestamp_start DATETIME NOT NULL,
timestamp_start_residual DATETIME NULL,
PRIMARY KEY (post_nat_ip_src, post_nat_port_src, ip_src, src_port,
timestamp_start)
);
The problem I have is when I try to use a daily dynamic DB. I have updated the
above nfacctd.conf file to the following (these files were created for diff
purposes):
diff -U 4 nfacctd_cgn.conf nfacctd_cgn_dynamic.conf
--- nfacctd_cgn.conf 2016-12-14 17:29:52.087336297 -0500
+++ nfacctd_cgn_dynamic.conf 2016-12-14 17:30:13.782334917 -0500
@@ -5,10 +5,13 @@
sql_user: pmacct
sql_passwd: <pass removed>
sql_db: pmacct
sql_optimize_clauses: true
-sql_table: cgn
+sql_history_roundoff: h
+sql_history: 1h
+sql_table: cgn_%Y%m%d
+sql_table_schema: /usr/local/pmacct/cgn.schema
sql_dont_try_update: true
sql_use_copy: true
-sql_refresh_time: 120
+sql_refresh_time: 3600
nfacctd_time_new: true
plugins: mysql
And cgn.schema is updated to the following:
diff -U 4 cgn.schema cgn_dynamic.schema
--- cgn.schema 2016-12-14 18:00:21.207219934 -0500
+++ cgn_dynamic.schema 2016-12-14 18:01:27.089215742 -0500
@@ -1,6 +1,6 @@
use pmacct;
-create table cgn (
+create table cgn_%Y%m%d (
post_nat_ip_src CHAR(15) NOT NULL,
post_nat_port_src INT(2) UNSIGNED NOT NULL,
ip_src CHAR(15) NOT NULL,
src_port INT(2) UNSIGNED NOT NULL,
This reports an error about the SQL syntax when trying to create the table:
ERROR ( default/mysql ): The SQL server says: You have an error in your SQL
syntax; check the manual that corresponds to your MariaDB server version for
the right syntax to use near 'create table cgn_20161214 (
post_nat_ip_src CHAR(15) NOT NULL,
p' at line 3
To test implement a work around I built the table manually by filling in the
YYYYMMDD. Initially, this caused the following errors (as if the
sql_optimize_clauses directive was ignored) to be generated (I'm invoking
nfacctd using 'nfacctd -f /usr/local/pmacct/nfacctd.conf' while testing):
ERROR ( default/mysql ): Unknown column 'stamp_updated' in 'field list'
ERROR ( default/mysql ): Unknown column 'stamp_inserted' in 'field list'
I was able to fix these errors by adding to my cgn.schema:
diff -U 4 cgn.schema cgn_dynamic.schema
--- cgn.schema 2016-12-14 18:00:21.207219934 -0500
+++ cgn_dynamic.schema 2016-12-14 18:03:29.025207985 -0500
@@ -1,10 +1,12 @@
use pmacct;
-create table cgn (
+create table cgn_%Y%m%d (
post_nat_ip_src CHAR(15) NOT NULL,
post_nat_port_src INT(2) UNSIGNED NOT NULL,
ip_src CHAR(15) NOT NULL,
src_port INT(2) UNSIGNED NOT NULL,
timestamp_start DATETIME NOT NULL,
timestamp_start_residual DATETIME NULL,
+ stamp_updated DATETIME NULL,
+ stamp_inserted DATETIME NULL,
PRIMARY KEY (post_nat_ip_src, post_nat_port_src, ip_src, src_port,
timestamp_start)
);
This appears to make everything work (data is being written to the cgn_20161214
table), but I'm still seeing the same error as above complaining about the SQL
syntax for the create table statement every time a new cache starts.
Thanks in advance for any assistance. Hopefully I'm overlooking something
simple.
-Ken
_______________________________________________
pmacct-discussion mailing list
http://www.pmacct.net/#mailinglists
