Error when try to store emoji

Question

I'm using sequelize as ORM for node.js to interact with mysql database.

My database charset is utf8mb4, utf8mb4_general_ci

My table charset is utf8mb4, utf8mb4_general_ci

My column TYPE is TEXT and charset is utf8mb4, utf8mb4_general_ci

Before executing any query, i'm executing following code:

Sequelize.query("SET NAMES utf8mb4");
Sequelize.query("SET CHARACTER SET utf8mb4");

After executing Sequelize.query("SHOW VARIABLES LIKE 'character_set_%'"), i've got:

[ [ RowDataPacket { Variable_name: 'character_set_client', Value: 'utf8mb4' },
    RowDataPacket { Variable_name: 'character_set_connection', Value: 'utf8mb4' },
    RowDataPacket { Variable_name: 'character_set_database', Value: 'utf8mb4' },
    RowDataPacket { Variable_name: 'character_set_filesystem', Value: 'binary' },
    RowDataPacket { Variable_name: 'character_set_results', Value: 'utf8mb4' },
    RowDataPacket { Variable_name: 'character_set_server', Value: 'utf8' },
    RowDataPacket { Variable_name: 'character_set_system', Value: 'utf8' },
    RowDataPacket {
      Variable_name: 'character_sets_dir',
      Value: 'c:\\openserver\\modules\\database\\MySQL-5.7-x64\\share\\charsets\\' } ],
  [ RowDataPacket { Variable_name: 'character_set_client', Value: 'utf8mb4' },
    RowDataPacket { Variable_name: 'character_set_connection', Value: 'utf8mb4' },
    RowDataPacket { Variable_name: 'character_set_database', Value: 'utf8mb4' },
    RowDataPacket { Variable_name: 'character_set_filesystem', Value: 'binary' },
    RowDataPacket { Variable_name: 'character_set_results', Value: 'utf8mb4' },
    RowDataPacket { Variable_name: 'character_set_server', Value: 'utf8' },
    RowDataPacket { Variable_name: 'character_set_system', Value: 'utf8' },
    RowDataPacket {
      Variable_name: 'character_sets_dir',
      Value: 'c:\\openserver\\modules\\database\\MySQL-5.7-x64\\share\\charsets\\' } ] ]

But when i'm trying to save string that containing emoji, i ran into an error:

Unhandled rejection SequelizeDatabaseError: ER_TRUNCATED_WRONG_VALUE_FOR_FIELD: Incorrect string value: '\xF0\x9F\x91\x8D\xF0\x9F...' for column 'content' at row 1

Please provide `SHOW CREATE TABLE` – Rick James Jan 25 '17 at 23:42 — Rick James, Jan 25 '17 at 23:42

score 4 · Answer 1 · answered Jan 25 '17 at 18:01

4

I ran into this issue also and solved it easily, you only need to set your database to utf8_general_ci. And when doing the connection at node.js you need to add the "charset" parameter:

var pool = mysql.createPool({
    host: "host",
    user: "username",
    password: "password",
    database: "database",
    connectionLimit: xxxx,
    charset : 'utf8mb4'
});

This did the trick for me.

answered Jan 25 '17 at 18:01

Alan

361
3
22

1

`utf8_general_ci` is a _collation_ for the _charater set_ utf8, which does _not_ handle Emoji. – Rick James Jan 25 '17 at 23:38

score 0 · Answer 2 · answered Feb 19 '18 at 05:20

I had the same problem trying to insert emoji into MySQL with Express and Sequelize. You can confirm your current character set like

SHOW VARIABLES WHERE Variable_name LIKE 'character\_set\_%' OR Variable_name LIKE 'collation%';

The default connection output from the above looks like this

character_set_client      utf8
character_set_connection  utf8
character_set_database    utf8mb4
character_set_filesystem  binary
character_set_results     utf8
character_set_server      utf8mb4
character_set_system      utf8
collation_connection      utf8_general_ci
collation_database        utf8mb4_unicode_ci
collation_server          utf8mb4_unicode_ci

If your MySQL config file /etc/my.cnf looks like

[client]
default-character-set = utf8mb4

[mysql]
default-character-set = utf8mb4

[mysqld]
character-set-client-handshake = FALSE
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci

But if you run

SET NAMES utf8mb4;

Then the SHOW VARIABLES WHERE... from above again it will update to utf8mb4 across the board.

character_set_client      utf8mb4
character_set_connection  utf8mb4
character_set_database    utf8mb4
character_set_filesystem  binary
character_set_results     utf8mb4
character_set_server      utf8mb4
character_set_system      utf8
collation_connection      utf8mb4_general_ci
collation_database        utf8mb4_unicode_ci
collation_server          utf8mb4_unicode_ci

So, the solution I found is to put the following on the first line of my User model:

// make sure db/client/connection can support emoji
sequelize.query("SET NAMES utf8mb4;");
// confirm settings
sequelize.query("SHOW VARIABLES LIKE 'character_set_%'").then(function(data) {
     console.log(data);
});

Error when try to store emoji

2 Answers2