News:

Support the VirtueMart project and become a member

Main Menu

Should I convert tables to utf8mb4? (currently utf8)

Started by EvanGR, August 21, 2018, 09:59:14 AM

Previous topic - Next topic

EvanGR

As the title says... can I safely convert the Virtuemart tables from utf8 to utf8mb4?

Thanks

Joomla 3.8.10/VM3.2.15


EvanGR


jenkinhill

Joomla 3.x tables use utf8mb4_general_ci and the VirtueMart tables use utf8_general_ci.  Never seen an issue with that, it works just fine and I see no reason to change collation.

To quote Adam Hooper in the article you link to: "If you need a database, don't use MySQL or MariaDB. Use PostgreSQL."  So maybe you should change to PostgreSQL.
Kelvyn
Lowestoft, Suffolk, UK

Retired from forum life November 2023

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

EvanGR

#4
Quote from: jenkinhill on August 21, 2018, 13:05:25 PM
Joomla 3.x tables use utf8mb4_general_ci and the VirtueMart tables use utf8_general_ci.

Ok.

QuoteNever seen an issue with that, it works just fine and I see no reason to change collation.

I've been saying exactly the same about the Joomla 2.5/VM2.6 e-commerce site I am now forced to migrate. We share the same mindset, why mess with something that is already working as it should?

(then again... MySQL's UTF8 mode is kinda broken... and that, in turn, creates several security vulnerabilities...
https://www.acunetix.com/vulnerabilities/web/mysql-utf8-4-byte-truncation)



QuoteTo quote Adam Hooper in the article you link to: "If you need a database, don't use MySQL or MariaDB. Use PostgreSQL."  So maybe you should change to PostgreSQL.

That seems like asking for a lot more trouble than it's worth, even for plain Joomla 3 installations.

https://joomla.stackexchange.com/questions/688/can-i-use-postgresql-with-joomla-3-3/690#690

GJC Web Design

QuoteTo quote Adam Hooper in the article you link to: "If you need a database, don't use MySQL or MariaDB. Use PostgreSQL."  So maybe you should change to PostgreSQL.

Jenks had his tongue firmly thrust in his cheek when he wrote that
GJC Web Design
VirtueMart and Joomla Developers - php developers https://www.gjcwebdesign.com
VM4 AusPost Shipping Plugin - e-go Shipping Plugin - VM4 Postcode Shipping Plugin - Radius Shipping Plugin - VM4 NZ Post Shipping Plugin - AusPost Estimator
Samport Payment Plugin - EcomMerchant Payment Plugin - ccBill payment Plugin
VM2 Product Lock Extension - VM2 Preconfig Adresses Extension - TaxCloud USA Taxes Plugin - Virtuemart  Product Review Component
https://extensions.joomla.org/profile/profile/details/67210
Contact for any VirtueMart or Joomla development & customisation

jenkinhill

Kelvyn
Lowestoft, Suffolk, UK

Retired from forum life November 2023

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

Studio 42

@jenkinhill, it's not true that the current collation have no trouble. I wrrte for some website a component to use Google cloud translator and sometime the returned string fails because collation(the site have hebrew and Arabic language and the customer use many spécial chars) and return mysql error 1267, and this is a collation error.
I added a UTF8 checker
$isUTF = mb_detect_encoding($translatedText, 'UTF-8', true);
and this check for strict UTF8 validity but mysql cannot save it, so i think that EvanGR is right, but  i dont know if changing to utf8mb4 solve the issue(read the full comments on the linked article).

GJC Web Design

Quotesometime the returned string fails because collation(the site have hebrew and Arabic language and the customer use many spécial chars) and return mysql error 1267, and this is a collation error.

but has to said very much an edge case...   I haven't had any problems so far even with Hebrew sites
GJC Web Design
VirtueMart and Joomla Developers - php developers https://www.gjcwebdesign.com
VM4 AusPost Shipping Plugin - e-go Shipping Plugin - VM4 Postcode Shipping Plugin - Radius Shipping Plugin - VM4 NZ Post Shipping Plugin - AusPost Estimator
Samport Payment Plugin - EcomMerchant Payment Plugin - ccBill payment Plugin
VM2 Product Lock Extension - VM2 Preconfig Adresses Extension - TaxCloud USA Taxes Plugin - Virtuemart  Product Review Component
https://extensions.joomla.org/profile/profile/details/67210
Contact for any VirtueMart or Joomla development & customisation

Studio 42

Quote from: GJC Web Design on August 21, 2018, 17:31:03 PM

but has to said very much an edge case...   I haven't had any problems so far even with Hebrew sites
Me too, the first time i had this problem, it was for 2 days only.
And i created and use the component since 2 months now, it's why i had not understand before that the problem was the bad UTF8 colation.
I need to check with my customer, if i can do a backup and change colation to check if with utf8mb4  the string are saved to the product language table.
And i dont think that the problem is in the hebrew or arabic language, but in some special char. He replaced for eg. Quote in title because VIrtuemart bugs to save real double quote and use other special char. So i mean that the problem are in this special chars, because it break in french translation too.

Studio 42

Hi,
I have set collation to utf8mb4_general_ci on my customer product french table that was giving error on google clod translator.
All is now working !
So yes Virtuemart should change to utf8mb4_general_ci

micropalla

Same problem for me.
I add it also here just because the other topic is more generic.
I receive this error: 1253 - COLLATION 'utf8_general_ci' is not valid for CHARACTER SET 'utf8mb4'

What can I do?

Studio 42


pinochico

to Patrick and all:

I confirm...

A lot of developpers don't understand problem with czech, polish, slovak, ... etc language, because speak and use only english.
Thanks for Joomla now, I would like to VM too :)
www.minijoomla.org  - new portal for Joomla!, Virtuemart and other extensions
XML Easy Feeder - feeds for FB, GMC,.. from products, categories, orders, users, articles, acymailing subscribers and database table
Virtuemart Email Manager - customs email templates
Import products for Virtuemart - from CSV and XML
Rich Snippets - Google Structured Data
VirtueMart Products Extended - Slider with products, show Others bought, Products by CF ID and others filtering products

Kuubs

Quote from: micropalla on July 13, 2022, 10:56:06 AM
Same problem for me.
I add it also here just because the other topic is more generic.
I receive this error: 1253 - COLLATION 'utf8_general_ci' is not valid for CHARACTER SET 'utf8mb4'

What can I do?

How did you fix this issue in the end??