powered by Jive Software

Database Error

I’'ve installed Wildfire 2.5.0 and sometimes these messages appear in the error logs.

2006.02.21 09:42:44 org.jivesoftware.wildfire.privacy.PrivacyListProvider.loadDefaultPrivacyList(Pri vacyListProvider.java:168) Error loading default privacy list of username: cred044i

java.sql.SQLException: ORA-01000: maximum open cursors exceeded

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:743)

at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:216)

at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement .java:799)

at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:10 39)

at oracle.jdbc.driver.T4CPreparedStatement.executeMaybeDescribe(T4CPreparedStateme nt.java:839)

at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:11 32)

at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatem ent.java:3285)

at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement .java:3329)

at org.jivesoftware.wildfire.privacy.PrivacyListProvider.loadDefaultPrivacyList(Pr ivacyListProvider.java:159)

at org.jivesoftware.wildfire.privacy.PrivacyListManager.getDefaultPrivacyList(Priv acyListManager.java:105)

at org.jivesoftware.wildfire.roster.Roster.broadcastPresence(Roster.java:464)

at org.jivesoftware.wildfire.handler.PresenceUpdateHandler.broadcastUpdate(Presenc eUpdateHandler.java:248)

at org.jivesoftware.wildfire.handler.PresenceUpdateHandler.process(PresenceUpdateH andler.java:94)

at org.jivesoftware.wildfire.handler.PresenceUpdateHandler.process(PresenceUpdateH andler.java:151)

at org.jivesoftware.wildfire.PresenceRouter.handle(PresenceRouter.java:92)

at org.jivesoftware.wildfire.PresenceRouter.route(PresenceRouter.java:61)

at org.jivesoftware.wildfire.PacketRouter.route(PacketRouter.java:73)

at org.jivesoftware.wildfire.net.SocketReader.processPresence(SocketReader.java:44 3)

at org.jivesoftware.wildfire.net.ClientSocketReader.processPresence(ClientSocketRe ader.java:56)

at org.jivesoftware.wildfire.net.SocketReader.readStream(SocketReader.java:240)

at org.jivesoftware.wildfire.net.SocketReader.run(SocketReader.java:119)

at java.lang.Thread.run(Unknown Source)

Any idea?

Eduardo

Folks,

When It happens, the users offline that try to connect to server, don’'t manage to connect to it.

Eduardo

check this out regarding the oracle db problem:

http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:1041031921901

some say its the application not closing the cursors or a bug in the oracle driver. a solution is to increase the # of cursors in your oracle db. “open_cursors=500”

i’'m assuming you have private data storing enabled on the server? and does all your client get this error or just that one?

Michael,

When the server achieves a certain quantity of user, it starts to appear this DB problem. At the moment I have 305 users recorded in my DB. At least 250 users are supposed to use the IM at the same time. As you said before, I have private data storing enabled on the server.

Important informations:

  • Wildfire 2.5.0 in SLES 9

  • Oracle9i Enterprise Edition Release 9.2.0.5.0

  • Exodus 0.9.1.0 Client

I increased the number of open cursors in my oracle db to 2000, but It’'s still facing this problem.

Regards,

Eduardo

Oi Eduardo,

I just tried to find cursors that we might not be closing without much luck. Could you try executing this command so we can try to narrow down the culprit statement? Try to execute that command while users are inactive.

select sql_Text from v$open_Cursor;[/code]

BTW, as it has already been mentioned be sure to be using the latest Oracle patches and also make sure that this is not a JDBC driver problem. You may want to check if there is a newer version of the JDBC driver or just try for a moment with a different driver.

Obrigado,

– Gato

Oi Gato,

I re-installed Wildfire 2.5.0 with a different JDBC driver, but it’'s still showing the DB problem:

2006.02.23 12:34:41 org.jivesoftware.wildfire.roster.RosterItemProvider.getItems(RosterItemProvider. java:321) Internal server error

java.sql.SQLException: ORA-01000: maximum open cursors exceeded

at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)

at oracle.jdbc.ttc7.TTIoer.processError(TTIoer.java:289)

at oracle.jdbc.ttc7.Oopen.receive(Oopen.java:120)

at oracle.jdbc.ttc7.TTC7Protocol.open(TTC7Protocol.java:614)

at oracle.jdbc.driver.OracleStatement.open(OracleStatement.java:578)

at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:28 06)

at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatemen t.java:609)

at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement .java:537)

at org.jivesoftware.wildfire.roster.RosterItemProvider.getItems(RosterItemProvider .java:291)

at org.jivesoftware.wildfire.roster.Roster.(Roster.java:98)

at org.jivesoftware.wildfire.roster.RosterManager.getRoster(RosterManager.java:76)

