Forwarded to devs Plesk is losing unicode characters > FFFF in mysql dump

[Sage Pointer]

TITLE:

Plesk is losing unicode characters > FFFF in mysql dump​

PRODUCT, VERSION, OPERATING SYSTEM, ARCHITECTURE:

Plesk Onyx 17.8.11, Linux Ubuntu 16.04 x64, MySQL 5.7.25-0ubuntu0.16.04.2​

PROBLEM DESCRIPTION:

When exporting MySQL database with Export/Import function or Backup Manager, it is dumped in utf8 charset (not utf8mb4), and all symbols > FFFF (for example, emoji and some CJK symbols) are lost and replaced with ? in dump.
So, Plesk Backup feature is lossy and cannot be used in production environment, if any of your database uses text fields with emoji, for example.​

STEPS TO REPRODUCE:

1. Create database in Plesk
2. Run a SQL query under this database:
CREATE TABLE emoji (data varchar(255) CHARACTER SET utf8mb4 DEFAULT NULL);
INSERT INTO emoji (data) VALUES (UNHEX('f09f9880')), ('Test');
3. Export this database and import it back.​

ACTUAL RESULT:

Instead of emoji symbol, it will be question mark inside a table. Latin text is preserved as is.​

EXPECTED RESULT:

Emoji symbol should be preserved and not replaced with question mark.​

ANY ADDITIONAL INFORMATION:

To solve this, you can for example run mysqldump with --default-character-set=utf8mb4 option.​

YOUR EXPECTATIONS FROM PLESK SERVICE TEAM:

Confirm bug​

 

[IgorG]

Thank you for report. Bugreport PPPM-10414 was submitted.

From developer:

As a possible workaround, I suggest putting the following file:

# cat /etc/mysql/conf.d/utf8mb4.cnf
[client]
default-character-set = utf8mb4

And use mysqldump from the command line directly. Instead of configuration file it is possible to use command line option --default-character-set=utf8mb4.
I do not see any problem with importing of dumps with 4-byte Unicode characters through panel web UI.

 

[FrankTheMan]

Bump, this seems to have not been fixed as of yet? we still encounter this bug when migration sites from plesk server to plesk server via the migration tool.

 

[Denis Gomes Franco]

Bump too @FrankTheMan.

Looks like this issue was not fixed and it seems to affect the Migration & Transfer Manager function as well. Last week I moved a client's subscription from one Plesk server to another and the database tables lost their emojis. Fortunately it was only a few minor details so the client didn't even noticed.

So I'm assuming that Plesk runs a mysqldump while transferring the subscription and it is not using the utf8mb4 charset as suggested as a workaround (BTW I found the same solution over at StackOverflow: mysqldump with utf8 can not export the right emojis string)

 

[IgorG]

Fixed in Plesk Obsidian 18.0.46:

The plesk db command line utility no longer uses the --no-defaults option when calling the mysql or mysqldump commands. (PPPM-10414)

Emoji characters stored in MySQL databases are now backed up and restored properly. (PPPM-10414)