NetXMS Support Forum

Hello,

After upgrading NetXMS from 1.2.16 to 2.0-M2 I see following errors in the netxms log file:

[12-Feb-2015 14:09:43.618] [ERROR] SQL query failed (Query = "INSERT INTO tdata_records_822 (record_id,row_id,instance) VALUES (?,?,?)"): ORA-00001: unique constraint (NETXMSOWNER.SYS_C0040248) violated
[12-Feb-2015 14:09:43.618] [ERROR] Invalid object identifier 116 in event processing policy
[12-Feb-2015 14:10:43.645] [ERROR] Invalid object identifier 116 in event processing policy
[12-Feb-2015 14:10:48.485] [ERROR] Invalid object identifier 116 in event processing policy
[12-Feb-2015 14:14:17.166] [ERROR] Invalid object identifier 116 in event processing policy
[12-Feb-2015 14:14:18.103] [ERROR] Invalid object identifier 116 in event processing policy
[12-Feb-2015 14:15:43.556] [ERROR] Invalid object identifier 116 in event processing policy
[12-Feb-2015 14:15:48.396] [ERROR] Invalid object identifier 116 in event processing policy
[12-Feb-2015 14:19:18.031] [ERROR] Invalid object identifier 116 in event processing policy
[12-Feb-2015 14:19:19.029] [ERROR] Invalid object identifier 116 in event processing policy
[12-Feb-2015 14:20:43.484] [ERROR] Invalid object identifier 116 in event processing policy
[12-Feb-2015 14:20:48.308] [ERROR] Invalid object identifier 116 in event processing policy
[12-Feb-2015 14:24:18.945] [ERROR] Invalid object identifier 116 in event processing policy
[12-Feb-2015 14:24:19.955] [ERROR] Invalid object identifier 116 in event processing policy
[12-Feb-2015 14:25:43.396] [ERROR] Invalid object identifier 116 in event processing policy
[12-Feb-2015 14:25:48.219] [ERROR] Invalid object identifier 116 in event processing policy
[12-Feb-2015 14:29:19.867] [ERROR] Invalid object identifier 116 in event processing policy
[12-Feb-2015 14:29:20.867] [ERROR] Invalid object identifier 116 in event processing policy
[12-Feb-2015 14:30:43.325] [ERROR] Invalid object identifier 116 in event processing policy
[12-Feb-2015 14:30:48.128] [ERROR] Invalid object identifier 116 in event processing policy
[12-Feb-2015 14:34:20.858] [ERROR] Invalid object identifier 116 in event processing policy
[12-Feb-2015 14:34:21.791] [ERROR] Invalid object identifier 116 in event processing policy

and following errors when running check on the database:
[root] $ /opt/netxms/bin/nxdbmgr check
NetXMS Database Manager Version 2.0-M2 Build 8007

Checking database (excluding collected data):
* Checking zone objects...                                            [PASSED]
* Checking node objects...                                            [PASSED]
* Checking interface objects...                                       [PASSED]
* Checking network service objects...                                 [PASSED]
* Checking cluster objects...                                         [PASSED]
* Checking template to node mapping...                                [PASSED]
* Checking object properties...                                       [PASSED]
Invalid object ID 116 used in policy. Delete it from policy? (Y/N) y
* Checking event processing policy...                                 [FIXED ]
* Checking network map links...                                       [PASSED]
Data collection table (TDATA) for node [110] not found. Create? (Y/N)  (Y/N) y
SQL query failed (ORA-02270: no matching unique or primary key for this column-list):
CREATE TABLE tdata_rows_110 (row_id number(20) not null,column_id integer not null,value varchar(255) null,PRIMARY KEY(row_id,column_id),FOREIGN KEY (row_id) REFERENCES tdata_records_110(row_id) ON DELETE CASCADE)
Data collection table (TDATA) for node [192] not found. Create? (Y/N)  (Y/N) y
SQL query failed (ORA-02270: no matching unique or primary key for this column-list):
CREATE TABLE tdata_rows_192 (row_id number(20) not null,column_id integer not null,value varchar(255) null,PRIMARY KEY(row_id,column_id),FOREIGN KEY (row_id) REFERENCES tdata_records_192(row_id) ON DELETE CASCADE)
Data collection table (TDATA) for node [194] not found. Create? (Y/N)  (Y/N) y
SQL query failed (ORA-02270: no matching unique or primary key for this column-list):
CREATE TABLE tdata_rows_194 (row_id number(20) not null,column_id integer not null,value varchar(255) null,PRIMARY KEY(row_id,column_id),FOREIGN KEY (row_id) REFERENCES tdata_records_194(row_id) ON DELETE CASCADE)

