We recently saw that .frm files have been somewhat of replaced for MyISAM tables in MySQL 8.0 (see this post).
However, what are those files ? Can they be used for something else than MyISAM tables ? … Let’s try to answer those questions and get more familiar with the .sdi files.
What does SDI stand for ?
SDI acronym stands for Serialized Dictionary Information.
As you may know already, MySQL 8.0 replaced the old way to store the metadata of tables, their structure, into the new transactional Data Dictionary (in InnoDB).
Additionally, that information is also part any InnoDB tabespaces , its is appended to the tablespace, so the meta data and data are bundled together.
For storage engine not supporting this feature, an external file, the actual SDI is created. This is the case for example for MyISAM tables.
What do they look like ?
So we know that for storage engine not having the capability to store the metadata of the tables, a SDI file is created. What does it look like ?
In fact the SDI file is a compact JSON file. It’s name is formed by the table’s name and the table’s id.
If we take the example table from the previous post, on the file system, the SDI file looks like this:
[root@imac my_isam_db]# ls -l *.sdi -rw-r----- 1 mysql mysql 3600 Aug 2 12:49 t1_5017.sdi
5017 is the table’s hidden id.
In fact, it’s a string representation of the OID of the dictionary object. The OID ensures uniqueness, which is required since several tables may map to the same name.
Now let’s have a look at what that JSON looks like:
{
"mysqld_version_id": 80026,
"dd_version": 80023,
"sdi_version": 80019,
"dd_object_type": "Table",
"dd_object": {
"name": "t1",
"mysql_version_id": 80026,
"created": 20210802104936,
"last_altered": 20210802104936,
"hidden": 1,
"options": "avg_row_length=0;key_block_size=0;keys_disabled=0;pack_record=1;stats_auto_recalc=0;stats_sample_pages=0;",
"columns": [
{
"name": "id",
"type": 4,
"is_nullable": false,
"is_zerofill": false,
"is_unsigned": true,
"is_auto_increment": true,
"is_virtual": false,
"hidden": 1,
"ordinal_position": 1,
"char_length": 10,
"numeric_precision": 10,
"numeric_scale": 0,
"numeric_scale_null": false,
"datetime_precision": 0,
"datetime_precision_null": 1,
"has_no_default": false,
"default_value_null": false,
"srs_id_null": true,
"srs_id": 0,
"default_value": "AAAAAA==",
"default_value_utf8_null": true,
"default_value_utf8": "",
"default_option": "",
"update_option": "",
"comment": "",
"generation_expression": "",
"generation_expression_utf8": "",
"options": "interval_count=0;",
"se_private_data": "",
"engine_attribute": "",
"secondary_engine_attribute": "",
"column_key": 2,
"column_type_utf8": "int unsigned",
"elements": [],
"collation_id": 8,
"is_explicit_collation": false
},
{
"name": "name",
"type": 16,
"is_nullable": true,
"is_zerofill": false,
"is_unsigned": false,
"is_auto_increment": false,
"is_virtual": false,
"hidden": 1,
"ordinal_position": 2,
"char_length": 20,
"numeric_precision": 0,
"numeric_scale": 0,
"numeric_scale_null": true,
"datetime_precision": 0,
"datetime_precision_null": 1,
"has_no_default": false,
"default_value_null": true,
"srs_id_null": true,
"srs_id": 0,
"default_value": "",
"default_value_utf8_null": true,
"default_value_utf8": "",
"default_option": "",
"update_option": "",
"comment": "",
"generation_expression": "",
"generation_expression_utf8": "",
"options": "interval_count=0;",
"se_private_data": "",
"engine_attribute": "",
"secondary_engine_attribute": "",
"column_key": 1,
"column_type_utf8": "varchar(20)",
"elements": [],
"collation_id": 8,
"is_explicit_collation": false
},
{
"name": "inserted",
"type": 18,
"is_nullable": false,
"is_zerofill": false,
"is_unsigned": false,
"is_auto_increment": false,
"is_virtual": false,
"hidden": 1,
"ordinal_position": 3,
"char_length": 19,
"numeric_precision": 0,
"numeric_scale": 0,
"numeric_scale_null": true,
"datetime_precision": 0,
"datetime_precision_null": 0,
"has_no_default": false,
"default_value_null": false,
"srs_id_null": true,
"srs_id": 0,
"default_value": "AAAAAA==",
"default_value_utf8_null": false,
"default_value_utf8": "CURRENT_TIMESTAMP",
"default_option": "CURRENT_TIMESTAMP",
"update_option": "",
"comment": "",
"generation_expression": "",
"generation_expression_utf8": "",
"options": "interval_count=0;",
"se_private_data": "",
"engine_attribute": "",
"secondary_engine_attribute": "",
"column_key": 1,
"column_type_utf8": "timestamp",
"elements": [],
"collation_id": 8,
"is_explicit_collation": false
}
],
"schema_ref": "my_isam_db",
"se_private_id": 18446744073709552000,
"engine": "MyISAM",
"last_checked_for_upgrade_version_id": 0,
"comment": "",
"se_private_data": "",
"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": "",
"default_subpartitioning": 0,
"indexes": [
{
"name": "PRIMARY",
"hidden": false,
"is_generated": false,
"ordinal_position": 1,
"comment": "",
"options": "flags=0;",
"se_private_data": "",
"type": 1,
"algorithm": 2,
"is_algorithm_explicit": false,
"is_visible": true,
"engine": "MyISAM",
"engine_attribute": "",
"secondary_engine_attribute": "",
"elements": [
{
"ordinal_position": 1,
"length": 4,
"order": 2,
"hidden": false,
"column_opx": 0
}
]
}
],
"foreign_keys": [],
"check_constraints": [],
"partitions": [],
"collation_id": 8
}
}
As you can see, this SDI file contains a lot of information. Just for reference this was the table’s creation statement:
CREATE TABLE `t1` ( `id` int unsigned NOT NULL AUTO_INCREMENT, `name` varchar(20) DEFAULT NULL, `inserted` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1
Are the SDI files used only with MyISAM ?
That SDI file can be used to copy MySIAM tables around MySQL 8.0 servers using the specific statement IMPORT TABLE FROM.
The SDI files are also used for any other Storage Engine that is not able to embed the metadata information. Currently only InnoDB and NDB don’t require any SDI file.
If you create a CSV table, it will also have a SDI:
CREATE TABLE `t2` ( `id` int unsigned NOT NULL, `name` varchar(20) NOT NULL DEFAULT '', `inserted` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ) ENGINE=CSV DEFAULT CHARSET=latin1 [root@imac my_isam_db]# ls -l t2* -rw-r----- 1 mysql mysql 3268 Aug 11 13:27 t2_5037.sdi -rw-r----- 1 mysql mysql 35 Aug 11 13:27 t2.CSM -rw-r----- 1 mysql mysql 0 Aug 11 13:27 t2.CSV
And of course we can check that information from the SDI file too:
[root@imac my_isam_db]# cat t2_5037.sdi | jq ."dd_object"."engine" "CSV"
The SDI files provides metadata redundancy. And even for InnoDB tables it can be useful if for example the data dictionary becomes unavailable, object metadata can be extracted directly from InnoDB tablespace files.
SDI Information and InnoDB
For InnoDB tables, the SDI information can be extracted from the tablespace using a tool called ibd2sdi.
ibd2sdi can be run on file-per-table tablespace files (
*.ibdfiles), general tablespace files (*.ibdfiles), system tablespace files (ibdata*files), and the data dictionary tablespace (mysql.ibd). It is not supported for use with temporary tablespaces or undo tablespaces.
Let’s try it then with InnoDB. I just created another table (t3) like the previous ones but using InnoDB as storage engine and indeed in the schema’s directory we can see this single .idb file:
[root@imac my_isam_db]# ls t1_5017.sdi t1.MYD t1.MYI t2_5037.sdi t2.CSM t2.CSV t3.ibd
Now we can generate a SDI file from it:
# ibd2sdi t3.ibd -d t3.sdi
We can parse the new created SDI file but it’s not required by MySQL, it’s just an extra copy of the metadata.
Can I modify manually an existing SDI file ?
Of course, this is not recommended. However if you delete or modify a .sdi file after the table was already in MySQL, the server won’t require it as the structure is already part of the Data Dictionary.
Every time you modify a table using an engine that requires a SDI file, a new file with a different OID number replaces the eventual previous one.
