You have reached the limit of custom field allowed in your database

autoize · February 29, 2020, 11:51pm

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.

github.com/mautic/mautic

Schema Update Failure With Large Number Of Custom Fields

opened 04:51PM - 06 Jan 17 UTC

closed 11:23AM - 28 Nov 17 UTC

MxyzptlkFishStix

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.

github.com/mautic/mautic

[Feature Req. / Enhancement] Restructure contact fields

opened 12:30PM - 24 May 17 UTC

closed 06:37PM - 25 Feb 18 UTC

MaxWebmecanik

feature

| 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!

Topic		Replies	Views
Mautic not allowing to add new custom fields Product Support community , development	0	610	December 22, 2020
Increase character limit for custom field Text Area in Mautic 2.X? Product Support	1	371	April 17, 2022
Custom Field Limits (and MySQL 64 Key limits) Product Support mautic-4	2	468	July 28, 2022
Cannot create custom fields Product Support	12	434	May 23, 2023
Strange SQL Error when trying to create new custom field Product Support	2	384	May 20, 2022

You have reached the limit of custom field allowed in your database

Related Topics