I am getting this error "Syntax error or access violation: 1118 Row size too large"

[rohit.sws]

Hi Friends,
I am getting below error

Espo.ERROR: Import: [42000] SQLSTATE[42000]: Syntax error or access violation: 1118 Row size too large (> 8126).
Changing some columns to TEXT or BLOB or
using ROW_FORMAT=DYNAMIC or ROW_FORMAT=COMPRESSED
may help. In current row format, BLOB prefix of 768 bytes is stored inline.

--------
I have made all the column to longtext in table even though I am getting above error.Can you suggest me what should I can do?


Table
-------

CREATE TABLE `occupation` (
`id` varchar(24) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
`name` varchar(1000) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
`deleted` tinyint(1) DEFAULT '0',
`description` longtext COLLATE utf8mb4_unicode_ci,
`created_at` datetime DEFAULT NULL,
`modified_at` datetime DEFAULT NULL,
`created_by_id` varchar(24) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
`modified_by_id` varchar(24) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
`assigned_user_id` varchar(24) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
`occupations_id` int(11) DEFAULT '0',
`code` text COLLATE utf8mb4_unicode_ci,
`group` text COLLATE utf8mb4_unicode_ci,
`totalemployment` double DEFAULT NULL,
`percentrelativestandarderror` double DEFAULT NULL,
`meanhourlywage` double DEFAULT NULL,
`mean_annual_wage` double DEFAULT NULL,
`percentrelativestandarderrormeanwage` double DEFAULT NULL,
`hourly10thpercentilewage` double DEFAULT NULL,
`hourly25thpercentilewage` double DEFAULT NULL,
`hourlymedianwage` double DEFAULT NULL,
`hourly75thpercentilewage` double DEFAULT NULL,
`hourly90thpercentilewage` double DEFAULT NULL,
`annual10thpercentilewage` double DEFAULT NULL,
`annual25thpercentilewage` double DEFAULT NULL,
`annualmedianwage` double DEFAULT NULL,
`annual90thpercentilewage` double DEFAULT NULL,
`annual` tinyint(1) NOT NULL DEFAULT '0',
`hourly` tinyint(1) NOT NULL DEFAULT '0',
`major_group` text COLLATE utf8mb4_unicode_ci,
`minor_group` text COLLATE utf8mb4_unicode_ci,
`broad_group` text COLLATE utf8mb4_unicode_ci,
`detailed_occupation` text COLLATE utf8mb4_unicode_ci,
`qfmedianpayhourlynote` longtext COLLATE utf8mb4_unicode_ci,
`moreinformationcitation` longtext COLLATE utf8mb4_unicode_ci,
`moreinformationsectionbody` longtext COLLATE utf8mb4_unicode_ci,
`moreinformationsectiontitle` longtext COLLATE utf8mb4_unicode_ci,
`similaroccupationssectionbody` longtext COLLATE utf8mb4_unicode_ci,
`similar_occupations_section_title` longtext COLLATE utf8mb4_unicode_ci,
`stateandareasectionbody` longtext COLLATE utf8mb4_unicode_ci,
`stateandareasectiontitle` longtext COLLATE utf8mb4_unicode_ci,
`joboutlooksectiondatatable` longtext COLLATE utf8mb4_unicode_ci,
`joboutlooksectionchart` longtext COLLATE utf8mb4_unicode_ci,
`joboutlooksectionbody` longtext COLLATE utf8mb4_unicode_ci,
`paysectioncharthtml` longtext COLLATE utf8mb4_unicode_ci,
`paysectionbody` longtext COLLATE utf8mb4_unicode_ci,
`paysectiontitle` longtext COLLATE utf8mb4_unicode_ci,
`howtobecomeonesectionbody` longtext COLLATE utf8mb4_unicode_ci,
`howtobecomeonesectionimage` longtext COLLATE utf8mb4_unicode_ci,
`howtobecomeonesectiontitle` longtext COLLATE utf8mb4_unicode_ci,
`workenvironmentsectionbody` longtext COLLATE utf8mb4_unicode_ci,
`workenvironmentsectionimage` longtext COLLATE utf8mb4_unicode_ci,
`workenvironmentsectiontitle` longtext COLLATE utf8mb4_unicode_ci,
`whattheydosectionbody` longtext COLLATE utf8mb4_unicode_ci,
`whattheydosectionimage` longtext COLLATE utf8mb4_unicode_ci,
`whattheydosectiontitle` longtext COLLATE utf8mb4_unicode_ci,
`summarymoreinformation` longtext COLLATE utf8mb4_unicode_ci,
`summarysimilaroccupations` longtext COLLATE utf8mb4_unicode_ci,
`summarystateandarea` longtext COLLATE utf8mb4_unicode_ci,
`summaryoutlook` longtext COLLATE utf8mb4_unicode_ci,
`summarypay` longtext COLLATE utf8mb4_unicode_ci,
`summaryhowtobecomeone` longtext COLLATE utf8mb4_unicode_ci,
`summaryworkenvironment` longtext COLLATE utf8mb4_unicode_ci,
`summarywhattheydo` longtext COLLATE utf8mb4_unicode_ci,
`image` longtext COLLATE utf8mb4_unicode_ci,
`qfemploymentoutlookrange` longtext COLLATE utf8mb4_unicode_ci,
`qfemploymentoutlookdescription` longtext COLLATE utf8mb4_unicode_ci,
`qfemploymentoutlook` longtext COLLATE utf8mb4_unicode_ci,
`qfnumberofjobshelp` longtext COLLATE utf8mb4_unicode_ci,
`qfnumberofjobsrange` longtext COLLATE utf8mb4_unicode_ci,
`qfnumberofjobsvalue` longtext COLLATE utf8mb4_unicode_ci,
`qfonthejobtraininghelp` longtext COLLATE utf8mb4_unicode_ci,
`qfonthejobtrainingvalue` longtext COLLATE utf8mb4_unicode_ci,
`qfworkexperiencehelp` longtext COLLATE utf8mb4_unicode_ci,
`qfworkexperiencevalue` text COLLATE utf8mb4_unicode_ci,
`qfentryleveleducationhelp` longtext COLLATE utf8mb4_unicode_ci,
`qfentryleveleducationvalue` longtext COLLATE utf8mb4_unicode_ci,
`qfmedianpayhourlyvalue` double DEFAULT NULL,
`qfmedianpayhourlyhelp` longtext COLLATE utf8mb4_unicode_ci,
`qfmedianpayannualhelp` longtext COLLATE utf8mb4_unicode_ci,
`qfmedianpayannualvalue` double DEFAULT NULL,
`videolink` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`occupationnameshortsingular` longtext COLLATE utf8mb4_unicode_ci,
`occupationnameshortplural` longtext COLLATE utf8mb4_unicode_ci,
`occupationnamefull` longtext COLLATE utf8mb4_unicode_ci,
`occupationcode` longtext COLLATE utf8mb4_unicode_ci,
`annual75thpercentilewage` double DEFAULT NULL,
`main_id` longtext COLLATE utf8mb4_unicode_ci,
`parentcode` int(11) DEFAULT NULL,
`minorgroup` longtext COLLATE utf8mb4_unicode_ci,
`detailedoccupation` longtext COLLATE utf8mb4_unicode_ci,
`title` longtext COLLATE utf8mb4_unicode_ci,
`qfemploymentopeningshelp` longtext COLLATE utf8mb4_unicode_ci,
`qfemploymentoutlookhelp` longtext COLLATE utf8mb4_unicode_ci,
`qfemploymentopeningsvalue` longtext COLLATE utf8mb4_unicode_ci,
`qfmedianpayannualrange` longtext COLLATE utf8mb4_unicode_ci,
PRIMARY KEY (`id`),
KEY `IDX_CREATED_BY_ID` (`created_by_id`),
KEY `IDX_MODIFIED_BY_ID` (`modified_by_id`),
KEY `IDX_ASSIGNED_USER_ID` (`assigned_user_id`),
KEY `IDX_NAME` (`name`(255),`deleted`),
KEY `IDX_ASSIGNED_USER` (`assigned_user_id`,`deleted`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ROW_FORMAT=DYNAMIC KEY_BLOCK_SIZE=8;

--------------

[tanya]

Hello,
this is not related with Espo, but with your mysql server. One of answers https://stackoverflow.com/questions/...-columns-to-te , don't know if it helps

[khopper]

I recently got this error also. Is it possible from within Espo to control the length of the columns created in the MySQL DB?
From what I can tell Espo just tells the DB to use all default settings. All of my varchar fields are 255 in length even though most of them I would not need more than a length of 50.

[khopper]

Even If I manually adjust the field length and go to make a change in espo it tries to resize the columns back to the default of 255.

[2019-02-27 14:44:11] Espo.INFO: SCHEMA, Execute Query: ALTER TABLE `account` CHANGE `industry` industry VARCHAR(255) DEFAULT '' COLLATE utf8mb4_unicode_ci, CHANGE `billing_address_street` billing_address_street VARCHAR(255) DEFAULT NULL COLLATE utf8mb4_unicode_ci, CHANGE `billing_address_city` billing_address_city VARCHAR(255) DEFAULT NULL COLLATE utf8mb4_unicode_ci, CHANGE `billing_address_state` billing_address_state VARCHAR(255) DEFAULT NULL COLLATE utf8mb4_unicode_ci, CHANGE `billing_address_country` billing_address_country VARCHAR(255) DEFAULT NULL COLLATE utf8mb4_unicode_ci, CHANGE `billing_address_postal_code` billing_address_postal_code VARCHAR(255) DEFAULT NULL COLLATE utf8mb4_unicode_ci, CHANGE `shipping_address_street` shipping_address_street VARCHAR(255) DEFAULT NULL COLLATE utf8mb4_unicode_ci, CHANGE `shipping_address_city` shipping_address_city VARCHAR(255) DEFAULT NULL COLLATE utf8mb4_unicode_ci, CHANGE `shipping_address_state` shipping_address_state VARCHAR(255) DEFAULT NULL COLLATE utf8mb4_unicode_ci, CHANGE `shipping_address_country` shipping_address_country VARCHAR(255) DEFAULT NULL COLLATE utf8mb4_unicode_ci, CHANGE `shipping_address_postal_code` shipping_address_postal_code VARCHAR(255) DEFAULT NULL COLLATE utf8mb4_unicode_ci, CHANGE `salesrep` salesrep VARCHAR(255) DEFAULT NULL COLLATE utf8mb4_unicode_ci, CHANGE `ipaddresses` ipaddresses VARCHAR(255) DEFAULT '' COLLATE utf8mb4_unicode_ci, CHANGE `staticordynamic` staticordynamic VARCHAR(255) DEFAULT 'Dynamic' COLLATE utf8mb4_unicode_ci, CHANGE `orderstatus` orderstatus VARCHAR(255) DEFAULT 'New Order' COLLATE utf8mb4_unicode_ci, CHANGE `product` product VARCHAR(255) DEFAULT '' COLLATE utf8mb4_unicode_ci, CHANGE `customer_name` customer_name VARCHAR(255) DEFAULT NULL COLLATE utf8mb4_unicode_ci, CHANGE `internetmigration` internetmigration VARCHAR(255) DEFAULT '' COLLATE utf8mb4_unicode_ci, CHANGE `mobility` mobility VARCHAR(255) DEFAULT '' COLLATE utf8mb4_unicode_ci, CHANGE `speedsold` speedsold VARCHAR(255) DEFAULT '' COLLATE utf8mb4_unicode_ci, CHANGE `uversedsl` uversedsl VARCHAR(255) DEFAULT '' COLLATE utf8mb4_unicode_ci, CHANGE `cclines` cclines VARCHAR(255) DEFAULT NULL COLLATE utf8mb4_unicode_ci, CHANGE `phonesystemrebate` phonesystemrebate VARCHAR(255) DEFAULT NULL COLLATE utf8mb4_unicode_ci, CHANGE `phonesystemv2e` phonesystemv2e VARCHAR(255) DEFAULT NULL COLLATE utf8mb4_unicode_ci [] []

[khopper]

tanya yuri

any update on this? If Espo could actually control the Column width in the DB back end we would run into less of these problems!

[khopper]

[2019-05-31 17:45:34] Espo.DEBUG: Rebuild Database for entities: [Account] with dependent entities: [Account, EmailAddress, PhoneNumber, Vendor, Document, Attachment, User, Lead, GoogleContactsPair, VoipMessage, Team, InboundEmail, Email, EmailAccount, EmailFolder, EmailFilter, EmailTemplate, EmailTemplateCategory, Note, Portal, Report, ReportCategory, TargetList, Contact, Invoice, InvoiceItem, Product, ProductCategory, ProductBrand, SalesOrder, SalesOrderItem, Quote, QuoteItem, Opportunity, OpportunityItem, Campaign, Template, MassEmail, EmailQueueItem, CampaignTrackingUrl, CampaignLogRecord, Task, Call, GoogleCalendar, Meeting, GoogleCalendarUser, GoogleCalendarRecurrentEvent, ShippingProvider, Tax, Case, KnowledgeBaseArticle, KnowledgeBaseCategory, PortalRole, Role, Preferences, DocumentFolder] [] []
[2019-05-31 17:45:34] Espo.DEBUG: DBAL: Table [entity_team] exists. [] []
[2019-05-31 17:45:34] Espo.DEBUG: Schema\Converter - End: building schema [] []
[2019-05-31 17:45:34] Espo.INFO: SCHEMA, Execute Query: ALTER TABLE `account` CHANGE `ponum` ponum VARCHAR(255) DEFAULT NULL COLLATE utf8mb4_unicode_ci [] []
[2019-05-31 17:45:34] Espo.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 /var/www/espocrm/vendor/doctrine/dbal/lib/Doctrine/DBAL/Connection.php:699 Stack trace: #0 /var/www/espocrm/vendor/doctrine/dbal/lib/Doctrine/DBAL/Connection.php(699): PDO->query('ALTER TABLE `ac...') #1 /var/www/espocrm/application/Espo/Core/Utils/Database/Schema/Schema.php(196): Doctrine\DBAL\Connection->executeQuery('ALTER TABLE `ac...') #2 /var/www/espocrm/application/Espo/Core/DataManager.php(95): Espo\Core\Utils\Database\Schema\Schema->rebuild('Account') #3 /var/www/espocrm/application/Espo/Core/DataManager.php(62): Espo\Core\DataManager->rebuildDatabase('Account') #4 /var/www/espocrm/application/Espo/Controllers/FieldManager.php(71): Espo\Core\DataManager->rebuild('Account') #5 /var/www/espocrm/application/Espo/Core/ControllerManager.php(143): Espo\Controllers\FieldManager->postActionCreate(Array, Object(stdClass), Object(Slim\Http\Request), Object(Slim\Http\Response)) #6 /var/www/espocrm/application/Espo/Core/ControllerManager.php(159): Espo\Core\ControllerManager->processRequest(Object(Espo\Controllers\FieldManag er), 'create', Array, Object(stdClass), Object(Slim\Http\Request), Object(Slim\Http\Response)) #7 /var/www/espocrm/application/Espo/Core/Application.php(296): Espo\Core\ControllerManager->process('FieldManager', 'create', Array, '{"type":"varcha...', Object(Slim\Http\Request), Object(Slim\Http\Response)) #8 [internal function]: Espo\Core\Application->Espo\Core\{closure}() #9 /var/www/espocrm/vendor/slim/slim/Slim/Slim.php(1210): call_user_func_array(Object(Closure), Array) #10 /var/www/espocrm/vendor/slim/slim/Slim/Slim.php(1356): Slim\Slim->applyHook('slim.before.dis...') #11 /var/www/espocrm/vendor/slim/slim/Slim/Middleware/Flash.php(85): Slim\Slim->call() #12 /var/www/espocrm/vendor/slim/slim/Slim/Middleware/MethodOverride.php(92): Slim\Middleware\Flash->call() #13 /var/www/espocrm/application/Espo/Core/Utils/Api/Auth.php(138): Slim\Middleware\MethodOverride->call() #14 /var/www/espocrm/application/Espo/Core/Utils/Api/Slim.php(125): Espo\Core\Utils\Api\Auth->call() #15 /var/www/espocrm/application/Espo/Core/Application.php(93): Espo\Core\Utils\Api\Slim->run() #16 /var/www/espocrm/api/v1/index.php(33): Espo\Core\Application->run() #17 {main} Next Doctrine\DBAL\DBALException: An exception occurred while executing 'ALTER TABLE `account` CHANGE `ponum` ponum VARCHAR(255) DEFAULT NULL COLLATE utf8mb4_unicode_ci': 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/espocrm/vendor/doctrine/dbal/lib/Doctrine/DBAL/DBALException.php:91 Stack trace: #0 /var/www/espocrm/vendor/doctrine/dbal/lib/Doctrine/DBAL/Connection.php(702): Doctrine\DBAL\DBALException::driverExceptionDuring Query(Object(PDOException), 'ALTER TABLE `ac...', Array) #1 /var/www/espocrm/application/Espo/Core/Utils/Database/Schema/Schema.php(196): Doctrine\DBAL\Connection->executeQuery('ALTER TABLE `ac...') #2 /var/www/espocrm/application/Espo/Core/DataManager.php(95): Espo\Core\Utils\Database\Schema\Schema->rebuild('Account') #3 /var/www/espocrm/application/Espo/Core/DataManager.php(62): Espo\Core\DataManager->rebuildDatabase('Account') #4 /var/www/espocrm/application/Espo/Controllers/FieldManager.php(71): Espo\Core\DataManager->rebuild('Account') #5 /var/www/espocrm/application/Espo/Core/ControllerManager.php(143): Espo\Controllers\FieldManager->postActionCreate(Array, Object(stdClass), Object(Slim\Http\Request), Object(Slim\Http\Response)) #6 /var/www/espocrm/application/Espo/Core/ControllerManager.php(159): Espo\Core\ControllerManager->processRequest(Object(Espo\Controllers\FieldManag er), 'create', Array, Object(stdClass), Object(Slim\Http\Request), Object(Slim\Http\Response)) #7 /var/www/espocrm/application/Espo/Core/Application.php(296): Espo\Core\ControllerManager->process('FieldManager', 'create', Array, '{"type":"varcha...', Object(Slim\Http\Request), Object(Slim\Http\Response)) #8 [internal function]: Espo\Core\Application->Espo\Core\{closure}() #9 /var/www/espocrm/vendor/slim/slim/Slim/Slim.php(1210): call_user_func_array(Object(Closure), Array) #10 /var/www/espocrm/vendor/slim/slim/Slim/Slim.php(1356): Slim\Slim->applyHook('slim.before.dis...') #11 /var/www/espocrm/vendor/slim/slim/Slim/Middleware/Flash.php(85): Slim\Slim->call() #12 /var/www/espocrm/vendor/slim/slim/Slim/Middleware/MethodOverride.php(92): Slim\Middleware\Flash->call() #13 /var/www/espocrm/application/Espo/Core/Utils/Api/Auth.php(138): Slim\Middleware\MethodOverride->call() #14 /var/www/espocrm/application/Espo/Core/Utils/Api/Slim.php(125): Espo\Core\Utils\Api... [] []
[2019-05-31 17:45:35] Espo.DEBUG: API (500) Error while rebuilding database. See log file for details.; POST /espocrm/api/v1/Admin/fieldManager/Account; Input data: {"type":"varchar","required":false,"trim":true,"dy namicLogicVisible":null,"dynamicLogicRequired":nul l,"dynamicLogicReadOnly":null,"name":"ponum","labe l":"PO Number","tooltipText":null,"isPersonalData":false, "inlineEditDisabled":false,"default":null,"maxLeng th":null,"options":[],"audited":false,"readOnly":false,"tooltip":false} ; Route pattern: /Admin/fieldManager/:scope; Route params: Array ( [scope] => Account ) [] []
[2019-05-31 17:45:35] Espo.ERROR: (500) Error while rebuilding database. See log file for details.; POST /espocrm/api/v1/Admin/fieldManager/Account [] []

[criffoh]

You can modify entity def replacing "varchar" type to "text" and rebuild:

...
"type": "text",
"rows": 1,
"trim": true,
...