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

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

Answer 2

Finally, it works. It was an issue with stored procedures, that was still utf8 instead of utf8mb4 after the migration. It was a 2-steps solution.

1. As suggested by @mike-adamenko set my.cnf to have the following

[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

2. Execute in mysql:

   SET NAMES utf8mb4 COLLATE utf8mb4_unicode_ci;

3. Drop procedures involved, and create them again. They will be in utf8mb4. Can be checked with

SHOW PROCEDURE STATUS where name LIKE 'procedure_name';

Answer 3

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 4

You can follow the documentation available for MySQL to resolve your problem. Here's the MySQL documentation, that you could refer to.

Basically, your ALTER TABLE scripts can be changed as per the documentation mentioned above and then you could use the following parameter in your connection string for the changes to take effect.

jdbc:mysql://localhost/yourdatabasename?useUnicode=true&characterEncoding=UTF-8

Please don't forget to restart your MySQL services after making the character set and the encoding changes.