[BUG] Unable to import ENGINE=MEMORY tables into Aurora Database

[admerzeau]

I'm trying to move all schemas/data from a Mysql 8.0.35 RDS database to an Aurora Database 3.05.2 using mydumper/myloader. Below the command I'm using (Note that for testing purposes I'm only exporting the empty schema)

mydumper --stream --no-data --threads 8 -u $SOURCE_MYSQL_USER --password=$SOURCE_MYSQL_PASSWORD --host $SOURCE_MYSQL_HOST --port $SOURCE_MYSQL_PORT -M -v 4 -L /schema-logs/log-output.log --regex '^(?!(mysql|performance_schema|information_schema|sys))' --compress --compress-protocol --kill-long-queries --build-empty-files --skip-definer --trx-consistency-only --lock-all-tables -o /schema-data/ | myloader --threads 8 --user $DEST_MYSQL_USER --password=$DEST_MYSQL_PASSWORD --host $DEST_MYSQL_HOST --skip-definer --port $DEST_AURORA_PORT --serialized-table-creation --overwrite-tables -v 4 -L /schema-logs/import-output.log --stream

The issue is that I'm getting the following error that is blocking the whole import process:

** (myloader:14022): CRITICAL **: 04:39:07.538: Thread 17: Error restoring: Storage engine MEMORY is disabled (Table creation is disallowed).

When I checked the logs I noticed that indeed some of the tables in the schemas in the source database are using the Storage Engine MEMORY that it seems that Aurora doesn’t support (https://stackoverflow.com/questions/58739765/unable-to-create-table-with-memory-engine-on-amazon-aurora)

2024-05-08 04:39:07 [INFO] - Thread 17: Creating table demo.t1 from content in //import-20240508-043842/demo.t1-schema.sql.gz. On db: demo
2024-05-08 04:39:07 [WARNING] - Thread 17: Error restoring 1: Storage engine MEMORY is disabled (Table creation is disallowed).
2024-05-08 04:39:07 [WARNING] - Thread 17: Retrying last failed executed statement

When I tried to create a similar table in Aurora, although I got a warning, Aurora is still able to create the table and automatically convert the Engine to InnoDB:

mysql> create database db1;
Query OK, 1 row affected (0.04 sec)

mysql> use db1;
Database changed

mysql> CREATE TABLE t3 (i INT) ENGINE = MEMORY;
Query OK, 0 rows affected, 2 warnings (0.34 sec)

mysql> show create table t3;
+-------+---------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                  |
+-------+---------------------------------------------------------------------------------------------------------------+
| t3    | CREATE TABLE `t3` (
  `i` int DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+-------+---------------------------------------------------------------------------------------------------------------+
1 row in set (0.03 sec)

I think the problem might be that Aurora returns a code that mydumper/myloader recognizes as critical (non recoverable) and stops the entire data import process. Let me know if you need any other extra info to replicate the issue.

• OS version: Amazon Linux 2
• MyDumper version: mydumper v0.16.1-3, built against MySQL 5.7.44-48 with SSL support

[davidducos]

Hi @admerzeau,
You are right, they are reporting an error code different that 0, but they created the table as innodb. We might need to do something similar to --success-on-1146 for this specific case.

[admerzeau]

hi @davidducos - that sounds like a good plan.

[davidducos]

@admerzeau you reported this at the right moment, as I will be releasing the new version in a couple of weeks

[davidducos]

Hi @admerzeau, can you check your backups file if you have the SQL_MODE set with NO_ENGINE_SUBSTITUTION??
As I'm performing a couple of tests and I found that you can avoid this scenario setting the SQL_MODE to NO_ENGINE_SUBSTITUTION. Take into account that you might need to change the -schema file on all the tables that is failing.