MySQL Bugs: #71964: Explanation of InnoDB physical row structure for CHAR is not clear.

Bug #71964	Explanation of InnoDB physical row structure for CHAR is not clear.
Submitted:	7 Mar 2014 9:02	Modified:	13 Aug 2015 17:38
Reporter:	Daniël van Eeden (OCA)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S3 (Non-critical)
Version:	5.6.16	OS:	Any
Assigned to:	Daniel Price	CPU Architecture:	Any
Tags:	innodb, storage, utf8, utf8mb4

Description:
http://dev.mysql.com/doc/refman/5.6/en/innodb-table-and-index.html#innodb-physical-record

"Internally, InnoDB attempts to store UTF-8 CHAR(N) columns in N bytes by trimming trailing spaces. (With REDUNDANT row format, such columns occupy 3 × N bytes.) Reserving the minimum space N in many cases enables column updates to be done in place without causing fragmentation of the index page."

So with REDUNDANT 3 x N bytes are used. This is logical for utf8 (utf8mb3), but does not make sense for the utf8mb4 character set. I assume 4 x N bytes are used for utf8mb4?

And for COMPACT:
CHAR(M) will be M bytes unless the OCTET_LENGTH(column) > M, then OCTET_LENGTH(column) will be used with a max of CHARACTER_OCTET_LENGTH in case of utf8mb4.
The CHARACTER_OCTET_LENGTH can be found in information_schema.columns.
Correct?

And for DYNAMIC:
I assume this follows the same rules as the COMPACT row_format for storing CHAR's?
http://dev.mysql.com/doc/refman/5.6/en/innodb-row-format-dynamic.html

How to repeat:
See description.

Thank you for the bug report.

Added tags

Posted by developer:
 
Q: I assume 4 x N bytes are used for utf8mb4?
Correct.

Q: CHAR(M) will be M bytes unless the OCTET_LENGTH(column) > M, then
OCTET_LENGTH(column) will be used with a max of CHARACTER_OCTET_LENGTH in
case of utf8mb4. The CHARACTER_OCTET_LENGTH can be found in information_schema.columns. Correct?
Yes.

Q: I assume this follows the same rules as the COMPACT row_format for storing CHAR's?
Yes.

The following pages have been updated for version 5, 6, and 7 of the reference manual. Changes should appear online within 24 hours:
https://dev.mysql.com/doc/refman/5.6/en/innodb-row-format-dynamic.html
https://dev.mysql.com/doc/refman/5.6/en/innodb-physical-record.html

Thank you for the bug report.