powered by Jive Software

SQL Expression limit reached defaultrosteritemprovider

We have an application that uses xmpp to communicate with other devices. It has a very large roster. We have run into an issue where the large roster throws an MS SQL error:

2014.03.26 09:28:12 org.jivesoftware.openfire.roster.DefaultRosterItemProvider - Internal server error

java.sql.SQLException: Internal error: An expression services limit has been reached. Please look for potentially complex expressions in your query, and try to simplify them.

at net.sourceforge.jtds.jdbc.SQLDiagnostic.addDiagnostic(SQLDiagnostic.java:368)

at net.sourceforge.jtds.jdbc.TdsCore.tdsErrorToken(TdsCore.java:2816)

at net.sourceforge.jtds.jdbc.TdsCore.nextToken(TdsCore.java:2254)

at net.sourceforge.jtds.jdbc.TdsCore.getMoreResults(TdsCore.java:631)

at net.sourceforge.jtds.jdbc.JtdsStatement.executeSQLQuery(JtdsStatement.java:477)

at net.sourceforge.jtds.jdbc.JtdsPreparedStatement.executeQuery(JtdsPreparedStatem ent.java:777)

….

I ran an sql trace and i believe the query is this:

SELECT rosterID,groupName FROM ofRosterGroups WHERE rosterID IN (148353,148354,148355,148356,148365,148366,148367,148368,148370,148372,148373,1 48374,148375,148381,148383,148387,….lots of rosterID’s….) ORDER BY rosterID, rank

Openfire information:

Version: Openfire 3.9.1
Server Directory: /usr/share/openfire

Java Version: 1.6.0_27 Sun Microsystems Inc. – OpenJDK 64-Bit Server VM
Appserver: jetty/7.x.y-SNAPSHOT
OS / Hardware: Linux / amd64

Is there a bug in the MS SQL code? I found a similar issue with oracle and openfire that appears resolved: http://issues.igniterealtime.org/browse/OF-163

Thanks,

Dean

Resolution: unresolved

So Openfire still has this bug unfortunately.