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.