"Monitoring Service", bag from "Open Archive", request history

Stas_Koleda · February 23, 2014, 3:13pm

I find and correct the error in the plugin “Monitoring Service”, in the modules implementing xep-0059 and xep-0136 (ex “Open Archive” plugin).

Client is used Vacuum-IM.

Modeling problems :

Two people communicating create 3 conversation with 20 messages in each.
One of the users makes a request to the server to get the story:

<iq type="get" id="sid_17">
  <list xmlns="urn:xmpp:archive" end="2014-02-23T14:01:38.278Z" with="user2@domain.local">
    <set xmlns="http://jabber.org/protocol/rsm">
      <max>10</max>
      <before/>
    </set>
  </list>
</iq>

The server returns:

<iq type="result" id="sid_17" to="user1@domain.local/Vacuum-IM">
  <list xmlns="urn:xmpp:archive">
    <chat with="user2@domain.local" start="2014-02-22T12:10:29.243Z"/>
    <set xmlns="http://jabber.org/protocol/rsm">
      <first index="0">1</first>
      <last>1</last>
      <count>3</count>
    </set>
  </list>
</iq>

Instead, the server should return:

<iq type="result" id="sid_17" to="user1@domain.local/Vacuum-IM">
  <list xmlns="urn:xmpp:archive">
    <chat with="user2@domain.local" start="2014-02-22T12:10:29.243Z"/>
    <chat with="user2@domain.local" start="2014-02-22T12:27:45.585Z"/>
    <chat with="user2@domain.local" start="2014-02-22T12:34:05.371Z"/>
    <set xmlns="http://jabber.org/protocol/rsm">
      <first index="0">1</ first>
      <last>3</last>
      <count>3</count>
    </set>
  </list>
</iq>

I find two errors due to which there is such a situation:

First, the tag “10” must request 10 conversations, but in fact it requests 10 posts of the total number of messages in these three conversations (ie 10 of 60).
Secondly, the tag does not work “”, as it does not contain the number (ie, an empty tag).

These problems I managed to solve by applying patches from two files:

“openfire_src\src\plugins\monitoring\src\java\com\reucon\openfire\plugin\archiv e\impl*JdbcPersistenceManager.java*”
“openfire_src\src\plugins\monitoring\src\java\com\reucon\openfire\plugin\archiv e\xep0059*XmppResultSet.java*”

In the first file you need to fix SQL-query “SELECT_CONVERSATIONS”. This query returns the conversations with messages, but should only return conversations.

The second file is needed to handle the case when a tag “” empty.

Corrected files and patched plugin as an attachment.

Monitoring_1.3.2-rc1_patched_X.zip for Openfire 3.7.X-3.8.X

Monitoring_1.4.3_beta_X.zip for Openfire 3.9.X

By the way, this plugin will generate an error when used in conjunction with MS SQL Server, because this database does not understand commands “LIMIT” and “OFFSET”. With PostgreSQL it’s work perfectly. FIXED

Sorry for my english
Monitoring_1.4.3_beta_3.zip (1650642 Bytes)
Monitoring_1.3.2-rc1_patched_4.zip (1541547 Bytes)

Simon_Ehlen · February 23, 2014, 9:33pm

Hi,

thank you for your effort in trying to fix this bug. Unfortunately it still doesn’t work for me. If I set the “Retrievable Messages” to 7, to receive a week of messages I receive a bunch of messages but not in the right order. And it doesn’t contain the last communication stored in the archive.

I’m using xabber for android.

Any ideas?

Cheers

Simon

Stas_Koleda · February 24, 2014, 12:40am

For your modeling errors I need XML-traffic between clients and the server. Try to install the plugin on the server “Debugger Plugin”. Then on the client make a request history. And write here XML-traffic.

Are you sure that this is not the error of the “xabber”?

Try another client request history, for example, Vacuum-IM.

Simon_Ehlen · February 24, 2014, 9:17am

Hi,

any hints how to access the xml logs? I start openfire using the init.d scripts for debian so the server forks to the background.

But I think it is an openfire issue, shouldn’t there be an ORDER BY statement in the SELECT_CONVERSATIONS statement?

Stas_Koleda · February 24, 2014, 9:31am

For access to XML logs, you need use “Debugger Plugin” for Openfire: http://www.igniterealtime.org/projects/openfire/plugins.jsp

