PubSubPersistenceManager - Duplicate entry

Openfire Openfire Support
1

This error message starting popping up in my logs - Openfire 3.7.1 on linux - any suggestions as to whats going on? Thanks!

2012.02.01 16:38:42 org.jivesoftware.openfire.pubsub.PubSubPersistenceManager - Duplicate entry ‘username@jabber.example.edu-urn:xmpp:avatar:data-03529a59976dac3af8d’ for key 1

com.mysql.jdbc.exceptions.MySQLIntegrityConstraintViolationException: Duplicate entry ‘username@jabber.example.edu-urn:xmpp:avatar:data-03529a59976dac3af8d’ for key 1

at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:931)

at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2985)

at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1631)

at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1723)

at com.mysql.jdbc.Connection.execSQL(Connection.java:3283)

at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1332)

at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1604)

at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1519)

at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1504)

at sun.reflect.GeneratedMethodAccessor50.invoke(Unknown Source)

at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)

at java.lang.reflect.Method.invoke(Unknown Source)

at org.logicalcobwebs.proxool.ProxyStatement.invoke(ProxyStatement.java:100)

at org.logicalcobwebs.proxool.ProxyStatement.intercept(ProxyStatement.java:57)

at $java.sql.Statement$$EnhancerByProxool$$14e4cbf6.executeUpdate()

at org.jivesoftware.openfire.pubsub.PubSubPersistenceManager.createPublishedItem(P ubSubPersistenceManager.java:1015)

at org.jivesoftware.openfire.pubsub.PublishedItemTask.run(PublishedItemTask.java:8 6)

at java.util.TimerThread.mainLoop(Unknown Source)

at java.util.TimerThread.run(Unknown Source)

2

It looks like you are using custom item id’s that are too long. The db has a 100 char limit on that field in the database (I ran into the same problem before).

You have two possible fixes,

  1. Change you id’s so that they are shorter
  2. Alter the db column to increase it’s length.

The second is pretty trivial and doesn’t force you to rewrite client code.

Please be aware, that this can have unwanted side effects, as you can get matches to persisted items even though the id’s are different (originally) but will match when the truncated version is pulled from the database. For example:

  • publish with id : id-too-long-12
  • works fine
  • restart server
  • node data is loaded from node but id has been truncated to id-too-long-1
  • publish new item with id: id-too-long-1
  • this will overwrite the previous item (treated as an update since the id’s match) and you will be none the wiser that it occurred.

Obvously id’s with long common prefixes can cause this potential issue.

3

any idea what table in the DB?

4

ofPubsubItem

5

Please mark answers as correct or helpful if they help you solve your problem to help others searching these forums.

Thank you.