after upgrading 6.2 version to 6.4 clickhouse db schema is not updated

[MrdUkk]

Environment: Version: 6.4.250511 [Enterprise/Professional build] GIT rev: 6.4-stable:061f406cc515b6ccbfb3ec2243471190b2607497:20250511 Pro rev: r7265 Built on: Ubuntu 22.04.4 LTS Platform: x86_64 Edition: Enterprise XL (Bundle) used with Clickhouse v24 backend installed on that host

What happened: used v6.2 as starting point. Meanwhile v6.4 released, Upgraded inplace to v6.4. the ntopng seems to be working fine but periodically we encounter errors in log: [historical_db_search.lua:42] [clickhouse_utils.lua:1363] WARNING Failure: Code: 47. DB::Exception: Unknown expression or function identifier 'L7_CATEGORY' in scope SELECT COUNT(*) AS totalRows FROM hourly_flows WHERE ((INTERFACE_ID = 26) AND (L7_CATEGORY = 12)) AND ((FIRST_SEEN >= 1747058400) AND (FIRST_SEEN <= 1747735200) AND (LAST_SEEN <= 1747735200))

[MySQLDB.cpp:46] ERROR SQL error (1): Code: 47. DB::Exception: Unknown expression or function identifier 'L7_CATEGORY' in scope SELECT COUNT(*) AS totalRows FROM hourly_flows WHERE ((INTERFACE_ID = 26) AND (L7_CATEGORY = 12)) AND ((FIRST_SEEN >= 1747058400) AND (FIRST_SEEN <= 1747735200) AND (LAST_SEEN <= 1747735200)). (UNKNOWN_IDENTIFIER) (version 24.8.4.13 (official build))

[historical_db_search.lua:42] [clickhouse_utils.lua:1317] WARNING Failure: Code: 47. DB::Exception: Unknown expression or function identifier 'L7_CATEGORY' in scope SELECT toInt32(FIRST_SEEN) AS FIRST_SEEN, toInt32(LAST_SEEN) AS LAST_SEEN, PROTOCOL, L7_PROTO, L7_PROTO_MASTER, SCORE, NUM_FLOWS, PACKETS, TOTAL_BYTES, SRC_ASN, DST_ASN, FLOW_RISK, SRC_NETWORK_ID, DST_NETWORK_ID, IPv4NumToString(PROBE_IP) AS PROBE_IP_FORMATTED, INPUT_SNMP, OUTPUT_SNMP, SRC_COUNTRY_CODE, DST_COUNTRY_CODE, MACNumToString(SRC_MAC) AS SRC_MAC, MACNumToString(DST_MAC) AS DST_MAC, WLAN_SSID, WTP_MAC_ADDRESS

[MySQLDB.cpp:45] ERROR SQL query: WITH toUInt32(1747058400) AS ts_start, toUInt32(1747738800) AS ts_end, 18000 AS slot_size, (ts_start - (ts_start % slot_size)) AS first_slot, (ts_end - (ts_end % slot_size)) AS last_slot SELECT slot, coalesce(avg(average_bytes), 0) AS value FROM ( SELECT (epoch - (epoch % slot_size)) AS slot, avg_bytes_per_second AS average_bytes FROM (SELECT INTERFACE_ID, FIRST_SEEN, LAST_SEEN, TOTAL_BYTES / toFloat64((LAST_SEEN - FIRST_SEEN) + 1) AS avg_bytes_per_second FROM hourly_flows WHERE ((INTERFACE_ID=26) AND ( (L7_CATEGORY=12))) AND (FIRST_SEEN >= 1747058400 AND FIRST_SEEN <= 1747738800 AND LAST_SEEN <= 1747738800)) ARRAY JOIN range(toUInt32(FIRST_SEEN), toUInt32(LAST_SEEN) + 1) AS epoch) GROUP BY slot ORDER BY slot WITH FILL FROM first_slot TO last_slot STEP slot_size;

How did you reproduce it?

1. browsing clicking into various web user interface of ntopng pages.
2. comparison of live running instance and 'empty' clear v6.4

Debug Information: i diff'ed from github v6.2stable and v6.4stable branches of that file: ntopng/httpdocs/misc/db_schema_clickhouse.sql

next i entered my clickhouse instanse via clickhouse-client as admin and described table 'flow' (for example) it doesn't contain QOE_SCORE UInt8
have SRC_NETWORK_ID UInt16 instead of UInt32 in v6.4 and bunch of differences onwards.

do you have some automatic upgrade sequence or scripts when version of ntopng is upgraded? for example zabbix provided scripts that will upgrade database schema.... may be i don't find article mentioning that? Something.

What are the proper ways of upgrade without headache and losing all our historic data?

[MatteoBiscosi]

Hi @MrdUkk most of the issues should be fixed now. Please update to the latest ntopng version and let me know.

[MrdUkk]

@MatteoBiscosi i mentioned version 6.4.250511 used. is newer version exists?

[MatteoBiscosi]

Hi @MrdUkk yes, i released an update yesterday, so the version should be 6.4.250520 (or similar). Please run the update and check the new update

[MrdUkk]

@MatteoBiscosi thanks! i will try on next couple of days and return with feedback

[MrdUkk]

thanks. i see changed schema! issue is fixed.