Database restore does not retain emojis

Question

I am doing a naïve database backup and restore like this:

SCHEMA=${2:-mindlevel}
DB_HOST=$1
mysqldump -uroot -ppassword -h$DB_HOST --default-character-set=utf8mb4 --complete-insert --no-create-info $SCHEMA -r ${SCHEMA}_backup.sql 
mysqladmin -uroot -ppassword -h$DB_HOST -f drop $SCHEMA &&
mysql -uroot -ppassword -h$DB_HOST --default-character-set=utf8mb4 < ${SCHEMA}_schema.sql &&
mysql -uroot -ppassword -h$DB_HOST $SCHEMA --default-character-set0=utf8mb4 < ${SCHEMA}_backup.sql

But all emojis that I had before the restore are shown as ?, but if I enter an emoji again it is stored and shown correctly.

I have tried having --default-character-set=utf8 but with the same result.

UPDATE:

SHOW CREATE TABLE user;

CREATE TABLE `user` (
  `username` varchar(191) NOT NULL,
  `description` varchar(1024) DEFAULT NULL,
  `image` varchar(191) DEFAULT 'user.jpg',
  `score` int(11) DEFAULT 0,
  `level` int(11) DEFAULT 0,
  `created` bigint(20) NOT NULL,
  `last_active` bigint(20) DEFAULT NULL,
  PRIMARY KEY (`username`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

The backup file:

file mindlevel_backup.sql 
mindlevel_backup.sql: UTF-8 Unicode text, with very long lines

The top part of mindlevel_backup.sql:

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=

Any ideas?

score 1 · Answer 1 · answered Nov 03 '18 at 03:58

1

The column in the table also needs to be declared CHARACTER SET utf8mb4. Please provide SHOW CREATE TABLE and the first part of backup.sql.

answered Nov 03 '18 at 03:58

Rick James

135,179
13
127
222

If the column wasn't utf8mb4 then it wouldn't be able to store the emoji correctly after the restore either? But I'll update my question with your requests. – spydon Nov 03 '18 at 16:49
See "question mark" in https://stackoverflow.com/questions/38363566/trouble-with-utf8-characters-what-i-see-is-not-what-i-stored . And do the debugging mentioned there. – Rick James Nov 03 '18 at 16:58
I have done everything in there already. That is why I created a new question. It is so strange... – spydon Nov 03 '18 at 17:14
If `mindlevel_backup.sql` comes from `mysqldump`, it will start with a character set setting -- overriding your settings. Check it. – Rick James Nov 03 '18 at 17:21
Does it not respect the `--default-character-set=utf8mb4` which I have as a parameter? – spydon Nov 03 '18 at 17:31
@spydon - not the word "default". That implies there may be ways to override it. (There are.) – Rick James Nov 03 '18 at 17:33
How confusing, how do I check what it starts with? Tried to google but to no avail. However that didn't seem to be the problem, but the problem seem to have been something with reading up the dump again (see my answer). Thanks a lot for your patience and help! – spydon Nov 03 '18 at 17:39
Ah, now I understand what you meant. That is even stranger, it does the same thing in there as I did manually in the `-e` parameter of my answer (updated the question). – spydon Nov 03 '18 at 17:43

spydon · Accepted Answer · 2018-11-03T17:44:07.843

1

The solution was to change one line. From:

mysql -uroot -ppassword -h$DB_HOST $SCHEMA --default-character-set=utf8mb4 < ${SCHEMA}_backup.sql

To:

mysql -uroot -ppassword -h$DB_HOST $SCHEMA --default-character-set=utf8mb4 -e "SET NAMES utf8mb4; SOURCE"${SCHEMA}"_backup.sql;"

I don't know why that worked, so if anyone care to explain that to me I'll update the answer.

edited Nov 03 '18 at 17:44

answered Nov 03 '18 at 17:36

spydon

9,372
6
33
63

1

I had the same problem, I only added "--default-character-set=utf8mb4" to my `mysqldump` command in order to make it work. (I wasn't specifying the charset before). I think you don't have to use "set names" if you use mysqldump instead of mysql. – Tokeeen.com Jun 01 '19 at 07:00
as Tokeen said, using `--default-character-set=utf8mb4` when exporting and importing makes it work. This is: `mysqldump --default-character-set=utf8mb4 ... > dump.sql` and `mysql --default-character-set=utf8mb4 ... < dump.sql` – Jesus Monzon Legido Mar 27 '21 at 17:19

Database restore does not retain emojis

2 Answers2