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.

  • 1
    related bug link. Though I'm thinking this might be its own bug. In theory I agree with you, it should be able to create your many varchar column table in ROW_FORMAT=DYNAMIC. FWIW get the same error in MariaDB-10.2.17 and 10.3.9. I don't think the documents are wrong, there's just a check for ROW length that might not fully count for ROW_FORMAT=DYNAMIC/COMPRESSED. – danblack Sep 12 '18 at 22:43
  • @danblack, I have come across various source regarding this topic where they haven't talk this as an issue instead they demonstrated the same concept. Which means that it just a mysql behaviour. But, we want an explanation about the need for such behaviour – Dinesh Kumar Sep 18 '18 at 5:10
  • 1
    if you report it as a bug, it might spark someones interest enough to change mysql behaviour. – danblack Sep 18 '18 at 5:12
  • Rerun that CREATE TABLE, trying each of the four ROW_FORMATs. – Rick James Oct 5 '18 at 4:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.