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
MySQL Database Update Issues - warning
MySQL Database Update Issues - warning
Rod. Aware 8.6 (latest build), Developer Edition, on OS Linux (Ubuntu) using GUI hosted on AWS EC2, MYSQL on AWS RDS
Re: MySQL Database Update Issues - warning
Thanks for the warning.
Upgrading MySQL suddenly became more exciting
Btw, how does one even make a roll-back of MySQL?
Upgrading MySQL suddenly became more exciting
Btw, how does one even make a roll-back of MySQL?
Re: MySQL Database Update Issues - warning
Can I presume this applies only to business objects and their attributes?
V8.8
MySQL, AWS EC2, S3
PDFtk Toolkit
MySQL, AWS EC2, S3
PDFtk Toolkit
Re: MySQL Database Update Issues - warning
@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
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
Rod. Aware 8.6 (latest build), Developer Edition, on OS Linux (Ubuntu) using GUI hosted on AWS EC2, MYSQL on AWS RDS
Re: MySQL Database Update Issues - warning
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
Manually change the table.fieldName inside mySql.
That should clear it up
Click Here to see a collection of my tips & hacks on this forum. Or search for "JaymerTip" in the search bar at the top.
Jaymer
Aware Programming & Consulting - Tampa FL
Jaymer
Aware Programming & Consulting - Tampa FL
Re: MySQL Database Update Issues - warning
@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 works on simple BSVs.
Our BSVs are large and complex and importing xml versions of BSV results in errors, so not a solution unfortunately.
Rod. Aware 8.6 (latest build), Developer Edition, on OS Linux (Ubuntu) using GUI hosted on AWS EC2, MYSQL on AWS RDS
Re: MySQL Database Update Issues - warning
@RLJB
Thanks for the clarification.
I had my suspicion that roll-back meant restoring a snapshot.
Thanks for the clarification.
I had my suspicion that roll-back meant restoring a snapshot.
Re: MySQL Database Update Issues - warning
That shouldn’t happen.
When you Discover an issue, you should be reporting that, so Vlad can correct it.
Click Here to see a collection of my tips & hacks on this forum. Or search for "JaymerTip" in the search bar at the top.
Jaymer
Aware Programming & Consulting - Tampa FL
Jaymer
Aware Programming & Consulting - Tampa FL