Pmacct - "Unknown key: interface" , "no such column: vlan_in"

bonnietwin · 25 June 2023 18:37

Well at least someone has discovered it so it can be corrected.

As long as we keep moving forwards and keep making things better then we are improving things

tphz · 25 June 2023 18:41

No problem.
Thank you for adding the necessary information on bugzilla.

cfusco · 25 June 2023 18:43

@jon if you have the opportunity, would be nice to test the multi interface settings. If you or @tphz can test the configuration in message 11 I could add it to the wiki.

jon · 25 June 2023 18:49

That sounds good!

I am still testing the pmacct and the sql db. I have old data (before the CU 175 update), but I am not seeing new data. And that may be timing more than anything else (it only updates every x minutes). So I am waiting for a few writes…

jon · 25 June 2023 18:53

I still see the same error as above. And the db doesn’t appear to be updating. So I am going to hold for a little while.

Jun 25 13:45:01 ipfire pmacctd[24294]: INFO ( plugin3/sqlite3 ): *** Purging cache - START (PID: 24294) *** 
Jun 25 13:45:01 ipfire pmacctd[24294]: ERROR ( plugin3/sqlite3 ): no such column: vlan_in  
Jun 25 13:45:01 ipfire pmacctd[24294]: INFO ( plugin3/sqlite3 ): *** Purging cache - END (PID: 24294, QN: 0/226, ET: 0) *** 
[root@ipfire ~] #

EDIT:

may be related to pmacct issues known…

github.com/pmacct/pmacct

Netflow not working pmacct to Postgress

opened 03:03AM - 23 Jun 23 UTC

closed 10:09PM - 19 Jul 23 UTC

rohanrajnv

bug

**Description** I am trying to install nfacctd and pfacctd to export the flow d…ata to postgres. I am getting the following the error when I export the data. Config: Command: nfacctd -P pgsql -d -l 2055 -f nfacctd.conf DEBUG ( default/core ): Processing NetFlow/IPFIX flowset [262] from [192.168.30.254:3976] seqno [20311401] DEBUG ( default/core ): Processing NetFlow/IPFIX flowset [258] from [192.168.30.254:3976] seqno [20311401] DEBUG ( default/core ): Processing NetFlow/IPFIX flowset [258] from [192.168.30.254:3976] seqno [20311401] DEBUG ( default/core ): Processing NetFlow/IPFIX flowset [262] from [192.168.30.254:3976] seqno [20311401] ^C( default_pgsql/pgsql ) *** Purging queries queue *** INFO ( default_pgsql/pgsql ): *** Purging cache - START (PID: 109609) *** DEBUG ( default_pgsql/pgsql ): FAILED query follows: UPDATE acct SET packets=packets+18, bytes=bytes+14098, flows=flows+0, stamp_updated=CURRENT_TIMESTAMP(0) WHERE to_timestamp(1687484580)::Timestamp without time zone = stamp_inserted AND vlan_in=0 AND ip_src='172.217.171.202' AND as_src=0 AND as_dst=0 AND port_src=0 AND port_dst=0 AND tos=0 AND ip_proto=0 AND tag=0 AND class_id='unknown' AND mac_src='0:0:0:0:0:0' AND mac_dst='0:0:0:0:0:0' AND ip_dst='0.0.0.0' ERROR ( default_pgsql/pgsql ): ERROR: column "vlan_in" does not exist LINE 1: ...:Timestamp without time zone = stamp_inserted AND vlan_in=0 .. **Version** NetFlow Accounting Daemon, nfacctd 1.7.9-git [20230620-1 (f91700a9)] Arguments: '--enable-pgsql' '--enable-geoipv2' '--enable-debug' '--enable-l2' '--enable-traffic-bins' '--enable-bgp-bins' '--enable-bmp-bins' '--enable-st-bins' Libs: cdada 0.4.0 libpcap version 1.10.1 (with TPACKET_V3) PostgreSQL 140008 MaxmindDB 1.7.1 Plugins: memory print nfprobe sfprobe tee postgresql System: Linux 5.15.0-72-generic #79-Ubuntu SMP Wed Apr 19 08:22:18 UTC 2023 x86_64 Compiler: gcc 11.3.0

cfusco · 25 June 2023 20:21

I would remove “may” from your statement. This looks like the same bug you are observing. This is likely due to the fact that the column vlan_in does not exist or it is misspelled in the table acct, which triggers the error.

tphz · 25 June 2023 20:34

I wonder if the information in the following link will be helpful

github.com

