Description:
The "default_value" field inside the "columns" structure of a Table object in SDI seems to copy the buffer content from C++ without regard to whether the column actually has a default value or what its length is; the buffer is sized based on the maximum length of the column as-defined, and the default_value is the full length. That means that e.g. a utf8mb4-encoded VARCHAR(128) column without a default results in a Base64-encoded buffer of 514 zero bytes in the JSON stored.
If the column has a default value, the default is included in the "default_value" field as expected, but it still contains all trailing zeroes from the internal buffer, so a default of "foo" results in the same 514-byte value but starting with "\x03\x00foo\x00..." (little-endian 16-bit length of 3, followed by the string "foo", followed by 509 zero bytes).
How to repeat:
Create a simple table:
CREATE TABLE `t` (`x` VARCHAR(128) NOT NULL);
Use the provided ibd2sdi tool to dump the SDI data, filtering it to the specific object just for brevity here:
$ ~/opt/mysql/8.0.33/bin/ibd2sdi ~/sandboxes/msb_8_0_33/data/test/t.ibd | jq '.[1]["object"]["dd_object"]["columns"][0]'
{
"name": "x",
"type": 16,
"is_nullable": false,
"is_zerofill": false,
"is_unsigned": false,
"is_auto_increment": false,
"is_virtual": false,
"hidden": 1,
"ordinal_position": 1,
"char_length": 512,
"numeric_precision": 0,
"numeric_scale": 0,
"numeric_scale_null": true,
"datetime_precision": 0,
"datetime_precision_null": 1,
"has_no_default": true,
"default_value_null": false,
"srs_id_null": true,
"srs_id": 0,
"default_value": "AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA\nAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA\nAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA\nAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA\nAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA\nAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA\nAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA\nAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA\nAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA\nAA==",
"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": "table_id=1091;",
"engine_attribute": "",
"secondary_engine_attribute": "",
"column_key": 1,
"column_type_utf8": "varchar(128)",
"elements": [],
"collation_id": 255,
"is_explicit_collation": false
}
Note the default_value containing "AAAA..." which decodes to a 514-byte buffer of \0.
Creating a table with a default value:
CREATE TABLE `t` (`x` VARCHAR(128) NOT NULL DEFAULT "foo");
$ ~/opt/mysql/8.0.33/bin/ibd2sdi ~/sandboxes/msb_8_0_33/data/test/t.ibd | jq '.[1]["object"]["dd_object"]["columns"][0]["default_value"]'
"AwBmb28AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA\nAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA\nAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA\nAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA\nAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA\nAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA\nAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA\nAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA\nAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA\nAA=="
If you create a table with e.g. a VARCHAR(16000) it will store an even bigger buffer of 64002 zero bytes. :)
Suggested fix:
Do not store the default_value in SDI if there is no default value. Do not store the entire buffer including all trailing zero bytes in any case.
Description: The "default_value" field inside the "columns" structure of a Table object in SDI seems to copy the buffer content from C++ without regard to whether the column actually has a default value or what its length is; the buffer is sized based on the maximum length of the column as-defined, and the default_value is the full length. That means that e.g. a utf8mb4-encoded VARCHAR(128) column without a default results in a Base64-encoded buffer of 514 zero bytes in the JSON stored. If the column has a default value, the default is included in the "default_value" field as expected, but it still contains all trailing zeroes from the internal buffer, so a default of "foo" results in the same 514-byte value but starting with "\x03\x00foo\x00..." (little-endian 16-bit length of 3, followed by the string "foo", followed by 509 zero bytes). How to repeat: Create a simple table: CREATE TABLE `t` (`x` VARCHAR(128) NOT NULL); Use the provided ibd2sdi tool to dump the SDI data, filtering it to the specific object just for brevity here: $ ~/opt/mysql/8.0.33/bin/ibd2sdi ~/sandboxes/msb_8_0_33/data/test/t.ibd | jq '.[1]["object"]["dd_object"]["columns"][0]' { "name": "x", "type": 16, "is_nullable": false, "is_zerofill": false, "is_unsigned": false, "is_auto_increment": false, "is_virtual": false, "hidden": 1, "ordinal_position": 1, "char_length": 512, "numeric_precision": 0, "numeric_scale": 0, "numeric_scale_null": true, "datetime_precision": 0, "datetime_precision_null": 1, "has_no_default": true, "default_value_null": false, "srs_id_null": true, "srs_id": 0, "default_value": "AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA\nAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA\nAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA\nAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA\nAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA\nAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA\nAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA\nAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA\nAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA\nAA==", "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": "table_id=1091;", "engine_attribute": "", "secondary_engine_attribute": "", "column_key": 1, "column_type_utf8": "varchar(128)", "elements": [], "collation_id": 255, "is_explicit_collation": false } Note the default_value containing "AAAA..." which decodes to a 514-byte buffer of \0. Creating a table with a default value: CREATE TABLE `t` (`x` VARCHAR(128) NOT NULL DEFAULT "foo"); $ ~/opt/mysql/8.0.33/bin/ibd2sdi ~/sandboxes/msb_8_0_33/data/test/t.ibd | jq '.[1]["object"]["dd_object"]["columns"][0]["default_value"]' "AwBmb28AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA\nAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA\nAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA\nAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA\nAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA\nAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA\nAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA\nAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA\nAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA\nAA==" If you create a table with e.g. a VARCHAR(16000) it will store an even bigger buffer of 64002 zero bytes. :) Suggested fix: Do not store the default_value in SDI if there is no default value. Do not store the entire buffer including all trailing zero bytes in any case.