If a roster is > 1000 items large we are seeing the following error when starting the server. Looks like the oracle statements do not take these limitations into account.
2009.05.14 17:11:44 [org.jivesoftware.openfire.roster.RosterItemProvider.getItems(RosterItemProvide r.java:314)] Internal server error
java.sql.SQLException: ORA-01795: maximum number of expressions in a list is 1000
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:331)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:288)
at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:745)
at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:219)
at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement .java:813)
at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:10 49)
at oracle.jdbc.driver.T4CPreparedStatement.executeMaybeDescribe(T4CPreparedStateme nt.java:854)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:11 54)
at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatem ent.java:3370)
at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement .java:3415)
at sun.reflect.GeneratedMethodAccessor7.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.ja va:25)
at java.lang.reflect.Method.invoke(Method.java:597)
at org.logicalcobwebs.proxool.ProxyStatement.invoke(ProxyStatement.java:100)
at org.logicalcobwebs.proxool.ProxyStatement.intercept(ProxyStatement.java:57)
at oracle.jdbc.internal.OraclePreparedStatement$$EnhancerByProxool$$c28290e6.execu teQuery()
at org.jivesoftware.openfire.roster.RosterItemProvider.getItems(RosterItemProvider .java:306)
at org.jivesoftware.openfire.roster.Roster.(Roster.java:110)
at org.jivesoftware.openfire.roster.RosterManager.getRoster(RosterManager.java:86)
at org.jivesoftware.openfire.user.User.getRoster(User.java:368)
It means that SQL contains IN (?,…?) statement and there are ‘?’ chars more then 1000. This is Oracle database restriction. It can be fixed only with new Java code. In our project the same error was fixed with java.sql.Array interface usage.
I wonder what’s going on in the code. A StringBuilder with 100 chars was likely never designed to query more than 20 groups. So I’d say that this is a bug or poor design and one needs to rewrite this code.
LG
// Load the groups for the loaded contact
if (!itemList.isEmpty()) {
StringBuilder sb = new StringBuilder(100);
sb.append("SELECT rosterID,groupName FROM ofRosterGroups").append(" WHERE rosterID IN (");
for (RosterItem item : itemList) {
sb.append(item.getID()).append(",");
}
sb.setLength(sb.length()-1);
sb.append(") ORDER BY rosterID, rank");
pstmt = con.prepareStatement(sb.toString());
rs = pstmt.executeQuery();
while (rs.next()) {
itemsByID.get(rs.getLong(1)).getGroups().add(rs.getString(2));
}
rs.close();
}
Hey LG, That 100 is just the initial size of the StringBuilder, it will grow as needed.
Chur, that’s interesting to know that it’s an Oracle limitation. Is there no way to tweak Oracle accept a larger number of “contains IN” clauses? Is Oracle the only database that has this issue? If so, then there’d have to be some Oracle specific code added or if other db’s have a similar limitation maybe the code should be reworked to split that select statement in to smaller chunks.
This is Oracle specific error. I suggest to split code to have few sequential requests because usage of java.sql.Array is very close to JDBC Driver implementation and is not very flexible.