Upgrade to 6.0.0 - Database upgrade fails

[twparker]

I am receiving similar errors, but I believe I caused my own problems by running the commands that Victor posted earlier.

DROP TABLE idata;
CREATE TABLE idata
(
  item_id integer not null,
  idata_timestamp integer not null,
  idata_value varchar(255) null,
  raw_value varchar(255) null
);
DROP TABLE tdata;
CREATE TABLE tdata
(
  item_id integer not null,
  tdata_timestamp integer not null,
  tdata_value varchar(max) null
);

Is my database irrevocably damaged?

[Filipp Sudanov]

dlauwers, twparker
can you run
nxdbmgr -t upgrade

This is to see what exactly it's doing there

[twparker]

Getting a lot of text - here's a snippet

DROP TABLE tdata_8505
>>> RELEASE SAVEPOINT nxdbmgr_1771000353984
>>> SELECT var_value FROM metadata WHERE var_name='TDataTableCreationCommand_0'
>>> CREATE TABLE tdata_8505 (item_id integer not null,tdata_timestamp bigint not null,tdata_value text null, PRIMARY KEY(item_id,tdata_timestamp))
>>> SELECT var_value FROM metadata WHERE var_name='TDataTableCreationCommand_1'
>>> SELECT var_value FROM metadata WHERE var_name='TDataTableCreationCommand_2'
>>> SELECT var_value FROM metadata WHERE var_name='TDataTableCreationCommand_3'
>>> SELECT var_value FROM metadata WHERE var_name='TDataTableCreationCommand_4'
>>> SELECT var_value FROM metadata WHERE var_name='TDataTableCreationCommand_5'
>>> SELECT var_value FROM metadata WHERE var_name='TDataTableCreationCommand_6'
>>> SELECT var_value FROM metadata WHERE var_name='TDataTableCreationCommand_7'
>>> SELECT var_value FROM metadata WHERE var_name='TDataTableCreationCommand_8'
>>> SELECT var_value FROM metadata WHERE var_name='TDataTableCreationCommand_9'
>>> SELECT var_value FROM metadata WHERE var_name='TDataIndexCreationCommand_0'
>>> SELECT var_value FROM metadata WHERE var_name='TDataIndexCreationCommand_1'
>>> SELECT var_value FROM metadata WHERE var_name='TDataIndexCreationCommand_2'
>>> SELECT var_value FROM metadata WHERE var_name='TDataIndexCreationCommand_3'
>>> SELECT var_value FROM metadata WHERE var_name='TDataIndexCreationCommand_4'
>>> SELECT var_value FROM metadata WHERE var_name='TDataIndexCreationCommand_5'
>>> SELECT var_value FROM metadata WHERE var_name='TDataIndexCreationCommand_6'
>>> SELECT var_value FROM metadata WHERE var_name='TDataIndexCreationCommand_7'
>>> SELECT var_value FROM metadata WHERE var_name='TDataIndexCreationCommand_8'
>>> SELECT var_value FROM metadata WHERE var_name='TDataIndexCreationCommand_9'
>>> SELECT COUNT(*) FROM idata_74790

[twparker]

This appears more relevant

>>> RELEASE SAVEPOINT nxdbmgr_1771000399839
>>> SELECT var_value FROM metadata WHERE var_name='IDataTableCreationCommand'
>>> CREATE TABLE idata_46970 (item_id integer not null,idata_timestamp bigint not null,idata_value varchar(255) null,raw_value varchar(255) null, PRIMARY KEY(item_id,idata_timestamp))
SQL query failed (25P02 ERROR:  current transaction is aborted, commands ignored until end of transaction block):
CREATE TABLE idata_46970 (item_id integer not null,idata_timestamp bigint not null,idata_value varchar(255) null,raw_value varchar(255) null, PRIMARY KEY(item_id,idata_timestamp))
Rolling back last stage due to upgrade errors...
Database upgrade failed
>>> SELECT var_value FROM metadata WHERE var_name='PendingOnlineUpgrades'

[Filipp Sudanov]

