utf8mb4 in MySQL Workbench and JDBC

Question

I've been working with a UTF-8 encoded MySQL DB that now needs to be able to store 4-byte emojis, so I decided to change from utf8 encoding to utf8mb4:

ALTER DATABASE bstdb CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;
ALTER TABLE HISTORY CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE HISTORY CHANGE SOURCE_CONTEXT SOURCE_CONTEXT VARCHAR(2000) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
SET NAMES utf8mb4 COLLATE utf8mb4_unicode_ci;

And changed mysql.conf.d "character-set-server = utf8" to "character-set-server = utf8mb4"

After these steps, I am able to store emojis (as 💢),but only when the SQL query is executed in the MySQL console: When I try to launch the query from MySQL Workbench or from a Wildfly webapp, I am getting this error:

Error Code: 1366. Incorrect string value: '\xF0\x9F\x92\xA2' for column 'SOURCE_CONTEXT' at row 1

I assume I need to change the way the clients are connecting to the DB, but I have no clue on how. I've read something on using "useUnicode=yes" in JDBC, but does not work.

${bdpath:3306/bstdb?useUnicode=yes}

Edit: As suggested in comments, I tried with:

${bdpath:3306/bstdb?characterEncoding=UTF-8}

but no luck, I am getting the same "Incorrect string value: '\xF0\x9F\x92\xA2'" error.

Also tried

${bdpath:3306/bstdb?useUnicode=true&characterEncoding=utf8mb4&}

but it refuses to stablish a connection.

Any idea on how to configure MySQL workbench and/or JDBC/Wildfly?

MySQL version is 5.7.18

MySQL WorkBench version is 6.0.8

JDBC driver version is 5.1.34

Thanks!

Answer 1

Starting from MySQL Connector/J 5.1.47,

When UTF-8 is used for characterEncoding in the connection string, it maps to the MySQL character set name utf8mb4.

You can check docs here

Answer 2

Use characterEncoding=utf8 for jdbc url

jdbc:mysql://x.x.x.x:3306/db?useUnicode=true&characterEncoding=utf8

Also check that you have configured MySQL to work with utf8mb4

    [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

See here