14 Replies Latest reply on Nov 5, 2007 8:40 AM by gmeroz

    JDBCCacheLoader problem in java application

    gmeroz Newbie

      in version 2.0.0GA

      When i use JDBCCacheLoader inside JBossAS it wokrs fine, but when
      i try to run the same code from a regular java Main class it fails with the following exception. i seems that some connections are not closed.

      ....
      added 85 dummy node to Jboss cache.
      added 86 dummy node to Jboss cache.
      18:15:52,164 ERROR [NonManagedConnectionFactory] Failed to get connection for url=jdbc:oracle:thin:@dbms01:1521:besapp, user=GGGG, password=gggg
      java.sql.SQLException: Io exception: The Network Adapter could not establish the connection
       at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:125)
       at oracle.jdbc.driver.DatabaseError.throwSqlException
      ...
      ...
       at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:563)
       at java.sql.DriverManager.getConnection(DriverManager.java:582)
       at java.sql.DriverManager.getConnection(DriverManager.java:185)
       at org.jboss.cache.loader.NonManagedConnectionFactory.checkoutConnection(NonManagedConnectionFactory.java:99)
       at org.jboss.cache.loader.NonManagedConnectionFactory.getConnection(NonManagedConnectionFactory.java:80)
       at org.jboss.cache.loader.AdjListJDBCCacheLoader.loadNode(AdjListJDBCCacheLoader.java:373)
       at org.jboss.cache.loader.AdjListJDBCCacheLoader.get(AdjListJDBCCacheLoader.java:97)
       at org.jboss.cache.interceptors.CacheLoaderInterceptor.loadData(CacheLoaderInterceptor.java:530)
       at org.jboss.cache.interceptors.CacheLoaderInterceptor.loadNode(CacheLoaderInterceptor.java:408)
       at org.jboss.cache.interceptors.CacheLoaderInterceptor.loadIfNeeded(CacheLoaderInterceptor.java:210)
       at org.jboss.cache.interceptors.CacheLoaderInterceptor.invoke(CacheLoaderInterceptor.java:157)
       at org.jboss.cache.interceptors.Interceptor.invoke(Interceptor.java:76)
       at org.jboss.cache.interceptors.UnlockInterceptor.invoke(UnlockInterceptor.java:37)
       at org.jboss.cache.interceptors.Interceptor.invoke(Interceptor.java:76)
       at org.jboss.cache.interceptors.PessimisticLockInterceptor.invoke(PessimisticLockInterceptor.java:203)
       at org.jboss.cache.interceptors.Interceptor.invoke(Interceptor.java:76)
       at org.jboss.cache.interceptors.NotificationInterceptor.invoke(NotificationInterceptor.java:32)
       at org.jboss.cache.interceptors.Interceptor.invoke(Interceptor.java:76)
       at org.jboss.cache.interceptors.TxInterceptor.handleNonTxMethod(TxInterceptor.java:298)
       at org.jboss.cache.interceptors.TxInterceptor.invoke(TxInterceptor.java:131)
       at org.jboss.cache.interceptors.Interceptor.invoke(Interceptor.java:76)
       at org.jboss.cache.interceptors.CacheMgmtInterceptor.invoke(CacheMgmtInterceptor.java:81)
       at org.jboss.cache.interceptors.Interceptor.invoke(Interceptor.java:76)
       at org.jboss.cache.interceptors.InvocationContextInterceptor.invoke(InvocationContextInterceptor.java:62)
       at org.jboss.cache.CacheImpl.invokeMethod(CacheImpl.java:3939)
       at org.jboss.cache.CacheImpl.get(CacheImpl.java:1441)
       at org.jboss.cache.CacheImpl.get(CacheImpl.java:1415)
       at org.jboss.cache.pojo.impl.InternalHelper.get(InternalHelper.java:92)
       at org.jboss.cache.pojo.impl.InternalHelper.getPojoInstance(InternalHelper.java:38)
       at org.jboss.cache.pojo.impl.InternalHelper.getPojo(InternalHelper.java:198)
       at org.jboss.cache.pojo.impl.PojoCacheDelegate.putObjectII(PojoCacheDelegate.java:131)
       at org.jboss.cache.pojo.impl.PojoCacheImpl.putObject(PojoCacheImpl.java:136)
       at org.jboss.cache.pojo.impl.PojoCacheImpl.org$jboss$cache$pojo$impl$PojoCacheImpl$attach$aop(PojoCacheImpl.java:101)
       at org.jboss.cache.pojo.impl.PojoCacheImpl.attach(PojoCacheImpl.java)
       at org.jboss.cache.pojo.impl.PojoCacheImpl.attach(PojoCacheImpl.java:93)
       at JDBCCacheTest.main(JDBCCacheTest.java:10)
       at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
       at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
       at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
      




        • 1. Re: JDBCCacheLoader problem in java application
          gmeroz Newbie

          The code to produce this error:

          import org.jboss.cache.pojo.PojoCache;
          import org.jboss.cache.pojo.PojoCacheFactory;
          import org.jboss.cache.Fqn;
          
          public class JDBCCacheTest {
          
           public static final String CONF_FILE = "jboss-cache.xml";
           public static void main(String[] args) {
           PojoCache cache = PojoCacheFactory.createCache(CONF_FILE, true);
           cache.getCache().removeNode(Fqn.fromString("C"));
           for (int i = 0; i < 100; i++) {
           cache.attach("C/"+ Integer.toString(i), Integer.toString(i));
           System.out.println("added "+i+" dummy node to Jboss cache.");
           }
           }
          }
          
          and jbosscache config file:
          
          <?xml version="1.0" encoding="UTF-8"?>
          
          <server>
          
           <classpath codebase="./lib/jboss" archives="jbosscache.jar,jboss-cache.jar, jgroups.jar,jboss-aop-jdk50.jar,javassist.jar,jboss-common-core.jar,jboss-serialization.jar,pojocache.jar"/>
          
           <mbean code="org.jboss.cache.TreeCache"
           name="jboss.cache:service=EngineServerTreeCache">
          
           <depends>jboss:service=Naming</depends>
           <depends>jboss:service=TransactionManager</depends>
           <depends>jboss.jca:service=DataSourceBinding,name=beDS</depends>
           <attribute name="TransactionManagerLookupClass">org.jboss.cache.transaction.DummyTransactionManagerLookup</attribute>
          
           <attribute name="CacheMode">LOCAL</attribute>
           <attribute name="CacheLoaderConfiguration">
           <config>
           <passivation>false</passivation>
           <!--<preload>/</preload>-->
           <shared>true</shared>
          
           <cacheloader>
           <class>com.bevents.infra.service.cache.JDBCCacheLoader</class>
           <properties>
           cache.jdbc.table.name=jbosscache_engine_cache
           cache.jdbc.table.create=true
           cache.jdbc.table.drop=true
           cache.jdbc.table.primarykey=jbosscache_engine_cache_pk
           cache.jdbc.fqn.column=fqn
           cache.jdbc.fqn.type=varchar(255)
           cache.jdbc.node.column=node
           cache.jdbc.node.type=blob
           cache.jdbc.parent.column=parent
           cache.jdbc.driver=oracle.jdbc.driver.OracleDriver
           cache.jdbc.url=jdbc:oracle:thin:@dbms01:1521:besapp
           cache.jdbc.user=some_db
           cache.jdbc.password=some_db
           </properties>
          
           <async>false</async>
           <fetchPersistentState>false</fetchPersistentState>
           <ignoreModifications>false</ignoreModifications>
           <purgeOnStartup>false</purgeOnStartup>
           </cacheloader>
           </config>
           </attribute>
           </mbean>
          
          </server>
          
          


          • 2. Re: JDBCCacheLoader problem in java application
            Elias Ross Master

             

            18:15:52,164 ERROR [NonManagedConnectionFactory] Failed to get connection for url=jdbc:oracle:thin:@
            dbms01:1521:besapp, user=GGGG, password=gggg
            java.sql.SQLException: Io exception: The Network Adapter could not establish the connection
            

            Can you "telnet besapp 1521" from the machine running JBoss?

            • 3. Re: JDBCCacheLoader problem in java application
              gmeroz Newbie

              yes. actualy i was able to put 60 or so items into the cache (which were inserted into the DB).

              it seems that some connection is not closed by JBossCache.

              • 4. Re: JDBCCacheLoader problem in java application
                Manik Surtani Master

                I wrote a unit test to incorporate pretty much what you are trying to test, with a few changes:

                * Uses core cache instead of pojo cache, since this shouldn't be a factor in your test
                * Uses Derby instead of Oracle, easier to set up and tear down during a unit test
                * Tested on SVN trunk (2.1.0) rather than 2.0.0, but not much has changed wrt, the JDBCCacheLoader or connection management.

                Both of the above should be irrelevant to what you are suggesting regarding the JDBCCacheLoader closing connections.

                I don't see any stale connections or even the problem that you see - the test runs fine. The unit test is here, if you want to have a look at it, and perhaps try and run it on Oracle if that makes any difference.

                http://viewvc.jboss.org/cgi-bin/viewvc.cgi/jbosscache/core/trunk/src/test/java/org/jboss/cache/loader/JDBCCacheLoaderConnectionTest.java?view=markup

                To try this out yourself,

                1) Check out trunk from SVN

                svn co http://anonsvn.jboss.org/repos/jbosscache/core/trunk
                


                2) Run the test:
                mvn clean test -Dtest=JDBCCacheLoaderConnectionTest
                


                3) Edit src/test/java/org/jboss/cache/loader/JDBCCacheLoaderConnectionTest.java to connect to your Oracle DB instead

                4) Repeat (2)


                • 5. Re: JDBCCacheLoader problem in java application
                  Manik Surtani Master

                  Ah, I just noticed one important thing - your configuration uses com.bevents.infra.service.cache.JDBCCacheLoader instead of the JDBCCacheLoader shipped with JBoss Cache. Not sure what impact this has on how connections are managed.

                  • 6. Re: JDBCCacheLoader problem in java application
                    gmeroz Newbie

                    this class only extends JDBCCacheLoader to change the table name on runtime (something i used for testing). When i use the original JDBCCacheLoader it's the same.

                    i'll try your demo...

                    • 7. Re: JDBCCacheLoader problem in java application
                      gmeroz Newbie

                      The test runs successfully for both Derby & Oracle.

                      i change my code to use core cache instead of pojo cache & it solves the problem.

                      • 8. Re: JDBCCacheLoader problem in java application
                        gmeroz Newbie

                        Since we both agree that there shouldn't be a difference between pojo & core cache related to this issue, i decided to run more tests & managed to reproduce the problem.

                        i just changed the loop in your test to run up to 1000 & got the following error (only when using Oracle):

                        java.lang.IllegalStateException: Failed to load node for fqn /C/487
                         at org.jboss.cache.loader.AdjListJDBCCacheLoader.reportAndRethrowError(AdjListJDBCCacheLoader.java:652)
                         at org.jboss.cache.loader.AdjListJDBCCacheLoader.loadNode(AdjListJDBCCacheLoader.java:408)
                         at org.jboss.cache.loader.JDBCCacheLoader._put(JDBCCacheLoader.java:188)
                         at org.jboss.cache.loader.JDBCCacheLoader.put(JDBCCacheLoader.java:69)
                         at org.jboss.cache.interceptors.CacheStoreInterceptor.invoke(CacheStoreInterceptor.java:219)
                         at org.jboss.cache.interceptors.Interceptor.invoke(Interceptor.java:76)
                         at org.jboss.cache.interceptors.CacheLoaderInterceptor.invoke(CacheLoaderInterceptor.java:180)
                         at org.jboss.cache.interceptors.Interceptor.invoke(Interceptor.java:76)
                         at org.jboss.cache.interceptors.UnlockInterceptor.invoke(UnlockInterceptor.java:36)
                         at org.jboss.cache.interceptors.Interceptor.invoke(Interceptor.java:76)
                         at org.jboss.cache.interceptors.PessimisticLockInterceptor.invoke(PessimisticLockInterceptor.java:201)
                         at org.jboss.cache.interceptors.Interceptor.invoke(Interceptor.java:76)
                         at org.jboss.cache.interceptors.NotificationInterceptor.invoke(NotificationInterceptor.java:32)
                         at org.jboss.cache.interceptors.Interceptor.invoke(Interceptor.java:76)
                         at org.jboss.cache.interceptors.TxInterceptor.handleNonTxMethod(TxInterceptor.java:299)
                         at org.jboss.cache.interceptors.TxInterceptor.invoke(TxInterceptor.java:131)
                         at org.jboss.cache.interceptors.Interceptor.invoke(Interceptor.java:76)
                         at org.jboss.cache.interceptors.CacheMgmtInterceptor.invoke(CacheMgmtInterceptor.java:97)
                         at org.jboss.cache.interceptors.Interceptor.invoke(Interceptor.java:76)
                         at org.jboss.cache.interceptors.InvocationContextInterceptor.invoke(InvocationContextInterceptor.java:62)
                         at org.jboss.cache.CacheImpl.invokeMethod(CacheImpl.java:4019)
                         at org.jboss.cache.CacheImpl.put(CacheImpl.java:1535)
                         at org.jboss.cache.loader.JDBCCacheLoaderConnectionTest.testConnectionRelease(JDBCCacheLoaderConnectionTest.java:55)
                         at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
                         at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
                         at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
                         at com.intellij.rt.execution.junit.JUnitStarter.main(JUnitStarter.java:40)
                         at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
                         at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
                         at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
                         at com.intellij.rt.execution.application.AppMain.main(AppMain.java:90)
                        Caused by: java.lang.IllegalStateException: Failed to get connection for url=jdbc:oracle:thin:@dbms01:1521:besapp, user=gggg, password=gggg
                         at org.jboss.cache.loader.NonManagedConnectionFactory.reportAndRethrowError(NonManagedConnectionFactory.java:236)
                         at org.jboss.cache.loader.NonManagedConnectionFactory.getConnection(NonManagedConnectionFactory.java:85)
                         at org.jboss.cache.loader.AdjListJDBCCacheLoader.loadNode(AdjListJDBCCacheLoader.java:373)
                         ... 44 more
                        Caused by: java.sql.SQLException: Io exception: The Network Adapter could not establish the connection
                         at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:125)
                         at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:162)
                         at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:274)
                         at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:328)
                         at oracle.jdbc.driver.PhysicalConnection.<init>(PhysicalConnection.java:348)
                         at oracle.jdbc.driver.T4CConnection.<init>(T4CConnection.java:151)
                         at oracle.jdbc.driver.T4CDriverExtension.getConnection(T4CDriverExtension.java:32)
                         at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:563)
                         at java.sql.DriverManager.getConnection(DriverManager.java:582)
                         at java.sql.DriverManager.getConnection(DriverManager.java:185)
                         at org.jboss.cache.loader.NonManagedConnectionFactory.checkoutConnection(NonManagedConnectionFactory.java:99)
                         at org.jboss.cache.loader.NonManagedConnectionFactory.getConnection(NonManagedConnectionFactory.java:80)
                         ... 45 more
                        
                        


                        Is there any workaround for hits?

                        • 9. Re: JDBCCacheLoader problem in java application
                          Manik Surtani Master

                          Hmm, yes and such problems won't crop up in Derby given that the number of connections is theoretically unlimited.

                          http://www.nabble.com/Maximum-connections-to-embedded-database-t4547454.html

                          • 10. Re: JDBCCacheLoader problem in java application
                            gmeroz Newbie

                            1. Should i open a bug for that?

                            2. Is there is any workaround your can think of? i need to pre-cache some data, i was planning to use jboss cache for that.

                            • 11. Re: JDBCCacheLoader problem in java application
                              Manik Surtani Master

                              Ok, so I tried this with a remote MySQL 5 database, ran through 2000 iterations and had no problems.

                              It will be a while before I can test this on Oracle as well, what version of Oracle and which JDBC driver do you use?

                              Do you know if a firewall between your app server and database is causing connection problems, this can be a pretty common issue.

                              • 12. Re: JDBCCacheLoader problem in java application
                                Manik Surtani Master

                                Basically I can't see how or why connections aren't being released. If there is an issue, I suspect it may involve the Oracle driver and/or the connection to the database.

                                Have you tried using a c3p0 connection pool instead? This may help you as a workaround.

                                • 13. Re: JDBCCacheLoader problem in java application
                                  Mircea Markus Master

                                  You are using NonManagedConnectionFactory, which is does not pool the connection. The stack trace shows that the driver tries to create a new physical connection to the database(no driver pooling) , so it might be a problem with driver not being able to cope with such a high frequency of connections being released/acquired. Can you please try it again, this time with a C3p0ConnectionFactory?

                                  • 14. Re: JDBCCacheLoader problem in java application
                                    gmeroz Newbie

                                    It was Oracle9i!

                                    I checked on Oracle10g to be able to supply information about the version & the test run with an iteration of 5000.

                                    In both cases i used the driver (10.1.0.4.0).

                                    C3p0ConnectionFactory solve it for Oracle9i.

                                    thanks