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

Tue Dec 6 03:32:40 EST 2011

     [ https://issues.jboss.org/browse/ISPN-1592?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

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/main/java/org/infinispan/loaders/jdbc/binary/JdbcBinaryCacheStore.java#L199

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.0.blocks/html/blocks/blocks82.htm . 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: