Trouble Upgrading MySQL 5.7 to 8: The auxiliary tables of FTS indexes on the table are created in system table-space due to bug

My team and I are attempting to upgrade our multi-tenancy AWS RDS/MySQL instance from MySQL 5.7 to 8. After trying the upgrade, we received a report showing a large number of errors for the customers, and lineitems tables.

Example Errors

{
    "level": "Error",
    "dbObject": "@[email protected]",
    "description": " The auxiliary tables of FTS indexes on the table '@[email protected]' are created in system table-space due to https://bugs.mysql.com/bug.php?id=72132. In MySQL8.0, DDL queries executed on this table shall cause database unavailability. To avoid that, drop and recreate all the FTS indexes on the table or rebuild the table using ALTER TABLE query before the upgrade."
},

{
    "level": "Error",
    "dbObject": "@[email protected]",
    "description": " The auxiliary tables of FTS indexes on the table '@[email protected]' are created in system table-space due to https://bugs.mysql.com/bug.php?id=72132. In MySQL8.0, DDL queries executed on this table shall cause database unavailability. To avoid that, drop and recreate all the FTS indexes on the table or rebuild the table using ALTER TABLE query before the upgrade."
},

Note: @007b and @007d equal { and }

Our RDS/MySQL server contains a database for each client we have with the name of the database in the format of {UUID}. Each table may or may not have a customers and/or lineitems table. Each of those tables may or may not contain a FULLTEXT index.

I wrote a Laravel job that fetches the list of databases and then performs the following actions for each of the customers and lineitems tables if they exist:

• Check for the FULLTEXT index.
• Drop the FULLTEXT if it exists.
• Run the query ALTER TABLE <table_name> ENGINE = InnoDB;
• Run the query OPTIMIZE TABLE <table_name>;

After the job was completed (~5+ hours), we attempted to perform the upgrade again and received a single error.

{
  "id": "auroraGetDanglingFulltextIndex",
  "title": "Tables with dangling FULLTEXT index reference",
  "status": "ERROR",
  "description": "Table '@007bb281b00a-14bb-4097-aa29-e9432f2e55db@007d.#sql-ib32025827-3477973988' doesn't exist"
},

We’ve been attempting this upgrade for a few weeks now and this is just our latest attempt.

In our previous attempts (Before adding the OPTIMIZE step in the job below), we were receiving a single error like the one above except the .#sql-ib32025827-3477973988 part would be equal to either lineitems or customers. We could manually run the job for that database/table and attempt the upgrade process again, but another singular error would be returned for a different database/table.

Here is the Laravel job handler:

    public function handle()
    {
        try {
            config([
                'database.connections.auto_master.database' => $this->database,
            ]);

            DB::purge('auto_master');

            // Attempt to reconnect to the database 5 times with a 100ms delay
            retry(
                5,
                function () {
                    DB::reconnect('auto_master');
                },
                100
            );

            $customersTableExists = Schema::connection('auto_master')->hasTable(
                'customers'
            );

            $nameIndexExists =
                $customersTableExists &&
                DB::select('SHOW INDEX FROM customers WHERE Key_name = ?', [
                    'name',
                ]);

            if ($customersTableExists) {
                Schema::connection('auto_master')->table('customers', function (
                    Blueprint $table
                ) use ($nameIndexExists) {
                    if ($nameIndexExists) {
                        $table->dropIndex('name');
                    }

                    // $table->fullText(['FirstName', 'LastName'], 'name');
                });

                DB::connection('auto_master')->statement(
                    'ALTER TABLE customers ENGINE = InnoDB;'
                );

                // Run Optimize Table to rebuild the table and fix the FTS index
                DB::connection('auto_master')->statement(
                    'OPTIMIZE TABLE customers;'
                );
            }

            $lineitemsTableExists = Schema::connection('auto_master')->hasTable(
                'lineitems'
            );

            $lineitemsDescriptionFullIndexExists =
                $lineitemsTableExists &&
                DB::select('SHOW INDEX FROM lineitems WHERE Key_name = ?', [
                    'lineitems_description_full',
                ]);

            if ($lineitemsTableExists) {
                Schema::connection('auto_master')->table('lineitems', function (
                    Blueprint $table
                ) use ($lineitemsDescriptionFullIndexExists) {
                    if ($lineitemsDescriptionFullIndexExists) {
                        $table->dropIndex('lineitems_description_full');
                    }

                    // $table->fullText('description', 'lineitems_description_full');
                });

                DB::connection('auto_master')->statement(
                    'ALTER TABLE lineitems ENGINE = InnoDB;'
                );

                // Run Optimize Table to rebuild the table and fix the FTS index
                DB::connection('auto_master')->statement(
                    'OPTIMIZE TABLE lineitems;'
                );
            }
        } catch (Throwable $th) {
            // Log the error

            throw $th;
        }
    }

And this is the query that selects the databases/schemas and creates the jobs.

        $databases = DB::select('SHOW DATABASES');

        $databasesFiltered = array_filter($databases, function ($database) use (
            $schema
        ) {
            if ($schema) {
                return $database->Database === $schema;
            }

            return preg_match('/^{[a-f0-9-]+}$/', $database->Database);
        });

        foreach ($databasesFiltered as $database) {
            FixAuxBugForInnoDBFTSIndexesJob::dispatch($database->Database);
        }

We’re continuing to try different options, but each attempt is taking us anywhere from 4 to 8 hours to run through.

If anyone has any suggestions, it would be greatly appreciated!

Oct 25th update

We were able to run the following command and it returned some results that seemed promising.

SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES WHERE NAME LIKE '%#sql-ib32025827-3477973988%';

Based on these results, we attempted to drop the table in various ways, but all our efforts resulted in returning a "table not found" error.

These are a few of our attempts:

As-is

DROP TABLE `@007bb281b00a@002d14bb@002d4097@002daa29@002de9432f2e55db@007d`.`#sql-ib32025827-3477973988`;

Convert encoded characters

DROP TABLE `{b281b00a-14bb-4097-aa29-e9432f2e55db}`.`#sql-ib32025827-3477973988`;

Prepend "#mysql50#" to table name as suggested by AWS

DROP TABLE `{b281b00a-14bb-4097-aa29-e9432f2e55db}`.`#mysql50##sql-ib32025827-3477973988`;