Upgrading to MySQL v8

Important: AgileApps currently does not support MySQL RDS version 8. Support for this version is expected to be available by Q4 2024. Meanwhile, AgileApps is compatible with MySQL RDS version 5.7.

MySQL upgrade approaches

Approach	Downtime	Cost	Supports lower case table names	Suitable for large DBS	Suitable for large number of tenants in single MySQL instance	Possibility of Tenant wise upgrade
In-place upgrade	High	Low	No	No	Yes	No
Data Export/Import	High	High	Yes	No	No	Yes
Replica	Low	High	Yes	Yes	Yes	No
Blur/Green (AWS only)	Low	High	Yes	Yes	Yes	No
AWS Aurora Replica	Moderate	High	TBC	Yes	Yes	No
DMS	Minimum	High	Yes	No	No	Yes

Note: The chosen approach for production should be tested in all lower environments, and lower environments should be upgraded the same way as production. The tenant-wise migration may take longer but is a safer approach, limiting the risk of upgrade issues to a particular set of tenants.

Prerequisites

Follow the below steps to upgrade from MySQL v5.7 to MySQL v8:

1. Ensure that you are using the AgileApps version 10.16.x. If you use any lower version, upgrade the AgileApps version to 10.16.x version using:

• Fresh installation of AgileApps 10.16.6
• Upgrade to 10.16 using Upgrade utility

Note: After upgrade, ensure that "set scrollTolerantForwardOnly" flag is true in the "com.softwareag.catalina.resource.pid-agileappsRN.properties" and "com.softwareag.catalina.resource.pid-agileappsRN.properties" files at "$INSTALL_DIR\profiles\IS_{instance_name}\configuration\com.softwareag.platform.config.propsloader" folder.

2. Validate that the applications are working fine.
3. Check the charset value with the below command in the existing MySQL 5.7 configuration and ensure that you upgrade to MySQL 8 with the same charset/collation value.
show GLOBAL variables like '%character_set%'
If utf8mb4 is used instead of utf8mb3, you might get a row size too large exception error. However, you can use utf8mb4 for new installation.
4. Perform the following checks in the MySQL utilities

-$ mysqlcheck -u root -pLongjump@123 --all-databases --check-upgrade
→ db1000.INTEGRATION_CONNECTOR_LICENSE_USER_ACTION   OK
→ db1000.INTEGRATION_STAGES                          OK

5. -$ mysqlsh root@localhost:3306 -e "util.checkForServerUpgrade();" The MySQL server at localhost:3306, version 5.7.43 - Check the MySQL Community Server (GPL) for compatibility issues for upgrading to MySQL 8.0.34

6. Check if backup is taken for all the databases, otherwise take a backup of the data directory using a backup or export utility such as mysqldump.
7. Check whether mysqlsh is installed with below command
$ mysqlsh --version
8. If output is similar to below
-bash: mysqlsh: command not found

Then, perform the manual installation.

$ yum install libyaml -y
$ wget https://dev.mysql.com/get/Downloads/MySQL-Shell/mysql-shell-8.0.34-1.el7.x86_64.rpm
$ rpm -ivh mysql-shell-8.0.34-1.el7.x86_64.rpm

9. Ensure that the value of Lower_case_table_names are same as set in MySQL 5.7 configuration.

Note: The default behavior of this parameter is changed in MySQL 8.0 and can have major impact on application. If set to 0, table names are stored as specified and comparisons are case-sensitive. If set to 1, table names are stored in lowercase on disk and comparisons are not case-sensitive. You can find more information in the following points: lower_case_table name 0 signifies that the table name comparison in SQL queries is case-sensitive. lower_case_table name 1 signifies that the table name comparison in SQL queries is case-insensitive. If a product creates a table in one case and queries in a different case, the query will fail if lower_case_table_names is not set to 1. In MySQL 8.x, lower_case_table_names can be set to 1 only when creating new MySQL instances in AWS RDS. In MySQL 8.x, the default for lower_case_table_names is 1. To avoid confusion during the MySQL 8.0 upgrade, always query tables in the same case they were created. It is not advisable to convert existing tables; therefore, the code has to match the case in which the table was created.

In-place upgrade to MySQL v8

1. Download and install mysql 8.

$ rpm -Uvh [[https://repo.mysql.com/mysql80-community-release-el7-3.noarch.rpm]]
$ sed -i 's/enabled=1/enabled=0/' /etc/yum.repos.d/mysql-community.repo
$ yum --enablerepo=mysql80-community install mysql-community-server -y

2. Check MySQL status
$ systemctl status mysqld.service – Ensure that the service is not active.

3. Update the Jar file in the AgileApps installation with mysql-8-connector.jar.

yes | cp mysql-connector-java-8.0.29.jar /opt/softwareag/AgileApps/configurationPanel/mysql-driver.jar
yes | cp mysql-connector-java-8.0.29.jar /opt/softwareag/profiles/IS_default/dropins/mysql-driver.jar

4. Update the my.cnf file [/etc/my.cnf]
a) Update the charset/collation value
i) If you are upgrading from MySQL 5.7, use the same charset/collation value available in MySQL 5.7. For the charset value available in MySQL 5.7, refer step 3 in the above Prerequisites section.

ii) If you are performing a fresh installation of MySQL 8, use the below charset/collation:

• Add the following code to the my.cnf file located in the MySQL Client:
  

default-character-set=utf8mb4

• Add the following code to the my.cnf file located in MySQL Server:
  

character-set-server=utf8mb4
collation-server=utf8mb4_0900_ai_ci

b) Update the sql mode as below:
sql-mode="PIPES_AS_CONCAT,ANSI_QUOTES"

5. Start the MySQL service
$ systemctl start mysqld.service;

6. Populate the Time zone Tables
The platform uses MySQL's time zone tables for time zone conversions. These tables are not automatically populated when MySQL is installed, so it necessary to do so after installation.
Run the following program to initialize the MySQL timezone tables: mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root mysql where /usr/share/zoneinfo is the standard Linux location for the time zone files. (Your system may differ.)
MySQL Version 8.0, see https://dev.mysql.com/doc/refman/8.0/en/mysql-tzinfo-to-sql.html
To specify character settings at MySQL configuration time: MySQL Version 8.0, see https://dev.mysql.com/doc/refman/8.0/en/charset-applications.html

7. Check for logs at /var/log/mysqld.log for any error.
If the upgrade fails due to some errors, the server reverts all changes to the data directory. An attempt to upgrade again to MySQL 8.0 involves:

• If any redo logs are present in the data directory, remove all of them.
• Start the MySQL 5.7 server on the same data directory.
• Fix all the errors that were reported during upgrade.
• Perform an in-place upgrade to MySQL 8.0 again.

Note: You can check out the reserved words list in the Reserved Words page.

Note: For any additional information, please refer to the MySQL documentation.