Matrix field hits the table row size limit.

[iliaivanov]

Description

In the current project, we are heavily using matrix field which contains a number of block type having a decent number of fields of different kinds. Lately, by adding new block types and fields, mysql started to throw an error (with around ~200 in the table):

PDOException: SQLSTATE[42000]: Syntax error or access violation: 1118 Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs in /app/vendor/yiisoft/yii2/db/Command.php:842

It doesn't look like edge case with matrix fields and actually, some other Craft users might face the same issue. I found some existing posts in stack exchange and stack overflow about kinda similar problem but suggested solution there (make changes in mysql server configuration or table engine) is impossible to implement due to different impediments with infrastructure and other things. We discussed this issue with colleagues and suggested as an option to create a custom field type, based on matrix field, which will rely on multiple separated tables instead of multiple numbers of fields in a single table (like fields collections in Drupal).

Can you suggest any ideas how to solve this issue? What do you think about the new field type idea? In case we will implement new field type, can it be included into Craft core?

Steps to reproduce

1. Create entry matrix field.
2. Add a lot of block type (~20) with different types of fields (~10-15 per block type).

Additional info

• Craft version: 3.0.0-beta.28 and older
• PHP version: 7.1
• Database driver & version: Mysql

[brandonkelly]

If you have any Plain Text fields within your Matrix field that could have a Max Length of <= 255 characters, then go into their settings and set the Max Length. That will convert the field’s column to VARCHAR instead of TEXT, drastically reducing their affect on MySQL row size limitations.

[iliaivanov]

Thanks a lot! I'll try it out!

[iliaivanov]

@brandonkelly I tried it out but got the same error when added new text fields (with varchar row type). Btw besides simple plain text fields, this matrix fields has tables, rich text and other field types. So even this fix would work it'll be a temporary solution until this field will grow more and hit the limit again.

[brandonkelly]

You updated existing Plain Text fields first right? Besides that, you could consider migrating your Table fields to Super Table, which stores its content in separate DB tables, so will cut down on the TEXT columns.

[brandonkelly]

(Worth noting that this is a big reason we added PostgreSQL support in Craft 3, where this is generally less of a problem.)

[iliaivanov]

@brandonkelly answering your questions: "You updated existing Plain Text fields first right?" - Yes, sure. "Besides that, you could consider migrating your Table fields to Super Table" - This is an option too, but we still might hit this limit again in the feature by adding new fields to the matrix. We have pretty big matrix field in order to allow content manager to give content managers flexibility to create pages with different components and layouts (every matrix block is a separate component).

Regarding PostgreSQL. Do you basically mean, that if we'll migrate to PostgreSQL, so we wouldn't have this row size problem? Is there any disadvantages of such migration?

[brandonkelly]

PostgreSQL has limits too, but they’re more liberal (1.6 TB vs. 65.535 KB). Only disadvantage is that there’s no direct migration path for existing Craft 2 sites, and utilities like Feed Me haven't been ported yet. So you’d need to write a script that helped you migrate all the content. But that might be easier than writing new field types (especially in the long run).

[angrybrad]

@iliaivanov you can also make sure that InnoDB is using the Barracuda file format and turn ROW_FORMAT=COMPRESSED on. More reading here: https://dba.stackexchange.com/questions/6598/innodb-create-table-error-row-size-too-large/6599#6599 and https://dba.stackexchange.com/questions/22441/error-1118-row-size-too-large-utf8-innodb/22463#22463

[angrybrad]

@brandonkelly "row" is confusing in the error message, since it's referring to row length of a database page (which is used in calculating the maximum length of an index), not a literal row of data in a table, which is probably where the 1.6TB reference comes from in PostgreSQL. The maximum length for a value in a B-tree index is one third of the size of a buffer page... by default floor(8192/3) = 2730 bytes, which is actually smaller than MySQL. But PostgreSQL has other index types (GIN, TSearch) to help get around the issue.

[iliaivanov]

@takobell Thank you for the suggestion. I changed row format to compressed and table accepted more rows, but then still got the same error. As far as I understand, even row format is a temporary solution?

[iliaivanov]

@brandonkelly @takobell varchar make things actually worth. It allocates 255*3B (in case of UTF8 and default column length of 255) comparing to 9 to 12B used by TEXT. https://dev.mysql.com/doc/refman/5.7/en/column-count-limit.html

The internal representation of a MySQL table has a maximum row size limit of 65,535 bytes, even if the storage engine is capable of supporting larger rows. BLOB and TEXT columns only contribute 9 to 12 bytes toward the row size limit because their contents are stored separately from the rest of the row.

