1237 error on vm search

[pm4698]

Hello there,

Joomla 3.6.5
Virtuemart 3.0.18

After a search of a term in search module i get blank page with this error:

Code Select Expand

1267 - Illegal mix of collations (utf8_general_ci,IMPLICIT) and (utf8mb4_general_ci,COERCIBLE) for operation 'like' SQL=SELECT SQL_CALC_FOUND_ROWS p.`virtuemart_product_id` , IF(pp.override, pp.product_override_price, pp.product_price) as product_price FROM `#__virtuemart_products` as p INNER JOIN `#__virtuemart_products_el_gr` as l using (`virtuemart_product_id`) LEFT JOIN `#__virtuemart_product_shoppergroups` as ps ON p.`virtuemart_product_id` = `ps`.`virtuemart_product_id` LEFT JOIN `#__virtuemart_product_categories` as pc ON p.`virtuemart_product_id` = `pc`.`virtuemart_product_id` LEFT JOIN `#__virtuemart_categories` as c ON c.`virtuemart_category_id` = `pc`.`virtuemart_category_id` LEFT JOIN `#__virtuemart_categories_el_gr` as cl ON cl.`virtuemart_category_id` = `pc`.`virtuemart_category_id` LEFT JOIN `#__virtuemart_product_prices` as pp ON p.`virtuemart_product_id` = pp.`virtuemart_product_id` WHERE ((`l`.product_name LIKE "%IAMS%ADULT%LARGE%BREED%MΞ•%ΚΟ΀ΟΞ%" AND `l`.product_name LIKE "%ΟΞ₯Ξ›ΞŸ%12KG%" OR `p`.product_sku LIKE "%IAMS%ADULT%LARGE%BREED%MΞ•%ΚΟ΀ΟΞ%" AND `p`.product_sku LIKE "%ΟΞ₯Ξ›ΞŸ%12KG%" OR `l`.product_s_desc LIKE "%IAMS%ADULT%LARGE%BREED%MΞ•%ΚΟ΀ΟΞ%" AND `l`.product_s_desc LIKE "%ΟΞ₯Ξ›ΞŸ%12KG%" OR `l`.product_desc LIKE "%IAMS%ADULT%LARGE%BREED%MΞ•%ΚΟ΀ΟΞ%" AND `l`.product_desc LIKE "%ΟΞ₯Ξ›ΞŸ%12KG%" OR `category_description` LIKE "%IAMS%ADULT%LARGE%BREED%MΞ•%ΚΟ΀ΟΞ%" AND `category_description` LIKE "%ΟΞ₯Ξ›ΞŸ%12KG%") 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`= "1" OR `ps`.`virtuemart_shoppergroup_id` IS NULL ) AND p.`published`="1" ) group by p.`virtuemart_product_id` ORDER BY `product_price` ASC, `virtuemart_product_id` ASC LIMIT 0, 18

I clicked fix database in Manage of Joomla, i also checked tables virtuemart_products, virtuemart_products_el_gr, virtuemart_product_shoppergroups, virtuemart_categories, virtuemart_categories, virtuemart_categories_el_gr and virtuemart_product_prices and those tables have the same collation.

From joomla settings i have this data:
Database Version:   5.6.17
Database Collation:   utf8_general_ci
Database Connection Collation:   utf8mb4_general_ci

Any ideas?

[Milbo]

yeh, either all your tables use mb4 or none. I wrote a fallback for the reviews, but in this case, you have only vm tables.

Maybe the problem is here "I clicked fix database in Manage of Joomla", when it converted most, but not all vm tables to utf8mb4, then you get in trouble.

[pm4698]

But my talbles are not mb4 but utf8-general!