Row size too large DB error when trying to add new field to channel

InnoDB is definitely preferred. If you’re only seeing this on one site with fewer fields than others, it could be the field types themselves, but most likely it is a difference of MySQL server version. For this particular table, most fields are BLOB which are off-table, but starting in MySQL 5.6(.20?), InnoDB calculates using a maximum on-table possible value, resulting in a false positive, and an overzealous error. Some options:

• Downgrade MySQL
• Tell MySQL to ignore those InnoDB warnings by disabling InnoDB strict mode (different from SQL strict mode), innodb_strict_mode = 0
• Reduce the number of fields in use. Some fields that are contextually related for instance could be changed to a single Grid field.

FWIW, this is fully solved in v4, and shouldn’t be affected by arbitrary changes made to MySQL storage engines such as this particular one that came in some point in MySQL 5.6.

I understand, however compared to other v2 installs we use the amount of fields with this specific v3 install is really small, we have v2 installs that basically use 5 times the amount of fields we now use on v3 and on a side note seeing MySQL 5.03 (released like over 10 years ago) as min. requirement for EE3.x in the docs and running into issues with 5.7.20 feels kinda strange.

Since v4 isn’t released yet and we cannot downgrade to MySQL 5.6 (since we run this specific version in a shared hosting env) and we cannot specify any specific MySQL config settings either (due to the same shared hosting env restrictions) and reducing fields isn’t an option especially because we need to add more fields atm not fewer fields is there any other option/fix avail?

MySQL is 5.7.20-1 EE is 3.5.3

Does 3.5.11 bring any changes RE this issue or do have to switch to a managed or virtual server instead that allows us either to downgrade the MySQL version or to alter the InnoDB settings?

we have v2 installs that basically use 5 times the amount of fields we now use on v3

Yeah it can be frustrating because the error is wrong. The install absolutely can have more columns than InnoDB strict mode allows you to create; MySQL is hedging based on internal calculations, and refuses to add the column. I’m guessing the fields on the v2 installs were created before the environment was updated to MySQL 5.6.20+. It’s not the existence of the fields that causes the error, that check only occurs when adding a new column. There is literally no difference in the channel data schema between those two versions, and I can confirm that it would happen on v2 once that InnoDB strict mode threshold is reached.

It is a very complex issue, and two of the factors involved are outside of ExpressionEngine’s control. The third we have addressed in v4, as it is a major backwards-incompatible change to the schema.

reducing fields isn’t an option especially because we need to add more fields atm not fewer fields

Did you give a look at which fields could be grouped into Grid? You would not lose any fields that way, it’s just restructuring your IA. If you’d like to cut and paste a list of each of your field groups, I’m happy to take a look and help with the content model. Sometimes that’s a fun puzzle, and we’ve been able to guide site builders to reusable, solid IA with a tremendous reduction in fields.

is there any other option/fix avail

I guess one other option is you could export the database, and add your new fields locally where you have full control over MySQL settings, and then import the database up to your production server. It’s a little tedious, and most hosts will let you switch to MySQL 5.5 or disable that flag, so it doesn’t hurt to ask.

on a side note seeing MySQL 5.03 (released like over 10 years ago) as min. requirement

MySQL features in point releases aren’t as critical to our application as PHP is. For an app developer, there’s little in each MySQL release that is compelling, features that we can benefit from or are itching to try. Most MySQL improvements deal with internals, performance, and so forth, so the host typically has a greater interest or direct benefit from upgrading.

But on all points of the tech stack, our minimum version requirements are just that, the minimum requirements, and through v3 our application does not use any MySQL features that aren’t available in 5.03. We also have tons of customers in the education and government sectors who for whatever reason stay locked on technology for much longer periods of time than commercial web hosts, so it’s in our interest to let them be able to choose ExpressionEngine. Hope that makes sense.

Thanks for your detailed answer Derek.

Since we already use Grid fields a lot we don’t have much more fields we could convert into a Grid field structure. The export > alter > import approach may work, but it’s indeed nothing I’d like to use for a production system every time we need to add fields.

Guess we will go from shared hosting to a managed server instead.

Appreciate your support!

Derek, would be modifying the core DB class connector to use SET SESSION innodb_strict_mode=0; an option?

Could be, though maintaining a hack will be a chore across updates. Also not sure a host that doesn’t allow you to request config changes would allow such a query to be executed, they could prevent it somehow. If you do try it, make sure to run show variables like '%strict%'; from the ExpressionEngine SQL Utility to see if it “stuck”.