nxdbmgr failes to repair
-
Have been running a server with timescaledb on Ubuntu 20.04 for 2months now.
Its been abit unstable compared to have running default PostGresql over years on other setups.Could i have a currupt database?
nxdbmgr will give this "in loop" when running "nxdbmgr check -d -f" as it fails to commit the fix:
Found collected data for non-existing DCI [6152]. Delete invalid records? (Yes/No/All/Skip) Y * DCI history records [ 66% ] SQL query failed (25P02 ERROR: current transaction is aborted, commands ignored until end of transaction block): SELECT distinct(item_id) FROM idata_sc_30 75SQL query failed (25P02 ERROR: current transaction is aborted, commands ignored until end of transaction block): SELECT distinct(item_id) FROM idata_sc_90 83SQL query failed (25P02 ERROR: current transaction is aborted, commands ignored until end of transaction block): SELECT distinct(item_id) FROM idata_sc_180 91SQL query failed (25P02 ERROR: current transaction is aborted, commands ignored until end of transaction block): SELECT distinct(item_id) FROM idata_sc_other [FIXED ] * Table DCI history records [ 0SQL query failed (25P02 ERROR: current transaction is aborted, commands ignored until end of transaction block): SELECT count(*) FROM tdata_sc_default WHERE tdata_timestamp > to_timestamp(1617706540) 8SQL query failed (25P02 ERROR: current transaction is aborted, commands ignored until end of transaction block): SELECT count(*) FROM tdata_sc_7 WHERE tdata_timestamp > to_timestamp(1617706540) 16SQL query failed (25P02 ERROR: current transaction is aborted, commands ignored until end of transaction block): SELECT count(*) FROM tdata_sc_30 WHERE tdata_timestamp > to_timestamp(1617706540) 25SQL query failed (25P02 ERROR: current transaction is aborted, commands ignored until end of transaction block): SELECT count(*) FROM tdata_sc_90 WHERE tdata_timestamp > to_timestamp(1617706540) 33SQL query failed (25P02 ERROR: current transaction is aborted, commands ignored until end of transaction block): SELECT count(*) FROM tdata_sc_180 WHERE tdata_timestamp > to_timestamp(1617706540) 41SQL query failed (25P02 ERROR: current transaction is aborted, commands ignored until end of transaction block): SELECT count(*) FROM tdata_sc_other WHERE tdata_timestamp > to_timestamp(1617706540) 50SQL query failed (25P02 ERROR: current transaction is aborted, commands ignored until end of transaction block): SELECT distinct(item_id) FROM tdata_sc_default 58SQL query failed (25P02 ERROR: current transaction is aborted, commands ignored until end of transaction block): SELECT distinct(item_id) FROM tdata_sc_7 66SQL query failed (25P02 ERROR: current transaction is aborted, commands ignored until end of transaction block): SELECT distinct(item_id) FROM tdata_sc_30 75SQL query failed (25P02 ERROR: current transaction is aborted, commands ignored until end of transaction block): SELECT distinct(item_id) FROM tdata_sc_90 83SQL query failed (25P02 ERROR: current transaction is aborted, commands ignored until end of transaction block): SELECT distinct(item_id) FROM tdata_sc_180 91SQL query failed (25P02 ERROR: current transaction is aborted, commands ignored until end of transaction block): SELECT distinct(item_id) FROM tdata_sc_other [PASSED] 106 errors was found, 106 errors was corrected All errors in database was fixed Commit changes? (Yes/No) Y -
Hi,
looks like bug in nxdbmgr. Somebody from dev team will check.
Best regards,
Victor -
Thanks.
Also got this when doing "Clear collected data" on 5 DCIs
06.04.2021 15:14:59 netxms01 Normal SYS_NODE_NORMAL Node status changed to NORMAL 06.04.2021 15:14:59 netxms01 Critical SYS_DB_QUERY_FAILED Database query failed (Query: SELECT avg(idata_value::double precision) FROM idata_sc_7 WHERE item_id=? AND idata_timestamp BETWEEN to_timestamp(?) AND to_timestamp(?) AND idata_value~E'^\\d+(\\.\\d+)*$'; Error: Internal error (pResult is NULL in UnsafeDrvSelect)) 06.04.2021 15:14:59 netxms01 Normal SYS_DB_CONN_RESTORED Connection with backend database engine restored 06.04.2021 15:14:58 netxms01 Critical SYS_DB_QUERY_FAILED Database query failed (Query: SELECT avg(idata_value::double precision) FROM idata_sc_7 WHERE item_id=? AND idata_timestamp BETWEEN to_timestamp(?) AND to_timestamp(?) AND idata_value~E'^\\d+(\\.\\d+)*$'; Error: server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request.) 06.04.2021 15:14:58 netxms01 Critical SYS_DB_QUERY_FAILED Database query failed (Query: SELECT avg(idata_value::double precision) FROM idata_sc_7 WHERE item_id=? AND idata_timestamp BETWEEN to_timestamp(?) AND to_timestamp(?) AND idata_value~E'^\\d+(\\.\\d+)*$'; Error: server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request.) 06.04.2021 15:14:58 netxms01 Critical SYS_DB_QUERY_FAILED Database query failed (Query: SELECT avg(idata_value::double precision) FROM idata_sc_7 WHERE item_id=? AND idata_timestamp BETWEEN to_timestamp(?) AND to_timestamp(?) AND idata_value~E'^\\d+(\\.\\d+)*$'; Error: server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request.) 06.04.2021 15:14:58 netxms01 Critical SYS_NODE_CRITICAL Node status changed to CRITICAL 06.04.2021 15:14:58 netxms01 Critical SYS_DB_QUERY_FAILED Database query failed (Query: INSERT INTO alarms (alarm_id,parent_alarm_id,creation_time,last_change_time,source_object_id,zone_uin,source_event_code,message,original_severity,current_severity,alarm_key,alarm_state,ack_by,resolved_by,hd_state,hd_ref,repeat_count,term_by,timeout,timeout_event,source_event_id,ack_timeout,dci_id,alarm_category_ids,rule_guid,event_tags,rca_script_name,impact) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?); Error: server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request.) 06.04.2021 15:14:58 netxms01 Critical SYS_DB_QUERY_FAILED Database query failed (Query: SELECT port FROM snmp_ports WHERE zone=? OR zone=-1 ORDER BY zone DESC, id ASC; Error: server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request.) 06.04.2021 15:14:58 netxms01 Critical SYS_DB_QUERY_FAILED Database query failed (Query: SELECT avg(idata_value::double precision) FROM idata_sc_7 WHERE item_id=? AND idata_timestamp BETWEEN to_timestamp(?) AND to_timestamp(?) AND idata_value~E'^\\d+(\\.\\d+)*$'; Error: server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request.) 06.04.2021 15:14:58 netxms01 Critical SYS_DB_QUERY_FAILED Database query failed (Query: SELECT avg(idata_value::double precision) FROM idata_sc_7 WHERE item_id=? AND idata_timestamp BETWEEN to_timestamp(?) AND to_timestamp(?) AND idata_value~E'^\\d+(\\.\\d+)*$'; Error: server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request.) 06.04.2021 15:14:55 netxms01 Critical SYS_DB_CONN_LOST Lost connection with backend database engine -
Hi,
that one looks like PostgreSQL problem - server drops connection for some reason. Check in Postgres or system log if it was restarted.
Best regards,
Victor -
I have manually deleted the problems, but still get the 25P02 error that can be solved with "savepoint": https://www.postgresql.org/docs/current/sql-savepoint.html
SQL query failed (25P02 ERROR: current transaction is aborted, commands ignored until end of transaction block): SELECT distinct(item_id) FROM idata_sc_30 75>>> SELECT distinct(item_id) FROM idata_sc_90 SQL query failed (25P02 ERROR: current transaction is aborted, commands ignored until end of transaction block): SELECT distinct(item_id) FROM idata_sc_90 83>>> SELECT distinct(item_id) FROM idata_sc_180 SQL query failed (25P02 ERROR: current transaction is aborted, commands ignored until end of transaction block): SELECT distinct(item_id) FROM idata_sc_180 91>>> SELECT distinct(item_id) FROM idata_sc_other SQL query failed (25P02 ERROR: current transaction is aborted, commands ignored until end of transaction block): SELECT distinct(item_id) FROM idata_sc_other [PASSED] * Table DCI history records [ 0>>> SELECT count(*) FROM tdata_sc_default WHERE tdata_timestamp > to_timestamp(1618041778) SQL query failed (25P02 ERROR: current transaction is aborted, commands ignored until end of transaction block): SELECT count(*) FROM tdata_sc_default WHERE tdata_timestamp > to_timestamp(1618041778) 8>>> SELECT count(*) FROM tdata_sc_7 WHERE tdata_timestamp > to_timestamp(1618041778) SQL query failed (25P02 ERROR: current transaction is aborted, commands ignored until end of transaction block): SELECT count(*) FROM tdata_sc_7 WHERE tdata_timestamp > to_timestamp(1618041778) 16>>> SELECT count(*) FROM tdata_sc_30 WHERE tdata_timestamp > to_timestamp(1618041778) SQL query failed (25P02 ERROR: current transaction is aborted, commands ignored until end of transaction block): SELECT count(*) FROM tdata_sc_30 WHERE tdata_timestamp > to_timestamp(1618041778) 25>>> SELECT count(*) FROM tdata_sc_90 WHERE tdata_timestamp > to_timestamp(1618041778) SQL query failed (25P02 ERROR: current transaction is aborted, commands ignored until end of transaction block): SELECT count(*) FROM tdata_sc_90 WHERE tdata_timestamp > to_timestamp(1618041778) 33>>> SELECT count(*) FROM tdata_sc_180 WHERE tdata_timestamp > to_timestamp(1618041778) SQL query failed (25P02 ERROR: current transaction is aborted, commands ignored until end of transaction block): SELECT count(*) FROM tdata_sc_180 WHERE tdata_timestamp > to_timestamp(1618041778) 41>>> SELECT count(*) FROM tdata_sc_other WHERE tdata_timestamp > to_timestamp(1618041778) SQL query failed (25P02 ERROR: current transaction is aborted, commands ignored until end of transaction block): SELECT count(*) FROM tdata_sc_other WHERE tdata_timestamp > to_timestamp(1618041778) 50>>> SELECT distinct(item_id) FROM tdata_sc_default SQL query failed (25P02 ERROR: current transaction is aborted, commands ignored until end of transaction block): SELECT distinct(item_id) FROM tdata_sc_default 58>>> SELECT distinct(item_id) FROM tdata_sc_7 SQL query failed (25P02 ERROR: current transaction is aborted, commands ignored until end of transaction block): SELECT distinct(item_id) FROM tdata_sc_7 66>>> SELECT distinct(item_id) FROM tdata_sc_30 SQL query failed (25P02 ERROR: current transaction is aborted, commands ignored until end of transaction block): SELECT distinct(item_id) FROM tdata_sc_30 75>>> SELECT distinct(item_id) FROM tdata_sc_90 SQL query failed (25P02 ERROR: current transaction is aborted, commands ignored until end of transaction block): SELECT distinct(item_id) FROM tdata_sc_90 83>>> SELECT distinct(item_id) FROM tdata_sc_180 SQL query failed (25P02 ERROR: current transaction is aborted, commands ignored until end of transaction block): SELECT distinct(item_id) FROM tdata_sc_180 91>>> SELECT distinct(item_id) FROM tdata_sc_other SQL query failed (25P02 ERROR: current transaction is aborted, commands ignored until end of transaction block): SELECT distinct(item_id) FROM tdata_sc_other [PASSED] Database doesn't contain any errors >>> UPDATE config SET var_value='UNLOCKED' WHERE var_name='DBLockStatus' >>> UPDATE config SET var_value='' WHERE var_name='DBLockInfo' >>> UPDATE config SET var_value='0' WHERE var_name='DBLockPID' Database check completed >>> SELECT var_value FROM metadata WHERE var_name='PendingOnlineUpgrades' -
Can you have a look into log of Postgres DB. There should be a ERROR: line prior to the moment when
"current transaction is aborted, commands ignored until end of transaction block"
started to occur. This should give us some understanding of what's happened. -
I increased max_locks_per_transaction from 64 to 512 in postgresql.conf after finding this errors in postgresql.log as you pointed me too Filip.
After this change, nxdbmgr completed without problems - Thanks!Logs showed this:
2021-04-13 13:59:31.878 CEST [834154] usr_netxms@db_netxms ERROR: out of shared memory
2021-04-13 13:59:31.878 CEST [834154] usr_netxms@db_netxms HINT: You might need to increase max_locks_per_transaction.
2021-04-13 13:59:31.878 CEST [834154] usr_netxms@db_netxms STATEMENT: SELECT distinct(item_id) FROM idata_sc_302021-04-13 14:07:51.986 CEST [834154] usr_netxms@db_netxms ERROR: current transaction is aborted, commands ignored until end of transaction block
2021-04-13 14:07:51.986 CEST [834154] usr_netxms@db_netxms STATEMENT: SELECT distinct(item_id) FROM idata_sc_30
2021-04-13 14:07:51.986 CEST [834154] usr_netxms@db_netxms ERROR: current transaction is aborted, commands ignored until end of transaction block
2021-04-13 14:07:51.986 CEST [834154] usr_netxms@db_netxms STATEMENT: SELECT distinct(item_id) FROM idata_sc_90
2021-04-13 14:07:51.986 CEST [834154] usr_netxms@db_netxms ERROR: current transaction is aborted, commands ignored until end of transaction block
2021-04-13 14:07:51.986 CEST [834154] usr_netxms@db_netxms STATEMENT: SELECT distinct(item_id) FROM idata_sc_180
2021-04-13 14:07:51.986 CEST [834154] usr_netxms@db_netxms ERROR: current transaction is aborted, commands ignored until end of transaction block
2021-04-13 14:07:51.986 CEST [834154] usr_netxms@db_netxms STATEMENT: SELECT distinct(item_id) FROM idata_sc_other
2021-04-13 14:07:51.986 CEST [834154] usr_netxms@db_netxms ERROR: current transaction is aborted, commands ignored until end of transaction block
2021-04-13 14:07:51.986 CEST [834154] usr_netxms@db_netxms STATEMENT: SELECT count(*) FROM tdata_sc_default WHERE tdata_timestamp > to_timestamp(1618315671)
2021-04-13 14:07:51.987 CEST [834154] usr_netxms@db_netxms ERROR: current transaction is aborted, commands ignored until end of transaction block
2021-04-13 14:07:51.987 CEST [834154] usr_netxms@db_netxms STATEMENT: SELECT count(*) FROM tdata_sc_7 WHERE tdata_timestamp > to_timestamp(1618315671)
2021-04-13 14:07:51.987 CEST [834154] usr_netxms@db_netxms ERROR: current transaction is aborted, commands ignored until end of transaction block
2021-04-13 14:07:51.987 CEST [834154] usr_netxms@db_netxms STATEMENT: SELECT count(*) FROM tdata_sc_30 WHERE tdata_timestamp > to_timestamp(1618315671)
2021-04-13 14:07:51.987 CEST [834154] usr_netxms@db_netxms ERROR: current transaction is aborted, commands ignored until end of transaction block
2021-04-13 14:07:51.987 CEST [834154] usr_netxms@db_netxms STATEMENT: SELECT count(*) FROM tdata_sc_90 WHERE tdata_timestamp > to_timestamp(1618315671)
2021-04-13 14:07:51.987 CEST [834154] usr_netxms@db_netxms ERROR: current transaction is aborted, commands ignored until end of transaction block
2021-04-13 14:07:51.987 CEST [834154] usr_netxms@db_netxms STATEMENT: SELECT count(*) FROM tdata_sc_180 WHERE tdata_timestamp > to_timestamp(1618315671)
2021-04-13 14:07:51.987 CEST [834154] usr_netxms@db_netxms ERROR: current transaction is aborted, commands ignored until end of transaction block
2021-04-13 14:07:51.987 CEST [834154] usr_netxms@db_netxms STATEMENT: SELECT count(*) FROM tdata_sc_other WHERE tdata_timestamp > to_timestamp(1618315671)
2021-04-13 14:07:51.987 CEST [834154] usr_netxms@db_netxms ERROR: current transaction is aborted, commands ignored until end of transaction block
2021-04-13 14:07:51.987 CEST [834154] usr_netxms@db_netxms STATEMENT: SELECT distinct(item_id) FROM tdata_sc_default
2021-04-13 14:07:51.987 CEST [834154] usr_netxms@db_netxms ERROR: current transaction is aborted, commands ignored until end of transaction block
2021-04-13 14:07:51.987 CEST [834154] usr_netxms@db_netxms STATEMENT: SELECT distinct(item_id) FROM tdata_sc_7
2021-04-13 14:07:51.988 CEST [834154] usr_netxms@db_netxms ERROR: current transaction is aborted, commands ignored until end of transaction block
2021-04-13 14:07:51.988 CEST [834154] usr_netxms@db_netxms STATEMENT: SELECT distinct(item_id) FROM tdata_sc_30
2021-04-13 14:07:51.988 CEST [834154] usr_netxms@db_netxms ERROR: current transaction is aborted, commands ignored until end of transaction block
2021-04-13 14:07:51.988 CEST [834154] usr_netxms@db_netxms STATEMENT: SELECT distinct(item_id) FROM tdata_sc_90
2021-04-13 14:07:51.988 CEST [834154] usr_netxms@db_netxms ERROR: current transaction is aborted, commands ignored until end of transaction block
2021-04-13 14:07:51.988 CEST [834154] usr_netxms@db_netxms STATEMENT: SELECT distinct(item_id) FROM tdata_sc_180
2021-04-13 14:07:51.988 CEST [834154] usr_netxms@db_netxms ERROR: current transaction is aborted, commands ignored until end of transaction block
2021-04-13 14:07:51.988 CEST [834154] usr_netxms@db_netxms STATEMENT: SELECT distinct(item_id) FROM tdata_sc_other
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