JMS error with SQLServer

[balaji_srini]

Hi, I am getting this error using a SQLServer database.

JBoss version - 4.0.1.

Could not store message: 37 msg=36 hard NOT_STORED PERSISTENT queue=QUEUE.Event Queue priority=4 lateClone=false hashCode=21058500; - nested throwable: (java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC][SQLServer]Cannot insert duplicate key row in object 'JMS_MESSAGES' with unique index 'JMS_MESSAGES_IDX'.)

Any ideas?

Thanks.

[balaji_srini]

This is what I see in my mssql-jdbc2-service.xml:

 BLOB_TYPE=BINARYSTREAM_BLOB
 INSERT_TX = INSERT INTO JMS_TRANSACTIONS (TXID) values(?)
 INSERT_MESSAGE = INSERT INTO JMS_MESSAGES (MESSAGEID, DESTINATION, MESSAGEBLOB, TXID, TXOP) VALUES(?,?,?,?,?)
 SELECT_ALL_UNCOMMITED_TXS = SELECT TXID FROM JMS_TRANSACTIONS
 SELECT_MAX_TX = SELECT MAX(TXID) FROM JMS_MESSAGES
 DELETE_ALL_TX = DELETE FROM JMS_TRANSACTIONS
 SELECT_MESSAGES_IN_DEST = SELECT MESSAGEID, MESSAGEBLOB FROM JMS_MESSAGES WHERE DESTINATION=?
 SELECT_MESSAGE_KEYS_IN_DEST = SELECT MESSAGEID FROM JMS_MESSAGES WHERE DESTINATION=?
 SELECT_MESSAGE = SELECT MESSAGEID, MESSAGEBLOB FROM JMS_MESSAGES WHERE MESSAGEID=? AND DESTINATION=?
 MARK_MESSAGE = UPDATE JMS_MESSAGES SET TXID=?, TXOP=? WHERE MESSAGEID=? AND DESTINATION=?
 UPDATE_MESSAGE = UPDATE JMS_MESSAGES SET MESSAGEBLOB=? WHERE MESSAGEID=? AND DESTINATION=?
 UPDATE_MARKED_MESSAGES = UPDATE JMS_MESSAGES SET TXID=?, TXOP=? WHERE TXOP=?
 UPDATE_MARKED_MESSAGES_WITH_TX = UPDATE JMS_MESSAGES SET TXID=?, TXOP=? WHERE TXOP=? AND TXID=?
 DELETE_MARKED_MESSAGES_WITH_TX = DELETE FROM JMS_MESSAGES WHERE TXID IN (SELECT TXID FROM JMS_TRANSACTIONS) AND TXOP=?
 DELETE_TX = DELETE FROM JMS_TRANSACTIONS WHERE TXID = ?
 DELETE_MARKED_MESSAGES = DELETE FROM JMS_MESSAGES WHERE TXID=? AND TXOP=?
 DELETE_TEMPORARY_MESSAGES = DELETE FROM JMS_MESSAGES WHERE TXOP='T'
 DELETE_MESSAGE = DELETE FROM JMS_MESSAGES WHERE MESSAGEID=? AND DESTINATION=?
 CREATE_MESSAGE_TABLE = CREATE TABLE JMS_MESSAGES (MESSAGEID INTEGER NOT NULL, DESTINATION VARCHAR(150) NOT NULL, TXID INTEGER, TXOP CHAR(1), MESSAGEBLOB IMAGE)
 CREATE_IDX_MESSAGE_TXOP_TXID = CREATE INDEX JMS_MESSAGES_TXOP_TXID ON JMS_MESSAGES (TXOP, TXID)
 CREATE_IDX_MESSAGE_DESTINATION = CREATE INDEX JMS_MESSAGES_DESTINATION ON JMS_MESSAGES (DESTINATION)
 CREATE_IDX_MESSAGE_MESSAGEID_DESTINATION = CREATE UNIQUE CLUSTERED INDEX JMS_MESSAGES_IDX ON JMS_MESSAGES (MESSAGEID, DESTINATION)
 CREATE_TX_TABLE = CREATE TABLE JMS_TRANSACTIONS ( TXID INTEGER, PRIMARY KEY (TXID) )
 CREATE_TABLES_ON_STARTUP = TRUE

Please help. Thanks!

[balaji_srini]

Can someone help with this error? Thanks in advance!

[adrian.brock]

Do you see the problem with 4.0.5?
No. That's probably because it has been fixed.

[balaji_srini]

Hi Adrian, thanks for your response. Unfortunately, this is happening in our production system, so we won't be able to upgrade to 4.0.5 easily. Is this fix one that I can apply on top of 4.0.1? I am guessing it has something to do with the sql definitions in mssql-jdbc2-service.xml. Is the fix just to remove the index?

[adrian.brock]

The release notes (JIRA) should answer your question.
It is easier for you to find them and read them
than its for me to find and cut and paste redundantly.

If they don't answer your question then feel free to explain which part you don't understand
here. Don't update the JIRA issue with questions!

[marney]

I am seeing this issue on a production server running JBoss 4.2.2 (again, MS SQL Server database). I looked in JBoss JIRA and did not see anything addressing this issue. Portion of our app's trace:

