Apache Derby as cache store behaves different with 5.2.1.Final release

[dex80526]

I updated ISPN from 5.1.5.Final to 5.2.1 for our application, and found some issues with my cache store using Apache Derbey DB (10.8.2.2).

After the update, my test code for the cache store failed in faing to find the DB table which is created by ISPN cache manager.  But, the same code works fine with ISPN 5.1.5 release.

I tried to use Apache Derby CLI ij tool to connect to DB, connection is fine, but the same error (No table exists with the name ISPN_STRING_TABLE_test) occured when tries to describe or select agaisnt the table.

Note, the above error does not occure to the previous ISPN release.

What changes in 5.2.1 could casue this?  or something I missed?

Here is the cache store configuration:

<store

              class="org.infinispan.loaders.jdbc.stringbased.JdbcStringBasedCacheStore"

              fetchPersistentState="true"

              purgeOnStartup="false">

              <properties>

                <property name="stringsTableNamePrefix" value="ISPN_STRING_TABLE"/>

                <property name="idColumnName" value="ID_COLUMN"/>

                <property name="dataColumnName" value="DATA_COLUMN"/>

                <property name="timestampColumnName" value="TIMESTAMP_COLUMN"/>

                <property name="timestampColumnType" value="BIGINT"/>

               <property name="connectionFactoryClass" value="org.infinispan.loaders.jdbc.connectionfactory.PooledConnectionFactory"/>

              <property name="connectionUrl" value="jdbc:derby:/var/tmp/tests/testDB;create=true"/>

                <property name="userName" value="sa"/>

                 <property name="driverClass" value="org.apache.derby.jdbc.EmbeddedDriver"/>

                <property name="idColumnType" value="VARCHAR(255)"/>

                <property name="dataColumnType" value="BLOB"/>

                <property name="dropTableOnExit" value="false"/>

                <property name="createTableOnStart" value="true"/>

             </properties>

           </store>

[nadirx]

Could it be https://issues.jboss.org/browse/ISPN-2548 ?

[dex80526]

It does not look like the same one.  The database and the table is created sucessfully in my case. But, the database created by ISPN is no longer accessbile from Apache Derby cli ij tool, and other JDBC code.

[nadirx]

It could be a problem introduced by the new XML parsers. Replace the stringsTableNamePrefix property with tableNamePrefix.

Also you might want to look into using the new schema: https://docs.jboss.org/author/display/ISPN/Cache+Loaders+and+Stores

[dex80526]

Tristan: thanks for looking into this.

I tried the following confiuration:

<loaders

         passivation="false"

         shared="false"

         preload="true">

         <stringKeyedJdbcStore xmlns="urn:infinispan:config:jdbc:5.2" fetchPersistentState="false" ignoreModifications="false" purgeOnStartup="false">

            <connectionPool connectionUrl="jdbc:derby:/var/tmp/tests/UserProfileDB;create=true" username="sa" driverClass="org.apache.derby.jdbc.EmbeddedDriver"/>

            <stringKeyedTable dropOnExit="true" createOnStart="true" prefix="ISPN_STRING_TABLE">

               <idColumn name="ID_COLUMN" type="VARCHAR(255)" />

               <dataColumn name="DATA_COLUMN" type="BLOB" />

               <timestampColumn name="TIMESTAMP_COLUMN" type="BIGINT" />

            </stringKeyedTable>

        </stringKeyedJdbcStore>

</loaders>

I got the XML parser errors (below). Do you have any ideas?  BTW, I was on 5.2.1Final.

2013-03-15 09:37:17,579 ERROR [InfinispanClusterGuiDemo] (pool-1-thread-1) Unable to start cache!

org.infinispan.config.ConfigurationException: javax.xml.stream.XMLStreamException: ParseError at [row,col]:[74,151]

Message: Unexpected element '{urn:infinispan:config:jdbc:5.2}stringKeyedJdbcStore'

    at org.infinispan.configuration.parsing.ParserRegistry.parse(ParserRegistry.java:87)

    at org.infinispan.manager.DefaultCacheManager.<init>(DefaultCacheManager.java:353)

    at org.infinispan.manager.DefaultCacheManager.<init>(DefaultCacheManager.java:339)

    at com.testit.platform.cluster.infinispan.service.ClusterServiceManager.<init>(ClusterServiceManager.java:43)

    at com.testit.platform.cluster.infinispan.client.InfinispanClusterGuiDemo$10.run(InfinispanClusterGuiDemo.java:378)

    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1110)

    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:603)

    at java.lang.Thread.run(Thread.java:722)

Caused by: javax.xml.stream.XMLStreamException: ParseError at [row,col]:[74,151]

Message: Unexpected element '{urn:infinispan:config:jdbc:5.2}stringKeyedJdbcStore'

    at org.jboss.staxmapper.XMLMapperImpl.processNested(XMLMapperImpl.java:108)

    at org.jboss.staxmapper.XMLExtendedStreamReaderImpl.handleAny(XMLExtendedStreamReaderImpl.java:69)

    at org.infinispan.configuration.parsing.Parser52.parseLoaders(Parser52.java:588)

    at org.infinispan.configuration.parsing.Parser52.parseCache(Parser52.java:180)

    at org.infinispan.configuration.parsing.Parser52.parseNamedCache(Parser52.java:139)

    at org.infinispan.configuration.parsing.Parser52.readElement(Parser52.java:106)

    at org.infinispan.configuration.parsing.Parser52.readElement(Parser52.java:75)

    at org.jboss.staxmapper.XMLMapperImpl.processNested(XMLMapperImpl.java:110)

    at org.jboss.staxmapper.XMLMapperImpl.parseDocument(XMLMapperImpl.java:69)

    at org.infinispan.configuration.parsing.ParserRegistry.parse(ParserRegistry.java:77)

    ... 7 more

[nadirx]

Are you running in some kind of container ? Is the infinispan-cachestore-jdbc jar at version 5.2.1.Final too ?

Also you will unfortunately eventually run into https://issues.jboss.org/browse/ISPN-2928

[dex80526]

Tristan: thanks again.

Yes, I have all 5.2.1.Final jars here.  I ran into another issue.  The parser does not like connectionFactorClass attribute as in the following:

<stringKeyedJdbcStore xmlns="urn:infinispan:config:jdbc:5.2" fetchPersistentState="false" ignoreModifications="false" purgeOnStartup="false">

            <connectionPool connectionUrl="jdbc:derby:/var/tmp/tests/testStore;create=true"

            username="sa" driverClass="org.apache.derby.jdbc.EmbeddedDriver"

            connectionFactoryClass="org.infinispan.loaders.jdbc.connectionfactory.PooledConnectionFactory"

            />

Here is the exception:

org.infinispan.config.ConfigurationException: javax.xml.stream.XMLStreamException: ParseError at [row,col]:[80,15]
Message: Unexpected attribute 'connectionFactoryClass' encountered
    at org.infinispan.configuration.parsing.ParserRegistry.parse(ParserRegistry.java:87)

In addition, in the earlier version of the documentation, the sample configuration shows how to use different connectionFactoryClass.  The  new version here does not have those sample configuration details any more.  I took a look at some source code and test configuration in this area, I did not see any which uses the new store configuration.

Do you know the new cache store/loader will be able to load the data generated with  earlier version?  That's another area I want to verify,

What is my option?  The bug you mentioned above which seems a easy fix. Is there any plan to have 5.2 patch relaese? 

I need to upgrade to 5.2 to use xstite feature.

Appreciate your help.

[nadirx]

The loader hasn't changed, it's just the configuration parser / builder API (so it will load old data). Actually you don't need to specify the connectionFactoryClass anymore: it is implied by the <connectionPool /> element. I'll fix the docs.

[dex80526]

Tristan Tarrant wrote:

Are you running in some kind of container ? Is the infinispan-cachestore-jdbc jar at version 5.2.1.Final too ?

No, there is no container. I am runing a simple test code similiar to the gui demo code.

[dex80526]

Tristan Tarrant wrote:

Actually you don't need to specify the connectionFactoryClass anymore: it is implied by the <connectionPool /> element. I'll fix the docs.

Then, how a user to choose a different connectionFactoryClass, for example, SimpleConnectionFactory for test?

[dex80526]

