Unable to migrate database to Aurora

Table of Contents

Problem:

Once we try to migrate the rds node into the aurora using the snapshot or using the inbuilt option from “RDS –> Actions –> Create Aurora Read Replica“, we may face the below error/issue due to which the rds to aurora migration failed.

Failed to migrate from mysql 5.7.12 to aurora-mysql 5.7.12. Reason: Unable to migrate database to Aurora. Some of the tables in this snapshot are corrupted. Please try migrating a different snapshot or dump and load to a new database and try to migrate.

Solution

Once you will see over the error message then we might think about the snapshot is corrupted or really we have some tables inside the database which are corrupted BUT

To fix this migration, we need to make sure that the RDS instance is following all the pre-requisites mentioned here. i.e. We need to make sure we are not using the MyISAM engine tables and tables with row_format=compressed. If RDS node is running with these 2 limitations then our RDS to aurora migration will be failed.

To get a list of tables which are using the MyISAM engine tables or compressed row format :

select concat(TABLE_SCHEMA, '.', TABLE_NAME) as `==> MyISAM or Compressed Tables`,Engine, row_format,
round(((data_length + index_length) / 1024 / 1024), 2) "Approx size (MB)"
from INFORMATION_SCHEMA.TABLES
where
  ENGINE <> 'InnoDB'
  and
  (
    -- User tables
    TABLE_SCHEMA not in ('mysql', 'performance_schema',
                         'information_schema')
    or
    -- Non-standard system tables
    (
      TABLE_SCHEMA = 'mysql' and TABLE_NAME not in
        (
          'columns_priv', 'db', 'event', 'func', 'general_log',
          'help_category', 'help_keyword', 'help_relation',
          'help_topic', 'host', 'ndb_binlog_index', 'plugin',
          'proc', 'procs_priv', 'proxies_priv', 'servers', 'slow_log',
          'tables_priv', 'time_zone', 'time_zone_leap_second',
          'time_zone_name', 'time_zone_transition',
          'time_zone_transition_type', 'user'
        )
    )
  )
  or
  (
    -- Compressed tables
       ROW_FORMAT = 'Compressed'
  ); 
+---------------------------------+--------+------------+------------------+
| ==> MyISAM or Compressed Tables | Engine | row_format | Approx size (MB) |
+---------------------------------+--------+------------+------------------+
| test.testing1                   | InnoDB | Compressed |             4.00 |
| test.testing2                   | MyISAM | Dynamic    |             0.00 |
+---------------------------------+--------+------------+------------------+
2 rows in set (0.04 sec)

Before starting the migration into aurora, we need to make sure to convert above tables from MyISAM to InnoDB engine and the table which are present into the compressed row format into the Dynamic like below.

alter table test.testing1 row_format=dynamic;
Query OK, 0 rows affected (1.88 sec)
Records: 0  Duplicates: 0  Warnings: 0
alter table test.testing2 engine=innodb;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

Once we will fix the above limitations inside the rds instance, we can smoothly migrate the rds node into the aurora without any issues.

Leave a Reply