Exception happens when nprobe dump flows into clickhouse

[ioesoft]

Environment:

OS name: Red Hat Enterprise Linux Server release 7.9 (Maipo) OS version: 7.9 Architecture: x86_64 nprobe version: v.10.7.240827(latest dev version) clickhouse server version: ClickHouse server version 20.8.3 revision 54438

What happened: I want a nporbe to dump flows into clickhouse. But, there is parse exception when inserting flows to mysql DB.

Feb 5 12:11:01 localhost nprobe: Code: 27, e.displayText() = DB::Exception: Cannot parse input: expected '|' before: '.168.0.41|192.168.0.255|137|137|::|::|0|4|17|10|6|1170|15|0|0|1738724886|1738724986|0|0|0|0|0.0.0.0|0|1|42051|192.168.0.77|0|13432236|0.000|0.000|0.000|0|0|0|0|': (at row 1)

How did you reproduce it?

Install a nprobe and a clickhouse on the same machine. Set the following configuration as per your guide like below: https://www.ntop.org/nprobe/netflow-ipfix-at-scale-comparing-nprobe-clickhouse-vs-nprobe-ntopng/ <nprobe.conf> -I=nProbe --zmq="tcp://192.168.0.30:5556" --clickhouse="127.0.0.1:ntop::default:" --zmq-probe-mode -i=enp3s0 -n=none -T="@NTOPNG@ %JA3C_HASH %JA3S_HASH %SRC_AS %DST_AS %SRC_AS_MAP %DST_AS_MAP %MAX_IP_PKT_LEN %ICMP_TYPE %FLOW_END_REASON %APPL_LATENCY_MS %L7_PROTO_RISK %L7_PROTO_RISK_NAME %L7_RISK_SCORE %FLOW_VERDICT %L7_RISK_INFO %SMTP_MAIL_FROM %SMTP_RCPT_TO %HTTP_X_FORWARDED_FOR %CLIENT_TCP_FLAGS %SERVER_TCP_FLAGS" -b=2 -f="ip proto not 50"

Debug Information: Here is the log snippet on nprobe about the exception:

Feb 4 17:30:21 localhost nProbe[25177]: [nprobe.c:8346] Welcome to nProbe v.10.7.240827 for x86_64-unknown-linux-gnu with native PF_RING acceleration Feb 4 17:30:21 localhost nProbe[25177]: [nprobe.c:8368] Enterprise M Edition running on CentOS Linux release 7.9.2009 (Core) Feb 4 17:30:21 localhost nProbe[25177]: [nprobe.c:8376] Current limits [16 ZMQ exporters][16 collector devices] Feb 4 17:30:21 localhost nProbe[25177]: [nprobe.c:8391] SystemId: L1A7CAD4C9206A1D8--U1A7CAD4CFCCA4F5C--OL Feb 4 17:30:21 localhost nProbe[25177]: [nprobe.c:8395] Tracing enabled Feb 4 17:30:21 localhost nProbe[25177]: [nprobe.c:8484] Sample rate [packet: 1][flow collection/export: 1/1] Feb 4 17:30:21 localhost nProbe[25177]: [nprobe.c:8549] Unique instance identifier (UUID) 6BA3D15F-AFB8-A109-B003-511322033CF3 Feb 4 17:30:21 localhost nProbe[25177]: [plugin.c:260] Initializing Custom Fields Feb 4 17:30:21 localhost nProbe[25177]: [customPlugin.c:96] Initialized Custom plugin Feb 4 17:30:21 localhost nProbe[25177]: [plugin.c:260] Initializing MySQL DB Feb 4 17:30:21 localhost nProbe[25177]: [dbPlugin.c:191] Initializing DB plugin Feb 4 17:30:21 localhost nProbe[25177]: [dbPlugin.c:240] WARNING: [ClickHouse] Discarding -P Feb 4 17:30:21 localhost nProbe[25177]: [dbPlugin.c:251] [ClickHouse] Dumping flows in /tmp/clickhouse.60mNZx Feb 4 17:30:21 localhost nProbe[25177]: [dbPlugin.c:314] Attempting to connect to database as [host: 127.0.0.1][dbname: ntop][table prefix: ][user: default][pwd: ] Feb 4 17:30:21 localhost nProbe[25177]: [database.c:42] MySQL initialized succesfully Feb 4 17:30:21 localhost nProbe[25177]: [database.c:64] Successfully connected to MySQL [host:dbname:user:passwd]=[127.0.0.1@9004:ntop:default:] Feb 4 17:30:21 localhost nProbe[25177]: [database.c:78] [SQL] CREATE DATABASE IF NOT EXISTS ntop Feb 4 17:30:21 localhost nProbe[25177]: [plugin.c:260] Initializing DHCP Protocol

