Upgrading to MySQL 8.0

With Bahmni Standard 1.0 release, Bahmni will be shipping with MySQL 8.0 as the default database version for OpenMRS service and reports service. Refer to this Jira EPIC for more details.

The below migration steps involves upgrading the database engine. So we highly recommend to try out these steps on a Test or Pre-Prod environment before running through these on production database.

Make sure to have a full backup of Bahmni before following the below steps.

There are different scenarios on how your instance of Bahmni would be configured with a database instance. If you are using the database server on managed service provided by cloud infrastructure (Ex. RDS on AWS) then please consult the provider’s documentation and migration steps for your database server. The below steps covers when you are running database server as a docker container.

On this page:

1 Migrating from Bahmni on Cent-OS:
2 Migrating from MySQL 5.6 or MySQL 5.7 already running on Docker:
- 2.1 Migrating from MySQL 5.6
- 2.2 Migrating from MySQL 5.7:
3 Upgrading RDS Instance from MySQL 5.7 to MySQL 8.0:
4 Impacts with the MySQL 8.0 upgrade

Migrating from Bahmni on Cent-OS:

If your Bahmni instance is running on CentOS with Bahmni 0.93 or Bahmni 0.92, we recommend migrating to Bahmni on Docker. Following the steps on this documentation will help you migrate your entire Bahmni instance to Docker. The process involves taking the database backup and file artifacts backup and restoring it on a MySQL 8.0 instance.

Migrating from MySQL 5.6 or MySQL 5.7 already running on Docker:

If your instance of Bahmni is already running on docker with the database services part of docker compose, then you can make an upgrade on the existing database instance without the need for backup and restore on a new instance. This would require a downtime to Bahmni application, so requesting to plan accordingly.

Migrating from MySQL 5.6

Migration from MySQL 5.6 to MySQL 8.0 involves a two step process. First you need to upgrade to MySQL 5.7 and then to MySQL 8.0. If your database instances are already running with MySQL 5.7 skip the steps below and move to Migrating from 5.7 section.

Step 1: Stopping the services connected to OpenMRS Database

The first step involves stopping the services having database connection to OpenMRS database. Run the below commands from bahmni-docker/bahmni-lite or bahmni-docker/bahmni-standard subdirectory. Pass the --env-file option to the below docker compose commands, if you have previously used a different env file other than .env for your implementation.

docker compose stop openmrs
docker compose stop openmrsdb
docker compose stop reports
docker compose stop reportsdb

Step 2: Updating the image tags for services and starting the database

Now open your implementation specific .env file with a text editor such as nano or vi. Update the values of the variables as mentioned below.

OPENMRS_DB_IMAGE_NAME=mysql:5.7
REPORTS_DB_IMAGE_NAME=mysql:5.7

Now run the below commands to bring up both the database services from MySQL 5.6 to MySQL 5.7.

docker compose up -d openmrsdb
docker compose up -d reportsdb

Step 3: Performing mysql_upgrade

The next step is to trigger the mysql_upgrade client command inside the database container’s shell. This command will migrate the MySQL system schema to MySQL 5.7 from MySQL 5.6

Exec into each of openmrsdb container and reportsdb container by running the command below.

You will get a bash shell, within the shell run the command below

Once the command completes successfully, then exit the container shell by running exit command. Now restart the service by running the below command

Follow the above steps for reportsdb container as well.

Step 4: Starting up the application services

Now you can bring up the OpenMRS and Reports services up again by running the below command,

Now you have your Bahmni instance running with MySQL 5.7. We recommend validating the critical scenarios such as login, reports, verifying existing data before proceeding to upgrade to MySQL 8.0.

Migrating from MySQL 5.7:

Step 1: Stopping the services connected to OpenMRS Database

The first step involves stopping the services having database connection to OpenMRS database. Run the below commands from bahmni-docker/bahmni-lite or bahmni-docker/bahmni-standard subdirectory. Pass the --env-file option to the below docker compose commands, if you have previously used a different env file other than .env for your implementation.

Step 2: Updating the image tags for services and starting the database

Now open your implementation specific .env file with a text editor such as nano or vi. Update the values of the variables as mentioned below.

Now run the below commands to bring up both the database services from MySQL 5.6 to MySQL 5.7.

Step 3: Updating the default authentication_plugin:

One of the significant change in MySQL 8.0 is the use of caching_sha2_password as the default authentication plugin. This improves security and performance. Read more about it here.

Exec into the OpenMRS DB service by running the below command

Now login to mysql as root user

Update the default plugin to caching_sha2_password by running the below command. Make sure to update the values of OMRS_DB_USERNAME and OMRS_DB_PASSWORD from .env in the below command

Verify whether the plugin is updated by running

Follow the same steps Reports Database as well for Reports-DB-User

Upgrading RDS Instance from MySQL 5.7 to MySQL 8.0:

Bahmni Infra automation terraform scripts provision an AWS RDS MySQL instance. If your implementation uses deployment via terraform, then you need to update your fork of bahmni-infra with the below mentioned steps.

We recommend applying the configuration first on a staging or pre-prod environment and then apply these on prod environment

The overall changes required for the upgrade in the terraform configuration can be seen here. You can either rebase your fork of bahmni-infra repository to pull in these changes or follow the commits step by step in the sequence mentioned below. Please do a terraform plan and validate the changes before running terraform apply

Step 1: Enable Major version upgrades in RDS

Terraform has an attribute for aws_db_instance resource to enable or disable major version upgrades. A new variable has been introduced in terraform configuration. Please refer this commit to add the flag.

Step 2: Adding new parameter group for MySQL 8 RDS Instance

A new custom parameter group needs to be created to be used for the upgraded RDS instance. Refer this commit for the required code changes.

Step 3: Updating RDS version and parameter

This step will upgrade the RDS instance engine version to MySQL 8.0. This will cause downtime so, plan accordingly.

Update the value of mysql_version variable in your environment specific .tfvars file (Ex: nonprod.tfvars) to 8.0. Refer this commit.
Update the mapped parameter group for RDS instance. Refer this commit.

Impacts with the MySQL 8.0 upgrade

1. Strict query validation

Starting MySQL 8.0, the ONLY_FULL_GROUP_BY sql mode is enabled by default. This default setting may lead to the failure of certain queries related to patient queues and other reporting queries. As a temporary solution, the ONLY_FULL_GROUP_BY mode is disabled through an environment variable in Bahmni. It is anticipated that this workaround may be eliminated in upcoming releases.

2. Performance Impact in Ward List queries

When migrating to MySQL 8.0, it was noted that the query retrieving admitted patients in the ADT module was experiencing significant delays, leading to API requests timing out. To address this issue, an optimization strategy was implemented to cache certain constant values, effectively resolving the performance impact.

For this fix, a liquibase migration is added to bahmni-core module which updates the query defined in emrapi.sqlGet.wardsListDetails. If your implementation has re-configured the query, we recommend refactoring your existing query to the use the cached variables and test for performance impacts.

3. MySQL Stored Procedure Creation requires SUPER Privilege

With MySQL 8.0, binary logging is enabled. As a result, when a stored procedure or function needs to be created, MySQL requires SUPER privilege to be used.

If you are manually creating a stored procedure, use the root user.

If application database user like openmrs-user is creating a procedure with liquibase, then temporarily enable trusting of function creators. This can be enabled by running the below command in the MySQL shell openmrsdb container.