Troubleshooting MySQL 5.6→5.7→8.0 Upgrade Failure Caused by Orphan Tablespaces and Data Dictionary Inconsistency

The Zabbix monitoring system was running on MySQL 5.6 with about 1.5 TB of data and partitioned tables; adding a column to a huge history table was extremely slow, so the team decided to upgrade to MySQL 8.0 to use the instant‑add‑column feature.

Because a direct data‑logical export/import would be too slow, an in‑place upgrade path was chosen: first upgrade from 5.6 to 5.7, then from 5.7 to 8.0. The 5.6→5.7 step succeeded, but the 5.7→8.0 upgrade failed with the following errors:

2021-07-20T07:33:18.138368Z 1 [ERROR] [MY-011006] [Server] Got error 197 from SE while migrating tablespaces.
2021-07-20T07:33:18.145105Z 0 [ERROR] [MY-010020] [Server] Data Dictionary initialization failed.
2021-07-20T07:33:18.145502Z 0 [ERROR] [MY-010119] [Server] Aborting
2021-07-20T07:33:40.435143Z 0 [System] [MY-010910] [Server] /usr/local/mysql/bin/mysqld: Shutdown complete (mysqld 8.0.25) MySQL Community Server - GPL.

The logs indicate a tablespace migration failure and data‑dictionary initialization error, prompting an investigation into why the 5.7 upgrade succeeded while 8.0 did not.

MySQL 8.0 stores all metadata in the InnoDB dictionary table (single tablespace mysql.ibd) with atomic DDL, whereas earlier versions kept metadata scattered across system tables and InnoDB internal tables. During the upgrade, the missing file ./zabbix/#sql-ib104-715696445.ibd caused the failure because the data dictionary attempted to reference a non‑existent tablespace.

Further queries showed inconsistency: the INFORMATION_SCHEMA.INNODB_SYS_TABLES view had no entry for the orphan table, but INNODB_SYS_DATAFILES and INNODB_SYS_TABLESPACES still listed the tablespace, indicating a mismatch between the dictionary and the actual files.

Attempting to drop the orphan table with DROP TABLE `#mysql50##sql-ib104-715696445` failed because the dictionary entry was absent. The article therefore proposes two alternative remediation methods.

1. Logical export/import upgrade to MySQL 8.0 – using tools such as mysqldump or mydumper (the latter supports parallel table dumping) to extract schema and data, then import into a fresh 8.0 instance.

2. Tablespace transfer with in‑place upgrade – create a new MySQL 5.7 instance on a different port, load the Zabbix metadata, discard the original tablespaces, move the .ibd files to the new instance, adjust row formats if necessary, and finally run an in‑place upgrade to 8.0. Key commands include:

mysqldump -uroot -p -B mysql -E -R --triggers --hex-blob --set-gtid-purged=off --single-transaction --master-data=2 >zabbix-metadata-01.sql
mysqldump -uroot -p -B zabbix -E -R --triggers --hex-blob --no-data --set-gtid-purged=off --single-transaction --master-data=2 >zabbix-metadata-02.sql

mysql -h127.0.0.1 -P3307 -uroot -p < zabbix-metadata-01.sql
mysql -h127.0.0.1 -P3307 -uroot -p < zabbix-metadata-02.sql

mysql -uroot -p -NBe "select concat('alter table ',TABLE_NAME,' discard tablespace;') from information_schema.TABLES where TABLE_SCHEMA='zabbix'";
alter table xxxxxx discard tablespace;

mv /data/3306/zabbix/*.ibd /data/3307/zabbix/

alter table xxxxxx import tablespace;

alter table acknowledges row_format=compact;
alter table acknowledges import tablespace;

The article concludes that by either re‑importing data logically or carefully transferring tablespaces while aligning row formats, the upgrade can be completed successfully despite the original data‑dictionary inconsistency.