I tried using the H2 DB (exact the configuration of the sample configuration, and then I got the following exception:

2013-03-15 13:11:43,870 DEBUG [BasicResourcePool] (com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread-#0) An exception occurred while acquiring a poolable resource. Will retry.

java.lang.NullPointerException

    at sun.jdbc.odbc.JdbcOdbcDriver.getProtocol(JdbcOdbcDriver.java:524)

    at sun.jdbc.odbc.JdbcOdbcDriver.knownURL(JdbcOdbcDriver.java:493)

    at sun.jdbc.odbc.JdbcOdbcDriver.acceptsURL(JdbcOdbcDriver.java:307)

    at java.sql.DriverManager.getDriver(DriverManager.java:273)

    at com.mchange.v2.c3p0.DriverManagerDataSource.driver(DriverManagerDataSource.java:223)

    at com.mchange.v2.c3p0.DriverManagerDataSource.getConnection(DriverManagerDataSource.java:134)

    at com.mchange.v2.c3p0.WrapperConnectionPoolDataSource.getPooledConnection(WrapperConnectionPoolDataSource.java:182)

    at com.mchange.v2.c3p0.WrapperConnectionPoolDataSource.getPooledConnection(WrapperConnectionPoolDataSource.java:171)

    at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool$1PooledConnectionResourcePoolManager.acquireResource(C3P0PooledConnectionPool.java:137)

    at com.mchange.v2.resourcepool.BasicResourcePool.doAcquire(BasicResourcePool.java:1014)

    at com.mchange.v2.resourcepool.BasicResourcePool.access$800(BasicResourcePool.java:32)

    at com.mchange.v2.resourcepool.BasicResourcePool$AcquireTask.run(BasicResourcePool.java:1810)

    at com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread.run(ThreadPoolAsynchronousRunner.java:547)

2013-03-15 13:11:43,873 DEBUG [BasicResourcePool] (com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread-#1) An exception occurred while acquiring a poolable resource. Will retry.

java.lang.NullPointerException

    at sun.jdbc.odbc.JdbcOdbcDriver.getProtocol(JdbcOdbcDriver.java:524)

    at sun.jdbc.odbc.JdbcOdbcDriver.knownURL(JdbcOdbcDriver.java:493)

    at sun.jdbc.odbc.JdbcOdbcDriver.acceptsURL(JdbcOdbcDriver.java:307)

    at java.sql.DriverManager.getDriver(DriverManager.java:273)

    at com.mchange.v2.c3p0.DriverManagerDataSource.driver(DriverManagerDataSource.java:223)

    at com.mchange.v2.c3p0.DriverManagerDataSource.getConnection(DriverManagerDataSource.java:134)

    at com.mchange.v2.c3p0.WrapperConnectionPoolDataSource.getPooledConnection(WrapperConnectionPoolDataSource.java:182)

    at com.mchange.v2.c3p0.WrapperConnectionPoolDataSource.getPooledConnection(WrapperConnectionPoolDataSource.java:171)

    at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool$1PooledConnectionResourcePoolManager.acquireResource(C3P0PooledConnectionPool.java:137)

    at com.mchange.v2.resourcepool.BasicResourcePool.doAcquire(BasicResourcePool.java:1014)

    at com.mchange.v2.resourcepool.BasicResourcePool.access$800(BasicResourcePool.java:32)

    at com.mchange.v2.resourcepool.BasicResourcePool$AcquireTask.run(BasicResourcePool.java:1810)

    at com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread.run(ThreadPoolAsynchronousRunner.java:547)

I debug it a bit. Basically, the connectionUrl is null.  It seems the new config or parser is not doing correctly.

[nadirx]

For choosing different connection providers you replace <connectionPool /> with <simpleConnection /> or <dataSource />

Unfortunately, because <connectionPool /> and <simpleConnection /> didn't get much testing, they are broken when using declarative (aka XML) configuration

You are now bumping into https://issues.jboss.org/browse/ISPN-2928

So, either you use <dataSource />, use the programmatic API or wait for the above issue to be fixed (I already have a PR for it).

[nadirx]

Hi Dex,

apart from the new parser problems, I tried the configuration you included in your first post and it works fine. Connecting with ij I get the following:

ij version 10.8

ij> connect 'jdbc:derby:/var/tmp/tests/testDB';

ij> show tables;

TABLE_SCHEM         |TABLE_NAME                    |REMARKS            

------------------------------------------------------------------------          

SA                  |ISPN_STRING_TABLE_test        |

The table is created in the SA schema, so you need to prefix this when querying:

ij> select * from ISPN_STRING_TABLE_test;

ERROR 42X05: Table/View 'ISPN_STRING_TABLE_test' does not exist.

ij> select * from SA.ISPN_STRING_TABLE_test;

ID_COLUMN                                                                                                                       |DATA_COLUMN                                                                                                                     |TIMESTAMP_COLUMN   

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

a                                                                                                                               |0301fe030e3e0161                                                                                                                |-1                 

1 row selected

[dex80526]

<loader> configuration (the old way) is working for me in ISPN too. But, I have problem to use ij to select from the table with the "SA." schema as you did. It always gives the ERROR Table/View does not exist.

What version of Apache derby do you use in your test?

Thanks,

dex

[nadirx]

I used 10.8.