Fix "[ERROR] [MY-011006] [Server] Got error 197 from SE while migrating tablespaces." on MySQL upgrade

Maybe you get the error [ERROR] [MY-011006] [Server] Got error 197 from SE while migrating tablespaces. if you upgrade your MySQL-Server from 5.7 to 8.0. Here is how you fix this error.

Short background: You get this error if you manually deleted a database within the MySQL data folder via cli. This means the database still exists in the information_schema but not as folder on the filesystem.

Solution 1: You should see other error messages before the error 197 in the logs. In my case the following:
2024-08-06T19:06:25.465334Z 1 [ERROR] [MY-012216] [InnoDB] Cannot open datafile for read-only: './t_test/target.ibd' OS error: 71. We can see, that the database can not open the table file "target" on the database "t_test". So now the solution is very simple:

• We go into the mysql data folder cd /var/lib/mysql
• We create the missing folder t_test (thats the missing database folder on the filesystem, replace with your missing database folder) mkdir t_test
• Give it the right permission chown -R mysql:mysql t_test
• Now open a connection to the mysql database and now drop the database via sql drop database t_test;

Now the information_schema is cleaned up and you should be able to upgrade your server.

Solution 2: See https://bugs.mysql.com/bug.php?id=97295. Dump the database. Delete the whole database and setup the new MySQL database with the new version and import the dump.