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:
Quote2021-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_30
Quote2021-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