SQL exception when saving entry with field larger than 64KB

[PaulBaileyAtChan]

Description of the problem
EE has a long-standing problem with field content larger than 64KB. I understand that this relates to SQL column widths, and is in theory possible to work around by increasing those widths, but this is not a reasonable expectation for most users.

There are two issues here. The first is the existence of a limit. While most people will likely never hit the 64KB limit, there are real situations in which the limit might be exceeded. The following link is to an EE forum discussion of several years ago after I hit the 64KB limit with a real use-case. Note also a recent post to that thread, in which another user describes losing data after unknowingly hitting the 64KB limit with real content.

https://expressionengine.com/forums/topic/250764/better-handling-of-64kb-content-fields

In the context of Medium-like long-form writing, and short fiction, with a reasonable amount of markup, it's not at all unlikely that 64KB will be exceeded for a single content field. EE should be able to handle this.

The second issue relates to how EE handles exceeding an existing limit. The current limit is undocumented, and no native warnings are provided that the limit has been exceeded. In the environment described below, saving an entry with a content field larger than 64KB results in an SQL exception. Note also that in the EE forum discussion linked earlier, a user describes content being truncated with no error/warning.

Anything which has the potential to cause the loss of user data should be treated seriously. As a minimum, the 64KB limit should be documented, and a warning should be provided, to avoid potential loss of data. Ideally, EE should seamlessly handle data larger than this.

How To Reproduce
Create/save an entry with a content field larger than 64KB.

Error Messages
SQLSTATE[22001]: String data, right truncated: 1406 Data too long for column

#0 ee/legacy/database/drivers/mysqli/mysqli_driver.php(112): CI_DB_mysqli_connection->query('UPDATE `exp_cha...')
#1 ee/legacy/database/DB_driver.php(270): CI_DB_mysqli_driver->_execute('UPDATE `exp_cha...')
#2 ee/legacy/database/DB_driver.php(180): CI_DB_driver->simple_query('UPDATE `exp_cha...')
#3 ee/legacy/database/DB_active_rec.php(1477): CI_DB_driver->query('UPDATE `exp_cha...')
#4 ee/EllisLab/ExpressionEngine/Service/Model/Query/Update.php(129): CI_DB_active_record->update('channel_data')
#5 ee/EllisLab/ExpressionEngine/Service/Model/Query/Update.php(99): EllisLab\ExpressionEngine\Service\Model\Query\Update->actOnGateway(Object(EllisLab\ExpressionEngine\Model\Channel\Gateway\ChannelDataGateway), Object(EllisLab\ExpressionEngine\Model\Channel\ChannelEntry))
#6 ee/EllisLab/ExpressionEngine/Service/Model/Query/Update.php(48): EllisLab\ExpressionEngine\Service\Model\Query\Update->doWork(Object(EllisLab\ExpressionEngine\Model\Channel\ChannelEntry))
#7 ee/EllisLab/ExpressionEngine/Service/Model/DataStore.php(282): EllisLab\ExpressionEngine\Service\Model\Query\Update->run()
#8 ee/EllisLab/ExpressionEngine/Service/Model/DataStore.php(248): EllisLab\ExpressionEngine\Service\Model\DataStore->runQuery('Update', Object(EllisLab\ExpressionEngine\Service\Model\Query\Builder))
#9 ee/EllisLab/ExpressionEngine/Service/Model/Query/Builder.php(71): EllisLab\ExpressionEngine\Service\Model\DataStore->updateQuery(Object(EllisLab\ExpressionEngine\Service\Model\Query\Builder))
#10 ee/EllisLab/ExpressionEngine/Service/Model/Model.php(366): EllisLab\ExpressionEngine\Service\Model\Query\Builder->update()
#11 ee/EllisLab/ExpressionEngine/Model/Content/ContentModel.php(225): EllisLab\ExpressionEngine\Service\Model\Model->save()
#12 ee/EllisLab/ExpressionEngine/Controller/Publish/AbstractPublish.php(376): EllisLab\ExpressionEngine\Model\Content\ContentModel->save()
#13 ee/EllisLab/ExpressionEngine/Controller/Publish/Edit.php(527): EllisLab\ExpressionEngine\Controller\Publish\AbstractPublish->saveEntryAndRedirect(Object(EllisLab\ExpressionEngine\Model\Channel\ChannelEntry))
#14 [internal function]: EllisLab\ExpressionEngine\Controller\Publish\Edit->entry('7336')
#15 ee/EllisLab/ExpressionEngine/Core/Core.php(241): call_user_func_array(Array, Array)
#16 ee/EllisLab/ExpressionEngine/Core/Core.php(110): EllisLab\ExpressionEngine\Core\Core->runController(Array)
#17 ee/EllisLab/ExpressionEngine/Boot/boot.php(151): EllisLab\ExpressionEngine\Core\Core->run(Object(EllisLab\ExpressionEngine\Core\Request))
#18 index.php(152): require_once('...')
#18 index.php(152): require_once('...')

Environment Details:

• Version: 5.3.2
• PHP Version: 7.0.33-0
• MySQL Version: 5.7.30
• OS: ubuntu 16.04.15
• Web Server: Apache 2

[TomJaeger]

Thanks for posting this issue. We discussed this yesterday after in came up in slack. The following is our current plan to move forward.

1. In EE 5.4 (and 6) we'll be adding the ability to change the DB storage type of fields to be TEXT, MEDIUMTEXT, or LONGTEXT
2. In EE 6 we're also adding an additional JS check on the publish / edit screen for content length on appropriate native fields and will warn the user accordingly.