Bug 879635 - JDBCCacheStore doesn't work propertly with MSSql
Summary: JDBCCacheStore doesn't work propertly with MSSql
Alias: None
Product: JBoss Data Grid 6
Classification: JBoss
Component: Infinispan
Version: 6.1.0
Hardware: Unspecified
OS: Unspecified
Target Milestone: ER5
: 6.1.0
Assignee: Tristan Tarrant
QA Contact: Martin Gencur
Depends On:
TreeView+ depends on / blocked
Reported: 2012-11-23 14:17 UTC by Anna Manukyan
Modified: 2013-10-30 14:19 UTC (History)
2 users (show)

Fixed In Version:
Doc Type: Bug Fix
Doc Text:
Clone Of:
Last Closed:
Type: Bug

Attachments (Terms of Use)
Relevant part of trace log (15.87 KB, text/x-log)
2012-11-27 07:50 UTC, Martin Gencur
no flags Details

System ID Private Priority Status Summary Last Updated
Red Hat Issue Tracker ISPN-2548 0 Major Resolved JDBCCacheStore doesn't work propertly with MSSql 2017-03-22 18:11:47 UTC

Comment 1 Anna Manukyan 2012-11-23 14:42:29 UTC
Also, please note - that the tests were working properly with JDG6.1.0.ER2 (Infinispan-5.2.0.Beta2 version).

I've noticed that on 9th of November, there were some changes done in TableManipulation to tableExists() method, where the query for defining whether the table exists in DB or not is generated according to the database type. 

So we just tried the previous query for SQL_SERVER database, to find out whether the issue is really in Infinispan or it is on database side, and our change worked. 

The current code is (org.infinispan.loaders.jdbc.TableManipulation.tableExists(Connection connection, String tableName) line 123:

   query = "SELECT count(*) from (SELECT TOP (1) 1 FROM " + tableName + ") T";

We changed to:

   query = "SELECT count(*) FROM " + tableName;

This solution works.

Comment 2 Tristan Tarrant 2012-11-26 14:04:43 UTC
Anna, the intention of the complex query above was to minimize the effort the database uses to discover if the table exists when a table is very large.

Comment 3 JBoss JIRA Server 2012-11-26 14:25:48 UTC
Anna Manukyan <amanukya> made a comment on jira ISPN-2548

Yup, I understand Tristan. But the fact is this doesn't work for MsSQl.

Comment 4 JBoss JIRA Server 2012-11-26 14:41:53 UTC
Tristan Tarrant <ttarrant> made a comment on jira ISPN-2548

Anna, I don't have an instance of SQL Server here, can you try the following query please ?

query = "SELECT count(*) from (SELECT TOP (1) FROM " + tableName + ") T";

Comment 5 JBoss JIRA Server 2012-11-26 16:13:20 UTC
Anna Manukyan <amanukya> made a comment on jira ISPN-2548

Hi Tristan,

we have tried out the new query sent by you, but it gives the same exception described in the jobs logs file.

 There is already an object named 'edg_bin____defaultcache' in the database.

Best regards,

Comment 6 JBoss JIRA Server 2012-11-26 16:32:02 UTC
Tristan Tarrant <ttarrant> made a comment on jira ISPN-2548

Anna can you run the test with trace logs enabled please ?

Comment 7 Martin Gencur 2012-11-27 07:50:22 UTC
Created attachment 652517 [details]
Relevant part of trace log

Comment 8 JBoss JIRA Server 2012-11-29 11:33:30 UTC
Adrian Nistor <anistor> made a comment on jira ISPN-2548

Integrated. Thanks!

Comment 10 JBoss JIRA Server 2013-10-30 14:19:43 UTC
Nicolas Filotto <nfilotto> made a comment on jira ISPN-2548

This is so disappointing to revert this change just because of one query, this feature brought a lot of simplicity to the end user, as we did not have to deal with any db complexity such as the schema name. So now, we need again this kind of change https://github.com/infinispan/infinispan/commit/8a6363ebb2ac26bdbfa9de96550d4514ba011ab0, so if we intend to have several instances on the same database server, we need to define the schema name as prefix of all the tables which is just a real mess to configure when we have to deal with several different instances which is unfortunately my case, too bad for me :-(

FYI, the right query was actually {{query = "SELECT count(*) from (SELECT TOP (1) 1 as C FROM " + tableName + ") T";}}, only the alias was missing which seems to be required in case of MS SQL.

Note You need to log in before you can comment on or make changes to this bug.