Strange Issue with Upgrading from 3.2.4 to 4.1.0

[mikew]

Your software
My PHP version is : 7.4
My MySQL/MariaDB version is (delete as applicable): 10.4

Updating/Installing Errors

I am getting an error when finishing the upgrade,
the error message I am seeing is this:

[2021-12-10 15:55:30] mautic.ERROR: [UPGRADE ERROR] Exit code 1; ++ adding generated column generated_email_domain -> ALTER TABLE leads ADD generated_email_domain VARCHAR(255) AS (SUBSTRING(email, LOCATE("@", email) + 1)) COMMENT '(DC2Type:generated)'; ALTER TABLE leads ADD INDEX `generated_email_domain`(generated_email_domain) \ In AbstractMySQLDriver.php line 128: \ An exception occurred while executing 'ALTER TABLE leads ADD generated_emai l_domain VARCHAR(255) AS (SUBSTRING(email, LOCATE("@", email) + 1)) COMMENT '(DC2Type:generated)'; ALTER TABLE leads ADD INDEX `generated_email_domain`(generated_ email_domain)': \ 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 Exception.php line 18: \ 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 PDOConnection.php line 132: \ 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 \ doctrine:migrations:migrate [--write-sql [WRITE-SQL]] [--dry-run] [--query-time] [--allow-no-migration] [--all-or-nothing [ALL-OR-NOTHING]] [--configuration [CONFIGURATION]] [--db-configuration [DB-CONFIGURATION]] [--db DB] [--em EM] [--shard SHARD] [-h|--help] [-q|--quiet] [-v|vv|vvv|--verbose] [-V|--version] [--ansi] [--no-ansi] [-n|--no-interaction] [-e|--env ENV] [--no-debug] [--]  [] \ [] []

I am (delete as applicable): Updating
Upgrading/installing via (delete as applicable) : Command Line

These errors are showing in the installer :

These errors are showing in the Mautic log :

[2021-12-10 15:49:59] mautic.CRITICAL: Uncaught PHP Exception Doctrine\DBAL\Exception\InvalidFieldNameException: "An exception occurred while executing 'SELECT t0.id AS id_1, t0.label AS label_2, t0.show_label AS show_label_3, t0.alias AS alias_4, t0.type AS type_5, t0.is_custom AS is_custom_6, t0.custom_parameters AS custom_parameters_7, t0.default_value AS default_value_8, t0.is_required AS is_required_9, t0.validation_message AS validation_message_10, t0.help_message AS help_message_11, t0.field_order AS field_order_12, t0.properties AS properties_13, t0.validation AS validation_14, t0.parent_id AS parent_id_15, t0.conditions AS conditions_16, t0.label_attr AS label_attr_17, t0.input_attr AS input_attr_18, t0.container_attr AS container_attr_19, t0.lead_field AS lead_field_20, t0.save_result AS save_result_21, t0.is_auto_fill AS is_auto_fill_22, t0.show_when_value_exists AS show_when_value_exists_23, t0.show_after_x_submissions AS show_after_x_submissions_24, t0.always_display AS always_display_25, t0.form_id AS form_id_26 FROM form_fields t0 WHERE t0.form_id = ? ORDER BY t0.field_order ASC' with params [12]:  SQLSTATE[42S22]: Column not found: 1054 Unknown column 't0.parent_id' in 'field list'" at /var/www/mautic/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/AbstractMySQLDriver.php line 79 {"exception":"[object] (Doctrine\\DBAL\\Exception\\InvalidFieldNameException(code: 0): An exception occurred while executing 'SELECT t0.id AS id_1, t0.label AS label_2, t0.show_label AS show_label_3, t0.alias AS alias_4, t0.type AS type_5, t0.is_custom AS is_custom_6, t0.custom_parameters AS custom_parameters_7, t0.default_value AS default_value_8, t0.is_required AS is_required_9, t0.validation_message AS validation_message_10, t0.help_message AS help_message_11, t0.field_order AS field_order_12, t0.properties AS properties_13, t0.validation AS validation_14, t0.parent_id AS parent_id_15, t0.conditions AS conditions_16, t0.label_attr AS label_attr_17, t0.input_attr AS input_attr_18, t0.container_attr AS container_attr_19, t0.lead_field AS lead_field_20, t0.save_result AS save_result_21, t0.is_auto_fill AS is_auto_fill_22, t0.show_when_value_exists AS show_when_value_exists_23, t0.show_after_x_submissions AS show_after_x_submissions_24, t0.always_display AS always_display_25, t0.form_id AS form_id_26 FROM form_fields t0 WHERE t0.form_id = ? ORDER BY t0.field_order ASC' with params [12]:\n\nSQLSTATE[42S22]: Column not found: 1054 Unknown column 't0.parent_id' in 'field list' at /var/www/mautic/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/AbstractMySQLDriver.php:79, Doctrine\\DBAL\\Driver\\PDO\\Exception(code: 42S22): SQLSTATE[42S22]: Column not found: 1054 Unknown column 't0.parent_id' in 'field list' at /var/www/mautic/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDO/Exception.php:18, PDOException(code: 42S22): SQLSTATE[42S22]: Column not found: 1054 Unknown column 't0.parent_id' in 'field list' at /var/www/mautic/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOStatement.php:112)"} []
[2021-12-10 15:50:00] mautic.CRITICAL: Uncaught PHP Exception Doctrine\DBAL\Exception\InvalidFieldNameException: "An exception occurred while executing 'SELECT t0.id AS id_1, t0.label AS label_2, t0.show_label AS show_label_3, t0.alias AS alias_4, t0.type AS type_5, t0.is_custom AS is_custom_6, t0.custom_parameters AS custom_parameters_7, t0.default_value AS default_value_8, t0.is_required AS is_required_9, t0.validation_message AS validation_message_10, t0.help_message AS help_message_11, t0.field_order AS field_order_12, t0.properties AS properties_13, t0.validation AS validation_14, t0.parent_id AS parent_id_15, t0.conditions AS conditions_16, t0.label_attr AS label_attr_17, t0.input_attr AS input_attr_18, t0.container_attr AS container_attr_19, t0.lead_field AS lead_field_20, t0.save_result AS save_result_21, t0.is_auto_fill AS is_auto_fill_22, t0.show_when_value_exists AS show_when_value_exists_23, t0.show_after_x_submissions AS 
[2021-12-10 16:05:28] mautic.WARNING: Command `doctrine:migrations:migrate` exited with status code 1 [] []

