Hi Folks,
in order to update netxms to the newest version, I wanted to save my database.
When I'm trying to export it to file, I get the following error:
"Exporting table idata_sc_default
SQL query failed (42P01 ERROR: relation "idata_sc_default" does not exist
LINE 1: SELECT * FROM idata_sc_default
^):
SELECT * FROM idata_sc_default
Database export failed."
"Check-data-tables" and "check" using nxdbmgr says everything is fine. Database is PostgreSQL.
I checked the database tables manually and in deed there is no "idata_sc_default" table.
If there has to be this table, how can I get it back or create it new?
If there is no need for this table, how can I export my database before updating?
Thanks for your help!
Hello,
did you use the nxdbmgr export command?
If this doesn't work you can dump the database (pg_dump, mysqldump, exp, etc.).
You can dump database while netxmsd is running.
Here is the documentation for PostgreSQL.
https://www.postgresql.org/docs/current/backup-dump.html (https://www.postgresql.org/docs/current/backup-dump.html)
Best regards
Woody
Hi,
Yes, I used nxdbmgr export command, giving the specified error.
I know pg_dump, used this and have a dump of my database, but the problem is still the missing table.
I'd like to work with nxdbmgr instead of using pgsql for export/dump.
Maybe it will be created after updating netxms?
Hello,
netxms has fixed many bugs with the latest version.
I think you can just upgrade it.
I never had problems oder data loss while upgrading.
Best regards
Woody
Hi,
what exact version you are using? Please show content of table "metadata".
Best regards,
Victor
Quote from: Woody on March 16, 2020, 01:32:50 PM
Hello,
netxms has fixed many bugs with the latest version.
I think you can just upgrade it.
I never had problems oder data loss while upgrading.
Best regards
Woody
Upgrade works but error is still there
Quote from: Victor Kirhenshtein on March 18, 2020, 10:43:09 AM
Hi,
what exact version you are using? Please show content of table "metadata".
Best regards,
Victor
Hi Victor,
now I'm on version 3.2.400.
I played along a little bit with excluding tables until the export worked.
Following tables needed to be skipped:
idata_sc_default
idata_sc_7
idata_sc_30
idata_sc_90
idata_sc_180
idata_sc_other
tdata_sc_default
tdata_sc_7
tdata_sc_30
tdata_sc_90
tdata_sc_180
tdata_sc_other
idata_83292
And here is the metadata-table:
var_name | var_value
-----------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SchemaVersion | 700
Syntax | PGSQL
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,tdata_value text null)
TDataIndexCreationCommand_0 | CREATE INDEX idx_tdata_%d ON tdata_%d(item_id,tdata_timestamp)
LocationHistory | CREATE TABLE gps_history_%d (latitude varchar(20), longitude varchar(20), accuracy integer not null, start_timest var_name | var_value
-----------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SchemaVersion | 700
Syntax | PGSQL
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,tdata_value text null)
TDataIndexCreationCommand_0 | CREATE INDEX idx_tdata_%d ON tdata_%d(item_id,tdata_timestamp)
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))
ServerID | 2E2D7FC5BAC725ED
SingeTablePerfData | 0
SchemaVersionLevel.30 | 102
SchemaVersionLevel.22 | 59
SchemaVersionLevel.31 | 10
SchemaVersionMajor | 32
IDataTableCreationCommand | CREATE TABLE idata_%d (item_id integer not null,idata_timestamp integer not null,idata_value varchar(255) null,raw_value varchar(255) null)
SchemaVersionMinor | 12
(14 rows)
(END)
Edit: Now there is another problem, maybe it depends on the "missing-table-problem" an would have appeared befor updating netxms:
We put 4 new devices into our network, which are found via active network discovery. After a while, they disappear in netxms and get found within the next network discovery. This kept repeading.
Kind regards
Sebastian
idata_sc* tables are only needed for TimescaleDB deployment, so it definitely is export bug.
As for disappearing nodes - do you have any SQL errors in server log?
Best regards,
Victor
Hi Victor,
there are no errors in the log-file, if I checked the right one. (which is set in netxmxd.conf?).
netxmsd.conf is:
"DBDriver = pgsql.ddr
DBServer = localhost
DBName = netxms
DBLogin = xxxxx
DBPassword = xxxxxx
LogFailedSQLQueries = yes
LogFile = /var/log/netxmsd"
In server configuration, "DeleteUnreachableNodesPeriod" is set to 0.
Edit: I added one oft the new nodes manually and after a while this one also disappears.
Kind regards,
Sebastian
It could also be bug in deduplication (when server decides that two nodes are in fact represent same device and deletes one of them). You can try to turn deduplication off by setting configuration parameter NetworkDiscovery.MergeDuplicateNodes to false. Also, try to set debug level to 6, create node, wait for it to disappear, and show log file filtered by that node name.
Also, try to execute command on debug console
show object name
replacing nme with actual name of disappeared node.
Best regards,
Victor
Hi Victor,
I was a little busy last week.
Here's what debug lvl 6 shows:
Quote2020.03.30 02:48:51.256 *D* [poll.conf ] Primary IP address 1xx.xxx.xxx.21 of node server08.domain.de [279] found on interface Microsoft Network Adapter Multiplexor Driver of node ilo-server08.domain.de [106297]
2020.03.30 02:48:51.256 *D* [poll.conf ] Node server08.domain.de [279] is a duplicate of node ilo-server08.domain.de [106297]
2020.03.30 02:48:51.256 *D* [poll.conf ] Removing node ilo-server08.domain.de [106297] as duplicate
2020.03.30 02:48:51.256 *D* [ ] Deleting object 106297 [ilo-server08.domain.de]
The problem is appearing at all of our ILOs after we decided to monitor their network.
The servers are in a different network than their ILOs. The ILOs get found and then get deleted.
e.g.: server08.domain.de has primary IP 192.111.222.21 an ILO-server08.domain.de has IP 192.111.100.8.
Best regards
Sebastian