Openfire 4.3.2 and perpetual error in logs

Openfire 4.3.2 is installed with database in MS SQL Express server. Users are internal (no AD-integration).
And there are error messages in error log every 5 minutes.

2019.02.28 08:05:54 org.jivesoftware.openfire.pubsub.PubSubPersistenceManager - Incorrect syntax near the keyword 'LEFT'.
java.sql.BatchUpdateException: Incorrect syntax near the keyword 'LEFT'.
	at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeBatch(SQLServerPreparedStatement.java:2065) ~[mssql-jdbc-7.0.0.jre8.jar:?]
	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_202]
	at java.util.concurrent.FutureTask.run(Unknown Source) [?:1.8.0_202]
	at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source) [?:1.8.0_202]
	at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source) [?:1.8.0_202]
	at java.lang.Thread.run(Unknown Source) [?:1.8.0_202]
2019.02.28 08:10:54 org.jivesoftware.openfire.pubsub.PubSubPersistenceManager - Incorrect syntax near the keyword 'LEFT'.
java.sql.BatchUpdateException: Incorrect syntax near the keyword 'LEFT'.
	at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeBatch(SQLServerPreparedStatement.java:2065) ~[mssql-jdbc-7.0.0.jre8.jar:?]
	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_202]
	at java.util.concurrent.FutureTask.run(Unknown Source) [?:1.8.0_202]
	at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source) [?:1.8.0_202]
	at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source) [?:1.8.0_202]
	at java.lang.Thread.run(Unknown Source) [?:1.8.0_202]

I’ve read a lot of articles on this topic in Internet and they were all about MySQL integration and SQL statements errors. But i use MS SQL. And I never see any mention of MS SQL.
Tell me please how to get rid of these errors.
Btw i don’t use PubSub functionality so now I just ignore these error messages.

Thanks for reporting this; so we can help you a bit more, can you provide a bit more information;

Is this a new install or an upgrade?

If it was an upgrade, from which version of Openfire was it upgraded?

What version of MS SQL Express are you running?

If you access the Database properties (Server -> Server Manager -> Database) what is shown for Database and Version ?

Thanks

Greg

Here is info you requesting.
It is new installation of Openfire 4.3.2.
Connection string from config file:

  <database> 
    <defaultProvider> 
      <driver>com.microsoft.sqlserver.jdbc.SQLServerDriver</driver>  
      <serverURL>jdbc:sqlserver://server-pr:65221;databaseName=Openfire;applicationName=Openfire;instance=PSKSOFT</serverURL>  
      <username encrypted="true">username placeholder</username>  
      <password encrypted="true">password placeholder</password>  
      <testSQL>select 1</testSQL>  
      <testBeforeUse>false</testBeforeUse>  
      <testAfterUse>false</testAfterUse>  
      <testTimeout>500</testTimeout>  
      <timeBetweenEvictionRuns>30000</timeBetweenEvictionRuns>  
      <minIdleTime>900000</minIdleTime>  
      <maxWaitTime>500</maxWaitTime>  
      <minConnections>5</minConnections>  
      <maxConnections>200</maxConnections>  
      <connectionTimeout>1.0</connectionTimeout> 
    </defaultProvider> 
  </database>  

Database is on different server. Server version: MS SQL Server 2008 (SP2) - 10.50.4000.0 (Express Edition).

I see the same error in Openfire all.log all the time.
I suspect it has something to do with incorrect ofProperty jdbcUserProvider.searchSQL SQL…

Actual call stack is below. Does it provide any clue?

2019.05.08 20:46:12 ERROR [TaskEngine-pool-16]: 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_202]
at java.util.concurrent.FutureTask.run(Unknown Source) [?:1.8.0_202]
at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source) [?:1.8.0_202]
at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source) [?:1.8.0_202]
at java.lang.Thread.run(Unknown Source) [?:1.8.0_202]

I know what’s causing

org.jivesoftware.openfire.pubsub.PubSubPersistenceManager - Incorrect syntax near the keyword 'LEFT'.
java.sql.BatchUpdateException: Incorrect syntax near the keyword 'LEFT'

error. It is incorrect or at least non-standard SQL generated by Openfire. It would be nice if Openfire developers fix it.

As SQL Server Profiler shows, Openfire tries to execute the following SQL:

DELETE FROM ofPubsubItem
   LEFT JOIN (SELECT id FROM ofPubsubItem WHERE serviceID= @P0  AND nodeID= @P1  ORDER BY creationDate DESC LIMIT  @P2 ) AS noDelete
            ON ofPubsubItem.id = noDelete.id
WHERE noDelete.id IS NULL AND ofPubsubItem.serviceID =  @P3  AND nodeID =  @P4 ....'

It is incorrect (or at very least non-standard DELETE from join statement. According to https://stackoverflow.com/questions/4097260/sql-delete-based-on-condition-in-join, https://www.sqlservercentral.com/forums/topic/delete-statements-when-using-a-join-best-practice and other resources, there is no ANSI SQL standard for deleting from a join. Using sub-queries instead of joins would always work, but if you want to use join, you normally need to include FROM keyword twice like:

DELETE FROM ofPubsubItem
  FROM ofPubsubItem LEFT JOIN (SELECT id FROM ofPubsubItem WHERE serviceID= @P0  AND nodeID= @P1  ORDER BY creationDate DESC LIMIT  @P2 ) AS noDelete
            ON ofPubsubItem.id = noDelete.id
WHERE noDelete.id IS NULL AND ofPubsubItem.serviceID =  @P3  AND nodeID =  @P4 ....'

According to https://github.com/candy-chat/candy/wiki/Installing-a-XMPP-server, Openfire PEP module may leak a memory. Since PEP (Personal Eventing Protocol https://xmpp.org/extensions/xep-0163.html) is kind of subset of PubSub, I wonder if that memory leakage could be a result of constant SQL exceptions?

Thanks for reporting. Filed as https://issues.igniterealtime.org/browse/OF-1779

This problem should be fixed in Openfire 4.4.5 and 4.5.0.

1 Like