Orphan tables

MySQL | PostgreSQL | SQL Server

This page discusses known issues with orphan tables in MySQL.

What are orphan tables?

Orphan tables are tables with disconnected definitions across MySQL data dictionaries and can occur in MySQL 5.6 or MySQL 5.7. Either of the following scenarios can block a major version upgrade (MVU) from MySQL 5.7 to MySQL 8.0:

The presence of InnoDB data files (.ibd) without corresponding definition files (.frm), or the other way around.
The presence of intermediate tables left over from ALTER TABLE statements that are no longer referenced or used by any active application logic.

Orphan temporary tables

Orphan temporary table names start with the #sql- prefix, such as #sql-123.

Use the following query to help identify orphan temporary (temp) tables:

SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES WHERE NAME RLIKE '#sql-[0-9].*';

You can use the DROP TABLE command to drop orphan temporary tables without any other additional steps. This addresses most cases:

DROP TABLE `DB`.`#mysql50#TEMPORARY_ORPHAN_TABLE`;

Replace DB with the database name you want to use.

An example might look like the following:

DROP TABLE `testdb`.`#mysql50##sql-1234`;

If the previous DROP table command doesn't work, then the definition file (.frm) might be reused by another ALTER TABLE operation. In such cases, a placeholder .frm file needs to be created on disk to remove the table. Contact Cloud SQL support for assistance. If you don't have a support contract, see Self-serve methods for troubleshooting steps.

Orphan intermediate tables

Orphan intermediate table names start with the #sql-ib prefix, for example, #sql-ib23-343224.

Use the following query to identify orphan intermediate tables:

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

To remove intermediate orphan tables, first change the orphan definition filename (.frm) to match the table name, and then drop the table from the command line.

To remove intermediate orphan tables, contact the Cloud SQL support team for assistance. If you don't have a support contract, then see Self-serve methods for troubleshooting steps.

Orphan normal tables

An orphan InnoDB table occurs when its corresponding data file (.ibd) file remains in the file system, but the data dictionary no longer references the data file correctly. This scenario requires manual intervention.

To resolve this issue, contact Cloud SQL support. The support team can create a placeholder .frm file, and then use a DROP TABLE command to try to remove the table. If unsuccessful, the InnoDB (.ibd) file likely requires manual removal from the data directory.

After the file is manually removed, you can back up all tables and database structures.

Drop a database using DROP DATABASE and create a database using CREATE DATABASE. This last step might require downtime for applications connected to the affected database.

If you don't have a support contract, see Self-serve methods for troubleshooting steps.

Self-service troubleshooting

The following self-service troubleshooting methods involve dropping or migrating the entire database to remove the orphan tables when dropping an individual table doesn't work. This method is disruptive. If your organization is under a support contract, then we highly recommend contacting Cloud SQL support team for assistance.

To remove orphan temporary tables, make sure to follow the steps in Orphan temporary tables first. If the DROP TABLE command is unsuccessful, then try the following suggestions.

Before you begin

We strongly recommend you take a full instance backup to help reduce the risk of data loss.
To help reduce the length of possible application downtime, we strongly recommend cloning the instance and verifying the following migration steps before completing them in a production environment.

For more information, see Clone instances.

Drop schema using object migration

Database object migration is a multi-step process to move database objects, such as tables, to a temporary schema:

Back up other database objects, including procedures, functions, and views.
Drop and recreate the impacted schema.
Import the backed-up objects back into the original schema.

This migration method typically causes application downtime. To minimize interruption, prepare all necessary scripts in advance. For example, make sure that your scripts are ready to handle:

Renaming tables and moving them to a temporary schema.
Backing up other database objects, such as procedures, functions, views, and any others.
Restoring all database objects to the original schema.

After these scripts are ready, complete the following steps:

Create a temp schema (example: fix_orphan_tables) on the same instance.
Stop the application traffic on the affected schema.
Move all the tables to temp schema using RENAME TABLE:
```
RENAME TABLE DB.TABLE_NAME TO fix_orphan_tables.TABLE_NAME;
```
Make the following replacements:
- DB: the database name you want to use.
- TABLE_NAME: the table name.
Backup database objects, such as views, routines, stored procedures, triggers, and events. One way of doing so is using mysqldump:
```
mysqldump -u USER --password=PASSWORD \
  -h HOST_IP --set-gtid-purged=OFF --no-data --no-create-db  \
  --no-create-info --routines --triggers --skip-opt --events \
  DB > DB_export.sql
```
Make the following replacements:
- USER: the username.
- PASSWORD: the database password.
- HOST_IP: the IP address of the host.
- DB: the database name you want to use.
You are strongly encouraged to manually backup views using the SHOW CREATE VIEW command snippet.
Drop the schema containing orphaned tables.
Create the schema with the original name.

Verify if the orphan table is removed:

SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES WHERE NAME LIKE '%ORPHAN_TABLE_NAME</var>';

Replace ORPHAN_TABLE_NAME with the orphan table name.

Copy the tables back to original schema:
```
RENAME TABLE fix_orphan_tables.TABLE_NAME TO DB.TABLE_NAME;
```
Make the following replacements:
- TABLE_NAME: the table name.
- DB: the database name you want to use.
Copy all the database objects from the backup taken in step 4.
```
mysql -u USER \
  --password=PASSWORD \
  -h <var>HOST_IP \
  -D<var>DB < <var>DB_export.sql
```
Make the following replacements:
- USER: the username.
- PASSWORD: the database password.
- HOST_IP: the IP address of the host.
- DB: the database name you want to use.
You are strongly encouraged to manually restore views by creating them again using the CREATE VIEW statement.
Resume any application traffic that you stopped previously.

Drop schema using dump and load to the same instance

Another way to remove an orphan table is to perform a full dump of the impacted schema, dropping and recreating the schema, and then restoring the dump. In some scenarios, this method might be faster and less complex. To minimize interruption, make sure to prepare all backup and restore scripts in advance.