pmacct/pmacct/blob/master/sql/README.sqlite3

See how to configure and compile pmacct for SQLite use in the "Configuring
pmacct for compilation and installing" chapter of QUICKSTART.

To create the database and the table you have to execute the following scripts.
Remember that SQLite - unlike MySQL and PostgreSQL - does not endorse concepts
of authentication (username and password couple) and permissions which are
embedded into the filesystem instead. The database filename '/tmp/pmacct.db'
is just a trivial example: you are free to build the database wherever fits
better for you on the system (e.g., to follow a partitioning scheme, impose 
specific permissions, etc.). 

- To create v1 tables:
  * sqlite3 /tmp/pmacct.db < pmacct-create-table.sqlite3

- To use v1 tables:
  * data will be available in 'acct' table of 'pmacct' DB.
  * Add 'sql_table_version: 1' line to your configuration.

Similarly, v2 to v9 tables:

This file has been truncated. show original

edit

...
Aggregation primitive => SQL table field
...
...
* in_vlan => vlan_in (INT(4) NOT NULL DEFAULT 0)
...

edit2

NOTE: mind to specify EVERYTIME which SQL table version you
intend to adhere to by using the following config directives:
 
* sql_table_version: [ 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 ]
* sql_table_type: [ bgp ]
 
NOTE: specifying a non-documented SQL table profile will result
in an non-determined behaviour. Unless this will create crashes
to the application, such situations will not be supported.

cfusco · 25 June 2023 20:54

In the log of the last release, there is this bit:

Introduced support for in/out VLAN support for sfacctd. To be
savy, ‘in_vlan’ and ‘vlan’ were muxed onto the same primitive
depending on the daemon being used. Thanks to Jim Westfall
( @jwestfall69 ) for this contribution.

The developer likely introduced a bug there.

@jon maybe a downgrade until the bug is fixed is the best option.

jon · 25 June 2023 20:57

Thank you!

And sql_table_version - this one I have.
and sql_table_type - this one I am missing.

FYI - I think the issue is related to an error in one of the pmacct source files. I entered a bug in their issues area.

github.com/pmacct/pmacct

pmacct 1.7.8 - no such column: vlan_in

opened 08:48PM - 25 Jun 23 UTC

JonMurphy

**Description** What's the issue? Did something go wrong? Would you like someth…ing new to exist? pmacct stopped placing data into sqlite. I see lots of these errors: ``` Jun 25 13:45:01 ipfire pmacctd[24294]: INFO ( plugin3/sqlite3 ): *** Purging cache - START (PID: 24294) *** Jun 25 13:45:01 ipfire pmacctd[24294]: ERROR ( plugin3/sqlite3 ): no such column: vlan_in Jun 25 13:45:01 ipfire pmacctd[24294]: INFO ( plugin3/sqlite3 ): *** Purging cache - END (PID: 24294, QN: 0/226, ET: 0) *** ``` This happen after the upgrade of pmacct from from version 1.7.6 to 1.7.8. I do not have `vlan_in` in my pmacct.conf file. And no `vlan_in` in sqlite. I turned on debug in the conf file and now see this in the logs: ``` Jun 25 15:30:01 ipfire pmacctd[1776]: INFO ( plugin3/sqlite3 ): *** Purging cache - START (PID: 1776) *** Jun 25 15:30:01 ipfire pmacctd[1776]: DEBUG ( plugin3/sqlite3 ): FAILED query follows: UPDATE acct SET packets=packets+797, bytes=bytes+171373, stamp_updated=DATETIME('now', 'localtime') WHERE DATETIME(1687669200, 'unixepoch', 'localtime') = stamp_inserted AND mac_src='nn:nn:b9:5a:b9:db' AND mac_dst='nn:nn:5e:00:00:fb' AND vlan_in=0 AND ip_src='192.168.60.1' AND ip_dst='224.0.0.251' AND src_port=0 AND dst_port=0 AND ip_proto='0' Jun 25 15:30:01 ipfire pmacctd[1776]: ERROR ( plugin3/sqlite3 ): no such column: vlan_in Jun 25 15:30:01 ipfire pmacctd[1776]: DEBUG ( plugin3/sqlite3 ): FAILED query follows: UPDATE acct SET packets=packets+217, bytes=bytes+85483, stamp_updated=DATETIME('now', 'localtime') WHERE DATETIME(1687669200, 'unixepoch', 'localtime') = stamp_inserted AND mac_src='nn:nn:b9:5a:b9:db' AND mac_dst='nn:nn:86:42:f8:10' AND vlan_in=0 AND ip_src='nn.nn.27.38' AND ip_dst='192.168.60.212' AND src_port=0 AND dst_port=0 AND ip_proto='0' Jun 25 15:30:01 ipfire pmacctd[1776]: ERROR ( plugin3/sqlite3 ): no such column: vlan_in Jun 25 15:30:01 ipfire pmacctd[1776]: DEBUG ( plugin3/sqlite3 ): FAILED query follows: UPDATE acct SET packets=packets+256, bytes=bytes+44502, stamp_updated=DATETIME('now', 'localtime') WHERE DATETIME(1687669200, 'unixepoch', 'localtime') = stamp_inserted AND mac_src='nn:nn:86:42:f8:10' AND mac_dst='nn:nn:b9:5a:b9:db' AND vlan_in=0 AND ip_src='192.168.60.212' AND ip_dst='nn.nn.27.38' AND src_port=0 AND dst_port=0 AND ip_proto='0' Jun 25 15:30:01 ipfire pmacctd[1776]: ERROR ( plugin3/sqlite3 ): no such column: vlan_in ``` lots of `vlan_in`. IMHO - this seems similar to: https://github.com/pmacct/pmacct/issues/695 **Version** ``` [root@ipfire ~] # pmacct -V pmacct IMT plugin client, pmacct 1.7.8-git (RELEASE) ``` **Appreciation** Please consider starring this project to boost our reach on github! already done!

