I have a very weird state in MySQL 5.7.27. My mysql system table **mysql/slave_worker_info** shows 2 entries in **information_schema.INNODB_SYS_TABLESPACES**
mysql> SELECT * FROM information_schema.INNODB_SYS_TABLESPACES WHERE name like '%slave_worker_info%'\G
*************************** 1. row ***************************
SPACE: 5
NAME: mysql/slave_worker_info
FLAG: 0
FILE_FORMAT: Antelope
ROW_FORMAT: Compact or Redundant
PAGE_SIZE: 16384
ZIP_PAGE_SIZE: 0
SPACE_TYPE: Single
FS_BLOCK_SIZE: 4096
FILE_SIZE: 98304
ALLOCATED_SIZE: 98304
*************************** 2. row ***************************
SPACE: 1674
NAME: mysql/slave_worker_info
FLAG: 33
FILE_FORMAT: Barracuda
ROW_FORMAT: Dynamic
PAGE_SIZE: 16384
ZIP_PAGE_SIZE: 0
SPACE_TYPE: Single
FS_BLOCK_SIZE: 4096
FILE_SIZE: 98304
ALLOCATED_SIZE: 98304
2 rows in set (0.01 sec)
Even if I try to DROP or DISCARD TABLESPACE for **mysql/slave_worker_info** there is still 1 Orphan entry in **information_schema.INNODB_SYS_TABLESPACES**
mysql> drop table slave_worker_info;
Query OK, 0 rows affected (0.01 sec)
mysql> SELECT * FROM information_schema.INNODB_SYS_TABLESPACES WHERE name like '%slave_worker_info%';
*************************** 1. row ***************************
SPACE: 5
NAME: mysql/slave_worker_info
FLAG: 0
FILE_FORMAT: Antelope
ROW_FORMAT: Compact or Redundant
PAGE_SIZE: 16384
ZIP_PAGE_SIZE: 0
SPACE_TYPE: Single
FS_BLOCK_SIZE: 4096
FILE_SIZE: 0
ALLOCATED_SIZE: 0
Because of this I’m unable to upgrade to MySQL 8.0 and after upgrade it just crashes with below error.
mysqld: Duplicate entry 'mysql/slave_worker_info' for key 'tablespaces.name'
[ERROR] [MY-011006] [Server] Got error 168 from SE while migrating tablespaces.
[ERROR] [MY-010020] [Server] Data Dictionary initialization failed.
[ERROR] [MY-010119] [Server] Aborting
[System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.33-25) Percona Server (GPL), Release 25, Revision 60c9e2c5.
[Warning] [MY-011068] [Server] The syntax 'skip_slave_start' is deprecated and will be removed in a future release. Please use skip_replica_start instead.
[ERROR] [MY-013236] [Server] The designated data directory /var/lib/mysql/ is unusable. You can remove all files that the server added to it.
[ERROR] [MY-010119] [Server] Aborting
[ERROR] [MY-010119] [Server] Aborting
Note: Even with this extra tablespace entry 5.7 works without any issues, but with its EOL we have to move to 8.0.
Hello . I’m not sure how you ended into this situation but is your dataset too big ?
An alternative that you could consider is doing a logical backup / restore . In some cases i.e. not huge dataset, downtime acceptable or possible migration to another server without taking down the production one, this may be a better approach.
Hello and thank you. While i understand that this may be time consuming some times it may the easier way to move overcome the issue.
A dataset of 1.4T is not small but not huge as well. Dump/restore speed depends on a few factors such as schema, hardware specs etc.
It may worths providing the output of the following command to get a basic idea of data and indexes distribution
SELECT CONCAT(ROUND(SUM(data_length) / (1024*1024*1024),2),'G') Data_Size,
CONCAT(ROUND(SUM(index_length)/ (1024*1024*1024),2),'G') Index_Size,
CONCAT(ROUND((sum(data_length)+sum(index_length))/(1024*1024*1024), 2),'G') Total_Size
FROM information_schema.TABLES
WHERE TABLE_SCHEMA NOT IN ('information_schema', 'performance_schema', 'sys')
GROUP BY NULL
Is this a stand-alone instance? If not, what about trying using another instance i.e. transfer a physical backup from another instance/replica and upgrade using that backup ?
Another approach you could try depending on the amount of schemas/table that you have is the following:
create an empty instance running MySQL 8
create all schemas (no data)
create all tables (no data)
import tables by discarding tablespace , one by one
This approach will require some scripting but can help you skip any remnants
Before taking further actions i would further suggest the following:
MySQL 5.7.27 is too old release almost 5years ago. As suggested here https://dev.mysql.com/doc/refman/8.0/en/upgrade-paths.html I’d suggest first upgrading to latest 5.7 and then retry the upgrade to 8. There have been many bug fixes since 5.7.27 was released.
If you’re currently using Percona Server for MySQL 5.7, Percona recommends upgrading to this version of 5.7 prior to upgrading to Percona Server for MySQL 8.0.
It might be worth trying to upgrade at least to 5.7.28 (or if possible to latest 5.7.44) before upgrading to MySQL 8.0
We did try to upgrade to the latest version which was successful but the Orphan issue still persisted and still creating issue during MySQL 8.0 upgrade, we are now working towards doing a dump restore as a last resort.