Aurora MySQL 5.7 to 8.0 Upgrade Issue with general_log_backup

Question

I am upgrading our Amazon Aurora from 5.7 to 8.0 and I got this error in the precheck logs.

    20) Tables recognized by InnoDB that belong to a different engine
  Error: Following tables are recognized by InnoDB engine while the SQL layer
    believes they belong to a different engine. Such situation may happen when
    one removes InnoDB table files manually from the disk and creates e.g. a
    MyISAM table with the same name.

    A possible way to solve this situation is to e.g. in case of MyISAM table:

    1. Rename the MyISAM table to a temporary name (RENAME TABLE).
    2. Create some dummy InnoDB table (its definition does not need to match),
    then copy (copy, not move) and rename the dummy .frm and .ibd files to the
    orphan name using OS file commands.
    3. The orphan table can be then dropped (DROP TABLE), as well as the dummy
    table.
    4. Finally the MyISAM table can be renamed back to its original name.

  mysql.general_log_backup - recognized by the InnoDB engine but belongs to CSV

Has anyone seen this? Since this is in the "mysql" database, I don't have any access to this at all (I can't drop this). But it seems weird that upgrade check utility will recognize this as an error.

I even ran:

[bin (*)]$ mysqlcheck ... --repair mysql general_log_backup
mysql.general_log_backup                           OK

Then when I look at the source code for the mysql shell, I am not sure why it is not excluding the "mysql" database. I feel like this might be a bug but I couldn't find it here.

Upon further investigation, I ran this query and got this result

SELECT a.table_schema,
       a.table_name,
       concat('recognized by the InnoDB engine but belongs to')
FROM information_schema.tables a
JOIN
  (SELECT substring_index(NAME, '/', 1) AS table_schema,
          substring_index(substring_index(NAME, '/', -1), '#', 1) AS TABLE_NAME
   FROM information_schema.innodb_sys_tables
   WHERE NAME like '%/%') b ON a.table_schema = b.table_schema
AND a.table_name = b.table_name
WHERE a.engine != 'Innodb'

+--------------+--------------------+----------------------------------------------------------+
| table_schema | table_name         | concat('recognized by the InnoDB engine but belongs to') |
+--------------+--------------------+----------------------------------------------------------+
| mysql        | general_log_backup | recognized by the InnoDB engine but belongs to           |
+--------------+--------------------+----------------------------------------------------------+
1 row in set (0.11 sec)

And it is because this entry is present in the innodb_sys_tables.

mysql> select * from information_schema.innodb_sys_tables where NAME like '%general%';
+----------+--------------------------+------+--------+-------+-------------+------------+---------------+------------+
| TABLE_ID | NAME                     | FLAG | N_COLS | SPACE | FILE_FORMAT | ROW_FORMAT | ZIP_PAGE_SIZE | SPACE_TYPE |
+----------+--------------------------+------+--------+-------+-------------+------------+---------------+------------+
|    16462 | mysql/general_log_backup |   33 |      9 | 16448 | Barracuda   | Dynamic    |             0 | Single     |
+----------+--------------------------+------+--------+-------+-------------+------------+---------------+------------+
1 row in set (0.09 sec)

Why is there such an entry in the innodb_sys_tables? How can I remove this entry?

Answer 1

This is an AWS bug. At the moment there is no fix available from our side. I have contacted them and the only way around this is for an AWS RDS Engineer to be on hand when you perform an update to manually bypass this pre-check for you.

I have logged the bug with aws here if anyone encounters this - the more upvotes the more likely they are to address it quickly: https://repost.aws/questions/QUOCwE_YkxQoSKBI6Ev8VBZw/how-can-i-bypass-prechecks-for-rds-engine-upgrades

In the meantime I would suggest doing the following to speed up the process:

1. Initiate a Blue/Green deployment from the RDS console
2. Wait for the process to finish creating your Blue/Green deployment, new Aurora Cluster and Aurora instance
3. The process will eventually get stuck in "Provisioning" and if you check the Status on the Green Environment status you will see "DB engine version upgrade" is stuck in "In progress"
4. Take note of your Green database ARN
5. Send a support request to AWS asking them to bypass the Tables recognized by InnoDB that belong to a different engine check, and importantly, quote your ARN

They may require you to schedule a phone call, but with the steps above completed this should be a 5 minute operation.

Good luck!