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

[ https://issues.jboss.org/browse/ISPN-1592?page=com.atlassian.jira.plugin.... ] Martin Gencur updated ISPN-1592: -------------------------------- Description: 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. was: 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 still allows using VARCHAR type for ID column on all DBs except Sybase so no need to change documentation; also we can use INT type for all DBs including Sybase (Sybase would work only with INT type). 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. -- This message is automatically generated by JIRA. If you think it was sent incorrectly, please contact your JIRA administrators: https://issues.jboss.org/secure/ContactAdministrators!default.jspa For more information on JIRA, see: http://www.atlassian.com/software/jira