Feb 5 12:11:01 localhost nProbe[25325]: [dbPlugin.c:105] Executing cat /tmp/clickhouse.yc3bLZ/20250205_121008.flows | clickhouse-client --host "127.0.0.1" --user "default" --password "" -d ntop --format_csv_delimiter="|" --query="INSERT INTO flows (IN_SRC_MAC,OUT_DST_MAC,INPUT_SNMP,OUTPUT_SNMP,SRC_VLAN,IPV4_SRC_ADDR,IPV4_DST_ADDR,L4_SRC_PORT,L4_DST_PORT,IPV6_SRC_ADDR,IPV6_DST_ADDR,SRC_TOS,DST_TOS,IP_PROTOCOL_VERSION,PROTOCOL,L7_PROTO,L7_CONFIDENCE,IN_BYTES,IN_PKTS,OUT_BYTES,OUT_PKTS,FIRST_SWITCHED,LAST_SWITCHED,CLIENT_TCP_FLAGS,SERVER_TCP_FLAGS,L7_PROTO_RISK,L7_RISK_SCORE,EXPORTER_IPV4_ADDRESS,DIRECTION,SAMPLING_INTERVAL,TOTAL_FLOWS_EXP,NPROBE_IPV4_ADDRESS,NPROBE_INSTANCE_NAME,FLOW_SOURCE,JA4C_HASH,UNIQUE_SOURCE_ID,CLIENT_NW_LATENCY_MS,SERVER_NW_LATENCY_MS,APPL_LATENCY_MS,TCP_WIN_MAX_IN,TCP_WIN_MAX_OUT,OOORDER_IN_PKTS,OOORDER_OUT_PKTS,RETRANSMITTED_IN_PKTS,RETRANSMITTED_OUT_PKTS,SRC_FRAGMENTS,DST_FRAGMENTS,L7_INFO,DNS_QUERY,DNS_QUERY_TYPE,DNS_RET_CODE,HTTP_URL,HTTP_SITE,HTTP_METHOD,HTTP_RET_CODE,TLS_SERVER_NAME,BITTORRENT_HASH,HTTP_USER_AGENT,L7_RISK_INFO) FORMAT CSV" Feb 5 12:11:01 localhost systemd: Started Session 104577 of user root. Feb 5 12:11:01 localhost nprobe: Code: 27, e.displayText() = DB::Exception: Cannot parse input: expected '|' before: '.168.0.41|192.168.0.255|137|137|::|::|0|4|17|10|6|1170|15|0|0|1738724886|1738724986|0|0|0|0|0.0.0.0|0|1|42051|192.168.0.77|0|13432236|0.000|0.000|0.000|0|0|0|0|': (at row 1) Feb 5 12:11:01 localhost nprobe: Row 1: Feb 5 12:11:01 localhost nprobe: Column 0, name: IN_SRC_MAC, type: String, parsed text: "28:D0:EA:C9:22:7D" Feb 5 12:11:01 localhost nprobe: Column 1, name: OUT_DST_MAC, type: String, parsed text: "FF:FF:FF:FF:FF:FF" Feb 5 12:11:01 localhost nprobe: Column 2, name: INPUT_SNMP, type: UInt32, parsed text: "3" Feb 5 12:11:01 localhost nprobe: Column 3, name: OUTPUT_SNMP, type: UInt32, parsed text: "3" Feb 5 12:11:01 localhost nprobe: Column 4, name: SRC_VLAN, type: UInt16, parsed text: "192" Feb 5 12:11:01 localhost nprobe: ERROR: garbage after UInt16: ".168.0.41|" Feb 5 12:11:01 localhost nprobe: , Stack trace (when copying this message, always include the lines below): Feb 5 12:11:01 localhost nprobe: 0. Poco::Exception::Exception(std::__1::basic_string<char, std::__1::char_traits, std::__1::allocator > const&, int) @ 0x13cd24bc in /usr/bin/clickhouse Feb 5 12:11:01 localhost nprobe: 1. DB::Exception::Exception(std::__1::basic_string<char, std::__1::char_traits, std::__1::allocator > const&, int) @ 0xa4346c9 in /usr/bin/clickhouse Feb 5 12:11:01 localhost nprobe: 2. ? @ 0x9b5a0ca in /usr/bin/clickhouse Feb 5 12:11:01 localhost nprobe: 3. ? @ 0x11300a1d in /usr/bin/clickhouse Feb 5 12:11:01 localhost nprobe: 4. DB::CSVRowInputFormat::readRow(std::__1::vector<COWDB::IColumn::mutable_ptrDB::IColumn, std::__1::allocator<COWDB::IColumn::mutable_ptrDB::IColumn > >&, DB::RowReadExtension&) @ 0x11301e19 in /usr/bin/clickhouse Feb 5 12:11:01 localhost nprobe: 5. DB::IRowInputFormat::generate() @ 0x11845449 in /usr/bin/clickhouse Feb 5 12:11:01 localhost nprobe: 6. DB::ISource::work() @ 0x112719d7 in /usr/bin/clickhouse Feb 5 12:11:01 localhost nprobe: 7. DB::InputStreamFromInputFormat::readImpl() @ 0x11245aa5 in /usr/bin/clickhouse Feb 5 12:11:01 localhost nprobe: 8. DB::IBlockInputStream::read() @ 0x10a7675d in /usr/bin/clickhouse

