Magento 1.8 - Advanced Search doesn't work

Question

I have found a strange bug in the latest release of magento: Advanced Search tool doesn't work properly.

What have I done:

• Install a fresh copy of magento
• Added a product called Test 1 by the administration page
• Reindex all
• Cleared all caches
• At the frontend page:
  • Open the Advanced Search Page
  • Search the "Test 1" product that you have added before in the catalog
    • No result

I have no result using as search attribute the "Name" of the product.

Seems that another user has the same issue: Magento advanced search returns no results

If you see the entity table in the database, you can see the attribute "71" with the name of the product. Attribute 71 is the "Name" Magento Attribute.

Catalog Product Varchar Attribute Backend Table

If I try to read the Magento code at app/code/core/Mage/CatalogSearch/Model/Advanced.php and debug it, I can see this command:

$this->getProductCollection()->addFieldsToFilter($allConditions);

array(1) {
  ["catalog_product_entity_varchar"] => array(1) {
    [71] => array(1) {
      ["like"] => string(9) "%Test 1%"
    }
  }
}

but Magento doesn't show me any result.

If I try to execute a search process using the SKU field, magento return the product!

array(1) {
  ["catalog_product_entity"] => array(1) {
    ["sku"] => array(1) {
      ["like"] => string(6) "%test%"
    }
  }
}

Is a Magento Bug?

Update: If I add the default category to the list of the product categories, seems that it works. Is a normal Magento behaviour?

thanks

Answer 1

This is probably a bug in mysql 5.6 due to how the advanced search query is done. There is a work around in this bug report.

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