VirtueMart Forum

VirtueMart 2 + 3 => Administration & Configuration => Topic started by: EvanGR on August 21, 2018, 09:59:14 am

Title: Should I convert tables to utf8mb4? (currently utf8)
Post by: EvanGR 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
Title: Re: Should I convert tables to utf8mb4? (currently utf8)
Post by: Studio 42 on August 21, 2018, 11:59:08 am
Why ?
Title: Re: Should I convert tables to utf8mb4? (currently utf8)
Post by: EvanGR 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

Title: Re: Should I convert tables to utf8mb4? (currently utf8)
Post by: 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.  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.
Title: Re: Should I convert tables to utf8mb4? (currently utf8)
Post by: EvanGR 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
Title: Re: Should I convert tables to utf8mb4? (currently utf8)
Post by: GJC Web Design 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
Title: Re: Should I convert tables to utf8mb4? (currently utf8)
Post by: jenkinhill on August 21, 2018, 17:11:21 pm
 8) 8) 8) 8)
Title: Re: Should I convert tables to utf8mb4? (currently utf8)
Post by: Studio 42 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).
Title: Re: Should I convert tables to utf8mb4? (currently utf8)
Post by: GJC Web Design 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
Title: Re: Should I convert tables to utf8mb4? (currently utf8)
Post by: Studio 42 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.
Title: Re: Should I convert tables to utf8mb4? (currently utf8)
Post by: Studio 42 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