Bug #93499 Innodb tablespaces with subpartitions do not contain Table SDI object
Submitted: 6 Dec 2018 5:46 Modified: 23 Mar 2019 13:40
Reporter: Sergei Glushchenko Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Data Dictionary Severity:S3 (Non-critical)
Version:8.0.13 OS:Any
Assigned to: CPU Architecture:Any

[6 Dec 2018 5:46] Sergei Glushchenko
Description:
According to https://dev.mysql.com/doc/refman/8.0/en/serialized-dictionary-information.html

For partitioned InnoDB tables comprised of multiple tablespaces, SDI data is stored in the tablespace file of the first partition.

This is true for table with one level of partitioning. But not for tables with subpartitions.

How to repeat:
Create two tables, t1 and t2:

CREATE TABLE t1 (a INT NOT NULL, PRIMARY KEY(a))
 PARTITION BY RANGE (a) PARTITIONS 3 (
     PARTITION p1 VALUES LESS THAN (200),
     PARTITION p2 VALUES LESS THAN (600),
     PARTITION p3 VALUES LESS THAN (1800));

CREATE TABLE t2 (a INT NOT NULL, b INT)
    PARTITION BY RANGE (a) PARTITIONS 3 SUBPARTITION BY KEY (b) (
        PARTITION p1 VALUES LESS THAN (200) (
            SUBPARTITION p11,
            SUBPARTITION p12,
            SUBPARTITION p13),
        PARTITION p2 VALUES LESS THAN (600) (
            SUBPARTITION p21,
            SUBPARTITION p22,
            SUBPARTITION p23),
        PARTITION p3 VALUES LESS THAN (1800) (
            SUBPARTITION p31,
            SUBPARTITION p32,
            SUBPARTITION p33));

Go to the data dictionary and run

ibd2sdi t1#P#p1.ibd

it shows something like:

["ibd2sdi"
,
{
        "type": 1,
        "id": 335,
        "object":
                {
    "mysqld_version_id": 80013,
    "dd_version": 80013,
    "sdi_version": 1,
    "dd_object_type": "Table",
    "dd_object": {
        "name": "t1",
        "mysql_version_id": 80013,
        "created": 20181206053709,
        "last_altered": 20181206053709,
        "hidden": 1,
        "options": "avg_row_length=0;key_block_size=0;keys_disabled=0;pack_record=0;stats_auto_recalc=0;stats_sample_pages=0;",
        "columns": [
            {
                "name": "a",
                "type": 4,
                "is_nullable": false,
                "is_zerofill": false,
                "is_unsigned": false,
                "is_auto_increment": false,
 ...

,
{
        "type": 2,
        "id": 7,
        "object":
                {
    "mysqld_version_id": 80013,
    "dd_version": 80013,
    "sdi_version": 1,
    "dd_object_type": "Tablespace",
    "dd_object": {
        "name": "test/t1#P#p1",
        "comment": "",
        "options": "",
        "se_private_data": "flags=16417;id=2;server_version=80013;space_version=1;",
        "engine": "InnoDB",
        "files": [
            {
                "ordinal_position": 1,
                "filename": "./test/t1#P#p1.ibd",
                "se_private_data": "id=2;"
            }
        ]
    }
}
}
]

As you can see both Tablespace and Table objects are there.

Now run

ibd2sdi t2#P#p1#SP#p11.ibd
["ibd2sdi"
,
{
        "type": 2,
        "id": 10,
        "object":
                {
    "mysqld_version_id": 80013,
    "dd_version": 80013,
    "sdi_version": 1,
    "dd_object_type": "Tablespace",
    "dd_object": {
        "name": "test/t2#P#p1#SP#p11",
        "comment": "",
        "options": "",
        "se_private_data": "flags=16417;id=5;server_version=80013;space_version=1;",
        "engine": "InnoDB",
        "files": [
            {
                "ordinal_position": 1,
                "filename": "./test/t2#P#p1#SP#p11.ibd",
                "se_private_data": "id=5;"
            }
        ]
    }
}
}
]

as you can see only Tablespace object it there, but Table object is missing.
[6 Dec 2018 6:03] MySQL Verification Team
Hello Sergei,

Thank you for the report and test case.

Thanks,
Umesh
[1 Mar 2019 14:40] Daniel Price
Posted by developer:
 
Fixed as of the upcoming 8.0.17 release, and here's the changelog entry:

Serialized Dictionary Information (SDI) was not stored in any tablespace
file for tables with subpartitions. SDI data should be stored in the
tablespace file of the first partition.
[5 Mar 2019 11:55] Daniel Price
Posted by developer:
 
commit 53252d9f1556425e89c8275220cf48215a26f804
Author: Mauritz Sundell <mauritz.sundell@oracle.com>
Date:   Fri Mar 1 16:21:51 2019 +0100

    Revert "Bug#29020745: INNODB TABLESPACES WITH SUBPARTITIONS DO NOT CONTAIN TABLE SDI"
    
    This reverts commit 1b2d32392cfaf69cec2835e8693fd748a61fd19d.
    
    Approved-by: Kari Wedde <kari.wedde@oracle.com>
[5 Mar 2019 11:55] Daniel Price
Posted by developer:
 
Patch reverted. Changelog entry removed.
[5 Mar 2019 12:29] Daniel Price
Posted by developer:
 
Changed status back to VERIFIED.
[23 Mar 2019 13:40] Daniel Price
Posted by developer:
 
Fixed as of the upcoming 8.0.17 release, and here's the changelog entry:

When using subpartitioning, table Serialized Dictionary Information (SDI)
was not stored in any of the tablespace files.