magento2: Magento 245 - Elasticsearch error caused by prefix-table

Preconditions and environment

Magento version CE-2.4.5
Database with prefix table

Steps to reproduce

Install Magento version CE-2.4.5
View category page

Error report
Exception #1 (Zend_Db_Statement_Exception): SQLSTATE[42S02]: Base table or view not found: 1146 Table 'eav_attribute' doesn't exist

Expected result

View category page, list page, search page without error

Actual result

Error report
Exception #1 (Zend_Db_Statement_Exception): SQLSTATE[42S02]: Base table or view not found: 1146 Table 'eav_attribute' doesn't exist

Additional information

The class that is causing the issue: https://github.com/magento/magento2/blob/2.4.5/app/code/Magento/Elasticsearch/Model/ResourceModel/Fulltext/Collection/SearchResultApplier.php Line 242 (SELECT attribute_id FROM eav_attribute WHERE entity_type_id={$entityTypeId}

The query did not declare the prefix for eav_attribute table

Release note

No response

Triage and priority

Severity: S0 - Affects critical data or functionality and leaves users without workaround.
Severity: S1 - Affects critical data or functionality and forces users to employ a workaround.
Severity: S2 - Affects non-critical data or functionality and forces users to employ a workaround.
Severity: S3 - Affects non-critical data or functionality and does not force users to employ a workaround.
Severity: S4 - Affects aesthetics, professional look and feel, “quality” or “usability”.

About this issue

Original URL
State: open
Created 2 years ago
Reactions: 1
Comments: 23 (5 by maintainers)

Most upvoted comments

I have fixed by below changes File: vendor/magento/module-elasticsearch/Model/ResourceModel/Fulltext/Collection/SearchResultApplier.php Line no: 235

From

if ($field === 'name') {
    $entityTypeId = $this->collection->getEntity()->getTypeId();
    $entityMetadata = $this->metadataPool->getMetadata(ProductInterface::class);
    $linkField = $entityMetadata->getLinkField();
    $query->joinLeft(
        ['product_var' => $this->collection->getTable('catalog_product_entity_varchar')],
        "product_var.{$linkField} = e.{$linkField} AND product_var.attribute_id =
        (SELECT attribute_id FROM eav_attribute WHERE entity_type_id={$entityTypeId}
        AND attribute_code='name')",
        ['product_var.value AS name']
    );
} elseif ($field === 'price') {
    $query->joinLeft(
        ['price_index' => $this->collection->getTable('catalog_product_index_price')],
        'price_index.entity_id = e.entity_id'
        . ' AND price_index.customer_group_id = 0'
        . ' AND price_index.website_id = (Select website_id FROM store WHERE store_id = '
        . $storeId . ')',
        ['price_index.max_price AS price']
    );
}

if ($field === 'name') {
    $entityTypeId = $this->collection->getEntity()->getTypeId();
    $entityMetadata = $this->metadataPool->getMetadata(ProductInterface::class);
    $eavTable = $this->collection->getTable('eav_attribute');                
    $linkField = $entityMetadata->getLinkField();
    $query->joinLeft(
        ['product_var' => $this->collection->getTable('catalog_product_entity_varchar')],
        "product_var.{$linkField} = e.{$linkField} AND product_var.attribute_id =
        (SELECT attribute_id FROM ".$eavTable." WHERE entity_type_id={$entityTypeId}
        AND attribute_code='name')",
        ['product_var.value AS name']
    );
} elseif ($field === 'price') {
    $storeTable = $this->collection->getTable('store');
    $query->joinLeft(
        ['price_index' => $this->collection->getTable('catalog_product_index_price')],
        'price_index.entity_id = e.entity_id'
        . ' AND price_index.customer_group_id = 0'
        . ' AND price_index.website_id = (Select website_id FROM '.$storeTable.' WHERE store_id = '
        . $storeId . ')',
        ['price_index.max_price AS price']
    );
}

mitaldeveloper on Nov 7, 2022

This bug is in a published live release and it is site breaking - it needs to be added to the release notes!

iphigenie on Sep 16, 2022

Hi!

We had the same problem. The bug is confirmed. To fix we apply the suggestion: Catalog > Inventory > Display Out of Stock Products configuration is set to No

rjslegall on Nov 26, 2022

Surprised this hasn’t been followed up yet. It’s quite a biggie to slip through the net. Release notes Known Issues do not have this listed for 2.4.5.

drinkingsouls on Oct 13, 2022