Project

General

Profile

pubsub can't archive chinese messages to database

xukiw xu
Added over 3 years ago

The english message is ok, but when I publish chinese message,the server returns :

When I open Mysql's table tig_pubsub_items, the chinese message is not successful saved to the table.

The table's Character encoding is UTF-8,and the mysql connection string also contains "useUnicode=true&characterEncoding=UTF-8", so what's the problem?


Replies (4)

(1)

Added by Wojciech Kapcia TigaseTeam over 3 years ago

Are there any exceptions in the logs?

(1)

Added by xukiw xu over 3 years ago

Yes,there are some exceptions in the log file tigase-console.log :

2015-09-03 15:20:29.013 [main]             ConfigurationCache.store()         WARNING:  Dumping server configuration to: etc/config-dump.properties
2015-09-03 15:22:05.150 [ConnectionOpenThread]  SocketThread.<clinit>()       WARNING:  5 socketReadThreads started.
2015-09-03 15:22:05.192 [ConnectionOpenThread]  SocketThread.<clinit>()       WARNING:  5 socketWriteThreads started.
2015-09-03 15:22:05.582 [urn:ietf:params:xml:ns:xmpp-sasl Queue Worker 0]  DomainFilter.filter()  WARNING: Can't access user repository.
tigase.db.TigaseDBException: Error getting user data for: xukiw@119.29.56.86/null/allowed-domains
        at tigase.db.jdbc.JDBCRepository.getData(JDBCRepository.java:273)
        at tigase.db.UserRepositoryMDImpl.getData(UserRepositoryMDImpl.java:103)
        at tigase.xmpp.RepositoryAccess.getData(RepositoryAccess.java:767)
        at tigase.xmpp.impl.DomainFilter.getDomains(DomainFilter.java:462)
        at tigase.xmpp.impl.DomainFilter.filter(DomainFilter.java:87)
        at tigase.server.xmppsession.SessionManager.addOutPackets(SessionManager.java:978)
        at tigase.server.xmppsession.SessionManager$ProcessorWorkerThread.process(SessionManager.java:2439)
        at tigase.util.WorkerThread.run(WorkerThread.java:128)
Caused by: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure

The last packet successfully received from the server was 42,356 milliseconds ago.  The last packet sent successfully to the server was 0 millisecon
ds ago.
        at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
        at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
        at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
        at java.lang.reflect.Constructor.newInstance(Constructor.java:422)
        at com.mysql.jdbc.Util.handleNewInstance(Util.java:406)
        at com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:1119)
        at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3057)
        at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:2943)
        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3486)
        at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1959)
        at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2113)
        at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2687)
        at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2616)
        at com.mysql.jdbc.StatementImpl.executeQuery(StatementImpl.java:1464)
        at tigase.db.jdbc.JDBCRepository.getNodeNID(JDBCRepository.java:1122)
        at tigase.db.jdbc.JDBCRepository.getNodeNID(JDBCRepository.java:1165)
        at tigase.db.jdbc.JDBCRepository.getData(JDBCRepository.java:239)
        at tigase.db.UserRepositoryMDImpl.getData(UserRepositoryMDImpl.java:103)
        at tigase.xmpp.RepositoryAccess.getData(RepositoryAccess.java:767)
        at tigase.xmpp.impl.DomainFilter.getDomains(DomainFilter.java:462)
        at tigase.xmpp.impl.DomainFilter.filter(DomainFilter.java:87)
        at tigase.server.xmppsession.SessionManager.addOutPackets(SessionManager.java:978)
        at tigase.server.xmppsession.SessionManager$ProcessorWorkerThread.process(SessionManager.java:2439)
        at tigase.util.WorkerThread.run(WorkerThread.java:128)
