Bug #121102 INPLACE rebuild may corrupt long-prefix secondary indexes

• Submitted: 17 Aug 1:29
• Modified: 21 Aug 11:22
• Reporter: George Ma (OCA)
• Status: Documenting
• Category: MySQL Server: InnoDB storage engine
• Severity: S2 (Serious)
• Version: 8.0.46,8.4.11,9.7.2, 26.10.0
• OS: Any
• CPU Architecture: Any

[17 Aug 1:29] George Ma

Description:
  When rebuilding an InnoDB table with `ALGORITHM=INPLACE`, converting from
  `ROW_FORMAT=REDUNDANT`/`COMPACT` to `DYNAMIC`/`COMPRESSED` can create a corrupt
  secondary index if:

  - an indexed `VARCHAR` or `TEXT` value is externally stored; and
  - the new index prefix needs more than 767 bytes.

  The ALTER succeeds, but the rebuilt index entry is truncated. A later update
  of the primary key cannot delete that entry correctly.

  Debug builds abort; release builds may report successful DML while leaving the
  secondary index corrupt.

How to repeat:
  ```sql
  CREATE TABLE t1 (
    id INT NOT NULL,
    c VARCHAR(1000) CHARACTER SET utf8mb4 NOT NULL,
    -- Add enough fixed-length columns to force c off-page
    PRIMARY KEY (id),
    KEY k_c (c(191))
  ) ENGINE=InnoDB ROW_FORMAT=REDUNDANT;

  INSERT INTO t1 VALUES (1, REPEAT(_utf8mb4 0xF09F9880, 1000));

  ALTER TABLE t1
    ROW_FORMAT=DYNAMIC,
    DROP KEY k_c, ADD KEY k_c (c(768)),
    ALGORITHM=INPLACE;

  UPDATE t1 SET id = 2 WHERE id = 1;
  CHECK TABLE t1;
```

  Expected: the update succeeds and CHECK TABLE returns OK.

  Actual: the old index entry contains only about 767 bytes instead of the
  required 3072 bytes. In release builds, CHECK TABLE reports a corrupt index.

[21 Aug 11:22] Shane Bester

Thanks for the report.
Testcase:
---------

drop table if exists t1;
  CREATE TABLE t1 (
    id INT NOT NULL,
    c0 VARCHAR(1000) CHARACTER SET utf8mb4 NOT NULL,
    c1 VARCHAR(1000) CHARACTER SET utf8mb4 NOT NULL,
    c2 VARCHAR(1000) CHARACTER SET utf8mb4 NOT NULL,
    c3 VARCHAR(1000) CHARACTER SET utf8mb4 NOT NULL,
    c4 VARCHAR(1000) CHARACTER SET utf8mb4 NOT NULL,
    c5 VARCHAR(1000) CHARACTER SET utf8mb4 NOT NULL,
    c6 VARCHAR(1000) CHARACTER SET utf8mb4 NOT NULL,
    c7 VARCHAR(1000) CHARACTER SET utf8mb4 NOT NULL,
    c8 VARCHAR(1000) CHARACTER SET utf8mb4 NOT NULL,
    c9 VARCHAR(1000) CHARACTER SET utf8mb4 NOT NULL,
    PRIMARY KEY (id),
    KEY k_c (c0(191))
  ) ENGINE=InnoDB ROW_FORMAT=REDUNDANT;

  INSERT INTO t1(id,c0,c1,c2,c3,c4,c5,c6,c7,c8,c9) VALUES (1, REPEAT(_utf8mb4 0xF09F9880, 1000),REPEAT(_utf8mb4 0xF09F9880, 1000),REPEAT(_utf8mb4 0xF09F9880, 1000),REPEAT(_utf8mb4 0xF09F9880, 1000),REPEAT(_utf8mb4 0xF09F9880, 1000),REPEAT(_utf8mb4 0xF09F9880, 1000),REPEAT(_utf8mb4 0xF09F9880, 1000),REPEAT(_utf8mb4 0xF09F9880, 1000),REPEAT(_utf8mb4 0xF09F9880, 1000),REPEAT(_utf8mb4 0xF09F9880, 1000));

  ALTER TABLE t1
    ROW_FORMAT=DYNAMIC,
    DROP KEY k_c, ADD KEY k_c (c0(768)),
    ALGORITHM=INPLACE;

  UPDATE t1 SET id = 2 WHERE id = 1;
  CHECK TABLE t1 extended;

[8 Sep 5:20] MySQL Admin

Posted by developer: Bug status updated to 'Patch pending'

[16 Oct 5:20] MySQL Admin

Posted by developer: Bug status updated to 'Patch approved'

[19 Oct 8:48] MySQL Admin

Posted by developer: Bug status updated to 'Documenting'