JDBC and utf8mb4 encoding the emoji symbol 🔥

Question

I use Mysql 5.7.18 and I faced an issue with emoji symbols when updating from JDBC.

Table creation: body is typed utf8 and localizations is utf8mb4

CREATE TABLE `my_table` (
  `body` TEXT NOT NULL,
  `localizations` TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8;

Now I use SequelPro to insert data. To do it correctly I press Database -> View using encoding -> utf8mb4. Insertion code:

INSERT INTO my_table(body, localizations) VALUES ('test', '🔥');

Then I try to load it from jdbc. Here is connection string part currently used in project (formatting is used here only for convenient reading):

?useEncoding=true
&characterEncoding=UTF-8

It successfully loads localizations from database. But updating table from JDBC returns error:

SQLException: Incorrect string value: '\xF0\x9F\x94\xA5"}...' for column 'localizations'

I suppose that's because of connection encoding. SequelPro has a Show server variables option and here is the diff between utf8 and utf8mb4:

character_set_client = utf8mb4
character_set_connection = utf8mb4
character_set_results = utf8mb4
collation_connection = utf8mb4_general_ci

So I try to modify connection string:

?useEncoding=true
&characterEncoding=UTF-8
&sessionVariables=
  character_set_client=utf8mb4,
  character_set_connection=utf8mb4,
  character_set_results=utf8mb4,
  collation_connection=utf8mb4_general_ci

Still the same issue. Any advice please?

Answer 1

The error message says (I think) that JDBC was not told utf8mb4...

I suspect useEncoding was a typo:

?useUnicode=yes&characterEncoding=UTF-8  -- in the getConnection() call. 

http://mysql.rjweb.org/doc.php/charcoll#best_practice

Answer 2

I was facing the same issue. I found out in my session variables that

character_set_client=utf8mb4

but

character_set_server=utf8

As suggested in links below,

https://dev.mysql.com/doc/refman/5.7/en/charset-server.html

https://docs.oracle.com/cd/E17952_01/connector-j-en/connector-j-reference-charsets.html

I removed characterEncoding, charSet and useEncoding properties from JDBC config and restarted mysql server with

--character-set-server=utf8mb4

Worked like a Charm!