g70p · 25 June 2023 20:57

verify typos or errors at the config file: here some help from GPT:

Missing colon after “syslog: daemon”: Update the line “syslog: daemon” to “syslog: daemon:” by adding a colon at the end. This ensures the syntax is correct.
Missing colon after “daemonize: true”: Update the line “daemonize: true” to “daemonize: true:” by adding a colon at the end. This ensures the syntax is correct.
Missing colon after “debug: false”: Update the line “debug: false” to “debug: false:” by adding a colon at the end. This ensures the syntax is correct.
Missing colon after “promisc: true”: Update the line “promisc: true” to “promisc: true:” by adding a colon at the end. This ensures the syntax is correct.
Missing colon after “interface: green0”: Update the line “interface: green0” to “interface: green0:” by adding a colon at the end. This ensures the syntax is correct.
Missing space after the comment in the first line: Update the first line from “! Pmacctd configuration file for IPFire environment” to "! Pmacctd configuration file for IPFire environment " (add a space after “environment”).
Regards

despite coloms are optional copy paste this. It might be correct without Tabs spaces or other typos:

! Pmacctd configuration file for IPFire environment

syslog daemon
daemonize true
debug false
promisc true
interface green0


! "plugin1" plugin configuration

plugins memory[plugin1]

plugin_buffer_size[plugin1] 102400
plugin_pipe_size[plugin1] 10240000

imt_mem_pools_number 256
imt_path[plugin1] /var/spool/pmacct/plugin1.pipe

aggregate[plugin1] src_host, src_port, src_mac, dst_host, dst_port, dst_mac, proto
aggregate_filter[plugin1] ip

tphz · 25 June 2023 21:10

Test1

Contents of pmacct.conf file

!
! Pmacctd configuration file for IPFire environment
!

syslog: daemon
daemonize: true
debug: false
promisc: true
!pcap_interface: green0
pcap_interfaces_map: /etc/pmacct/interfaces.map


!
! "plugin1" plugin configuration
!
plugins: memory[plugin1]

plugin_buffer_size[plugin1]: 102400
plugin_pipe_size[plugin1]: 10240000

imt_mem_pools_number: 256
imt_path[plugin1]: /var/spool/pmacct/plugin1.pipe

aggregate[plugin1]: src_host, src_port, src_mac, dst_host, dst_port, dst_mac, proto
aggregate_filter[plugin1]: ip

Contents of interfaces.map file

ifname=green0 ifindex=100 direction=in
ifname=blue0 ifindex=101 direction=in

obraz

pmacct IMT plugin client, pmacct 1.7.8-git (RELEASE)

[root@ipfire175t ~]# /etc/init.d/pmacct restart
Stopping the pmacctd daemon...                                                                          [  OK  ]
Starting the pmacctd daemon...                                                                            [  OK  ]
[root@ipfire175t ~]#


