The functional tests for JDBCCacheStore using MSSQL2008 R2 as a store, are failing. You can find the tests here: https://svn.devel.redhat.com/repos/jboss-qa/jdg/jdg-functional-tests/trunk/invm/jdbc-cache-store/src/test/java/com/jboss/datagrid/test/jdbcstore/StringBasedCacheStoreTest.java https://svn.devel.redhat.com/repos/jboss-qa/jdg/jdg-functional-tests/trunk/invm/jdbc-cache-store/src/test/java/com/jboss/datagrid/test/jdbcstore/BinaryCacheStoreTest.java https://svn.devel.redhat.com/repos/jboss-qa/jdg/jdg-functional-tests/trunk/invm/jdbc-cache-store/src/test/java/com/jboss/datagrid/test/jdbcstore/MixedCacheStoreTest.java The test failures can be found here: http://jenkins.mw.lab.eng.bos.redhat.com/hudson/job/edg-60-jdbc-cache-stores-tomcat7/114/DATABASE=mssql2008R2,jdk=java16_default,label=RHEL6_x86_64/testReport/com.jboss.datagrid.test.jdbcstore/BinaryCacheStoreTest/testPutGetRemoveWithoutPassivationWithPreload/ https://jenkins.mw.lab.eng.bos.redhat.com/hudson/job/edg-60-jdbc-cache-stores-eap6/123/DATABASE=mssql2008R2,jdk=java16_default,label=RHEL6_x86_64/testReport/junit/com.jboss.datagrid.test.jdbcstore/BinaryCacheStoreTest/testPutGetRemoveWithoutPassivationWithPreload/
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: ........... case SQL_SERVER: query = "SELECT count(*) from (SELECT TOP (1) 1 FROM " + tableName + ") T"; break; ........... We changed to: ............. case SQL_SERVER: query = "SELECT count(*) FROM " + tableName; break; ............. This solution works.
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.
Anna Manukyan <amanukya> made a comment on jira ISPN-2548 Yup, I understand Tristan. But the fact is this doesn't work for MsSQl.
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";
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. E.g. There is already an object named 'edg_bin____defaultcache' in the database. Best regards, Anna.
Tristan Tarrant <ttarrant> made a comment on jira ISPN-2548 Anna can you run the test with trace logs enabled please ?
Created attachment 652517 [details] Relevant part of trace log
Adrian Nistor <anistor> made a comment on jira ISPN-2548 Integrated. Thanks!
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.