Hi,
I suggest that MySQL databases should be created with collation utf8mb4_unicode_ci instead of utf8_general_ci.
First, why utf8mb4 instead of utf8?
- utf8 can lead to data loss (truncated data)
- utf8 can lead to critical security issues (see more in the link at the bottom)
- it supports all of Unicode which is required in a worldwide project such as ProjeQtOr to fully support asian languages for example, and also emojis
Then, why _unicode_ci over _general_ci?
- _unicode_ci is better at sorting (you will see almost no differences in languages such as English or French but this should be helpful in other languages, such as German).
- _general_ci has however slightly better performances
What can be done?
1. Make new ProjeQtOr installations utf8mb4 by default if MySQL is selected and MySQL/MariaDB version >= 5.5.3 (released more than 6 years ago). In prerequisities, you can still require MySQL v5+ but you should recommand v5.5.3 or higher.
2. utf8mb4 is backwards compatible with utf8 so existing databases can be migrated if MySQL/MariaDB version >= 5.5.3 (released more than 6 years ago) B) Edit: Ask the administrator to make a full backup before this step.
When MySQL database is in utf8mb4, connect with SET NAMES 'utf8mb4';
You can complete your reading with https://mathiasbynens.be/notes/mysql-utf8mb4
It explains why you should use utf8mb4 and how to migrate.
I hope I convinced you,
Thanks for reading,
Hi,
Request recorded as Ticket #2457.
This does not seem to be an urgent feature as Chinees or Japanese users never requested for this.
Also, due to heavy work requested (mostly on testing) and few improvement expected, this is not a priority evolution.
Regards.
If migration is too difficult, making utf8mb4 the default for newly created database should be a pretty straightforward task.
Thanks anyway,
making utf8mb4 the default for newly created database should be a pretty straightforward task.
Not so much, as both cases should work for new upgrade scripts...
It shouldn't be needed to put charset in SQL update scripts, the DBMS will guess it from the database charset and collation. Plus, the "utf8mb4" is only available in MySQL. The "utf8mb4" is the same as "utf8" in PostgreSQL. However "utf8" in MySQL is something different from other DBMS "utf8" such as PostgreSQL.
I think I will try a migration in my pre-prod environment and will tell you how it's going.
Security of our application is important for us.
After investigating this issue, I discovered that you were actually storing cp1252 data into UTF8 tables.
To be more precise, you store "default character set" of the DBMS, which for MySQL/MariaDB is cp1252 if it was never changed.
You have to do a "SET names utf8;" before making any query on the database, which never happens as $enforceUTF8 is never set. This is weird because in your .sql scripts, all your tables are created in UTF8, so nothing should have prevented connecting to database in UTF8 since you were using UTF8 since the beginnings of ProjeQtOr.
Yes, it is an issue we already identified.
Although tables are defined in UTF8, if server is on windows an is not correctly configured in UTF8 data is stored in CP1252 (windows format).
It is transparent in ProjeQtOr, because reverse transfomation is automatically done by MySql when reading data.
But if you read data through other tool (such as PhpMyAdmin) you see that accentuated chars are not correct.
This issue does not happen on Linux, always working by defaut on UTF8.
We fixed this in V4.4 by forcing "set names UTF8" on each connection.
But we force this (by setting $enforceUTF8 to true) only for new installations, to avoid erroneous display, as setting this on not converted database would result in lack of reverse transformation for existing data.
I had tests to be sure...
And very strangely it seems that $enforceUTF8 is not positionned any more by default on new installs : I will investigate
But on my new install, all was fine, using MySql 5.7... with defaut configuration (no specific UTF8 config as I used to do on previous MySql versions)
No issue with phpMyAdmin, export to utf8 fine, all OK.
And setting $enforceUTF8 does not change anything.
I can confirm I installed ProjeQtOr on version 4.5.7 and never received the $enforceUTF8 parameter. I installed ProjeQtOr on a CentOS machine and the defaults character set for MySQL are still latin1/cp1252, this is not dependant on the OS but on the MySQL/MariaDB config file, which always defaults to cp1252.
Edit: Maybe MySQL 5.7 changes this default? I don't know, I'm using MariaDB.
Found the issue.
Will be fixed on next patch / version
Found out how to fix the issue (or how to convert the cp1252 data stored as UTF8, as UTF8MB3).
Note that it will NOT work if you have a mixed cp1252/UTF8 database. It was my case for 8 lines related to the same activity in history table, I don't know how this happened but the activity didn't exist anymore so I just deleted the lines.
This is a proof of concept and should only be used by advanced users on a dev/pre-prod environment before going production.
1) Export your database with mysqldump command line. You should get a file in UTF8.
2) Open the .sql file with Notepad++ or a software which is able to convert/encode from/to CP1252/ANSI and UTF8.
3) In "Encode" menu, you should see "Encode as UTF-8 (without BOM)" ticked.
4) Tick "Convert to ANSI". Nothing should have changed.
5) Tick "Encode as UTF-8 (without BOM)". Magic, all your characters should be converted.
6) $enforceUTF8=true; in ProjeQtOr config file.
7) Run the .sql file on your database or copy the contents of the .sql file to your query window and execute. It will fail if you had UTF8 characters before converting in your database. MySQL will tell you which lines fail.
8) Test this database on your dev/pre-prod environment.
So far so good during my tests.
Thanks for sharing this trick.