News:

You may pay someone to create your store, or you visit our seminar and become a professional yourself with the silver certification

Main Menu

1271 - Illegal mix of collations o frontend search

Started by Dreakman, July 07, 2015, 17:05:09 PM

Previous topic - Next topic

Dreakman

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

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!
El Dance

Milbo

Please give your versions, and php version and if you use the mb_ functions
Should I fix your bug, please support the VirtueMart project and become a member
______________________________________
Extensions approved by the core team: http://extensions.virtuemart.net/

Dreakman

Hello and hanks for your reply!

Joomla: 3.4.1
VM: 3.0.8

I don't have any mb functions.
El Dance

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.
Should I fix your bug, please support the VirtueMart project and become a member
______________________________________
Extensions approved by the core team: http://extensions.virtuemart.net/


Milbo

Quote from: sandomatyas on May 03, 2016, 07:10:10 AM
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
Should I fix your bug, please support the VirtueMart project and become a member
______________________________________
Extensions approved by the core team: http://extensions.virtuemart.net/

sandomatyas