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.

Answer 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.)