Author Topic: SQL errors in 1.2.2 ?  (Read 6564 times)

DH

  • Jr. Member
  • **
  • Posts: 63
    • View Profile
SQL errors in 1.2.2 ?
« on: August 16, 2012, 02:56:24 pm »
Hi,

 Server Platform: Windows Server 2008 R2 standard (64bit)
 NetXMS Server: netxms-1.2.2-x64

 Since upgrading to 1.2.2 I'm having intermittent problems viewing graphs of DCI values and getting DCI collection failures in the client. In the server system log I get a number of entries when the DCI collection fails:

SQL query failed (Query = "INSERT INTO event_log (event_id,event_code,event_timestamp,event_source,event_severity,event_message,root_event_id,user_tag) VALUES (?,?,?,?,?,?,?,?)"): constraint failed

SQL query failed (Query = "INSERT INTO event_log (event_id,event_code,event_timestamp,event_source,event_severity,event_message,root_event_id,user_tag) VALUES (?,?,?,?,?,?,?,?)"): constraint failed

SQL query failed (Query = "INSERT INTO event_log (event_id,event_code,event_timestamp,event_source,event_severity,event_message,root_event_id,user_tag) VALUES (?,?,?,?,?,?,?,?)"): column event_id is not unique

SQL query failed (Query = "INSERT INTO event_log (event_id,event_code,event_timestamp,event_source,event_severity,event_message,root_event_id,user_tag) VALUES (?,?,?,?,?,?,?,?)"): constraint failed

SQL query failed (Query = "INSERT INTO event_log (event_id,event_code,event_timestamp,event_source,event_severity,event_message,root_event_id,user_tag) VALUES (?,?,?,?,?,?,?,?)"): column event_id is not unique

SQL query failed (Query = "INSERT INTO event_log (event_id,event_code,event_timestamp,event_source,event_severity,event_message,root_event_id,user_tag) VALUES (?,?,?,?,?,?,?,?)"): constraint failed

 I then restart NetXMS Core and its ok again for a while before failing again with the same problem.

 Any pointers on where I should be looking would a great help.

David

Victor Kirhenshtein

  • Lead Developer
  • Administrator
  • Hero Member
  • *****
  • Posts: 7137
    • View Profile
Re: SQL errors in 1.2.2 ?
« Reply #1 on: August 16, 2012, 05:54:37 pm »
Hi!

Looks more like a bug. What database you are using and what driver?

Best regards,
Victor

DH

  • Jr. Member
  • **
  • Posts: 63
    • View Profile
Re: SQL errors in 1.2.2 ?
« Reply #2 on: August 16, 2012, 06:29:08 pm »
Hi Victor,

 It is the one built into the installer, is that SQLite ? The original install was quite some time ago ( 0.x version) and upgraded over time. Is there a way of asking netxms which dB it is using ?

Regards,
David


Victor Kirhenshtein

  • Lead Developer
  • Administrator
  • Hero Member
  • *****
  • Posts: 7137
    • View Profile
Re: SQL errors in 1.2.2 ?
« Reply #3 on: August 16, 2012, 06:49:13 pm »
Hi!

Check netxmsd.conf file, DBDriver parameter.

Best regards,
Victor

DH

  • Jr. Member
  • **
  • Posts: 63
    • View Profile
Re: SQL errors in 1.2.2 ?
« Reply #4 on: August 16, 2012, 07:17:52 pm »
Hi Victor,

 Yes it is SQLite, conf file:

LogFile = {syslog}
DBDriver = sqlite.ddr
DBName = C:\NetXMS\database\bn-netxms
LogFailedSQLQueries = yes

Regards,
David

DH

  • Jr. Member
  • **
  • Posts: 63
    • View Profile
Re: SQL errors in 1.2.2 ?
« Reply #5 on: August 28, 2012, 11:23:00 am »
Hi,

 Anyone any thoughts on this ?

Regards,
David

macro

  • Newbie
  • *
  • Posts: 20
    • View Profile
Re: SQL errors in 1.2.2 ?
« Reply #6 on: September 03, 2012, 12:05:57 pm »
Guys... Just a reminder or in case you are not aware. Please do not use SQLite for production. SQLite is not designed to handle big data volumes or a high DB load. Don't get me wrong, SQLite is still a great DB engine and does very well what its designed for. But it's just not up to the task for an average NetXMS production environment. Use NetXMS with SQLite for trying out, testing new features or if you monitor home network with 3 nodes :) But stay away from it in the production!!! You will be really better off with MySQL, Postgres or Oracle.

As for the topic. There is a fix for this specific problem committed yesterday. Well, actually this is a work around some obscure SQLite behaviour (see warning above!). It will go into 1.2.3. If you need it faster feel free to get it off the svn and test :)

DH

  • Jr. Member
  • **
  • Posts: 63
    • View Profile