“ORDER BY” is not to blame, it should sort in chronological order, and further limit the result is using the “LIMIT” and “OFFSET”. In addition, under the XEP-0136 (http://xmpp.org/extensions/xep-0136.html):
…
The server MUST list the collections (empty elements including all attributes) in chronological order when responding to any request.
…

Simon_Ehlen · February 24, 2014, 9:37am

Yes, I installed the plugin. But where do I find the resulting xml logfiles?

Simon_Ehlen · February 24, 2014, 10:02am

I used Vacuum-IM to get the xml data using the xml console:

user1@host.tld/Vacuum-IM 10:58:54 +0 >>>>

  <set xmlns="[http://jabber.org/protocol/rsm](http://jabber.org/protocol/rsm)">

30

<<<< user1@host.tld/Vacuum-IM 10:58:54 +158 <<<<

  <set xmlns="[http://jabber.org/protocol/rsm](http://jabber.org/protocol/rsm)">

6036

6051

49

But this output is not correct as there are conversations after 20.02.2014 which are missing in the server reply. They are available using the openfire admin console.

Stas_Koleda · February 24, 2014, 10:16am

I’m not install this plugin. In Windows, logs must be stored in: C:\Program Files (x86)\Openfire\logs\stdout.log

Give me anser of server for this command:

10000

Simon_Ehlen · February 24, 2014, 10:36am

user1@host.tld/Vacuum-IM 11:33:53 +9243 >>>>

  <set xmlns="[http://jabber.org/protocol/rsm](http://jabber.org/protocol/rsm)">

    <max xmlns="[http://jabber.org/protocol/rsm](http://jabber.org/protocol/rsm)">10000</max>

    <before xmlns="[http://jabber.org/protocol/rsm](http://jabber.org/protocol/rsm)"/>

<<<< user1@host.tld/Vacuum-IM 11:33:53 +96 <<<<

  <set xmlns="[http://jabber.org/protocol/rsm](http://jabber.org/protocol/rsm)">

6026

6079

50

Lion · February 24, 2014, 3:23pm

It seems that sorting by ‘start’ does not applied.

Stas_Koleda · February 24, 2014, 4:37pm

Very strange. Your first request return 16 instead 30 conversations. I suspect that there are problems with the structure of the database. Let’s try to do the following:

What is the value you set in the “Server-Archiving-Archive Settings-Retrievable Messages”?
Turn on Debug (Server-Server Manager-Logs-Debug-Enable)
Run the query from Vacuum-IM:

30

In the Debug window, locate the line “findConversations SELECT_CONVERSATIONS”, it will contain the SQL-query. Execute it to its database.
Put here SQL-query and its result. And the answer from the Vacuum-IM.

Simon_Ehlen · February 24, 2014, 8:01pm

I set it to 7 days:

SELECT DISTINCT ofconversation.conversationid,
ofconversation.room,
ofconversation.isexternal,
ofconversation.startdate,
ofconversation.lastactivity,
ofconversation.messagecount,
ofconparticipant.joineddate,
ofconparticipant.leftdate,
ofconparticipant.barejid,
ofconparticipant.jidresource,
ofconparticipant.nickname,
ofmessagearchive.fromjid,
ofmessagearchive.tojid
FROM ofconversation
INNER JOIN ofconparticipant
ON ofconversation.conversationid =
ofconparticipant.conversationid
INNER JOIN ofmessagearchive
ON ofconparticipant.conversationid =
ofmessagearchive.conversationid
WHERE ofconversation.startdate >= 1392666702239
AND ofconparticipant.barejid = 'user1@host.tld’
AND ( ofmessagearchive.tojid = 'user2@host.tld’
OR ofmessagearchive.fromjid = ‘user2@host.tld’ )
ORDER BY ofconversation.conversationid
LIMIT 30 offset 13

“6038”;NULL;“0”;“1392792982399”;“1392793855667”;“4”;“1392792982399”;“13927945518 03”;“user1@host.tld”;;NULL;“user1@host.tld”;“user2@host.tld”

“6038”;NULL;“0”;“1392792982399”;“1392793855667”;“4”;“1392792982399”;“13927945518 03”;“user1@host.tld”;;NULL;“user2@host.tld”;“user1@host.tld”

“6039”;NULL;“0”;“1392794816826”;“1392795083621”;“3”;“1392794816826”;“13927957518 04”;“user1@host.tld”;“5B06272E”;NULL;“user2@host.tld”;“user1@host.tld”

“6039”;NULL;“0”;“1392794816826”;“1392795083621”;“3”;“1392794816826”;“13927957518 04”;“user1@host.tld”;“5B06272E”;NULL;“user1@host.tld”;“user2@host.tld”

“6040”;NULL;“0”;“1392803399331”;“1392803955150”;“4”;“1392803399331”;“13928047518 05”;“user1@host.tld”;;NULL;“user2@host.tld”;“user1@host.tld”

“6040”;NULL;“0”;“1392803399331”;“1392803955150”;“4”;“1392803399331”;“13928047518 05”;“user1@host.tld”;;NULL;“user1@host.tld”;“user2@host.tld”

“6041”;NULL;“0”;“1392805937653”;“1392806969437”;“61”;“1392805937653”;“1392807751 803”;“user1@host.tld”;“5B06272E”;NULL;“user1@host.tld”;“user2@host.tld”

“6041”;NULL;“0”;“1392805937653”;“1392806969437”;“61”;“1392805937653”;“1392807751 803”;“user1@host.tld”;“5B06272E”;NULL;“user2@host.tld”;“user1@host.tld”

“6042”;NULL;“0”;“1392807832681”;“1392810583123”;“76”;“1392807832681”;“1392811351 806”;“user1@host.tld”;“5B06272E”;NULL;“user2@host.tld”;“user1@host.tld”

“6042”;NULL;“0”;“1392807832681”;“1392810583123”;“76”;“1392807832681”;“1392811351 806”;“user1@host.tld”;“5B06272E”;NULL;“user1@host.tld”;“user2@host.tld”

“6043”;NULL;“0”;“1392812525512”;“1392812525513”;“1”;“1392812525512”;“13928131518 06”;“user1@host.tld”;“5B06272E”;NULL;“user1@host.tld”;“user2@host.tld”

“6044”;NULL;“0”;“1392816559550”;“1392817640840”;“35”;“1392816559550”;“1392818251 803”;“user1@host.tld”;“5B06272E”;NULL;“user2@host.tld”;“user1@host.tld”

“6044”;NULL;“0”;“1392816559550”;“1392817640840”;“35”;“1392816559550”;“1392818251 803”;“user1@host.tld”;“5B06272E”;NULL;“user1@host.tld”;“user2@host.tld”

“6045”;NULL;“0”;“1392818331830”;“1392818339922”;“3”;“1392818331830”;“13928191518 05”;“user1@host.tld”;“5B06272E”;NULL;“user2@host.tld”;“user1@host.tld”

“6045”;NULL;“0”;“1392818331830”;“1392818339922”;“3”;“1392818331830”;“13928191518 05”;“user1@host.tld”;“5B06272E”;NULL;“user1@host.tld”;“user2@host.tld”

“6046”;NULL;“0”;“1392883544693”;“1392884277084”;“12”;“1392883544693”;“1392884277 084”;“user1@host.tld”;“382A19ED”;NULL;“user1@host.tld”;“user2@host.tld”

“6046”;NULL;“0”;“1392883544693”;“1392884277084”;“12”;“1392883544693”;“1392884277 084”;“user1@host.tld”;“382A19ED”;NULL;“user2@host.tld”;“user1@host.tld”

“6047”;NULL;“0”;“1392884917058”;“1392885419160”;“9”;“1392884917058”;“13928860518 03”;“user1@host.tld”;“382A19ED”;NULL;“user1@host.tld”;“user2@host.tld”

“6047”;NULL;“0”;“1392884917058”;“1392885419160”;“9”;“1392884917058”;“13928860518 03”;“user1@host.tld”;“382A19ED”;NULL;“user2@host.tld”;“user1@host.tld”

“6048”;NULL;“0”;“1392888029824”;“1392888872060”;“9”;“1392888029824”;“13928896518 07”;“user1@host.tld”;“382A19ED”;NULL;“user1@host.tld”;“user2@host.tld”

“6048”;NULL;“0”;“1392888029824”;“1392888872060”;“9”;“1392888029824”;“13928896518 07”;“user1@host.tld”;“382A19ED”;NULL;“user2@host.tld”;“user1@host.tld”

“6049”;NULL;“0”;“1392890999217”;“1392891417545”;“12”;“1392890999217”;“1392892051 806”;“user1@host.tld”;;NULL;“user2@host.tld”;“user1@host.tld”

“6049”;NULL;“0”;“1392890999217”;“1392891417545”;“12”;“1392890999217”;“1392892051 806”;“user1@host.tld”;;NULL;“user1@host.tld”;“user2@host.tld”

“6050”;NULL;“0”;“1392892057438”;“1392892340986”;“7”;“1392892057438”;“13928929518 08”;“user1@host.tld”;“382A19ED”;NULL;“user1@host.tld”;“user2@host.tld”

“6050”;NULL;“0”;“1392892057438”;“1392892340986”;“7”;“1392892057438”;“13928929518 08”;“user1@host.tld”;“382A19ED”;NULL;“user2@host.tld”;“user1@host.tld”

“6051”;NULL;“0”;“1392892980381”;“1392893043922”;“3”;“1392892980381”;“13928938518 02”;“user1@host.tld”;“382A19ED”;NULL;“user2@host.tld”;“user1@host.tld”

“6051”;NULL;“0”;“1392892980381”;“1392893043922”;“3”;“1392892980381”;“13928938518 02”;“user1@host.tld”;“382A19ED”;NULL;“user1@host.tld”;“user2@host.tld”

“6052”;NULL;“0”;“1392894630725”;“1392894642082”;“2”;“1392894630725”;“13928953518 03”;“user1@host.tld”;;NULL;“user2@host.tld”;“user1@host.tld”

“6053”;NULL;“0”;“1392897606459”;“1392898349670”;“19”;“1392897606459”;“1392898951 808”;“user1@host.tld”;“382A19ED”;NULL;“user1@host.tld”;“user2@host.tld”

“6053”;NULL;“0”;“1392897606459”;“1392898349670”;“19”;“1392897606459”;“1392898951 808”;“user1@host.tld”;“382A19ED”;NULL;“user2@host.tld”;“user1@host.tld”

<<<< user1@host.tld/Vacuum-IM 20:51:42 +97 <<<<

  <set xmlns="[http://jabber.org/protocol/rsm](http://jabber.org/protocol/rsm)">

6045

6064

43

Stas_Koleda · February 25, 2014, 3:29am

One of the bugs found.

Try this version of the plugin.

If this does not solve the problem, re-follow the recommendations: http://community.igniterealtime.org/message/236393#236393
Monitoring_1.4.0_patched_2.zip (1727195 Bytes)

Simon_Ehlen · February 25, 2014, 6:02am

Will give it a try. What did you change?

Stas_Koleda · February 25, 2014, 6:17am

I changed the SQL-query, this change allows you to skip duplicate conversations that appear when table ofMessageArchive joined to table ofConversation:

public static final String SELECT_CONVERSATIONS = "SELECT DISTINCT " + "ofConversation.conversationID, " + "ofConversation.room, " + "ofConversation.isExternal, " + "ofConversation.startDate, " + "ofConversation.lastActivity, " + "ofConversation.messageCount, " + "ofConParticipant.joinedDate, " + "ofConParticipant.leftDate, " + "ofConParticipant.bareJID, " + "ofConParticipant.jidResource, " + "ofConParticipant.nickname, "

+ "case when ofConParticipant.bareJID=ofMessageArchive.fromJID then ofMessageArchive.fromJID else ofMessageArchive.toJID end as fromJID, "

+ "case when ofConParticipant.bareJID=ofMessageArchive.toJID then ofMessageArchive.fromJID else ofMessageArchive.toJID end as toJID "

"FROM ofConversation "
"INNER JOIN ofConParticipant ON ofConversation.conversationID = ofConParticipant.conversationID "
“INNER JOIN ofMessageArchive ON ofConParticipant.conversationID = ofMessageArchive.conversationID”;

Simon_Ehlen · February 25, 2014, 7:09am

It seems to work! At least for vacuum-im, in xabber the messages contains wrong timestamps, but I think this is related to xabber.

Thank you very much!

Stas_Koleda · February 25, 2014, 7:37am

Very glad it all worked out.

Thank you too for testing

Mahendra · February 25, 2014, 10:13am

“By the way, this plugin will generate an error when used in conjunction with MS SQL Server, because this database does not understand commands “LIMIT” and “OFFSET”. With PostgreSQL it’s work perfectly.”

True …so what whould be the solution for people like me who use sql server 2008 R2

Stas_Koleda · February 25, 2014, 10:34am

I wish that the developers paid attention to it. I’ll try to fix it.

Dele_Olajide · February 25, 2014, 11:29am

There are no developers

There are only people like you doing your best to make it all work for all of us