Upgrading to MySQL v8

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

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. 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

4. -$ 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

5. The following needs to be verified and changed before the MySQL 8.x upgrade.
show GLOBAL variables like '%character_set%'
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

MySQL upgrade approaches

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) 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. 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.

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

7. 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

8. 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: For any additional information, please refer to the MySQL documentation.