When you define following jdbc-store for your web session cache container: <binary-keyed-jdbc-store datasource="java:jboss/datasources/testDS" preload="true" passivation="false" purge="false"> <binary-keyed-table prefix="b"> <id-column name="id" type="VARCHAR(255)"/> <data-column name="datum" type="VARBINARY(10000)"/> <timestamp-column name="ver" type="BIGINT"/> </binary-keyed-table> </binary-keyed-jdbc-store> ... then you won't deploy your application using Sybase and attached jconn4 driver. Problem is that it cannot create table executing following statement: CREATE TABLE "b_repl"(id VARCHAR(255) NOT NULL, datum VARBINARY(10000), ver BIGINT, PRIMARY KEY (id)) Correct statement in this configuration is the one without quotation marks. So the deployment fails with following exception: ERROR [org.infinispan.loaders.jdbc.TableManipulation] (ServerService Thread Pool -- 21) ISPN008011: Error while creating table; used DDL statement: 'CREATE TABLE "b_repl"(id VARCHAR(255) NOT NULL, datum VARBINARY(10000), ver BIGINT, PRIMARY KEY (id))': java.sql.SQLException: Incorrect syntax near '('. at com.sybase.jdbc4.jdbc.SybConnection.getAllExceptions(SybConnection.java:2780) at com.sybase.jdbc4.jdbc.SybStatement.handleSQLE(SybStatement.java:2665) at com.sybase.jdbc4.jdbc.SybStatement.nextResult(SybStatement.java:295) at com.sybase.jdbc4.jdbc.SybStatement.nextResult(SybStatement.java:272) at com.sybase.jdbc4.jdbc.SybStatement.updateLoop(SybStatement.java:2515) at com.sybase.jdbc4.jdbc.SybStatement.executeUpdate(SybStatement.java:2499) at com.sybase.jdbc4.jdbc.SybStatement.executeUpdate(SybStatement.java:577) at org.jboss.jca.adapters.jdbc.WrappedStatement.executeUpdate(WrappedStatement.java:375) at org.infinispan.loaders.jdbc.TableManipulation.executeUpdateSql(TableManipulation.java:153) at org.infinispan.loaders.jdbc.TableManipulation.createTable(TableManipulation.java:129) at org.infinispan.loaders.jdbc.TableManipulation.start(TableManipulation.java:231) at org.infinispan.loaders.jdbc.binary.JdbcBinaryCacheStore.doConnectionFactoryInitialization(JdbcBinaryCacheStore.java:514) at org.infinispan.loaders.jdbc.binary.JdbcBinaryCacheStore.start(JdbcBinaryCacheStore.java:102) at org.infinispan.loaders.CacheLoaderManagerImpl.start(CacheLoaderManagerImpl.java:152) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) [rt.jar:1.7.0_21] at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57) [rt.jar:1.7.0_21] at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) [rt.jar:1.7.0_21] at java.lang.reflect.Method.invoke(Method.java:601) [rt.jar:1.7.0_21] at org.infinispan.util.ReflectionUtil.invokeAccessibly(ReflectionUtil.java:203) at org.infinispan.factories.AbstractComponentRegistry$PrioritizedMethod.invoke(AbstractComponentRegistry.java:886) at org.infinispan.factories.AbstractComponentRegistry.invokeStartMethods(AbstractComponentRegistry.java:657) at org.infinispan.factories.AbstractComponentRegistry.internalStart(AbstractComponentRegistry.java:646) at org.infinispan.factories.AbstractComponentRegistry.start(AbstractComponentRegistry.java:549) at org.infinispan.factories.ComponentRegistry.start(ComponentRegistry.java:217) at org.infinispan.CacheImpl.start(CacheImpl.java:582) at org.infinispan.manager.DefaultCacheManager.wireAndStartCache(DefaultCacheManager.java:686) at org.infinispan.manager.DefaultCacheManager.createCache(DefaultCacheManager.java:649) at org.infinispan.manager.DefaultCacheManager.getCache(DefaultCacheManager.java:545) at org.infinispan.manager.DefaultCacheManager.getCache(DefaultCacheManager.java:559) at org.jboss.as.clustering.infinispan.DefaultEmbeddedCacheManager.getCache(DefaultEmbeddedCacheManager.java:109) at org.jboss.as.clustering.infinispan.DefaultEmbeddedCacheManager.getCache(DefaultEmbeddedCacheManager.java:100) at org.jboss.as.clustering.infinispan.subsystem.CacheService.start(CacheService.java:78) at org.jboss.as.clustering.msc.AsynchronousService$1.run(AsynchronousService.java:82) at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145) [rt.jar:1.7.0_21] at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615) [rt.jar:1.7.0_21] at java.lang.Thread.run(Thread.java:722) [rt.jar:1.7.0_21] at org.jboss.threads.JBossThread.run(JBossThread.java:122) [jboss-threads-2.1.0.Final-redhat-1.jar:2.1.0.Final-redhat-1]
Created attachment 781545 [details] driver
This needs an upstream jira. Infinispan should consult the JDBC driver's DatabaseMetaData when determining things like quote characters, what kind of quoting is supported for which kind of statements, etc.
Upstream issue created.
Is the Sybase instance configured with the quoted_identifier option enabled? http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.infocenter.dc38151.1510/html/iqrefbb/CACIIHCI.htm
Yes quoted_identifier is on.
Try appending the following to the jdbc url of the testDS datasource: ?SQLINITSTRING=set quoted_identifier on
Hmm It seems the table was successfully created, but I'm getting following weird exception: ERROR [org.infinispan.loaders.jdbc.binary.JdbcBinaryCacheStore] (OOB-13,shared=udp) ISPN008012: Sql failure while inserting bucket: Bucket{entries={perf19/web=ImmortalCacheEntry{key=perf19/web, value=ImmortalCacheValue {value=perf19=null}}}, bucketId='1768992768'}: java.sql.SQLException: Implicit conversion from datatype 'IMAGE' to 'VARBINARY' is not allowed. Use the CONVERT function to run this query. at com.sybase.jdbc4.jdbc.SybConnection.getAllExceptions(SybConnection.java:2780) at com.sybase.jdbc4.jdbc.SybStatement.handleSQLE(SybStatement.java:2665) at com.sybase.jdbc4.jdbc.SybStatement.nextResult(SybStatement.java:295) at com.sybase.jdbc4.jdbc.SybStatement.nextResult(SybStatement.java:272) at com.sybase.jdbc4.jdbc.SybStatement.updateLoop(SybStatement.java:2515) at com.sybase.jdbc4.jdbc.SybStatement.executeUpdate(SybStatement.java:2499) at com.sybase.jdbc4.jdbc.SybPreparedStatement.executeUpdate(SybPreparedStatement.java:297) at org.jboss.jca.adapters.jdbc.CachedPreparedStatement.executeUpdate(CachedPreparedStatement.java:115) at org.jboss.jca.adapters.jdbc.WrappedPreparedStatement.executeUpdate(WrappedPreparedStatement.java:493) at org.infinispan.loaders.jdbc.binary.JdbcBinaryCacheStore.insertBucket(JdbcBinaryCacheStore.java:214) at org.infinispan.loaders.bucket.BucketBasedCacheStore.storeLockSafe(BucketBasedCacheStore.java:94) at org.infinispan.loaders.bucket.BucketBasedCacheStore.storeLockSafe(BucketBasedCacheStore.java:49) at org.infinispan.loaders.LockSupportCacheStore.store(LockSupportCacheStore.java:213) at org.infinispan.loaders.AbstractCacheStore.applyModifications(AbstractCacheStore.java:126) at org.infinispan.loaders.AbstractCacheStore.commit(AbstractCacheStore.java:163) at org.infinispan.interceptors.CacheStoreInterceptor.commitCommand(CacheStoreInterceptor.java:161) at org.infinispan.interceptors.CacheStoreInterceptor.visitCommitCommand(CacheStoreInterceptor.java:143) at org.infinispan.commands.tx.CommitCommand.acceptVisitor(CommitCommand.java:60) at org.infinispan.interceptors.base.CommandInterceptor.invokeNextInterceptor(CommandInterceptor.java:120) at org.infinispan.interceptors.base.CommandInterceptor.handleDefault(CommandInterceptor.java:134) at org.infinispan.commands.AbstractVisitor.visitCommitCommand(AbstractVisitor.java:136) at org.infinispan.commands.tx.CommitCommand.acceptVisitor(CommitCommand.java:60) at org.infinispan.interceptors.base.CommandInterceptor.invokeNextInterceptor(CommandInterceptor.java:120) at org.infinispan.interceptors.EntryWrappingInterceptor.visitCommitCommand(EntryWrappingInterceptor.java:116) at org.infinispan.commands.tx.CommitCommand.acceptVisitor(CommitCommand.java:60) at org.infinispan.interceptors.base.CommandInterceptor.invokeNextInterceptor(CommandInterceptor.java:120) at org.infinispan.interceptors.base.CommandInterceptor.handleDefault(CommandInterceptor.java:134) at org.infinispan.commands.AbstractVisitor.visitCommitCommand(AbstractVisitor.java:136) at org.infinispan.interceptors.locking.AbstractTxLockingInterceptor.visitCommitCommand(AbstractTxLockingInterceptor.java:101) at org.infinispan.commands.tx.CommitCommand.acceptVisitor(CommitCommand.java:60) at org.infinispan.interceptors.base.CommandInterceptor.invokeNextInterceptor(CommandInterceptor.java:120) at org.infinispan.interceptors.NotificationInterceptor.visitCommitCommand(NotificationInterceptor.java:65) at org.infinispan.commands.tx.CommitCommand.acceptVisitor(CommitCommand.java:60) at org.infinispan.interceptors.base.CommandInterceptor.invokeNextInterceptor(CommandInterceptor.java:120) at org.infinispan.interceptors.TxInterceptor.visitCommitCommand(TxInterceptor.java:153) at org.infinispan.commands.tx.CommitCommand.acceptVisitor(CommitCommand.java:60) at org.infinispan.interceptors.base.CommandInterceptor.invokeNextInterceptor(CommandInterceptor.java:120) at org.infinispan.interceptors.base.CommandInterceptor.handleDefault(CommandInterceptor.java:134) at org.infinispan.commands.AbstractVisitor.visitCommitCommand(AbstractVisitor.java:136) at org.infinispan.statetransfer.TransactionSynchronizerInterceptor.visitCommitCommand(TransactionSynchronizerInterceptor.java:73) at org.infinispan.commands.tx.CommitCommand.acceptVisitor(CommitCommand.java:60) at org.infinispan.interceptors.base.CommandInterceptor.invokeNextInterceptor(CommandInterceptor.java:120) at org.infinispan.statetransfer.StateTransferInterceptor.handleTopologyAffectedCommand(StateTransferInterceptor.java:216) at org.infinispan.statetransfer.StateTransferInterceptor.handleTxCommand(StateTransferInterceptor.java:189) at org.infinispan.statetransfer.StateTransferInterceptor.visitCommitCommand(StateTransferInterceptor.java:121) at org.infinispan.commands.tx.CommitCommand.acceptVisitor(CommitCommand.java:60) at org.infinispan.interceptors.base.CommandInterceptor.invokeNextInterceptor(CommandInterceptor.java:120) at org.infinispan.interceptors.base.CommandInterceptor.handleDefault(CommandInterceptor.java:134) at org.infinispan.commands.AbstractVisitor.visitCommitCommand(AbstractVisitor.java:136) at org.infinispan.commands.tx.CommitCommand.acceptVisitor(CommitCommand.java:60) at org.infinispan.interceptors.base.CommandInterceptor.invokeNextInterceptor(CommandInterceptor.java:120) at org.infinispan.interceptors.InvocationContextInterceptor.handleAll(InvocationContextInterceptor.java:128) at org.infinispan.interceptors.InvocationContextInterceptor.handleDefault(InvocationContextInterceptor.java:92) at org.infinispan.commands.AbstractVisitor.visitCommitCommand(AbstractVisitor.java:136) at org.infinispan.commands.tx.CommitCommand.acceptVisitor(CommitCommand.java:60) at org.infinispan.interceptors.base.CommandInterceptor.invokeNextInterceptor(CommandInterceptor.java:120) at org.infinispan.interceptors.BatchingInterceptor.handleDefault(BatchingInterceptor.java:92) at org.infinispan.commands.AbstractVisitor.visitCommitCommand(AbstractVisitor.java:136) at org.infinispan.commands.tx.CommitCommand.acceptVisitor(CommitCommand.java:60) at org.infinispan.interceptors.InterceptorChain.invoke(InterceptorChain.java:343) at org.infinispan.transaction.TransactionCoordinator.commit(TransactionCoordinator.java:182) at org.infinispan.transaction.synchronization.SynchronizationAdapter.afterCompletion(SynchronizationAdapter.java:81) at org.infinispan.transaction.tm.DummyTransaction.notifyAfterCompletion(DummyTransaction.java:285) at org.infinispan.transaction.tm.DummyTransaction.runCommitTx(DummyTransaction.java:334) at org.infinispan.transaction.tm.DummyTransaction.commit(DummyTransaction.java:91) at org.infinispan.transaction.tm.DummyBaseTransactionManager.commit(DummyBaseTransactionManager.java:102) at org.infinispan.statetransfer.StateConsumerImpl.doApplyState(StateConsumerImpl.java:477) at org.infinispan.statetransfer.StateConsumerImpl.applyState(StateConsumerImpl.java:421) at org.infinispan.statetransfer.StateResponseCommand.perform(StateResponseCommand.java:85) at org.infinispan.remoting.InboundInvocationHandlerImpl.handleInternal(InboundInvocationHandlerImpl.java:101) at org.infinispan.remoting.InboundInvocationHandlerImpl.handleWithWaitForBlocks(InboundInvocationHandlerImpl.java:122) at org.infinispan.remoting.InboundInvocationHandlerImpl.handle(InboundInvocationHandlerImpl.java:86) at org.infinispan.remoting.transport.jgroups.CommandAwareRpcDispatcher.executeCommandFromLocalCluster(CommandAwareRpcDispatcher.java:247) at org.infinispan.remoting.transport.jgroups.CommandAwareRpcDispatcher.handle(CommandAwareRpcDispatcher.java:220) at org.jgroups.blocks.RequestCorrelator.handleRequest(RequestCorrelator.java:484) at org.jgroups.blocks.RequestCorrelator.receiveMessage(RequestCorrelator.java:391) at org.jgroups.blocks.RequestCorrelator.receive(RequestCorrelator.java:249) at org.jgroups.blocks.MessageDispatcher$ProtocolAdapter.up(MessageDispatcher.java:600) at org.jgroups.blocks.mux.MuxUpHandler.up(MuxUpHandler.java:130) at org.jgroups.JChannel.up(JChannel.java:707) at org.jgroups.stack.ProtocolStack.up(ProtocolStack.java:1025) at org.jgroups.protocols.RSVP.up(RSVP.java:172) at org.jgroups.protocols.FRAG2.up(FRAG2.java:181) 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:896) at org.jgroups.protocols.pbcast.STABLE.up(STABLE.java:245) at org.jgroups.protocols.UNICAST2.handleDataReceived(UNICAST2.java:765) at org.jgroups.protocols.UNICAST2.up(UNICAST2.java:420) at org.jgroups.protocols.pbcast.NAKACK.up(NAKACK.java:645) at org.jgroups.protocols.VERIFY_SUSPECT.up(VERIFY_SUSPECT.java:147) at org.jgroups.protocols.FD.up(FD.java:253) at org.jgroups.protocols.FD_SOCK.up(FD_SOCK.java:288) at org.jgroups.protocols.MERGE3.up(MERGE3.java:290) at org.jgroups.protocols.Discovery.up(Discovery.java:359) at org.jgroups.protocols.TP$ProtocolAdapter.up(TP.java:2607) at org.jgroups.protocols.TP.passMessageUp(TP.java:1260) at org.jgroups.protocols.TP$IncomingPacket.handleMyMessage(TP.java:1822) at org.jgroups.protocols.TP$IncomingPacket.run(TP.java:1795) at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145) [rt.jar:1.7.0_40] at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615) [rt.jar:1.7.0_40] at java.lang.Thread.run(Thread.java:724) [rt.jar:1.7.0_40]
Have you tried using IMAGE as the datum column type?
I've just tried that and it works! But shouldn't Infinispan handle this property setting - SQLINITSTRING=set quoted_identifier by itself?
The IMAGE data type is the correct one to store binary data (i.e. the session data): http://dcx.sybase.com/index.html#1201/en/dbreference/image.html Lowering priority and keeping this open to track fix for ISPN-3486.
Marking for exclusion from Release Notes documentation as not a bug