MySQL Database Update Issues - warning

[RLJB]

Interesting issue:

- updating to new MySQL version (V8 in this instance, but also happens with older upgrades)

- certain words become "Reserved Words", for example:

GENERATED (R); added in 5.7.6 (reserved)

RANK (R); added in 8.0.2 (reserved)

(full list here: https://dev.mysql.com/doc/mysqld-versio ... s-8-0.html)

- if you have an Attribute that is named one of these NEW reserved words and you go to change it you will be stuck:

- AwareIM can not publish as the word is reserved, when you attempt to publish you will get an error such as "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..... " etc

Solution implemented:

- roll back to previous MySQL version
- search BSV (and all BSVs on that database server) for Reserved words and change them to a non reserved word
- publish all BSVs affected
- update database

[joben]

Thanks for the warning.
Upgrading MySQL suddenly became more exciting
Btw, how does one even make a roll-back of MySQL?

[kklosson]

Can I presume this applies only to business objects and their attributes?

[RLJB]

@joben

Well you actually can NOT roll-back MySQL version, so you are literally stuck.

We're on AWS, so what we did:

1) started new RDS DB server instance using old MYQL 5.7

2) restored a snapshot to the 5.7 version

3) connected AwareIM to the v5.7 database

3) adjusted all reserved words attributes in AwareIM config and stored procedures too

4) published all the BSVs affected

5) proceed with update of MySQL to V8


@kklosson
yes you can't have a column name as a reserved word, so we're talking Business Object attribute names

the BIG issue you have is that you might update MySQL and not even be aware about this issue for a year or more (in our case)... if it is a little used attribute, it just sits dormant for a while but then a process runs or a query using that attribute and you get a MySQL error THEN you can't fix it, because you can't publish (and change the attribute name) because on going into test you get a MySQL error... you're now stuck, one way, dead end street - AwareIM won't let you delete the attribute, nor change it's name as it requires it to run a SQL statement using a reserved word - fun huh

[Jaymer]

So just export as XML, then change it the field name in a text editor. And then re-import.
Manually change the table.fieldName inside mySql.
That should clear it up

[RLJB]

@jaymer

That works on simple BSVs.
Our BSVs are large and complex and importing xml versions of BSV results in errors, so not a solution unfortunately.

[joben]

@RLJB
Thanks for the clarification.
I had my suspicion that roll-back meant restoring a snapshot.

[Jaymer]

@jaymer

That works on simple BSVs.
Our BSVs are large and complex and importing xml versions of BSV results in errors, so not a solution unfortunately.

That shouldn’t happen.
When you Discover an issue, you should be reporting that, so Vlad can correct it.