Message archiving broken in 3.8.0

After looking over things a bit further, it seems the new plugin uses a diffrent Id in the table ofID to store the last used conversationID. The old plugin used ID 50, the new plugin uses Id 600. This pretty much means that anyone upgrading the plugin from 1.2.0 to 1.3.0 will break their archiving if they have messages previously archived by the old plugin. All you need to do is get the value of the old conversationId marker like so:

select id from ofID where idType=50;

and then insert that into the new Id Type like so:

update ofID set id=6205 where idType=600;

This worked for my installation, you should verify that the numbers match up for yours. the DB upgrade script should do this for you, but does not.

Regards,

Jon

1 Like

Thanks for the investigation of this issue. I have filed a ticket for Leon Roy (author of the updated plugin’s version) to look at this. OF-611

If someone still needs 1.2.0 version of a plugin, it is attached to this message.
monitoring 1.2.0.jar (1528987 Bytes)

after installing the plugin 1.2.0 gives the following error “Stack overflow at line:826”

Well, maybe it can be easily downgraded and 1.3.0 made some changes to the database and 1.2.0 can’t understand them. Can’t help anymore, i’m not a developer or database guy. If you can, follow Jon’s instructions to updated IDs, or revert to a backup prior upgrading to 3.8.0 and 1.3.0.

In case there’s others who get bit by this and have to fix the data here’s a quick run down of what needs to be done. It does not include all of the details needed but should get you headed in the right direction. I exported data from the ofMessageArchive table and did some manipulation in Excel to create SQL statements that I could run to do the heavy lifting. For my uses the fields for the conversation end times were not important so I didn’t do anything with that.

For my database conversationIDs 1 - 119608 were from before the update.

After the update the new conversations started at 1 and I caught it when the count was at around 55.

I decided that I would correct those duplicates by renumbering these 55 conversations beginning at 119701.

  1. Open the ofID table, find the record with idType=600, and set id to a high value. It needs to be higher than it’s current value + the value of the record of idType 50. I set mine to 120000 so there’d be a very clear delineation of new records created.

  2. Open the ofMessageArchive table and change the conversationIDs of the incorrect records to their new conversationIDs (1 = 119701, 2 = 119702, etc)

Example SQL: Update ofMessageArchive set conversationID=119701 where conversationID=1 and sentDate > 1360678596000;

  1. In the ofConversation table create a record for each of these new conversationIDs

Example SQL: INSERT INTO openfire.ofConversation (conversationID, room, isExternal, startDate, lastActivity, messageCount) VALUES (‘119703’,NULL,‘0’,‘1360678839259’,‘1360678839259’,‘1’);

  1. In the ofConParticipants table create 2 records for each of the new conversationIDs. One record for each JID involved in the conversation.

Example SQL: INSERT INTO openfire.ofConParticipant (conversationID, joinedDate, bareJID, jidResource, nickname) VALUES (‘119703’,‘1360678839259’,‘user1@domain.org’,‘spark’,NULL), (‘119703’,‘1360678839259’,‘user2@domain.org’,‘spark’,NULL);

2 Likes

Thanks Jon/wroot. We used the plugin for internal use before sharing out - to prevent conflicts with 1.2 we changed the JiveID on Conversation.java. Must’ve missed that modification before we submitted.

That said I’m not sure that updating the value for 600 is the best approach. I think it might be better to correct the Conversation.java JiveID back to 50 so that at least users still using 1.2.0 will have a fully backwards compatible solution.

I’ll post the updated plugin here shortly.

I’ll see if I can create a script to migrate any new messages created with 1.3.0 to work with previous versions of monitoring.


  • beta1 adds beta to version number till plugin verified by a few people as sound
  • beta2 enforces 3.8 check due to changes in XmppDateUtil in latest trunk
    monitoring-1.3.1-beta2.jar (1541387 Bytes)
3 Likes

Hi Leon,

I tried removing the old Monitoring Service and adding the new one you provided. However, I get the following error when trying to view “Archiving” under “Server.”

The error message is below:

