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