SQL query failed mariadb.

[madmucho]

Dear Technical support,
i get two SQL query failed events in my netxmsd instance, it happens again and again

Instance is debian 10 and database mariadb, and netxms 3.5.136-1 version.

I set up debug mode and catch as much as i can, looks like there is special char in value which cause query to fail.
mariadb database for netxms is Charset latin1 and collation name latin1_swedish_ci

2020.10.16 11:14:05.968 *D* [db.query           ] BEGIN TRANSACTION successful (level 1)
2020.10.16 11:14:05.997 *D* [                   ] Node(MTFlaje.poh.cz)->getMetricFromSNMP(.1.3.6.1.2.1.31.1.1.1.11.11): dwResult=0
2020.10.16 11:14:05.997 *D* [                   ] DataCollector(): processing DC object 368423 ".1.3.6.1.2.1.31.1.1.1.11.120" owner=183 sourceNode=0
2020.10.16 11:14:06.015 *E* [db.drv             ] SQL query failed (Query = "INSERT INTO hardware_inventory (node_id,category,component_index,hw_type,vendor,model,location,capacity,part_number,seri
al_number,description) VALUES (?,?,?,?,?,?,?,?,?,?,?)"): Incorrect string value: '\xC5\xA5 Eth...' for column `netxms`.`hardware_inventory`.`hw_type` at row 1
2020.10.16 11:14:06.015 *D* [                   ] NetObj::expandText(sourceObject=108 template='Database query failed (Query: %1; Error: %2)' alarm=0 event=102620065)
2020.10.16 11:14:06.015 *D* [event.corr         ] CorrelateEvent: event SYS_DB_QUERY_FAILED id 102620065 source netxms [108]

] EVENT SYS_DB_QUERY_FAILED [52] at {0} (ID:102620065 F:0x0001 S:4 TAGS:"") FROM netxms: Database query failed (Query: INSERT INTO hardware_inventory
 (node_id,category,component_index,hw_type,vendor,model,location,capacity,part_number,serial_number,description) VALUES (?,?,?,?,?,?,?,?,?,?,?); Error: Incorrect string value: '\xC5\xA5 Eth...' for
 column `netxms`.`hardware_inventory`.`hw_type` at row 1)

Looks like unescaped value to insert.

[Filipp Sudanov]

NetXMS expects that database is created with UTF-8 charset, which should happend by default in current version of mariadb. I've just tried on my Debian 10, database got created with utf8mb4.

Try creating the database like this:
CREATE DATABASE mydatabase CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

If you have some data in you database, you can try migrating it to the newly created DB with nxdbmgr.

[madmucho]

Ajajaj have this db since version 2, so i try migrate data o my local and see how that works, our db has now 60gb.

Thank you for information.