Author Topic: SQL query failed mariadb.  (Read 96 times)

madmucho

  • Newbie
  • *
  • Posts: 14
    • View Profile
SQL query failed mariadb.
« on: October 16, 2020, 12:27:03 pm »
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

Code: [Select]
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]

Code: [Select]
] 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.
« Last Edit: October 16, 2020, 12:35:40 pm by madmucho »

Filipp Sudanov

  • Global Moderator
  • Sr. Member
  • *****
  • Posts: 326
    • View Profile
Re: SQL query failed mariadb.
« Reply #1 on: October 16, 2020, 03:20:49 pm »
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

  • Newbie
  • *
  • Posts: 14
    • View Profile
Re: SQL query failed mariadb.
« Reply #2 on: October 17, 2020, 10:07:33 pm »
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.