Project

General

Profile

MySQL exception with emoji

Julia Zashchitina
Added almost 4 years ago

Hello.

We are using Tigase Server 7.0.0 with database hosted on Amazon RDS. When sending a message with emoji to offline user tigase server tries to store it in database and fails with "java.sql.SQLException: Incorrect string value: '\xF0\x9F\x98\x83\xF0\x9F...' for column 'message' at row 1". Looking at https://projects.tigase.org/issues/2544 topic we changed 'character-set-server', 'collation-server' and table's character set to utf8mb4. However the issue still persists. Could you please suggest how to fix it? Thanks in advance.


Replies (6)

Added by Wojciech Kapcia TigaseTeam almost 4 years ago

  • have you re-created the schema?

  • Can you share full exception stacktrace?

Added by Julia Zashchitina almost 4 years ago

No, we haven't recreated the schema. Here's what we've done:

  1. Updated file /etc/mysql/my.cnf:
character-set-client-handshake = FALSE
init-connect='SET NAMES utf8mb4 COLLATE utf8mb4_unicode_ci'
character-set-server=utf8mb4
collation-server=utf8mb4_unicode_ci
  1. Alter tables:
ALTER TABLE tig_pairs CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE muc_history CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE msg_history CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
2015-04-06 13:21:02.873 [in_9-sess-man]    JDBCMsgRepository.storeMessage()   WARNING:  Problem adding new entry to DB: 
java.sql.SQLException: Incorrect string value: '\xF0\x9F\x98\x83\xF0\x9F...' for column 'message' at row 1
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1055)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:956)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3491)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3423)
    at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1936)
    at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2060)
    at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2542)
    at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1734)
    at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2019)
    at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1937)
    at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1922)
    at tigase.server.amp.JDBCMsgRepository.storeMessage(JDBCMsgRepository.java:769)
    at tigase.xmpp.impl.OfflineMessages.savePacketForOffLineUser(OfflineMessages.java:299)
    at tigase.xmpp.impl.MessageAmp.postProcess(MessageAmp.java:175)
    at tigase.server.xmppsession.SessionManager.processPacket(SessionManager.java:1823)
    at tigase.cluster.SessionManagerClustered.processPacket(SessionManagerClustered.java:252)
    at tigase.cluster.SessionManagerClustered.processPacket(SessionManagerClustered.java:238)
    at tigase.server.AbstractMessageReceiver$QueueListener.run(AbstractMessageReceiver.java:1424)

Added by Wojciech Kapcia TigaseTeam almost 4 years ago

Well, there is still definitely something missing in terms of MySQL configuration. Have you also updated the connection settings both for client and mysqld in your MySQL configuration? There is a guide How to support full Unicode in MySQL databases outlining the steps.

Added by Julia Zashchitina almost 4 years ago

We've done every step of this tutorial except for this part:

In your application code, set the connection character set to utf8mb4. This can be done by simply replacing any variants of SET NAMES utf8 with SET NAMES utf8mb4. 
If your old SET NAMES statement specified the collation, make sure to change that as well, e.g. SET NAMES utf8 COLLATE utf8_unicode_ci becomes SET NAMES utf8mb4 COLLATE utf8mb4_unicode_ci.

However, when we connect to database from our MySQL client, only after performing 'SET NAMES utf8mb4 COLLATE utf8mb4_unicode_ci' we are able to manually insert message with emoji to msg_history with this client.

Could you please suggest how and where can we make Tigase Server to provide above mentioned parameters?

Added by Wojciech Kapcia TigaseTeam almost 4 years ago

We are shipping:

            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.11</version>

Because newer versions had problems with deadlocks. However, as Changes in MySQL Connector/J 5.1.13 (2010-06-24) states:

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

You should update jdbc connector to resolve the issue.

Added by Julia Zashchitina almost 4 years ago

Thanks a lot, Wojciech, updating jdbc connector indeed fixed the issue.

    (1-6/6)