I changed all possible text fields to TEXT, but I can't field type (mysql column type) for the dropdown, multi-select fields for instance. They are varchar by default.

Some questions:

• Can you add Advanced settings for the other field type, so different column types can be selected if needed?
• Character Limit can also affect field length in database. Atm Plain text field with length limit set to 10 will have varchar(255) column in database.
• Is there any other good possibility to avoid "row size limit" without changing DB schema? How difficult will it be to implement kinda matrix field, but with separate tables for every field or let's say tables with fields grouped by block type?

[iliaivanov]

Btw can the same story happen with the content table in case of the big amount of regular fields?

[iliaivanov]

Further testing

As I tested a bit more impact of changing column type for fields extending from BaseOptionsField abstract class and also ROW_FORMAT setting for the matrix content tables, I still got the same error anyway with an increased number of fields in matrix block types. So with MySql database, changing row format or play around with content column type is a temporary solution. But still can be an option for some users.

In our company project case, this issue is blocking us from adding new components to the matrix field. In a case when MySql is the only option for a database server, it is a pretty critical issue in my opinion.

Possible solution

As a possible solution, I had an idea to restructure existing Matrix field database tables structure or create a new field with database structure shown below. The custom field (at diagrams I called it field collection) is pretty much the same as matrix field, but content table(s) structure is different.

One additional content table

In this case, the content table has reference to the field collection set (matrix block type) and holds set's field name and it's value. This is not so flexible in a sense of content column type but will eliminate row size problem.

Multiple content tables for each field in set

This structure is more complicated and based on separate tables for each field from the field collection set (matrix block type). Pivot table contains text references to fields each set has. Field name also represents field's content table name.

This structure will be a bit slower due to the joins need to be done to get set's content. But from the other hand, every set's content can be requested independently and/or lazy loaded or requested via a separated request from UI when selected.

Conclusion

In my opinion, this might a good solution in order to avoid row size limit and make matrix field more scalable and bulletproof. In order to plan our solution for the current issue, can you please comment on the following questions:

• Is it possible that Matrix field DB structure will be changed in the near future?
• Is proposed solution looks appropriate in a sense of Craft architecture?
• In case we create this new custom field (with a bit different UI apparently) can it be included in the core?

[brandonkelly]

Currently this wouldn't be possible, because element queries only allow joining only a single “content” table per query.

We do have some drastic DB structure changes planned (see #1524), but these are deep changes with huge impact over the system, so not something we’ll be able to do for 3.x.

[iliaivanov]

Yeah, I see it now. So there is no solution besides the changing DB engine for us I guess or to somehow restructure matrix field?

[brandonkelly]

Another possibility might be to switch to the Neo field type, which lets you use global fields, rather than defining new fields for each block type. It will be more taking on your main content table, but you find yourself duplicating the same fields across multiple block types, it might be a better solution for you.

[narration-sd]

That might really do the trick, @brandonkelly, if I understand the actual problem here better today. Which is what I originally thought it was; just misunderstood for a while @bradbell 's comment on row size factors.

I'm going to take down the late-night imagination in Slack#contribute, then, as it's not so apropos.

It's just possible that noSql or rdb+ hierarchical features could be interesting to an overall redesign suggested in your #1524 or Matrix inception, though. I do note that MySql 8 has added recursive CTEs, so that part isn't going to be available only with Postgres.

The other glimmer keeps being about UX to handle a basket of Matrix block choices like @iliaivanov 's nicely and informatively. No doubt also for futures, but could help many content builders

[iliaivanov]

@brandonkelly this plugin should perfectly serve our needs, great suggestion! Unfortunately, it's not ported to the Craft 3 yet...

[narration-sd]

@iliaivanov Well, there is a beginning of some stage for Craft 3. Unfortunately it hasn't been updated since June. It carries a warning against use in 'projects'.

I tried it, and at the present beta version of Craft 3, it will install, but doesn't sem to want to show up in the list of possible Field Types.

If you want to experiment, here's the way to load it:

 `composer require benjamminf/craft-neo`

This will put it in your /vendor folder and configure autoload, so that Craft can see it.

It might be best to let Benjamin know your interest, to understand the reasons you need it to validate for your project,, or possibly to work on some of the development yourself.

Having done a large plugin among others, I'm not sure it should be so difficult to port, though there may be some reason. He's made at least a structural beginning, so you can ask -- or if he might have another version available that's not so visible yet that he'd be willing to let you try.

He's @benf on Slack, or you can look at his Github page with some contact details: https://github.com/benjamminf . Wish you both well.