Exception:
java.lang.NullPointerException
          at org.jivesoftware.openfire.plugin.monitoring.archive_002dsearch_jsp._jspService(archive_002dsearch_jsp.java:106)
          at org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:97)
          at javax.servlet.http.HttpServlet.service(HttpServlet.java:820)
          at org.jivesoftware.openfire.container.PluginServlet.handleJSP(PluginServlet.java:247)
          at org.jivesoftware.openfire.container.PluginServlet.service(PluginServlet.java:105)
          at javax.servlet.http.HttpServlet.service(HttpServlet.java:820)
          at org.eclipse.jetty.servlet.ServletHolder.handle(ServletHolder.java:547)
          at org.eclipse.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1359)
          at org.jivesoftware.admin.PluginFilter.doFilter(PluginFilter.java:78)
          at org.eclipse.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1330)
          at org.jivesoftware.admin.AuthCheckFilter.doFilter(AuthCheckFilter.java:164)
          at org.eclipse.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1330)
          at com.opensymphony.module.sitemesh.filter.PageFilter.parsePage(PageFilter.java:118)
          at com.opensymphony.module.sitemesh.filter.PageFilter.doFilter(PageFilter.java:52)
          at org.eclipse.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1330)
          at org.jivesoftware.util.LocaleFilter.doFilter(LocaleFilter.java:74)
          at org.eclipse.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1330)
          at org.jivesoftware.util.SetCharacterEncodingFilter.doFilter(SetCharacterEncodingFilter.java:50)
          at org.eclipse.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1330)
          at org.jivesoftware.admin.PluginFilter.doFilter(PluginFilter.java:78)
          at org.eclipse.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1330)
          at org.jivesoftware.admin.AuthCheckFilter.doFilter(AuthCheckFilter.java:164)
          at org.eclipse.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1330)
          at org.eclipse.jetty.servlet.ServletHandler.doHandle(ServletHandler.java:478)
          at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:119)
          at org.eclipse.jetty.security.SecurityHandler.handle(SecurityHandler.java:520)
          at org.eclipse.jetty.server.session.SessionHandler.doHandle(SessionHandler.java:227)
          at org.eclipse.jetty.server.handler.ContextHandler.doHandle(ContextHandler.java:941)
          at org.eclipse.jetty.servlet.ServletHandler.doScope(ServletHandler.java:409)
          at org.eclipse.jetty.server.session.SessionHandler.doScope(SessionHandler.java:186)
          at org.eclipse.jetty.server.handler.ContextHandler.doScope(ContextHandler.java:875)
          at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:117)
          at org.eclipse.jetty.server.handler.ContextHandlerCollection.handle(ContextHandlerCollection.java:250)
          at org.eclipse.jetty.server.handler.HandlerCollection.handle(HandlerCollection.java:149)
          at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:110)
          at org.eclipse.jetty.server.Server.handle(Server.java:349)
          at org.eclipse.jetty.server.HttpConnection.handleRequest(HttpConnection.java:441)
          at org.eclipse.jetty.server.HttpConnection$RequestHandler.headerComplete(HttpConnection.java:919)
          at org.eclipse.jetty.http.HttpParser.parseNext(HttpParser.java:582)
          at org.eclipse.jetty.http.HttpParser.parseAvailable(HttpParser.java:218)
          at org.eclipse.jetty.server.AsyncHttpConnection.handle(AsyncHttpConnection.java:51)
          at org.eclipse.jetty.io.nio.SelectChannelEndPoint.handle(SelectChannelEndPoint.java:586)
          at org.eclipse.jetty.io.nio.SelectChannelEndPoint$1.run(SelectChannelEndPoint.java:44)
          at org.eclipse.jetty.util.thread.QueuedThreadPool.runJob(QueuedThreadPool.java:598)
          at org.eclipse.jetty.util.thread.QueuedThreadPool$3.run(QueuedThreadPool.java:533)
          at java.lang.Thread.run(Unknown Source)

Thanks!

UPDATE: I renamed the file to monitoring.jar and uploaded again. It now works after rebuilding the archiving index. Still missing the messages between updating to 1.3.0 and placing this update (beta 2).

1 Like