Your problem
My problem is :
After upgrade system seems to be running correctly however I am unable to edit any contacts.

Steps I have tried to fix the problem :
Tried upgrading to mariadb
chown directory

[rcheesley]

Looks like the error is suggesting the row size is too large - the total size of all fields in the table is more than the limit, 65535, that’s why you are getting this error.

Do you have contacts with super long domain names in their emails?

Thinking out loud (without testing or checking), maybe use text instead of varchar for the generated_email_domain column?

[mikew]

Hi @rcheesley

Thanks for the hint. A few things:

1. I have looked through the DB and cannot find any column called generated_email_domain.
2. I went and ran a mysql query to extract maxlenght of all the columns, and the only one that I see is rather big is being provided by MakeWebBetter plugin. I went and deleted this custom field before doing the upgrade and same behaviour.
   This was my script:

for x in `cat leads_tables.csv `;do t=`echo $x | cut -d, -f1`; result=`mysql -u root mautic -sN -e "select max(length("$t")) from leads"`; echo $t, $result >> leads_column_max_size.csv; done

Here are the results:

id, 6
unsigned,
owner_id, 1
unsigned,
unsigned,
is_published, 1
date_added, 19
created_by, 1
created_by_user, 16
date_modified, 19
modified_by, 1
modified_by_user, 16
checked_out_by_user, 16
points, 1
last_active, 19
internal, 6
social_cache, 6
date_identified, 19
preferred_profile_image, 8
firstname, 40
lastname, 35
company, 16
email, 44
phone, 17
mobile, 32
city, 30
state, 35
zipcode, 10
timezone, 19
country, 22
data_source, 21
mwb_customer_group, 13
mwb_newsletter_subs, 2
mwb_customer_cart_id, 3
mwb_bill_add_line_1, 28
mwb_bill_city, 15
mwb_bill_state, 7
mwb_bill_country, 2
mwb_last_order_stat, 13
mwb_last_order_ff_stat, 13
mwb_last_order_num, 4
mwb_last_pay_method, 20
mwb_current_orders, 2
mwb_last_product, 36
mwb_last_products, 36
mwb_products, 99
mwb_product_types, 19
mwb_last_num_of_prod, 1
mwb_total_products_num, 2
mwb_prod_1_img_url, 88
mwb_prod_1_name, 39
mwb_prod_1_price, 5
mwb_prod_1_url, 71
mwb_prod_2_img_url, 88
mwb_prod_2_name, 39
mwb_prod_2_price, 5
mwb_prod_2_url, 68
mwb_prod_3_img_url, 88
mwb_prod_3_name, 39
mwb_prod_3_price, 5
mwb_prod_3_url, 68
mwb_total_val_of_orders, 6
mwb_avg_order_value, 13
mwb_total_orders, 2
mwb_first_order_val, 5
mwb_first_order_date, 10
mwb_last_order_val, 5
mwb_last_order_date, 10
mwb_avg_days_bt_orders, 13
mwb_acc_creation_date, 10
mwb_order_monetary, 4
mwb_order_frequency, 4
**mwb_abncart_prod_html, 12720**
mwb_abncart_prods_count, 1
mwb_abncart_total, 5
mwb_abncart_products, 54
mwb_abncart_url, 112
mwb_coupon_disc, 1
mwb_subs_order_num, 4
mwb_subs_parent_order_num, 4
mwb_subs_order_date, 10
mwb_subs_order_paid_date, 10
mwb_subs_next_pay_date, 10
mwb_abncart_stat, 3
mwb_abdn_coupon, 15
mwb_coupon_code, 3
mwb_subs_payment_type, 7
mwb_subs_order_status, 10
mwb_subs_bill_period, 5
mwb_subs_bill_interval, 1
mwb_subs_products, 4
mwb_order_recency, 4
mwb_last_skus, 4

