NetXMS Support Forum

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.

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)')

Thank you for tip,
works ok.
Have a nice day

Dear users,
i have problem on debian installation.

i try to migrate from sqlite to mysql, i have mysql server ready db initialized and invoking migrate command with stoped netxmsd service.
i get following.

root@netxms:/tmp# nxdbmgr -I -t migrate /tmp/netxmsd.conf
WARNING: invalid transaction size, reset to defaultNetXMS Database Manager Version 2.0.3 Build 8066 (UNKNOWN) (UNICODE)

Database driver sqlite.ddr loaded
Connected to source database


WARNING!!!
This operation will clear all configuration and collected data from destination database before migration.
Are you sure? (Y/N) y
>>> SELECT id FROM nodes
>>> DELETE FROM config
>>> DELETE FROM config_clob
>>> DELETE FROM users
>>> DELETE FROM user_groups
>>> DELETE FROM user_group_members
>>> DELETE FROM user_profiles
>>> DELETE FROM userdb_custom_attributes
>>> DELETE FROM object_properties
>>> DELETE FROM object_custom_attributes
>>> DELETE FROM zones
>>> DELETE FROM mobile_devices
>>> DELETE FROM access_points
>>> DELETE FROM racks
>>> DELETE FROM nodes
>>> DELETE FROM clusters
>>> DELETE FROM cluster_members
>>> DELETE FROM cluster_sync_subnets
>>> DELETE FROM cluster_resources
>>> DELETE FROM subnets
>>> DELETE FROM interfaces
>>> DELETE FROM interface_address_list
>>> DELETE FROM network_services
>>> DELETE FROM vpn_connectors
>>> DELETE FROM vpn_connector_networks
>>> DELETE FROM object_containers
>>> DELETE FROM conditions
>>> DELETE FROM cond_dci_map
>>> DELETE FROM templates
>>> DELETE FROM dct_node_map
>>> DELETE FROM nsmap
>>> DELETE FROM container_members
>>> DELETE FROM acl
>>> DELETE FROM trusted_nodes
>>> DELETE FROM items
>>> DELETE FROM dc_tables
>>> DELETE FROM dc_table_columns
>>> DELETE FROM dct_column_names
>>> DELETE FROM dct_thresholds
>>> DELETE FROM dct_threshold_conditions
>>> DELETE FROM dci_schedules
>>> DELETE FROM raw_dci_values
>>> DELETE FROM event_cfg
>>> DELETE FROM event_log
>>> DELETE FROM actions
>>> DELETE FROM event_groups
>>> DELETE FROM event_group_members
>>> DELETE FROM event_policy
>>> DELETE FROM policy_source_list
>>> DELETE FROM policy_event_list
>>> DELETE FROM policy_action_list
>>> DELETE FROM policy_time_range_list
>>> DELETE FROM policy_situation_attr_list
>>> DELETE FROM time_ranges
>>> DELETE FROM thresholds
>>> DELETE FROM alarms
>>> DELETE FROM alarm_notes
>>> DELETE FROM alarm_events
>>> DELETE FROM snmp_trap_cfg
>>> DELETE FROM snmp_trap_pmap
>>> DELETE FROM agent_pkg
>>> DELETE FROM object_tools
>>> DELETE FROM object_tools_acl
>>> DELETE FROM object_tools_table_columns
>>> DELETE FROM object_tools_input_fields
>>> DELETE FROM syslog
>>> DELETE FROM script_library
>>> DELETE FROM snmp_trap_log
>>> DELETE FROM agent_configs
>>> DELETE FROM address_lists
>>> DELETE FROM graphs
>>> DELETE FROM graph_acl
>>> DELETE FROM certificates
>>> DELETE FROM audit_log
>>> DELETE FROM situations
>>> DELETE FROM snmp_communities
>>> DELETE FROM ap_common
>>> DELETE FROM ap_bindings
>>> DELETE FROM ap_config_files
>>> DELETE FROM usm_credentials
>>> DELETE FROM network_maps
>>> DELETE FROM network_map_elements
>>> DELETE FROM network_map_links
>>> DELETE FROM images
>>> DELETE FROM dashboards
>>> DELETE FROM dashboard_elements
>>> DELETE FROM dashboard_associations
>>> DELETE FROM business_services
>>> DELETE FROM slm_checks
>>> DELETE FROM node_links
>>> DELETE FROM slm_agreements
>>> DELETE FROM slm_tickets
>>> DELETE FROM slm_service_history
>>> DELETE FROM organizations
>>> DELETE FROM persons
>>> DELETE FROM job_history
>>> DELETE FROM licenses
>>> DELETE FROM mapping_tables
>>> DELETE FROM mapping_data
>>> DELETE FROM dci_summary_tables
>>> DELETE FROM scheduled_tasks
>>> DELETE FROM zmq_subscription
Database successfully cleared
Migrating table config
ERROR: cannot prepare INSERT statement (Column 'possible_values' specified twice)
Database migration failed.
root@netxms:/tmp# nxdbmgr -h
NetXMS Database Manager Version 2.0.3 Build 8066 (UNKNOWN) (UNICODE)


i try export db and i get this
root@netxms:/var/lib/netxms# nxdbmgr export /tmp/migrate.db
NetXMS Database Manager Version 2.0.3 Build 8066 (UNKNOWN) (UNICODE)

Exporting table config
Segmentation fault


Instalation is from debian repository not compiled version.
Im currently on sqlite and is working, db check is ok.

Any ideas how to fix it ?

Hello,
it is simple docker file i putting it here, so you can use it or help me make it better.
after docker image is completed on my NAS I export it as backup and import again, so it basicaly flatten all layers and image is smaller.

FROM debian:jessie
MAINTAINER Chuck Norris
RUN DEBIAN_FRONTEND=noninteractive apt-get update && DEBIAN_FRONTEND=noninteractive  apt-get install apt-utils -y && DEBIAN_FRONTEND=noninteractive apt-get install wget nano -y
RUN echo 'deb http://packages.netxms.org/debian jessie main' >> /etc/apt/sources.list && wget -q -O - http://packages.netxms.org/netxms.gpg | apt-key add -
RUN DEBIAN_FRONTEND=noninteractive apt-get update | apt-get upgrade -y
RUN DEBIAN_FRONTEND=noninteractive apt-get install netxms-server nano snmp -y
COPY *.conf /etc/
RUN mkdir /opt/netxmsdb

RUN nxdbmgr init /usr/share/netxms/sql/dbinit_sqlite.sql
RUN DEBIAN_FRONTEND=noninteractive apt-get remove wget apt-utils -y && DEBIAN_FRONTEND=noninteractive apt-get autoremove -y && DEBIAN_FRONTEND=noninteractive apt-get clean -y
RUN rm -rf /var/lib/apt/lists/* /tmp/* /var/tmp*

VOLUME /opt/netxmsdb
EXPOSE 4700
EXPOSE 4701
EXPOSE 4702
EXPOSE 4747


CMD ["netxmsd", "nxagentd"]

About docker hub, i will think about it, for now, i dont see much reason go there, i will better consult changes here with others. But it is standart i understand.
in atachment two copied configs

Dear users,
let me inform you about docker image which i created and maintain on dockerhub
https://hub.docker.com/r/madmucho/netxms/

this release contains netxms base sqlite version, it is very small based od debian 8 fast and running in seconds.

If you miss something inside let me know here so i will add futures and items to next versions.