at org.jivesoftware.wildfire.handler.PresenceUpdateHandler.broadcastUpdate(Presenc eUpdateHandler.java:247)

at org.jivesoftware.wildfire.handler.PresenceUpdateHandler.process(PresenceUpdateH andler.java:94)

at org.jivesoftware.wildfire.handler.PresenceUpdateHandler.process(PresenceUpdateH andler.java:151)

at org.jivesoftware.wildfire.PresenceRouter.handle(PresenceRouter.java:92)

at org.jivesoftware.wildfire.PresenceRouter.route(PresenceRouter.java:61)

at org.jivesoftware.wildfire.PacketRouter.route(PacketRouter.java:73)

at org.jivesoftware.wildfire.net.SocketReader.processPresence(SocketReader.java:44 3)

at org.jivesoftware.wildfire.net.ClientSocketReader.processPresence(ClientSocketRe ader.java:56)

at org.jivesoftware.wildfire.net.SocketReader.readStream(SocketReader.java:240)

at org.jivesoftware.wildfire.net.SocketReader.run(SocketReader.java:119)

at java.lang.Thread.run(Unknown Source)

Obrigado,

Eduardo

here is the error log from our system. Same problem.

java.sql.SQLException: ORA-01000: maximum open cursors exceeded

at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)

at oracle.jdbc.ttc7.TTIoer.processError(TTIoer.java:289)

at oracle.jdbc.ttc7.Oopen.receive(Oopen.java:120)

at oracle.jdbc.ttc7.TTC7Protocol.open(TTC7Protocol.java:614)

at oracle.jdbc.driver.OracleStatement.open(OracleStatement.java:578)

at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:28 06)

at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatemen t.java:609)

at oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStatement.java :685)

at com.version2software.wildfire.plugins.database.StatisticsDAO.deleteOld(Statisti csDAO.java:705)

at com.version2software.wildfire.plugins.database.StatisticsDAO.insertWeeklyStatis tic(StatisticsDAO.java:320)

at com.version2software.wildfire.plugins.database.StatisticsDAO.insertStatistic(St atisticsDAO.java:257)

at com.version2software.wildfire.plugins.collector.ServerSessionCountCollector$Col lectorThread.run(ServerSessionCountCollector.java:58)

