#StackBounty: #mysql #innodb #mysql-5.7 #row #data-pages Which columns are off-paged (ROW_FORMAT=DYNAMIC)?

Bounty: 50

When going through the MySQL 5.7 manual, I found some contradictory facts.

Fact 1:

When a table is created with ROW_FORMAT=DYNAMIC or ROW_FORMAT=COMPRESSED, InnoDB can store long variable-length column values (for VARCHAR, VARBINARY, and BLOB and TEXT types) fully off-page, with the clustered index record containing only a 20-byte pointer to the overflow page

Whether any columns are stored off-page depends on the page size and the total size of the row. When the row is too long, InnoDB chooses the longest columns for off-page storage until the clustered index record fits on the B-tree page. TEXT and BLOB columns that are less than or equal to 40 bytes are always stored in-line.

Doc link.

I the first paragraph, it is mentioned that varchar, varbinary, blob and text will be off paged (based on some criteria). In the second paragraph, text and blob will be in-lined (size < 40 bytes). So, what about varchar?

Fact 2:

CREATE TABLE t (a VARCHAR(10000), b VARCHAR(10000),
       c VARCHAR(10000), d VARCHAR(10000), e VARCHAR(10000),
       f VARCHAR(10000), g VARCHAR(6000)) ENGINE=InnoDB CHARACTER SET latin1;
ERROR 1118 (42000): Row size too large. The maximum row size for the used 
table type, not counting BLOBs, is 65535. This includes storage overhead, 
check the manual. You have to change some columns to TEXT or BLOBs

Doc link.

So, the message recommends us to have text or blob, which means varchar can’t be off-paged. This one contradicts with Fact 1 where it is mentioned that varchar is also off-paged if needed.

I think mostly it will be a documentation error. If you feel something else, please share here.

Get this bounty!!!

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.