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.