NetXMS Support Forum

Hi,

I am trying to upgrade the DB, and I am using Mysql8.
Here is what I get:
nxdbmgr upgrade -t
NetXMS Database Manager Version 4.1.283 Build 4.1-283-g624f7b1b4c (UNICODE)

Upgrading database...
Upgrading from version 22.22 to 22.24
>>> ALTER TABLE object_properties ADD state_before_maint integer
>>> ALTER TABLE dct_threshold_instances ADD row_number integer
SQL query failed (You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'row_number integer' at line 1):
ALTER TABLE dct_threshold_instances ADD row_number integer
Rolling back last stage due to upgrade errors...
Database upgrade failed
>>> SELECT var_value FROM metadata WHERE var_name='PendingOnlineUpgrades'

I know row_number is a reserved word in 8, but I don't know how to edit the ALTER statement and put ` characters around it in order to continue.

Can't figure if there's anything I can do from metadata:
select * from metadata;
+-----------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| var_name                    | var_value                                                                                                                                                                                            |
+-----------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| IDataIndexCreationCommand_0 | CREATE INDEX idx_idata_%d_id_timestamp ON idata_%d(item_id,idata_timestamp DESC)                                                                                                                     |
| IDataTableCreationCommand   | CREATE TABLE idata_%d (item_id integer not null,idata_timestamp integer not null,idata_value varchar(255) null)                                                                                      |
| LocationHistory             | CREATE TABLE gps_history_%d (latitude varchar(20), longitude varchar(20), accuracy integer not null, start_timestamp integer not null, end_timestamp integer not null, PRIMARY KEY(start_timestamp)) |
| PendingOnlineUpgrades       |                                                                                                                                                                                                      |
| SchemaVersion               | 700                                                                                                                                                                                                  |
| SchemaVersionMajor          | 22                                                                                                                                                                                                   |
| SchemaVersionMinor          | 22                                                                                                                                                                                                   |
| ServerID                    | 2D29B5BE09DEAAD1                                                                                                                                                                                     |
| Syntax                      | MYSQL                                                                                                                                                                                                |
| TDataIndexCreationCommand_0 | CREATE INDEX idx_tdata_%d ON tdata_%d(item_id,tdata_timestamp)                                                                                                                                       |
| TDataTableCreationCommand_0 | CREATE TABLE tdata_%d (item_id integer not null,tdata_timestamp integer not null,tdata_value longtext null)                                                                                          |
+-----------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
11 rows in set (0.00 sec)


Thank you,
Alex

Hi,

you can try to manually add column tt_row_number to table dct_threshold_instances (integer not null, set it to 0 if you have any records in dct_threshold_instances table), then you can try to run upgrade with -X option (it will ignore upgrade errors and continue). One of next upgrade procedures will try to rename row_number to tt_row_number, so you should end up with correct schema.
Make sure you have database snapshot or backup before trying this, and watch for possible other errors, because with -X DB manager would not stop at them anymore.

Best regards,
Victor

Thank you, Viktor!
That worked just fine.