Bug #111112 JSON SDI default_value in columns object includes trailing zero bytes
Submitted: 22 May 2023 19:56 Modified: 24 May 2023 12:37
Reporter: Jeremy Cole (Basic Quality Contributor) (OCA) Email Updates:
Status: Closed Impact on me:
Category:MySQL Server Severity:S3 (Non-critical)
Version:8.0 OS:Any
Assigned to: CPU Architecture:Any
Tags: SDI

[22 May 2023 19:56] Jeremy Cole
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:


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_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:


$ ~/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"]'

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.
[23 May 2023 12:11] MySQL Verification Team
Hi Mr. Cole,

Thank you very much for your bug report.

However, what you describe is expected behaviour. That is how MySQL is supposed to function. If you do not find this documented properly in our Reference Manual, then we can make it a documentation bug.

Meanwhile, we shall check whether it is documented or not ......

Waiting on your response.
[24 May 2023 12:37] MySQL Verification Team
Hi Mr. Cole,

We have analysed the matter further and it turns out that this behaviour is here to stay. We shall inform you if any changes are made in 8.1.

This behaviour will be, very soon documented thoroughly.

It will be documented, very soon, on https://dev.mysql.com/doc/refman/8.0/en/serialized-dictionary-information.html and  the ibd2sdi man page. It may be also documented on the following page:

There is already some documentation for it on the following page:


This report is closed now.
[24 May 2023 15:34] Jon Stephens
I've updated the ibd2sdi man page (see --type option) with some of the info from this bug report and a similar example.

(mysqldoc rev 75723)