Limit of row length in SQL database

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • Jakub Grufik
    Senior Member
    • Aug 2022
    • 361

    Limit of row length in SQL database

    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.

    Code:
    [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
    Active Community Member
    • Jun 2018
    • 569

    #2
    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

    Comment

    • Jakub Grufik
      Senior Member
      • Aug 2022
      • 361

      #3
      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.
      Attached Files

      Comment


      • dimyy
        dimyy commented
        Editing a comment
        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
        Jakub Grufik commented
        Editing a comment
        thanks a lot for the suggestions man
    • Kyle
      Senior Member
      • May 2020
      • 143

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

      Comment

      • shalmaxb
        Senior Member
        • Mar 2015
        • 1603

        #5
        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.

        Comment


        • esforim
          esforim commented
          Editing a comment
          lol 65k! That is one scary varchar field
      Working...