Error upgrading from 6.2.5 to 6.4.17 SQLSTATE 42000 Row size too large
How Do I?
11 months ago
It was about time I upgraded one of my sites, but on running the update script, I ran into the following error message:
We stopped on Preflight check.
SQLSTATE 42000|: Syntax error or access violation: 1118 Row size too large.
The maximum row size for the used table type, not counting BLOBs, is 8126.
This includes storage overhead, check the manual.
You have to change some columns to TEXT or BLOBs:
ALTER TABLE 'exp_members ADD
'dismissed_banner' char(1) NOT NULL DEFAULT 'n'I’m running MySQL on PHP 8.1
11 months ago
This is probably being caused by your site’s exp_channel_data table.
Prior to EE4 all the fields defined in a site were stored as columns in the exp_channel_data table: for sites with many fields this meant that the table became wide.
There is a limit enforced by InnodB of 8126 characters for a row in a table - if your exp_channel-data table is wider than this then some MySQL operations will fail when undertaken, others work OK.
There are two possible solutions.
- You can turn of ‘strict’ innodb rules - this is not easy to do unless you have access to the SQL database, but it stops Innodb complaining about the error and allows things to progress - this can be achieved by issuing the command
SET innodb_strict_mode=OFF;- You can shrink the
exp_channel_datatable by migrating the fields it contains to the more modern “one table per field” approach introduced in EE4. There is no official utility for doing this, but various versions of some code written by EllisLabs back in 2017 around that do this migration but can be a bit buggy. I recently updated this code to work with EE7 and php 8 (and hit at least most of the bugs) - if you want to get a copy send me a direct message or contact me via the EE slack community.
HTH