1271 - Illegal mix of collations o frontend search

[Dreakman]

Hello,
after entering non-latin characters in search module i have this error:

Code Select Expand

1271 - Illegal mix of collations for operation 'like' SQL=SELECT SQL_CALC_FOUND_ROWS p.`virtuemart_product_id` FROM `db_virtuemart_products` as p LEFT JOIN `db_virtuemart_product_shoppergroups` as ps ON p.`virtuemart_product_id` = `ps`.`virtuemart_product_id` LEFT JOIN `db_virtuemart_product_categories` as pc ON p.`virtuemart_product_id` = `pc`.`virtuemart_product_id` LEFT JOIN `db_virtuemart_categories` as c ON c.`virtuemart_category_id` = `pc`.`virtuemart_category_id` LEFT JOIN `db_virtuemart_product_prices` as pp ON p.`virtuemart_product_id` = pp.`virtuemart_product_id` WHERE ((`p`.created_on LIKE "%Р±%" OR `p`.product_sku LIKE "%Р±%" OR `product_price` LIKE "%Р±%" OR `p`.product_sales LIKE "%Р±%") AND ((p.`product_parent_id` = "0" AND `pc`.`virtuemart_category_id` > "0") OR p.`product_parent_id` > "0") AND ((p.`product_parent_id` > "0" AND `pc`.`virtuemart_category_id` > "0") OR p.`product_parent_id` = "0") AND `c`.`published` = 1 AND ( `ps`.`virtuemart_shoppergroup_id`= "2" OR `ps`.`virtuemart_shoppergroup_id` IS NULL ) AND p.`published`="1" ) group by p.`virtuemart_product_id` ORDER BY `pc`.`ordering` ASC LIMIT 0, 9

Des anybody have any idea how to fix it ?
Best Regards!

[Milbo]

Please give your versions, and php version and if you use the mb_ functions

[Dreakman]

Hello and hanks for your reply!

Joomla: 3.4.1
VM: 3.0.8

I don't have any mb functions.

[Edwardocz]

We have a website Joomla 3.4.4., VirtueMart 3.0.9 and PHP 5.6.13. MB does not. Web address: http://www.prirodni-budejovice.cz/prirodni/
I write to dandruff word with a hook, for example TEPLOMĚR. He runs the following message:

Oops!
1271 - Illegal mix of collations for operation 'like' SQL=SELECT SQL_CALC_FOUND_ROWS p.`virtuemart_product_id` FROM `jos_virtuemart_products` as p INNER JOIN `jos_virtuemart_products_cs_cz` as l using (`virtuemart_product_id`) LEFT JOIN `jos_virtuemart_product_shoppergroups` as ps ON p.`virtuemart_product_id` = `ps`.`virtuemart_product_id` LEFT JOIN `jos_virtuemart_product_categories` as pc ON p.`virtuemart_product_id` =

Write the word TEPLOMER without a hook, and find the right.

Author template he wrote this: "We provide support for the code that we developed (Template) Issue that you are reporting is generated by default Virtuemart script."

Thank you for your help and eliminating errors in the code Virtuemart

[sandomatyas]

Check your settings if 'Creation Date' is an available search field. Virtuemart uses these fields to construct the select query using LIKE '%..%' but 'Creation date' is a datetime field which doesn't have collation.

[copierx]

Thank you sandomatyas. your solution fixed my problem.

[Milbo]

That means you had to disable this field to get it running? I would like to fix that.

[sandomatyas]

yes, that's right

[Milbo]

Check your settings if 'Creation Date' is an available search field. Virtuemart uses these fields to construct the select query using LIKE '%..%' but 'Creation date' is a datetime field which doesn't have collation.

Yeah and therefore it cant be a mix of collations.
Hmmm, I renamed cap against cäp and enabled any search fields

Array
(
   

• => product_name
      [1] => `p`.created_on
      [2] => `p`.product_sku
      [3] => `p`.product_mpn
      [4] => product_s_desc
      [5] => product_desc
      [6] => `l`.slug
      [7] => category_name
      [8] => category_description
      [9] => mf_name
      [10] => product_price
      [11] => `p`.product_special
      [12] => `p`.product_sales
      [13] => `p`.product_availability
      [14] => `p`.product_available_date
      [15] => `p`.product_height
      [16] => `p`.product_width
      [17] => `p`.product_length
      [18] => `p`.product_lwh_uom
      [19] => `p`.product_weight
      [20] => `p`.product_weight_uom
      [21] => `p`.product_in_stock
      [22] => `p`.low_stock_notification
      [23] => `p`.modified_on
      [24] => `p`.product_gtin
      [25] => `p`.product_unit
      [26] => `p`.product_packaging
      [27] => `p`.virtuemart_product_id
  )
  
  searched for cäp => I got my cäp. hmmmm.
  
  Ah I see it. You all use the version below 3.0.17.x and I added the fix there. Joomla changed this surprisingly in version j3.5.1

[sandomatyas]

Got the same problem with VM 3.2.2