Caused by: java.io.EOFException: Can not read response from server. Expected to read 4 bytes, read 0 bytes before connection was unexpectedly lost.
        at com.mysql.jdbc.MysqlIO.readFully(MysqlIO.java:2502)
        at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:2954)
        at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:2943)
        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3486)
        at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1959)
        at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2113)
        at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2687)
        at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2616)
        at com.mysql.jdbc.StatementImpl.executeQuery(StatementImpl.java:1464)
        at tigase.db.jdbc.JDBCRepository.getNodeNID(JDBCRepository.java:1122)
        at tigase.db.jdbc.JDBCRepository.getNodeNID(JDBCRepository.java:1165)
        at tigase.db.jdbc.JDBCRepository.getData(JDBCRepository.java:239)
        at tigase.db.UserRepositoryMDImpl.getData(UserRepositoryMDImpl.java:103)
        at tigase.xmpp.RepositoryAccess.getData(RepositoryAccess.java:767)
        at tigase.xmpp.impl.DomainFilter.getDomains(DomainFilter.java:462)
        at tigase.xmpp.impl.DomainFilter.filter(DomainFilter.java:87)
        at tigase.server.xmppsession.SessionManager.addOutPackets(SessionManager.java:978)
        at tigase.server.xmppsession.SessionManager$ProcessorWorkerThread.process(SessionManager.java:2439)
        at tigase.util.WorkerThread.run(WorkerThread.java:128)
tigase.pubsub.repository.RepositoryException: Item writing error
        at tigase.pubsub.repository.PubSubDAOJDBC.writeItem(PubSubDAOJDBC.java:1055)
        at tigase.pubsub.repository.PubSubDAOJDBC.writeItem(PubSubDAOJDBC.java:59)
        at tigase.pubsub.repository.PubSubDAOPool.writeItem(PubSubDAOPool.java:527)
        at tigase.pubsub.repository.cached.Items.writeItem(Items.java:76)
        at tigase.pubsub.modules.PublishItemModule.doPublishItems(PublishItemModule.java:340)
        at tigase.pubsub.modules.PublishItemModule.process(PublishItemModule.java:557)
        at tigase.component2.modules.ModulesManager.process(ModulesManager.java:87)
        at tigase.component2.AbstractComponent.processStanzaPacket(AbstractComponent.java:221)
        at tigase.component2.AbstractComponent.processPacket(AbstractComponent.java:209)
        at tigase.pubsub.PubSubComponent.processPacket(PubSubComponent.java:599)
        at tigase.server.AbstractMessageReceiver$QueueListener.run(AbstractMessageReceiver.java:1424)
Caused by: java.sql.SQLException: Incorrect string value: '\xE4\xBD\xA0\xE5\xA5\xBD...' for column '_item_data' at row 1
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1072)
        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3563)
        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3495)
        at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1959)
        at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2113)
        at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2693)
        at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2102)
        at com.mysql.jdbc.PreparedStatement.execute(PreparedStatement.java:1364)
        at com.mysql.jdbc.CallableStatement.execute(CallableStatement.java:877)
        at tigase.pubsub.repository.PubSubDAOJDBC.writeItem(PubSubDAOJDBC.java:1051)
        ... 10 more
2015-09-03 15:22:18.399 [in_2-pubsub]      AbstractComponent.processStanzaPacket()  SEVERE: tigase.pubsub.repository.RepositoryException: Item writi
ng error when processing from=sess-man@localhost, to=null, DATA=<iq xmlns="jabber:client" to="pubsub.119.29.56.86" from="xukiw@119.29.56.86/QHD" typ
e="set" id="1:publishnode"><pubsub xmlns="http://jabber.org/protocol/pubsub"><publish node="80@muc.119.29.56.86" jid="xukiw@119.29.56.86/QHD"><item>
<entry><msg from="xukiw" type="groupchat">你好</msg></entry></item></publish></pubsub></iq>, SIZE=321, XMLNS=jabber:client, PRIORITY=NORMAL, PERMISSION=
AUTH, TYPE=set
java.lang.RuntimeException: tigase.pubsub.repository.RepositoryException: Item writing error
        at tigase.pubsub.modules.PublishItemModule.process(PublishItemModule.java:564)
        at tigase.component2.modules.ModulesManager.process(ModulesManager.java:87)
        at tigase.component2.AbstractComponent.processStanzaPacket(AbstractComponent.java:221)
        at tigase.component2.AbstractComponent.processPacket(AbstractComponent.java:209)
        at tigase.pubsub.PubSubComponent.processPacket(PubSubComponent.java:599)
        at tigase.server.AbstractMessageReceiver$QueueListener.run(AbstractMessageReceiver.java:1424)
