Your software
My Mautic version is: 4.3.1
My PHP version is: 7.4
My Database type and version is: MariaDB
Your problem
My problem is: When upgrading from 4.2.2 to 4.3.1, doctrine put back the fields fax, facebook, foursquare, instagram and skype which had been previously deleted. It then wanted to create indexes to support these fields, which violated the 64 key limit inherent in MySQL.
After the upgrade, when running bin/console doctrine:schema:update --dump-sql, I see that there is a command to adjust the length of a ton of fields per the PR here, which makes sense. But the same command wants to add back in several columns for fields we deleted from the leads table to get past the 64 key limit for MySQL.
ALTER TABLE hatcoma_leads ADD fax VARCHAR(191) DEFAULT NULL, ADD facebook VARCHAR(191) DEFAULT NULL, ADD foursquare VARCHAR(191) DEFAULT NULL, ADD instagram VARCHAR(191) DEFAULT NULL, ADD skype VARCHAR(191) DEFAULT NULL, CHANGE preferred_locale preferre d_locale VARCHAR(191) DEFAULT NULL, CHANGE website website VARCHAR(191) DEFAULT NULL, CHANGE linkedin linkedin VARCHAR(191) DEFAULT NULL, CHANGE twitter twitter VARCHAR(191) DEFAULT NULL, CHANGE contact_notes contact_notes VARCHAR(191) DEFAULT NULL, CHANGE phone_extension phone_extension VAR CHAR(191) DEFAULT NULL, CHANGE marketing_suspended_reaso marketing_suspended_reaso VARCHAR(191) DEFAULT NULL, CHANGE gdpr_consent_detail gdpr_consent_detail VARCHAR(191) DEFAULT NULL, CHANGE market_segment market_segment VARCHAR(191) DEFAULT NULL, CHANGE market_subsegment market_subsegment V ARCHAR(191) DEFAULT NULL, CHANGE persona persona VARCHAR(191) DEFAULT NULL, CHANGE home_address_1 home_address_1 VARCHAR(191) DEFAULT NULL, CHANGE home_state home_state VARCHAR(191) DEFAULT NULL, CHANGE home_zip_code home_zip_code VARCHAR(191) DEFAULT NULL, CHANGE home_country home_country V ARCHAR(191) DEFAULT NULL, CHANGE home_city home_city VARCHAR(191) DEFAULT NULL, CHANGE segment_preference_center segment_preference_center VARCHAR(191) DEFAULT NULL, CHANGE last_enrichment_result last_enrichment_result VARCHAR(191) DEFAULT NULL, CHANGE personal_email personal_email VARCHAR(1 91) DEFAULT NULL, CHANGE gdpr_cookie_optin_detail gdpr_cookie_optin_detail VARCHAR(191) DEFAULT NULL, CHANGE rep_company_name rep_company_name VARCHAR(191) DEFAULT NULL, CHANGE rep_phone rep_phone VARCHAR(191) DEFAULT NULL, CHANGE rep_email rep_email VARCHAR(191) DEFAULT NULL, CHANGE custome r_service_response customer_service_response VARCHAR(191) DEFAULT NULL, CHANGE product_area_of_interest product_area_of_interest VARCHAR(191) DEFAULT NULL, CHANGE last_webform_context last_webform_context VARCHAR(191) DEFAULT NULL, CHANGE last_request_type last_request_type VARCHAR(191) DEFAULT NULL, CHANGE website_region website_region VARCHAR(191) DEFAULT NULL;
What I find odd is that even if I run the SQL statements in the schema update, these columns that are re-added to the leads table don’t actually have entries in the lead_fields table and therefore don’t show up in the UI as being re-added. I also quickly run out of keys and get this error:
ERROR 1069 (42000): Too many keys specified; max 64 keys allowed
My biggest concern is that this is a ticking time bomb for anyone else who deleted any of the default fields to get around the 64 key limit because any attempts by a future upgrade to update the schema will fail trying to re-add the indexes for columns that shouldn’t exist because the fields are deleted in lead_fields.
Secondarily, am I working off old information and having my database out of sync with the ORM is not a major issue anymore? Do I need to worry about this or can I just re-drop the columns from the leads table and call it a day?
SIDE NOTE: I currently can’t successfully run bin/console doctrine:schema:update --force because of a separate issue with a nonexistent index.
This error shows at the command line when I try to manually run the SQL statements in --dump-sql: