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

Reply via email to