Author Topic: 1271 - Illegal mix of collations o frontend search  (Read 1299 times)

Dreakman

  • Beginner
  • *
  • Posts: 16
    • Electro Dance Net
1271 - Illegal mix of collations o frontend search
« on: July 07, 2015, 17:05:09 pm »
Hello,
after entering non-latin characters in search module i have this error:

Code: [Select]
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

  • Virtuemart Projectleader
  • Administrator
  • Super Hero
  • *
  • Posts: 9327
  • VM3.2 Cached and Optimized
    • VM3 Extensions
  • VirtueMart Version: VirtueMart 3 on joomla 3
Re: 1271 - Illegal mix of collations o frontend search
« Reply #1 on: July 08, 2015, 20:35:37 pm »
Please give your versions, and php version and if you use the mb_ functions
I should fix your bug, please support the VirtueMart project and become a member
______________________________________
Extensions approved by the core team: http://extensions.virtuemart.net/

Dreakman

  • Beginner
  • *
  • Posts: 16
    • Electro Dance Net
Re: 1271 - Illegal mix of collations o frontend search
« Reply #2 on: July 10, 2015, 22:11:08 pm »
Hello and hanks for your reply!

Joomla: 3.4.1
VM: 3.0.8

I don't have any mb functions.
El Dance

Edwardocz

  • Beginner
  • *
  • Posts: 2
  • A beginner
Re: 1271 - Illegal mix of collations o frontend search
« Reply #3 on: September 18, 2015, 15:25:42 pm »
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

  • Jr. Member
  • **
  • Posts: 127
Re: 1271 - Illegal mix of collations o frontend search
« Reply #4 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.

copierx

  • Beginner
  • *
  • Posts: 7
Re: 1271 - Illegal mix of collations o frontend search
« Reply #5 on: January 18, 2017, 14:36:42 pm »
Thank you sandomatyas. your solution fixed my problem.

Milbo

  • Virtuemart Projectleader
  • Administrator
  • Super Hero
  • *
  • Posts: 9327
  • VM3.2 Cached and Optimized
    • VM3 Extensions
  • VirtueMart Version: VirtueMart 3 on joomla 3
Re: 1271 - Illegal mix of collations o frontend search
« Reply #6 on: January 18, 2017, 15:55:10 pm »
That means you had to disable this field to get it running? I would like to fix that.
I should fix your bug, please support the VirtueMart project and become a member
______________________________________
Extensions approved by the core team: http://extensions.virtuemart.net/

sandomatyas

  • Jr. Member
  • **
  • Posts: 127
Re: 1271 - Illegal mix of collations o frontend search
« Reply #7 on: January 28, 2017, 15:28:33 pm »
yes, that's right

Milbo

  • Virtuemart Projectleader
  • Administrator
  • Super Hero
  • *
  • Posts: 9327
  • VM3.2 Cached and Optimized
    • VM3 Extensions
  • VirtueMart Version: VirtueMart 3 on joomla 3
Re: 1271 - Illegal mix of collations o frontend search
« Reply #8 on: January 29, 2017, 12:29:24 pm »
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
I should fix your bug, please support the VirtueMart project and become a member
______________________________________
Extensions approved by the core team: http://extensions.virtuemart.net/

sandomatyas

  • Jr. Member
  • **
  • Posts: 127
Re: 1271 - Illegal mix of collations o frontend search
« Reply #9 on: July 06, 2017, 09:39:06 am »
Got the same problem with VM 3.2.2