Caused by: tigase.pubsub.repository.RepositoryException: Item writing error
        at tigase.pubsub.repository.PubSubDAOJDBC.writeItem(PubSubDAOJDBC.java:1055)
        at tigase.pubsub.repository.PubSubDAOJDBC.writeItem(PubSubDAOJDBC.java:59)
        at tigase.pubsub.repository.PubSubDAOPool.writeItem(PubSubDAOPool.java:527)
        at tigase.pubsub.repository.cached.Items.writeItem(Items.java:76)
        at tigase.pubsub.modules.PublishItemModule.doPublishItems(PublishItemModule.java:340)
        at tigase.pubsub.modules.PublishItemModule.process(PublishItemModule.java:557)
        at tigase.component2.modules.ModulesManager.process(ModulesManager.java:87)
        at tigase.component2.AbstractComponent.processStanzaPacket(AbstractComponent.java:221)
        at tigase.component2.AbstractComponent.processPacket(AbstractComponent.java:209)
        at tigase.pubsub.PubSubComponent.processPacket(PubSubComponent.java:599)
        at tigase.server.AbstractMessageReceiver$QueueListener.run(AbstractMessageReceiver.java:1424)
Caused by: java.sql.SQLException: Incorrect string value: '\xE4\xBD\xA0\xE5\xA5\xBD...' for column '_item_data' at row 1
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1072)
        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3563)
        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3495)
        at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1959)
        at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2113)
        at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2693)
        at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2102)
        at com.mysql.jdbc.PreparedStatement.execute(PreparedStatement.java:1364)
        at com.mysql.jdbc.CallableStatement.execute(CallableStatement.java:877)
        at tigase.pubsub.repository.PubSubDAOJDBC.writeItem(PubSubDAOJDBC.java:1051)
        at tigase.pubsub.repository.PubSubDAOJDBC.writeItem(PubSubDAOJDBC.java:59)
        at tigase.pubsub.repository.PubSubDAOPool.writeItem(PubSubDAOPool.java:527)
        at tigase.pubsub.repository.cached.Items.writeItem(Items.java:76)
        at tigase.pubsub.modules.PublishItemModule.doPublishItems(PublishItemModule.java:340)
        at tigase.pubsub.modules.PublishItemModule.process(PublishItemModule.java:557)
        at tigase.component2.modules.ModulesManager.process(ModulesManager.java:87)
        at tigase.component2.AbstractComponent.processStanzaPacket(AbstractComponent.java:221)
        at tigase.component2.AbstractComponent.processPacket(AbstractComponent.java:209)
        at tigase.pubsub.PubSubComponent.processPacket(PubSubComponent.java:599)
        at tigase.server.AbstractMessageReceiver$QueueListener.run(AbstractMessageReceiver.java:1424)

The init.properties's content is as follows:

--comp-class-1 = tigase.muc.MUCComponent
--virt-hosts = 119.29.56.86
--user-db-uri = jdbc:mysql://localhost/tigasedb?user=tigase&password=fjdksl&useUnicode=true&characterEncoding=UTF-8
--user-db = mysql
--admins = admin@119.29.56.86
--comp-name-4 = message-archive
--comp-name-3 = proxy
config-type = --gen-config-def
--comp-name-2 = pubsub
--comp-name-1 = muc
muc/muc-lock-new-room[B]=false
muc/default_room_config/muc#roomconfig_persistentroom=true
--cluster-mode = false
--sm-plugins = +message-archive-xep-0136
--debug = server
--comp-class-4 = tigase.archive.MessageArchiveComponent
--comp-class-3 = tigase.socks5.Socks5ProxyComponent
--comp-class-2 = tigase.pubsub.PubSubComponent
bosh/concurrent-requests[I] = 16
bosh/max-inactivity[L] = 360
ess-man/plugins-conf/message-archive-xep-0136/store-muc-messages=true
message-archive/archive-repo-uri=jdbc:mysql://localhost/tigasedb?user=tigase&password=fjdksl&useUnicode=true&characterEncoding=UTF-8

The tig_pubsub_items table's character setting is shown in the photo, so I dont know where the problem is.

11.png (18.7 KB) 11.png

Added by xukiw xu over 3 years ago

The problem is fixed ,change the procedure TigPubSubWriteItem of MySQL, change the datatype of parameter _item_data to 'text',and change datatype of 'data' column of tig_pubsub_items to 'text'.

(1)

Added by Wojciech Kapcia TigaseTeam over 3 years ago

It looks like with default mediumtext MySQL is closing the connection - please check MySQL logs.

    (1-4/4)