SQL error using the default MessageStore impl

[dr4w]

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

    <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.

[dr4w]

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

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

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

[oleg.zhurakousky]

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

[dr4w]

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