Project

General

Profile

Does tigase support save emoji into message database table?

Matthew M
Added almost 5 years ago

iOS and Android mobile app can send emoji (which may have 4 bytes) in message, but it seems that tigase can not save it to database, and it seems that we have to upgrade to mysql 5.5 in order to save it (and change the message column to support 4 bytes of unicode, which is utf8mb4).

I wonder if Tigase has any thoughts or recommendation about this? Or will Tigase do any special treatment of such emoji characters?

Reference: http://apps.timwhitlock.info/emoji/tables/unicode


Replies (11)

Added by Igor Khomenko almost 5 years ago

I had the same issue with MUC messages history

It was an issue with db config

try to add

 useUnicode=true&characterEncoding=UTF-8
--user-db-uri=dbc:mysql://....amazonaws.com:3306/tigasedb?user=...&password=...&autoCreateUser=true&useUnicode=true&characterEncoding=UTF-8

now it works with iOS emoji

Added by Matthew M almost 5 years ago

Hi Igor,

Thanks a lot for information! Besides the changes in the configuration, we actually have the "useUnicode=true" in the config settings, but it still fails saving emoji to DB.

I wonder if there is anything to be done on DB table side.

  1. What's the mysql version you are using? 5.1 or 5.5?

  2. What's the Encoding of the table column of the "msg" in tables of tig_ma_msgs and muc_history? Do we have to use utf8mb4 instead utf-8? It seems that we have to use utf8mb4 in order to support iOS's emoji: http://mathiasbynens.be/notes/mysql-utf8mb4#utf8-to-utf8mb4

Many thanks!

Added by Igor Khomenko almost 5 years ago

Yes, we use utf8mb4 and MySQL 5.5

Added by Matthew M almost 5 years ago

Thanks for the updates.

We tried utf8mb4 and MySQL 5.5, but still failing saving messages to database, tigase still throw SQL exception like "Invalid string", when someone compose a message from iOS with emoji.

I tried with and without the following settings, both gives me the same error:

&useUnicode=true&characterEncoding=UTF-8

Does it has to do with 1on1 chat or muc chat?

I wonder if any Tigase team members have tried to save emoji to database, I am not sure if have another other settings missing in the project... This is causing a serious problem that any message with emoji will not be persisted.

Many thanks for the help!

Added by Wojciech Kapcia TigaseTeam almost 5 years ago

Matthew M wrote:

tigase still throw SQL exception like "Invalid string", when someone compose a message from iOS with emoji.

Could you provide full exception?

Does it has to do with 1on1 chat or muc chat?

Does it happen only with 1-1 chats or MUC?

I wonder if any Tigase team members have tried to save emoji to database, I am not sure if have another other settings missing in the project... This is causing a serious problem that any message with emoji will not be persisted.

Could you share name of any example app (android, ios) that allows sending emoji over desired xmpp server?

Added by Matthew M almost 5 years ago

We are using IM+ Instant Messenger iOS app, and using 1-1 chat. I can use that to connect to tigase.im without any problem and send emoji (but I am not sure if that is causing any error saving to tigase.im db).

Somehow I messed up with my full exception trace, will try to find it later. But it looks like "Incorrect string value: '\xF0\x90\x8D\x83\xF0\x90..."

Added by Wojciech Kapcia TigaseTeam almost 5 years ago

I've tried to replicate the problem - both for message archive (xep-0136) and offline messages. There wasn't any exception and the messages seems to be stored and afterwards correct emoji is delivered:

|        2 |        1 | 2014-05-05 12:01:24 |         1 | chat | <message id="1399305684349-1399305684542" type="chat" from="admin@…/IM+" time="1399305684573" xmlns="jabber:client" to="tigase1@…"><body> </body></message>   |
mysql> select * from msg_history \G
*************************** 1. row ***************************
      msg_id: 1
          ts: 2014-05-05 18:30:37
     expired: NULL
  sender_uid: 1
receiver_uid: 2
     message: <message id="1399307438078-1399307437427" type="chat" from="admin@…/IM+" xmlns="jabber:client" to="tigase1@…"><body>sjjs</body><delay from="…" xmlns="urn:xmpp:delay" stamp="2014-05-05T16:30:37.447Z">Offline Storage - node1.…</delay></message>
