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:
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:
in getResultCount() from app\code\local\Mage\CatalogSearch\Block\Advanced\Result.php
Then looking in the _parts section