CODEWITHSUNDEEP
javamysqlhibernatecharacter-encodingdropwizard
H-V
H-V

Reputation: 131

Double Encoded UTF-8 String - MySql, Hibernate

We are saving a string in MySQL DB after encoding it using Base64 using hibernate.

Following is the code that does this:

    @Basic
    @Column(name = "name", nullable = false)
    @ColumnTransformer(read = "FROM_BASE64(name) ", write ="TO_BASE64(?)")
    public String getName()

Now, when I am saving rotebühlstr, this is getting saved in DB as cm90ZWLDvGhsc3Ry. When I print it on terminal, this is shown as rotebühlstr where as it should be rotebühlstr

This is a dropwizard project and config.yaml for mysql connection is as follow:

      properties:
      charSet: UTF-8
      characterEncoding: UTF-8
      useUnicode: true
      hibernate.dialect: org.hibernate.dialect.MySQL5InnoDBDialect
      hibernate.jdbc.batch_size: 100
      hibernate.envers.audit_table_suffix: "_aud"
      hibernate.id.new_generator_mappings: false

MySQL column description : name varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,

    mysql> show variables like 'character_set_%';
    +--------------------------+--------------------------------------- 
    --------------------+
    | Variable_name            | Value                                                     
    |
    +--------------------------+--------------------------------------- 
    --------------------+
    | character_set_client     | utf8mb4                                                   
    |
    | character_set_connection | utf8mb4                                                   
    |
    | character_set_database   | utf8mb4                                                   
    |
    | character_set_filesystem | binary                                                    
    |
    | character_set_results    | utf8mb4                                                   
    |
    | character_set_server     | latin1                                                    
    |
    | character_set_system     | utf8                                                      
    |
    | character_sets_dir       | /usr/local/mysql-5.7.23-macos10.13- 
    x86_64/share/charsets/ |
    +--------------------------+--------------------------------------- 
    --------------------+
    8 rows in set (0.01 sec)

Observation:

In my colleague's local set up, this is working fine. There, java/hibernate is treating input string in latin1 and not in UTF-8. so, rotebühlstr is encoded in DB as cm90ZWL8aGxzdHI= and decoded correctly as rotebühlstr.

^This was happening because of difference in character_set_server. It was set as latin1 in my local and as utf-8 in colleague's local.

What we have tried so far:

What I can do now:

I can may be write a wrapper layer for encoding and decoding and stop using @ColumnTransformer. That way problem can be fixed.

Thanks.

Upvotes: 2

Views: 958

Answers (2)

coladict
coladict

Reputation: 5095

It works on your colleague, but not on your terminal, because yours is connected with latin1 charset, even though the database and field might be in utf8mb4.

You want to find your mysql config and add these options in their sections. Create the sections if they're missing.

[mysql]
default-character-set=utf8mb4

[client]
default-character-set=utf8mb4

[mysqld]
character_set_server = utf8mb4
collation_server = utf8mb4_general_ci

Exit any clients, restart the server and you should be fine. Also from now on when you do a show create database or show create table you will see when it's the wrong encoding.

Upvotes: 1

Amin Heydari Alashti
Amin Heydari Alashti

Reputation: 1021

I had a problem with charsets one time and the only charset could fix the problem was utf8mb4. As I can remember The problem arises from that utf8 cannot support some characters.

Additionally, for more information, you can check https://stackoverflow.com/a/43692337/2137378 too.

Upvotes: 1

Related Questions