I would like to purge old chat conversations in all chatroom that are older than 14 days. I am using Openfire 4.3.2 on Windows 202016 and MS SQL 2017.
I have configured my server to purge conversations older than 14 days as below, but when querying the database table ofMucConversationLog, the old messages are still there. Would you help point me in the right direction? Thanks!
The monitoring plugin appears to have some bugs that are specific to SQL Server. Can you check your logs for any related errors.
At least one of the known issues is tracked here: https://github.com/igniterealtime/openfire-monitoring-plugin/issues/47
Thanks Guus for responding to me!
I queried the MSsql database and do see 2 weeks worth of data in table dbo.ofMessageArchive as set. However, it did not truncate those messages from table dbo.ofMucConversationLog. It also did not honor the setting to retrieve the “number of days worth of messages a user is allowed to retrieve”; it allowed retrieval of everything in table dbo.ofMucConversationLog. Below is the error log, but I don’t see anything related to database.
at net.sourceforge.jtds.jdbc.JtdsStatement.executeBatch(JtdsStatement.java:1069) ~[jtds-1.3.1.jar:1.3.1]
at org.apache.commons.dbcp2.DelegatingStatement.executeBatch(DelegatingStatement.java:223) ~[commons-dbcp2-2.5.0.jar:2.5.0]
at org.apache.commons.dbcp2.DelegatingStatement.executeBatch(DelegatingStatement.java:223) ~[commons-dbcp2-2.5.0.jar:2.5.0]
at org.jivesoftware.openfire.pubsub.PubSubPersistenceManager.purgeItems(PubSubPersistenceManager.java:1893) [xmppserver-4.3.2.jar:4.3.2]
at org.jivesoftware.openfire.pubsub.PubSubPersistenceManager.access$000(PubSubPersistenceManager.java:57) [xmppserver-4.3.2.jar:4.3.2]
at org.jivesoftware.openfire.pubsub.PubSubPersistenceManager$2.run(PubSubPersistenceManager.java:283) [xmppserver-4.3.2.jar:4.3.2]
at java.util.concurrent.Executors$RunnableAdapter.call(Unknown Source) [?:1.8.0_201]
at java.util.concurrent.FutureTask.run(Unknown Source) [?:1.8.0_201]
at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source) [?:1.8.0_201]
at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source) [?:1.8.0_201]
at java.lang.Thread.run(Unknown Source) [?:1.8.0_201]
2019.12.03 11:20:30 org.jivesoftware.openfire.pubsub.PubSubPersistenceManager - Incorrect syntax near the keyword ‘LEFT’.
java.sql.BatchUpdateException: Incorrect syntax near the keyword ‘LEFT’.
at net.sourceforge.jtds.jdbc.JtdsStatement.executeBatch(JtdsStatement.java:1069) ~[jtds-1.3.1.jar:1.3.1]
at org.apache.commons.dbcp2.DelegatingStatement.executeBatch(DelegatingStatement.java:223) ~[commons-dbcp2-2.5.0.jar:2.5.0]
at org.apache.commons.dbcp2.DelegatingStatement.executeBatch(DelegatingStatement.java:223) ~[commons-dbcp2-2.5.0.jar:2.5.0]
at org.jivesoftware.openfire.pubsub.PubSubPersistenceManager.purgeItems(PubSubPersistenceManager.java:1893) [xmppserver-4.3.2.jar:4.3.2]
at org.jivesoftware.openfire.pubsub.PubSubPersistenceManager.access$000(PubSubPersistenceManager.java:57) [xmppserver-4.3.2.jar:4.3.2]
at org.jivesoftware.openfire.pubsub.PubSubPersistenceManager$2.run(PubSubPersistenceManager.java:283) [xmppserver-4.3.2.jar:4.3.2]
at java.util.concurrent.Executors$RunnableAdapter.call(Unknown Source) [?:1.8.0_201]
at java.util.concurrent.FutureTask.run(Unknown Source) [?:1.8.0_201]
at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source) [?:1.8.0_201]
at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source) [?:1.8.0_201]
at java.lang.Thread.run(Unknown Source) [?:1.8.0_201]
2019.12.03 11:25:30 org.jivesoftware.openfire.pubsub.PubSubPersistenceManager - Incorrect syntax near the keyword ‘LEFT’.
java.sql.BatchUpdateException: Incorrect syntax near the keyword ‘LEFT’.
at net.sourceforge.jtds.jdbc.JtdsStatement.executeBatch(JtdsStatement.java:1069) ~[jtds-1.3.1.jar:1.3.1]
at org.apache.commons.dbcp2.DelegatingStatement.executeBatch(DelegatingStatement.java:223) ~[commons-dbcp2-2.5.0.jar:2.5.0]
at org.apache.commons.dbcp2.DelegatingStatement.executeBatch(DelegatingStatement.java:223) ~[commons-dbcp2-2.5.0.jar:2.5.0]
at org.jivesoftware.openfire.pubsub.PubSubPersistenceManager.purgeItems(PubSubPersistenceManager.java:1893) [xmppserver-4.3.2.jar:4.3.2]
at org.jivesoftware.openfire.pubsub.PubSubPersistenceManager.access$000(PubSubPersistenceManager.java:57) [xmppserver-4.3.2.jar:4.3.2]
at org.jivesoftware.openfire.pubsub.PubSubPersistenceManager$2.run(PubSubPersistenceManager.java:283) [xmppserver-4.3.2.jar:4.3.2]
at java.util.concurrent.Executors$RunnableAdapter.call(Unknown Source) [?:1.8.0_201]
at java.util.concurrent.FutureTask.run(Unknown Source) [?:1.8.0_201]
at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source) [?:1.8.0_201]
at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source) [?:1.8.0_201]
at java.lang.Thread.run(Unknown Source) [?:1.8.0_201]
2019.12.03 11:30:30 org.jivesoftware.openfire.pubsub.PubSubPersistenceManager - Incorrect syntax near the keyword ‘LEFT’.
java.sql.BatchUpdateException: Incorrect syntax near the keyword ‘LEFT’.
at net.sourceforge.jtds.jdbc.JtdsStatement.executeBatch(JtdsStatement.java:1069) ~[jtds-1.3.1.jar:1.3.1]
at org.apache.commons.dbcp2.DelegatingStatement.executeBatch(DelegatingStatement.java:223) ~[commons-dbcp2-2.5.0.jar:2.5.0]
at org.apache.commons.dbcp2.DelegatingStatement.executeBatch(DelegatingStatement.java:223) ~[commons-dbcp2-2.5.0.jar:2.5.0]
at org.jivesoftware.openfire.pubsub.PubSubPersistenceManager.purgeItems(PubSubPersistenceManager.java:1893) [xmppserver-4.3.2.jar:4.3.2]
at org.jivesoftware.openfire.pubsub.PubSubPersistenceManager.access$000(PubSubPersistenceManager.java:57) [xmppserver-4.3.2.jar:4.3.2]
at org.jivesoftware.openfire.pubsub.PubSubPersistenceManager$2.run(PubSubPersistenceManager.java:283) [xmppserver-4.3.2.jar:4.3.2]
at java.util.concurrent.Executors$RunnableAdapter.call(Unknown Source) [?:1.8.0_201]
at java.util.concurrent.FutureTask.run(Unknown Source) [?:1.8.0_201]
at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source) [?:1.8.0_201]
at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source) [?:1.8.0_201]
at java.lang.Thread.run(Unknown Source) [?:1.8.0_201]|
