Database error - content table maximum rows exceeded

[jakepm]

Description

Receiving the following error when attempting to create a new field.

Seems the content table is full?

I'm seeing 5891 rows in total. The install has a lot of fields, most of which are translatable across 3 (multi) sites.

Database Exception – yii\db\Exception
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 8126. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs
The SQL being executed was: ALTER TABLE content ADD field_landingPageButtonALink varchar(255)
Error Info: Array
(
[0] => 42000
[1] => 1118
[2] => Row size too large. The maximum row size for the used table type, not counting BLOBs, is 8126. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs
)
↵
Caused by: 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 8126. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs
in /{path-removed}/vendor/yiisoft/yii2/db/Command.php at line 1299

Steps to reproduce

1. Create new field
2. Save (submit event gives DB error)

Additional info

• Craft version: 3.6.11.2
• PHP version: 7.4.12
• Database driver & version: MySQL 5.7.32
• Plugins & versions:

[jakepm]

I can see this is to do with the row size of the content table so not specifically related to the field I'm trying to create. Updated the title and description.

[brandonkelly]

We are working on a solution to this for Craft 4 (#1524). In the meantime, if you email a database backup and Composer files over to support@craftcms.com we may be able to offer some suggestions on how to work around the issue.

[jakepm]

@brandonkelly - I resolved this by deleting some fields that were redundant.

However, this same issue is now preventing me from doing a 'reapply project config' on the staging site after deploy, as the auto sync was not successful. We changed a lot of config stuff and I need to deploy this before we can move on to a rebuild, which will include halving the number of fields we currently use.

To clarify, after the auto sync failed, I rebuilt the entire project config from dev and am now trying to apply it to staging.

Do you know of any workarounds I can use?

I can tell the client we have to wait until the rebuild is done, but there are some features they were hoping to get live beforehand. Hopefully the reduction in fields will solve the issue for the next update?

FYI the error message is a little confusing as the count it provides is far less than the limit...
2021-04-01 15:37:37 [-][1][-][error][yii\db\Exception] 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 8126. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs in /{path-removed}/vendor/yiisoft/yii2/db/Command.php:1299

[brandonkelly]

If there were any added fields, Craft is probably trying to add those first and getting stuck, before it gets around to removing the deleted fields. Again if you write into support@craftcms.com we should be able to help you; or you could try again using the staging DB backup as a starting point locally, and only remove the unneeded fields first, deploy that everywhere, then add the new ones.

[jakepm]

Thanks Brandon, that makes sense and will definitely help us move forward. Needless to say that this was bad field management on our part in the first place. Also apologies for trying to continue a support thread via the GitHub. support@craftcms.com is indeed one of the best things about Craft :)

[jakepm]

@brandonkelly FYI, your workaround of manually deleting a bunch of fields on the target install, before running project sync, worked a treat. We now have a clear route to take the rebuild live once it's done, so thank you very much for that.

[brandonkelly]

Awesome!

[brandonkelly]

Row size limits are no longer an issue in Craft 5.

[rfay]

We're considering a change to DDEV that would allow monster rows and would appreciate comments. I know that Craft 5 has a different approach, but of course lots of people won't be there yet.

We'd appreciate comments on

• build: experimentally change default innodb_page_size to 32768 for Craft CMS monster rows with too many columns ddev/ddev#6549
• https://github.com/orgs/ddev/discussions/6543