Unknown column 'stanza' in 'field list'

Getting this error "Unknown column ‘stanza’ in ‘field list’ in the error logs and messages are not getting archived.
Openfire Version : 4.7.4
Monitoring version : 2.4.1
Mysql : 5.6

Any clue what this error could be related found an unanswered query from 2015.

It appears that one of the database upgrade scripts that are part of the Monitoring Service plugin has ran into an error.

Can you restart the plugin in the admin console, and see what is being logged?

Also, please provide the result of this database query?

SELECT * FROM ofVersion WHERE name = 'monitoring';

Hello Guus thank you for your reply
The result of DB query is: monitoring version = 2

image

The following is the log output after restarting the plugin:

2023.05.29 13:01:21 INFO [pool-796-thread-1]: org.jivesoftware.openfire.container.PluginManager - Successfully loaded plugin 'monitoring-2.5.0'.
2023.05.29 13:01:21 INFO [pool-4-thread-1]: org.jivesoftware.openfire.container.PluginMonitor - Finished processing all plugins.
2023.05.29 13:01:21 DEBUG [pool-monitoring5]: org.jivesoftware.openfire.archive.ArchiveIndexer[MESSAGE] - ... started to index messages to rebuild the Lucene index.
2023.05.29 13:01:21 DEBUG [pool-monitoring4]: org.jivesoftware.openfire.archive.ArchiveIndexer[MUCSEARCH] - ... started to index MUC messages to rebuild the Lucene index.
2023.05.29 13:01:21 ERROR [pool-monitoring5]: org.jivesoftware.openfire.archive.ArchiveIndexer[MESSAGE] - An exception occurred while trying to fetch all messages from the database to rebuild the Lucene index.
java.sql.SQLSyntaxErrorException: Unknown column 'isPMforJID' in 'field list'
    at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:120) ~[mysql-connector-java-8.0.28.jar:8.0.28]
    at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122) ~[mysql-connector-java-8.0.28.jar:8.0.28]
    at com.mysql.cj.jdbc.ClientPreparedStatement.executeInternal(ClientPreparedStatement.java:953) ~[mysql-connector-java-8.0.28.jar:8.0.28]
    at com.mysql.cj.jdbc.ClientPreparedStatement.executeQuery(ClientPreparedStatement.java:1009) ~[mysql-connector-java-8.0.28.jar:8.0.28]
    at org.apache.commons.dbcp2.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:122) ~[commons-dbcp2-2.9.0.jar:2.9.0]
    at org.apache.commons.dbcp2.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:122) ~[commons-dbcp2-2.9.0.jar:2.9.0]
    at org.jivesoftware.database.ProfiledConnection$TimedPreparedStatement.executeQuery(ProfiledConnection.java:785) ~[xmppserver-4.7.4.jar:4.7.4]
    at com.reucon.openfire.plugin.archive.impl.MessageIndexer.indexMessages(MessageIndexer.java:130) ~[monitoring-2.5.0.jar:?]
    at com.reucon.openfire.plugin.archive.impl.MessageIndexer.doRebuildIndex(MessageIndexer.java:89) ~[monitoring-2.5.0.jar:?]
    at org.jivesoftware.openfire.index.LuceneIndexer.lambda$rebuildIndex$1(LuceneIndexer.java:347) ~[monitoring-2.5.0.jar:?]
    at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:515) [?:?]
    at java.util.concurrent.FutureTask.run(FutureTask.java:264) [?:?]
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128) [?:?]
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628) [?:?]
    at java.lang.Thread.run(Thread.java:829) [?:?]
2023.05.29 13:01:21 DEBUG [pool-monitoring5]: org.jivesoftware.openfire.archive.ArchiveIndexer[MESSAGE] - ... finished indexing messages to update the Lucene index. Lasted indexed message date 1970-01-01T00:00:00Z
2023.05.29 13:01:21 DEBUG [pool-monitoring5]: org.jivesoftware.openfire.archive.ArchiveIndexer[MESSAGE] - Updating modification date to: 1970-01-01T00:00:00Z
2023.05.29 13:01:21 DEBUG [pool-monitoring3]: org.jivesoftware.openfire.archive.ArchiveIndexer[CONVERSATION] - ... identified 10 conversations.
2023.05.29 13:01:21 DEBUG [pool-monitoring3]: org.jivesoftware.openfire.archive.ArchiveIndexer[CONVERSATION] - ... started to index conversations to rebuild the Lucene index.
2023.05.29 13:01:21 DEBUG [pool-monitoring5]: org.jivesoftware.openfire.archive.ArchiveIndexer[MESSAGE] - Finished rebuilding the Lucene index. Duration: PT0.082805S
2023.05.29 13:01:21 DEBUG [pool-monitoring4]: org.jivesoftware.openfire.archive.ArchiveIndexer[MUCSEARCH] - ... finished the entire result set. Processed 0 messages in total.

The DB i am using was earlier connected to Openfire 4.0.2.

The version of the database scheme for Monitoring is 2, but should be 8. Apparently, there’s something preventing the plugin to successfully execute scrypt 3.

Please shut down Openfire, backup your database, and then try to manually run the third update script manually on your database:

ALTER TABLE ofMessageArchive ADD COLUMN messageID BIGINT NULL;
ALTER TABLE ofMessageArchive ADD COLUMN stanza TEXT NULL;

-- Update database version
UPDATE ofVersion SET version = 3 WHERE name = 'monitoring';

Try to address any issues that come up.

When this succeeds, restart Openfire, wait for a minute or two, and see if update scripts 4 through 8 are automatically executed, by verifying SELECT * FROM ofVersion WHERE name = 'monitoring'; again.