Feb 5 12:11:01 localhost nprobe: 9. DB::ParallelParsingBlockInputStream::parserThreadFunction(std::__1::shared_ptrDB::ThreadGroupStatus, unsigned long) @ 0x114e7ec8 in /usr/bin/clickhouse Feb 5 12:11:01 localhost nprobe: 10. ? @ 0x114e8b10 in /usr/bin/clickhouse Feb 5 12:11:01 localhost nprobe: 11. ThreadPoolImpl::worker(std::__1::__list_iterator<ThreadFromGlobalPool, void*>) @ 0xa43d6ad in /usr/bin/clickhouse Feb 5 12:11:01 localhost nprobe: 12. ThreadFromGlobalPool::ThreadFromGlobalPool<void ThreadPoolImpl::scheduleImpl(std::__1::function<void ()>, int, std::__1::optional)::'lambda1'()>(void&&, void ThreadPoolImpl::scheduleImpl(std::__1::function<void ()>, int, std::__1::optional)::'lambda1'()&&...)::'lambda'()::operator()() const @ 0xa43dd93 in /usr/bin/clickhouse Feb 5 12:11:01 localhost nprobe: 13. ThreadPoolImplstd::__1::thread::worker(std::__1::__list_iterator<std::__1::thread, void*>) @ 0xa43cc4d in /usr/bin/clickhouse Feb 5 12:11:01 localhost nprobe: 14. ? @ 0xa43b3ff in /usr/bin/clickhouse Feb 5 12:11:01 localhost nprobe: 15. start_thread @ 0x8105 in /usr/lib64/libpthread-2.17.so Feb 5 12:11:01 localhost nprobe: 16. __clone @ 0xfeb2d in /usr/lib64/libc-2.17.so Feb 5 12:11:01 localhost nprobe: (version 20.8.3.18) Feb 5 12:11:01 localhost nprobe: Code: 27. DB::Exception: Cannot parse input: expected '|' before: '.168.0.41|192.168.0.255|137|137|::|::|0|4|17|10|6|1170|15|0|0|1738724886|1738724986|0|0|0|0|0.0.0.0|0|1|42051|192.168.0.77|0|13432236|0.000|0.000|0.000|0|0|0|0|': (at row 1) Feb 5 12:11:01 localhost nprobe: Row 1: Feb 5 12:11:01 localhost nprobe: Column 0, name: IN_SRC_MAC, type: String, parsed text: "28:D0:EA:C9:22:7D" Feb 5 12:11:01 localhost nprobe: Column 1, name: OUT_DST_MAC, type: String, parsed text: "FF:FF:FF:FF:FF:FF" Feb 5 12:11:01 localhost nprobe: Column 2, name: INPUT_SNMP, type: UInt32, parsed text: "3" Feb 5 12:11:01 localhost nprobe: Column 3, name: OUTPUT_SNMP, type: UInt32, parsed text: "3" Feb 5 12:11:01 localhost nprobe: Column 4, name: SRC_VLAN, type: UInt16, parsed text: "192" Feb 5 12:11:01 localhost nprobe: ERROR: garbage after UInt16: ".168.0.41|" Feb 5 12:11:01 localhost nProbe[25325]: [dbPlugin.c:111] Imported /tmp/clickhouse.yc3bLZ/20250205_121008.flows Feb 5 12:11:02 localhost nProbe[25325]: [nprobe.c:11630] {"iface": {"name":"enp3s0","speed":100,"ip":"192.168.0.77"},"probe": {"version":"10.7.240827","osname":"CentOS Linux release 7.9.2009 (Core)","license":"Permanent license","edition":"Enterprise M","maintenance":"Until Wed Mar 5 18:14:54 2025 [28 days left]","ip":"192.168.0.77","public_ip":"183.99.7.72","uuid":"6BA3D15F-AFB8-A109-B003-511322033CF3","unique_source_id":13432236},"mode":"packet_collection","capture_interface":"enp3s0","time":1738725062,"bytes":452111254,"packets":664315,"packet_drops":0,"avg": {"bps":7383,"pps":3},"sampling": {"pkt_rate":1,"collection_rate":1,"flow_export_rate":1},"drops": {"export_queue_too_long":0,"too_many_flows":0,"elk_flow_drops":0,"sflow_pkt_sample_drops":0,"flow_collection_drops":0,"flow_collection_udp_socket_drops":0},"timeout": {"lifetime":120,"idle":60,"collected_lifetime":0},"flow_collection": {"nf_ipfix_flows":0,"sflow_samples":0,"exporters": {}},"zmq": {"num_flow_exports":19240,"num_zmq_exporters":1}}

