Magento – Advanced search is partially broken after MySQL upgrade to v. 5.6

advanced-searchce-1.6.1.0MySQLsearch

Maybe someone have been experienced the same problem and have solution for this?

My hosting provider was upgrading recently the MySQL version to 5.6 and after that the Advanced Search can not find anymore most of results searched by custom attributes. For example it gives results fine if you searching by “SKU” or by “Manufacturer”. No any results if you searching by custom attributes as “original code” and etc. even searching by “name” attribute does not work anymore. In default search they working (mini form) and they where working perfectly in Advanced Search also before the MySQL upgrade. Can anyone see some logic behind this and point to some direction to solve the problem?

Magento CE ver. 1.6.1.0

Any ideas? Anyone?

Best Answer

I had the same problem. I had to downgrade back to mysql 5.5 for it to work.

I narrowed the problem down to this query when searching by name which returns no results because it doesn't match the word:

select
    e.*,
    price_index.price,
    price_index.final_price,
    IF(price_index.tier_price IS NOT NULL, LEAST(price_index.min_price, price_index.tier_price), price_index.min_price) as minimal_price,
    price_index.min_price,
    price_index.max_price,
    price_index.tier_price,
    cat_index.position as cat_index_position
from
    catalog_product_entity as e
inner join
    catalog_product_index_price as price_index
    on (price_index.entity_id = e.entity_id AND price_index.website_id = '1' AND price_index.customer_group_id = 0)
inner join
    catalog_category_product_index as cat_index on (cat_index.product_id=e.entity_id AND cat_index.store_id='1' AND cat_index.visibility IN(3, 4) AND cat_index.category_id='2')
where
    (e.entity_id IN
        (SELECT
            t1.entity_id
        FROM
            catalog_product_entity_varchar AS t1
        LEFT JOIN
            catalog_product_entity_varchar AS t2
            ON t1.entity_id = t2.entity_id
            AND t1.attribute_id = t2.attribute_id
            AND t2.store_id=1
        WHERE
            (t1.store_id = 0)
        AND
            (t1.attribute_id = 60)
        AND
            (t1.entity_id = price_index.entity_id)
        AND
            (IF(t2.value_id>0, t2.value, t1.value) LIKE '%gift%')
        )
    );

Near the end the IF(t2.value_id>0, t2.value, t1.value) doesn't seem to return anything when t2.value_id and t2.value are NULL, if I change it to IF(t1.value_id>0, t1.value, t1.value), it worked.

You can construct the query that is being used for your advanced search by putting:

var_dump($this->getSearchModel()->getProductCollection());
die();

in getResultCount() from app\code\local\Mage\CatalogSearch\Block\Advanced\Result.php

Then looking in the _parts section

Related Topic