Author Topic: MySQL 1253 collation error after updating Virtuemart from 3.0.12 to 3.0.18  (Read 1574 times)

baijianpeng

  • Beginner
  • *
  • Posts: 23
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:

Quote
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.
http://www.joomlagate.com/

Chinese Joomla Users Portal

Milbo

  • Virtuemart Projectleader
  • Administrator
  • Super Hero
  • *
  • Posts: 9062
  • VM3.2 Cached and Optimized
    • VM3 Extensions
  • VirtueMart Version: VirtueMart 3 on joomla 3
Re: MySQL 1253 collation error after updating Virtuemart from 3.0.12 to 3.0.18
« Reply #1 on: February 20, 2017, 15:51:10 pm »
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.
I should fix your bug, please support the VirtueMart project and become a member
______________________________________
Extensions approved by the core team: http://extensions.virtuemart.net/

baijianpeng

  • Beginner
  • *
  • Posts: 23
Re: MySQL 1253 collation error after updating Virtuemart from 3.0.12 to 3.0.18
« Reply #2 on: February 21, 2017, 00:49:01 am »
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:

Quote
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.

http://www.joomlagate.com/

Chinese Joomla Users Portal

jenkinhill

  • UK Web Developer & Consultant
  • Global Moderator
  • Super Hero
  • *
  • Posts: 25909
  • Always on vacation
    • Jenkin Hill Internet
Re: MySQL 1253 collation error after updating Virtuemart from 3.0.12 to 3.0.18
« Reply #3 on: February 21, 2017, 12:11:53 pm »
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+
Kelvyn

Jenkin Hill Internet,
Keswick, Lake District

Unsolicited PMs/emails will be ignored.

Please mention your VirtueMart, Joomla and PHP versions when asking a question in this forum

Currently using VM3.2.3.9579 on Joomla 3.7.3 PHP 7.0.19

Testing VMVM3.2.3.9587 on J!3.7.3

baijianpeng

  • Beginner
  • *
  • Posts: 23
Re: MySQL 1253 collation error after updating Virtuemart from 3.0.12 to 3.0.18
« Reply #4 on: February 22, 2017, 02:57:20 am »
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.
http://www.joomlagate.com/

Chinese Joomla Users Portal