Magento 2.4 Elastic search error: Text fields are not optimised for operations

Question

Following Error logs in exception.log file:

main.CRITICAL: {"error":{"root_cause":[{"type":"illegal_argument_exception","reason":"Text fields are not optimised for operations that require per-document field data like aggregations and sorting, so these operations are disabled by default. Please use a keyword field instead. Alternatively, set fielddata=true on [gift_price] in order to load field data by uninverting the inverted index. Note that this can use significant memory."}],"type":"search_phase_execution_exception","reason":"all shards failed","phase":"query","grouped":true,"failed_shards":[{"shard":0,"index":"toplinem2_product_5_v19","node":"_emcfzugRbWjAjzCgv265Q"

score 9 · Accepted Answer · answered Jul 19 '21 at 06:16

on Investigation came to know this issue is due to upgrade process. Some product attributes which are text/varchar set to be filerable in search. like for me gift_price mentioned in above error. So you need to search those in your DB and change their statuses. I use the following queries to fix those issues:

First you can find those attributes to look them:

select * from xb_catalog_eav_attribute WHERE is_filterable_in_search = 1 and attribute_id IN (SELECT attribute_id FROM xb_eav_attribute WHERE entity_type_id = 4 AND backend_type = 'varchar')

Then you can run the following query:

UPDATE xb_catalog_eav_attribute 
SET is_filterable_in_search = 0 
WHERE attribute_id IN (SELECT attribute_id FROM xb_eav_attribute WHERE entity_type_id = 4 AND backend_type = 'varchar')

In my case gift_price this attribute is just appearing into error logs so I search this as well by following query:

SELECT * FROM evonomix_toplinem2_local.xb_eav_attribute where attribute_code = "gift_price";

and get its id then run following to mark it non filterable:

UPDATE xb_catalog_eav_attribute SET is_filterable_in_search = 0 WHERE attribute_id IN (695)

Purpose of all this explanation is to give brief on this issue and starting point to debuging for your similar issues.

Combining your answer with this, I finally traced and fixed the same error: https://magento.stackexchange.com/questions/299668/integrate-or-enable-elasticsearch-with-magento-2-3-3 — araujophillips, Sep 20 '21 at 13:27
Thanks for this mate!! :) This is working. I had the same issue with gift_price — S.S.Niranga, Sep 22 '21 at 01:39
It works, thank you! Just a note that on my side, I had to do "bin/magento cache:clean" after that to have the change applied. I thought that it is DB related and not required but it was not the case — Duc To, Mar 08 '22 at 03:26

score 4 · Answer 2 · answered Jul 21 '22 at 12:56

I am upgrading from 2.3.3 to 2.4.4, and I would like to add to the answers from @hassan-ali-shahzad and @rohit-prajapati that not only is_filterable_in_search but also is_filterable causes problems.

Below I've added the attribute to the SQL from @rohit-prajapati that will show all the problematic attributes.

SELECT * from catalog_eav_attribute WHERE (is_filterable_in_search = 1 OR is_filterable = 1) AND attribute_id IN (SELECT attribute_id FROM eav_attribute WHERE entity_type_id IN (SELECT entity_type_id FROM `eav_entity_type` WHERE `entity_type_code` = 'catalog_product') AND (backend_type = 'varchar' OR backend_type = 'text'));

This worked for me! Here is the query I ran to fix it: update catalog_eav_attribute set is_filterable=0, is_filterable_in_search=0 WHERE (is_filterable_in_search = 1 OR is_filterable = 1) AND attribute_id IN (SELECT attribute_id FROM eav_attribute WHERE entity_type_id IN (SELECT entity_type_id FROM eav_entity_type WHERE entity_type_code = "catalog_product") AND (backend_type = "varchar" OR backend_type = "text")); — seanbreeden, Sep 16 '22 at 16:35

score 2 · Answer 3 · answered Dec 03 '21 at 08:00

That works fine, but in my case catalog_product entity_type_id was 10 and also there was some text type attributes set to is_filterable_in_search = 1.

So modified query as following:

First take database backup & run query to verify records

SELECT * from catalog_eav_attribute WHERE is_filterable_in_search = 1 and attribute_id IN (SELECT attribute_id FROM eav_attribute WHERE entity_type_id IN (SELECT entity_type_id FROM `eav_entity_type` WHERE `entity_type_code` = 'catalog_product') AND (backend_type = 'varchar' OR backend_type = 'text'));

Run the update query:

UPDATE catalog_eav_attribute SET is_filterable_in_search = 0 WHERE attribute_id IN (SELECT attribute_id FROM eav_attribute WHERE entity_type_id IN (SELECT entity_type_id FROM `eav_entity_type` WHERE `entity_type_code` = 'catalog_product') AND (backend_type = 'varchar' OR backend_type = 'text'));

That's all.

Cheers!

score 0 · Answer 4 · answered Oct 28 '22 at 10:01

update catalog_eav_attribute set is_filterable=0, is_filterable_in_search=0 WHERE (is_filterable_in_search = 1 OR is_filterable = 1) AND attribute_id IN (SELECT attribute_id FROM eav_attribute WHERE entity_type_id IN (SELECT entity_type_id FROM eav_entity_type WHERE entity_type_code = "catalog_product") AND (backend_type = "varchar" OR backend_type = "text"));

MySQL update query worked for me.

score 0 · Answer 5 · answered Dec 15 '22 at 11:58

I've made a custom product attribute by adding this code:

$eavSetup->addAttribute(
    Product::ENTITY,
    'my_custom_attribute',
    [
        'type' => 'varchar',
        'backend' => '',
        'frontend' => '',
        'label' => 'My custom label',
        'input' => 'text',
        'class' => '',
        'group' => 'Product Details',
        'global' => ScopedAttributeInterface::SCOPE_STORE,
        'visible' => true,
        'required' => false,
        'user_defined' => true,
        'default' => '',
        'searchable' => true,
        'filterable' => true,
        'comparable' => true,
        'visible_on_front' => false,
        'used_in_product_listing' => false,
        'unique' => false,
        'source' => '',
        'is_used_in_grid' => true,
        'is_visible_in_grid' => true,
        'is_filterable_in_grid' => true,
        'apply_to' => 'booking' 
    ]
);

I went to the category page and I got no products , in the logs I've got the same as Hassan Ali Shahzad mentioned.

The fix for me was to update this field to NO:

Probably if I would change the: 'searchable' => true, to 'searchable' => false,, would worked as well.

Maybe this post will help someone in the future.

Cheers !

Magento 2.4 Elastic search error: Text fields are not optimised for operations

5 Answers5

Linked