Description:
MySQL shell util.checkForServerUpgrade fails to detect schema with orphan files and no errors are prompted, however upgrade process fails to initialize data dictionary:
...
19) Schema inconsistencies resulting from file removal or corruption
No issues found
....
Errors: 0
Warnings: 461
Notices: 0
NOTE: No fatal errors were found that would prevent an upgrade, but some potential issues were detected. Please ensure that the reported issues are not significant before upgrading.
In my case warnings are related to utf8 usage.
After this attempting an upgrade causes this problem:
2024-04-08T14:00:03.174505Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2024-04-08T14:00:03.177285Z 1 [Note] [MY-011088] [Server] Data dictionary initializing version '80023'.
2024-04-08T14:00:03.497915Z 1 [Note] [MY-010337] [Server] Created Data Dictionary for upgrade
2024-04-08T14:00:03.509363Z 1 [ERROR] [MY-011006] [Server] Got error 197 from SE while migrating tablespaces.
2024-04-08T14:00:03.514339Z 0 [ERROR] [MY-010020] [Server] Data Dictionary initialization failed.
2024-04-08T14:00:03.514365Z 0 [ERROR] [MY-010119] [Server] Aborting
I only found entries in these 2 tables not scanned by the util tool:
mysql> select * from information_schema.innodb_sys_datafiles where path like '%#sql%' limit 5;
+---------+-----------------------------+
| SPACE | PATH |
+---------+-----------------------------+
| 7858934 | /tmp/#sql158ae_55708_6a.ibd |
| 7858935 | /tmp/#sql158ae_55708_6b.ibd |
| 7860942 | /tmp/#sql158ae_55ac9_c2.ibd |
| 7860943 | /tmp/#sql158ae_55ac9_c3.ibd |
| 7860944 | /tmp/#sql158ae_55ac9_c4.ibd |
+---------+-----------------------------+
5 rows in set (0.00 sec)
mysql> select * from information_schema.innodb_sys_tablespaces where name like '%#sql%' limit 5;
+---------+------------------------+------+-------------+----------------------+-----------+---------------+------------+---------------+-----------+----------------+
| SPACE | NAME | FLAG | FILE_FORMAT | ROW_FORMAT | PAGE_SIZE | ZIP_PAGE_SIZE | SPACE_TYPE | FS_BLOCK_SIZE | FILE_SIZE | ALLOCATED_SIZE |
+---------+------------------------+------+-------------+----------------------+-----------+---------------+------------+---------------+-----------+----------------+
| 7858934 | tmp/#sql158ae_55708_6a | 0 | Antelope | Compact or Redundant | 16384 | 0 | Single | 0 | 0 | 0 |
| 7858935 | tmp/#sql158ae_55708_6b | 0 | Antelope | Compact or Redundant | 16384 | 0 | Single | 0 | 0 | 0 |
| 7860942 | tmp/#sql158ae_55ac9_c2 | 0 | Antelope | Compact or Redundant | 16384 | 0 | Single | 0 | 0 | 0 |
| 7860943 | tmp/#sql158ae_55ac9_c3 | 0 | Antelope | Compact or Redundant | 16384 | 0 | Single | 0 | 0 | 0 |
| 7860944 | tmp/#sql158ae_55ac9_c4 | 0 | Antelope | Compact or Redundant | 16384 | 0 | Single | 0 | 0 | 0 |
+---------+------------------------+------+-------------+----------------------+-----------+---------------+------------+---------------+-----------+----------------+
5 rows in set (0.01 sec)
How to repeat:
Not easy to repeat as these files are leftovers from very old operations not detected from any upgrade or backup process, neither detected by util.checkForServerUpgrade tool
Suggested fix:
Include these 2 information_schema tables into the util.checkForServerUpgrad check.
Additionally I think that upgrade process should not fail blindly with these types of orphan files if they don't have a any correspondent reference in the rest of information_schema database. I can't think in a scenario where these files can be used so this should be cleaned by the upgrade process.
As an alternative I'm thinking in a flag for upgrade=clean_orphan where we can force the upgrade process to clean orphan entries in the data dictionary
