Bug #82913 manual should clarify storage requirements for variable length character sets
Submitted: 8 Sep 2016 21:49 Modified: 18 Oct 2016 17:55
Reporter: Morgan Tocker Email Updates:
Status: Closed Impact on me:
Category:MySQL Server: Documentation Severity:S3 (Non-critical)
Version:5.7 OS:Any
Assigned to: CPU Architecture:Any

[8 Sep 2016 21:49] Morgan Tocker
In the DYNAMIC/COMPACT family of row formats, InnoDB uses the variable length for storage of CHAR columns.

The MySQL manual does not make this clear:

The table shows storage required only for single-byte character sets.  In MySQL 8.0 we've stated the intention is to change the default to utf8mb4; so knowing this is variable is useful information.

How to repeat:
Should be easy to reproduce (if required):

- Create two tables with one having 10 columns all VARCHAR(255), the other all CHAR(255) and insert 1 character in each.  Multiple by 1 million rows.
- Make sure the character set is utf8mb4
- OPTIMIZE both tables
- They should be the same size.

Suggested fix:
It's hard to fix and account for all nuances, since inside the server while transferring rows I believe the full buffer is used.
[8 Sep 2016 22:46] MySQL Verification Team
Thank you for the bug report.
[18 Oct 2016 17:55] Daniel Price
Posted by developer:
The reference section was updated with the following information:


"For InnoDB COMPACT, DYNAMIC, and COMPRESSED row formats, CHAR is treated
as variable-length type if the length of the column value is greater than
or equal to 768 bytes, which can occur if the maximum byte length of the
character set is greater than 3, as it is with utf8mb4, for example. When
treated as a variable-length type, a CHAR column value may be chosen for
off-page storage." 

Updates were also made to:


Updates were made to all versions of the reference manual (5.5,5.6,5.7,8.0).

Thank you for the bug report.