Hi Rik, This is because you violate the index on the table. You have to either align sql_refresh_time with sql_history or introduce an auto-increment field and make it part of the index. This is all explained in the following page:
http://wiki.pmacct.net/CustomizingTheSqlIndexes Cheers, Paolo On Tue, Apr 08, 2014 at 10:22:08AM +0000, Rik Bruggink - Fundaments B.V. wrote: > Hello List, > > I started using pmacct a while ago to monitor our traffic streams , and now i > want to segment the traffice in two different tables one with a five minute > average and one with a daily average. > > I am using this config todo so: > > ! sfacctd configuration > > daemonize: true > syslog: user > > interface: eth0 > > plugins: mysql[inbound5m], mysql[outbound5m], mysql[inbounddaily], > mysql[outbounddaily] > > sql_table_version: 7 > sql_optimize_clauses: true > sql_refresh_time: 60 > sql_dont_try_update: true > sql_use_copy: true > sql_host: hostname > sql_passwd: password > sql_db: pmacct > sql_user: pmacct > > sql_history[inbound5m]: 5m > sql_history_roundoff[inbound5m]: m > sql_table[inbound5m]: acct_v7_in_5m > aggregate[inbound5m]: dst_host > aggregate_filter[inbound5m]: vlan and (src net 10.0.0.0/8) and not (dst net > 10.0.0.0/8) > > sql_history[outbound5m]: 5m > sql_history_roundoff[outbound5m]: m > sql_table[outbound5m]: acct_v7_out_5m > aggregate[outbound5m]: src_host > aggregate_filter[outbound5m]: vlan and (src net 10.0.0.0/8) and not (dst net > 10.0.0.0/8) > > sql_history[inbounddaily]: 1d > sql_history_roundoff[inbounddaily]: d > sql_table[inbounddaily]: acct_v7_in_daily > aggregate[inbounddaily]: dst_host > aggregate_filter[inbounddaily vlan and (src net 10.0.0.0/8) and not (dst net > 10.0.0.0/8) > > sql_history[outbounddaily]: 1d > sql_history_roundoff[outbounddaily]: d > sql_table[outbounddaily]: acct_v7_out_daily > aggregate[outbounddaily]: src_host > aggregate_filter[outbounddaily]: vlan and (src net 10.0.0.0/8) and not (dst > net 10.0.0.0/8) > > sfacctd_renormalize: true > > sample mysql table structure: > > CREATE TABLE "acct_v7_out_5m" ( > "agent_id" int NOT NULL, > "class_id" char(16) NOT NULL, > "mac_src" char(17) NOT NULL, > "mac_dst" char(17) NOT NULL, > "vlan" int NOT NULL, > "as_src" int NOT NULL, > "as_dst" int NOT NULL, > "ip_src" char(45) NOT NULL, > "ip_dst" char(45) NOT NULL, > "src_port" int NOT NULL, > "dst_port" int NOT NULL, > "tcp_flags" int NOT NULL, > "ip_proto" char(6) NOT NULL, > "tos" int NOT NULL, > "packets" int NOT NULL, > "bytes" bigint NOT NULL, > "flows" int NOT NULL, > "stamp_inserted" datetime NOT NULL, > "stamp_updated" datetime DEFAULT NULL, > PRIMARY KEY > ("agent_id","class_id","mac_src","mac_dst","vlan","as_src","as_dst","ip_src","ip_dst","src_port","dst_port","ip_proto","tos","stamp_inserted"), > KEY "stamp_inserted" ("stamp_inserted"), > KEY "bytes" ("bytes"), > KEY "ip_src" ("ip_src"), > KEY "ip_dst" ("ip_dst") > ); > > the problem im facing now is that it is mentioning duplicate key for the sql > service. Is the config and table structure I am using correct, our am I > missing something? > > With kind regards, > > Rik Bruggink > > _______________________________________________ > pmacct-discussion mailing list > http://www.pmacct.net/#mailinglists _______________________________________________ pmacct-discussion mailing list http://www.pmacct.net/#mailinglists
