JDBC url for MySQL configuration to use utf8 character encoding

Question

MySQL 5.6 Configuration

I have configured /etc/mysql/my.cnf manually to use utf8. See below:

[mysqld]
character-set-server=utf8
character-sets-dir=/usr/share/mysql/charsets

[mysql]
character-sets-dir=/usr/share/mysql/charsets default-character-set=utf8

[mysqladmin]
character-sets-dir=/usr/share/mysql/charsets default-character-set=utf8

[mysqlcheck]
character-sets-dir=/usr/share/mysql/charsets default-character-set=utf8

[mysqldump]
character-sets-dir=/usr/share/mysql/charsets default-character-set=utf8

[mysqlimport]
character-sets-dir=/usr/share/mysql/charsets default-character-set=utf8

[mysqlshow]
character-sets-dir=/usr/share/mysql/charsets default-character-set=utf8

[client]
character-sets-dir=/usr/share/mysql/charsets default-character-set=utf8

From console:

mysql> show variables like 'char%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
| character_set_database   | utf8                       |
| character_set_filesystem | binary                     |
| character_set_results    | utf8                       |
| character_set_server     | utf8                       |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.01 sec)

Application configuration

Now for the web-app I used both of this connection url in Tomcat's context.xml

url="jdbc:mysql://localhost:3306/test?autoReconnect=true&amp;useUnicode=true&amp;characterEncoding=UTF-8"

url="jdbc:mysql://localhost:3306/test?autoReconnect=true&amp;useUnicode=true&amp;characterEncoding=UTF-8&amp;characterSetResults=utf8&amp;connectionCollation=utf8_general_ci"

Above does not work and gives me following for the application.

show variables like 'char%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8mb4                    |
| character_set_connection | utf8                       |
| character_set_database   | utf8                       |
| character_set_filesystem | binary                     |
| character_set_results    | utf8mb4                    |
| character_set_server     | utf8                       |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+

What might be wrong here? Why it is showing utf8mb4 instead utf8?

Community · Accepted Answer · 2020-06-20 09:12:55Z

18

You may have to do with the following:

Changes in MySQL Connector/J 5.1.13 (2010-06-24)

Connector/J did not support utf8mb4 for servers 5.5.2 and newer.

Connector/J now auto-detects servers configured with character_set_server=utf8mb4 or treats the Java encoding utf-8 passed using characterEncoding=... as utf8mb4 in the SET NAMES= calls it makes when establishing the connection. (Bug #54175)

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered Aug 5, 2013 at 13:40

wchiquito

16.6k2 gold badges37 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Kowser Over a year ago

Updating to latest mysql driver solved my problem. @William hint is correct.

Mihai Danila Over a year ago

utf8mb4 is MySQL's UTF-8. If you use utf8 in MySQL, you're missing out on an important portion of Unicode (the astral symbols). Switch everything, tables, database, to utf8mb4 or you may lose data. Yeah, I know, ridiculous, right? They must have confused the day they coded this in for a night out.

Rick James Over a year ago

Still, it seem like it should have set character_set_connection to utf8mb4, as SET NAMES does.

Stijn de Witt Over a year ago

MySQL originally used 3 bytes to encode utf8... this is wrong. UTF8 uses up to a maximum of 4 bytes. But having to remain backwards compatible with their old bugs, they sorta fixed it by introducing utf8mb4, which is the same encoding the rest of the world calls UTF8. So, use utf8mb4 if you want full unicode support. The bad thing is few people know this, so new systems keep getting created using the wrong encoding.

MrSalesi Over a year ago

This version you say not works for me, I use mysql-connector-java-8.0.26.jar instead

Yousha Aleayoub · Accepted Answer · 2019-11-27 21:30:35Z

0

For your web-app please try characterEncoding=utf8 instead of UTF-8. That resolved my SonarQube container issue.

edited Nov 27, 2019 at 21:30

Yousha Aleayoub

5,9294 gold badges61 silver badges72 bronze badges

answered Jul 24, 2019 at 7:16

J Roysdon

4124 silver badges8 bronze badges

Collectives™ on Stack Overflow

JDBC url for MySQL configuration to use utf8 character encoding

MySQL 5.6 Configuration

Application configuration

2 Answers 2

5 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

MySQL 5.6 Configuration

Application configuration

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related