Column not found: 1054 Unknown column 'entity_id'

[bery]

Hi,

when trying to install Elasticsuite extension on Magento EE 2.1.1 I have ran into an issue when running 'setup:upgrade' command. I believe that this is related to the staging feature introduced in Magento EE 2.1. and changes in column names that came with this change - e.g. renaming entit_id to row_id

  SQLSTATE[42S22]: Column not found: 1054 Unknown column 'entity_id' in 'field list', query was: INSERT INTO `catalog_category_entity_int` (`attribute_id`, `entity_id`, `value`) SELECT 211 as attribute_id, `catalog_category_entity`.`row_id`, 1 as value FROM `catalog_c
  ategory_entity` WHERE (catalog_category_entity.created_in <= 1) AND (catalog_category_entity.updated_in > 1) ON DUPLICATE KEY UPDATE `attribute_id` = VALUES(`attribute_id`), `entity_id` = VALUES(`entity_id`), `value` = VALUES(`value`)

  [PDOException]
  SQLSTATE[42S22]: Column not found: 1054 Unknown column 'entity_id' in 'field list'```

chaning lines [145](https://github.com/Smile-SA/elasticsuite/blob/master/src/module-elasticsuite-catalog/Setup/InstallData.php#L145) and [149](https://github.com/Smile-SA/elasticsuite/blob/master/src/module-elasticsuite-catalog/Setup/InstallData.php#L149) from entity_id to row_id solved my issue.

[Related issue](https://github.com/magento/magento2/issues/6478) in Magento 2 queue in case somebody is looking into similar errors 

[southerncomputer]

You have to patch the software to accept these new changes:

roughly:
`
app/code/Smile/ElasticsuiteCatalog/Model/Product/Indexer/Fulltext/Datasource/AttributeData.php:78: if (isset($row['row_id'])) { $productId=(int)$row['row_id']; }

app/code/Smile/ElasticsuiteCatalog/Model/ResourceModel/Product/Indexer/Fulltext/Datasource/CategoryData.php:168: if (isset($row['row_id'])) {
app/code/Smile/ElasticsuiteCatalog/Model/ResourceModel/Product/Indexer/Fulltext/Datasource/CategoryData.php:169: $categoryId = (int) $row['row_id']; }

app/code/Smile/ElasticsuiteCatalog/Model/ResourceModel/Product/Indexer/Fulltext/Datasource/CategoryData.php:198: ->from(['default_value' => $nameAttr->getBackendTable()], ['row_id'])
app/code/Smile/ElasticsuiteCatalog/Model/ResourceModel/Product/Indexer/Fulltext/Datasource/CategoryData.php:199: ->where('default_value.row_id != ?', $rootCategoryId)
app/code/Smile/ElasticsuiteCatalog/Model/ResourceModel/Product/Indexer/Fulltext/Datasource/CategoryData.php:202: ->where('default_value.row_id IN (?)', $loadCategoryIds);
app/code/Smile/ElasticsuiteCatalog/Model/ResourceModel/Product/Indexer/Fulltext/Datasource/CategoryData.php:206: "default_value.row_id = use_name_default_value.row_id" .

app/code/Smile/ElasticsuiteCatalog/Model/ResourceModel/Product/Indexer/Fulltext/Datasource/CategoryData.php:222: "default_value.row_id = store_value.row_id" .
app/code/Smile/ElasticsuiteCatalog/Model/ResourceModel/Product/Indexer/Fulltext/Datasource/CategoryData.php:232: "default_value.row_id = use_name_store_value.row_id" .

app/code/Smile/ElasticsuiteCatalog/Model/ResourceModel/Eav/Indexer/Fulltext/Datasource/AbstractAttributeData.php:83: 't_default.row_id = t_store.row_id',

app/code/Smile/ElasticsuiteCatalog/Model/ResourceModel/Eav/Indexer/Fulltext/Datasource/AbstractAttributeData.php:93: $select->from(['t_default' => $tableName], ['row_id', 'attribute_id'])

app/code/Smile/ElasticsuiteCatalog/Model/ResourceModel/Eav/Indexer/Fulltext/Datasource/AbstractAttributeData.php:97: ->where('t_default.row_id IN (?)', $entityIds)

