Bug 991012 - Cannot use Sybase db as cache jdbc-store
Cannot use Sybase db as cache jdbc-store
Status: CLOSED NOTABUG
Product: JBoss Enterprise Application Platform 6
Classification: JBoss
Component: Clustering (Show other bugs)
6.1.1
Unspecified Unspecified
low Severity low
: ---
: EAP 6.3.0
Assigned To: Paul Ferraro
Jitka Kozana
Russell Dickenson
:
Depends On:
Blocks:
  Show dependency treegraph
 
Reported: 2013-08-01 07:59 EDT by Tomas Remes
Modified: 2014-04-22 23:36 EDT (History)
5 users (show)

See Also:
Fixed In Version:
Doc Type: Bug Fix
Doc Text:
Story Points: ---
Clone Of:
Environment:
Last Closed: 2014-02-10 12:32:15 EST
Type: Bug
Regression: ---
Mount Type: ---
Documentation: ---
CRM:
Verified Versions:
Category: ---
oVirt Team: ---
RHEL 7.3 requirements from Atomic Host:
Cloudforms Team: ---


Attachments (Terms of Use)

  None (edit)
Description Tomas Remes 2013-08-01 07:59:47 EDT
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]
Comment 1 Tomas Remes 2013-08-01 08:00:44 EDT
Created attachment 781545 [details]
driver
Comment 3 Paul Ferraro 2013-08-27 09:50:29 EDT
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.
Comment 4 Tomas Remes 2013-09-11 04:19:19 EDT
Upstream issue created.
Comment 5 Paul Ferraro 2013-11-22 17:58:33 EST
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
Comment 6 Tomas Remes 2013-11-26 03:26:24 EST
Yes quoted_identifier is on.
Comment 7 Paul Ferraro 2013-12-02 16:27:22 EST
Try appending the following to the jdbc url of the testDS datasource:
?SQLINITSTRING=set quoted_identifier on
Comment 8 Tomas Remes 2013-12-03 04:01:36 EST
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]
Comment 9 Paul Ferraro 2013-12-12 10:51:08 EST
Have you tried using IMAGE as the datum column type?
Comment 10 Tomas Remes 2013-12-13 03:09:09 EST
I've just tried that and it works! But shouldn't Infinispan handle this property setting - SQLINITSTRING=set quoted_identifier by itself?
Comment 11 Radoslav Husar 2013-12-13 05:54:56 EST
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.
Comment 14 Scott Mumford 2014-04-22 23:36:29 EDT
Marking for exclusion from Release Notes documentation as not a bug

Note You need to log in before you can comment on or make changes to this bug.