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

**URL:** <https://forum.mautic.org/t/cannot-create-new-custom-fields-sqlstate-42s22-error/23961>\
**Category:** Product Support\
**Created:** [May 3, 2022, 11:25am UTC](https://forum.mautic.org/t/cannot-create-new-custom-fields-sqlstate-42s22-error/23961 "2022-05-03T11:25:11Z")\
**Posts on this page:** 16\
**Page:** 1

<div class="post-metadata">

**Author:** ![mikew](https://sea2.discourse-cdn.com/flex020/user_avatar/forum.mautic.org/mikew/32/5156_2.png) [@mikew](https://forum.mautic.org/u/mikew)\
**Post date:** [May 3, 2022, 11:25am UTC](https://forum.mautic.org/t/cannot-create-new-custom-fields-sqlstate-42s22-error/23961/1 "2022-05-03T11:25:11Z")

</div>

**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:

```auto
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.

---

<div class="post-metadata">

**Author:** ![pierre\_a](https://sea2.discourse-cdn.com/flex020/user_avatar/forum.mautic.org/pierre_a/32/7031_2.png) [@pierre\_a](https://forum.mautic.org/u/pierre_a)\
**Post date:** [May 3, 2022, 2:15pm UTC](https://forum.mautic.org/t/cannot-create-new-custom-fields-sqlstate-42s22-error/23961/2 "2022-05-03T14:15:29Z")

</div>

Hello @mikew,

Considering your problem I think the easiest way is to open a bug issue on Github : [Sign in to GitHub · GitHub](https://github.com/mautic/mautic/issues/new/choose)

Pierre

---

<div class="post-metadata">

**Author:** ![silavapi](https://sea2.discourse-cdn.com/flex020/user_avatar/forum.mautic.org/silavapi/32/7424_2.png) [@silavapi](https://forum.mautic.org/u/silavapi)\
**Post date:** [May 3, 2022, 2:26pm UTC](https://forum.mautic.org/t/cannot-create-new-custom-fields-sqlstate-42s22-error/23961/3 "2022-05-03T14:26:23Z")

</div>

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?

---

<div class="post-metadata">

**Author:** ![mikew](https://sea2.discourse-cdn.com/flex020/user_avatar/forum.mautic.org/mikew/32/5156_2.png) [@mikew](https://forum.mautic.org/u/mikew)\
**Post date:** [May 3, 2022, 5:43pm UTC](https://forum.mautic.org/t/cannot-create-new-custom-fields-sqlstate-42s22-error/23961/4 "2022-05-03T17:43:33Z")

</div>

Hey @silavapi

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:

```auto
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

---

<div class="post-metadata">

**Author:** ![mikew](https://sea2.discourse-cdn.com/flex020/user_avatar/forum.mautic.org/mikew/32/5156_2.png) [@mikew](https://forum.mautic.org/u/mikew)\
**Post date:** [May 3, 2022, 5:47pm UTC](https://forum.mautic.org/t/cannot-create-new-custom-fields-sqlstate-42s22-error/23961/5 "2022-05-03T17:47:17Z")

</div>

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.

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

---

<div class="post-metadata">

**Author:** ![mikew](https://sea2.discourse-cdn.com/flex020/user_avatar/forum.mautic.org/mikew/32/5156_2.png) [@mikew](https://forum.mautic.org/u/mikew)\
**Post date:** [May 8, 2022, 12:18pm UTC](https://forum.mautic.org/t/cannot-create-new-custom-fields-sqlstate-42s22-error/23961/6 "2022-05-08T12:18:41Z")

</div>

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

```auto
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

---

<div class="post-metadata">

**Author:** ![patrickeidemiller](https://sea2.discourse-cdn.com/flex020/user_avatar/forum.mautic.org/patrickeidemiller/32/511_2.png) [@patrickeidemiller](https://forum.mautic.org/u/patrickeidemiller)\
**Post date:** [May 8, 2022, 5:04pm UTC](https://forum.mautic.org/t/cannot-create-new-custom-fields-sqlstate-42s22-error/23961/7 "2022-05-08T17:04:30Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![mikew](https://sea2.discourse-cdn.com/flex020/user_avatar/forum.mautic.org/mikew/32/5156_2.png) [@mikew](https://forum.mautic.org/u/mikew)\
**Post date:** [May 8, 2022, 5:18pm UTC](https://forum.mautic.org/t/cannot-create-new-custom-fields-sqlstate-42s22-error/23961/8 "2022-05-08T17:18:52Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![silavapi](https://sea2.discourse-cdn.com/flex020/user_avatar/forum.mautic.org/silavapi/32/7424_2.png) [@silavapi](https://forum.mautic.org/u/silavapi)\
**Post date:** [May 9, 2022, 9:43am UTC](https://forum.mautic.org/t/cannot-create-new-custom-fields-sqlstate-42s22-error/23961/9 "2022-05-09T09:43:29Z")

</div>

@escopecz any thoughts on this issue?

---

<div class="post-metadata">

**Author:** ![escopecz](https://sea2.discourse-cdn.com/flex020/user_avatar/forum.mautic.org/escopecz/32/370_2.png) [@escopecz](https://forum.mautic.org/u/escopecz)\
**Post date:** [May 9, 2022, 12:38pm UTC](https://forum.mautic.org/t/cannot-create-new-custom-fields-sqlstate-42s22-error/23961/10 "2022-05-09T12:38:10Z")

</div>

Here is the PR adding the email domain field:

> <https://github.com/mautic/mautic/pull/10583>
>
> \<!-- ## Which branch should I use for my PR?
> 
> Assuming that:
> 
> a = current ma…jor release
> b = current minor release
> c = future major release
> 
> \* a.x for any features and enhancements (e.g. 4.x)
> \* a.b for any bug fixes (e.g. 4.0, 4.1, 4.2)
> \* c.x for any features, enhancements or bug fixes with backward compatibility breaking changes (e.g. 5.x) --\>
> 
> | Q | A
> | -------------------------------------- | ---
> | Bug fix? (use the a.b branch) | \[\]
> | New feature/enhancement? (use the a.x branch) | \[x\]
> | Deprecations? | \[\]
> | BC breaks? (use the c.x branch) | \[\]
> | Automated tests included? | \[x\] 
> | Related user documentation PR URL | /
> | Related developer documentation PR URL | /
> | Issue(s) addressed | /
> 
> \<!--
> Additionally (see https://contribute.mautic.org/contributing-to-mautic/developer/code/pull-requests#work-on-your-pull-request):
> - Always add tests and ensure they pass.
> - Bug fixes must be submitted against the lowest maintained branch where they apply
> (lowest branches are regularly merged to upper ones so they get the fixes too.)
> - Features and deprecations must be submitted against the "4.x" branch.
> \--\>
> 
> \#### Description:
> 
> This PR creates a generated column to hold the domain portion of a contacts email address. Email domains are used very frequently by marketers for segment filtering. Yet, it is very slow to use \`like\`, \`regex\` or \`endsWith\` operators because those segment queries then do not use indexes.
> 
> This PR will create the new field automatically for all existing and new contacts and keeps the values up to date if the email address changes on the database level.
> 
> Marketers, please use the \`Email Domain\` field for segment filters. It will build the segments much faster and use less resources to do so.
> 
> \#### Steps to test this PR:
> 
> \<!--
> This part is really important. If you want your PR to be merged, take the time to write very clear, annotated and step by step test instructions. Do not assume any previous knowledge - testers may not be developers.
> \--\>
> 1. Load up \[this PR\](https://mautibox.com)
> 2. Or apply this PR on your instance and run \`bin/console doctrine:migrations:migrate\` to add the generated field.
> 3. Create new or update an existing segment and you will be able to see the new \*\*Email Domain\*\* filter.
> 4. Use it in a segment with various operator and ensure that the right contacts are in the segment.
> 
> 
> \<a href="https://gitpod.io/#https://github.com/mautic/mautic/pull/10583"\>\<img src="https://gitpod.io/button/open-in-gitpod.svg"/\>\</a\>

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.

---

<div class="post-metadata">

**Author:** ![mikew](https://sea2.discourse-cdn.com/flex020/user_avatar/forum.mautic.org/mikew/32/5156_2.png) [@mikew](https://forum.mautic.org/u/mikew)\
**Post date:** [May 9, 2022, 12:51pm UTC](https://forum.mautic.org/t/cannot-create-new-custom-fields-sqlstate-42s22-error/23961/11 "2022-05-09T12:51:23Z")

</div>

HI @escopecz

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

---

<div class="post-metadata">

**Author:** ![escopecz](https://sea2.discourse-cdn.com/flex020/user_avatar/forum.mautic.org/escopecz/32/370_2.png) [@escopecz](https://forum.mautic.org/u/escopecz)\
**Post date:** [May 10, 2022, 9:15am UTC](https://forum.mautic.org/t/cannot-create-new-custom-fields-sqlstate-42s22-error/23961/12 "2022-05-10T09:15:21Z")

</div>

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

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

```

Also try if this will fix it:

```auto
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?

---

<div class="post-metadata">

**Author:** ![mikew](https://sea2.discourse-cdn.com/flex020/user_avatar/forum.mautic.org/mikew/32/5156_2.png) [@mikew](https://forum.mautic.org/u/mikew)\
**Post date:** [May 10, 2022, 9:21am UTC](https://forum.mautic.org/t/cannot-create-new-custom-fields-sqlstate-42s22-error/23961/13 "2022-05-10T09:21:42Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![awessel](https://sea2.discourse-cdn.com/flex020/user_avatar/forum.mautic.org/awessel/32/7314_2.png) [@awessel](https://forum.mautic.org/u/awessel)\
**Post date:** [May 19, 2022, 2:04pm UTC](https://forum.mautic.org/t/cannot-create-new-custom-fields-sqlstate-42s22-error/23961/14 "2022-05-19T14:04:16Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![awessel](https://sea2.discourse-cdn.com/flex020/user_avatar/forum.mautic.org/awessel/32/7314_2.png) [@awessel](https://forum.mautic.org/u/awessel)\
**Post date:** [May 31, 2022, 12:09pm UTC](https://forum.mautic.org/t/cannot-create-new-custom-fields-sqlstate-42s22-error/23961/15 "2022-05-31T12:09:33Z")

</div>

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é

---

<div class="post-metadata">

**Author:** ![mauticdeveloper](https://sea2.discourse-cdn.com/flex020/user_avatar/forum.mautic.org/mauticdeveloper/32/627_2.png) [@mauticdeveloper](https://forum.mautic.org/u/mauticdeveloper)\
**Post date:** [February 15, 2023, 7:31am UTC](https://forum.mautic.org/t/cannot-create-new-custom-fields-sqlstate-42s22-error/23961/16 "2023-02-15T07:31:47Z")

</div>

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.