Yeah it’s a known issue that 1.3.0 did not save messages properly. However 1.3.1 should be saving messages now and any messages saved in plugins prior to 1.3.0 should also be viewable. Are you seeing this?

Yes, I am. I just was wondering what was the best and easiest method for taking the messages that were not archived correctly in the mysql database and placing them correctly in the database for viewing. We’re missing a couple days that I can query manually, but this is certainly not nearly as easy as openfire makes it (and I’m not the only user of the admin cp).

Hello,

There will be a new version of the plugin that automatically makes the needed changes on the database?

I’m not a developer or database guy and I don’t feel comfortable trying to making those changes.

Hi blake, haven’t had much time of late but working on a migration script to resolve missing messages for users.

don’t know how voting betas into releases works, but I’m chiming in that 1.3.1b2 is working like a dream for me. (openfire running on an old xp box, 150 ldap users with a couple dozen really doing much)

Everything is working fine with the exception that group chats are showing names of null.

Any ideas?

http://i.imgur.com/Ddcl2zI.png

Also if I wanted to start from scratch can I just drop the tables and the plugin would just recreate them?

This error for me update to beta2

Exception:

java.lang.ClassCastException: org.jivesoftware.openfire.plugin.MonitoringPlugin cannot be cast to org.jivesoftware.openfire.plugin.MonitoringPlugin     at org.jivesoftware.openfire.plugin.monitoring.archive_002dsearch_jsp._jspService(archive_002dsearch_jsp.java:104)     at org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:97)     at javax.servlet.http.HttpServlet.service(HttpServlet.java:820)     at org.jivesoftware.openfire.container.PluginServlet.handleJSP(PluginServlet.java:247)     at org.jivesoftware.openfire.container.PluginServlet.service(PluginServlet.java:105)     at javax.servlet.http.HttpServlet.service(HttpServlet.java:820)     at org.eclipse.jetty.servlet.ServletHolder.handle(ServletHolder.java:547)     at org.eclipse.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1359)     at org.jivesoftware.admin.PluginFilter.doFilter(PluginFilter.java:78)     at org.eclipse.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1330)     at org.jivesoftware.admin.AuthCheckFilter.doFilter(AuthCheckFilter.java:164)     at org.eclipse.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1330)     at com.opensymphony.module.sitemesh.filter.PageFilter.parsePage(PageFilter.java:118)     at com.opensymphony.module.sitemesh.filter.PageFilter.doFilter(PageFilter.java:52)     at org.eclipse.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1330)     at org.jivesoftware.util.LocaleFilter.doFilter(LocaleFilter.java:74)     at org.eclipse.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1330)     at org.jivesoftware.util.SetCharacterEncodingFilter.doFilter(SetCharacterEncodingFilter.java:50)     at org.eclipse.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1330)     at org.jivesoftware.admin.PluginFilter.doFilter(PluginFilter.java:78)     at org.eclipse.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1330)     at org.jivesoftware.admin.AuthCheckFilter.doFilter(AuthCheckFilter.java:164)     at org.eclipse.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1330)     at org.eclipse.jetty.servlet.ServletHandler.doHandle(ServletHandler.java:478)     at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:119)     at org.eclipse.jetty.security.SecurityHandler.handle(SecurityHandler.java:520)     at org.eclipse.jetty.server.session.SessionHandler.doHandle(SessionHandler.java:227)     at org.eclipse.jetty.server.handler.ContextHandler.doHandle(ContextHandler.java:941)     at org.eclipse.jetty.servlet.ServletHandler.doScope(ServletHandler.java:409)     at org.eclipse.jetty.server.session.SessionHandler.doScope(SessionHandler.java:186)     at org.eclipse.jetty.server.handler.ContextHandler.doScope(ContextHandler.java:875)     at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:117)     at org.eclipse.jetty.server.handler.ContextHandlerCollection.handle(ContextHandlerCollection.java:250)     at org.eclipse.jetty.server.handler.HandlerCollection.handle(HandlerCollection.java:149)     at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:110)     at org.eclipse.jetty.server.Server.handle(Server.java:349)     at org.eclipse.jetty.server.HttpConnection.handleRequest(HttpConnection.java:441)     at org.eclipse.jetty.server.HttpConnection$RequestHandler.headerComplete(HttpConnection.java:919)     at org.eclipse.jetty.http.HttpParser.parseNext(HttpParser.java:582)     at org.eclipse.jetty.http.HttpParser.parseAvailable(HttpParser.java:218)     at org.eclipse.jetty.server.AsyncHttpConnection.handle(AsyncHttpConnection.java:51)     at org.eclipse.jetty.io.nio.SelectChannelEndPoint.handle(SelectChannelEndPoint.java:586)     at org.eclipse.jetty.io.nio.SelectChannelEndPoint$1.run(SelectChannelEndPoint.java:44)     at org.eclipse.jetty.util.thread.QueuedThreadPool.runJob(QueuedThreadPool.java:598)     at org.eclipse.jetty.util.thread.QueuedThreadPool$3.run(QueuedThreadPool.java:533)     at java.lang.Thread.run(Thread.java:662)

