Upgrade to 6.0.0 - Database upgrade fails

twparker

I saw the database upgrade start when I used apt get upgrade. I thought that the server could then be started while that was running, but I suppose not.

dlauwers

Hi,

Did all the updates/upgrades to be on 6.01, rebooted, bus till

NetXMS Database Manager Version 6.0.1 Build 6.0-469-g5e68d5db1d

Upgrading database...
Upgrading from version 60.7 to 60.8
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

On Debian 13
PostgreSQL 17.7

Any fixes or workarrounds ?

Thanks

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'
:$

Alex Kirhenshtein

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.

Alex Kirhenshtein

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

NetXMS Forum

Upgrade to 6.0.0 - Database upgrade fails