MONITORING SERVICE - The messages in this conversation were not archived

eleazar · February 3, 2021, 4:29pm

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!

eleazar · January 25, 2021, 8:35pm

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.

guus · February 3, 2021, 5:40pm

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;

gprilopes · February 10, 2021, 8:18pm

Hello, I’m not using the embedded database, I’m using Maria DB 10, I didn’t do anything extraordinary, I’ll send you my openfire installation script

gprilopes · February 12, 2021, 9:45am

PACOTES MANIPULAÇÃO DE ARQUIVOS

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

http://IP:9090 (admin/Tipo@12345)

configuração do banco na interface do openfire:

jdbc:mysql://127.0.0.1:3306/openfiredb?rewriteBatchedStatements=true&characterEncoding=UTF-8&characterSetResults=UTF-8&serverTimezone=UTC

guus · February 12, 2021, 10:24am

Using your exact steps, I have not been able to reproduce this exact problem. I have, however, found a related issue: https://github.com/igniterealtime/openfire-monitoring-plugin/issues/175

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.

gprilopes · February 12, 2021, 12:15pm

this is the table, is something wrong?

guus · February 12, 2021, 12:44pm

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.