Bug #44032 In ROW_FORMAT=REDUNDANT, update UTF-8 CHAR to/from NULL is not in-place

• Submitted: 2 Apr 2009 6:41
• Modified: 20 Jun 2010 1:03
• Reporter: Marko Mäkelä
• Status: Closed
• Category: MySQL Server: InnoDB storage engine
• Severity: S3 (Non-critical)
• Version: 5.0.3+
• OS: Any
• CPU Architecture: Any

[2 Apr 2009 6:41] Marko Mäkelä

Description:
Because the functions dtype_get_sql_null_size() and dtype_get_fixed_size() assume ROW_FORMAT=COMPACT, the storage size of CHAR columns that contain UTF-8 data is variable also when the table is in ROW_FORMAT=REDUNDANT. In ROW_FORMAT=REDUNDANT, CHAR columns are supposed to be fixed-length, also when they contain UTF-8 data or NULL.

Due to this bug, UTF-8 CHAR columns in ROW_FORMAT=REDUNDANT cannot be updated in place. InnoDB will delete and insert records instead, which is less efficient and may in the worst case fragment the index tree.

How to repeat:
Set a breakpoint in btr_cur_update_in_place(). Observe that it is not called for all of the UPDATE statements below:

CREATE TABLE t1(c CHAR(3) CHARACTER SET UTF8) ROW_FORMAT=REDUNDANT
ENGINE=InnoDB;
INSERT INTO t1 VALUES('abc'),(0xEFBCA4EFBCA4EFBCA4);
UPDATE t1 SET c='DDD' WHERE c=0xEFBCA4EFBCA4EFBCA4;
UPDATE t1 SET c=NULL WHERE c='DDD';
UPDATE t1 SET c='DDD' WHERE c IS NULL;
DROP TABLE t1;

Suggested fix:
Add a flag to dtype_get_sql_null_size() and related functions for distinguishing ROW_FORMAT=REDUNDANT and subsequent row formats.

[2 Apr 2009 13:28] Marko Mäkelä

This bug should mostly affect users who started to use UTF-8 in MySQL 4.1 or who explicitly created nullable UTF-8 CHAR columns in ROW_FORMAT=REDUNDANT. The data is not corrupted, but the performance is affected. As the fix is somewhat risky and the impact is low, this should only be fixed in the InnoDB Plugin and in MySQL 6.0, not in MySQL 5.1.

[7 Apr 2009 13:01] Marko Mäkelä

The patch has been committed to InnoDB's MySQL 6.0 source repository. It will also be available in InnoDB Plugin 1.0.4.

[23 Sep 2009 16:05] Paul DuBois

Noted in 5.4.2 changelog.

For InnoDB tables with ROW_FORMAT = REDUNDANT, CHAR columns should be
fixed-length and allow in-place updates. However, they were treated
as variable-length if they contained utf8 data or NULL, preventing
in-place updates.

[5 May 2010 15:04] Bugs System

Pushed into 5.1.47 (revid:[e-mail]-20100505145753-ivlt4hclbrjy8eye) (version source revid:[e-mail]-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)

[6 May 2010 17:53] Paul DuBois

Push resulted from incorporation of InnoDB tree. No changes pertinent to this bug.
Re-closing.

[28 May 2010 6:06] Bugs System

Pushed into mysql-next-mr (revid:[e-mail]-20100524190136-egaq7e8zgkwb9aqi) (version source revid:[e-mail]-20100331130613-8ja7n0vh36a80457) (pib:16)

[28 May 2010 6:34] Bugs System

Pushed into 6.0.14-alpha (revid:[e-mail]-20100524190941-nuudpx60if25wsvx) (version source revid:[e-mail]-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)

[28 May 2010 7:02] Bugs System

Pushed into 5.5.5-m3 (revid:[e-mail]-20100524185725-c8k5q7v60i5nix3t) (version source revid:[e-mail]-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)

[29 May 2010 23:08] Paul DuBois

Push resulted from incorporation of InnoDB tree. No changes pertinent to this bug.
Re-closing.

[15 Jun 2010 8:09] Bugs System

Pushed into 5.5.5-m3 (revid:[e-mail]-20100615080459-smuswd9ooeywcxuc) (version source revid:[e-mail]-20100415070122-1nxji8ym4mao13ao) (merge vers: 5.1.47) (pib:16)

[15 Jun 2010 8:24] Bugs System

Pushed into mysql-next-mr (revid:[e-mail]-20100615080558-cw01bzdqr1bdmmec) (version source revid:[e-mail]-20100415070122-1nxji8ym4mao13ao) (pib:16)

[17 Jun 2010 12:11] Bugs System

Pushed into 5.1.47-ndb-7.0.16 (revid:[e-mail]-20100617114014-bva0dy24yyd67697) (version source revid:[e-mail]-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)

[17 Jun 2010 12:58] Bugs System

Pushed into 5.1.47-ndb-6.2.19 (revid:[e-mail]-20100617115448-idrbic6gbki37h1c) (version source revid:[e-mail]-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)

[17 Jun 2010 13:38] Bugs System

Pushed into 5.1.47-ndb-6.3.35 (revid:[e-mail]-20100617114611-61aqbb52j752y116) (version source revid:[e-mail]-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)