[SOLVED] 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": "@007b852f8697-856d-4fb0-9e3f-69f79e0ce59c@007d.customers",
    "description": " The auxiliary tables of FTS indexes on the table '@007b852f8697-856d-4fb0-9e3f-69f79e0ce59c@007d.customers' 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": "@007b852f8697-856d-4fb0-9e3f-69f79e0ce59c@007d.lineitems",
    "description": " The auxiliary tables of FTS indexes on the table '@007b852f8697-856d-4fb0-9e3f-69f79e0ce59c@007d.lineitems' 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.

Database	Tables
`{852f8697-856d-4fb0-9e3f-69f79e0ce59c}`	customers, lineitems
`{466fe859-89c9-42df-95bf-49c600f18b2}`	lineitems
`{47e2eb66-9e5e-429f-8f87-f43638cd6b02}`	customers
...100s more.	...

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%';

TABLE_ID	NAME	FLAG	N_COLS	SPACE	FILE_FORMAT	ROW_FORMAT	ZIP_PAGE_SIZE	SPACE_TYPE
8990518	@007bb281b00a@002d14bb@002d4097@002daa29@002de9432f2e55db@007d/#sql-ib32025827-3477973988	33	27	8717504	Barracuda	Dynamic	0	Single

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`;