Author Topic: Should I convert tables to utf8mb4? (currently utf8)  (Read 367 times)

EvanGR

  • Jr. Member
  • **
  • Posts: 108
Should I convert tables to utf8mb4? (currently utf8)
« on: August 21, 2018, 09:59:14 am »
As the title says... can I safely convert the Virtuemart tables from utf8 to utf8mb4?

Thanks

Joomla 3.8.10/VM3.2.15

Studio 42

  • Contributing Developer
  • Sr. Member
  • *
  • Posts: 3334
  • Joomla & Virtuemart addon developper
    • Studio 42 - Virtuemart & Joomla extentions
  • VirtueMart Version: 2.6 & 3.0.x.y
Re: Should I convert tables to utf8mb4? (currently utf8)
« Reply #1 on: August 21, 2018, 11:59:08 am »
Why ?

EvanGR

  • Jr. Member
  • **
  • Posts: 108
Re: Should I convert tables to utf8mb4? (currently utf8)
« Reply #2 on: August 21, 2018, 12:04:39 pm »
For one or more of the following reasons:

1) Joomla 3 switched to utf8mb4

2) https://medium.com/@adamhooper/in-mysql-never-use-utf8-use-utf8mb4-11761243e434


jenkinhill

  • UK Web Developer & Consultant
  • Global Moderator
  • Super Hero
  • *
  • Posts: 26973
  • Always on vacation
    • Jenkin Hill Internet
Re: Should I convert tables to utf8mb4? (currently utf8)
« Reply #3 on: August 21, 2018, 13:05:25 pm »
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

Jenkin Hill Internet,
Lowestoft, Suffolk, UK

Unsolicited PMs/emails will be ignored.

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

Currently using VM..3.4.0.9935 on Joomla 3.8.12 PHP 7.0.31

EvanGR

  • Jr. Member
  • **
  • Posts: 108
Re: Should I convert tables to utf8mb4? (currently utf8)
« Reply #4 on: August 21, 2018, 13:23:17 pm »
Joomla 3.x tables use utf8mb4_general_ci and the VirtueMart tables use utf8_general_ci.

Ok.

Quote
Never 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)



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

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

  • 3rd party VirtueMart Developer
  • Super Hero
  • *
  • Posts: 8002
  • Virtuemart, Joomla & php developer
    • GJC Web Design
  • VirtueMart Version: 2.6.22 & 3.2.14
Re: Should I convert tables to utf8mb4? (currently utf8)
« Reply #5 on: August 21, 2018, 17:07:22 pm »
Quote
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.

Jenks had his tongue firmly thrust in his cheek when he wrote that
GJC Web Design
VirtueMart and Joomla Developers - php developers http://www.gjcwebdesign.com
VM3 AusPost Shipping Plugin - e-go Shipping Plugin - VM3 Postcode Shipping Plugin - Radius Shipping Plugin - VM3 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
http://extensions.joomla.org/profile/profile/details/67210
Contact for any VirtueMart or Joomla development & customisation

jenkinhill

  • UK Web Developer & Consultant
  • Global Moderator
  • Super Hero
  • *
  • Posts: 26973
  • Always on vacation
    • Jenkin Hill Internet
Re: Should I convert tables to utf8mb4? (currently utf8)
« Reply #6 on: August 21, 2018, 17:11:21 pm »
 8) 8) 8) 8)
Kelvyn

Jenkin Hill Internet,
Lowestoft, Suffolk, UK

Unsolicited PMs/emails will be ignored.

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

Currently using VM..3.4.0.9935 on Joomla 3.8.12 PHP 7.0.31

Studio 42

  • Contributing Developer
  • Sr. Member
  • *
  • Posts: 3334
  • Joomla & Virtuemart addon developper
    • Studio 42 - Virtuemart & Joomla extentions
  • VirtueMart Version: 2.6 & 3.0.x.y
Re: Should I convert tables to utf8mb4? (currently utf8)
« Reply #7 on: August 21, 2018, 17:15:02 pm »
@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

  • 3rd party VirtueMart Developer
  • Super Hero
  • *
  • Posts: 8002
  • Virtuemart, Joomla & php developer
    • GJC Web Design
  • VirtueMart Version: 2.6.22 & 3.2.14
Re: Should I convert tables to utf8mb4? (currently utf8)
« Reply #8 on: August 21, 2018, 17:31:03 pm »
Quote
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.

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 http://www.gjcwebdesign.com
VM3 AusPost Shipping Plugin - e-go Shipping Plugin - VM3 Postcode Shipping Plugin - Radius Shipping Plugin - VM3 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
http://extensions.joomla.org/profile/profile/details/67210
Contact for any VirtueMart or Joomla development & customisation

Studio 42

  • Contributing Developer
  • Sr. Member
  • *
  • Posts: 3334
  • Joomla & Virtuemart addon developper
    • Studio 42 - Virtuemart & Joomla extentions
  • VirtueMart Version: 2.6 & 3.0.x.y
Re: Should I convert tables to utf8mb4? (currently utf8)
« Reply #9 on: August 21, 2018, 17:39:10 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

  • Contributing Developer
  • Sr. Member
  • *
  • Posts: 3334
  • Joomla & Virtuemart addon developper
    • Studio 42 - Virtuemart & Joomla extentions
  • VirtueMart Version: 2.6 & 3.0.x.y
Re: Should I convert tables to utf8mb4? (currently utf8)
« Reply #10 on: August 23, 2018, 18:02:41 pm »
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