What's the risk of keeping a table with row size > 8126 in MySQL 5.7?

Question

I just upgraded from MySQL 5.6 to 5.7, and had the bad surprise to discover that a WordPress plugin table contains a ridiculous 438 fields, and exceeds the row size limit of 8126 bytes.

Here is the warning I got when running mysql_upgrade:

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.

This was just a warning though, and the table is still there. I can't create a new table with the same structure, but interestingly enough, I can keep the old table and use it (at least, SELECT from it).

I've read RolandoMySQLDBA's and Bill Karwin's answers on similar topics, and no solution can help me:

• changing to ROW_FORMAT=DYNAMIC is not enough, as the table is full of inline UTF-8 VARCHAR fields that alone exceed the max size
• modifying the structure of the table is out of the question, as it's part of a third-party software, and that would be like opening a can of worms

I've reported the error to the plugin developers and hope they'll fix the table soon, but in the meantime, I'm stuck with this outlaw table.

So the questions are:

• How can MySQL work with a table that does not respect the InnoDB storage rules, when it does not allow to create such a table?
• Is there any risk to keep the table in its current state?
• Are there limitations on the type of queries I can run on this table?
• If I replicate this server to another MySQL 5.7 server, will the replication fail because of this table?

I upgraded from MySQL 5.6. I did try DYNAMIC and COMPRESSED formats, but no luck. I calculated the size of the integer + varchar fields, they're totaling 17,059 bytes, so I have no chance to succeed this way.

The plugin is called Photo Gallery and the faulty table is wp_bwg_theme.

Answer 1

SET GLOBAL innodb_file_format=Barracuda;
SET GLOBAL innodb_file_per_table=ON;
ALTER TABLE tbl ROW_FORMAT=DYNAMIC;  -- Or COMPRESSED

That is what was needed in 5.6, I think. Since Barracuda and file_per_table are defaults in 5.7, most of this goes away. However, the upgrade may have left the table Antelope and/or not file_per_table.

So, I recommend throwing those 3 commands and see if it "fixes" your problem for good. (Yeah, keep some kind of backup somewhere first.)

You mentioned utf8; perhaps you want utf8mb4, too?

ALTER TABLE tbl CONVERT TO utf8mb4;

(I don't now if replication will be a problem. Presumably after the above changes it won't be a problem on a 5.7 Slave.)

Edit

But... Being the default (for 5.7) does not mean that you got those values for an existing file after an upgrade.

Look in information_schema.INNODB_SYS_TABLES to see what the file_format and row_format are. The column space indicates file_per_table if the value is > 0.

A drastic way to deal with the problem: Change the InnoDB page size. The default is 16KB, but 32KB is possible. If I am not mistaken, all tables on the system have to be converted to the new page size, so it is a non-trivial undertaking, and may have other ramifications.

The handling of VARCHAR in DYNAMIC/COMPRESSED

When is the string stored in the main part of the record, versus stored in some other block?

• Small size (string is < 40 bytes): The entire string is stored with the record. 2+N bytes.
• Medium size: Either 'small' or 'large', depending on "record not too big".
• Large size: 2+20 bytes in record (length+pointer); entire text stored separately.

"record too big": When the row is too long, the longest columns are stored in off-page storage. That is, some times a column will be entirely off-page, sometimes on-page, depending on the makeup of the entire row.

VARCHAR and TEXT and BLOB are handled the same for DYNAMIC or COMPRESSED.

The DYNAMIC format is based on the idea that if a portion of a long data value is stored off-page, it is usually most efficient to store all of the value off-page ("all or none").

COMPRESSED = DYNAMIC + compression. KEY_BLOCK_SIZE controls how much column data is stored in the clustered index, and how much is placed on overflow pages.

5.7 reference and older, more detailed, discussion; and other pages.

Answer 2

After reading Rick James' answer (+1 for him), the only thing I would add for this one is just to configure a bigger InnoDB Buffer Pool if you go with ROW_FORMAT=COMPRESSED. Why ?

I had mentioned in my old post innodb_file_format Barracuda how you need to increase the size of the Buffer Pool to accommodate the decompressing of pages you access.

Plagiarizing from my own post ...

SCENARIO

• You have a DB Server with a 8G Buffer Pool
• You ran compression with key_block_size=8
  • 8 is 50.00% of 16
  • 50.00% of 8G is 4G
  • raise innodb_buffer_pool_size to 12G (8G + 4G)
• You ran compression with key_block_size=4
  • 4 is 25.00% of 16
  • 25.00% of 8G is 2G
  • raise innodb_buffer_pool_size to 10G (8G + 2G)
• You ran compression with key_block_size=2
  • 2 is 12.50% of 16
  • 12.50% of 8G is 1G
  • raise innodb_buffer_pool_size to 9G (8G + 1G)
• You ran compression with key_block_size=1
  • 1 is 06.25% of 16
  • 06.25% of 8G is 0.5G (512M)
  • raise innodb_buffer_pool_size to 8704M (8G (8192M) + 512M)

MORAL OF THE STORY : The InnoDB Buffer Pool just needs additional breathing room when handling compressed data and index pages.