Monitoring Service error after update

Server 2008R2 x64
Upgrade from 3_6_4 to 4_4_4.
Monitoring Service 1.8.1
The new version is connected to a copy of the old database
set up groups, authorization, etc.
clients are connected, the problem with the history is not loaded on the client and is not displayed on the server only a list of messages without a body is visible
There is an error in the logs

2020.01.09 12:25:28 com.reucon.openfire.plugin.archive.impl.JdbcPersistenceManager - Error selecting conversation
com.microsoft.sqlserver.jdbc.SQLServerException: Недопустимое имя столбца stanza.
	at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(SQLServerException.java:234) ~[mssql-jdbc-7.2.2.jre8.jar:?]
	at com.microsoft.sqlserver.jdbc.SQLServerResultSet.findColumn(SQLServerResultSet.java:686) ~[mssql-jdbc-7.2.2.jre8.jar:?]
	at com.microsoft.sqlserver.jdbc.SQLServerResultSet.getString(SQLServerResultSet.java:2503) ~[mssql-jdbc-7.2.2.jre8.jar:?]
	at org.apache.commons.dbcp2.DelegatingResultSet.getString(DelegatingResultSet.java:898) ~[commons-dbcp2-2.6.0.jar:2.6.0]
	at org.apache.commons.dbcp2.DelegatingResultSet.getString(DelegatingResultSet.java:898) ~[commons-dbcp2-2.6.0.jar:2.6.0]
	at com.reucon.openfire.plugin.archive.impl.JdbcPersistenceManager.extractMessage(JdbcPersistenceManager.java:976) ~[monitoring-1.8.1.jar!/:?]
	at com.reucon.openfire.plugin.archive.impl.JdbcPersistenceManager.getConversation(JdbcPersistenceManager.java:876) [monitoring-1.8.1.jar!/:?]
	at com.reucon.openfire.plugin.archive.impl.JdbcPersistenceManager.getConversation(JdbcPersistenceManager.java:791) [monitoring-1.8.1.jar!/:?]
	at com.reucon.openfire.plugin.archive.xep0136.IQRetrieveHandler.retrieve(IQRetrieveHandler.java:107) [monitoring-1.8.1.jar!/:?]
	at com.reucon.openfire.plugin.archive.xep0136.IQRetrieveHandler.handleIQ(IQRetrieveHandler.java:39) [monitoring-1.8.1.jar!/:?]
	at com.reucon.openfire.plugin.archive.xep.AbstractXepSupport$1.handleIQ(AbstractXepSupport.java:43) [monitoring-1.8.1.jar!/:?]
	at org.jivesoftware.openfire.handler.IQHandler.process(IQHandler.java:62) [xmppserver-4.4.4.jar:4.4.4]
	at org.jivesoftware.openfire.IQRouter.handle(IQRouter.java:369) [xmppserver-4.4.4.jar:4.4.4]
	at org.jivesoftware.openfire.IQRouter.route(IQRouter.java:112) [xmppserver-4.4.4.jar:4.4.4]
	at org.jivesoftware.openfire.spi.PacketRouterImpl.route(PacketRouterImpl.java:74) [xmppserver-4.4.4.jar:4.4.4]
	at org.jivesoftware.openfire.net.StanzaHandler.processIQ(StanzaHandler.java:360) [xmppserver-4.4.4.jar:4.4.4]
	at org.jivesoftware.openfire.net.ClientStanzaHandler.processIQ(ClientStanzaHandler.java:95) [xmppserver-4.4.4.jar:4.4.4]
	at org.jivesoftware.openfire.net.StanzaHandler.process(StanzaHandler.java:314) [xmppserver-4.4.4.jar:4.4.4]
	at org.jivesoftware.openfire.net.StanzaHandler.process(StanzaHandler.java:201) [xmppserver-4.4.4.jar:4.4.4]
	at org.jivesoftware.openfire.nio.ConnectionHandler.messageReceived(ConnectionHandler.java:183) [xmppserver-4.4.4.jar:4.4.4]
	at org.apache.mina.core.filterchain.DefaultIoFilterChain$TailFilter.messageReceived(DefaultIoFilterChain.java:1015) [mina-core-2.1.3.jar:?]
	at org.apache.mina.core.filterchain.DefaultIoFilterChain.callNextMessageReceived(DefaultIoFilterChain.java:650) [mina-core-2.1.3.jar:?]
	at org.apache.mina.core.filterchain.DefaultIoFilterChain.access$1300(DefaultIoFilterChain.java:49) [mina-core-2.1.3.jar:?]
	at org.apache.mina.core.filterchain.DefaultIoFilterChain$EntryImpl$1.messageReceived(DefaultIoFilterChain.java:1128) [mina-core-2.1.3.jar:?]
	at org.apache.mina.core.filterchain.IoFilterAdapter.messageReceived(IoFilterAdapter.java:122) [mina-core-2.1.3.jar:?]
	at org.apache.mina.core.filterchain.DefaultIoFilterChain.callNextMessageReceived(DefaultIoFilterChain.java:650) [mina-core-2.1.3.jar:?]
	at org.apache.mina.core.filterchain.DefaultIoFilterChain.access$1300(DefaultIoFilterChain.java:49) [mina-core-2.1.3.jar:?]
	at org.apache.mina.core.filterchain.DefaultIoFilterChain$EntryImpl$1.messageReceived(DefaultIoFilterChain.java:1128) [mina-core-2.1.3.jar:?]
	at org.apache.mina.filter.codec.ProtocolCodecFilter$ProtocolDecoderOutputImpl.flush(ProtocolCodecFilter.java:413) [mina-core-2.1.3.jar:?]
	at org.apache.mina.filter.codec.ProtocolCodecFilter.messageReceived(ProtocolCodecFilter.java:257) [mina-core-2.1.3.jar:?]
	at org.apache.mina.core.filterchain.DefaultIoFilterChain.callNextMessageReceived(DefaultIoFilterChain.java:650) [mina-core-2.1.3.jar:?]
	at org.apache.mina.core.filterchain.DefaultIoFilterChain.access$1300(DefaultIoFilterChain.java:49) [mina-core-2.1.3.jar:?]
	at org.apache.mina.core.filterchain.DefaultIoFilterChain$EntryImpl$1.messageReceived(DefaultIoFilterChain.java:1128) [mina-core-2.1.3.jar:?]
	at org.apache.mina.core.filterchain.IoFilterEvent.fire(IoFilterEvent.java:106) [mina-core-2.1.3.jar:?]
	at org.apache.mina.core.session.IoEvent.run(IoEvent.java:89) [mina-core-2.1.3.jar:?]
	at org.apache.mina.filter.executor.OrderedThreadPoolExecutor$Worker.runTask(OrderedThreadPoolExecutor.java:766) [mina-core-2.1.3.jar:?]
	at org.apache.mina.filter.executor.OrderedThreadPoolExecutor$Worker.runTasks(OrderedThreadPoolExecutor.java:758) [mina-core-2.1.3.jar:?]
	at org.apache.mina.filter.executor.OrderedThreadPoolExecutor$Worker.run(OrderedThreadPoolExecutor.java:697) [mina-core-2.1.3.jar:?]
	at java.lang.Thread.run(Unknown Source) [?:1.8.0_202]

