MySQL max row size
A client has created a long questionnaire with around 110 questions. Everything is good up until question 107 but after that the MySQL row size was exceeded. It seems that when this happens, you can still add extra fields to a fieldset without any warning from Civi, but the column does not actually get added to the table in the database. This resulted in a broken database. I'm not sure what happens when an end user completes the questionnaire, but the reason that we picked up on it is because when you try to view the activities of a contact, there is a JS error and they do not load.
The MySQL maximum row size limit is 65,535 bytes apparently, and fields of VARCHAR format use up a lot more memory than fields of TEXT format. CiviCRM creates the columns as VARCHAR format. I was able to fix the issue by working out the correct column names for the missing fields, and adding them manually using the TEXT format. I was also able to change a bunch of the existing columns from VARCHAR to TEXT in order to free up more memory, and this did not result in any data loss.
I think it would be a very good idea to add some extra validation when creating new fields, to check that the column has actually been created in MySQL.
Also, I see that fields of 'Select' type created in Civi are also given the VARCHAR format in MySQL. Looking at the data in these fields, I saw that they had values such as '1', or '2', so the VARCHAR format was using way more space than required. Perhaps it would make sense for Select field types to be created as TEXT format instead? Is it the key or the value that is stored in the database here? Another validator could be added to make sure that none of the keys / values provided were too large to fit in a TEXT field on the database.