upgrade to v2.1 withembedded sqlite

[lindeamon]

hi,

just did an upgrade from v2.0.8 to v2.1.
using an embedded sqlite.
the db upgrade failed to continue with an error on table idata111.
using the ignore option did the work but how can i know that everything is working fine ?

10x
lindeamon

[victor]

Hi,

what was exact error message?

Best regards,
Victor

[lindeamon]

hi victor,

unfortunately, i do not remember the exact error message, only the one i wrote.

Best Regards,
Lindeamon

[madmucho]

Same simptoms sqlite in docker.

nxdbmgr upgrade -t
NetXMS Database Manager Version 2.1 Build 9012 (2.1-RELEASE) (UNICODE)

Upgrading database...
Upgrading from version 410 to 411
>>> SELECT var_value FROM metadata WHERE var_name='TdataTableUpdated'
>>> SELECT var_name,var_value FROM metadata WHERE var_name LIKE 'TDataTableCreationCommand_%' OR var_name LIKE 'TDataIndexCreationCommand_%'
>>> DELETE FROM metadata WHERE var_name LIKE 'TDataTableCreationCommand_%' OR var_name LIKE 'TDataIndexCreationCommand_%'
>>> INSERT INTO metadata (var_name,var_value) VALUES ('TDataTableCreationCommand_0','CREATE TABLE tdata_%d (item_id integer not null,tdata_timestamp integer not null,tdata_value varchar null)')
>>> INSERT INTO metadata (var_name,var_value) VALUES ('TDataIndexCreationCommand_0','CREATE INDEX idx_tdata_%d ON tdata_%d(item_id,tdata_timestamp)')
>>> SELECT id FROM nodes
Converting table tdata_108: failed
>>> DELETE FROM metadata WHERE var_name LIKE 'TDataTableCreationCommand_%' OR var_name LIKE 'TDataIndexCreationCommand_%'
>>> INSERT INTO metadata (var_name,var_value) VALUES ('TDataTableCreationCommand_0','CREATE TABLE tdata_%d (item_id integer not null,tdata_timestamp integer not null,record_id number(20) not null,UNIQUE(record_id))')
>>> INSERT INTO metadata (var_name,var_value) VALUES ('TDataTableCreationCommand_1','CREATE TABLE tdata_records_%d (record_id number(20) not null,row_id number(20) not null,instance varchar(255) null,PRIMARY KEY(row_id),FOREIGN KEY (record_id) REFERENCES tdata_%d(record_id) ON DELETE CASCADE)')
>>> INSERT INTO metadata (var_name,var_value) VALUES ('TDataTableCreationCommand_2','CREATE TABLE tdata_rows_%d (row_id number(20) not null,column_id integer not null,value varchar(255) null,PRIMARY KEY(row_id,column_id),FOREIGN KEY (row_id) REFERENCES tdata_records_%d(row_id) ON DELETE CASCADE)')
>>> INSERT INTO metadata (var_name,var_value) VALUES ('TDataIndexCreationCommand_0','CREATE INDEX idx_tdata_%d_id_timestamp ON tdata_%d(item_id,tdata_timestamp)')
>>> INSERT INTO metadata (var_name,var_value) VALUES ('TDataIndexCreationCommand_1','CREATE INDEX idx_tdata_rec_%d_instance ON tdata_records_%d(instance)')
>>> INSERT INTO metadata (var_name,var_value) VALUES ('TDataIndexCreationCommand_2','CREATE INDEX idx_tdata_rec_%d_id ON tdata_records_%d(record_id)')

[madmucho]

Result of nxdbupgrade with -X to skip errors,
all other version upgrade steps after 410 to 411 run with no errors.

