2

I have a strange inconsistency in the attribute index that could not be solved with a full reindex. For a dropdown attribute there are two different values for each store, like this:

mysql>  select * from catalog_product_index_eav where entity_id=10515 and attribute_id=274;                                                                                      +-----------+--------------+----------+-------+
| entity_id | attribute_id | store_id | value |
+-----------+--------------+----------+-------+
|     10515 |          274 |       12 | 99437 |
|     10515 |          274 |       12 | 99441 |
|     10515 |          274 |       13 | 99437 |
|     10515 |          274 |       13 | 99441 |
|     10515 |          274 |       14 | 99437 |
|     10515 |          274 |       14 | 99441 |
|     10515 |          274 |       15 | 99437 |
|     10515 |          274 |       15 | 99441 |
|     10515 |          274 |       16 | 99437 |
|     10515 |          274 |       16 | 99441 |
|     10515 |          274 |       17 | 99437 |
|     10515 |          274 |       17 | 99441 |
|     10515 |          274 |       18 | 99437 |
|     10515 |          274 |       18 | 99441 |
|     10515 |          274 |       19 | 99437 |
|     10515 |          274 |       19 | 99441 |
|     10515 |          274 |       20 | 99437 |
|     10515 |          274 |       20 | 99441 |
|     10515 |          274 |       21 | 99437 |
|     10515 |          274 |       21 | 99441 |
|     10515 |          274 |       22 | 99437 |
|     10515 |          274 |       22 | 99441 |
+-----------+--------------+----------+-------+
22 rows in set (0.00 sec)

(same result with catalog_product_index_eav_idx) The attribute has global scope, so there is only one value in the EAV table:

mysql>  select * from catalog_product_entity_int where entity_id=10515 and attribute_id=274;
+----------+----------------+--------------+----------+-----------+-------+
| value_id | entity_type_id | attribute_id | store_id | entity_id | value |
+----------+----------------+--------------+----------+-----------+-------+
|   260901 |              4 |          274 |        0 |     10515 | 99441 |
+----------+----------------+--------------+----------+-----------+-------+
1 row in set (0.00 sec)

The indexer should read that with a query like this (based on the code in Mage_Catalog_Model_Resource_Product_Indexer_Eav_Source):

mysql> select pid.entity_id,pid.attribute_id,pid.store_id,IFNULL(pis.value,pid.value) as value from (select d.entity_id,d.attribute_id,d.value,s.store_id,s.website_id from core_store s left join catalog_product_entity_int d on 1=1 and d.store_id=0 where s.store_id != 0 and d.entity_id=10515) pid left join catalog_product_entity_int pis on pis.entity_id = pid.entity_id and pis.attribute_id=pid.attribute_id and pis.store_id=pid.store_id where pid.attribute_id=274;
+-----------+--------------+----------+-------+
| entity_id | attribute_id | store_id | value |
+-----------+--------------+----------+-------+
|     10515 |          274 |       12 | 99441 |
|     10515 |          274 |       16 | 99441 |
|     10515 |          274 |       17 | 99441 |
|     10515 |          274 |       18 | 99441 |
|     10515 |          274 |       19 | 99441 |
|     10515 |          274 |       20 | 99441 |
|     10515 |          274 |       21 | 99441 |
|     10515 |          274 |       22 | 99441 |
|     10515 |          274 |       15 | 99441 |
|     10515 |          274 |       13 | 99441 |
|     10515 |          274 |       14 | 99441 |
+-----------+--------------+----------+-------+
11 rows in set (0.00 sec)

As far as I understand the indexer workflow, it creates the index in a temporary table, then (in a transaction) deletes everything from the existing index table and copies the data from the temporary table. I.e. after a full reindex it should not be possible that old index data is still around.

So where can this other value come from and how can I possibly get rid of it?

  • I just noticed that in my 1.7 installation, catalog_product_index_eav_idx has no foreign key relations! This means that even though I deleted 180,000 products, this table remains huge with a bunch of orphan rows. I don't know if my installation is corrupted, or if this is an oversight by Magento. – Buttle Butkus Jan 20 '16 at 3:16
4

I found the solution in the query logs and it's surprisingly logical.

Short Answer

The product in question was a bundle product and for bundle products all attributes of the bundle selections are taken into account as well. This way you also find bundles that may contain a product with the specified options in layered navigation.

Long Answer

This query runs after the first round of indexing:

INSERT IGNORE INTO `catalog_product_index_eav_idx` SELECT `l`.`parent_id`, `i`.`attribute_id`, `i`.`store_id`, `i`.`value` FROM `catalog_product_relation` AS `l`
CROSS JOIN `core_store` AS `cs`
INNER JOIN `catalog_product_index_eav_idx` AS `i` ON l.child_id = i.entity_id AND cs.store_id = i.store_id GROUP BY `l`.`parent_id`,
`i`.`attribute_id`,
`i`.`store_id`,
`i`.`value`;

If I run the SELECT subquery and limit it to the attribute and product from above, I get the following, which are exactly my mysterious values:

mysql> SELECT `l`.`parent_id`, `i`.`attribute_id`, `i`.`store_id`, `i`.`value` FROM `catalog_product_relation` AS `l`
    ->  CROSS JOIN `core_store` AS `cs`
    ->  INNER JOIN `catalog_product_index_eav_idx` AS `i` ON l.child_id = i.entity_id AND cs.store_id = i.store_id GROUP BY `l`.`parent_id`,
    ->         `i`.`attribute_id`,
    ->         `i`.`store_id`,
    ->         `i`.`value` having l.parent_id=10515 and i.attribute_id=274;
+-----------+--------------+----------+-------+
| parent_id | attribute_id | store_id | value |
+-----------+--------------+----------+-------+
|     10515 |          274 |       12 | 99437 |
|     10515 |          274 |       13 | 99437 |
|     10515 |          274 |       14 | 99437 |
|     10515 |          274 |       15 | 99437 |
|     10515 |          274 |       16 | 99437 |
|     10515 |          274 |       17 | 99437 |
|     10515 |          274 |       18 | 99437 |
|     10515 |          274 |       19 | 99437 |
|     10515 |          274 |       20 | 99437 |
|     10515 |          274 |       21 | 99437 |
|     10515 |          274 |       22 | 99437 |
+-----------+--------------+----------+-------+
11 rows in set (0.45 sec)

The code for this query is in Mage_Catalog_Model_Resource_Product_Indexer_Eav_Abstract::_prepareRelationIndex()

catalog_product_relation contains the bundle relations, so the value comes from a bundle selection of product 10515.

  • 1
    There is seems to be similar behaviour for grouped products. The values in catalog_product_index_eav table mirror not only the value of the grouped product but also the simple products associated with it. – Tomas Dermisek Jun 2 '15 at 6:28
  • Also for all product links (upselling and crosselling as well). Is there any way to get rid of it? – Aleksey Razbakov Aug 7 '15 at 15:16
  • Not sure, you probably have to rewrite the indexer where _prepareRelationIndex is called. In our case, we solved the problem with different product configuration. – Fabian Schmengler Aug 7 '15 at 20:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.