CODEWITHSUNDEEP
javamysqlhibernateunicode
Robin S
Robin S

Reputation: 199

Can't persist emojis with mysql and hibernate

I have actually found this problem multiple times on Stackoverflow, but the solutions would not help me.

I have a chat module in my android app and want to persist the messages in my server db, which works fine until special characters like emojis appear.

ERROR: Incorrect string value: '\xF0\x9F\x98\x81' for column 'message' at row 1
...
...
Caused by: java.sql.SQLException: Incorrect string value: '\xF0\x9F\x98\x81' for column 'message' at row 1
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1084)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4232)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4164)
    at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2615)
    at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2776)
    at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2838)
    at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2082)
    at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2334)
    at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2262)
    at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2246)
    at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.executeUpdate(ResultSetReturnImpl.java:187)
... 23 more

My environment is:

-Mysql 5.6
-Tomcat 8.0.8
-Hibernate 4.3.5
-JDK 1.8.0_05

This is the used table with the column in question, 'message':

table properties

These are my properties in the persistence.xml (version 2.1):

<property name="javax.persistence.jdbc.driver" value="com.mysql.jdbc.Driver" />
<property name="javax.persistence.jdbc.url" value="jdbc:mysql://localhost:3306/gamedb?useUnicode=true&amp;characterEncoding=UTF-8" />
<property name="javax.persistence.jdbc.user" value="*********" />
<property name="javax.persistence.jdbc.password" value="**************" />

<property name="hibernate.hbm2ddl.auto" value="update" />
<property name="hibernate.dialect" value="org.hibernate.dialect.MySQL5Dialect" />
<property name="hibernate.connection.useUnicode" value="true" />
<property name="hibernate.connection.characterEncoding" value="utf8" />

Now I tried the following solutions without effect:

-Change datatype of 'message' from varchar to longtext
-Change collation of 'message' to utf8mb4
-Change collation of table to utf8mb4
-Append url with "?useUnicode=true&amp;characterEncoding=UTF-8"
-Set character-set-server of mysql to utf8mb4

I think the emoji is correctly transmitted to the server, before it persists the message it broadcasts it back to the app and it gets correctly displayed.

Upvotes: 8

Views: 8690

Answers (8)

Саша Калашников
Саша Калашников

Reputation: 1

If your datasource is org.apache.commons.dbcp.BasicDataSource you can set connectionInitSqls parameter

    <bean id="dataSource"
          class="org.apache.commons.dbcp.BasicDataSource"
          p:driverClassName="${jdbc.driverClassName}"
          p:url="${jdbc.databaseurl}"
          p:username="${jdbc.username}"
          p:password="${jdbc.password}"
          p:testOnBorrow="true"
          p:maxActive="1000"
          p:testWhileIdle="true"
          p:validationQuery="SELECT 1"
          p:validationQueryTimeout="5">

        <property name="connectionInitSqls">
            <list>
                <value>SET NAMES 'utf8mb4' COLLATE 'utf8mb4_unicode_ci'</value>
            </list>
        </property>

    </bean>

Upvotes: 0

Mr_Thorynque
Mr_Thorynque

Reputation: 2002

Maybe you need to modify hibernate configuration as

<property name="hibernate.connection.characterEncoding" value="utf8mb4" />

Upvotes: 0

Koekiebox
Koekiebox

Reputation: 5963

I was able to fix the issue by providing the following in my connection URL;

useUnicode=true&characterEncoding=UTF-8

Example;

jdbc:mysql://localhost/database?useUnicode=true&characterEncoding=UTF-8

Upvotes: 0

sparkdoo
sparkdoo

Reputation: 534

I just found a nice little hack to get this to work without having to add any code. If you set the validation query to be SET NAMES utf8mb4 then it will execute this when it gets the connection, therefore setting the parameter each time a new connection is retrieved. You also need test on borrow to make this work.

Note I have found that this can sometimes take a few seconds to work after a restart so could have failures if you have records waiting to be processed on startup

So in your application.properties you would add something like 

datasource.test-on-borrow=true
datasource.validation-query=SET NAMES utf8mb4

Upvotes: 0

user2204107
user2204107

Reputation: 190

if you are using hibernate with c3p0, you can use c3p0 config connectionCustomizerClassName whitch you can set to a class doing work with connections c3p0 got.

example:

hibernate.cfg.xml

<property name="hibernate.c3p0.connectionCustomizerClassName">com.hzmoyan.newlyappserver.db.C3p0UseUtf8mb4</property>

C3p0UseUtf8mb4 class

public class C3p0UseUtf8mb4 extends  AbstractConnectionCustomizer{
     @Override
    public void onAcquire(Connection c, String parentDataSourceIdentityToken)
        throws Exception {
        super.onAcquire(c, parentDataSourceIdentityToken);
        try(Statement stmt = c.createStatement()) {
            stmt.executeQuery("SET NAMES utf8mb4");
        }
    }
}

Upvotes: 4

Robin S
Robin S

Reputation: 199

After not receiving further answers to my comments I found an alternative solution: Base64.

Instead of teaching my DB to understand utf8mb4 I encode all critical messages to Base64 before storing them and decode them when retrieving them from the database.

Pro:
- Works great
- Libraries are already given for java and android

Contra:
- Base64 strings take up more space than the pure utf8mb4 strings (33%-36% more)
- May cost some performance encoding and decoding

Upvotes: -1

Mathias Bynens
Mathias Bynens

Reputation: 149704

The solution is to use utf8mb4 rather than utf8 in MySQL. The blog post I linked to explains how to do just that.

Upvotes: 3

Jimmy T.
Jimmy T.

Reputation: 4190

I once had the same problem. I don't know a pretty solution but this had worked for me.

After I created the Session object I changed the connection collation by hand:

s.doReturningWork(new ReturningWork<Object>() {
    @Override
    public Object execute(Connection conn) throws SQLException
    {
        try(Statement stmt = conn.createStatement()) {
            stmt.executeQuery("SET NAMES utf8mb4");
        }

        return null;
    }
});

Upvotes: 7

Related Questions