Hi!
I have Openfire 4.6.1 version with the monitoring service 2.2.0. I started to noticed the “The messages in this conversation were not archived” message in the Conversation History (Server -> Archiving -> Search Archive) when click to any conversation after upgrading Openfire 4.5 to 4.6. I have seen this error in a similar topic, but looks like is a different error that the LOG file is giving, as follows:
2021.01.25 08:11:51 org.jivesoftware.openfire.archive.ConversationManager - Unable to archive message data!
java.sql.SQLSyntaxErrorException: user lacks privilege or object not found: ISPMFORJID in statement [INSERT INTO ofMessageArchive(messageID, conversationID, fromJID, fromJIDResource, toJID, toJIDResource, sentDate, body, stanza, isPMforJID) VALUES (?,?,?,?,?,?,?,?,?,?)]
at org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source) ~[hsqldb-2.4.1.jar:2.4.1]
at org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source) ~[hsqldb-2.4.1.jar:2.4.1]
at org.hsqldb.jdbc.JDBCPreparedStatement.<init>(Unknown Source) ~[hsqldb-2.4.1.jar:2.4.1]
at org.hsqldb.jdbc.JDBCConnection.prepareStatement(Unknown Source) ~[hsqldb-2.4.1.jar:2.4.1]
at org.apache.commons.dbcp2.DelegatingConnection.prepareStatement(DelegatingConnection.java:292) ~[commons-dbcp2-2.6.0.jar:2.6.0]
at org.apache.commons.dbcp2.DelegatingConnection.prepareStatement(DelegatingConnection.java:292) ~[commons-dbcp2-2.6.0.jar:2.6.0]
at org.jivesoftware.openfire.archive.ConversationManager$MessageArchivingRunnable.store(ConversationManager.java:1172) [monitoring-2.2.0.jar!/:4.6.1]
at org.jivesoftware.openfire.archive.Archiver.run(Archiver.java:154) [xmppserver-4.6.1.jar:4.6.1]
at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511) [?:1.8.0_202]
at java.util.concurrent.FutureTask.run(FutureTask.java:266) [?:1.8.0_202]
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149) [?:1.8.0_202]
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624) [?:1.8.0_202]
at java.lang.Thread.run(Thread.java:748) [?:1.8.0_202]
Caused by: org.hsqldb.HsqlException: user lacks privilege or object not found: ISPMFORJID
at org.hsqldb.error.Error.error(Unknown Source) ~[hsqldb-2.4.1.jar:2.4.1]
at org.hsqldb.error.Error.error(Unknown Source) ~[hsqldb-2.4.1.jar:2.4.1]
at org.hsqldb.ParserDQL.readSimpleColumnName(Unknown Source) ~[hsqldb-2.4.1.jar:2.4.1]
at org.hsqldb.ParserDQL.readSimpleColumnNames(Unknown Source) ~[hsqldb-2.4.1.jar:2.4.1]
at org.hsqldb.ParserDML.compileInsertStatement(Unknown Source) ~[hsqldb-2.4.1.jar:2.4.1]
at org.hsqldb.ParserCommand.compilePart(Unknown Source) ~[hsqldb-2.4.1.jar:2.4.1]
at org.hsqldb.ParserCommand.compileStatement(Unknown Source) ~[hsqldb-2.4.1.jar:2.4.1]
at org.hsqldb.Session.compileStatement(Unknown Source) ~[hsqldb-2.4.1.jar:2.4.1]
at org.hsqldb.StatementManager.compile(Unknown Source) ~[hsqldb-2.4.1.jar:2.4.1]
at org.hsqldb.Session.execute(Unknown Source) ~[hsqldb-2.4.1.jar:2.4.1]
... 11 more
I already tried to reload/reinstall the service but is not working yet. Could anybody know how to fix this, please? Thank you!
Hi! Solved my issue by downgrading to Monitoring Service 2.1.0. The only issue I had with this, is that all conversation from the date I had with version 2.2.0, the conversations can’t be displayed. This is ok for me, since it was not much time I had the update.
Your instance of Openfire seems to have trouble writing a message to the message archive in the database, because there is an issue with a column named ISPMFORJID. You are using the embedded database of Openfire.
This column was added in version 2.2.0 of the Monitoring plugin. For each type of database that Openfire supports, there are different database update scripts that add this column.
I have tried reproducing your problem by installing version 2.2.0 of the Monitoring plugin, and later by first installing 2.1.0 of the Monitoring plugin, and then upgrading to 2.2.0. Both routes give me a database that contains the ISPMFORJID column. In my last test, I’ve also made sure that things are written in the database, which works as expected.
Can you tell me how to reproduce this problem? If you have the problem again, can you install the dbaccess plugin, and use it to look at the structure of your database table (for example, perform this query and look at the column names: select * from ofMessageArchive limit 1;
apt install -y default-jre wget
apt install -y mariadb-server
mysql_secure_installation
mysql -e "create database openfiredb character set utf8 collate utf8_bin;"
mysql -e "create user openfireuser@localhost identified by '123';"
mysql -e "grant all privileges on openfiredb.* to openfireuser@localhost;"
mysql -e "flush privileges;"
wget -O openfire_4.6.2_all.deb https://www.igniterealtime.org/downloadServlet?filename=openfire/openfire_4.6.2_all.deb
dpkg -i openfire_4.6.2_all.deb
This issue occurs only on MySQL, and only when Monitoring plugin version 2.2.0 is being installed in an Openfire that did not have another version of that plugin earlier.
Without being able to reproduce your issue, there’s not a lot more that I can do now. Hopefully it’s some kind of byproduct of the issue that I’ve just found, and will go away after we release the fix for that.
Wait a second: there’s something wrong here. The error that you copy/pasted clearly shows that you’re using the database driver for the embedded database: Caused by: org.hsqldb.HsqlException shows that you’re using the HSQLDB driver.
I cannot see how you’re seeing both this error, and use another database than the embedded one.
Can you check if you’re actually using that database? You might have the database running, but perhaps Openfire is using the embedded database anyway? You can use the Openfire Admin Console to check what database you use. Navigate to Server > Server Manager > Database. That page will show you the database configuration that Openfire is using.
