NetXMS Support Forum

Hi when running nxdbmgr check on latest version I get asked if I want to delete orphaned tables.

The first few succeeded, however after the first few successful removals there are errors on subsequent tables re. constraint violations and the overall transaction fails:


# nxdbmgr check
NetXMS Database Manager Version 3.8.314 Build 3.8-314-g9d14b98df7 (UNICODE)

Checking database (excluding collected data):
* Zone object properties                                               [PASSED]
* Node object properties                                               [PASSED]
* Node to subnet bindings                                              [PASSED]
* Interface object properties                                          [PASSED]
* Interface bindings                                                   [PASSED]
* Network service object properties                                    [PASSED]
* Network service bindings                                             [PASSED]
* Cluster object properties                                            [PASSED]
* Cluster member nodes                                                 [PASSED]
* Template to node mapping                                             [PASSED]
* Object properties                                                    [PASSED]
* Container membership                                                 [PASSED]
* Event processing policy                                              [PASSED]
* Network map links                                                    [PASSED]
* Data tables                                                          [PASSED]
* Orphaned data tables                                                 [  12% ]
Data collection table tdata_1124 belongs to deleted object and no longer in use. Delete it? (Y/N)  (Yes/No/All/Skip) a
SQL query failed (2BP01 ERROR:  cannot drop table tdata_1124 because other objects depend on it
DETAIL:  constraint tdata_records_1124_record_id_fkey on table tdata_records_1124 depends on table tdata_1124
HINT:  Use DROP ... CASCADE to drop the dependent objects too.):
DROP TABLE tdata_1124
* Orphaned data tables                                                 [  12% ]
Data collection table tdata_1126 belongs to deleted object and no longer in use. Delete it? (Y/N)  (Yes/No/All/Skip) Y
SQL query failed (25P02 ERROR:  current transaction is aborted, commands ignored until end of transaction block):
DROP TABLE tdata_1126
* Orphaned data tables                                                 [  12% ]
Data collection table tdata_1128 belongs to deleted object and no longer in use. Delete it? (Y/N)  (Yes/No/All/Skip) Y
SQL query failed (25P02 ERROR:  current transaction is aborted, commands ignored until end of transaction block):
DROP TABLE tdata_1128
* Orphaned data tables                                                 [  12% ]
Data collection table tdata_1130 belongs to deleted object and no longer in use. Delete it? (Y/N)  (Yes/No/All/Skip) Y
SQL query failed (25P02 ERROR:  current transaction is aborted, commands ignored until end of transaction block):
DROP TABLE tdata_1130
* Orphaned data tables                                                 [  13% ]
Data collection table tdata_1132 belongs to deleted object and no longer in use. Delete it? (Y/N)  (Yes/No/All/Skip) Y
SQL query failed (25P02 ERROR:  current transaction is aborted, commands ignored until end of transaction block):
DROP TABLE tdata_1132
* Orphaned data tables                                                 [  13% ]
Data collection table tdata_1189 belongs to deleted object and no longer in use. Delete it? (Y/N)  (Yes/No/All/Skip) Y
SQL query failed (25P02 ERROR:  current transaction is aborted, commands ignored until end of transaction block):
DROP TABLE tdata_1189
* Orphaned data tables                                                 [  13% ]
<<SNIPPED>>


etc to the bottom:


<<SNIPPED>>
* Orphaned data tables                                                 [ERROR ]
* DCI configuration                                                    [   0SQL query failed (25P02 ERROR:  current transaction is aborted, commands ignored until end of transaction block):
SELECT item_id,node_id FROM items WHERE node_id NOT IN (SELECT object_id FROM object_properties)
SQL query failed (25P02 ERROR:  current transaction is aborted, commands ignored until end of transaction block):
SELECT item_id,node_id FROM dc_tables WHERE node_id NOT IN (SELECT object_id FROM object_properties)
[PASSED]
* Raw DCI values table                                                 [   0SQL query failed (25P02 ERROR:  current transaction is aborted, commands ignored until end of transaction block):
SELECT item_id FROM raw_dci_values
SQL query failed (25P02 ERROR:  current transaction is aborted, commands ignored until end of transaction block):
SELECT count(*) FROM raw_dci_values WHERE last_poll_time>1620602557
[PASSED]
* DCI thresholds                                                       [   0SQL query failed (25P02 ERROR:  current transaction is aborted, commands ignored until end of transaction block):
SELECT threshold_id,item_id FROM thresholds
[PASSED]
* Table DCI thresholds                                                 [   0SQL query failed (25P02 ERROR:  current transaction is aborted, commands ignored until end of transaction block):
SELECT id,table_id FROM dct_thresholds
[PASSED]
100 errors was found, 0 errors was corrected
Database still contain errors
Database check completed



I imagine cascade could be a risky thing to use by default?

I deleted what I could and left the other orphaned tables in there.  Wait for a patch or is there a manual alternative?

Cheers.

Hi,

looks like those tdata tables are from very old version, where we did use foreighn keys and related tables. It is safe to use DROP .. CASCADE on tdata tables. You can do that manually for each listed table, and next patch release will use DROP CASCADE for tdata.

Best regards,
Victor

Hi Victor,

looks like i having here a similar problem (NetXMS V3.8.366, Win10 64bit, MySQL 5.7): If i do a nxdbmgr check-data-tables i get the following error:


C:\NetXMS\bin>nxdbmgr.exe check-data-tables
NetXMS Database Manager Version 3.8.366 Build 3.8-366-gd7bdc676e2 (UNICODE)

Checking database (data tables only):
* Data tables                                                          [PASSED]
* Orphaned data tables                                                 [  62% ]
Data collection table tdata_2065 belongs to deleted object and no longer in use. Delete it? (Y/N)  (Yes/No/All/Skip) Y
SQL query failed (Cannot delete or update a parent row: a foreign key constraint fails):
DROP TABLE tdata_2065
* Orphaned data tables                                                 [ERROR ]
1 errors was found, 0 errors was corrected
Database still contain errors
Database check completed


Is the patch with DROP CASCADE for tdata tables in V3.8.366 in included?

Greetings
Marco

Was about to ask the same thing having just updated.  Looks like that might have missed this release?

Hi,

just checked - fix was made in development branch but I forgot to merge it into 3.8 branch. Now it's done and next patch release should work correctly.

Best regards,
Victor

Hi Victor,

thanks for the info... Will test this again after next patch relase...

Greetings
Marco