1

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?

4
  • 2
    did you try to reindex the search?
    – Marius
    Jun 3, 2013 at 10:19
  • Yes sure, many times and deleted cache. I even made copy of database in another server with older MySQL version and directed Magento there and the advanced search functioned again as before. But its not the solution in perspective.
    – Hex
    Jun 3, 2013 at 10:24
  • It looks like there are some isues with Magento and mysql 5.6. See this article: joomlacreator.com/blog/…. It's about magento 1.7 but this could also be an issue for you.
    – Marius
    Jun 3, 2013 at 10:35
  • Yes thanks for this link, I already googled for this problem and this blog post I know. Its more related to the installation problem because the MySQL variable name have been changed. It does not help me much to understand the anomaly in advanced search behavior.
    – Hex
    Jun 3, 2013 at 10:42

4 Answers 4

0

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

1
  • Thanks for pointing out this solution. I upgraded already magento to 1.8 but the problem was still there. I found the file to change and tested your solution. It works! So in Magento 1.8 I changed file: app\code\core\Mage\CatalogSearch\Model\Resource\Advanced\Collection.php This line: $field = $this->getConnection()->getCheckSql('t2.value_id>0', 't2.value', 't1.value'); To this: $field = $this->getConnection()->getCheckSql('t1.value_id>0', 't1.value', 't1.value');
    – Hex
    Dec 25, 2013 at 16:43
1

This is a known issue and was patched via a suggestion on the Magento 2 Github some time ago:

https://github.com/magento/magento2/issues/202

It also made its way into the new CE 1.8 Alpha*:

public function supportEngine()
{
    $variables  = $this->_getConnection()
        ->fetchPairs('SHOW VARIABLES');
    return (!isset($variables['have_innodb']) || $variables['have_innodb'] != 'YES') ? false : true;
}

A widely distributed diff has made the rounds - I have mirrored it here:

https://gist.github.com/philwinkle/5954728


* Source: http://svn.magentocommerce.com/source/branches/1.8/app/code/core/Mage/Install/Model/Installer/Db/Mysql4.php

1
  • That patch is for installation issues not the advanced search problem the questioner has. Sep 11, 2013 at 23:03
1

This is a bug in mysql 5.6 due to how the advanced search query is done. The bug is detailed here:

http://bugs.mysql.com/bug.php?id=70608

There is a workaround in there for 5.6 by doing:

set optimizer_switch='semijoin=off';

1
  • Can someone please explain where to add this: set optimizer_switch='semijoin=off'; Thanks!
    – Hex
    Dec 25, 2013 at 15:18
0

you can drop set optimizer_switch='semijoin=off'; right in you mysql console. open it by "mysql -u root -p__PASSWORD_HERE__" and type set global optimizer_switch='semijoin=off';

That's it.

I confirm that this is fixing the issue. magento 1.7.0.2 mysql Server version: 5.6.15-63.0-log Percona Server (GPL), Release 63.0

SELECT @@optimizer_switch\G ************* 1. row ************* @@optimizer_switch: index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=off,loosescan=on,firstmatch=on,subquery_materialization_cost_based=on,use_index_extensions=on

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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