MySQL Bugs: #93499: Innodb tablespaces with subpartitions do not contain Table SDI object

Bug #93499	Innodb tablespaces with subpartitions do not contain Table SDI object
Submitted:	6 Dec 2018 5:46	Modified:	23 Mar 13:40
Reporter:	Sergei Glushchenko (OCA)	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

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.

Hello Sergei,

Thank you for the report and test case.

Thanks,
Umesh

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.

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>

Posted by developer:
 
Patch reverted. Changelog entry removed.

Posted by developer:
 
Changed status back to VERIFIED.

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.