Bug #114584 util.checkForServerUpgrade is not detecting data dictionary problems properly
Submitted: 8 Apr 15:59 Modified: 15 May 12:43
Reporter: FRANCISCO BORDENAVE Email Updates:
Status: Duplicate Impact on me:
None 
Category:Shell Upgrade Checker Severity:S3 (Non-critical)
Version:8.0.x OS:Any
Assigned to: CPU Architecture:Any
Tags: shell util upgrade

[8 Apr 15:59] FRANCISCO BORDENAVE
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
[9 Apr 10:52] MySQL Verification Team
Hi,

Thanks for the report. I did not manage to reproduce myself but I'll verify for the shell team to check.
[10 Apr 8:46] MySQL Verification Team
Duplicate of Bug #110722
[15 May 12:43] FRANCISCO BORDENAVE
The duplicate bug belongs to MySQL Server, still the mysqlshell is not able to find some orphan entries in data dictionary like those innodb_sys_datafiles and innodb_sys_tablespaces so the shell is erroneously reporting no errors when there are remaining problems preventing the data dictionary upgrade.