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-8VARCHAR
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
.
Best Answer
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?
(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 thefile_format
androw_format
are. The columnspace
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
inDYNAMIC
/COMPRESSED
When is the string stored in the main part of the record, versus stored in some other block?
"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
andTEXT
andBLOB
are handled the same forDYNAMIC
orCOMPRESSED
.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.