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.
Hello Guus thank you for your reply
The result of DB query is: monitoring version = 2
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 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.