Duplicate entry 'user xxx' for key 1 -- Exposed during load testing.

Description

The following Exception is showing up multiple times (with different usernames) on the console for lachesis. This seems to occur around the same time things start going downhill in the testing.

java.sql.SQLException: Duplicate entry 'user33703' for key 1
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2975)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1600)
at com.mysql.jdbc.ServerPreparedStatement.serverExecute(ServerPreparedStatement.java:1125)
at com.mysql.jdbc.ServerPreparedStatement.executeInternal(ServerPreparedStatement.java:677)
at com.mysql.jdbc.PreparedStatement.execute(PreparedStatement.java:794)
at org.jivesoftware.wildfire.spi.PresenceManagerImpl.userUnavailable(PresenceManagerImpl.java:261)
at org.jivesoftware.wildfire.handler.PresenceUpdateHandler.process(PresenceUpdateHandler.java:122)
at org.jivesoftware.wildfire.handler.PresenceUpdateHandler.process(PresenceUpdateHandler.java:153)
at org.jivesoftware.wildfire.SessionManager$ClientSessionListener.onConnectionClose(SessionManager.java:1497)
at org.jivesoftware.wildfire.net.VirtualConnection.notifyCloseListeners(VirtualConnection.java:179)
at org.jivesoftware.wildfire.net.VirtualConnection.close(VirtualConnection.java:155)
at org.jivesoftware.wildfire.multiplex.ConnectionMultiplexerManager.closeClientSession(ConnectionMultiplexerManager.java:162)
at org.jivesoftware.wildfire.multiplex.MultiplexerPacketHandler.handle(MultiplexerPacketHandler.java:85)
at org.jivesoftware.wildfire.net.MultiplexerStanzaHandler.processIQ(MultiplexerStanzaHandler.java:49)
at org.jivesoftware.wildfire.net.StanzaHandler.process(StanzaHandler.java:256)
at org.jivesoftware.wildfire.net.StanzaHandler.process(StanzaHandler.java:155)
at org.jivesoftware.wildfire.net.ConnectionHandler.messageReceived(ConnectionHandler.java:83)
at org.apache.mina.common.support.AbstractIoFilterChain$TailFilter.messageReceived(AbstractIoFilterChain.java:703)
at org.apache.mina.common.support.AbstractIoFilterChain.callNextMessageReceived(AbstractIoFilterChain.java:362)
at org.apache.mina.common.support.AbstractIoFilterChain.access$1100(AbstractIoFilterChain.java:54)
at org.apache.mina.common.support.AbstractIoFilterChain$EntryImpl$1.messageReceived(AbstractIoFilterChain.java:800)
at org.apache.mina.filter.codec.support.SimpleProtocolDecoderOutput.flush(SimpleProtocolDecoderOutput.java:62)
at org.apache.mina.filter.codec.ProtocolCodecFilter.messageReceived(ProtocolCodecFilter.java:186)
at org.apache.mina.common.support.AbstractIoFilterChain.callNextMessageReceived(AbstractIoFilterChain.java:362)
at org.apache.mina.common.support.AbstractIoFilterChain.access$1100(AbstractIoFilterChain.java:54)
at org.apache.mina.common.support.AbstractIoFilterChain$EntryImpl$1.messageReceived(AbstractIoFilterChain.java:800)
at org.apache.mina.filter.executor.ExecutorFilter.processEvent(ExecutorFilter.java:231)
at org.apache.mina.filter.executor.ExecutorFilter$ProcessEventsRunnable.run(ExecutorFilter.java:286)
at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:885)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:907)
at java.lang.Thread.run(Thread.java:619)

Environment

Local load testing enviro:
WF/CM-1/MySQL on lachesis
on load-35
4 Tsung Clients (151,152,155,157)

Activity

Show:
Guus der Kinderen
January 25, 2012, 8:30 PM

Applied patch to trunk (rev 12955).

Guus der Kinderen
January 25, 2012, 8:23 PM

Your patch looks OK. There might be some concurrency issues, but that's probably true for the rest of that class too.

LG
January 14, 2012, 6:31 PM

Patch to delete offline presence before inserting it. Tested as described with multiple "<presence type="unavailable">...</presence>" packets using Spark debug window.

LG
January 14, 2012, 4:30 PM

Openfire 371 exception is:
org.jivesoftware.openfire.spi.PresenceManagerImpl - Error storing offline presence of user: <username>
org.postgresql.util.PSQLException: ERROR: duplicate key violates unique constraint "ofpresence_pk"
...
at org.jivesoftware.openfire.spi.PresenceManagerImpl.userUnavailable(PresenceManagerImpl.java:290)
...

LG
January 14, 2012, 3:49 PM

For Oracle it is "MERGE" and not "REPLACE INTO". A previous executed DELETE statement would be compatible for all databases. One should be able to catch the exception and log only a warn message.

Fixed

Assignee

Guus der Kinderen

Reporter

Megan Chinburg