How to apply ROW_FORMAT=DYNAMIC to an existing table

Question

I have a table with a large number of longtext fields (18) along with a number of other various integer and varchar fields. Recently a number of additional longtext fields were added, and have suddenly forced me to learn all about 8K row size limits. The DB is running Mysql 5.6.34, and the table in question is currently Antelope / ROW_FORMAT=COMPACT.

My understanding is that in this format, each column will take up to 768 bytes per row, until overflowing into separate storage. This leads me to this error when too many of the various longtexts get a significant amount of data:

Row size too large (> 8126). Changing some columns to TEXT or BLOB or using ROW_FORMAT=DYNAMIC or ROW_FORMAT=COMPRESSED may help. In current row format, BLOB prefix of 768 bytes is stored inline. Ignoring the rest of the fields in the table, were all 18 longtexts at 768 bytes, then the primary index would be storing 13,824 bytes for all fields.

I have attempted to update the table to ROW_FORMAT=DYNAMIC with the expectation that this should lower a longtext's overflow threshold from 768 bytes to just 20 bytes, and therefore should lower the maximum primary index storage requirement for all long text fields to 18 * 20 = 360 bytes. I attempted the update per the following:

ALTER TABLE mytable ROW_FORMAT=DYNAMIC;
OPTIMIZE TABLE mytable;

To no errors and the following output:

mydb.mytable optimize note Table does not support optimize, doing recreate + analyze instead mydb.mytable optimize status OK

If I view the CREATE TABLE syntax for the table I can see that the ROW_FORMAT=DYNAMIC is set.

I have then attempted to write a row to fill all longtext columns with around 5.7kb each, however I'm only able to fill 10 of them before I am preventing from saving the row, and 10 * 768 = 7,680 bytes, which when accounting for the other non-longtext required fields is getting pretty close to the 8kb limit, suggesting the ROW_FORMAT=DYNAMIC instruction is not applying to existing rows.

I don't particularly want to have to recreate the database by dumping/importing however as it is particularly large and would represent an extended service downtime that I'm not sure I can justify until other options are exhausted.

Solution 1

(This answer, though focused on indexes, is likely to solve your table problem.)

http://mysql.rjweb.org/doc.php/limits#767_limit_in_innodb_indexes

There are 5 choices for dealing with the 767 limit. This one seems to be the one you need

   SET GLOBAL innodb_file_format=Barracuda;
   SET GLOBAL innodb_file_per_table=1;
   SET GLOBAL innodb_large_prefix=1;
   logout & login (to get the global values);
   ALTER TABLE tbl ROW_FORMAT=DYNAMIC;  -- (or COMPRESSED)

(Upgrading to 5.7.7 or later is another solution -- but that only sets the above things as default; you would still need to do the ALTER, I think.)

Solution 2

Turns out I needed to set the innodb_file_format=barracuda before the ALTER/OPTIMIZE. For some reason I'd taken it as gospel that setting DYNAMIC would have implicitly set barracuda as well, however this has no longer proven to be the case:

SET GLOBAL innodb_file_format=barracuda

Comments

[DanH]

It appears that MySQL 5.7 seems a little more Barracuda orientated, is it worth upgrading to that or is the Barracuda and ROW_FORMAT=DYNAMIC handling identical other than the defaults?

[tadman]

It's always worth upgrading to the latest version that doesn't break your application.

[DanH]

Well of course, but regression testing isn't free :)

[tadman]

What I mean is it's always worth at least trying to upgrade. If testing is that much of a hassle you need more automated tests. The biggest changes in 5.7 are new defaults that make it a lot more particular about aggregate clauses where the results were previously ambiguous.

[Anch0rman]

DYNAMIC row format is not supported with Antelope, only COMPACT and REDUNDANT are. As you mentioned, Barracuda is now the recommended/default format. If you consider upgrading, you should read this: dev.mysql.com/doc/refman/5.7/en/innodb-file-format.html. Serious compatibility and corruption issues can arise when converting between file formats.

[Bill Karwin]

There was never any version of MySQL called 3.6.34. Do you mean 5.6.34? Run query SELECT @@version; to check.

[Bill Karwin]

Note that you may still have blob/text fields take up to 768 bytes per row in the primary page, if they fit within the row. If a blob/text doesn't fit, then the whole blob/text is moved to overflow pages and replaced with a 20-byte pointer to the first overflow page.

[DanH]

@BillKarwin yes a typo, 5.6.34 thanks

[DanH]

@BillKarwin ahh OK, well as long as the net effect is that I no longer see the row limit error I don't mind too much when specific fields are paging. Thanks again

[Rick James]

That's part of the answer.

[DanH]

Ahh yes, I think I already had these values set, possibly a default for the RDS MySQL configuration.

[Rick James]

You specified Antelope and COMPACT in your question. If RDS won't let you change them, then this Answer is not available to you.