Re: SQL errors in 1.2.2 ?
« Reply #7 on: September 03, 2012, 12:09:48 pm »
Hi,

 Is there an easy way to migrate existing configuration and history to a better dB product ?

Regards,
David

Victor Kirhenshtein

  • Lead Developer
  • Administrator
  • Hero Member
  • *****
  • Posts: 7137
    • View Profile
Re: SQL errors in 1.2.2 ?
« Reply #8 on: September 03, 2012, 06:29:01 pm »
Hi!

There are export/import option in nxdbmgr. What it basically does is exporting data from your existing database into SQLite database file, and the importing into another database. As you have SQLite already, you can try to just create empty database on another engine, create correct netxmsd.conf, and the use your existing database file as input for nxdbmgr import command.

Best regards,
Victor

DH

  • Jr. Member
  • **
  • Posts: 63
    • View Profile
Re: SQL errors in 1.2.2 ?
« Reply #9 on: September 06, 2012, 05:02:40 pm »
Hi,

 I have now installed MySQL Community Server 5.5.27 (x64), re-installed NetXMS to include MySQL client support. I setup MySQL database thus:

 mysql> CREATE DATABASE netxms;
 mysql> GRANT ALL ON netxms.* TO netxms@localhost IDENTIFIED BY 'password'

 netxmsd.conf:
---------------------------------------------------------------------------------
#
# NetXMS Server configuration file
# Created by NetXMS Server configuration wizard at Wed May 09 15:31:46 2012
#

LogFile = {syslog}
DBDriver = C:\NetXMS\bin\mysql.ddr
DBServer = localhost
DBName = netxms
DBLogin = netxms
DBPassword = password
LogFailedSQLQueries = yes
---------------------------------------------------------------------------------

I initialised the database using:

 nxdbmgr.exe init C:\NetXMS\lib\sql\dbinit_mysql.sql

then I tried importing the existing configuration and history using:

 mxdbmgr.exe import C:\NetXMS\database\bn-netxms

 this results in the following output:

-------------------------------------------------------------------------
NetXMS Database Manager Version 1.2.2



WARNING!!!
This operation will clear all configuration and collected data from database.
Are you sure? (Y/N) Y
Database successfully cleared
Importing table config
Importing table config_clob
Importing table users
-------------------------------------------------------------------------

 The import generates an exception, logged as follows:

-------------------------------------------------------------------------
Faulting application name: nxdbmgr.exe, version: 0.0.0.0, time stamp: 0x500d3368
Faulting module name: libnetxmsw.dll, version: 0.0.0.0, time stamp: 0x500d32f4
Exception code: 0xc0000005
Fault offset: 0x000000000001c5ab
Faulting process id: 0x11b8
Faulting application start time: 0x01cd8c3483769b7d
Faulting application path: C:\NetXMS\bin\nxdbmgr.exe
Faulting module path: C:\NetXMS\bin\libnetxmsw.dll
Report Id: c265019d-f827-11e1-ad02-00155d002212
-------------------------------------------------------------------------

Regards,
David

DH

  • Jr. Member
  • **
  • Posts: 63
    • View Profile
Re: SQL errors in 1.2.2 ?
« Reply #10 on: September 06, 2012, 05:04:50 pm »
Hi,

 I forgot to mention, I had to upgrade the database with nxdbmgr.exe upgrade after the init.

Regards,
David

Victor Kirhenshtein

  • Lead Developer
  • Administrator
  • Hero Member
  • *****
  • Posts: 7137
    • View Profile
Re: SQL errors in 1.2.2 ?
« Reply #11 on: September 06, 2012, 06:03:32 pm »
Hi!

Just try to reproduce the error on my system, but without success. Is it possible to send me your SQLite database because I suspect that some combination of data causes this crash. If it violates your security policy, we will find other ways to debug it.

Best regards,
Victor

DH

  • Jr. Member
  • **
  • Posts: 63
    • View Profile
Re: SQL errors in 1.2.2 ?
« Reply #12 on: September 06, 2012, 06:17:09 pm »
Hi Victor,

 Is it possible to send it to you directly rather than making it publicly available here ?

Regards,
David

Victor Kirhenshtein

  • Lead Developer
  • Administrator
  • Hero Member
  • *****
  • Posts: 7137
    • View Profile
Re: SQL errors in 1.2.2 ?
« Reply #13 on: September 06, 2012, 06:48:26 pm »
Sure. Send it to victor@netxms.org.

Best regards,
Victor

DH

  • Jr. Member
  • **
  • Posts: 63
    • View Profile
Re: SQL errors in 1.2.2 ?
« Reply #14 on: September 06, 2012, 07:33:23 pm »
Hi Victor,

 It is too big to email and I don't want to put it on the forum so have you any suggestions on how I can debug the problem ?

Regards,
David