fix "duplicate key violates unique constraint "ofpresence_pk""
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.
Agreed - this issue is a duplicate.
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.
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!)
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
SET QUOTED_IDENTIFIER ON
– 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
INSTEAD OF INSERT
– 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
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.