[infinispan-issues] [JBoss JIRA] (ISPN-1592) Jdbc binary cache store throwing conversion error when working with Sybase DB

Jdbc binary cache store throwing conversion error when working with Sybase DB ----------------------------------------------------------------------------- Key: ISPN-1592 URL: https://issues.jboss.org/browse/ISPN-1592 Project: Infinispan Issue Type: Bug Components: Loaders and Stores Reporter: Martin Gencur Assignee: Martin Gencur 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: {code} 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] {code} 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: {code} - ps.setString(3, bucket.getBucketIdAsString()); + ps.setInt(3, bucket.getBucketId()); {code} E.g. at line https://github.com/infinispan/infinispan/blob/master/cachestore/jdbc/src/... 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... . This would need more changes and some if-else statements for SQL queries.