Restart,
reinstalling the plugin via the Web does not help

Microsoft SQL Server 10.50.1600|
|JDBC Driver Version:|7.2.2.0|
URL:|jdbc:sqlserver://DB4:1433;useBulkCopyForBatchInsert=false;cancelQueryTimeout=-1;sslProtocol=TLS;jaasConfigurationName=SQLJDBCDriver;statementPoolingCacheSize=0;serverPreparedStatementDiscardThreshold=10;enablePrepareOnFirstPreparedStatementCall=false;fips=false;socketTimeout=0;authentication=NotSpecified;authenticationScheme=nativeAuthentication;xopenStates=false;sendTimeAsDatetime=true;trustStoreType=JKS;trustServerCertificate=false;TransparentNetworkIPResolution=true;serverNameAsACE=false;sendStringParametersAsUnicode=true;selectMethod=direct;responseBuffering=adaptive;queryTimeout=-1;packetSize=8000;multiSubnetFailover=false;loginTimeout=15;lockTimeout=-1;lastUpdateCount=true;encrypt=false;disableStatementPooling=true;databaseName=chat_20;columnEncryptionSetting=Disabled;applicationName=Openfire;applicationIntent=readwrite;|
|Transaction Support:|Yes|
|Transaction Isolation Level:|TRANSACTION_READ_COMMITTED|
|Supports multiple connections
open at once:|Yes|
|In read-only mode:|No|

Google suggests that

Недопустимое имя столбца stanza

translates to

Invalid column name stanza

