Database error 1118 - Row size too large

Question

The error occurs when I try to add a new custom field of any kind. The install contains 210 custom fields but will not accept any more. Any advice to troubleshoot would be great. Further details below:

EE 2.7.2 Fieldtype addons: Matrix, Playa, Wygam

Database error:

A Database Error Occurred Error Number: 1118 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. UPDATE exp_channel_data SET field_ft_233 = 'none' Filename: libraries/api/Api_channel_fields.php Line Number: 1454

PHP error:

A PHP Error was encountered Severity: Notice Message: Undefined index: field_fmt_options Filename: fieldtypes/EE_Fieldtype.php Line Number: 572

A PHP Error was encountered Severity: Warning Message: Invalid argument supplied for foreach() Filename: helpers/form_helper.php Line Number: 324

A PHP Error was encountered Severity: Notice Message: Undefined index: field_fmt_options Filename: fieldtypes/EE_Fieldtype.php Line Number: 572

A PHP Error was encountered Severity: Warning Message: Invalid argument supplied for foreach() Filename: helpers/form_helper.php Line Number: 324

A PHP Error was encountered Severity: Notice Message: Undefined index: field_fmt_options Filename: fieldtypes/EE_Fieldtype.php Line Number: 572

A PHP Error was encountered Severity: Warning Message: Invalid argument supplied for foreach() Filename: helpers/form_helper.php Line Number: 324

A PHP Error was encountered Severity: Notice Message: Undefined index: field_fmt_options Filename: fieldtypes/EE_Fieldtype.php Line Number: 572

A PHP Error was encountered Severity: Warning Message: Invalid argument supplied for foreach() Filename: helpers/form_helper.php Line Number: 324

A PHP Error was encountered Severity: Notice Message: Undefined index: field_fmt_options Filename: fieldtypes/EE_Fieldtype.php Line Number: 572

A PHP Error was encountered Severity: Warning Message: Invalid argument supplied for foreach() Filename: helpers/form_helper.php Line Number: 324

A PHP Error was encountered Severity: Notice Message: Undefined index: field_fmt_options Filename: fieldtypes/EE_Fieldtype.php Line Number: 572

A PHP Error was encountered Severity: Warning Message: Invalid argument supplied for foreach() Filename: helpers/form_helper.php Line Number: 324

Answer 1

You are hitting the limits of InnoDB. It might work to change your table to the MyISAM engine (backup before changing this), as the limits of MyISAM are larger compared to InnoDB.

Note that this can cause an performance hit as table locking acts different on MyISAM.

You can also replace all VARCHAR fields (with more than 10 characters) with a TEXT field. They are using less bytes and thus reducing the total row size.