Uploaded image for project: 'Infinispan'
  1. Infinispan
  2. ISPN-1592

Jdbc binary cache store throwing conversion error when working with Sybase DB

This issue belongs to an archived project. You can view it, but you can't modify it. Learn more

XMLWordPrintable

      Documentation at https://docs.jboss.org/author/display/ISPN/CacheLoaders suggests specifying idColumnType as VARCHAR. This is not problem with most DBs but it a problem with Sybase DB. This database treats parameters in SQL statements as type sensitive an when we define idColumnType as VARCHAR, jdbc driver for Sybase throws the following exception:

      18:37:33,900 ERROR [org.infinispan.loaders.jdbc.binary.JdbcBinaryCacheStore] (HotRodServerWorker-2-1) ISPN008014: Sql failure while loading key: 1892343808: com.sybase.jdbc4.jdbc.SybSQLException: Implicit conversion from datatype 'INT' to 'VARCHAR' is not allowed.  Use the CONVERT function to run this query.

	at com.sybase.jdbc4.tds.Tds.a(Unknown Source)
	at com.sybase.jdbc4.tds.Tds.nextResult(Unknown Source)
	at com.sybase.jdbc4.jdbc.ResultGetter.nextResult(Unknown Source)
	at com.sybase.jdbc4.jdbc.SybStatement.nextResult(Unknown Source)
	at com.sybase.jdbc4.jdbc.SybStatement.nextResult(Unknown Source)
	at com.sybase.jdbc4.jdbc.SybStatement.queryLoop(Unknown Source)
	at com.sybase.jdbc4.jdbc.SybStatement.executeQuery(Unknown Source)
	at com.sybase.jdbc4.jdbc.SybPreparedStatement.executeQuery(Unknown Source)
	at org.jboss.jca.adapters.jdbc.WrappedPreparedStatement.executeQuery(WrappedPreparedStatement.java:462)
	at org.infinispan.loaders.jdbc.binary.JdbcBinaryCacheStore.loadBucket(JdbcBinaryCacheStore.java:266)
	at org.infinispan.loaders.bucket.BucketBasedCacheStore.loadLockSafe(BucketBasedCacheStore.java:60) [infinispan-core-5.1.0-SNAPSHOT.jar:]
	at org.infinispan.loaders.bucket.BucketBasedCacheStore.loadLockSafe(BucketBasedCacheStore.java:49) [infinispan-core-5.1.0-SNAPSHOT.jar:]
	at org.infinispan.loaders.LockSupportCacheStore.load(LockSupportCacheStore.java:130) [infinispan-core-5.1.0-SNAPSHOT.jar:]
	at org.infinispan.interceptors.CacheLoaderInterceptor.loadIfNeeded(CacheLoaderInterceptor.java:130) [infinispan-core-5.1.0-SNAPSHOT.jar:]
	at org.infinispan.interceptors.CacheLoaderInterceptor.visitPutKeyValueCommand(CacheLoaderInterceptor.java:79) [infinispan-core-5.1.0-SNAPSHOT.jar:]
	at org.infinispan.interceptors.ActivationInterceptor.visitPutKeyValueCommand(ActivationInterceptor.java:60) [infinispan-core-5.1.0-SNAPSHOT.jar:]
	at org.infinispan.commands.write.PutKeyValueCommand.acceptVisitor(PutKeyValueCommand.java:77) [infinispan-core-5.1.0-SNAPSHOT.jar:]
	at org.infinispan.interceptors.base.CommandInterceptor.invokeNextInterceptor(CommandInterceptor.java:119) [infinispan-core-5.1.0-SNAPSHOT.jar:]
	at org.infinispan.interceptors.EntryWrappingInterceptor.invokeNextAndApplyChanges(EntryWrappingInterceptor.java:188) [infinispan-core-5.1.0-SNAPSHOT.jar:]
	at org.infinispan.interceptors.EntryWrappingInterceptor.visitPutKeyValueCommand(EntryWrappingInterceptor.java:137) [infinispan-core-5.1.0-SNAPSHOT.jar:]
	at org.infinispan.commands.write.PutKeyValueCommand.acceptVisitor(PutKeyValueCommand.java:77) [infinispan-core-5.1.0-SNAPSHOT.jar:]
	at org.infinispan.interceptors.base.CommandInterceptor.invokeNextInterceptor(CommandInterceptor.java:119) [infinispan-core-5.1.0-SNAPSHOT.jar:]
	at org.infinispan.interceptors.locking.OptimisticLockingInterceptor.visitPutKeyValueCommand(OptimisticLockingInterceptor.java:108) [infinispan-core-5.1.0-SNAPSHOT.jar:]
	at org.infinispan.commands.write.PutKeyValueCommand.acceptVisitor(PutKeyValueCommand.java:77) [infinispan-core-5.1.0-SNAPSHOT.jar:]
	at org.infinispan.interceptors.base.CommandInterceptor.invokeNextInterceptor(CommandInterceptor.java:119) [infinispan-core-5.1.0-SNAPSHOT.jar:]
	at org.infinispan.interceptors.base.CommandInterceptor.handleDefault(CommandInterceptor.java:133) [infinispan-core-5.1.0-SNAPSHOT.jar:]
	at org.infinispan.commands.AbstractVisitor.visitPutKeyValueCommand(AbstractVisitor.java:61) [infinispan-core-5.1.0-SNAPSHOT.jar:]
	at org.infinispan.commands.write.PutKeyValueCommand.acceptVisitor(PutKeyValueCommand.java:77) [infinispan-core-5.1.0-SNAPSHOT.jar:]
	at org.infinispan.interceptors.base.CommandInterceptor.invokeNextInterceptor(CommandInterceptor.java:119) [infinispan-core-5.1.0-SNAPSHOT.jar:]
	at org.infinispan.interceptors.TxInterceptor.enlistWriteAndInvokeNext(TxInterceptor.java:214) [infinispan-core-5.1.0-SNAPSHOT.jar:]
	at org.infinispan.interceptors.TxInterceptor.visitPutKeyValueCommand(TxInterceptor.java:152) [infinispan-core-5.1.0-SNAPSHOT.jar:]
	at org.infinispan.commands.write.PutKeyValueCommand.acceptVisitor(PutKeyValueCommand.java:77) [infinispan-core-5.1.0-SNAPSHOT.jar:]
	at org.infinispan.interceptors.base.CommandInterceptor.invokeNextInterceptor(CommandInterceptor.java:119) [infinispan-core-5.1.0-SNAPSHOT.jar:]
	at org.infinispan.interceptors.CacheMgmtInterceptor.visitPutKeyValueCommand(CacheMgmtInterceptor.java:115) [infinispan-core-5.1.0-SNAPSHOT.jar:]
	at org.infinispan.commands.write.PutKeyValueCommand.acceptVisitor(PutKeyValueCommand.java:77) [infinispan-core-5.1.0-SNAPSHOT.jar:]
	at org.infinispan.interceptors.base.CommandInterceptor.invokeNextInterceptor(CommandInterceptor.java:119) [infinispan-core-5.1.0-SNAPSHOT.jar:]
	at org.infinispan.interceptors.InvocationContextInterceptor.handleAll(InvocationContextInterceptor.java:107) [infinispan-core-5.1.0-SNAPSHOT.jar:]
	at org.infinispan.interceptors.InvocationContextInterceptor.handleDefault(InvocationContextInterceptor.java:67) [infinispan-core-5.1.0-SNAPSHOT.jar:]
	at org.infinispan.commands.AbstractVisitor.visitPutKeyValueCommand(AbstractVisitor.java:61) [infinispan-core-5.1.0-SNAPSHOT.jar:]
	at org.infinispan.commands.write.PutKeyValueCommand.acceptVisitor(PutKeyValueCommand.java:77) [infinispan-core-5.1.0-SNAPSHOT.jar:]
	at org.infinispan.interceptors.InterceptorChain.invoke(InterceptorChain.java:318) [infinispan-core-5.1.0-SNAPSHOT.jar:]
	at org.infinispan.CacheImpl.executeCommandAndCommitIfNeeded(CacheImpl.java:919) [infinispan-core-5.1.0-SNAPSHOT.jar:]
	at org.infinispan.CacheImpl.put(CacheImpl.java:633) [infinispan-core-5.1.0-SNAPSHOT.jar:]
	at org.infinispan.CacheImpl.put(CacheImpl.java:625) [infinispan-core-5.1.0-SNAPSHOT.jar:]
	at org.infinispan.AbstractDelegatingCache.put(AbstractDelegatingCache.java:114) [infinispan-core-5.1.0-SNAPSHOT.jar:]
	at org.infinispan.server.core.AbstractProtocolDecoder.put(AbstractProtocolDecoder.scala:187) [infinispan-server-core-5.1.0-SNAPSHOT.jar:]
	at org.infinispan.server.core.AbstractProtocolDecoder.decodeValue(AbstractProtocolDecoder.scala:141) [infinispan-server-core-5.1.0-SNAPSHOT.jar:]
	at org.infinispan.server.core.AbstractProtocolDecoder.decode(AbstractProtocolDecoder.scala:71) [infinispan-server-core-5.1.0-SNAPSHOT.jar:]
	at org.infinispan.server.core.AbstractProtocolDecoder.decode(AbstractProtocolDecoder.scala:44) [infinispan-server-core-5.1.0-SNAPSHOT.jar:]
	at org.jboss.netty.handler.codec.replay.CustomReplayingDecoder.callDecode(CustomReplayingDecoder.java:250) [infinispan-server-core-5.1.0-SNAPSHOT.jar:]
	at org.jboss.netty.handler.codec.replay.CustomReplayingDecoder.messageReceived(CustomReplayingDecoder.java:223) [infinispan-server-core-5.1.0-SNAPSHOT.jar:]
	at org.infinispan.server.core.AbstractProtocolDecoder.messageReceived(AbstractProtocolDecoder.scala:351) [infinispan-server-core-5.1.0-SNAPSHOT.jar:]
	at org.jboss.netty.channel.SimpleChannelUpstreamHandler.handleUpstream(SimpleChannelUpstreamHandler.java:80) [netty-3.2.5.Final.jar:]
	at org.jboss.netty.channel.DefaultChannelPipeline.sendUpstream(DefaultChannelPipeline.java:564) [netty-3.2.5.Final.jar:]
	at org.jboss.netty.channel.DefaultChannelPipeline.sendUpstream(DefaultChannelPipeline.java:559) [netty-3.2.5.Final.jar:]
	at org.jboss.netty.channel.Channels.fireMessageReceived(Channels.java:274) [netty-3.2.5.Final.jar:]
	at org.jboss.netty.channel.Channels.fireMessageReceived(Channels.java:261) [netty-3.2.5.Final.jar:]
	at org.jboss.netty.channel.socket.nio.NioWorker.read(NioWorker.java:349) [netty-3.2.5.Final.jar:]
	at org.jboss.netty.channel.socket.nio.NioWorker.processSelectedKeys(NioWorker.java:280) [netty-3.2.5.Final.jar:]
	at org.jboss.netty.channel.socket.nio.NioWorker.run(NioWorker.java:200) [netty-3.2.5.Final.jar:]
	at org.jboss.netty.util.ThreadRenamingRunnable.run(ThreadRenamingRunnable.java:108) [netty-3.2.5.Final.jar:]
	at org.jboss.netty.util.internal.DeadLockProofWorker$1.run(DeadLockProofWorker.java:44) [netty-3.2.5.Final.jar:]
	at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:886) [:1.6.0_21]
	at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:908) [:1.6.0_21]
	at java.lang.Thread.run(Thread.java:619) [:1.6.0_21]

      That is because IDs are hash codes and thus integers. When I specify idColumnType as INT, I get the conversion error in opposite direction: com.sybase.jdbc4.jdbc.SybSQLException: "Implicit conversion from datatype 'CHAR' to 'INT' is not allowed. Use the CONVERT function to run this query."

      There are 2 solutions to this problem (the first one is better IMHO):

      1) use integer type for idColumnType for all DBs, this will need change of few lines in JdbcBinaryCacheStore class in the following manner:

      -         ps.setString(3, bucket.getBucketIdAsString());
+         ps.setInt(3, bucket.getBucketId());

      E.g. at line https://github.com/infinispan/infinispan/blob/master/cachestore/jdbc/src/main/java/org/infinispan/loaders/jdbc/binary/JdbcBinaryCacheStore.java#L199

      This solution would suppose integer type for ID column which is IMHO right, but it would need a little change in the documentation mentioned above (suggesting integer, not varchar).

      2) we would need to handle Sybase specifically and use Sybase's convert function to do the conversion manually: http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.help.ase_15.0.blocks/html/blocks/blocks82.htm . This would need more changes and some if-else statements for SQL queries.

              mgencur Martin Gencur
              mgencur Martin Gencur
              Archiver:
              rhn-support-adongare Amol Dongare

                Created:
                Updated:
                Resolved:
                Archived: