Postgresql problem

Openfire Openfire Support
1

Hi all,

we have a fairly complex database setup for our Openfire 3.6.4 installation, but the problem we are seeing seems to be unrelated to the complexities of our user/group stuff (which we get from an external database). Basically, what happens is this: our external users and groups work just fine, but users are unable to add any buddies to their roster. Whenever they try, the error.log contains the following:

2010.06.11 12:23:56 [org.jivesoftware.openfire.handler.PresenceSubscribeHandler.process(PresenceSub scribeHandler.java:194)] Internal server error

org.jivesoftware.openfire.user.UserAlreadyExistsException: jan@jabber.example.com

at org.jivesoftware.openfire.roster.RosterItemProvider.createItem(RosterItemProvid er.java:109)

at org.jivesoftware.openfire.roster.Roster.provideRosterItem(Roster.java:330)

at org.jivesoftware.openfire.roster.Roster.createRosterItem(Roster.java:280)

at org.jivesoftware.openfire.roster.Roster.createRosterItem(Roster.java:264)

at org.jivesoftware.openfire.handler.PresenceSubscribeHandler.manageSub(PresenceSu bscribeHandler.java:249)

at org.jivesoftware.openfire.handler.PresenceSubscribeHandler.process(PresenceSubs cribeHandler.java:115)

[…]

The postgresql logging on our database server shows this:

<2010-06-11 12:17:10 GMT-openfire%openfireLive@192.168.1.105(56825)-1584>ERROR: duplicate key value violates unique constraint “ofroster_pk” <2010-06-11 12:17:10 GMT-openfire%openfireLive@192.168.1.105(56825)-1584>STATEMENT: INSERT INTO ofRoster (username, rosterID, jid, sub, ask, recv, nick) VALUES ($1, $2, $3, $4, $5, $6, $7)

It looks like two problems here: first of all the database error that shouldn’t really be happening and second of all that the error is handled as a “user already exists” exception, which is clearly not the case.

Any ideas what’s happening here? The inability to add users to the roster is obviously a major problem… any help would be much appreciated.

Holger

2

By the way, the postgresql error makes perfect sense if this is indeed the query string it’s receiving:

INSERT INTO ofRoster (username, rosterID, jid, sub, ask, recv, nick) VALUES ($1, $2, $3, $4, $5, $6, $7)

The values really should be actual values and not variable names, shouldn’t they? Unless something is wrong with postgresql’s logging, I would say there is a problem in Openfire that prevents the query string from being filled with the correct values…

3

Hi,

the SQL looks fine, it’s called bind variables.

“long rosterID = SequenceManager.nextID(JiveConstants.ROSTER);” with JiveConstants.ROSTER=18. So you need to look at the “ofID” table. There you should find IDTYPE 18 = ID n.

So if n+1 does already exist in the ofRoster table the insert does fail. You need to change the ID value for IDTYPE 18 in the ofID table to a number which is higher than the max existing rosterIDs in ofRoster. Setting it to 10000 could help unless you have a lot of rosters.

LG

4

Thank you, that’s exactly what it was - our migration script from a previous jabber server had added items to the roster, but we didn’t know we would have to increment the counter… I had kind of figured it out myself, but you helped me find the right setting