12

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&useUnicode=true&characterEncoding=UTF-8"

url="jdbc:mysql://localhost:3306/test?autoReconnect=true&useUnicode=true&characterEncoding=UTF-8&characterSetResults=utf8&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?

Kowser
  • 8,123
  • 7
  • 40
  • 63

2 Answers2

17

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)

Community
  • 1
  • 1
wchiquito
  • 16,177
  • 2
  • 34
  • 45
  • 1
    Updating to latest mysql driver solved my problem. @William hint is correct. – Kowser Aug 13 '13 at 20:31
  • 24
    `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. – Mihai Danila Sep 30 '13 at 21:30
  • Still, it seem like it should have set `character_set_connection` to utf8mb4, as `SET NAMES` does. – Rick James May 08 '15 at 04:02
  • 8
    MySQL originally used 3 bytes to encode `utf8`... this is wrong. UTF8 uses up to a [maximum of 4 bytes](http://stijndewitt.com/2014/08/09/max-bytes-in-a-utf-8-char/). 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](http://stijndewitt.com/2015/06/15/use-mysql-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. – Stijn de Witt Jan 19 '16 at 14:25
  • This version you say not works for me, I use mysql-connector-java-8.0.26.jar instead – MrSalesi Dec 19 '21 at 15:03
0

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

Yousha Aleayoub
  • 4,532
  • 4
  • 53
  • 64
J Roysdon
  • 362
  • 2
  • 7