We're updating the issue view to help you get more done. 

fix "duplicate key violates unique constraint "ofpresence_pk""

Description

It seems that some clients do not send presence packets when logging on in the form Openfire expects them (JWChat and privacy lists may cause this). Openfire checks for a presence packet to the server and deletes than the entry in JIVEOFFLINE - if the user goes offline Openfire stores the new Offline value in JIVEOFFLINE, even if the previous entry was not deleted.

Openfire should use another way to detect whether a user is online to make sure that deleting the entry always works.

Environment

None

Acceptance Test - Entry

None

Activity

Show:
Matt Curtis
November 19, 2010, 5:53 PM

I get these same errors when using asmack (Smack ported to Android). This bug is killing me as I have a plugin that monitors when users sign out (via the SessionEventListener) and performs actions on SessionDestroyed. That event never fires due to this bug.

Brian Biales
January 28, 2011, 4:50 PM

To work around this problem, simply change the INSERT into ofPresence into an INSERT or UPDATE. Since we are usually doing this insert with a single row, the following TRIGGER will actually do this for you, without changing the code at all.

Simply add this trigger to your SQLServer database (I have no idea if it will work for other databases, I am using SQLServer myself...)

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
– =============================================
– Author: Brian Biales
– Create date: 1/28/2011
– Description: work around for issue for OpenFire
– Because inserts are failing due to other code that doesn't properly delete
– the row when the user logs on, lets just change the INSERT to really
– be in INSERT or REPLACE. This works with SQL Server, not sure if it works
– with other databases.
– This is an INSTEAD OF INSERT trigger, so instead of simply inserting, which
– may violate the primary key unique constraint, we first delete any rows with
– the same primary key as one of our inserts, and then we do the insert.
– Because we insert into the same table as the trigger is defined, it is NOT
– recursively called.
– I have tested this with SQL Server 2008, but it should work
– in earlier versions as well.
– =============================================
CREATE TRIGGER dbo.InsertOrReplace_ofPresence
ON dbo.ofPresence
INSTEAD OF INSERT
AS
BEGIN
– SET NOCOUNT ON added to prevent extra result sets from
– interfering with SELECT statements.
SET NOCOUNT ON;
– to avoid duplicates, and because we cannot change the code to update if
– it already exists...
– we simply make sure we've deleted any rows with the same key as the ones being inserted
DELETE FROM dbo.ofPresence WHERE username IN (SELECT username from Inserted)
INSERT INTO dbo.ofPresence SELECT * from inserted
END
GO

RawToast
November 25, 2011, 10:49 AM

Seeing plenty of these on 3.7.1. Is this really a blocker? The bug has been around for a number of releases now (so it's hardly blocking!)

LG
January 14, 2012, 7:49 PM

Is it a duplicate of OF-270? This issue describes the more generic error message, OF-270/"Duplicate entry '...' for key 1" seems to be a MySQL error message.

Guus der Kinderen
February 10, 2012, 10:14 PM

Agreed - this issue is a duplicate.

Assignee

Daryl Herzmann

Reporter

LG

Labels

None

Expected Effort

None

Ignite Forum URL

None

Components

Fix versions

Affects versions

Priority

Minor
Configure