Emoji like `🎥` will be changed to `?` after `wp db import`

wp-cli / db-command

Performs basic database operations using credentials stored in wp-config.php.

MIT License

71 stars 59 forks source link

Emoji like `🎥` will be changed to `?` after `wp db import` #144

Closed miya0001 closed 3 years ago

miya0001 commented 5 years ago

Some of emoji text will be garbed after wp db import and wp db export.

Following is an example to represent this issue. https://github.com/wp-cli/db-command/compare/master...emoji-issue

The first test to check 🎥 will be passed but second one that is ran after wp db import will be failed.

Thanks. :)

miya0001 commented 5 years ago

Ah, I found a solution to solve this problem as follows.

wp db export --default-character-set=utf8mb4

But I think this option should be passed as default. Because it is difficult to realize emojis are broken for users.

What do you think?

schlessera commented 5 years ago

@miya0001 I'm open to adding this as a default setting, but I'm unsure what the exact ramifications are. Could this have any adverse effects that make it worse for some users? Could this break in some circumstances, for example where this character set might just not be available?

sirtawast commented 5 years ago

@miya0001 I'm open to adding this as a default setting, but I'm unsure what the exact ramifications are. Could this have any adverse effects that make it worse for some users? Could this break in some circumstances, for example where this character set might just not be available?

What is the current default then?

miya0001 commented 5 years ago

@sirtawast

The default value of --default-character-set is utf8. https://dev.mysql.com/doc/refman/5.5/en/mysqldump.html#option_mysqldump_default-character-set

miya0001 commented 5 years ago

I couldn't found the solution to detect character set utf8 or utf8mb4.

wojsmol commented 5 years ago

@miya0001 See this make/core blog post.

morganhvidt commented 5 years ago

I just lost all my emojis when migrating a site 🤪I'll try changing the --default-character-set next time. It would be nice if it's possible to have it as default.

schlessera commented 5 years ago

Yes, indeed, we should prioritize this to avoid situations like that. I'll work on getting this into the next release.

dpacmittal commented 4 years ago

Just got burned by this bug. We migrated the site to another server and noticed the emojis missing after a couple of hours. So we'll have to re-export (using mysqldump) and import the database and lose a few hours of user-generated content.

schlessera commented 4 years ago

Sorry to hear that, @dpacmittal . I got stuck on refactoring the DB command to make it easier to add defaults across the board (https://github.com/wp-cli/db-command/pull/160).

I'll try to get back to this as soon as possible to fix.

mmirus commented 4 years ago

Could be wrong, but is this already solved by specifying the correct DB_CHARSET in your WP config?

jcatello commented 4 years ago

This is still a problem and occurring more recently.

we can use wp cli export and wp cli import and have broken emojis.

we can then run mysqldump and mysql to import and the emojis remain in tact.

DB_CHARSET is set properly in both the source and target wp-config.php

MaximeCulea commented 4 years ago

I have read the following post on this topic: https://mathiasbynens.be/notes/mysql-utf8mb4

And I conclude this is not only a question of charset, but also of collate, in my case I had to:

when doing exports between live and stagings sites, I had to precise the charset with wp cli export: 'wp db export --default-character-set=utf8mb4'
on the staging website, I changed live from PHPMyAdmin the charset and collate on tables, to utf8mb4 and utf8mb4_unicode_ci. It worked directly so I did the same on live site.
also, just to be sure, I also set proper charset and collate in wp-config

For some other people in the same case, both sites (export and import) need to be using the same charset and collate.