2006.02.27 16:21:30 [com.version2software.wildfire.plugins.database.StatisticsDAO.addOrUpdate(Stati sticsDAO.java:640)

Jase700,

I believe that it could be an aplication problem. I’'ve installed W2.5.0 with another

JDBC driver version and the problem is still there.

Eduardo

Hi,

it is interesting that this problem occurs with only Oracle databases or drivers.

LG

Eduardo,

Can you execute the statement I posted in my previous post to identify the opened cursors? That will help us identify the source of the problem faster (if it’'s a Wildfire issue).

Thanks,

– Gato

Oi Gato,

Should I do it with Wildfire stopped or with Wildfire running?

Obrigado,

Eduardo

Oi Eduardo,

You should execute that statement while Wildfire is running. You can execute it before you get the max number of cursors error and also once you have received that error too.

Regards,

– Gato

In our Oracle environment, we have to log is as SYSTEM to run that query. Logging into the Jive schema may not have the permission necessary to run it.

Larry

Hi Gato,

Look below to see what it appears when I execute that SQL statement:

SQL_TEXT


SELECT groupName FROM jiveRosterGroups WHERE rosterID=:1 ORD

SELECT groupName FROM jiveRosterGroups WHERE rosterID=:1 ORD

SELECT groupName FROM jiveRosterGroups WHERE rosterID=:1 ORD

SELECT groupName FROM jiveRosterGroups WHERE rosterID=:1 ORD

SELECT groupName FROM jiveRosterGroups WHERE rosterID=:1 ORD

SELECT groupName FROM jiveRosterGroups WHERE rosterID=:1 ORD

SELECT groupName FROM jiveRosterGroups WHERE rosterID=:1 ORD

SELECT groupName FROM jiveRosterGroups WHERE rosterID=:1 ORD

SELECT groupName FROM jiveRosterGroups WHERE rosterID=:1 ORD

SELECT groupName FROM jiveRosterGroups WHERE rosterID=:1 ORD

SELECT groupName FROM jiveRosterGroups WHERE rosterID=:1 ORD

SQL_TEXT


SELECT groupName FROM jiveRosterGroups WHERE rosterID=:1 ORD

SELECT groupName FROM jiveRosterGroups WHERE rosterID=:1 ORD

SELECT groupName FROM jiveRosterGroups WHERE rosterID=:1 ORD

SELECT groupName FROM jiveRosterGroups WHERE rosterID=:1 ORD

SELECT groupName FROM jiveRosterGroups WHERE rosterID=:1 ORD

SELECT groupName FROM jiveRosterGroups WHERE rosterID=:1 ORD

SELECT groupName FROM jiveRosterGroups WHERE rosterID=:1 ORD

SELECT groupName FROM jiveRosterGroups WHERE rosterID=:1 ORD

SELECT groupName FROM jiveRosterGroups WHERE rosterID=:1 ORD

SELECT groupName FROM jiveRosterGroups WHERE rosterID=:1 ORD

SELECT groupName FROM jiveRosterGroups WHERE rosterID=:1 ORD

SQL_TEXT


SELECT groupName FROM jiveRosterGroups WHERE rosterID=:1 ORD

SELECT groupName FROM jiveRosterGroups WHERE rosterID=:1 ORD

SELECT groupName FROM jiveRosterGroups WHERE rosterID=:1 ORD

SELECT groupName FROM jiveRosterGroups WHERE rosterID=:1 ORD

SELECT groupName FROM jiveRosterGroups WHERE rosterID=:1 ORD

SELECT groupName FROM jiveRosterGroups WHERE rosterID=:1 ORD

SELECT groupName FROM jiveRosterGroups WHERE rosterID=:1 ORD

SELECT groupName FROM jiveRosterGroups WHERE rosterID=:1 ORD

SELECT groupName FROM jiveRosterGroups WHERE rosterID=:1 ORD

SELECT groupName FROM jiveRosterGroups WHERE rosterID=:1 ORD

SELECT groupName FROM jiveRosterGroups WHERE rosterID=:1 ORD

SQL_TEXT


SELECT groupName FROM jiveRosterGroups WHERE rosterID=:1 ORD

SELECT groupName FROM jiveRosterGroups WHERE rosterID=:1 ORD

SELECT groupName FROM jiveRosterGroups WHERE rosterID=:1 ORD

SELECT groupName FROM jiveRosterGroups WHERE rosterID=:1 ORD

SELECT groupName FROM jiveRosterGroups WHERE rosterID=:1 ORD

SELECT groupName FROM jiveRosterGroups WHERE rosterID=:1 ORD

SELECT groupName FROM jiveRosterGroups WHERE rosterID=:1 ORD

SELECT groupName FROM jiveRosterGroups WHERE rosterID=:1 ORD

SELECT groupName FROM jiveRosterGroups WHERE rosterID=:1 ORD

SELECT groupName FROM jiveRosterGroups WHERE rosterID=:1 ORD

SELECT groupName FROM jiveRosterGroups WHERE rosterID=:1 ORD

SQL_TEXT


SELECT groupName FROM jiveRosterGroups WHERE rosterID=:1 ORD

SELECT groupName FROM jiveRosterGroups WHERE rosterID=:1 ORD

SELECT groupName FROM jiveRosterGroups WHERE rosterID=:1 ORD

SELECT groupName FROM jiveRosterGroups WHERE rosterID=:1 ORD

SELECT groupName FROM jiveRosterGroups WHERE rosterID=:1 ORD

SELECT groupName FROM jiveRosterGroups WHERE rosterID=:1 ORD

SELECT groupName FROM jiveRosterGroups WHERE rosterID=:1 ORD

SELECT groupName FROM jiveRosterGroups WHERE rosterID=:1 ORD

SELECT groupName FROM jiveRosterGroups WHERE rosterID=:1 ORD

SELECT groupName FROM jiveRosterGroups WHERE rosterID=:1 ORD

SELECT groupName FROM jiveRosterGroups WHERE rosterID=:1 ORD

Regards,

Eduardo

Message was edited by: eduardo

Can I ask what specific circumstances cause this error? We are running WF 2.4.4 in a Solaris 8 / Oracle 9.2.0.4.0 environment with ojdbc14.jar as the JDBC driver. I want to upgrade to WF 2.5.0 (or 2.5.1 if I leave it a few days) but I am concerned about doing this with a user base of around160. Does this bug only manifest when lots of private data is stored (by which I assume this means the user’‘s profile)? Or does it happen with 250 users irrespective of how much data is stored? Thanks for your help. I really want to get rid of 2.4.4’‘s roster bugs but don’'t want to introduce total failures in their place!!

Oi Eduardo,

Thanks for the info. I found the problem in the code and I’'m fixing it now. The bug fix will be available in the next nightly build and also in Wildfire 2.5.1.

Thanks,

– Gato

Oi Gato,

Thanks for the support.

Eduardo

Thanks, Gaston, for the update. This answers my question. I’'ll ready my CDR burner for tomorrow if 2.5.1 is still scheduled for then.