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

Your software
My Mautic version is: v2.16.0
My PHP version is: 7.2.27

Custom field limit
My problem is: to add custom fields i have to add mare then 200 custom field and when i reached to 110 it give error that (You have reached the limit of custom field allowed in your database)


How i can increase this li it in my database???
Thanks

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.

There is an extended field plugin that is mentioned in this linked issue, which may help for your particular use case.

1 Like

We’re exploring Mautic as a solution and would also require a fair amount of custom fields for our purposes. Have you tried the plugin that was mentioned and did you have success with it?

I’ve tried the plugin. I added around 300 custom fields, didn’t test it extensively, but so far so good

1 Like

For the first time in years I have hit this error. It is super strange as I have many instances of Mautic installed with way more fields I am trying to do at the moment.

This is a clean install on 3.3.3.

Anyone know of a good workaround here, I am hitting the same problem with an installation of 4.1.0.

Also I have tried the extension however I am not sure it is compatable with 4.1.0 as after installing it was unable to create any new fields or edit old fields

I took a quick look. If you looked at the issue above I think you can see that, there is limit related to MySQL, so I think your chances are:

  • migrate the plugin to Mautic 4 (since you said it does not work as expected anymore)
  • add new fields programatically via new table - for example: my:_table which has a column lead_id (FK to lead table).

I came accross this problem today and the extended fields plugin doesn’t work properly with 4.1. anymore.
Also I needed a specific action which was introduced in Mautic 4.1 - send email to user with token support. This is not possible if the token would be one from the extended fields plugin - because the token alias gets looked up inside the “lead” table and not inside “lead fields” table.
Anyway.

I took a deeper look into the actual problem in the database:

Text fields get inserted into the database as “varchar” and the character number -

  • system fields are defined as varchar with 191 characters
  • new text customfields are varchar with 255 characters

The mysql limitation is that a table can not exceed 65,535-byte row size.

This means you can create as many fields until you hit the 65,535-bytes.
Therefor you can:

  • change the varchar characters to the actually needed size:
    (i.e if you only need the words red or blue you could limit the characters to 4 - because blue has only 4 characters)
  • or you change the fields to text instead of varchar - but this makes them non-indexable - means the search for something in the database gets a lot slower.

I changed most of my fields to a varchar character value of 99 or 49
after i did that with almost 100 fields I was able to insert all the other fields I needed :slight_smile:

6 Likes

Hi Alex,

how did you go about doing that for the individual fields?

Thanks!

Hi

Digging up old issues here.

So in order to update the size of a specific column can be done inside mysql with the following command:

alter table leads MODIFY column_name varchar(99);

where column_name is the column you want to change.

Now for my questions, inside one of my instances I am getting the following error:

ERROR 1054 (42S22): Unknown column '`mautic`.`l`.`email`' in 'GENERATED ALWAYS'

Anyone know how I can fix this, as I cannot change the size of any columns

tagging you @jester as you replied to a previous post and seem to maybe have the mysql expertise to hopefully help me out on this one as well.

@mikew could you you give exact query? It’s a bit hard to understand what’s triggering the error

As @mikew described you could just use the “alter table” sql to change a field.
The easiest way would be to simply open the database in phpmyadmin or adminer > navigate to the table lead_fields and change the values there. :slight_smile:

I changed my label and alias VARCHAR from 191 to 100 in the lead_fields table

but I still get the same error, were some other things needed to be changed?

ok its the VARCHAR of the columns in the leads table that need to be modified

Is it safe to delete unwanted custom fields in the leads table in the database?

Manage them from mautic’s interface
mautic > custom fields
Only delete what Mautic let’s you delete or modify the code to prevent mautic search for them.

BTW why do you need so many fields? Can you give me an example of what you are trying to achieve?

I mean, you can probably add values into the same field separated by commas and then fetch the data as you need it. If this is not your case, then a plugin will be needed.

Ah yes i can see i can delete from UI

It’s not me, the marketing guys want these fields, just extra data I suppose + I think quite a few of these fields are auto generated by other things in our setup

Unfortunately the fields that come with mautic can’t be deleted, but there is a new function (or plugin?) for mautic 4 or 5 (can’t remember it, read it in the forum) that let’s you reduce the lenght of mautic default fields.

By doing that, you can free loads of space for other new columns.

I’m not sure if this is the right place to post this, but I’ve been searching the forum for over an hour and can’t find a straightforward way to create a new post. I apologize to the original poster for adding my question here, but I’m really lost.

I’ve just installed the new version of the software, and as a first-time user, I’m running into some issues. Everything seems to be installed correctly, but I’m using a VPS server instead of a dedicated server. I’ve set it up under a subdomain, and while sending and tracking emails works fine, I’m having trouble with the Campaign Builder. Every time I try to create an email, I get the error message: “Your request could not be processed, please try again.” The logs don’t seem to provide any clues.

I’ve reinstalled the software three times now and keep facing the same issue. The software is behind a basic firewall, and I can’t find any documentation about specific port requirements. Can anyone help me out? I’m getting really frustrated with this and am about to give up on it, even though it came highly recommended. Thanks in advance for any assistance! I feel like I could bang my head off the wall looking for a solution. I don’t understand why it’s so hard with such popular software specially open source not defined simple documentation that could guide you through turning on the bug mode or a more defined way to find errors, in the logs

Its to early to tell at this point but the issue might be due to the fact that you are behind the firewall.

I am happy to take a look at the issue for you, please DM me and we can schedule a meeting.