MySQL 1253 collation error after updating Virtuemart from 3.0.12 to 3.0.18

[baijianpeng]

I built a website with a QuickStart package of a template, which had already installed Virtuemart v3.0.12 on Joomla 3.6.2.

Then after installation of that quickstart package, the first thing I want to do is upgrade/update the Joomla core and extensions.

After I update VM to 3.0.18, I went to frontend "shop" page, those listings can be shown up normally. But when I click on one product and tried to see its detail page, I got following error message instead of the nornal product page:

1253
COLLATION 'utf8_general_ci' is not valid for CHARACTER SET 'utf8mb4' SQL=SELECT `u`.*,`pr`.*,`l`.`product_name`,`rv`.`vote`, IFNULL(`u`.`name` COLLATE utf8mb4_general_ci, `pr`.`customer` COLLATE utf8_general_ci) AS customer FROM `#__virtuemart_rating_reviews` AS `pr` INNER JOIN `#__virtuemart_products_en_gb` AS `l` ON `l`.`virtuemart_product_id` = `pr`.`virtuemart_product_id` LEFT JOIN `#__virtuemart_rating_votes` AS `rv` on (`pr`.`virtuemart_rating_vote_id` IS NOT NULL AND `rv`.`virtuemart_rating_vote_id`=`pr`.`virtuemart_rating_vote_id` ) XOR (`pr`.`virtuemart_rating_vote_id` IS NULL AND (`rv`.`virtuemart_product_id`=`pr`.`virtuemart_product_id` and `rv`.`created_by`=`pr`.`created_by`) ) LEFT JOIN `#__users` AS `u`   ON `pr`.`created_by` = `u`.`id` WHERE `pr`.`virtuemart_product_id` = "5" ORDER BY created_on DESC LIMIT 0, 3

Go Back Home

I have no idea how to fix this? What had caused this?

In order to help you to help me, I checked the "quickstart" package initial installation, I noticed that after updating Joomla core to v3.6.5, some tables in the DB uses "utf8mb4_unicode_ci" collation, but some others still uses "utf8_general_ci" collation. And, ALL Virtuemart tables use "utf8_general_ci" collations.

Do I need to manually change VM talbes' collation to "utf8mb4_unicode_ci" before updating it to v3.0.18? Or just leave it as "utf8_general_ci" and directly update it ?

Thank you.

[Milbo]

Hmm complex. Joomla added the mb4 tables last spring (I think j3.6.1). I had to add a code checking for this problem
model ratings.php line 94.

I think you should revert the ratings table to utf8_general_ci.

[baijianpeng]

Last night, I installed VM v3.0.19 (downloaded from dev.virtuemart.net ), I thought maybe latest version will fix DB issues. But, the result is, frontend listing page does NOT show product "title" now, only images listed. When click on the image to open detail page, I got:

404 The requested product does not exist.

Well, I think I had damaged the whole website. Since I still have the original "QuickStart" package which contains demo data, I want to start again from that package.

Before start over, I have 2 questions: I know the DB table collation for ALL VM tables are "utf8_general_ci" in the demo data.

1. Should I keep it as this, or manually change them to "utf8mb4_unicode_ci" BEFORE I update the VM component?
2. Should I directly install VM v3.0.18 over v3.0.12, or update to the next release each step like "v3.0.12 to 3.0.13 to 3.0.14 to 3.0.15 ... to 3.0.18" ?

Thank you.

[jenkinhill]

From what you wrote earlier, it is some Joomla tables that use utf8mb4_unicode_ci and all VirtueMart tables use utf8_general_ci  so the issue is with the Joomla part of the install package.

All too often we see problems when people us quickstart packages. It would be much better to make a native installation of Joomla3.6.5, then to install VirtueMart (I'd use 3.19.6), select the option to install sample data and then install your Joomla template and configure it as necessary, although template overrides written for VirtueMart 3.0.12 will probably need a little editing for use with VM3.0.18+

[baijianpeng]

Yes,  all my Joomla core tables use utf8mb4_unicode_ci and all VirtueMart tables use utf8_general_ci  .

Then I just leave them as this, did not change the collation of VM tables. Next, I installed VM v3.0.18 over v3.0.12, and successfully upated VM. Those frontend product detail pages can be opened properly.

Thank you.