Any other thoughts here as still getting the same error.

[2021-12-11 11:45:23] mautic.NOTICE: Doctrine\DBAL\Exception\DriverException: An exception occurred while executing 'ALTER TABLE leads ADD generated_email_domain VARCHAR(255) AS (SUBSTRING(email, LOCATE("@", email) + 1)) COMMENT '(DC2Type:generated)';             ALTER TABLE leads ADD INDEX `generated_email_domain`(generated_email_domain)':  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/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/AbstractMySQLDriver.php line 128 while running console command `doctrine:migrations:migrate` [] []
[2021-12-11 11:45:23] mautic.WARNING: Command `doctrine:migrations:migrate` exited with status code 1 [] []
[2021-12-11 11:45:23] mautic.WARNING: Command `mautic:update:apply` exited with status code 1 [] []
[2021-12-11 11:46:43] mautic.NOTICE: Doctrine\DBAL\Exception\DriverException: An exception occurred while executing 'ALTER TABLE leads ADD generated_email_domain VARCHAR(255) AS (SUBSTRING(email, LOCATE("@", email) + 1)) COMMENT '(DC2Type:generated)';             ALTER TABLE leads ADD INDEX `generated_email_domain`(generated_email_domain)':  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/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/AbstractMySQLDriver.php line 128 while running console command `doctrine:migrations:migrate` [] []
[2021-12-11 11:46:43] mautic.WARNING: Command `doctrine:migrations:migrate` exited with status code 1 [] []
[2021-12-11 11:46:43] mautic.ERROR: [UPGRADE ERROR] Exit code 1; ++ adding generated column generated_email_domain -> ALTER TABLE leads ADD generated_email_domain VARCHAR(255) AS (SUBSTRING(email, LOCATE("@", email) + 1)) COMMENT '(DC2Type:generated)'; ALTER TABLE leads ADD INDEX `generated_email_domain`(generated_email_domain) \ In AbstractMySQLDriver.php line 128: \ An exception occurred while executing 'ALTER TABLE leads ADD generated_emai l_domain VARCHAR(255) AS (SUBSTRING(email, LOCATE("@", email) + 1)) COMMENT '(DC2Type:generated)'; ALTER TABLE leads ADD INDEX `generated_email_domain`(generated_ email_domain)': \ 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 Exception.php line 18: \ 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 PDOConnection.php line 132: \ 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 \ doctrine:migrations:migrate [--write-sql [WRITE-SQL]] [--dry-run] [--query-time] [--allow-no-migration] [--all-or-nothing [ALL-OR-NOTHING]] [--configuration [CONFIGURATION]] [--db-configuration [DB-CONFIGURATION]] [--db DB] [--em EM] [--shard SHARD] [-h|--help] [-q|--quiet] [-v|vv|vvv|--verbose] [-V|--version] [--ansi] [--no-ansi] [-n|--no-interaction] [-e|--env ENV] [--no-debug] [--]  [] \ [] []
[2021-12-11 11:50:32] mautic.NOTICE: Doctrine\DBAL\Exception\DriverException: An exception occurred while executing 'ALTER TABLE leads ADD generated_email_domain VARCHAR(255) AS (SUBSTRING(email, LOCATE("@", email) + 1)) COMMENT '(DC2Type:generated)';             ALTER TABLE leads ADD INDEX `generated_email_domain`(generated_email_domain)':  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/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/AbstractMySQLDriver.php line 128 while running console command `doctrine:migrations:migrate` [] []
[2021-12-11 11:50:32] mautic.WARNING: Command `doctrine:migrations:migrate` exited with status code 1 [] []
[2021-12-11 11:50:32] mautic.ERROR: [UPGRADE ERROR] Exit code 1; ++ adding generated column generated_email_domain -> ALTER TABLE leads ADD generated_email_domain VARCHAR(255) AS (SUBSTRING(email, LOCATE("@", email) + 1)) COMMENT '(DC2Type:generated)'; ALTER TABLE leads ADD INDEX `generated_email_domain`(generated_email_domain) \ In AbstractMySQLDriver.php line 128: \ An exception occurred while executing 'ALTER TABLE leads ADD generated_emai l_domain VARCHAR(255) AS (SUBSTRING(email, LOCATE("@", email) + 1)) COMMENT '(DC2Type:generated)'; ALTER TABLE leads ADD INDEX `generated_email_domain`(generated_ email_domain)': \ 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 Exception.php line 18: \ 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 PDOConnection.php line 132: \ 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 \ doctrine:migrations:migrate [--write-sql [WRITE-SQL]] [--dry-run] [--query-time] [--allow-no-migration] [--all-or-nothing [ALL-OR-NOTHING]] [--configuration [CONFIGURATION]] [--db-configuration [DB-CONFIGURATION]] [--db DB] [--em EM] [--shard SHARD] [-h|--help] [-q|--quiet] [-v|vv|vvv|--verbose] [-V|--version] [--ansi] [--no-ansi] [-n|--no-interaction] [-e|--env ENV] [--no-debug] [--]  [] \ [] []