i’ve rolled back to 1.2 - to many bugs not enough respones for support.

just a guess, but you probably would want to truncate the table instead of dropping… plugin might freak out if its expecting a table that is no longer there…

or maybe uninstall the plugin, check if the tables still exist (drop if they do) and then re-install the plugin to allow it to create the tables again…

Regarding support, did you take your time an look through the forum? You may find this one useful:

http://community.igniterealtime.org/docs/DOC-2199

Deleting plugins does not automatically delete the table in the DB. The plugin creates performs a number of DB changes (below is the PostGREs version of the changes). It also creates a ofID entry with IDtype=600 and the next number to use for a archive entry.

Revoking to an older version of Monitoring or deleting a plugin requires some DB maintenance. And that’s always difficult.

INSERT INTO ofVersion (name, version) VALUES (‘monitoring’, 1);

CREATE TABLE ofConversation (
conversationID        INTEGER       NOT NULL,
room                  VARCHAR(1024) NULL,
isExternal            SMALLINT      NOT NULL,
startDate             BIGINT        NOT NULL,
lastActivity          BIGINT        NOT NULL,
messageCount          INTEGER       NOT NULL,
CONSTRAINT ofConversation_pk PRIMARY KEY (conversationID)
);
CREATE INDEX ofConversation_ext_idx   ON ofConversation (isExternal);
CREATE INDEX ofConversation_start_idx ON ofConversation (startDate);
CREATE INDEX ofConversation_last_idx  ON ofConversation (lastActivity);
CREATE TABLE ofConParticipant (
conversationID       INTEGER       NOT NULL,
joinedDate           BIGINT        NOT NULL,
leftDate             BIGINT        NULL,
bareJID              VARCHAR(255)  NOT NULL,
jidResource          VARCHAR(255)  NOT NULL,
nickname             VARCHAR(255)  NULL
);
CREATE INDEX ofConParticipant_conv_idx ON ofConParticipant (conversationID, bareJID, jidResource, joinedDate);
CREATE INDEX ofConParticipant_jid_idx ON ofConParticipant (bareJID);
CREATE TABLE ofMessageArchive (
conversationID    INTEGER         NOT NULL,
fromJID           VARCHAR(1024)   NOT NULL,
fromJIDResource   VARCHAR(1024)   NULL,
toJID             VARCHAR(1024)   NOT NULL,
toJIDResource     VARCHAR(1024)   NULL,
sentDate          BIGINT          NOT NULL,
body              TEXT
);
CREATE INDEX ofMessageArchive_con_idx ON ofMessageArchive (conversationID);
CREATE TABLE ofRRDs (
id            VARCHAR(100)         NOT NULL,
updatedDate   BIGINT               NOT NULL,
bytes         bytea                NULL,
CONSTRAINT ofRRDs_pk PRIMARY KEY (id)
);

This has been tried. I even tried setting up a new blank VM And starting from scratch and install 1.3.1 beta 2 from scratch and I Get the small result.

Thanks for the input though.

Just a head up about the XMPP date time parsing bug regarding the monitoring plugin. This is tracked as OF-646 and should be fixed in the latest nighlies. Please test.

Is there a query to extract missing chat logs from the embedded-db? I really need access to the missing chat history.

Thanks.