7 Replies Latest reply on Oct 13, 2009 7:17 PM by john.hagewood

    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.



        • 1. Re: JMS error with SQLServer
          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!

          • 2. Re: JMS error with SQLServer
            balaji_srini

            Can someone help with this error? Thanks in advance!

            • 3. Re: JMS error with SQLServer

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

              • 4. Re: JMS error with SQLServer
                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?

                • 5. Re: JMS error with SQLServer

                  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!

                  • 6. Re: JMS error with SQLServer
                    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)


                    • 7. Re: JMS error with SQLServer
                      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