I went and had a look at the file vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/AbstractMySQLDriver.php on line 128 but no clues there.

[mikew]

@rcheesley @joeyk @ekke

Does anyone have any idea what is going on here ? First time I am experiencing something like this

[mikew]

I have now tried a few more tricks and nothing worked.

1. I tried to delete all the contacts to see if it was something with the length of an email address or name. This did not work
2. I tried to install 4.1.0 by itself and then import the old database - obviously this did not work either.
3. Tried to upgrade to 3.3.4 first and then 4.1 - still did not work.
4. I removed the two plugins that I have installed here: Multidomain and RSS feed - still did not work

[mikew]

So happy days. Thanks to two main factors:

1. @rcheesley initial suggestion and
2. I contacted a development house that often pastes here - I won’t mention any names and was quoted 40 hours to fix the problem which would equate to $720!!

So how did I fix it ?

First I did everything above, then I went and ran the following command:

sudo -u www-data php /var/www/mautic/bin/console doctrine:migration:migrate

Which provided me with a detailed output:

I then went and searched the mautic directory for where the code is calling this “generated_email_domain”

grep -R "generated_email" /var/www/mautic/*

And was provided with the file that is taking care of this call
/var/www/mautic/app/bundles/LeadBundle/EventListener/GeneratedColumnSubscriber.php: ‘generated_email_domain’

I then went and made a backup of the file GeneratedColumnSubscriber.php and then went into the file to edit it.
NOTE: I am not a developer, but I know how to read, and that’s all you really need to know

On Line 43 of this file is the problem (and this is where Ruth’s suggestion did the trick)

40         $emailDomain = new GeneratedColumn(
 41             'leads',
 42             'generated_email_domain',
 43             'VARCHAR(255)',
 44             'SUBSTRING(email, LOCATE("@", email) + 1)'

I went and changed VARCHAR to TEXT, saved the file and re-ran the doctrine migrate

sudo -u www-data php /var/www/mautic/bin/console doctrine:migration:migrate

And like magic it all worked well!!

All in all I think this exercise took about 4 hours or my time. It is proof of the strength of the community and the want for people to support and help each other!!

So a big thanks to @rcheesley for initial suggestion

[mkaaaay]

Another possible solution: we’ve seen similar messages and the solution for us was this:

1. globally SET GLOBAL innodb_strict_mode=OFF;
2. adjust the innodb Log size to 512mb at both db level and in the conf file

[mikew]

@mkaaaay

Thanks for the suggestions, I am happy to give it a try as I have a staging environment up.

Could you be a bit more specific with your instructions.

1. Would this mean inside the mysql command line to type: SET GLOBAL innodb_strict_mode=OFF;
2. To update /etc/mysql/my.cnf file or (I am running mariadb) /etc/mysql/mariadb.cnf ?

I am just writing here for any other less technical people that the mysql service would need to be restarted: sudo systemctl restart mysqld