# You have reached the limit of custom field allowed in your database **URL:** \ **Category:** Product Support\ **Tags:** community, discussion\ **Created:** [February 28, 2020, 9:34am UTC](https://forum.mautic.org/t/you-have-reached-the-limit-of-custom-field-allowed-in-your-database/13147 "2020-02-28T09:34:44Z")\ **Posts on this page:** 1\ **Showing post:** 2
**Author:** ![autoize](https://sea2.discourse-cdn.com/flex020/user_avatar/forum.mautic.org/autoize/32/105_2.png) [@autoize](https://forum.mautic.org/u/autoize)\ **Post date:** [February 29, 2020, 11:51pm UTC](https://forum.mautic.org/t/you-have-reached-the-limit-of-custom-field-allowed-in-your-database/13147/2 "2020-02-29T23:51:51Z")
> [@maemoon](#): > > You have reached the limit of custom field allowed Appears related to this Github issue. Mautic creates additional custom fields as **columns** in the `leads` table, and MySQL imposes a limit on how many columns or bytes of data there can be in a particular row. Wonder if upgrading to MySQL 8.0 from MySQL 5.7 would expand the number of columns possible. > > > What type of report is this: > > | Q | A > | ---| --- > | Bug report? | Y > | Featu…re request? | N > | Enhancement? | N > > \## Description: > When trying to update schema, I'm getting the following error. > > 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 > > I think the problem is when you create over a certain amount of custom fields, you go over row size limit. From what I've read, perhaps the leads table need to split after a certain number of fields to prevent this as your list of custom fields grow. > > I've tried unsuccessfully all the suggestions/recommendations: > https://drupal.stackexchange.com/questions/47908/pdoexception-sqlstate42000-syntax-error-or-access-violation > https://stackoverflow.com/questions/3927690/howto-clean-a-mysql-innodb-storage-engine/4056261#4056261 > https://stackoverflow.com/questions/34804962/sqlstate42000-syntax-error-or-access-violation-row-size-too-large > https://craftcms.stackexchange.com/questions/11480/internal-server-error-sqlstate42000-1118-row-size-too-large > > \## If a bug: > > | Q | A > | --- | --- > | Mautic version | 2.5.1 > | PHP version | 7.0.14 > > \### Steps to reproduce: > 1. Create a bunch of custom fields > 2. Try to do a schema update via the command line command. php app/console doctrine:schema:update --force > > \### Log errors: > > \_Please check for related errors in the latest log file in \[mautic root\]/app/log/ and/or the web server's logs and post them here. Be sure to remove sensitive information if applicable.\_ > > \[2017-01-06 16:44:40\] mautic.NOTICE: Doctrine\\DBAL\\Exception\\DriverException: An exception occurred while executing 'ALTER TABLE ######leads CHANGE website website LONGTEXT DEFAULT NULL, CHANGE casual\_name casual\_name VARCHAR(255) DEFAULT NULL, CHANGE sourcecode sourcecode VARCHAR(255) DEFAULT NULL, CHANGE alternate\_apn alternate\_apn VARCHAR(255) DEFAULT NULL, CHANGE assessment\_year assessment\_year VARCHAR(255) DEFAULT NULL, CHANGE bathrooms bathrooms VARCHAR(255) DEFAULT NULL, CHANGE bedrooms bedrooms VARCHAR(255) DEFAULT NULL, CHANGE bldg\_\_living\_area bldg\_\_living\_area VARCHAR(255) DEFAULT NULL, CHANGE census\_tract census\_tract VARCHAR(255) DEFAULT NULL, CHANGE county\_land\_use\_code county\_land\_use\_code VARCHAR(255) DEFAULT NULL, CHANGE county\_name county\_name VARCHAR(255) DEFAULT NULL, CHANGE days\_since\_mtg days\_since\_mtg VARCHAR(255) DEFAULT NULL, CHANGE fico fico VARCHAR(255) DEFAULT NULL, CHANGE first\_mortgage\_amount first\_mortgage\_amount VARCHAR(255) DEFAULT NULL, CHANGE first\_mortage\_ballon\_loan first\_mortage\_ballon\_loan VARCHAR(255) DEFAULT NULL, CHANGE first\_mortgage\_interest\_r first\_mortgage\_interest\_r VARCHAR(255) DEFAULT NULL, CHANGE first\_mortage\_junior\_pres first\_mortage\_junior\_pres VARCHAR(255) DEFAULT NULL, CHANGE first\_mortgage\_lender\_add first\_mortgage\_lender\_add VARCHAR(255) DEFAULT NULL, CHANGE first\_mortgage\_lender\_cit first\_mortgage\_lender\_cit VARCHAR(255) DEFAULT NULL, CHANGE first\_mortgage\_lender\_nam first\_mortgage\_lender\_nam VARCHAR(255) DEFAULT NULL, CHANGE first\_mortgage\_lender\_sta first\_mortgage\_lender\_sta VARCHAR(255) DEFAULT NULL, CHANGE first\_mortgage\_lender\_zip first\_mortgage\_lender\_zip VARCHAR(255) DEFAULT NULL, CHANGE first\_mortgage\_lender\_zip1 first\_mortgage\_lender\_zip1 VARCHAR(255) DEFAULT NULL, CHANGE first\_mortgage\_loan\_rate first\_mortgage\_loan\_rate VARCHAR(255) DEFAULT NULL, CHANGE first\_mortgage\_ltv first\_mortgage\_ltv VARCHAR(255) DEFAULT NULL, CHANGE first\_mortgge\_matured first\_mortgge\_matured VARCHAR(255) DEFAULT NULL, CHANGE first\_mortgage\_primary\_lo first\_mortgage\_primary\_lo VARCHAR(255) DEFAULT NULL, CHANGE first\_mortgage\_private\_pa first\_mortgage\_private\_pa VARCHAR(255) DEFAULT NULL, CHANGE first\_mortgage\_seller\_car first\_mortgage\_seller\_car VARCHAR(255) DEFAULT NULL, CHANGE first\_mortgage\_term first\_mortgage\_term VARCHAR(255) DEFAULT NULL, CHANGE first\_mortgage\_transactio first\_mortgage\_transactio VARCHAR(255) DEFAULT NULL, CHANGE loanintent loanintent VARCHAR(255) DEFAULT NULL, CHANGE foreclosed foreclosed VARCHAR(255) DEFAULT NULL, CHANGE va\_eligible va\_eligible VARCHAR(255) DEFAULT NULL, CHANGE past\_bk past\_bk VARCHAR(255) DEFAULT NULL, CHANGE homesetad\_exemption homesetad\_exemption VARCHAR(255) DEFAULT NULL, CHANGE improved improved VARCHAR(255) DEFAULT NULL, CHANGE improved\_value improved\_value VARCHAR(255) DEFAULT NULL, CHANGE junior\_1\_mtg\_amount junior\_1\_mtg\_amount VARCHAR(255) DEFAULT NULL, CHANGE junior\_1\_mtg\_ballon\_loan junior\_1\_mtg\_ballon\_loan VARCHAR(255) DEFAULT NULL, CHANGE junior\_1\_mtg\_credit\_line junior\_1\_mtg\_credit\_line VARCHAR(255) DEFAULT NULL, CHANGE junior\_1\_mtg\_equity\_loan junior\_1\_mtg\_equity\_loan VARCHAR(255) DEFAULT NULL, CHANGE junior\_1\_mtg\_interest\_rat junior\_1\_mtg\_interest\_rat VARCHAR(255) DEFAULT NULL, CHANGE junior\_1\_mtg\_lender\_addre junior\_1\_mtg\_lender\_addre VARCHAR(255) DEFAULT NULL, CHANGE junior\_1\_mtg\_lender\_city junior\_1\_mtg\_lender\_city VARCHAR(255) DEFAULT NULL, CHANGE junior\_1\_mtg\_lender\_name junior\_1\_mtg\_lender\_name VARCHAR(255) DEFAULT NULL, CHANGE junior\_1\_mtg\_lender\_state junior\_1\_mtg\_lender\_state VARCHAR(255) DEFAULT NULL, CHANGE junior\_1\_mtg\_lender\_zip\_c junior\_1\_mtg\_lender\_zip\_c VARCHAR(255) DEFAULT NULL, CHANGE junior\_1\_mtg\_lender\_\_4 junior\_1\_mtg\_lender\_\_4 VARCHAR(255) DEFAULT NULL, CHANGE junior\_1\_mtg\_loan\_rate\_ty junior\_1\_mtg\_loan\_rate\_ty VARCHAR(255) DEFAULT NULL, CHANGE junior\_1\_mtg\_matured junior\_1\_mtg\_matured VARCHAR(255) DEFAULT NULL, CHANGE junior\_1\_mtg\_maturity\_rat junior\_1\_mtg\_maturity\_rat VARCHAR(255) DEFAULT NULL, CHANGE junior\_1\_mtg\_primary\_loan junior\_1\_mtg\_primary\_loan VARCHAR(255) DEFAULT NULL, CHANGE junior\_1\_mtg\_private\_part junior\_1\_mtg\_private\_part VARCHAR(255) DEFAULT NULL, CHANGE junior\_1\_mtg\_seller\_carry junior\_1\_mtg\_seller\_carry VARCHAR(255) DEFAULT NULL, CHANGE junior\_1\_mtg\_term junior\_1\_mtg\_term VARCHAR(255) DEFAULT NULL, CHANGE junior\_1\_mtg\_transaction junior\_1\_mtg\_transaction VARCHAR(255) DEFAULT NULL, CHANGE land\_value land\_value VARCHAR(255) DEFAULT NULL, CHANGE last\_market\_sale\_price\_ty last\_market\_sale\_price\_ty VARCHAR(255) DEFAULT NULL, CHANGE level level VARCHAR(255) DEFAULT NULL, CHANGE lot\_acreage lot\_acreage VARCHAR(255) DEFAULT NULL, CHANGE lot\_area lot\_area VARCHAR(255) DEFAULT NULL, CHANGE lu1limit lu1limit VARCHAR(255) DEFAULT NULL, CHANGE mail\_carrier\_route mail\_carrier\_route VARCHAR(255) DEFAULT NULL, CHANGE most\_recent\_mortgage\_trad most\_recent\_mortgage\_trad VARCHAR(255) DEFAULT NULL, CHANGE mtg\_trigger\_days mtg\_trigger\_days VARCHAR(255) DEFAULT NULL, CHANGE mtg\_trigger\_flag mtg\_trigger\_flag VARCHAR(255) DEFAULT NULL, CHANGE mtg08 mtg08 VARCHAR(255) DEFAULT NULL, CHANGE mtg13 mtg13 VARCHAR(255) DEFAULT NULL, CHANGE mtg19 mtg19 VARCHAR(255) DEFAULT NULL, CHANGE order\_record\_id order\_record\_id VARCHAR(255) DEFAULT NULL, CHANGE owner\_occupied owner\_occupied VARCHAR(255) DEFAULT NULL, CHANGE owner\_transfer\_recording owner\_transfer\_recording VARCHAR(255) DEFAULT NULL, CHANGE owner\_transfer\_sale\_date owner\_transfer\_sale\_date VARCHAR(255) DEFAULT NULL, CHANGE owner\_transfer\_sale\_price owner\_transfer\_sale\_price VARCHAR(255) DEFAULT NULL, CHANGE parcel\_id parcel\_id VARCHAR(255) DEFAULT NULL, CHANGE phone\_dnc phone\_dnc VARCHAR(255) DEFAULT NULL, CHANGE pin\_code pin\_code VARCHAR(255) DEFAULT NULL, CHANGE pr\_26 pr\_26 VARCHAR(255) DEFAULT NULL, CHANGE pr\_84 pr\_84 VARCHAR(255) DEFAULT NULL, CHANGE prior\_market\_sale\_price prior\_market\_sale\_price VARCHAR(255) DEFAULT NULL, CHANGE property\_tax\_amount property\_tax\_amount VARCHAR(255) DEFAULT NULL, CHANGE property\_type property\_type VARCHAR(255) DEFAULT NULL, CHANGE state\_land\_use\_code state\_land\_use\_code VARCHAR(255) DEFAULT NULL, CHANGE swimming\_pool\_present swimming\_pool\_present VARCHAR(255) DEFAULT NULL, CHANGE total\_assessed\_value total\_assessed\_value VARCHAR(255) DEFAULT NULL, CHANGE year\_built year\_built VARCHAR(255) DEFAULT NULL, CHANGE do\_not\_call\_home do\_not\_call\_home VARCHAR(255) DEFAULT NULL, CHANGE do\_not\_call\_work do\_not\_call\_work VARCHAR(255) DEFAULT NULL, CHANGE loan\_officer loan\_officer VARCHAR(255) DEFAULT NULL, CHANGE subject\_property\_address subject\_property\_address VARCHAR(255) DEFAULT NULL, CHANGE subject\_property subject\_property VARCHAR(255) DEFAULT NULL, CHANGE subject\_property\_city subject\_property\_city VARCHAR(255) DEFAULT NULL, CHANGE subject\_property\_state subject\_property\_state VARCHAR(255) DEFAULT NULL, CHANGE subject\_property\_zip subject\_property\_zip VARCHAR(255) DEFAULT NULL, CHANGE coborrower\_first\_name coborrower\_first\_name VARCHAR(255) DEFAULT NULL, CHANGE coborrower\_last\_name coborrower\_last\_name VARCHAR(255) DEFAULT NULL, CHANGE borrower\_gender borrower\_gender VARCHAR(255) DEFAULT NULL, CHANGE coborrower\_gender coborrower\_gender VARCHAR(255) DEFAULT NULL, CHANGE coborrower\_email coborrower\_email VARCHAR(255) DEFAULT NULL, CHANGE agent\_first\_name agent\_first\_name VARCHAR(255) DEFAULT NULL, CHANGE agent\_last\_name agent\_last\_name VARCHAR(255) DEFAULT NULL, CHANGE agent\_company agent\_company VARCHAR(255) DEFAULT NULL, CHANGE agent\_address agent\_address VARCHAR(255) DEFAULT NULL, CHANGE agent\_city agent\_city VARCHAR(255) DEFAULT NULL, CHANGE agent\_state agent\_state VARCHAR(255) DEFAULT NULL, CHANGE agent\_zip agent\_zip VARCHAR(255) DEFAULT NULL, CHANGE agent\_phone agent\_phone VARCHAR(255) DEFAULT NULL, CHANGE agent\_cell agent\_cell VARCHAR(255) DEFAULT NULL, CHANGE agent\_email agent\_email VARCHAR(255) DEFAULT NULL, CHANGE guid guid VARCHAR(255) DEFAULT NULL, CHANGE zillow\_url zillow\_url VARCHAR(255) DEFAULT NULL, CHANGE nmlsid nmlsid VARCHAR(255) DEFAULT NULL': 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 (uncaught exception) at /var/www/mautic.######.com/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/AbstractMySQLDriver.php line 115 while running console command \`doctrine:schema:update\` \[\] \[\] > \[2017-01-06 16:44:45\] mautic.WARNING: Command \`doctrine:schema:update\` exited with status code 1 \[\] \[\] There is an **extended field plugin** that is mentioned in this linked issue, which may help for your particular use case. > > > | Q | A > | ---| --- > | Bug report? | > | Feature request? | ✓ > | Enhancement? |… ✓ > > \## Description: > > Today, when a contact field is created, Mautic does two things: > 1. It creates a entry in the \`lead\_fields\` table containing the metadata of that field > 2. It adds a columns to the \`leads\` table which will then contain the actual contents of that field > > The problem today is that MySQL tables are limited in width by the very nature of how RDBMSs work. This means that adding too custom fields, especially text fields which are stored as \`VARCHAR(255)\` results in problems between what Doctrine thinks should be in the database and the actual schema (see https://github.com/mautic/mautic/issues/3202#issuecomment-279751132 and https://github.com/mautic/mautic/issues/3202). > > Some possible quick fixes: > \- Limiting the number of custom fields the user can create > \- Store custom fields in \_narrower\_ data types such as \`TEXT\` instead of \`VARCHAR(255)\` > > Neither of these would adresse the real issue with the current structure, custom fields are limited. > This is especially problematic for CRM users who want to find \*\*all\*\* of their data in both their marketing automation solution and CRM solution. > > Therefore, I think the only way forward is to drastically restructure contact fields. There is already some structure in place with the \`lead\_fields\` table. I think what we would need to do would be to create a \`lead\_fields\_leads\_xref\` table which would have the following structure > > | Lead ID 🔑 | Lead Field ID :key: | Content | (maybe some metadata columns...) > | --- | --- | --- | --- | > | 42 (My contact ID) | 3 (First name field ID) | Maximilien | | > | ... | ... | ... | | > | 42 (Still my contact ID | 1337 (Salesforce opportunity closing date field ID) | 24/05/2017 | | > | ... | ... | ... | | > > This would allow (practically) infinite custom fields. > > @alanhartless, @matishaladiwala I would love to have your feedback on this issue! --- _[View the full topic](https://forum.mautic.org/t/you-have-reached-the-limit-of-custom-field-allowed-in-your-database/13147)._