[root@ipfire175t ~]# pmacct -p /var/spool/pmacct/plugin1.pipe -s
SRC_MAC            DST_MAC            SRC_IP                                         DST_IP                                         SRC_PORT  DST_PORT  PROTOCOL    PACKETS               BYTES
0a:00:27:00:00:1c  ff:ff:ff:ff:ff:ff  192.168.233.254                                192.168.233.255                                137       137       udp         3                     234
0a:00:27:00:00:1c  ff:ff:ff:ff:ff:ff  192.168.233.254                                192.168.233.255                                138       138       udp         1                     229
0a:00:27:00:00:0f  ff:ff:ff:ff:ff:ff  192.168.232.254                                192.168.232.255                                137       137       udp         3                     234
0a:00:27:00:00:0f  08:00:27:34:cb:83  192.168.232.254                                192.168.232.1                                  19334     222       tcp         324                   25200
0a:00:27:00:00:1c  01:00:5e:7f:ff:fa  192.168.233.254                                239.255.255.250                                59288     3702      udp         49                    33516
0a:00:27:00:00:0f  08:00:27:34:cb:83  192.168.232.254                                192.168.232.1                                  19939     444       tcp         211                   60101
0a:00:27:00:00:0f  08:00:27:34:cb:83  192.168.232.254                                192.168.232.1                                  20040     444       tcp         91                    25847
0a:00:27:00:00:0f  08:00:27:34:cb:83  192.168.232.254                                192.168.232.1                                  19849     444       tcp         150                   43059
0a:00:27:00:00:0f  01:00:5e:7f:ff:fa  192.168.232.254                                239.255.255.250                                59288     3702      udp         49                    33516
0a:00:27:00:00:0f  ff:ff:ff:ff:ff:ff  192.168.232.254                                192.168.232.255                                138       138       udp         1                     229

jon · 25 June 2023 21:30

@iptom (or anyone else),

Do you see the vlan-in error in your messages log?

 ipfire pmacctd[24294]: ERROR ( plugin3/sqlite3 ): no such column: vlan_in

Jon

tphz · 25 June 2023 21:38

I have not yet configured pmacct + sqlite.
I am in the process of doing it.

tphz · 25 June 2023 22:46

After the basic configuration described in the link below
https://wiki.ipfire.org/addons/pmacct/pmacct_sqlite3

 grep "vlan_in" /var/log/messages
Jun 26 00:30:01 ipfire175t pmacctd[4637]: ERROR ( plugin2/sqlite3 ): no such column: vlan_in  
Jun 26 00:45:01 ipfire175t pmacctd[9679]: ERROR ( plugin2/sqlite3 ): no such column: vlan_in

tphz · 25 June 2023 23:26

I did some test/experiment

I created a new file

/usr/share/pmacct/sql/pmacct-create-table_v1_fix.sqlite3

with the content

DROP TABLE IF EXISTS acct;
CREATE TABLE acct (
    mac_src CHAR(17) NOT NULL DEFAULT '0:0:0:0:0:0',
    mac_dst CHAR(17) NOT NULL DEFAULT '0:0:0:0:0:0',
    ip_src CHAR(45) NOT NULL DEFAULT '0.0.0.0',
    ip_dst CHAR(45) NOT NULL DEFAULT '0.0.0.0',
    src_port INT(4) NOT NULL DEFAULT 0,
    dst_port INT(4) NOT NULL DEFAULT 0,
    ip_proto CHAR(6) NOT NULL DEFAULT 0,
    packets INT NOT NULL,
    bytes BIGINT NOT NULL,
    stamp_inserted DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00',
    stamp_updated DATETIME,
    vlan_in INT(4) NOT NULL DEFAULT 0,
    PRIMARY KEY (mac_src, mac_dst, ip_src, ip_dst, src_port, dst_port, ip_proto, stamp_inserted)
);

with the added line
vlan_in INT(4) NOT NULL DEFAULT 0,

then command

sqlite3 /var/spool/pmacct/pmacct_sqlitev1_fix.db < /usr/share/pmacct/sql/pmacct-create-table_v1_fix.sqlite3

next I edited the file

/etc/pmacct/pmacct.conf

!
! "plugin2" plugin configuration
!
plugins: sqlite3[plugin2]

sql_db[plugin2]: /var/spool/pmacct/pmacct_sqlitev1_fix.db
sql_table_version[plugin2]: 1
sql_history[plugin2]: 60m
sql_refresh_time[plugin2]: 15
sql_history_roundoff[plugin2]: m

aggregate[plugin2]: src_host, src_port, src_mac, dst_host, dst_port, dst_mac, proto
aggregate_filter[plugin2]: ip

notice:
sql_refresh_time[plugin2]: 15 for test only

