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 OF-271 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
Bug
Open
Blocker
A similar exceptions gets thrown on the "jiverostergroups_pk" constraint.