Can't upgrade DB from version 22.22 to 22.24 due to Mysql 8 reserved word
-
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 -
Hello! It looks like you're interested in this conversation, but you don't have an account yet.
Getting fed up of having to scroll through the same posts each visit? When you register for an account, you'll always come back to exactly where you were before, and choose to be notified of new replies (either via email, or push notification). You'll also be able to save bookmarks and upvote posts to show your appreciation to other community members.
With your input, this post could be even better 💗
Register Login