app/code/Smile/ElasticsuiteCatalog/Model/Category/Indexer/Fulltext/Datasource/AttributeData.php:41: if (isset($row['row_id'])) {
app/code/Smile/ElasticsuiteCatalog/Model/Category/Indexer/Fulltext/Datasource/AttributeData.php:42: $productId = (int) $row['row_id']; } else
`

[romainruaud]

Hello @bery , you are right, the 2.2.x branch is compatible with Magento 2.1 CE only, which means : does not currently support the staging feature.

We are working on this support and plan to release it soon.

One workaround can also be to temporary disable the staging module, to prevent database table changes.

We'll keep you in touch.

[southerncomputer]

Also you need to remove the two view layout.xml files from module-layered_nagivationStaging and disable Magento_AdvancedSearch for layered lnavigation to work!

Disabling modules does not remove their DI/etc from magento 2.1.1 ! you have t use module:uninstall which then relies upon composer to remove the module otherwise if you didnt use composer to install magento it will make a big mess!

I've got elasticsuite working fine on 2.1.1-EE - but it took all these tweaks to get it to work perfectly.

The Elasticsearch built into EE dies at 8GB of ram limit after 100K products - Elasticsuite finishes indexer fulltext in 2;30 not using 1/10tth of the Ram ! Plus it can be further optimized by logging the DB CALLS you will see ElasaticSuiite calls 3 DB CALLS that are not necessary for simple products during indexing which wastes a lot of DB call time!

[romainruaud]

Hello @southerncomputer and many thanks for your little benchmark comparaison between raw Magento and our module :)

We really appreciate to see such involvment from people using the module and such enthousiasm.

About the staging, here is what we use to disable it actually (when creating a new project) :

• add the following lines into the composer.json file

    "scripts": {
        "pre-autoload-dump":    [ "rm -rf ./vendor/magento/module*-staging" ]
    }

• lauch the following commands:

    composer install
    composer dumpautoload

[romainruaud]

For Smile's internal purpose : This is related to the staging-compatibility feature.

[romainruaud]

By the way @southerncomputer

Could you please indicate in which piece of code occur the 3 DB calls you are speaking above ? We'll gladly fix it to ensure even better performance during indexing :)

[southerncomputer]

ElasticsuiteCatalog\Model\Product\Indexer\Fulltext\Datasource\AttributeData.php

If count(allChildrensIds)==0 then it calls mysql query with null Where in statement (at least on my implementation), maybe better way to disable this. I just used Database query logging to locate this call that loops over and over.

Maybe try it and see!

`<?php
/**

• DISCLAIMER
  *
• Do not edit or add to this file if you wish to upgrade Smile Elastic Suite to newer
• versions in the future.
  *
• @category Smile
• @Package Smile\ElasticsuiteCatalog
• @author Aurelien FOUCRET [email protected]
• @copyright 2016 Smile
• @license Open Software License ("OSL") v. 3.0
  */

namespace Smile\ElasticsuiteCatalog\Model\Product\Indexer\Fulltext\Datasource;

use Smile\ElasticsuiteCatalog\Model\Eav\Indexer\Fulltext\Datasource\AbstractAttributeData;
use Smile\ElasticsuiteCore\Api\Index\DatasourceInterface;
use Smile\ElasticsuiteCore\Api\Index\Mapping\DynamicFieldProviderInterface;

/**

• Datasource used to index product attributes.

• This class is also used to generate attribute mapping since it implements DynamicFieldProviderInterface.
  *

• @category Smile

• @Package Smile\ElasticsuiteCatalog

• @author Aurelien FOUCRET [email protected]
  /
  class AttributeData extends AbstractAttributeData implements DatasourceInterface, DynamicFieldProviderInterface
  {
  /*

  • @var array
    */
    private $forbidenChildrenAttributeCode = ['visibility', 'status', 'price', 'tax_class_id'];

  /**

  • {@inheritdoc}
    */
    public function addData($storeId, array $indexData)
    {
    $productIds = array_keys($indexData);
    $indexData = $this->addAttributeData($storeId, $productIds, $indexData);

    $relationsByChildId = $this->resourceModel->loadChildrens($productIds);
    $allChildrenIds = array_keys($relationsByChildId);
    if (count($allChildrenIds)>0) {
    $childrenIndexData = $this->addAttributeData($storeId, $allChildrenIds);
    }
    `

[bery]

Thanks for coming back. I already made most of the changes above manually to make the module work, and for now, everything seems to be stable.

Looking forward to the new version!

[romainruaud]

@afoucret assigned for reviewing #123

[romainruaud]

Fixed by #123