Upgrade Failed

[Carl Green]

Hi

I have migrated from x86 to x64 netxms system on v2.1.2 on SQLLite database successfully so had a go at upgrading to v2.2.1 but hitting issue of:

C:\NetXMS\bin>nxdbmgr.exe upgrade -t
NetXMS Database Manager Version 2.2.1 Build 9502 (2.2.1) (UNICODE)

Upgrading database...
Upgrading from version 21.4 to 21.5
>>> ALTER TABLE nodes ADD rack_orientation integer
>>> ALTER TABLE chassis ADD rack_orientation integer
>>> UPDATE nodes SET rack_orientation=0
>>> UPDATE chassis SET rack_orientation=0
Unable to set not null constraint.
Rolling back last stage due to upgrade errors...
Database upgrade failed

Any pointers to fix this?

I might have to bite the bullet reconfigure the system from scratch as my plan was to migrate over to MSSQL database but as I can't get the migrate to work either looking like this might be my only option.

Thanks

Carl

[Carl Green]

Ran this command instead to ignore SQL errors on upgrade, its working on 2.2.1 so will see how things go.

C:\NetXMS\bin>nxdbmgr.exe upgrade -t -X
NetXMS Database Manager Version 2.2.1 Build 9502 (2.2.1) (UNICODE)

Upgrading database...
Upgrading from version 21.4 to 21.5
>>> ALTER TABLE nodes ADD rack_orientation integer
>>> ALTER TABLE chassis ADD rack_orientation integer
>>> UPDATE nodes SET rack_orientation=0
>>> UPDATE chassis SET rack_orientation=0
Unable to set not null constraint.
Unable to set not null constraint.
>>> UPDATE metadata SET var_value='5' WHERE var_name='SchemaVersionMinor'
Upgrading from version 21.5 to 22.0
>>> INSERT INTO metadata (var_name,var_value) VALUES ('SchemaVersionLevel.21','5')
>>> UPDATE metadata SET var_value='22' WHERE var_name='SchemaVersionMajor'
>>> UPDATE metadata SET var_value='0' WHERE var_name='SchemaVersionMinor'
Upgrading from version 22.0 to 22.1
>>> SELECT var_value FROM config WHERE var_name='NumberOfDataCollectors'
>>> INSERT INTO config (var_name,var_value,default_value,is_visible,need_server_restart,is_public,data_type,description) VALUES ('DataCollector.ThreadPool.BaseSize','10','10',1,1,'N','I','Base size for data collector thread pool.')
>>> INSERT INTO config (var_name,var_value,default_value,is_visible,need_server_restart,is_public,data_type,description) VALUES ('DataCollector.ThreadPool.MaxSize','250','250',1,1,'N','I','Maximum size for data collector thread pool.')
>>> UPDATE config SET default_value='250' WHERE var_name='DataCollector.ThreadPool.MaxSize'
>>> DELETE FROM config WHERE var_name='NumberOfDataCollectors'
>>> UPDATE metadata SET var_value='1' WHERE var_name='SchemaVersionMinor'
Upgrading from version 22.1 to 22.2
>>> INSERT INTO config (var_name,var_value,default_value,is_visible,need_server_restart,is_public,data_type,description) VALUES ('DBWriter.MaxRecordsPerTransaction','1000','1000',1,1,'N','I','Maximum number of records per one transaction for delayed database writes.')
>>> UPDATE metadata SET var_value='2' WHERE var_name='SchemaVersionMinor'
Upgrading from version 22.2 to 22.3
>>> CREATE TABLE dci_access (  dci_id integer not null,  user_id integer not null,  PRIMARY KEY(dci_id,user_id))
>>> UPDATE metadata SET var_value='3' WHERE var_name='SchemaVersionMinor'
Upgrading from version 22.3 to 22.4
>>> SELECT var_value FROM metadata WHERE var_name='SchemaVersionLevel.21'
>>> UPDATE metadata SET var_value='4' WHERE var_name='SchemaVersionMinor'
Upgrading from version 22.4 to 22.5
>>> ALTER TABLE items ADD instance_retention_time integer
>>> ALTER TABLE dc_tables ADD instance_retention_time integer
>>> UPDATE items SET instance_retention_time=-1
>>> UPDATE dc_tables SET instance_retention_time=-1
>>> INSERT INTO config (var_name,var_value,default_value,is_visible,need_server_restart,is_public,data_type,description) VALUES ('InstanceRetentionTime','0','0',1,1,'Y','I','Default retention time (in days) for missing DCI instances')
Unable to set not null constraint.
Unable to set not null constraint.
>>> UPDATE metadata SET var_value='5' WHERE var_name='SchemaVersionMinor'
Database upgrade succeeded

[Carl Green]

Opted to start again with fresh database under MSSQL instead of using the SQLLite database.

[Borgso]

Having the same problem with SQLite as Database.

Starting with upgrade to 2.2.x series.
After "nxdbmgr upgrade -X" i get server up and running but without any nodes and this in log

SQL query failed (Query = "INSERT INTO nodes (primary_ip,primary_name,snmp_port,node_flags,snmp_version,community,status_poll_type,agent_port,auth_method,secret,snmp_oid,uname,agent_version,platform_name,poller_node_id,zone_guid,proxy_node,snmp_proxy,icmp_proxy,required_polls,use_ifxtable,usm_auth_password,usm_priv_password,usm_methods,snmp_sys_name,bridge_base_addr,down_since,driver_name,rack_image_front,rack_position,rack_height,rack_id,boot_time,agent_cache_mode,snmp_sys_contact,snmp_sys_location,last_agent_comm_time,syslog_msg_count,snmp_trap_count,node_type,node_subtype,ssh_login,ssh_password,ssh_proxy,chassis_id,port_rows,port_numbering_scheme,agent_comp_mode,tunnel_id,lldp_id,fail_time_snmp,fail_time_agent,runtime_flags,rack_orientation,rack_image_rear,id) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)"): table nodes has no column named rack_image_front

[Borgso]

Found a bug report for this: https://track.radensolutions.com/issue/NX-1377

[victor]

Hi,

we do not recommend to use SQLite in production environment. Due to very limited SQLite capability for changing schema (basically you can only add new column) upgrade procedures often file. We do create workarounds for this, but sometimes (as now) SQLite versions of upgrade procedures are not ready. I recommend to limit SQLite use to proof of concept systems and migrate to other supported DB when possible.

Best regards,
Victor

[Borgso]

Thanks for clarify, fair enough reason

Was running SQLite on a Test/Demo server (proof of concept) because it was fast and easy to rollback/maintaine
But as i now understand the problem i will migrate to another DB