Assuming that’s correct, it looks like the upgrade scripts for the monitoring plugin have failed; though I can’t help any more than that - apologies!

Greg

Thanks. I removed the plugin and its directories.
Installed again and the error is no more. Unfortunately, it was too late to check the version in ofVersion
Probably the problem was that I did not clear the plugin directory after removal

Search through the Web interface works. But there is always an error in the log

2020.01.14 06:51:21 com.reucon.openfire.plugin.archive.impl.JdbcPersistenceManager - Error selecting conversation
com.microsoft.sqlserver.jdbc.SQLServerException: Invalid column name stanza.
	at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(SQLServerException.java:234) ~[mssql-jdbc-7.2.2.jre8.jar:?]
	at com.microsoft.sqlserver.jdbc.SQLServerResultSet.findColumn(SQLServerResultSet.java:686) ~[mssql-jdbc-7.2.2.jre8.jar:?]
	at com.microsoft.sqlserver.jdbc.SQLServerResultSet.getString(SQLServerResultSet.java:2503) ~[mssql-jdbc-7.2.2.jre8.jar:?]
	at org.apache.commons.dbcp2.DelegatingResultSet.getString(DelegatingResultSet.java:898) ~[commons-dbcp2-2.6.0.jar:2.6.0]
	at org.apache.commons.dbcp2.DelegatingResultSet.getString(DelegatingResultSet.java:898) ~[commons-dbcp2-2.6.0.jar:2.6.0]
	at com.reucon.openfire.plugin.archive.impl.JdbcPersistenceManager.extractMessage(JdbcPersistenceManager.java:976) ~[monitoring-1.8.1.jar!/:?]
	at com.reucon.openfire.plugin.archive.impl.JdbcPersistenceManager.getConversation(JdbcPersistenceManager.java:876) [monitoring-1.8.1.jar!/:?]
	at com.reucon.openfire.plugin.archive.impl.JdbcPersistenceManager.getConversation(JdbcPersistenceManager.java:791) [monitoring-1.8.1.jar!/:?]
	at com.reucon.openfire.plugin.archive.xep0136.IQRetrieveHandler.retrieve(IQRetrieveHandler.java:107) [monitoring-1.8.1.jar!/:?]
	at com.reucon.openfire.plugin.archive.xep0136.IQRetrieveHandler.handleIQ(IQRetrieveHandler.java:39) [monitoring-1.8.1.jar!/:?]
	at com.reucon.openfire.plugin.archive.xep.AbstractXepSupport$1.handleIQ(AbstractXepSupport.java:43) [monitoring-1.8.1.jar!/:?]
	at org.jivesoftware.openfire.handler.IQHandler.process(IQHandler.java:62) [xmppserver-4.5.0.jar:4.5.0]
	at org.jivesoftware.openfire.IQRouter.handle(IQRouter.java:369) [xmppserver-4.5.0.jar:4.5.0]
	at org.jivesoftware.openfire.IQRouter.route(IQRouter.java:112) [xmppserver-4.5.0.jar:4.5.0]
	at org.jivesoftware.openfire.spi.PacketRouterImpl.route(PacketRouterImpl.java:74) [xmppserver-4.5.0.jar:4.5.0]
	at org.jivesoftware.openfire.net.StanzaHandler.processIQ(StanzaHandler.java:366) [xmppserver-4.5.0.jar:4.5.0]
	at org.jivesoftware.openfire.net.ClientStanzaHandler.processIQ(ClientStanzaHandler.java:95) [xmppserver-4.5.0.jar:4.5.0]
	at org.jivesoftware.openfire.net.StanzaHandler.process(StanzaHandler.java:316) [xmppserver-4.5.0.jar:4.5.0]
	at org.jivesoftware.openfire.net.StanzaHandler.process(StanzaHandler.java:203) [xmppserver-4.5.0.jar:4.5.0]
	at org.jivesoftware.openfire.nio.ConnectionHandler.messageReceived(ConnectionHandler.java:183) [xmppserver-4.5.0.jar:4.5.0]
	at org.apache.mina.core.filterchain.DefaultIoFilterChain$TailFilter.messageReceived(DefaultIoFilterChain.java:1015) [mina-core-2.1.3.jar:?]
	at org.apache.mina.core.filterchain.DefaultIoFilterChain.callNextMessageReceived(DefaultIoFilterChain.java:650) [mina-core-2.1.3.jar:?]
	at org.apache.mina.core.filterchain.DefaultIoFilterChain.access$1300(DefaultIoFilterChain.java:49) [mina-core-2.1.3.jar:?]
	at org.apache.mina.core.filterchain.DefaultIoFilterChain$EntryImpl$1.messageReceived(DefaultIoFilterChain.java:1128) [mina-core-2.1.3.jar:?]
	at org.apache.mina.core.filterchain.IoFilterAdapter.messageReceived(IoFilterAdapter.java:122) [mina-core-2.1.3.jar:?]
	at org.apache.mina.core.filterchain.DefaultIoFilterChain.callNextMessageReceived(DefaultIoFilterChain.java:650) [mina-core-2.1.3.jar:?]
	at org.apache.mina.core.filterchain.DefaultIoFilterChain.access$1300(DefaultIoFilterChain.java:49) [mina-core-2.1.3.jar:?]
	at org.apache.mina.core.filterchain.DefaultIoFilterChain$EntryImpl$1.messageReceived(DefaultIoFilterChain.java:1128) [mina-core-2.1.3.jar:?]
	at org.apache.mina.filter.codec.ProtocolCodecFilter$ProtocolDecoderOutputImpl.flush(ProtocolCodecFilter.java:413) [mina-core-2.1.3.jar:?]
	at org.apache.mina.filter.codec.ProtocolCodecFilter.messageReceived(ProtocolCodecFilter.java:257) [mina-core-2.1.3.jar:?]
	at org.apache.mina.core.filterchain.DefaultIoFilterChain.callNextMessageReceived(DefaultIoFilterChain.java:650) [mina-core-2.1.3.jar:?]
	at org.apache.mina.core.filterchain.DefaultIoFilterChain.access$1300(DefaultIoFilterChain.java:49) [mina-core-2.1.3.jar:?]
	at org.apache.mina.core.filterchain.DefaultIoFilterChain$EntryImpl$1.messageReceived(DefaultIoFilterChain.java:1128) [mina-core-2.1.3.jar:?]
	at org.apache.mina.core.filterchain.IoFilterEvent.fire(IoFilterEvent.java:106) [mina-core-2.1.3.jar:?]
	at org.apache.mina.core.session.IoEvent.run(IoEvent.java:89) [mina-core-2.1.3.jar:?]
	at org.apache.mina.filter.executor.OrderedThreadPoolExecutor$Worker.runTask(OrderedThreadPoolExecutor.java:766) [mina-core-2.1.3.jar:?]
	at org.apache.mina.filter.executor.OrderedThreadPoolExecutor$Worker.runTasks(OrderedThreadPoolExecutor.java:758) [mina-core-2.1.3.jar:?]
	at org.apache.mina.filter.executor.OrderedThreadPoolExecutor$Worker.run(OrderedThreadPoolExecutor.java:697) [mina-core-2.1.3.jar:?]
	at java.lang.Thread.run(Unknown Source) [?:1.8.0_202]

