1

I have a very large entry type in craft with quite a few fields. I've been updating a single record on a regular basis but upon adding a few more paragraphs of rich text in a field, I received this error when trying to save:

Internal Server Error

CDbCommand failed to execute the SQL statement: SQLSTATE[42000]: Syntax error or access violation: 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.

Is there some other solution beyond what the error is suggesting? Not sure what I'm missing. Any thoughts would be appreciated.

Thanks!

UPDATE 10/1

I was able to get the existing table in my Craft instance updated to the barracuda file format with row_format=DYNAMIC. Updated the global MySQL params as well:

+--------------------------+-----------+
| Variable_name            | Value     |
+--------------------------+-----------+
| innodb_file_format       | Barracuda |
| innodb_file_format_check | ON        |
| innodb_file_format_max   | Barracuda |
| innodb_file_per_table    | ON        |
+--------------------------+-----------+

The problem now is with new matrix fields: the newly created tables are not being set to the row_format=DYNAMIC. Is there somewhere in Craft where I need to update this? Or is there somewhere else I need to update this?

2 Answers 2

3

You're running into a MySQL/InnoDB server limitation that commonly occurs with a significant number of columns in a single table.

Fortunately, there are several documented workarounds for it:

https://stackoverflow.com/questions/15585602/change-limit-for-mysql-row-size-too-large

https://www.percona.com/blog/2011/04/07/innodb-row-size-limitation/

https://dba.stackexchange.com/questions/6598/innodb-create-table-error-row-size-too-large

From the Craft side, another workaround would be to re-think your information architecture. Currently, most fields in Craft have their content stored in the craft_content table in the database, so when you have a lot of textual fields on the site, you'll see the error you're getting. The exception to this rule is Matrix fields, which get their own table where their content is stored.

If you can move any of those fields into Matrix fields where they make sense, you'll take them out of the craft_content table helping mitigate the issue.

4
  • Appreciate the quick answer Brad. I'll take a look at these and see what the best option is for my situation. BTW: Been a lurker on these forums for the last year+. Don't know what this community would do without you!
    – jooosh
    Sep 15, 2015 at 21:55
  • So I was able to get the existing table in my Craft instance updated to the barracuda file format with row_format=DYNAMIC. Updated the global MySQL params as well: innodb_file_format = Barracuda, innodb_file_format_check = ON, innodb_file_format_max = Barracuda, innodb_file_per_table = ON. The problem now is with new matrix fields, that create new tables, are not being set to the row_format=DYNAMIC. Is there somewhere in Craft where I can set this? Thanks
    – jooosh
    Oct 1, 2015 at 23:32
  • I think I missed the innodb_default_row_format param. Will confirm later and update my answer.
    – jooosh
    Oct 2, 2015 at 0:10
  • innodb_default_row_format was introduced in MySQL 5.7.9. I'm running an earlier version. Looks like setting a default is not an option unless I upgrade.
    – jooosh
    Oct 2, 2015 at 4:08
2

I know this is an old post, but another area to consider aside from information architecture/field structure is you have a lot of Plain Text fields but haven't set a max length. I'm not sure if this is more optimised in Craft 3, but for a site that has been originally developed for Craft 2, creating a Plain Text field without a max length means it will be stored as TEXT in MySQL and if you're lax with setting max lengths you could trigger rowsize issues because you have a lot of Plain Text fields using the TEXT data type when they most likely don't need to.

If you set a max length on a Plain Text field it will the be converted to varchar, which can help this problem. Typically a lot of Plain Text fields can be limited to specific max values, especially one's that are linked to fixed data from an API which has known lengths. You do however need to audit your existing field content to ensure you don't set max lengths that are lower than any existing data otherwise you'll truncate it. Bad times.

One area you might need to be aware of is multi byte awareness when fields become varchar. In Craft 3, you do get warned when a field and it's type is actually not large enough to store the existing data + multi byte.

Definitely do not use innodb_strict_mode=0 though, you are just masking the problem. Ultimately, it is going to be down to your field/data architecture as mentioned. Although changing that shouldn't be done without analysis and testing before modifying existing fields and such.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.