Bug #110194 DD upgrade leaves dictonary tables in temporary schema dd_upgrade_targets_80030

• Submitted: 23 Feb 2023 14:06
• Modified: 3 Apr 2023 16:54
• Reporter: Satya Bodapati (OCA)
• Status: Verified
• Category: MySQL Server: Data Dictionary
• Severity: S3 (Non-critical)
• Version: 8.0.23, 8.0.30, 8.0.32
• OS: Any
• CPU Architecture: Any

[23 Feb 2023 14:06] Satya Bodapati

Description:
After upgrading DD tables, server leaves them in the temporary schema 'dd_upgrade_targets_80XX' XX is the server version that is used for upgrade. 

After upgrade, for example catalogs table is in dd_upgrade_targets_80XX

        "type": 1,
        "id": 363,
        "object":
                {
    "mysqld_version_id": 80030,
    "dd_version": 80023,
    "sdi_version": 80019,
    "dd_object_type": "Table",
    "dd_object": {
        "name": "catalogs",
        "mysql_version_id": 80030,
        "created": 20230220170946,
        "last_altered": 20230220170946,
        "hidden": 1,
        "options": "avg_row_length=0;encrypt_type=N;explicit_encryption=0;explicit_tablespace=1;key_block_size=0;keys_disabled=0;pack_record=1;row_type=2;stats_auto_recalc=0;stats_persistent=0;stats_sample_pages=0;",
        "columns": [
            {
                "name": "id",
                "type": 9,
                "is_nullable": false,
                "is_zerofill": false,
                "is_unsigned": true,
                "is_auto_increment": true,
                "is_virtual": false,
                "hidden": 1,
                "ordinal_position": 1,
                "char_length": 20,
                "numeric_precision": 20,
                "numeric_scale": 0,
                "numeric_scale_null": false,
                "datetime_precision": 0,
                "datetime_precision_null": 1,
                "has_no_default": false,
                   ....
             ..
           ]
    "schema_ref": "dd_upgrade_targets_80030",        ----->
        "se_private_id": 1075, 
        "engine": "InnoDB",
        "last_checked_for_upgrade_version_id": 0,
        "comment": "",
        "se_private_data": "autoinc=0;version=0;",
        "engine_attribute": "",
        "secondary_engine_attribute": "",
        "row_format": 2,
        "partition_type": 0,
        "partition_expression": "",
        "partition_expression_utf8": "",
        "default_partitioning": 0,
        "subpartition_type": 0,
        "subpartition_expression": "",
        "subpartition_expression_utf8": "",

How to repeat:
Create a datadir using mysql 8.0.22 (or lower is fine too)
upgrade to 8.0.30
ibd2sdi  ./mysql.ibd |grep "schema_ref"

"schema_ref"
        "schema_ref": "mysql",
        "schema_ref": "mysql",
        "schema_ref": "mysql",
        "schema_ref": "mysql",
        "schema_ref": "mysql",
        "schema_ref": "mysql",
        "schema_ref": "mysql",
        "schema_ref": "mysql",
        "schema_ref": "mysql",
        "schema_ref": "mysql",
        "schema_ref": "mysql",
        "schema_ref": "mysql",
        "schema_ref": "mysql",
        "schema_ref": "mysql",
        "schema_ref": "dd_upgrade_targets_80030",
        "schema_ref": "dd_upgrade_targets_80030",
        "schema_ref": "dd_upgrade_targets_80030",
        "schema_ref": "dd_upgrade_targets_80030",
        "schema_ref": "dd_upgrade_targets_80030",
        "schema_ref": "dd_upgrade_targets_80030",
        "schema_ref": "dd_upgrade_targets_80030",
        "schema_ref": "dd_upgrade_targets_80030",
        "schema_ref": "dd_upgrade_targets_80030",
        "schema_ref": "dd_upgrade_targets_80030",
        "schema_ref": "dd_upgrade_targets_80030",
        "schema_ref": "dd_upgrade_targets_80030",
        "schema_ref": "dd_upgrade_targets_80030",
        "schema_ref": "dd_upgrade_targets_80030",
        "schema_ref": "dd_upgrade_targets_80030",
        "schema_ref": "dd_upgrade_targets_80030",
        "schema_ref": "dd_upgrade_targets_80030",
        "schema_ref": "dd_upgrade_targets_80030",
        "schema_ref": "dd_upgrade_targets_80030",
        "schema_ref": "dd_upgrade_targets_80030",
        "schema_ref": "dd_upgrade_targets_80030",
        "schema_ref": "dd_upgrade_targets_80030",
        "schema_ref": "mysql",
        "schema_ref": "mysql",
        "schema_ref": "mysql",

Suggested fix:
Update the schema_ref at end of upgrade

[27 Feb 2023 13:25] MySQL Verification Team

# This issue seems to be fixed in latest GA as I'm not longer seeing when upgraded from 8.0.23 -> 8.0.32. Will confirm for reported version before informing this to Satya

[27 Feb 2023 18:30] Satya Bodapati

Need help reproducing the bug?

[28 Feb 2023 7:15] MySQL Verification Team

Hello Satya,

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

regards,
Umesh

[28 Feb 2023 7:16] MySQL Verification Team

Interestingly, issue seen only when upgraded from 8.0.22(or lower) to higher(8.0.32 confirmed), where as if you upgrade from 8.0.23 then issue is not seen at least during my tests.

[28 Feb 2023 7:18] MySQL Verification Team

Test results

Attachment: 110194_8.0.23_30_32.results (application/octet-stream, text), 8.15 KiB.

[3 Apr 2023 16:54] Satya Bodapati

yes, we need version < 8.0.23 because the DD_VERSION didn't change from 8.0.23.

So a server version < 8.0.23 should be upgraded to the latest.