search

craftcms / cms

Build bespoke content experiences with Craft.
https://craftcms.com
Other
3.28k stars 635 forks source link

[4.4.5]: Using "Field Limit" after creating new "Plain Text Field" -> Database Exception #12954

Closed mfell closed 1 year ago

mfell commented 1 year ago

What happened?

Description

Got a Database Exception when using "Field Limit" of a just created and saved new "Plain Text Field".

Database Exception – [yii\db\Exception](https://www.yiiframework.com/doc-2.0/yii-db-exception.html)
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
The SQL being executed was: ALTER TABLE `content` ADD `field_assetDownloadName_hdmffqxr` varchar(200)
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 65535. 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 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs
in /var/www/html/vendor/yiisoft/yii2/db/Command.php at line 1302

Steps to reproduce

  1. Create a new "Plain Text Field"
  2. Safe
  3. Set the option "Field Limit" (in my case 50)

Expected behavior

no Database Exception

Actual behavior

Database Exception – yii_db_Exception.pdf

Can send you config.json and DB, if needed.

Craft CMS version

Craft Pro 4.4.5

PHP version

8.1.13

Operating system and version

Linux 5.15.49-linuxkit (DDEV)

Database type and version

MySQL 5.7.38

Image driver and version

Imagick 3.7.0 (ImageMagick 6.9.11-60)

Installed plugins and versions

mfell commented 1 year ago

Here's a video

https://user-images.githubusercontent.com/6691773/226942985-0609536e-6788-4462-8c1a-2beb4de28c50.mov

brandonkelly commented 1 year ago

That error occurs when the content table has grown too large, and MySQL can’t allow any additional columns to be added.

Plain Text fields will choose their column type automatically by default, based on the Field Limit setting. 50 would result in varchar(200), which is actually more taxing on MySQL’s row size limit than text despite having a significantly smaller storage allocation, because text columns’ content gets stored on disk rather than directly within the table, internally. So if your content table was already right on the edge of the max row size, changing a text column to varchar(200) could push it over.

Plain Text fields do let you choose their column types, via their “Column Type” settings under “Advanced”. So as a temporary workaround you could go through and force some of your Plain Text fields to use text columns.

We are working on a long-term solution for Craft 5. I’ll keep this open and follow-up when that’s done.

mfell commented 1 year ago

Ok...if I understand correctly, the problem is the length of the content table and not the number of fields?

If so, sounds better to use a light switch for boolean and not a drop down field (null, yes, no) - like I do.

mfell commented 1 year ago

Just a suggestion / idea: The dropdown field (e.g.) has no setting "Advanced" and is a VARCHAR(255) in database.

In my case I use this field type mostly (99%) for setting classes and if not, this field is way too big for me.

Having the option to switch this field to TEXT would reduce the row size enormously in my projects.

brandonkelly commented 1 year ago

Ok...if I understand correctly, the problem is the length of the content table and not the number of fields?

Right :)

Having the option to switch this field to TEXT would reduce the row size enormously in my projects.

text columns can help with the row size, but also come with a significant performance hit, especially if you ever need to query against the column in an element query or entry condition.

It may make sense for Dropdown (and Radio Buttons) fields to use dynamically-sized columns though, e.g. varchar(3) if we know yes is the longest possible value. That would lead to a SQL error if the column already contains any data that’s longer than three characters, though (e.g. if you edit an existing Dropdown field and delete a preexisting option with a longer value). So we would need to couple that with a bit of cleanup code as well, to ensure all rows are set to one of the current field options, or null.

mfell commented 1 year ago

@brandonkelly Thank you so much, to put this topic on the roadmap!

Smart Dropdown/Radio Button column lengths

May I suggest to think about this for the checkbox field, too? For me this looks like an identical topic.

Bildschirmfoto 2023-04-01 um 10 34 10

Bildschirmfoto 2023-04-01 um 10 34 56

brandonkelly commented 1 year ago

Checkboxes and Multi-select fields already do use smart column sizes, based on how many bytes would be needed if all options were selected.

brandonkelly commented 1 year ago

Made the Dropdown/Radio Buttons changes for 4.5 (#13025) 🎉

mfell commented 1 year ago

Made the Dropdown/Radio Buttons changes for 4.5 (#13025) 🎉

GRRRRREEEEAT! 🎉

And thank you so much!🍻

brandonkelly commented 1 year ago

Craft 4.5.0 is out now with those changes.