Cannot Create New Custom Fields. SQLSTATE[42S22] Error

[mikew]

Your software
My Mautic version is: 4.2.1
My PHP version is: 7.4
My Database type and version is: mariadb 10.04

Your problem
My problem is:
When trying to add a new custom field I am getting an error

These errors are showing in the log:

An exception occurred while executing 'ALTER TABLE leads ADD new_field_name DATE DEFAULT NULL': SQLSTATE[42S22]: Column not found: 1054 Unknown column '`mautic`.`l`.`email`' in 'GENERATED ALWAYS'

Steps I have tried to fix the problem:
Researched the forum
Researched Google

Reaching out to someone who understands DB to help here.

[pierre_a]

Hello @mikew,

Considering your problem I think the easiest way is to open a bug issue on Github : Sign in to GitHub · GitHub

Pierre

[rcheesley]

Hi @mikew a couple of things to check:

• Is your schema up to date?
• Have you had any install/upgrade woes with this instance
• How many custom fields have you already got set up?

[mikew]

Hey @rcheesley

1. Yes
2. Yes
   3.Quite a few, but I think there is some actual DB issue after my last upgrade.

Even when I go directly into mysql and try to drop a column at the moment I am getting this error:

MariaDB [mautic]> alter table leads drop column mwb_order_recency;
ERROR 1054 (42S22): Unknown column '`mautic`.`l`.`email`' in 'GENERATED ALWAYS'

H E L P

[mikew]

WOW. Okay so this is what I did.

I think that the culprit of all these bad database issues is the generated_email_domain column that I have not idea who puts in there or who is using it, but I went and dropped this table, then I was able to add more custom fields and also drop other unwanted columns inside the mautic database.

@rcheesley - any idea what this column is, who is creating it and why ? and what happens if I drop it ?

[mikew]

@rcheesley I am just adding where I find this. I see that it is coming from the leadBundle:

app/bundles/LeadBundle/EventListener/GeneratedColumnSubscriber.php:            'generated_email_domain',
app/bundles/LeadBundle/EventListener/GeneratedColumnSubscriber.php:        $event->addChoice('lead', 'generated_email_domain', [
app/bundles/LeadBundle/EventListener/GeneratedColumnSubscriber.php:            'label'      => $this->translator->trans('mautic.email.segment.choice.generated_email_domain'),
app/bundles/LeadBundle/EventListener/GeneratedColumnSubscriber.php.orig:            'generated_email_domain',
app/bundles/LeadBundle/EventListener/GeneratedColumnSubscriber.php.orig:        $event->addChoice('lead', 'generated_email_domain', [
app/bundles/LeadBundle/EventListener/GeneratedColumnSubscriber.php.orig:            'label'      => $this->translator->trans('mautic.email.segment.choice.generated_email_domain'),
app/bundles/EmailBundle/Translations/en_US/messages.ini.orig:mautic.email.segment.choice.generated_email_domain="Email Domain"
app/bundles/EmailBundle/Translations/en_US/messages.ini:mautic.email.segment.choice.generated_email_domain="Email Domain"
var/cache/prod/translations/catalogue.en_US.NbP0Msw.php:    'mautic.email.segment.choice.generated_email_domain' => 'Email Domain',

My main question is what is this needed for and how badly will things going wrong if I drop this column ?
I have done it in a test environment and everything seems to be working.

If you can point me to the correct place I am happy to open an issue on GitHub.

Thanks,
Mike

[patrickeidemiller]

I have the same issue, so eagerly awaiting a solution.

[mikew]

Are you able to access your MySQL database ? If so I would suggest following my directions above - alter table leads drop column generated_email_domains (or something like that, not in front of computer).

After I did this everything continued working and I was able to create new custom fields.

By the way I would strongly suggest doing a dump of your database before doing this.
Mysqldump -u username database_name -pPassword | gzip > Mautic-db-backup-date.sql.gz

[rcheesley]

@escopecz any thoughts on this issue?

[escopecz]

Here is the PR adding the email domain field:

Email domain field

I don’t think it will break anything if you remove that column but I suggest to leave it there. It will be created again when you run migrations during next Mautic upgrade.

The generated field basically takes the email address of the contact in the “email” column and takes the domain name after the “@”. Then you can use that value in segment filters which is much faster than other methods. It’s MySql feature so Mautic does not have to care about keeping this field up to date. MySql does it for us.

Can you please confirm that you have the “email” column in the “leads” table? The error suggests that that column is missing.

[mikew]

HI @escopecz

thanks for the reply. Yes I definitely have the email column in the leads table.

[escopecz]

Do you get any output when you execute this command?:

bin/console doctrine:schema:update --dump-sql | grep generated

Also try if this will fix it:

bin/console doctrine:migration:migrate

If not try to delete the generated_email_domain column from the leads table and then run the migration command once more.

Do you use table prefix?

[mikew]

Hi @escopecz

I will try and get an instance up with the problem prevalent as I had already gone ahead and deleted these fields and run the commands you suggested and document results here.

I am not using table prefix

[awessel]

Hi @escopecz,

I am not mike but I ran into the same issue with the same error message but with a little older mautic version 4.1.2.

Steps I made to solve it:

• Drop Mautic Database
• Recreate it
• Create Schema
• Do the migrations
• Update the schema

All steps were done via console scripts. Error during creation of a custom field still occured.

Output of
bin/console doctrine:schema:update --dump-sql | grep generated
was nothing but the error code 1.

bin/console doctrine:migration:migrate has not fixed the issue as I did already before but I gave it a try anyway.

Solution for me was:
delete the generated_email_domain column from the leads table and then run the migration command once more as you suggested.

Thanks for the help

[awessel]

Hi again,

after using the above mentioned solution and using mautic afterwards I can say, that the solution is only temporarily. After a while I had to do “the solution” again.
Either a cron job does some changes or the mautic app code itself has some “healing” functionality.

Greetings, André

[mauticdeveloper]

After deleting the column. I was able to add custom field so I again added the deleted column by running ALTER TABLE leads ADD COLUMN generated_email_domain VARCHAR(255) AFTER fieldname; and it worked for me. Now I don’t need to delete column again to add custom fields.