Description:
Tested on Debian 10 with MySQL packages from the official MySQL-Repos.
If a specific table schema is used, upgrading the table to MySQL 8 fails. The table is broken / corrupted after the upgrade has finished, cannot be successfully repaired and due to the nature of the upgrade to version 8, downgrading is not possible anymore (the issue happens after the data directory has been successfully upgraded).
In my small test case, the issue did not cause the MySQL upgrade to fail completely. On a larger scale with several hundreds of databases on a server however, the upgrade failed due to this issue, still the data directory was already upgraded, so no downgrade possible. I cannot say why the upgrade is failing in some cases and not in others.
The error on a larger database server, where the upgrade completely fails due to this:
"[...]
2021-10-22T13:49:00.223101Z 5 [Note] [MY-013394] [Server] Checking 'somedb01' schema.
2021-10-22T13:49:00.223532Z 5 [Note] [MY-013394] [Server] Checking 'somedb02' schema.
2021-10-22T13:49:00.279648Z 5 [Note] [MY-013394] [Server] Checking 'somedb03' schema.
2021-10-22T13:49:00.346179Z 5 [Note] [MY-013394] [Server] Checking 'affecteddb' schema.
2021-10-22T13:49:00.526075Z 5 [Warning] [MY-013382] [Server] Table 'affecteddb.brokentable1' requires repair.
2021-10-22T13:49:00.526192Z 5 [Warning] [MY-013382] [Server] Table 'affecteddb.brokentable2' requires repair.
2021-10-22T13:49:00.526241Z 5 [Note] [MY-013394] [Server] Checking 'somedb04' schema.
2021-10-22T13:49:00.580142Z 5 [Note] [MY-013394] [Server] Checking 'somedb05' schema.
2021-10-22T13:49:00.629512Z 5 [Note] [MY-013394] [Server] Checking 'somedb06' schema.
[...] (No other errors logged) [...]
2021-10-22T13:49:23.890729Z 5 [Note] [MY-013394] [Server] Checking 'sys' schema.
2021-10-22T13:49:23.939738Z 0 [ERROR] [MY-013380] [Server] Failed to upgrade server.
2021-10-22T13:49:23.943780Z 0 [ERROR] [MY-010119] [Server] Aborting
2021-10-22T13:49:23.973041Z 0 [Note] [MY-012330] [InnoDB] FTS optimize thread exiting.
2021-10-22T13:49:24.593534Z 0 [Note] [MY-010120] [Server] Binlog end"
On a smaller server with my test case, the upgrade finishes, but the affected table is not accessible afterwards:
"[...]
2021-10-22T16:32:03.334727Z 2 [System] [MY-011003] [Server] Finished populating Data Dictionary tables with data.
2021-10-22T16:32:03.337033Z 2 [Note] [MY-011008] [Server] Finished migrating TABLE statistics data.
2021-10-22T16:32:03.338443Z 2 [Note] [MY-011008] [Server] Finished migrating TABLE statistics data.
2021-10-22T16:32:03.473588Z 2 [Note] [MY-010006] [Server] Using data dictionary with version '80023'.
2021-10-22T16:32:03.743236Z 5 [System] [MY-013381] [Server] Server upgrade from '50700' to '80027' started.
2021-10-22T16:32:03.744893Z 5 [Note] [MY-013386] [Server] Running queries to upgrade MySQL server.
2021-10-22T16:32:05.620225Z 5 [Note] [MY-013387] [Server] Upgrading system table data.
2021-10-22T16:32:05.885157Z 5 [Note] [MY-013385] [Server] Upgrading the sys schema.
2021-10-22T16:32:06.175655Z 5 [Note] [MY-013400] [Server] Upgrade of help tables started.
2021-10-22T16:32:06.273083Z 5 [Note] [MY-013400] [Server] Upgrade of help tables completed.
2021-10-22T16:32:06.273223Z 5 [Note] [MY-013394] [Server] Checking 'mysql' schema.
2021-10-22T16:32:06.342338Z 5 [Note] [MY-013394] [Server] Checking 'sys' schema.
2021-10-22T16:32:06.356299Z 5 [Note] [MY-013394] [Server] Checking 'testdb' schema.
2021-10-22T16:32:06.357376Z 5 [Warning] [MY-013382] [Server] Table 'testdb.testTable1' requires repair.
2021-10-22T16:32:06.357601Z 5 [ERROR] [MY-013383] [Server] Table 'testdb.testTable1' repair failed.
2021-10-22T16:32:06.360602Z 5 [System] [MY-013381] [Server] Server upgrade from '50700' to '80027' completed.
[...] (Server then normally starts, table testdb.testTable1 is broken) [...]"
From my test case:
Before the upgrade:
Server version: 5.7.36 MySQL Community Server (GPL)
[...]
mysql> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| testdb |
+--------------------+
5 rows in set (0.01 sec)
mysql> SELECT * FROM testdb.testTable1;
Empty set (0.00 sec)
mysql> USE testdb;
[...]
Database changed
mysql> CHECK TABLE testTable1;
+-------------------+-------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+-------------------+-------+----------+----------+
| testdb.testTable1 | check | status | OK |
+-------------------+-------+----------+----------+
1 row in set (0.00 sec)
After the upgrade:
Server version: 8.0.27 MySQL Community Server - GPL
[...]
mysql> USE testdb;
[...]
Database changed
mysql> SELECT * from testTable1;
ERROR 1034 (HY000): Incorrect key file for table 'testTable1'; try to repair it
mysql> REPAIR TABLE testTable1;
+-------------------+--------+----------+-------------------------------------------------------------+
| Table | Op | Msg_type | Msg_text |
+-------------------+--------+----------+-------------------------------------------------------------+
| testdb.testTable1 | repair | Error | Incorrect key file for table 'testTable1'; try to repair it |
| testdb.testTable1 | repair | error | Corrupt |
+-------------------+--------+----------+-------------------------------------------------------------+
2 rows in set (0.00 sec)
mysql> REPAIR TABLE testTable1 USE_FRM;
+-------------------+--------+----------+------------------+
| Table | Op | Msg_type | Msg_text |
+-------------------+--------+----------+------------------+
| testdb.testTable1 | repair | Warning | Can't open table |
| testdb.testTable1 | repair | error | Corrupt |
+-------------------+--------+----------+------------------+
2 rows in set (0.00 sec)
How to repeat:
I have provided a sql dump for a test case as an attachment. I have tried this on Debian 10 going from MySQL 5.7.36 -> 8.0.27, but I assume it will also happen on other (Linux) distributions.
Import it into MySQL 5. The table is working correctly, as shown above. Then upgrade to MySQL 8. The table is now corrupted and it is not possible to repair / downgrade anymore.