<20250205_122501.flows.temp on clickhouse> BA:45:E6:C8:D6:2E|33:33:00:00:00:16|3|3|0.0.0.0|0.0.0.0|fe80::8e6:1fde:f82e:c0a1|ff02::16|0|6|58|102|6|460|5|0|0|1738725780|1738725899|0|0|0|0|0.0.0.0|0|1|42660|192.168.0.77|0|13432236|0.000|0.000|0.000|0|0|0|0|0|0|0|0|||0|0||||0|||| 28:C5:D2:00:14:23|01:00:5E:00:00:FB|3|3|192.168.0.195|224.0.0.251|5353|5353|::|::|0|4|17|8|6|1485|6|0|0|1738725842|1738725842|0|0|0|0|0.0.0.0|0|1|42661|192.168.0.77|0|13432236|0.000|0.000|0.000|0|0|0|0|0|0|0|0|_dosvc._tcp.local||0|0||||0|||| 28:C5:D2:00:14:23|33:33:00:00:00:FB|3|3|0.0.0.0|0.0.0.0|5353|5353|fe80::127f:fe61:ef29:7011|ff02::fb|0|6|17|8|6|1605|6|0|0|1738725842|1738725843|0|0|0|0|0.0.0.0|0|1|42662|192.168.0.77|0|13432236|0.000|0.000|0.000|0|0|0|0|0|0|0|0|_dosvc._tcp.local||0|0||||0|||| 00:0C:29:E6:F0:E0|01:00:5E:00:00:FB|3|3|192.168.0.100|224.0.0.251|::|::|0|4|2|82|6|32|1|0|0|1738725849|1738725849|0|0|0|0|0.0.0.0|0|1|42663|192.168.0.77|0|13432236|0.000|0.000|0.000|0|0|0|0|0|0|0|0|||0|0||||0|||| 58:86:94:29:2E:D7|01:00:5E:7F:FF:FA|3|3|192.168.0.1|239.255.255.250|35860|1900|::|::|0|4|17|12|6|7316|16|0|0|1738725790|1738725850|0|0|0|0|0.0.0.0|0|1|42664|192.168.0.77|0|13432236|0.000|0.000|0.000|0|0|0|0|0|0|0|0|||0|0||||0|||| 00:0C:29:A5:2D:D3|01:00:5E:00:00:FB|3|3|192.168.0.224|224.0.0.251|::|::|0|4|2|82|6|32|1|0|0|1738725853|1738725853|0|0|0|0|0.0.0.0|0|1|42665|192.168.0.77|0|13432236|0.000|0.000|0.000|0|0|0|0|0|0|0|0|||0|0||||0|||| A8:A1:59:A5:B6:3A|58:86:94:29:2E:D7|3|3|192.168.0.153|168.126.63.2|56213|53|::|::|0|96|4|17|5.169|6|73|1|134|1|1738725855|1738725855|0|0|0|0|0.0.0.0|0|1|42666|192.168.0.77|0|13432236|0.000|0.000|2.735|0|0|0|0|0|0|0|0|daisy.ubuntu.com|daisy.ubuntu.com|28|0||||0|||| A8:A1:59:A5:B6:3A|58:86:94:29:2E:D7|3|3|192.168.0.153|168.126.63.2|42584|53|::|::|0|96|4|17|5.169|6|73|1|137|1|1738725856|1738725856|0|0|0|0|0.0.0.0|0|1|42667|192.168.0.77|0|13432236|0.000|0.000|3.235|0|0|0|0|0|0|0|0|daisy.ubuntu.com|daisy.ubuntu.com|28|0||||0|||| 28:D0:EA:C9:22:7D|FF:FF:FF:FF:FF:FF|3|3|192.168.0.41|192.168.0.255|137|137|::|::|0|4|17|10|6|936|12|0|0|1738725802|1738725902|0|0|0|0|0.0.0.0|0|1|42668|192.168.0.77|0|13432236|0.000|0.000|0.000|0|0|0|0|0|0|0|0|sukho||0|0||||0|||| 00:0C:29:E6:F0:E0|58:86:94:29:2E:D7|3|3|192.168.0.100|20.198.119.84|59198|443|::|::|0|4|6|91|6|181|2|211|1|1738725862|1738725862|24|24|0|0|0.0.0.0|0|1|42669|192.168.0.77|0|13432236|0.000|0.000|0.000|1026|6778|0|0|0|0|0|0|||0|0||20.198.119.84||0|||| A8:A1:59:A5:B6:3A|58:86:94:29:2E:D7|3|3|192.168.0.153|168.126.63.2|44344|53|::|::|0|96|4|17|5.169|6|86|1|278|1|1738725866|1738725866|0|0|0|0|0.0.0.0|0|1|42670|192.168.0.77|0|13432236|0.000|0.000|2.625|0|0|0|0|0|0|0|0|connectivity-check.ubuntu.com|connectivity-check.ubuntu.com|1|0||||0|||| A8:A1:59:A5:B6:3A|58:86:94:29:2E:D7|3|3|192.168.0.153|91.189.91.96|42570|80|::|::|0|4|6|7.169|6|355|5|401|4|1738725866|1738725866|27|27|140737488357376|150|0.0.0.0|0|1|42671|192.168.0.77|0|13432236|0.008|91.204|181.911|64240|65535|0|0|0|0|0|0|connectivity-check.ubuntu.com||0|0|connectivity-check.ubuntu.com/|connectivity-check.ubuntu.com|GET|204||||{"11":"Empty or missing User-Agent","47":"Obsolete nginx server 1.18.0"} 1C:FD:08:79:A7:4A|00:0C:29:CA:1D:E8|3|3|192.168.0.77|192.168.0.30|57540|5556|::|::|0|4|6|0|0|78015|84|4368|84|1738725807|1738725925|24|16|0|0|0.0.0.0|0|1|42672|192.168.0.77|0|13432236|0.000|0.000|0.000|229|52883|0|0|0|0|0|0|||0|0||||0|||| 58:86:94:FF:46:2C|FF:FF:FF:FF:FF:FF|3|3|192.168.0.219|192.168.0.255|138|138|::|::|0|4|17|10.16|6|229|1|0|0|1738725867|1738725867|0|0|4194304|10|0.0.0.0|0|1|42673|192.168.0.77|0|13432236|0.000|0.000|0.000|0|0|0|0|0|0|0|0|desktop-a14ehd5||0|0||||0|||| 28:D0:EA:C9:22:7D|33: