Hello. I am @hoshino from the DBRE team.

The Database Reliability Engineering (DBRE) team operates as a cross-functional organization, tackling database-related challenges and building platforms that balance organizational agility with effective governance. Database Reliability Engineering (DBRE) is a relatively new concept, and only a few companies have established dedicated DBRE organizations. Among those that do, their approaches and philosophies often differ, making DBRE a dynamic and continually evolving field.

For information on the background of the establishment of the DBRE team at our company and the team's role, please refer to our tech blog, "The Need for DBRE at KTC.”

This article discusses an issue encountered during the migration from Amazon Aurora MySQL 2 to Amazon Aurora MySQL 3, where the execution of the mysqldump command terminates unexpectedly without displaying an error message. I hope this proves helpful.

The root cause of the error

Let's start by explaining the cause of the error. The process terminated without an error message in this case because the collation set in the trigger of the Amazon Aurora MySQL 2 database was utf8mb4_0900_ai_ci, which is not supported in MySQL 5. As a result, mysqldump was unable to recognize it.

The investigation process that led to identifying the root cause and determining the solution will be explained in detail below.

The phenomenon that occurred

When executing the mysqldump command directly to export data from Aurora MySQL 2, the process unexpectedly terminated without generating an error message.

After executing the command, I checked the exit code, and 2 (Internal Error) was returned.
It was evident that an error had occurred, but the exact cause could not be determined.

$ mysqldump --defaults-extra-file=/tmp/sample.cnf > sample.sql
$ echo $?
2

Cause investigation.

To determine the root cause of the issue, I followed these steps.

First, I examined the behavior by running a different version of the mysqldump command.

This time, I am utilizing the mysqldump command from the MySQL 5.7 series for Aurora MySQL 2.

$ mysqldump --version
mysqldump  Ver 10.13 Distrib 5.7.40, for linux-glibc2.12 (x86_64)

I attempted to perform the export using the mysqldump command from MySQL 8.

$ mysqldump80 --version
mysqldump  Ver 8.0.31 for Linux on x86_64 (MySQL Community Server - GPL)

$ mysqldump80 --defaults-extra-file=/tmp/sample.cnf > sample.sql
$ echo $?
0

The result was successful. This indicates that the error might be caused by a version difference in MySQL.

Furthermore, to investigate the possibility that the mysqldump command itself might be causing an internal error, I tested various options to determine whether any error messages appeared. The result showed that adding the --skip-triggers option prevents the error.

$ mysqldump --defaults-extra-file=/tmp/sample.cnf --skip-triggers > sample.sql
$ echo $?
0

The result suggests that the error occurs in the trigger-related part. So, I checked the trigger settings.

mysql> SHOW TRIGGERS FROM sample_database \G
*************************** 1. row ***************************
             Trigger: sample_trigger
               Event: UPDATE
               Table: sample_table
           Statement: BEGIN
  SET NEW.`lock_version` = OLD.`lock_version` + 1;
END
              Timing: BEFORE
             Created: 2024-10-04 01:06:38.17
            sql_mode: STRICT_TRANS_TABLES
             Definer: sample-user@%
character_set_client: utf8mb4
collation_connection: utf8mb4_general_ci
  Database Collation: utf8mb4_0900_ai_ci
*************************** 2. row ***************************
(The rest is omitted)

Here, I noticed that the database collation was set to utf8mb4_0900_ai_ci. This is a collation that is not recognized by MySQL 5.

I modified the trigger definition of the table where the error occurred to utf8mb4_general_ci and then executed the mysqldump command again.

mysql> SHOW TRIGGERS FROM kinto_terms_tool \G
*************************** 1. row ***************************
             Trigger: sample_trigger
               Event: UPDATE
               Table: sample_table
           Statement: BEGIN
  SET NEW.`lock_version` = OLD.`lock_version` + 1;
END
              Timing: BEFORE
             Created: 2024-10-04 01:06:38.17
            sql_mode: STRICT_TRANS_TABLES
             Definer: sample-user@%
character_set_client: utf8mb4
collation_connection: utf8mb4_general_ci
  Database Collation: utf8mb4_general_ci
*************************** 2. row ***************************
(The rest is omitted)

$ mysqldump --defaults-extra-file=/tmp/sample.cnf > sample.sql
$ echo $?
0

The mysqldump command was successful.
This difference in collation also explains the successful execution of commands in MySQL 8.

This investigation revealed that the mysqldump failed because the database collation set in the trigger was utf8mb4_0900_ai_ci, which does not exist in MySQL 5.

Relationship between Amazon Aurora MySQL 2 and MySQL 5.7

Amazon Aurora MySQL 2 is based on MySQL 5.7, but the two are not entirely identical. AWS has added its own extension functions to Aurora, incorporating some features from MySQL 8.0, such as the utf8mb4_0900_ai_ci collation sequence, which was the root cause of the problem.

When I try to specify utf8mb4_0900_ai_ci as a collation in MySQL 5.7, the following error occurs:

mysql> ALTER DATABASE sample_database CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
ERROR 1273 (HY000): Unknown collation: 'utf8mb4_0900_ai_ci'

On the other hand, the same command is executed normally in Aurora MySQL 2.

mysql> ALTER DATABASE sample_database CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
Query OK, 1 row affected (0.03 sec)

mysql> SHOW CREATE DATABASE sample_database;
+------------------+---------------------------------------------------------------------------------------------------------+
| Database         | Create Database                                                                                         |
+------------------+---------------------------------------------------------------------------------------------------------+
| sample_database  | CREATE DATABASE `sample_database` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ |
+------------------+---------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

Further investigation

To determine if the error was solely caused by the trigger, I examined other MySQL objects as well.

In an Aurora MySQL 2 environment, I created a view with the collation set to utf8mb4_0900_ai_ci and observed its behavior during the dump process.

CREATE VIEW customer_view AS
SELECT
    customer_name COLLATE utf8mb4_0900_ai_ci AS sorted_name,
    address
FROM
    customers;

When I run the mysqldump command, it succeeds without any errors.

$ mysqldump --defaults-extra-file=/tmp/sample.cnf > sample.sql
$ echo $?
0

Next, I conducted the same test with a stored procedure in the Aurora MySQL 2 environment.

DELIMITER //

CREATE PROCEDURE sample_procedure()
BEGIN
    DECLARE customer_name VARCHAR(255);

    -- String manipulation with specified collation
    SET customer_name = (SELECT name COLLATE utf8mb4_0900_ai_ci FROM customers WHERE id = 1);
    
    -- Comparison using collation
    IF customer_name COLLATE utf8mb4_0900_ai_ci = 'sample' THEN
        SELECT 'Match found!';
    ELSE
        SELECT 'No match.';
    END IF;
END //

DELIMITER ;

In this case as well, the dump completes successfully without any issues.

$ mysqldump --defaults-extra-file=/tmp/sample.cnf > sample.sql
$ echo $?
0

Aurora MySQL 2 allows you to use the collation utf8mb4_0900_ai_ci, which does not exist in MySQL 5.
However, I discovered that when the mysqldump command is based on MySQL 5, it fails to recognize this collation, resulting in errors, particularly in trigger-related sections.

Since the issue does not occur with views or stored procedures, I suspect that the problem is related to how collation is handled in triggers.

Solution

The problem in question occurred because the collation of the database set in the trigger was utf8mb4_0900_ai_ci, which is not supported in MySQL 5.

To address the error, I changed the database collation to utf8mb4_general_ci and reconfigured the trigger. This enables the mysqldump command in MySQL 5.7 to correctly recognize the collation, allowing the export to be performed successfully.

ALTER DATABASE sample_database CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
-- Recreate the trigger if necessary

Another solution is to use the mysqldump command for MySQL 8.0. MySQL 8.0 clients can recognize utf8mb4_0900_ai_ci, so it is possible to export without changing the collation of the database.

$ mysqldump80 --defaults-extra-file=/tmp/sample.cnf > sample.sql
$ echo $?
0

In certain situations, changing the client version may not be feasible due to environmental or other dependency constraints.

Conclusion

In this case, the mysqldump command terminated without displaying any error messages, and it was only by checking the exit code that I discovered an error had occurred. If the process concludes without an error message like this, there is a risk of unknowingly exporting or importing incomplete data. Therefore, when backing up or migrating a database, it is crucial to verify the processing results, such as by checking the exit code.

Aurora MySQL 2 has already reached its End of Life (EOL) and is no longer supported. Please be mindful if you still have any environments running on Aurora MySQL 2 and are planning a migration