There was some query just before CREATE TABLE idata_46970 which failed.
60.7 to 60.8 handles idata/tdata tables, doing this:

  1. Check if idata_<id> exists
  2. If the table is empty → DROP it
  3. If the table is not empty → RENAME it to idata_v5_<id>
  4. Create a new idata_<id> table using CreateIDataTable(id)

Pls check, do you have
idata_46970
idata_v5_46970
tables in the DB?

If idata_46970 is there, pls show output of
\d idata_46970

[dlauwers]

@filipp-sudanov said in Upgrade to 6.0.0 - Database upgrade fails:

dlauwers, twparker
can you run
nxdbmgr -t upgrade

This is to see what exactly it's doing there

Hi, this is the result

It is "working on" Create Table tdata_16066 for about 5 minutes and then it fails

>>> SELECT COUNT(*) FROM idata_16066
>>> SAVEPOINT nxdbmgr_1771234999753
>>> DROP TABLE idata_16066
>>> RELEASE SAVEPOINT nxdbmgr_1771234999753
>>> SELECT var_value FROM metadata WHERE var_name='IDataTableCreationCommand'
>>> CREATE TABLE idata_16066 (item_id integer not null,idata_timestamp bigint not null,idata_value varchar(255) null,raw_value varchar(255) null, PRIMARY KEY(item_id,idata_timestamp))
>>> SELECT var_value FROM metadata WHERE var_name='IDataIndexCreationCommand_0'
>>> SELECT var_value FROM metadata WHERE var_name='IDataIndexCreationCommand_1'
>>> SELECT var_value FROM metadata WHERE var_name='IDataIndexCreationCommand_2'
>>> SELECT var_value FROM metadata WHERE var_name='IDataIndexCreationCommand_3'
>>> SELECT var_value FROM metadata WHERE var_name='IDataIndexCreationCommand_4'
>>> SELECT var_value FROM metadata WHERE var_name='IDataIndexCreationCommand_5'
>>> SELECT var_value FROM metadata WHERE var_name='IDataIndexCreationCommand_6'
>>> SELECT var_value FROM metadata WHERE var_name='IDataIndexCreationCommand_7'
>>> SELECT var_value FROM metadata WHERE var_name='IDataIndexCreationCommand_8'
>>> SELECT var_value FROM metadata WHERE var_name='IDataIndexCreationCommand_9'
>>> SELECT COUNT(*) FROM tdata_16066
>>> SAVEPOINT nxdbmgr_1771234999765
>>> DROP TABLE tdata_16066
>>> RELEASE SAVEPOINT nxdbmgr_1771234999765
>>> SELECT var_value FROM metadata WHERE var_name='TDataTableCreationCommand_0'
>>> CREATE TABLE tdata_16066 (item_id integer not null,tdata_timestamp bigint not null,tdata_value text null, PRIMARY KEY(item_id,tdata_timestamp))
SQL query failed (25P02 ERROR:  current transaction is aborted, commands ignored until end of transaction block):
CREATE TABLE tdata_16066 (item_id integer not null,tdata_timestamp bigint not null,tdata_value text null, PRIMARY KEY(item_id,tdata_timestamp))
Rolling back last stage due to upgrade errors...
Database upgrade failed
>>> SELECT var_value FROM metadata WHERE var_name='PendingOnlineUpgrades'
:$

[alk]

Check Postgres’s log - good chance you run out of shared memory

[dlauwers]

Seems that the table is empty ?

postgres psql -d netxms -c "SELECT * FROM idata_16066 LIMIT 10;"
[sudo] password for xxxxxx:
 item_id | idata_timestamp | idata_value | raw_value
---------+-----------------+-------------+-----------
(0 rows)

[dlauwers]

@alk said in Upgrade to 6.0.0 - Database upgrade fails:

Check Postgres’s log - good chance you run out of shared memory

YES !!

I have increased the shared memory, and now no error

sudo nxdbmgr upgrade

NetXMS Database Manager Version 6.0.2 Build 6.0-504-g549db2d3a0

Upgrading database...

Core database schema is up to date

Database upgrade succeeded

[twparker]

I had to increase my shared memory and increase max transaction locks, but I am also back in business. 

[alk]

In general I always recommend to start with https://pgtune.leopard.in.ua, when using Postgres