Bug #78542 Clarify off page storage of other long text/binary types with DYNAMIC format

• Submitted: 24 Sep 2015 13:33
• Modified: 28 Apr 2016 16:39
• Reporter: Mark Leith
• Status: Closed
• Category: MySQL Server: Documentation
• Severity: S3 (Non-critical)
• Version: 5.5+
• OS: Any
• CPU Architecture: Any

[24 Sep 2015 13:33] Mark Leith

Description:
The documentation is not very clear on how long CHAR/VARCHAR/VARBINARY when it comes to how the pages are stored in the DYNAMIC row format.

What is documented here:

https://dev.mysql.com/doc/refman/5.5/en/innodb-row-format-dynamic.html

Doesn't mention that the same also applies to VARCHAR/VARBINARY, and CHAR when using utf8mb4 and > CHAR(193) (making it longer than 768) for example. 

How to repeat:
Read the docs, see that nothing is mentioned for other long types.

Suggested fix:
Clarify for all types that are stored off page.

[24 Sep 2015 13:36] Marko Mäkelä

I believe that already CHAR(192) with mbmaxlen>3 (such as, utf8mb4 or utf16 or gb18030) can be stored off-page by InnoDB.

[24 Sep 2015 20:13] Morgan Tocker

This likely applies to JSON type too.

[28 Apr 2016 16:39] Daniel Price

Posted by developer:
 
The content has been updated as follows:

https://dev.mysql.com/doc/refman/5.7/en/innodb-row-format-dynamic.html

"When a table is created with ROW_FORMAT=DYNAMIC or ROW_FORMAT=COMPRESSED,
long variable-length column values (for VARBINARY, VARCHAR, BLOB, TEXT,
and JSON columns) are stored fully off-page, and the clustered index
record contains only a 20-byte pointer to the overflow page. InnoDB will
also store long CHAR column values off-page if the column value is greater
than or equal to 768 bytes, which can occur when the maximum byte length
of the character set is greater than 3, as it is with utf8mb4, for
example."

Note:JSON is only mentioned in the 5.7 version of the manual.