Database Error

[Eduardo]

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

[Eduardo]

Folks,

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

Eduardo

[michaelb]

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?

[Eduardo]

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

[Gaston_Dombiak]

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

[Eduardo]

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

[Jason5]

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)

[Eduardo]

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

[LG1]

Hi,

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

LG

[Gaston_Dombiak]

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

[Eduardo]

Oi Gato,

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

Obrigado,

Eduardo

[Gaston_Dombiak]

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

[Larry_Sherrill]

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

[Eduardo]

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

[John9]

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!!

[Gaston_Dombiak]

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

[Eduardo]

Oi Gato,

Thanks for the support.

Eduardo

[John9]

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.