Aurora MySQL 3.12.0 - 8.4.7 Precheck Failed With checkTableCommand Due to Missing Tables

Question

We were unable to upgrade Aurora MySQL from 3.12.0 to 8.4.7, it failed on prechecks:
```
        {
            "id": "checkTableCommand",
            "title": "Issues reported by 'check table x for upgrade' command",
            "status": "OK",
            "description": "Issues reported by 'check table x for upgrade' command",
            "detectedProblems": [
                {
                    "level": "Error",
                    "dbObject": "bdm.fts_00000000000028f_0000000000002ca5_index_1",
                    "description": "Table 'bdm.fts_00000000000028f_0000000000002ca5_index_1' doesn't exist"
                },
                {
                    "level": "Error",
                    "dbObject": "bdm.fts_00000000000028f_0000000000002ca5_index_2",
                    "description": "Table 'bdm.fts_00000000000028f_0000000000002ca5_index_2' doesn't exist"
                },
                {
                    "level": "Error",
                    "dbObject": "bdm.fts_00000000000028f_0000000000002ca5_index_3",
                    "description": "Table 'bdm.fts_00000000000028f_0000000000002ca5_index_3' doesn't exist"
                },
                {
                    "level": "Error",
                    "dbObject": "bdm.fts_00000000000028f_0000000000002ca5_index_4",
                    "description": "Table 'bdm.fts_00000000000028f_0000000000002ca5_index_4' doesn't exist"
                },
                {
                    "level": "Error",
                    "dbObject": "bdm.fts_00000000000028f_0000000000002ca5_index_5",
                    "description": "Table 'bdm.fts_00000000000028f_0000000000002ca5_index_5' doesn't exist"
                },
                {
                    "level": "Error",
                    "dbObject": "bdm.fts_00000000000028f_0000000000002ca5_index_6",
                    "description": "Table 'bdm.fts_00000000000028f_0000000000002ca5_index_6' doesn't exist"
                },
                {
                    "level": "Error",
                    "dbObject": "bdm.fts_00000000000028f_being_deleted",
                    "description": "Table 'bdm.fts_00000000000028f_being_deleted' doesn't exist"
                },
                {
                    "level": "Error",
                    "dbObject": "bdm.fts_00000000000028f_being_deleted_cache",
                    "description": "Table 'bdm.fts_00000000000028f_being_deleted_cache' doesn't exist"
                },
                {
                    "level": "Error",
                    "dbObject": "bdm.fts_00000000000028f_config",
                    "description": "Table 'bdm.fts_00000000000028f_config' doesn't exist"
                },
                {
                    "level": "Error",
                    "dbObject": "bdm.fts_00000000000028f_deleted",
                    "description": "Table 'bdm.fts_00000000000028f_deleted' doesn't exist"
                },
                {
                    "level": "Error",
                    "dbObject": "bdm.fts_00000000000028f_deleted_cache",
                    "description": "Table 'bdm.fts_00000000000028f_deleted_cache' doesn't exist"
                }
            ]
        },
```
After some research, it seems these tables are related to a FULLTEXT index that we have migrated from mysql 5.7 to 8.0 previously.
These tables are list in information_schema.INNODB_TABLES, but we cannot delete those records to pass the prechecks.
Is there something we can do to resolve the prechecks?

re:Post Agent · Accepted Answer

The errors you're encountering are related to dangling FULLTEXT index tables that exist in the InnoDB data dictionary but are missing from the actual database. This is a known issue that can occur when FULLTEXT indexes aren't properly cleaned up during previous migrations.

To resolve this issue, you have several options:

**1. Identify and recreate the parent table:**
First, identify which table these FULLTEXT index tables belong to. The naming pattern `fts_00000000000028f_*` indicates these are auxiliary tables for a FULLTEXT index. You can try to find the parent table by searching for tables with FULLTEXT indexes in the `bdm` database.

**2. Optimize the parent table:**
If you can identify the parent table that originally had the FULLTEXT index, try running:
```sql
OPTIMIZE TABLE bdm.your_parent_table;
```
This command can help rebuild the FULLTEXT index structure and potentially clean up the dangling references.

**3. Recreate the table:**
If the OPTIMIZE TABLE approach doesn't work, you may need to recreate the parent table entirely. This involves:
- Creating a backup of the table data
- Dropping the table (which should remove all associated FULLTEXT auxiliary tables)
- Recreating the table with the same structure
- Reloading the data

**4. Drop the FULLTEXT index:**
If the table still exists but you no longer need the FULLTEXT index, you can drop the index from the parent table, which should clean up the auxiliary tables.

Since these tables are listed in INNODB_TABLES but don't physically exist, this indicates a metadata inconsistency. The precheck is correctly identifying this as an error-level issue that must be resolved before the upgrade can proceed. After applying any of these remediation steps, retry the upgrade to see if the prechecks pass.  
**Sources**  
[Precheck descriptions for upgrading Aurora MySQL version 3 to version 8.4 - Amazon Aurora](https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/AuroraMySQL.upgrade-prechecks-v3-to-v84.descriptions.html)  
[Finding the reasons for Aurora MySQL major version upgrade failures - Amazon Aurora](https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/AuroraMySQL.Upgrading.failure-events.html)  
[Best practices for upgrading Amazon RDS for MySQL 8.0 to 8.4 with prechecks, Blue/Green, and rollback | AWS Database Blog](https://aws.amazon.com/blogs/database/best-practices-for-upgrading-amazon-rds-for-mysql-8-0-to-8-4-with-prechecks-blue-green-and-rollback/)

Aurora MySQL 3.12.0 -> 8.4.7 Precheck Failed With checkTableCommand Due to Missing Tables

Add your answer

Relevant content