Bug #105958 MySQL shell preupgrade check failed due to MySQL schema nul, prn, and aux
Submitted: 22 Dec 2021 8:06 Modified: 6 Jul 11:42
Reporter: TIng-Lin Chu Email Updates:
Status: Closed Impact on me:
None 
Category:Shell Upgrade Checker Severity:S3 (Non-critical)
Version:8.0.27 OS:Any
Assigned to: CPU Architecture:Any

[22 Dec 2021 8:06] TIng-Lin Chu
Description:

1) MySQL 5.7 preupgrade check to catch orphan table issues
For the following tables, either the datadir directory or frm file was removed or corrupted.
More Information:
https://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting-datadict.html

[table_schema, table_name]
aux@@@,a - Check the server logs or examine datadir to determine the issue and correct it before upgrading.

2) The DB instance must have enough space to rebuild the largest table that uses an old temporal data format.
No issues found.

Errors: 1
Warnings: 0
Database Objects Affected: 1

The root cause is on MySQL documenthttps://dev.mysql.com/doc/refman/5.7/en/identifier-mapping.html:
On Windows, some names such as nul, prn, and aux are encoded by appending @@@ to the name when the server creates the corresponding file or directory. This occurs on all platforms for portability of the corresponding database object between platforms.

How to repeat:
Create any table in schema aux,

Run  mysqlsh -- util checkForServerUpgrade root@mysqhost:3306 --target-version=8.0.26 --output-format=JSON

        {
            "id": "schemaInconsistencyCheck",
            "title": "Schema inconsistencies resulting from file removal or corruption",
            "status": "OK",
            "description": "Error: Following tables show signs that either table datadir directory or frm file was removed/corrupted. Please check server logs, examine datadir to detect the issue and fix it before upgrade",
            "detectedProblems": [
                {
                    "level": "Error",
                    "dbObject": "aux@@@.a",
                    "description": "present in INFORMATION_SCHEMA's INNODB_SYS_TABLES table but missing from TABLES table"
                }
            ]
        },

Suggested fix:
Please fix check nul, prn, and aux schema policy.
[27 Dec 2021 8:29] MySQL Verification Team
Hello TIng-Lin Chu,

Thank you for the report and feedback.
Verified as described.

regards,
Umesh