Limit of row length in SQL database

[Jakub Grufik]

Hello guys,

I am trying to create new fields in the Opportunity and I am getting an error that we probably reached maximum row length for SQL database.

I deleted a lot of unused fields in opportunity but I am still able to see those columns in the sql table.

Could you please help me how to clean database of unused field that have been already deleted in the app? I remember that I tried to delete it from the sql table manually once but somehow the columns reappeared in the table later (probably cached or something)

Thanks a lot.

[2023-04-13 07:06:08] ALERT: Rebuild database fault: 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 /data/7/5/7575340d-2d72-4955-9959-98ec6389749c/crmsys.sk/sub/esmero/vendor/doctrine/dbal/src/Driver/PDO/Connection.php:72 Stack trace: #0 /data/7/5/7575340d-2d72-4955-9959-98ec6389749c/crmsys.sk/sub/esmero/vendor/doctrine/dbal/src/Driver/PDO/Connection.php(72): PDO->query('ALTER TABLE opp...') #1 /data/7/5/7575340d-2d72-4955-9959-98ec6389749c/crmsys.sk/sub/esmero/vendor/doctrine/dbal/src/Connection.php(1049): Doctrine\DBAL\Driver\PDO\Connection->query('ALTER TABLE opp...') #2 /data/7/5/7575340d-2d72-4955-9959-98ec6389749c/crmsys.sk/sub/esmero/application/Espo/Core/Utils/Database/Schema/Schema.php(218): Doctrine\DBAL\Connection->executeQuery('ALTER TABLE opp...') #3 /data/7/5/7575340d-2d72-4955-9959-98ec6389749c/crmsys.sk/sub/esmero/application/Espo/Core/Utils/Database/Schema/SchemaProxy.php(58): Espo\Core\Utils\Database\Schema\Schema->rebuild() #4 /data/7/5/7575340d-2d72-4955-9959-98ec6389749c/crmsys.sk/sub/esmero/application/Espo/Core/DataManager.php(145): Espo\Core\Utils\Database\Schema\SchemaProxy->rebuild(Array) #5 /data/7/5/7575340d-2d72-4955-9959-98ec6389749c/crmsys.sk/sub/esmero/application/Espo/Core/DataManager.php(110): Espo\Core\DataManager->rebuildDatabase(Array) #6 /data/7/5/7575340d-2d72-4955-9959-98ec6389749c/crmsys.sk/sub/esmero/application/Espo/Controllers/FieldManager.php(114): Espo\Core\DataManager->rebuild(Array) #7 /data/7/5/7575340d-2d72-4955-9959-98ec6389749c/crmsys.sk/sub/esmero/application/Espo/Core/Api/ActionProcessor.php(88): Espo\Controllers\FieldManager->postActionCreate(Object(Espo\Core\Api\RequestWrapper), Object(Espo\Core\Api\ResponseWrapper)) #8 /data/7/5/7575340d-2d72-4955-9959-98ec6389749c/crmsys.sk/sub/esmero/application/Espo/Core/Api/RequestProcessor.php(124): Espo\Core\Api\ActionProcessor->process('FieldManager', 'create', Object(Espo\Core\Api\RequestWrapper), Object(Espo\Core\Api\ResponseWrapper)) #9 /data/7/5/7575340d-2d72-4955-9959-98ec6389749c/crmsys.sk/sub/esmero/application/Espo/Core/Api/RequestProcessor.php(95): Espo\Core\Api\RequestProcessor->proceed(Object(Espo\Core\Api\RequestWrapper), Object(Espo\Core\Api\ResponseWrapper)) #10 /data/7/5/7575340d-2d72-4955-9959-98ec6389749c/crmsys.sk/sub/esmero/application/Espo/Core/Api/RequestProcessor.php(62): Espo\Core\Api\RequestProcessor->processInternal(Object(Espo\Core\Api\Route), Object(Espo\Core\Api\RequestWrapper), Object(Espo\Core\Api\ResponseWrapper)) #11 /data/7/5/7575340d-2d72-4955-9959-98ec6389749c/crmsys.sk/sub/esmero/application/Espo/Core/Api/Starter.php(86): Espo\Core\Api\RequestProcessor->process(Object(Espo\Core\Api\Route), Object(Espo\Core\Api\RequestWrapper), Object(Espo\Core\Api\ResponseWrapper)) #12 /data/7/5/7575340d-2d72-4955-9959-98ec6389749c/crmsys.sk/sub/esmero/vendor/slim/slim/Slim/Handlers/Strategies/RequestResponse.php(43): Espo\Core\Api\Starter->Espo\Core\Api\{closure}(Object(Slim\Psr7\Request), Object(Slim\Psr7\Response), Array) #13 /data/7/5/7575340d-2d72-4955-9959-98ec6389749c/crmsys.sk/sub/esmero/vendor/slim/slim/Slim/Routing/Route.php(384): Slim\Handlers\Strategies\RequestResponse->__invoke(Object(Closure), Object(Slim\Psr7\Request), Object(Slim\Psr7\Response), Array) #14 /data/7/5/7575340d-2d72-4955-9959-98ec6389749c/crmsys.sk/sub/esmero/vendor/slim/slim/Slim/MiddlewareDispatcher.php(81): Slim\Routing\Route->handle(Object(Slim\Psr7\Request)) #15 /data/7/5/7575340d-2d72-4955-9959-98ec6389749c/crmsys.sk/sub/esmero/vendor/slim/slim/Slim/MiddlewareDispatcher.php(81): Slim\MiddlewareDispatcher->handle(Object(Slim\Psr7\Request)) #16 /data/7/5/7575340d-2d72-4955-9959-98ec6389749c/crmsys.sk/sub/esmero/vendor/slim/slim/Slim/Routing/Route.php(341): Slim\MiddlewareDispatcher->handle(Object(Slim\Psr7\Request)) #17 /data/7/5/7575340d-2d72-4955-9959-98ec6389749c/crmsys.sk/sub/esmero/vendor/slim/slim/Slim/Routing/RouteRunner.php(84): Slim\Routing\Route->run(Object(Slim\Psr7\Request)) #18 /data/7/5/7575340d-2d72-4955-9959-98ec6389749c/crmsys.sk/sub/esmero/vendor/slim/slim/Slim/Middleware/RoutingMiddleware.php(59): Slim\Routing\RouteRunner->handle(Object(Slim\Psr7\Request)) #19 /data/7/5/7575340d-2d72-4955-9959-98ec6389749c/crmsys.sk/sub/esmero/vendor/slim/slim/Slim/MiddlewareDispatcher.php(147): Slim\Middleware\RoutingMiddleware->process(Object(Slim\Psr7\Request), Object(Slim\Routing\RouteRunner)) #20 /data/7/5/7575340d-2d72-4955-9959-98ec6389749c/crmsys.sk/sub/esmero/vendor/slim/slim/Slim/Middleware/ErrorMiddleware.php(107): Psr\Http\Server\RequestHandlerInterface@anonymous->handle(Object(Slim\Psr7\Request)) #21 /data/7/5/7575340d-2d72-4955-9959-98ec6389749c/crmsys.sk/sub/esmero/vendor/slim/slim/Slim/MiddlewareDispatcher.php(147): Slim\Middleware\ErrorMiddleware->process(Object(Slim\Psr7\Request), Object(Psr\Http\Serv... [] []
[2023-04-13 07:06:08] ERROR: (500) Error while rebuilding database. See log file for details.; POST /Admin/fieldManager/Opportunity; line: 119, file: /data/7/5/7575340d-2d72-4955-9959-98ec6389749c/crmsys.sk/sub/esmero/application/Espo/Controllers/FieldManager.php [] []​

[dimyy]

in 7.4 hard rebuild procedure to help

in other versions, analyze string variables and set limits + shorten fields to these limits through the database

[Jakub Grufik]

Hello dimyy thanks for the help.

Do you mean that I should set the max length for every text field in the entity manager and then set the same limit in the column of the table in SQL? I tried to delete one column in the table manually couple minutes ago and now I am able to create one new field which is good. But I would prefer the system to trim the database automatically if I delete some field in the app. Or somehow proceed manually with mentioned "Hard rebuild" would be enough as well.

[dimyy]

Yes. I mean max length.
If you upgrade to 7.4.* you can use https://docs.espocrm.com/administration/commands/ (php command.php rebuild --hard)
If you don't upgrade you must manually trim the length of the fields in the database.

[Jakub Grufik]

thanks a lot for the suggestions man

[Kyle]

Postgres has much larger limits that can be tuned, currently only experimental but will be helpful when production ready for issues like this.

[shalmaxb]

The field-length issue is caused by MySQL (as well as MariaDB). The best solution is to change as many textfields (255) as possible to text (65000). To have them appear like textfields in the detail view, adjust the height.

[espcrm]

lol 65k! That is one scary varchar field