Announcement Announcement Module
Collapse
No announcement yet.
SQL error using the default MessageStore impl Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • SQL error using the default MessageStore impl

    Hi All

    I am hoping somebody may be able to point me in the right direction, Have Spring Integration 2.0.5 with the following configuration

    Code:
        <jee:jndi-lookup id="dataSource" jndi-name="jdbc/BranchDB" cache="true" resource-ref="true" lookup-on-startup="false" proxy-interface="javax.sql.DataSource"/>
    
        <task:executor id="messageHubTaskExecutor" pool-size="50"/>
    
        <int:poller id="defaultPoller" default="true" fixed-rate="1000" max-messages-per-poll="1" task-executor="messageHubTaskExecutor" receive-timeout="100">
            <!-- transactional transaction-manager="transactionManager"/ -->
        </int:poller>
    
        <int-jdbc:message-store id="messageStore" data-source="dataSource" region="BarclaysBranchTiered"/>
    
        <jee:jndi-lookup id="storeDocumentServiceLocation" jndi-name="url/storeDocumentServiceLocation" cache="true" resource-ref="true" lookup-on-startup="true" expected-type="java.net.URL"/>
    
        <bean id="storeDocumentWS" class="uk.co.iquo.commons.services.messageHub.webServiceClients.storeDocument.impl.StoreDocumentCallerImpl" >
            <property name="tokenGenerator" ref="tokenGenerator"/>
            <property name="serviceURL" ref="storeDocumentServiceLocation"/>
            <property name="proxyName" value="${uk.co.iquo.commons.services.messageHub.webserviceClients.proxy.address}"/>
            <property name="proxyPort" value="${uk.co.iquo.commons.services.messageHub.webserviceClients.proxy.port}"/>
            <property name="serviceTimeOut" value="${uk.co.iquo.commons.services.messageHub.webserviceClients.serviceTimeOut}"/>
        </bean>
    
        <bean id="storeDocument" class="uk.co.iquo.commons.services.messageHub.services.storeDocument.impl.StoreDocumentServiceImpl">
            <property name="storeDocumentCaller" ref="storeDocumentWS"/>
            <property name="policyManagerDao" ref="PolicyManagerDao"/>
        </bean>
    
        <bean id="storeDocumentSender" class="uk.co.iquo.commons.services.messageHub.services.storeDocument.impl.StoreDocumentSenderImpl">
            <property name="channel" ref="storeDocumentStartChannel"/>
        </bean>
    
        <int:channel id="storeDocumentStartChannel" datatype="org.dom4j.Document">
            <int:queue message-store="messageStore"/>
        </int:channel>
    
        <int:service-activator id="storeDocumentStartServiceActivator" ref="exceedServices" method="storeDocument" input-channel="storeDocumentStartChannel" output-channel="storeDocumentFilterChannel"/>
    
        <int:channel id="storeDocumentFilterChannel" datatype="uk.co.iquo.commons.services.messageHub.components.messages.payload.StoreDocumentRequest">
            <int:queue message-store="messageStore"/>
        </int:channel>
    
        <int:filter id="storeDocumentMonitorFilter" input-channel="storeDocumentFilterChannel" output-channel="storeDocumentChannel1" ref="locateCustomerMonitorSelector"/>
    
        <int:channel id="storeDocumentChannel1" datatype="uk.co.iquo.commons.services.messageHub.components.messages.payload.StoreDocumentRequest">
            <int:queue message-store="messageStore"/>
        </int:channel>
    
        <int:service-activator id="loadExceedQuoteIDForstoreDocument" ref="storeDocument" method="loadExceedPolicyId" input-channel="storeDocumentChannel1" output-channel="storeDocumentChannel2"/>
    
        <int:channel id="storeDocumentChannel2" datatype="uk.co.iquo.commons.services.messageHub.components.messages.payload.StoreDocumentRequest">
            <int:queue message-store="messageStore"/>
        </int:channel>
    
        <int:recipient-list-router id="storeDocumentLoadExceedClientIdSucessRouter" input-channel="storeDocumentChannel2">
            <int:recipient channel="storeDocumentChannel3" selector-expression="!headers.containsKey('PolicyNumber')"/>
            <int:recipient channel="storeDocumentChannel4" selector-expression="headers.containsKey('PolicyNumber')"/>
        </int:recipient-list-router>
    
        <int:channel id="storeDocumentChannel3" datatype="uk.co.iquo.commons.services.messageHub.components.messages.payload.StoreDocumentRequest">
            <int:queue message-store="messageStore"/>
        </int:channel>
    
        <int:chain id="storeDocumentDataDelayChain" input-channel="storeDocumentChannel3" output-channel="storeDocumentChannel1">
            <int:transformer ref="dataRetryTransformer" method="updateRetry"/>
            <int:delayer default-delay="20000" delay-header-name="dataRetryDelay" scheduler="messageHubTaskScheduler" message-store="messageStore"/>
        </int:chain>
    
        <int:channel id="storeDocumentChannel4" datatype="uk.co.iquo.commons.services.messageHub.components.messages.payload.StoreDocumentRequest">
            <int:queue message-store="messageStore"/>
        </int:channel>
    
        <int:service-activator id="storeDocumentServiceActivator" ref="storeDocument" method="storeDocument" input-channel="storeDocumentChannel4" output-channel="storeDocumentChannel5"/>
    
        <int:channel id="storeDocumentChannel5" datatype="uk.co.iquo.commons.services.messageHub.components.messages.payload.StoreDocumentRequest">
            <int:queue message-store="messageStore"/>
        </int:channel>
    
        <int:header-value-router id="storeDocumentSucessRouter" header-name="completionCode" input-channel="storeDocumentChannel5" default-output-channel="storeDocumentChannel6">
            <int:mapping value="00" channel="nullChannel"/>
        </int:header-value-router>
    
        <int:channel id="storeDocumentChannel6" datatype="uk.co.iquo.commons.services.messageHub.components.messages.payload.StoreDocumentRequest">
            <int:queue message-store="messageStore"/>
        </int:channel>
    
        <int:chain id="storeDocumentDelayChain" input-channel="storeDocumentChannel6" output-channel="storeDocumentChannel4">
            <int:transformer ref="retryTransformer" method="updateRetry"/>
            <int:delayer default-delay="20000" delay-header-name="retryDelay" scheduler="messageHubTaskScheduler" message-store="messageStore"/>
        </int:chain>
    And this config has been running for some time without any problems then we started getting the following exception which I will post in the first reply to this thread.

  • #2
    This is the stack trace that I referd to in the original post

    Code:
    28 Nov 2011 15:09:25,276 [messageHubTaskExecutor-14] DEBUG org.springframework.integration.handler.ServiceActivatingHandler  - handler 'ServiceActivator for [org.springframework.integration.handler.MethodInvokingMessageProcessor@5a865a86]' sending reply Message: [Payload=StoreDocumentRequest{clientID=null, docRef=NMHOQPCK, docType=Quote Pack, documentName=HHBranchQuotePack.pdf, effectiveDate=2011-12-11, expiryDate=2012-12-10, indexClass=ExceedPolicyPacks, policyNBR=null, printDate=2011-11-27, printRef=IQUO Output, productCode=MHO, quoteNumber=null, quoteSequenceNumber=null, schemeCode=292}][Headers={clientUID=3127, dataRetryDelay=2400000, JdbcMessageStore.SAVED=true, dataRetryCount=28, schemeCode=292, docType=Quote Pack, documentType=HHBranchQuotePack, timestamp=1322468014982, id=a5f76c62-4776-4c29-9231-8d7becee8cdc, docRef=NMHOQPCK, JdbcMessageStore.CREATED_DATE=1322468014982, policyUID=3741, livePolicy=false}]
    28 Nov 2011 15:09:25,276 [messageHubTaskExecutor-14] DEBUG org.springframework.integration.channel.QueueChannel  - preSend on channel 'storeDocumentChannel2', message: [Payload=StoreDocumentRequest{clientID=null, docRef=NMHOQPCK, docType=Quote Pack, documentName=HHBranchQuotePack.pdf, effectiveDate=2011-12-11, expiryDate=2012-12-10, indexClass=ExceedPolicyPacks, policyNBR=null, printDate=2011-11-27, printRef=IQUO Output, productCode=MHO, quoteNumber=null, quoteSequenceNumber=null, schemeCode=292}][Headers={clientUID=3127, dataRetryDelay=2400000, JdbcMessageStore.SAVED=true, dataRetryCount=28, schemeCode=292, docType=Quote Pack, documentType=HHBranchQuotePack, timestamp=1322468014982, id=a5f76c62-4776-4c29-9231-8d7becee8cdc, docRef=NMHOQPCK, JdbcMessageStore.CREATED_DATE=1322468014982, policyUID=3741, livePolicy=false}]
    28 Nov 2011 15:09:25,291 [messageHubTaskExecutor-14] DEBUG org.springframework.integration.jdbc.JdbcMessageStore  - Inserting message with id key=a5f76c62-4776-4c29-9231-8d7becee8cdc and created date=1322492965276
    28 Nov 2011 15:09:25,525 [messageHubTaskExecutor-14] DEBUG org.springframework.integration.channel.PublishSubscribeChannel  - preSend on channel 'errorChannel', message: [Payload=org.springframework.integration.MessageDeliveryException: failed to send Message to channel 'storeDocumentChannel2'][Headers={timestamp=1322492965525, id=b2431a72-b82a-41b4-994f-0b0849023b1f}]
    28 Nov 2011 15:09:25,525 [messageHubTaskExecutor-14] DEBUG org.springframework.integration.handler.LoggingHandler  - (inner bean)#53 received message: [Payload=org.springframework.integration.MessageDeliveryException: failed to send Message to channel 'storeDocumentChannel2'][Headers={timestamp=1322492965525, id=b2431a72-b82a-41b4-994f-0b0849023b1f}]
    28 Nov 2011 15:09:25,525 [messageHubTaskExecutor-14] ERROR org.springframework.integration.handler.LoggingHandler  - org.springframework.integration.MessageDeliveryException: failed to send Message to channel 'storeDocumentChannel2'
    	at java.lang.Throwable.<init>(Throwable.java:80)
    	at org.springframework.integration.MessagingException.<init>(MessagingException.java:57)
    	at org.springframework.integration.channel.AbstractMessageChannel.send(AbstractMessageChannel.java:146)
    	at org.springframework.integration.channel.AbstractMessageChannel.send(AbstractMessageChannel.java:128)
    	at org.springframework.integration.core.MessagingTemplate.doSend(MessagingTemplate.java:288)
    	at org.springframework.integration.core.MessagingTemplate.send(MessagingTemplate.java:149)
    	at org.springframework.integration.handler.AbstractReplyProducingMessageHandler.sendMessage(AbstractReplyProducingMessageHandler.java:176)
    	at org.springframework.integration.handler.AbstractReplyProducingMessageHandler.sendReplyMessage(AbstractReplyProducingMessageHandler.java:157)
    	at org.springframework.integration.handler.AbstractReplyProducingMessageHandler.produceReply(AbstractReplyProducingMessageHandler.java:125)
    	at org.springframework.integration.handler.AbstractReplyProducingMessageHandler.handleResult(AbstractReplyProducingMessageHandler.java:119)
    	at org.springframework.integration.handler.AbstractReplyProducingMessageHandler.handleMessageInternal(AbstractReplyProducingMessageHandler.java:101)
    	at org.springframework.integration.handler.AbstractMessageHandler.handleMessage(AbstractMessageHandler.java:78)
    	at org.springframework.integration.endpoint.PollingConsumer.doPoll(PollingConsumer.java:70)
    	at org.springframework.integration.endpoint.AbstractPollingEndpoint$1.call(AbstractPollingEndpoint.java:146)
    	at org.springframework.integration.endpoint.AbstractPollingEndpoint$1.call(AbstractPollingEndpoint.java:144)
    	at org.springframework.integration.endpoint.AbstractPollingEndpoint$Poller$1.run(AbstractPollingEndpoint.java:207)
    	at org.springframework.integration.util.ErrorHandlingTaskExecutor$1.run(ErrorHandlingTaskExecutor.java)
    	at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:897)
    	at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:919)
    	at java.lang.Thread.run(Thread.java:736)
    Caused by: org.springframework.dao.DuplicateKeyException: PreparedStatementCallback; SQL [INSERT into INT_MESSAGE_GROUP(MESSAGE_ID, REGION, CREATED_DATE, GROUP_KEY, MARKED, MESSAGE_BYTES) values (?, ?, ?, ?, 0, ?)]; One or more values in the INSERT statement, UPDATE statement, or foreign key update caused by a DELETE statement are not valid because the primary key, unique constraint or unique index identified by "1" constrains table "DB2ADMIN.INT_MESSAGE_GROUP" from having duplicate values for the index key.. SQLCODE=-803, SQLSTATE=23505, DRIVER=3.57.110; nested exception is com.ibm.websphere.ce.cm.DuplicateKeyException: One or more values in the INSERT statement, UPDATE statement, or foreign key update caused by a DELETE statement are not valid because the primary key, unique constraint or unique index identified by "1" constrains table "DB2ADMIN.INT_MESSAGE_GROUP" from having duplicate values for the index key.. SQLCODE=-803, SQLSTATE=23505, DRIVER=3.57.110
    	at java.lang.Throwable.<init>(Throwable.java:80)
    	at org.springframework.core.NestedRuntimeException.<init>(NestedRuntimeException.java:64)
    	at org.springframework.dao.DataAccessException.<init>(DataAccessException.java:55)
    	at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:177)
    	at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72)
    	at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:602)
    	at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:811)
    	at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:867)
    	at org.springframework.integration.jdbc.JdbcMessageStore.addMessageToGroup(JdbcMessageStore.java:296)
    	at org.springframework.integration.store.MessageGroupQueue.offer(MessageGroupQueue.java:86)
    	at org.springframework.integration.store.MessageGroupQueue.put(MessageGroupQueue.java:176)
    	at org.springframework.integration.store.MessageGroupQueue.put(MessageGroupQueue.java:37)
    	at org.springframework.integration.channel.QueueChannel.doSend(QueueChannel.java:79)
    	at org.springframework.integration.channel.AbstractMessageChannel.send(AbstractMessageChannel.java:154)
    	... 17 more
    Caused by: com.ibm.websphere.ce.cm.DuplicateKeyException: One or more values in the INSERT statement, UPDATE statement, or foreign key update caused by a DELETE statement are not valid because the primary key, unique constraint or unique index identified by "1" constrains table "DB2ADMIN.INT_MESSAGE_GROUP" from having duplicate values for the index key.. SQLCODE=-803, SQLSTATE=23505, DRIVER=3.57.110
    	at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
    	at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:56)
    	at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:39)
    	at java.lang.reflect.Constructor.newInstance(Constructor.java:527)
    	at com.ibm.websphere.rsadapter.GenericDataStoreHelper.mapExceptionHelper(GenericDataStoreHelper.java:605)
    	at com.ibm.websphere.rsadapter.GenericDataStoreHelper.mapException(GenericDataStoreHelper.java:667)
    	at com.ibm.ws.rsadapter.AdapterUtil.mapException(AdapterUtil.java:2111)
    	at com.ibm.ws.rsadapter.jdbc.WSJdbcUtil.mapException(WSJdbcUtil.java:1034)
    	at com.ibm.ws.rsadapter.jdbc.WSJdbcPreparedStatement.executeUpdate(WSJdbcPreparedStatement.java:821)
    	at org.springframework.jdbc.core.JdbcTemplate$2.doInPreparedStatement(JdbcTemplate.java:817)
    	at org.springframework.jdbc.core.JdbcTemplate$2.doInPreparedStatement(JdbcTemplate.java:1)
    	at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:586)
    	... 25 more
    
    28 Nov 2011 15:09:25,525 [messageHubTaskExecutor-14] DEBUG org.springframework.integration.channel.PublishSubscribeChannel  - postSend (sent=true) on channel 'errorChannel', message: [Payload=org.springframework.integration.MessageDeliveryException: failed to send Message to channel 'storeDocumentChannel2'][Headers={timestamp=1322492965525, id=b2431a72-b82a-41b4-994f-0b0849023b1f}]
    When I check the database table directly with
    Code:
    SELECT * FROM DB2ADMIN."INT_MESSAGE_GROUP" where message_id='b2431a72-b82a-41b4-994f-0b0849023b1f';
    no results are returned, so not sure why this is happening, or where to look to get more info about what may be setup incorrect.

    Any help would be most welcome

    Reagrds

    David Wilkinson

    Comment


    • #3
      David, i'll look into this, but since 2.0.5 JdbcMessageStore had major worked performed on it and we just released 2.1.RC1.

      Could you possibly upgrade and see if you have the same error?
      Thanks

      Comment


      • #4
        Hi Oleg

        Thanks for the quick reply.

        I will upgrade the Jar files and see if I get the same error and let you know on this thread

        Regards

        David

        Comment

        Working...
        X