Search code examples
javamysqljdbcmysql-workbenchutf8mb4

utf8mb4 in MySQL Workbench and JDBC

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!

Solution

  • 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

    1. Execute in mysql:

      SET NAMES utf8mb4 COLLATE utf8mb4_unicode_ci;

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

    SHOW PROCEDURE STATUS where name LIKE 'procedure_name';