Takuma Matsuo

Posted on Dec 30, 2023 • Updated on Apr 2

Version up test against Aurora MySQL 2 EOL with SQL test tool

#aurora #mysql #aws #sql

Introduction

As you know, EOS (end of support) for Amazon Aurora MySQL 2 is finally coming in October 2024.

Are you preparing for migration (upgrade)?

Also, like Aurora MySQL, if you are currently using Amazon RDS for MySQL 5.7, EOS will be reached in October 2023. I think some people will upgrade to RDS for MySQL 8, or some may take this opportunity to migrate to Aurora.

You may have heard of Amazon RDS extended-support service, but it's a paid service and has a time limit.

ref. Using Amazon RDS Extended Support

Test is important!

I think you are planning a version upgrade from Aurora MySQL 2 to Aurora MySQL 3 for the EOS timing. As you know, testing is the most time-consuming part of version upgrades.

So, how do you test it?

I think many organizations often prepare a test environment for Aurora MySQL 3 and perform a series of application tests. You can easily imagine that testing can be quite difficult if the scale of the application is large, the number of target databases is large, or there are time constraints.

"Insight SQL Testing", which is introduced in this post, is a tool that allows you to perform tests efficiently by using SQL tests.

In this post, we will introduce how to perform a SQL assessment using Insight SQL Testing to reduce risks during the upgrade from Aurora MySQL 2 to Aurora MySQL 3.

Incompatible changes when upgrading from Aurora MySQL 2 to Aurora MySQL 3

So, what changes are included in Aurora MySQL 3 (= MySQL 8) ?

Here are some incompatible changes that may cause problems when upgrading from Aurora MySQL 2 to Aurora MySQL 3. Additionally, incompatible changes, especially regarding SQL, can have a significant impact on the use of MySQL by existing applications.

Added reserved words
Changed sorting behavior in GROUP BY clause
- Until 8.0.12, implicit sorting was done using GROUP BY, but as of 8.0.13 this is no longer done.
ref. SQL Changes | MySQL 8.0 Reference Manual

What is particularly noteworthy about the change in GROUP BY is that it was changed when MySQL 8 was upgraded to a minor version, and since SQL execution does not result in an error, it is difficult to notice the change in behavior.

Version upgrade test using SQL test

In SQL tests for the purpose of confirming version upgrades, we check whether the previously prepared SQL behaves the same before and after the version upgrade.

Prepare SQLs
Preparing the test databases
Execute the prepared SQLs against the test databases
Summarize of execution results

It takes a huge amount of effort to manually perform the flow of 'SQL preparation - SQL testing - result evaluation' required for SQL testing. Insight SQL Testing can make this process easy.

How to prepare SQL?

For Aurora MySQL version upgrade testing using Insight SQL Testing, you can prepare SQLs to be tested using the following methods.

Get the general.log of Aurora MySQL in advance from the AWS console etc.
Get the general.log via RDS API from Insight SQL Testing

Note that the SQLs should be configured to be output to the general.log in advance. In this post, we will introduce how to get the general.log via RDS API from Insight SQL Testing.

Preparation

Configure output SQLs to general.log

In the current working environment of Aurora MySQL 2, enable query log output to output SQL information. For Aurora MySQL, configure the following settings in the cluster parameter group.

Set general_log to on.
Set log_output to FILE.

Start Insight SQL Testing

Start IDT Manager, the manager for Insight SQL Testing. IDT Manager is available on AWS Marketplace, so you can easily start IDT Manager in the same way you would normally start EC2.

To retrieve general.log for Aurora MySQL via the RDS API, the required roles must be configured and attached to the EC2 for Insight SQL Testing.

Required policy is as follows.

{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Sid": "VisualEditor1",
            "Effect": "Allow",
            "Action": [
                "rds:DownloadDBLogFilePortion",
                "rds:DescribeDBInstances",
                "rds:DownloadCompleteDBLogFile",
                "rds:DescribeDBLogFiles",
                "rds:DescribeDBClusters"
            ],
            "Resource": [
                "arn:aws:rds:xxxxxxxxxxx:xxxxxxxxxxxx:cluster:*",
                "arn:aws:rds:xxxxxxxxxxx:xxxxxxxxxxxx:db:*"
            ]
        }
    ]
}

Prepare Aurora MySQL clusters for testing

Here, use the following Aurora MySQL clusters:

Current working Aurora MySQL 2 environment (source DB, Aurora MySQL from which SQLs are obtained)
Aurora MySQL 2 test environment (test source DB)
Aurora MySQL 3 test environment (target DB)

The "Aurora MySQL 2 test environment" and "Aurora MySQL 3 test environment" are DBs that will be used in SQL execution test, and the both data must be same. You can prepare them from a snapshot etc.

Assessment with Insight SQL Testing

Assessment with Insight SQL Testing is performed as follows.

Register Aurora MySQL clusters for testing
Prepare evaluation SQL set (registering test SQL)
Perform an assessment

Register Aurora MySQL clusters for testing

Select "NEW" for the "TARGET DATABASE" and input required information.
The target DB should be configured for both Aurora MySQL 2 test environment (test source DB) and Aurora MySQL 3 test environment (target DB).

Prepare evaluation SQL set (registering test SQL)

Select "NEW" for "SQL WORKLOAD" menu and select "Accumulate SQL from Amazon RDS".
Enter SQL workload name, Region, Cluster Identifier, and Database name to begin the process. Once creation is complete, it will be displayed in the evaluation SQL set list.
You can see collected SQLs from the current working Aurora MySQL 2 by opening the created evaluation SQL set.

Perform an assessment

After creating the evaluation SQL set, run a assessment.

Select "NEW" for "ASSESSMENT" menu.
Input the assessment name and select "2DB assessment".
Set the created SQL workload.
Set the target databases. Aurora MySQL 3 for "Target database" and Aurora MySQL 2 for "Test source database".
Select "Execution" for execution type and set password for db user. Then select "NEW".
You can see the created assessment in assessment list.

Check the Assessment result

Once the assessment is complete, check the assessment results. You can see the summary of the assessment result.

In the summary view, you can check the number of SQLs that resulted in each result category. Error, Different result, Performance degradation, and success. From the summary view, you can see SQL execution details by drill-down to each SQL.

For example, if you have the following SQL, it may be the status of "Different returns".

select max(ename) from emp group by ename;

Because the behavior of group by clause has changed in Aurora MySQL 3 (MySQL 8.0), you can see that the difference in the order of the select results is detected.

Conclusion

In this post, we introduced how to use SQL testing using Insight SQL Testing to check for version upgrades in conjunction with Aurora MySQL 2 EOS.

Insight SQL Testing is available on AWS Marketplace, Insight SQL Testing Manager (Hourly Billing) . Please contact us from AWS Marketplace page for further details. You can find the our contact info here, Insight SQL Testing Manager (Hourly Billing) | Support.

DEV Community