JdbcBatchUpdate exception of JDBC cache store with H2
dex80526 Nov 29, 2011 1:02 PMI tried to use the jdbc cachstore with H2 DB in a cluster replication mode (I had other issues with jdbm cache loaser posted earlier).
My configuration is that:
<clustering mode="replication"> | |
<stateRetrieval | |
timeout="240000" | |
fetchInMemoryState="true" | |
alwaysProvideInMemoryState="false" | |
/> |
<!-- | |
Network calls are synchronous. | |
--> | |
<sync replTimeout="20000"/> |
</clustering> | |
<loaders | |
passivation="false" | |
shared="false" | |
preload="true"> |
<loader | |
class="org.infinispan.loaders.jdbc.stringbased.JdbcStringBasedCacheStore" | |
fetchPersistentState="true" | |
purgeOnStartup="false"> | |
<properties> | |
<property name="stringsTableNamePrefix" value="ISPN_STRING_TABLE"/> | |
<property name="idColumnName" value="ID_COLUMN"/> | |
<property name="dataColumnName" value="DATA_COLUMN"/> | |
<property name="timestampColumnName" value="TIMESTAMP_COLUMN"/> | |
<property name="timestampColumnType" value="BIGINT"/> | |
<property name="connectionFactoryClass" value="org.infinispan.loaders.jdbc.connectionfactory.PooledConnectionFactory"/> | |
<property name="connectionUrl" value="jdbc:h2:file:/var/tmp/h2cachestore;DB_CLOSE_DELAY=-1"/> | |
<property name="userName" value="sa"/> | |
<property name="driverClass" value="org.h2.Driver"/> | |
<property name="idColumnType" value="VARCHAR(255)"/> | |
<property name="dataColumnType" value="BINARY"/> | |
<property name="dropTableOnExit" value="false"/> | |
<property name="createTableOnStart" value="true"/> | |
</properties> </loader> |
I noticed that when the scond node starts to join the cluster, it tries to do batch inserts. This causes Jdbc exception: Unique index or primary key violation error, since the same data exists in the local cache store (H2 database table).
The question is why we do the "Insert" for each cach entry for merge the state, instead of just applying the difference.
Here are logs:
Nov 29, 2011 10:40:47 AM org.infinispan.remoting.rpc.RpcManagerImpl retrieveState
INFO: ISPN000074: Trying to fetch state from test.dex.com-33892
Nov 29, 2011 10:40:48 AM org.infinispan.loaders.jdbc.DataManipulationHelper fromStreamSupport
ERROR: ISPN008003: SQL failure while integrating state into store
org.h2.jdbc.JdbcBatchUpdateException: Unique index or primary key violation: "PRIMARY_KEY_6 ON PUBLIC.ISPN_STRING_TABLE____DEFAULTCACHE(ID_COLUMN)"; SQL statement:
INSERT INTO ISPN_STRING_TABLE____defaultcache (DATA_COLUMN, TIMESTAMP_COLUMN, ID_COLUMN) VALUES(?,?,?) [23505-158]
at org.h2.jdbc.JdbcPreparedStatement.executeBatch(JdbcPreparedStatement.java:1107)
at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeBatch(NewProxyPreparedStatement.java:1723)
at org.infinispan.loaders.jdbc.DataManipulationHelper.fromStreamSupport(DataManipulationHelper.java:109)
at org.infinispan.loaders.jdbc.stringbased.JdbcStringBasedCacheStore.fromStreamLockSafe(JdbcStringBasedCacheStore.java:263)
at org.infinispan.loaders.LockSupportCacheStore.fromStream(LockSupportCacheStore.java:225)
at org.infinispan.statetransfer.StateTransferManagerImpl.applyPersistentState(StateTransferManagerImpl.java:335)
at org.infinispan.statetransfer.StateTransferManagerImpl.applyState(StateTransferManagerImpl.java:279)
at org.infinispan.remoting.InboundInvocationHandlerImpl.applyState(InboundInvocationHandlerImpl.java:235)
at org.infinispan.remoting.transport.jgroups.JGroupsTransport.setState(JGroupsTransport.java:607)
at org.jgroups.blocks.MessageDispatcher$ProtocolAdapter.handleUpEvent(MessageDispatcher.java:711)
at org.jgroups.blocks.MessageDispatcher$ProtocolAdapter.up(MessageDispatcher.java:771)
at org.jgroups.JChannel.up(JChannel.java:1441)
at org.jgroups.stack.ProtocolStack.up(ProtocolStack.java:1074)
at org.jgroups.protocols.pbcast.STREAMING_STATE_TRANSFER.connectToStateProvider(STREAMING_STATE_TRANSFER.java:523)
at org.jgroups.protocols.pbcast.STREAMING_STATE_TRANSFER.handleStateRsp(STREAMING_STATE_TRANSFER.java:462)
at org.jgroups.protocols.pbcast.STREAMING_STATE_TRANSFER.up(STREAMING_STATE_TRANSFER.java:223)
at org.jgroups.protocols.FRAG2.up(FRAG2.java:189)
at org.jgroups.protocols.FlowControl.up(FlowControl.java:418)
at org.jgroups.protocols.FlowControl.up(FlowControl.java:400)
at org.jgroups.protocols.pbcast.GMS.up(GMS.java:908)
at org.jgroups.protocols.pbcast.STABLE.up(STABLE.java:246)
at org.jgroups.protocols.UNICAST.handleDataReceived(UNICAST.java:613)
at org.jgroups.protocols.UNICAST.up(UNICAST.java:294)
at org.jgroups.protocols.pbcast.NAKACK.up(NAKACK.java:703)
at org.jgroups.protocols.VERIFY_SUSPECT.up(VERIFY_SUSPECT.java:133)
at org.jgroups.protocols.FD.up(FD.java:275)
at org.jgroups.protocols.FD_SOCK.up(FD_SOCK.java:275)
at org.jgroups.protocols.MERGE2.up(MERGE2.java:209)
at org.jgroups.protocols.Discovery.up(Discovery.java:293)
at org.jgroups.protocols.TP.passMessageUp(TP.java:1109)
at org.jgroups.protocols.TP$IncomingPacket.handleMyMessage(TP.java:1665)
at org.jgroups.protocols.TP$IncomingPacket.run(TP.java:1647)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1110)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:603)
at java.lang.Thread.run(Thread.java:722)
Do I miss something in the configuration?
How should I configure to sync the data in cache (and cache store) when a new node joins cluster?
Note, I can not purge cache store when a node starts, since I am using the cache store as the persistent store.