14 May 2008 14:58:08,352 DEBUG [TMMessagingProxy http-0.0.0.0-8082-1] Sending an asynch JMS message: LogStoreHandler.createAuditEntry
14 May 2008 14:58:08,352 INFO [JNDIObjectFactory http-0.0.0.0-8082-1] Looking up queue/AuditQueue
14 May 2008 14:58:08,352 INFO [JNDIObjectFactory http-0.0.0.0-8082-1] About to add the following object to the JNDI reference cache: queue/AuditQueue
14 May 2008 14:58:08,352 ERROR [TMMessagingProxy http-0.0.0.0-8082-1] Error sending aynch message
org.jboss.mq.SpyJMSException: Could not store message: 3606 msg=0 hard NOT_STORED NON_PERSISTENT queue=TOPIC.ecJobTopic.ID:2.-2147483648 priority=4 lateClone=false hashCode=4669730; - nested throwable: (com.microsoft.sqlserver.jdbc.SQLServerException: Cannot insert duplicate key row in object 'dbo.JMS_MESSAGES' with unique index 'JMS_MESSAGES_IDX'.)
at org.jboss.mq.pm.jdbc2.PersistenceManager.saveToStorage(PersistenceManager.java:1638)
at org.jboss.mq.server.MessageCache.saveToStorage(MessageCache.java:420)
at org.jboss.mq.server.MessageReference.makeSoft(MessageReference.java:312)
at org.jboss.mq.server.MessageCache.validateSoftReferenceDepth(MessageCache.java:367)
at org.jboss.mq.server.MessageCache.addInternal(MessageCache.java:158)
at org.jboss.mq.server.MessageCache.add(MessageCache.java:128)
at org.jboss.mq.server.JMSQueue.addMessage(JMSQueue.java:179)
at org.jboss.mq.server.JMSDestinationManager.addMessage(JMSDestinationManager.java:415)
at org.jboss.mq.server.JMSDestinationManager.addMessage(JMSDestinationManager.java:399)
at org.jboss.mq.server.JMSServerInterceptorSupport.addMessage(JMSServerInterceptorSupport.java:106)
at org.jboss.mq.security.ServerSecurityInterceptor.addMessage(ServerSecurityInterceptor.java:168)
at org.jboss.mq.server.TracingInterceptor.addMessage(TracingInterceptor.java:226)
at org.jboss.mq.server.JMSServerInvoker.addMessage(JMSServerInvoker.java:112)
at org.jboss.mq.il.jvm.JVMServerIL.addMessage(JVMServerIL.java:101)
at org.jboss.mq.Connection.sendToServer(Connection.java:920)
at org.jboss.mq.SpySession.sendMessage(SpySession.java:924)
at org.jboss.mq.SpyMessageProducer.send(SpyMessageProducer.java:272)
at org.jboss.mq.SpyMessageProducer.send(SpyMessageProducer.java:219)

[john.hagewood]

I am having this same problem, intermittently, in JBOSS 4.2.3.GA, running on Windows 2003 Server, MS SQL Server 2005:

org.jboss.mq.SpyJMSException: Could not store message: 3682 msg=3669 hard NOT_STORED PERSISTENT queue=QUEUE.VPSEmailQueue priority=4 lateClone=false hashCode=23743259; - nested throwable: (com.microsoft.sqlserver.jdbc.SQLServerException: Cannot insert duplicate key row in object 'dbo.JMS_MESSAGES' with unique index 'JMS_MESSAGES_IDX'.)
at org.jboss.mq.pm.jdbc2.PersistenceManager.add(PersistenceManager.java:1133)
at org.jboss.mq.server.PersistentQueue.addMessage(PersistentQueue.java:58)
at org.jboss.mq.server.JMSQueue.addMessage(JMSQueue.java:180)
at org.jboss.mq.server.JMSDestinationManager.addMessage(JMSDestinationManager.java:439)
at org.jboss.mq.server.JMSDestinationManager.addMessage(JMSDestinationManager.java:422)
at org.jboss.mq.server.JMSServerInvoker.addMessage(JMSServerInvoker.java:112)
at org.jboss.mq.il.uil2.ServerSocketManagerHandler.handleMsg(ServerSocketManagerHandler.java:114)
at org.jboss.mq.il.uil2.SocketManager$ReadTask.handleMsg(SocketManager.java:419)
at org.jboss.mq.il.uil2.msgs.BaseMsg.run(BaseMsg.java:398)
at EDU.oswego.cs.dl.util.concurrent.PooledExecutor$Worker.run(PooledExecutor.java:761)
at java.lang.Thread.run(Thread.java:619)
Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Cannot insert duplicate key row in object 'dbo.JMS_MESSAGES' with unique index 'JMS_MESSAGES_IDX'.
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(Unknown Source)
at com.microsoft.sqlserver.jdbc.IOBuffer.processPackets(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.getPrepExecResponse(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PreparedStatementExecutionRequest.executeStatement(Unknown Source)
at com.microsoft.sqlserver.jdbc.CancelableRequest.execute(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeRequest(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeUpdate(Unknown Source)
at org.jboss.resource.adapter.jdbc.WrappedPreparedStatement.executeUpdate(WrappedPreparedStatement.java:365)
at org.jboss.mq.pm.jdbc2.PersistenceManager.add(PersistenceManager.java:1172)
at org.jboss.mq.pm.jdbc2.PersistenceManager.add(PersistenceManager.java:1118)
... 10 more

We have had to turn on JMS queuing of our outbound emails until a resolution can be found. We are dropping notification emails on the floor due to this JMS issue!

Please help!?

Thanks

John Hagewood
Manager of Software Development