[root@ipfire175t ~]# sqlite3 /var/spool/pmacct/pmacct_sqlitev1_fix.db
SQLite version 3.41.2 2023-03-22 11:56:21
Enter ".help" for usage hints.
sqlite> .mode columns
sqlite> .headers ON
sqlite> select * from acct LIMIT 10;
mac_src            mac_dst            ip_src           ip_dst         src_port  dst_port  ip_proto  packets  bytes  stamp_inserted       stamp_updated        vlan_in
-----------------  -----------------  ---------------  -------------  --------  --------  --------  -------  -----  -------------------  -------------------  -------
0a:00:27:00:00:0f  08:00:27:34:cb:83  192.168.232.254  192.168.232.1  19334     222       tcp       421      30540  2023-06-26 01:00:00  2023-06-26 01:16:01  0      
0a:00:27:00:00:0f  08:00:27:34:cb:83  192.168.232.254  192.168.232.1  23177     444       tcp       84       23688  2023-06-26 01:00:00  2023-06-26 01:15:01  0      
0a:00:27:00:00:0f  08:00:27:34:cb:83  192.168.232.254  192.168.232.1  23266     444       tcp       75       21151  2023-06-26 01:00:00  2023-06-26 01:16:01  0      
sqlite> .quit

edit

New vlan_in errors in messages log did not appear

ummeegge · 26 June 2023 10:32

Hi all,

@jon for a fast one according to the “Unknown key: interface”, in 1.7.6 (old Pmacct version on IPFire which accepted ‘interface’), “interface:” has been marked in the source code as “Legacy Key” → https://github.com/pmacct/pmacct/blob/1.7.6/src/cfg.c#L73 whereby in 1.7.8 it disappeared completely → https://github.com/pmacct/pmacct/blob/1.7.8/src/cfg.c that´s why the key parsing → https://github.com/pmacct/pmacct/blob/master/src/cfg.c#L728 delivers this warning but also grabs only the first listed interface (green0 in that case).

If you search through the “interface” entries in cfg.c, only “pcap_interface” appears for me as a reliable substitution for the old “interface:” line in pmacct.conf but also as a usable key for a basic IPFire configuration since there is no collective (a configuration for all) support for Nfacctd, Telemetry, Sfacctd, BGP nor BMP and even if for some, the configuration will be much more extensive.

Short conclusion for a fast overview: “interface:” should be replaced by “pcap_interface:” where you can use green, blue, red and orange (only one of them) and for multiple interfaces the “interfaces.map” needs to be (as before) integrated.

Fast tests:

If the “interface:” line is completely deleted, no warning appears and green0 will be captured.
If “interface: green0” will be used the above mentioned warning appears and green0 willbe captured.
If “interface: blue0” will be used the same warning appears but only green0 will be captured.
If “pcap_interface:” will be used, the configured interface will be captured.
If “interfaces.map” will be used, multiple configured interfaces will be captured, no need for “pcap_interfaces” nor “pcap_interfaces_map:”

According to your “vlan_in” problem, i think a solution is to modify the SQL template like @tphz → Pmacct - "Unknown key: interface" , "no such column: vlan_in" - #27 by tphz did it.

IMHO the Pmacct communication according to fundamental (configuration keys) update changes is worthy of improvement.

For the first. Best,

Erik

cfusco · 26 June 2023 10:48

@ummeegge If it is possible, could you check whether the content of message 11 can be added to the wiki documentation?

ummeegge · 26 June 2023 11:17

Hello @cfusco,

you statements are correct according to interfaces.map even the ‘direction’ key might need also some further explanation since it is also possible to set two directions ‘in’ and ‘out’ for the same interface or even to leave it blank. There has been some changes according to this → pcap_interfaces_map in pmacctd doesn't work with multiple items on same interface · Issue #531 · pmacct/pmacct · GitHub but i haven´t checked them. Also the ‘pretag.map’ → https://github.com/pmacct/pmacct/blob/master/examples/pretag.map.example might be interesting in this topic ?!

The last paragraph (pcap_interface:) from your statement should not be in the wiki (may until an update changes this) but be changed in the configuration file with another IPFire update in my opinion.

Best,

Erik

jon · 26 June 2023 16:47

Thank you @ummeegge & @tphz!

Keep in mind adding the column to a newly created database is fairly easy. But adding to an existing database may not be. I think the sqlite command is alter table.

Time to read up adding a column to an existing database!

jon · 26 June 2023 17:09

forgot to comment on this: It seems pmacct change something and the alias is gone. This is probably something to be raised in the GitHub pmacct “Issues” section.