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

Support
,
#1

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)

Screenshot_2
Screenshot_2732Ă—309 17.9 KB

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

#2

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
Extended field plugin
#3

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?

#4

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

1 Like
#5

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.

#6

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

#7

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).
#8

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:

2 Likes
#9

Hi Alex,

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

Thanks!

#10

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

#11

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.

How many custom fields can I have in company/contact and can they be transfered?
#12

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

#13

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: