[JBoss JIRA] (ISPN-1592) Jdbc binary cache store throwing conversion error when working with Sybase DB
9:28 a.m.
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:
- ps.setString(3, bucket.getBucketIdAsString());
+ ps.setInt(3, bucket.getBucketId());
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
9:32 a.m.
[
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 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.
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:
- ps.setString(3, bucket.getBucketIdAsString());
+ ps.setInt(3, bucket.getBucketId());
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.
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 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
9:53 a.m.
[
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.
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.
--
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
1:31 p.m.
[
https://issues.jboss.org/browse/ISPN-1592?page=com.atlassian.jira.plugin....
]
Martin Gencur updated ISPN-1592:
--------------------------------
Status: Pull Request Sent (was: Open)
Git Pull Request: https://github.com/infinispan/infinispan/pull/705
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.
--
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
2:26 p.m.
[
https://issues.jboss.org/browse/ISPN-1592?page=com.atlassian.jira.plugin....
]
Galder Zamarreño commented on ISPN-1592:
----------------------------------------
Those if-else should go when we have ISPN-701 in.
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.
--
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
2:31 p.m.
[
https://issues.jboss.org/browse/ISPN-1592?page=com.atlassian.jira.plugin....
]
Galder Zamarreño updated ISPN-1592:
-----------------------------------
Fix Version/s: 5.1.0.CR1
5.1.0.FINAL
Git Pull Request: https://github.com/infinispan/infinispan/pull/705 (was:
https://github.com/infinispan/infinispan/pull/705)
Affects Version/s: 5.1.0.BETA5
5.0.1.FINAL
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
Affects Versions: 5.0.1.FINAL, 5.1.0.BETA5
Reporter: Martin Gencur
Assignee: Martin Gencur
Fix For: 5.1.0.CR1, 5.1.0.FINAL
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.
--
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
2:33 p.m.
[
https://issues.jboss.org/browse/ISPN-1592?page=com.atlassian.jira.plugin....
]
Galder Zamarreño updated ISPN-1592:
-----------------------------------
Status: Resolved (was: Pull Request Sent)
Resolution: Done
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
Affects Versions: 5.0.1.FINAL, 5.1.0.BETA5
Reporter: Martin Gencur
Assignee: Martin Gencur
Fix For: 5.1.0.CR1, 5.1.0.FINAL
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.
--
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
4547
days inactive
4547
days old
infinispan-issues@lists.jboss.org
6 comments
4 participants
participants (4)
-
Galder Zamarreño (Commented) (JIRA) -
Galder Zamarreño (Updated) (JIRA)
-
Martin Gencur (Created) (JIRA)
-
Martin Gencur (Updated) (JIRA)