Trying to upgrade percona 5.7 to percona 8 with not valid tablespace

MySQL & MariaDB Percona Distribution for MySQL
1

Hello together,

i am trying to migrate a percona 5.7 server to a fresh percona 8 installation.
I stopped the percona 5.7 server and copied the datadir to the new server.

In theory the new server should start up and upgrading the data files to the percona 8.

but after starting up the percona 8 server i am getting the following error in the /var/log/mysql/error.log

2021-02-13T17:16:50.375276Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.22-13) starting as process 13214
2021-02-13T17:16:51.071705Z 1 [System] [MY-011012] [Server] Starting upgrade of data directory.
2021-02-13T17:16:51.071929Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.


2021-02-13T17:16:58.317723Z 1 [ERROR] [MY-012592] [InnoDB] Operating system error number 2 in a file operation.
2021-02-13T17:16:58.320464Z 1 [ERROR] [MY-012593] [InnoDB] The error means the system cannot find the path specified.
2021-02-13T17:16:58.323171Z 1 [ERROR] [MY-012216] [InnoDB] Cannot open datafile for read-only: ‘./deleteme@002edrupal_xxx/xxx_ibe_season.ibd’ OS error: 71
2021-02-13T17:16:58.325916Z 1 [Warning] [MY-012019] [InnoDB] Ignoring tablespace deleteme.drupal_xxx/xxx_ibe_season because it could not be opened.
2021-02-13T17:16:58.641189Z 1 [ERROR] [MY-011006] [Server] Got error 197 from SE while migrating tablespaces.
2021-02-13T17:16:58.654759Z 0 [ERROR] [MY-010020] [Server] Data Dictionary initialization failed.
2021-02-13T17:16:58.663129Z 0 [ERROR] [MY-010119] [Server] Aborting

I had the warnings with the missing tablespaces for some years on the percona 5.7, but it was never a problem. The server had same entries in the error log. but it ever started up without any problems.

error.log of Percona 5.7:

2021-02-13T18:57:19.345489Z 0 [ERROR] InnoDB: Operating system error number 2 in a file operation.
2021-02-13T18:57:19.345492Z 0 [ERROR] InnoDB: The error means the system cannot find the path specified.
2021-02-13T18:57:19.345494Z 0 [ERROR] InnoDB: If you are installing InnoDB, remember that you must create directories yourself, InnoDB does not create them.
2021-02-13T18:57:19.345497Z 0 [ERROR] InnoDB: Cannot open datafile for read-only: ‘./deleteme@002edrupal_xxx/xxx_ibe_season.ibd’ OS error: 71
2021-02-13T18:57:19.345499Z 0 [ERROR] InnoDB: Operating system error number 2 in a file operation.
2021-02-13T18:57:19.345502Z 0 [ERROR] InnoDB: The error means the system cannot find the path specified.
2021-02-13T18:57:19.345504Z 0 [ERROR] InnoDB: If you are installing InnoDB, remember that you must create directories yourself, InnoDB does not create them.
2021-02-13T18:57:19.345507Z 0 [ERROR] InnoDB: Could not find a valid tablespace file for deleteme@002edrupal_xxx/xxx_ibe_season. Please refer to http://dev.mysql.com/doc/refman/5.7/en/innodb-troubleshooting-datadict.html for how to resolve the issue.
2021-02-13T18:57:19.345513Z 0 [Warning] InnoDB: Ignoring tablespace deleteme@002edrupal_xxx/xxx_ibe_season because it could not be opened.

has anyone a hint how I can clear the defunct databases?

1 Like
2

Hi @Phillip85
have you run mysql_upgrade tool?

as described in the documentation - MySQL :: MySQL 8.0 Reference Manual :: 4.4.5 mysql_upgrade — Check and Upgrade MySQL Tables

1 Like
3

Hi @Mykola,

with Mysql 8 the documentation says:

As of MySQL 8.0.16, the MySQL server performs the upgrade tasks previously handled by [ mysql_upgrade ] (for details, see [Section 2.11.3, “What the MySQL Upgrade Process Upgrades”]). Consequently, [ mysql_upgrade ] is unneeded and is deprecated as of that version; expect it to be removed in a future version of MySQL. Because [ mysql_upgrade ] no longer performs upgrade tasks, it exits with status 0 unconditionally.

so i did not run it.

the error log says

2021-02-13T17:16:51.071705Z 1 [System] [MY-011012] [Server] Starting upgrade of data directory.

so i think the process started in the startup process.

1 Like
4

Hi @Phillip85
Since you indicated you have been seeing ongoing data dictionary issues in your Percona Server 5.7 installation you should consider doing a logical dump and importing this into a Percona Server 8.0 installation. This will have the benefit of skipping any corrupted tables in PS 5.7, and ensuring you are importing only pristine versions of your data rows into PS 8.0. You can use a tool such as mydumper since it can generate logical dumps in parallel, and then the corollary tool myloader to import in parallel.

1 Like
5

Hi @Michael_Coburn ,

yeah that was kind of process i wanted to avoid :blush:
the full database is around 2 TB big, with single tables around 200 GB.

a mysql dump will take a lot more time then just copy the datafiles.
but i will take a look at your tool :slight_smile: i hope it will speed it up a lot

2 Likes