Problem with Oracle and Large Rosters

Openfire Openfire Support
1

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)

Should this be filed as a bug?

2

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.

3

Were you able to do this in your application or did you need to modify the OpenFire source code (RosterItemProvider)?

4

I meant our application (not OpenFire source)

5

Hi,

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();
}
6

I do not check the code yet, but I’ve saved few links about this error. This one was very helpful for me: http://www.orafaq.com/forum/t/41437/0/

7

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.

Ryan

8

Hi!

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.

To get more information about this Oracle error see this thread: http://www.orafaq.com/forum/t/41437/0/

Best regards

Sergey

9

Hi!

I reviewed the code and suggest to change the code to use sub-query here:

SELECT rosterID,groupName FROM ofRosterGroups WHERE rosterID IN (

SELECT rosterID FROM ofRoster WHERE username=?

) ORDER BY rosterID, rank

This is the simplest way to fix this bug, IMHO

Regards

Sergey

10

I made a similar change in our code, but I used a simple join instead of a subquery:

SELECT g.rosterID,g.groupName FROM ofRosterGroups g, ofRoster r WHERE g.rosterID = r.rosterID and r.username = ?

And the section I modified below now looks like this:

if (!itemList.isEmpty()) {
//StringBuilder sb = new StringBuilder(100);
//sb.append(LOAD_ROSTER_ITEM_GROUPS).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(LOAD_ROSTER_ITEM_GROUPS);
pstmt.setString(1, username);
rs = pstmt.executeQuery();
while (rs.next()) {
itemsByID.get(rs.getLong(1)).getGroups().add(rs.getString(2));
}
rs.close();
}

11

JM-1538 created, so it’s just a matter of patience to get an official fix for this.

12

The issue’s description is not correct enough: Oracle allows 1000 expressions in a prepared statement.

Actually Oracle allows not more then 1000 expressions inside SQL IN clause but not in a prepared statement.