How should the update script work?
Change the table structure? If I understand correctly, then I have an updated structure
https://1drv.ms/u/s!AoS-71VAeM4yyhhMuwkThfab4DhA?e=boRab4
for old messages (before the update), the stanza field == NULL ,
the new ones have this field filled in. Select works correctly
https://1drv.ms/u/s!AoS-71VAeM4yyhnrM6k7QCtRlCOe?e=ZoYYVj
I want to deal with the error.
do you have any thoughts? what can be checked? turn it on?

I have same problem now, when I updated from 1.7.0 to 2.0.1
Search in Web interface works, but from clients - not.
And this error in log

Hi Davis - are you using Microsoft SQL Server?

What value is there in the ofVersion table for the row with name monitoring ?

What’s the definition of the database table ofMessageArchive in your database?

There appears to be a column missing. That column should have been added by database update script 3, which was added in this commit: https://github.com/igniterealtime/openfire-monitoring-plugin/commit/70968913df66942907a2003ab650725e79f7ff7b

Hi guus
In resources\database\upgrade no “script 3” .

ofMessageArchive ofVersion
And this error in log

Hi guus,
Details are in Issue #72
I use PostgreSQL and I have this column in table.
But seems like this error appears only when client uses XEP-0136 and it says that there is not stanza column in RESULT

My problem fixed in pull request https://github.com/igniterealtime/openfire-monitoring-plugin/pull/96
Unfortunately it is still unmerged

Fixed in 2.1.0

1 Like