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?

Calvados

Ha, I thought you were trying to fiddle with your post title, but you actually mean encoding the character 🔥. I hate emoji... – Gnostic

This is first time I used non-symbolic emoji, 8-bit character was always enough for me. But end users enjoy emoji so gotta support it =) – Calvados

Try executing SET NAMES 'utf8mb4' COLLATE 'utf8mb4_unicode_ci' immediately after establishing your Connection. – Migrate

I found no way to set encoding on mysql 5. Gord's suggestion seems to be a workaround, but it works. Decided to use it. Would you post your solution as a separate answer? – Calvados

Answer 1

If you are using old version of mysql-connector-java, consider to upgrade to newer version. Upgrade version of mysql-connector-java from 5.1.31 to 5.1.49 solved my issue. My JDBC_URL is "jdbc:mysql://{HOST}:{PORT}/{DBNAME}?useUnicode=true&characterEncoding=utf-8";

Uropygium

Answer 2

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

Qumran

Answer 3

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!

Lodger