I am setting up a new openfire installation (version4.0.2) pointing to SQL server 2014. this all worked fine and now I’m trying to add and use the monitoring service (version 1.5.1). Once adding said service I started getting errors in the log and nothing monitored regardless of the archiving settings I have added. Initially it looked like all the tables that needed to be added to the SQL database were nto being added. I eventually found this article openfire 3.9.1 with monitoring service 1.4 stack overflow 1824 which has a SQL script that created the tables. Ran this and started to get this error java.sql.SQLException: The ntext data type cannot be selected as DISTINCT because it is not comparable. Agian I found this article OpenFire MSSQL error using DISTINCT with ntext which suggested try to change in table “ofMessageArchive” the “body” field from ntext to nvarchar(MAX), So dropping the table and recreating it with this change removed this error as well. now however when I send messages to and from any of my test accoutns I get the following error:-
2016.07.01 11:17:14 org.jivesoftware.openfire.archive.ConversationManager - Invalid column name ‘messageID’.
java.sql.BatchUpdateException: Invalid column name ‘messageID’.
at net.sourceforge.jtds.jdbc.JtdsStatement.executeBatch(JtdsStatement.java:1069)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
at java.lang.reflect.Method.invoke(Unknown Source)
at org.logicalcobwebs.proxool.ProxyStatement.invoke(ProxyStatement.java:100)
at org.logicalcobwebs.proxool.ProxyStatement.intercept(ProxyStatement.java:57)
at $java.lang.AutoCloseable$$EnhancerByProxool$$aeff9554.executeBatch()
at org.jivesoftware.openfire.archive.ConversationManager$ArchivingTask.run(Convers ationManager.java:1010)
at org.jivesoftware.openfire.archive.ConversationManager$1.run(ConversationManager .java:190)
at java.util.concurrent.Executors$RunnableAdapter.call(Unknown Source)
at java.util.concurrent.FutureTask.run(Unknown Source)
at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
at java.lang.Thread.run(Unknown Source)
2016.07.01 11:18:14 org.jivesoftware.openfire.archive.ConversationManager - Invalid column name ‘messageID’.
java.sql.BatchUpdateException: Invalid column name ‘messageID’.
at net.sourceforge.jtds.jdbc.JtdsStatement.executeBatch(JtdsStatement.java:1069)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
at java.lang.reflect.Method.invoke(Unknown Source)
at org.logicalcobwebs.proxool.ProxyStatement.invoke(ProxyStatement.java:100)
at org.logicalcobwebs.proxool.ProxyStatement.intercept(ProxyStatement.java:57)
at $java.lang.AutoCloseable$$EnhancerByProxool$$aeff9554.executeBatch()
at org.jivesoftware.openfire.archive.ConversationManager$ArchivingTask.run(Convers ationManager.java:1010)
at org.jivesoftware.openfire.archive.ConversationManager$1.run(ConversationManager .java:190)
at java.util.concurrent.Executors$RunnableAdapter.call(Unknown Source)
at java.util.concurrent.FutureTask.run(Unknown Source)
at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
at java.lang.Thread.run(Unknown Source)
MessageID is a little too common a column for searching to be of much use now on google. my ofMucConversationLog and ofmessageArchive are both empty. I am however getting data in ofConversation. Has anyone come across this issue or can tell me where I am going wrong with getting the monitoring service working and populating the SQL tables?
Thanks
Si