*************************** 2. row ***************************
      msg_id: 2
          ts: 2014-05-05 18:32:46
     expired: NULL
  sender_uid: 1
receiver_uid: 2
     message: <message id="1399307566863-1399307566809" type="chat" from="admin@…/IM+" xmlns="jabber:client" to="tigase1@…"><body> </body><delay from="…" xmlns="urn:xmpp:delay" stamp="2014-05-05T16:32:46.828Z">Offline Storage - node1.…</delay></message>
2 rows in set (0.00 sec)

This setup use rather default setup:

  • mentioned above &useUnicode=true&characterEncoding=UTF-8 in the JDBC string and

  • UTF8 configuration for MySQL described in Prepare the MySQL database

[client]
default-character-set=utf8

[mysqld]
init_connect='SET collation_connection = utf8_general_ci; SET NAMES utf8;'
character-set-server=utf8
collation-server=utf8_general_ci

Added by Matthew M almost 5 years ago

Thanks for verification.... we went on to trouble shoot the system... and found out somehow we are stuck with a slight older version of JDBC, and we upgraded to JDBC 5.1.27 and it starts to work! Thanks a lot for the help!

Added by Matthew M almost 5 years ago

We found a very strange problem, which is related to https://projects.tigase.org/boards/4/topics/2064.

Which version of mysql-connector-java.jar is used in tigase-server/jars/ ?

http://mvnrepository.com/artifact/mysql/mysql-connector-java

We are using utf8mb4 and MySQL 5.5, and we found we could not save any emoji with mysql-connector 5.1.6 version, nor with the original mysql-connector-java.jar in tigase-server. But we can save emoji correctly if we use 5.1.27 version.

However mysql-connector 5.1.27 version cause a severe problem: after about 100 BOSH or more connection (with some simple load testing), tigase start not to responding to any new connections, and we found this in logs. So we wonder what's the version of jdbc connector in tigase-server? Many thanks!

No response within time limit received for a packet: from=null, to=null, DATA=
<iq type="set" to="sess-man@xmpp.tiagse-im.com" id="8fffd0b5-6ebf-4766-a6d0-eff75f976742" 
    from="bosh@xmpp.tigase-im.com/12819178-97c8-48f7-9004-4f8f4b2bcb22">
    <command node="STREAM_OPENED" xmlns="http://jabber.org/protocol/commands">
        <x type="submit" xmlns="jabber:x:data">
            <field var="session-id">
                <value>82d7e5ad-d9e7-4f1c-b22b-161d197b91f0</value>
            </field>
            <field var="hostname">
                <value>sumilux.com</value>
            </field>
            <field var="xml:lang">
                <value>en</value>
            </field>
        </x>
    </command>
</iq>

Added by Wojciech Kapcia TigaseTeam almost 5 years ago

Currently we are shipping:

<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>5.1.11</version>
    <scope>runtime</scope>
</dependency>

We had reports that newer JDBC MySQL drivers can cause issues (even deadlocks). My previous tests were made on the version that we bundle (i.e. the above).

Added by Raylin lin almost 5 years ago

Matthew M wrote:

We found a very strange problem, which is related to https://projects.tigase.org/boards/4/topics/2064.

Which version of mysql-connector-java.jar is used in tigase-server/jars/ ?

http://mvnrepository.com/artifact/mysql/mysql-connector-java

We are using utf8mb4 and MySQL 5.5, and we found we could not save any emoji with mysql-connector 5.1.6 version, nor with the original mysql-connector-java.jar in tigase-server. But we can save emoji correctly if we use 5.1.27 version.

However mysql-connector 5.1.27 version cause a severe problem: after about 100 BOSH or more connection (with some simple load testing), tigase start not to responding to any new connections, and we found this in logs. So we wonder what's the version of jdbc connector in tigase-server? Many thanks!

[...]

have you tried 5.1.30? I have done some tsung login testing,and it seems to work.

    (1-11/11)