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!

Mike Adamenko · Answer 1 · 2018-07-31T15:07:11.820

11

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

edited Jul 31 '18 at 15:07

answered Jun 16 '17 at 14:40

Mike Adamenko

2,944
1
15
28

11

The connection cannot be established at all when using the "characterEncoding=utf8mb4" parameter: : "java.sql.SQLException: Unsupported character encoding 'utf8mb4'." – motagirl2 Jun 16 '17 at 14:51
1

You specify the Java encoding here, thus you should use `UTF-8`. – Usagi Miyamoto Jun 16 '17 at 14:55
No, she doesn't need utf8 encoding – Mike Adamenko Jun 16 '17 at 15:08
@MikeAdamenko "characterEncoding=utf8mb4" definitely doesn't work. – Joshua Davis Apr 10 '18 at 22:31

score 7 · Answer 2 · answered Oct 19 '18 at 14:16

7

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

answered Oct 19 '18 at 14:16

Alaa Nassef

280
2
13

score 6 · Accepted Answer · answered Jun 19 '17 at 11:37

6

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.

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

Execute in mysql:

SET NAMES utf8mb4 COLLATE utf8mb4_unicode_ci;
Drop procedures involved, and create them again. They will be in utf8mb4. Can be checked with

SHOW PROCEDURE STATUS where name LIKE 'procedure_name';

answered Jun 19 '17 at 11:37

motagirl2

589
1
10
21

1

Missed that configuration file part in my answer, that's a learning for me! – N00b Pr0grammer Jun 20 '17 at 05:53
what do you mean by "drop procedures involved" ? and for your 3rd point, can you give an example of what the `procedure_name` is? Getting empty results :( – Jonathan Laliberte Jun 09 '18 at 12:18
By "procedures involved" I mean those that were custom-created in that given database, by using the "CREATE PROCEDURE procedure_name etc etc" statement ("procedure_name" is the name you give to that procedure, as in a function name) – motagirl2 Jun 10 '18 at 21:24

N00b Pr0grammer · Answer 4 · 2017-06-16T14:58:35.043

2

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.

edited Jun 16 '17 at 14:58

answered Jun 16 '17 at 14:42

N00b Pr0grammer

4,503
5
32
46

2

The connection cannot be established at all when using the "characterEncoding=utf8mb4" parameter: "java.sql.SQLException: Unsupported character encoding 'utf8mb4'." – motagirl2 Jun 16 '17 at 14:50
You specify the Java encoding here, thus you should use `UTF-8`. – Usagi Miyamoto Jun 16 '17 at 14:56
1

Nothing. The same issue as at the beginnging: History: error in method 'create' : java.sql.SQLException: Incorrect string value: '\xF0\x9F\x92\xA2' for column 'srcctxt' at row 1 :/ – motagirl2 Jun 16 '17 at 15:02

utf8mb4 in MySQL Workbench and JDBC

4 Answers4