3.8.314 orphaned data tables SQL query failed (2BP01 ERROR: cannot drop table
-
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 completedI 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 completedIs the patch with DROP CASCADE for tdata tables in V3.8.366 in included?
Greetings
Marco -
-
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 -
Hello! It looks like you're interested in this conversation, but you don't have an account yet.
Getting fed up of having to scroll through the same posts each visit? When you register for an account, you'll always come back to exactly where you were before, and choose to be notified of new replies (either via email, or push notification). You'll also be able to save bookmarks and upvote posts to show your appreciation to other community members.
With your input, this post could be even better 💗
Register Login