Bug #111286 SDI has incorrect types for DB_TRX_ID and DB_ROLL_PTR fields

• Submitted: 5 Jun 2023 22:05
• Modified: 6 Jun 2023 8:54
• Reporter: Jeremy Cole (Basic Quality Contributor) (OCA)
• Status: Verified
• Category: MySQL Server: Data Dictionary
• Severity: S3 (Non-critical)
• Version: 8.0.33
• OS: Any
• CPU Architecture: Any
• Tags: SDI

[5 Jun 2023 22:05] Jeremy Cole

Description:
SDI has incorrect types for the InnoDB system columns DB_TRX_ID and DB_ROLL_PTR; it has:
* DB_TRX_ID as MEDIUMINT (dd::enum_column_types::INT24, value 10) but should be either BIGINT or ideally a new 48-bit integer type
* DB_ROLL_PTR as BIGINT (dd::enum_column_types::LONGLONG, value 9) but should probably be a new/special 7-byte type since it requires special parsing

How to repeat:
Create a simple test table (the schema is largely irrelevant):

CREATE TABLE `t` (`id` int NOT NULL, `v` varchar(100) NOT NULL, PRIMARY KEY (`id`));

Examine the SDI data for DB_TRX_ID and DB_ROLL_PTR using ibd2sdi and jq:

$ ~/opt/mysql/8.0.33/bin/ibd2sdi ~/sandboxes/msb_8_0_33/data/test/t.ibd | jq '.[1]["object"]["dd_object"]["columns"][2]'
{
  "name": "DB_TRX_ID",
  "type": 10,
  "is_nullable": false,
  "is_zerofill": false,
  "is_unsigned": false,
  "is_auto_increment": false,
  "is_virtual": false,
  "hidden": 2,
  "ordinal_position": 3,
  "char_length": 6,
  "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": "",
  "se_private_data": "table_id=1128;",
  "engine_attribute": "",
  "secondary_engine_attribute": "",
  "column_key": 1,
  "column_type_utf8": "",
  "elements": [],
  "collation_id": 63,
  "is_explicit_collation": false
}
```

```
$ ~/opt/mysql/8.0.33/bin/ibd2sdi ~/sandboxes/msb_8_0_33/data/test/t.ibd | jq '.[1]["object"]["dd_object"]["columns"][3]'
{
  "name": "DB_ROLL_PTR",
  "type": 9,
  "is_nullable": false,
  "is_zerofill": false,
  "is_unsigned": false,
  "is_auto_increment": false,
  "is_virtual": false,
  "hidden": 2,
  "ordinal_position": 4,
  "char_length": 7,
  "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": "",
  "se_private_data": "table_id=1128;",
  "engine_attribute": "",
  "secondary_engine_attribute": "",
  "column_key": 1,
  "column_type_utf8": "",
  "elements": [],
  "collation_id": 63,
  "is_explicit_collation": false
}

 51 // Redefined enum_field_types here. We can remove some old types ?
 52 enum class enum_column_types {
 53   DECIMAL = 1,  // This is 1 > than MYSQL_TYPE_DECIMAL
 54   TINY,      // = 2
 55   SHORT,     // = 3
 56   LONG,      // = 4
 57   FLOAT,     // = 5
 58   DOUBLE,    // = 6
 59   TYPE_NULL, // = 7
 60   TIMESTAMP, // = 8
 61   LONGLONG,  // = 9
 62   INT24,     // = 10
 63   DATE,      // etc.
 64   TIME,
 65   DATETIME,
 66   YEAR,
 67   NEWDATE,
 68   VARCHAR,
 69   BIT,
 70   TIMESTAMP2,
 71   DATETIME2,
 72   TIME2,
 73   NEWDECIMAL,
 74   ENUM,
 75   SET,
 76   TINY_BLOB,
 77   MEDIUM_BLOB,
 78   LONG_BLOB,
 79   BLOB,
 80   VAR_STRING,
 81   STRING,
 82   GEOMETRY,
 83   JSON
 84 };

These are added in ha_innobase::get_extra_columns_and_keys() as follows:

14928   /* Add the InnoDB system columns DB_TRX_ID, DB_ROLL_PTR. */
14929   dd::Column *db_trx_id = dd_add_hidden_column(
14930       dd_table, "DB_TRX_ID", DATA_TRX_ID_LEN, dd::enum_column_types::INT24);
14931   if (db_trx_id == nullptr) {
14932     return ER_WRONG_COLUMN_NAME;
14933   }
14934
14935   dd::Column *db_roll_ptr =
14936       dd_add_hidden_column(dd_table, "DB_ROLL_PTR", DATA_ROLL_PTR_LEN,
14937                            dd::enum_column_types::LONGLONG);
14938   if (db_roll_ptr == nullptr) {
14939     return ER_WRONG_COLUMN_NAME;
14940   }
14941
14942   dd_add_hidden_element(primary, db_trx_id);
14943   dd_add_hidden_element(primary, db_roll_ptr);

I've no idea why the completely incorrect types are used there.

In addition, the length fields are passed to dd_add_hidden_column() as DATA_TRX_ID_LEN and DATA_ROLL_PTR_LEN are incorrect and result in char_length fields stored in the JSON in SDI that are incorrect, as that field is intended to be used for display width rather than stored byte length (see for example the char_length for an INT UNSIGNED type, which is 11).

[6 Jun 2023 8:54] MySQL Verification Team

Hello Jeremy,

Thank you for the report and feedback!

Regards,
Umesh

[6 Jun 2023 12:35] huahua xu

The other hidden system column `DB_ROW_ID` has the same issue: 

```

if (primary == nullptr) {
    dd::Column *db_row_id = dd_add_hidden_column(
        dd_table, "DB_ROW_ID", DATA_ROW_ID_LEN, dd::enum_column_types::INT24);

    if (db_row_id == nullptr) {
      return ER_WRONG_COLUMN_NAME;
    }

    primary = dd_set_hidden_unique_index(dd_table->add_first_index(),
                                         primary_key_name, db_row_id);
  }

  ```