During every execution of nxdbmgr the same errors occurs (also for Invalid object ID 116).

Could help in resolving this issue?

Best regards,
Marcin

Hi,

please send me output of query

SELECT * FROM metadata

from your database. For now, try to answer y only to invalid object fix - that way it will fix at least this problem.

Best regards,
Victor

Below is the output:

VAR_NAME      VAR_VALUE
---------------------------------------------------------------
LocationHistory
CREATE TABLE gps_history_%d (latitude varchar(20), longitude varchar(20), accuracy integer not null, start_timestamp integer not null, end_timestamp integer not null, PRIMARY KEY(start_timestamp))

SchemaVersion
344

Syntax
ORACLE

IDataTableCreationCommand
CREATE TABLE idata_%d (item_id integer not null,idata_timestamp integer not null,idata_value varchar(255) null)

IDataIndexCreationCommand_0
CREATE INDEX idx_idata_%d_id_timestamp ON idata_%d(item_id,idata_timestamp DESC)

TDataTableCreationCommand_0
CREATE TABLE tdata_%d (item_id integer not null,tdata_timestamp integer not null,record_id number(20) not null,UNIQUE(record_id))

TDataTableCreationCommand_1
CREATE TABLE tdata_records_%d (record_id number(20) not null,row_id number(20) not null,instance varchar(255) null,PRIMARY KEY(record_id),FOREIGN KEY(record_id) REFERENCES tdata_%d(record_id) ON DELETE CASCADE)

TDataTableCreationCommand_2
CREATE TABLE tdata_rows_%d (row_id number(20) not null,column_id integer not null,value varchar(255) null,PRIMARY KEY(row_id,column_id),FOREIGN KEY (row_id) REFERENCES tdata_records_%d(row_id) ON DELETE CASCADE)

TDataIndexCreationCommand_0
CREATE INDEX idx_tdata_%d_id_timestamp ON tdata_%d(item_id,tdata_timestamp)

TDataIndexCreationCommand_1
CREATE INDEX idx_tdata_rec_%d_instance ON tdata_records_%d(instance)


10 rows selected.

Removing invalid object ID 116 does not solve the problem.
Another nxdbmgr check few minutes later is reporting again the same issue.

Regards,
Marcin

Hi,

seems that metadata is incorrect for some reason. Try to stop server and execute the following query:


UPDATE metadata SET var_value='CREATE TABLE tdata_records_%d (record_id number(20) not null,row_id number(20) not null,instance varchar(255) null,PRIMARY KEY(row_id),FOREIGN KEY (record_id) REFERENCES tdata_%d(record_id) ON DELETE CASCADE)' WHERE var_name='TDataTableCreationCommand_1';


then drop table(s) having ORA-00001 errors and run nxdbmgr check again (it should re-create all missing tables correctly).

Best regards,
Victor

After UPDATE in DB I run nxdbmgr . It worked:

Data collection table (TDATA) for node [816] not found. Create? (Y/N)  (Y/N) y
Data collection table (TDATA) for node [819] not found. Create? (Y/N)  (Y/N) y
Data collection table (TDATA) for node [822] not found. Create? (Y/N)  (Y/N) y
* Checking data tables...                                             [FIXED ]
33 errors was found, 33 errors was corrected
All errors in database was fixed
Commit changes? (Y/N) y

nxdbmgr check caused some changes in NetXMS configuration. I see alarms that were not present before.
For example there was an event processing policy without defined event.
It was not working, now after stasrting NetMXS server it started generating alarms for every event.

Thank you,
Marcin

Yes, that could happen if there was incorrect data in event processing policy. You have to manually fix or delete those rules.

Best regards,
Victor