Upgrading from version 410 to 411
>>> SELECT var_value FROM metadata WHERE var_name='TdataTableUpdated'
>>> SELECT var_name,var_value FROM metadata WHERE var_name LIKE 'TDataTableCreationCommand_%' OR var_name LIKE 'TDataIndexCreationCommand_%'
>>> DELETE FROM metadata WHERE var_name LIKE 'TDataTableCreationCommand_%' OR var_name LIKE 'TDataIndexCreationCommand_%'
>>> INSERT INTO metadata (var_name,var_value) VALUES ('TDataTableCreationCommand_0','CREATE TABLE tdata_%d (item_id integer not null,tdata_timestamp integer not null,tdata_value varchar null)')
>>> INSERT INTO metadata (var_name,var_value) VALUES ('TDataIndexCreationCommand_0','CREATE INDEX idx_tdata_%d ON tdata_%d(item_id,tdata_timestamp)')
>>> SELECT id FROM nodes
Converting table tdata_108: failed
>>> DELETE FROM metadata WHERE var_name LIKE 'TDataTableCreationCommand_%' OR var_name LIKE 'TDataIndexCreationCommand_%'
>>> INSERT INTO metadata (var_name,var_value) VALUES ('TDataTableCreationCommand_0','CREATE TABLE tdata_%d (item_id integer not null,tdata_timestamp integer not null,record_id number(20) not null,UNIQUE(record_id))')
>>> INSERT INTO metadata (var_name,var_value) VALUES ('TDataTableCreationCommand_1','CREATE TABLE tdata_records_%d (record_id number(20) not null,row_id number(20) not null,instance varchar(255) null,PRIMARY KEY(row_id),FOREIGN KEY (record_id) REFERENCES tdata_%d(record_id) ON DELETE CASCADE)')
>>> INSERT INTO metadata (var_name,var_value) VALUES ('TDataTableCreationCommand_2','CREATE TABLE tdata_rows_%d (row_id number(20) not null,column_id integer not null,value varchar(255) null,PRIMARY KEY(row_id,column_id),FOREIGN KEY (row_id) REFERENCES tdata_records_%d(row_id) ON DELETE CASCADE)')
>>> INSERT INTO metadata (var_name,var_value) VALUES ('TDataIndexCreationCommand_0','CREATE INDEX idx_tdata_%d_id_timestamp ON tdata_%d(item_id,tdata_timestamp)')
>>> INSERT INTO metadata (var_name,var_value) VALUES ('TDataIndexCreationCommand_1','CREATE INDEX idx_tdata_rec_%d_instance ON tdata_records_%d(instance)')
>>> INSERT INTO metadata (var_name,var_value) VALUES ('TDataIndexCreationCommand_2','CREATE INDEX idx_tdata_rec_%d_id ON tdata_records_%d(record_id)')
Rolling back last stage due to upgrade errors...
Database upgrade failed
root@d699ef6920c2:/opt/netxmsdb# nxdbmgr upgrade -t -X
NetXMS Database Manager Version 2.1 Build 9012 (2.1-RELEASE) (UNICODE)

Upgrading database...
Upgrading from version 410 to 411
>>> SELECT var_value FROM metadata WHERE var_name='TdataTableUpdated'
>>> SELECT var_name,var_value FROM metadata WHERE var_name LIKE 'TDataTableCreationCommand_%' OR var_name LIKE 'TDataIndexCreationCommand_%'
>>> DELETE FROM metadata WHERE var_name LIKE 'TDataTableCreationCommand_%' OR var_name LIKE 'TDataIndexCreationCommand_%'
>>> INSERT INTO metadata (var_name,var_value) VALUES ('TDataTableCreationCommand_0','CREATE TABLE tdata_%d (item_id integer not null,tdata_timestamp integer not null,tdata_value varchar null)')
>>> INSERT INTO metadata (var_name,var_value) VALUES ('TDataIndexCreationCommand_0','CREATE INDEX idx_tdata_%d ON tdata_%d(item_id,tdata_timestamp)')
>>> SELECT id FROM nodes
Converting table tdata_108: failed
Converting table tdata_126: failed
Converting table tdata_134: failed
Converting table tdata_160: failed
Converting table tdata_194: failed
Converting table tdata_232: failed
>>> SELECT id FROM clusters
>>> SELECT id FROM mobile_devices
>>> SELECT id FROM access_points
>>> SELECT id FROM chassis
>>> UPDATE metadata SET var_value='411' WHERE var_name='SchemaVersion'

All tdata_ tables failed to convert, as i look to sources looks like something wrong is in logic of
static BOOL H_UpgradeFromV410(int currVersion, int newVersion) function.