14 Replies Latest reply on Oct 12, 2006 3:28 PM by malek256

    Urgent- Socket error when adding object to DB using JDBC Cac

    mitra123

      Hi,
      I am currently trying to test the following scenario:
      Add 10000/20000/50000 objects to cache1 in JVM1, persist the data to DB (MYSQL) and read the data in cache 2 (separate JVM)

      After about adding 1793 objects to DB, I get the following error:

      MESSAGE: java.net.BindException: Address already in use: connect

      STACKTRACE:

      java.net.SocketException: java.net.BindException: Address already in use: connect
      at com.mysql.jdbc.StandardSocketFactory.connect(StandardSocketFactory.java:156)
      at com.mysql.jdbc.MysqlIO.(MysqlIO.java:284)
      at com.mysql.jdbc.Connection.createNewIO(Connection.java:2541)
      at com.mysql.jdbc.Connection.(Connection.java:1474)
      at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:266)
      at java.sql.DriverManager.getConnection(DriverManager.java:525)
      at java.sql.DriverManager.getConnection(DriverManager.java:171)
      at org.jboss.cache.loader.JDBCCacheLoader$NonManagedConnectionFactory.getConnection(JDBCCacheLoader.java:1410)
      at org.jboss.cache.loader.JDBCCacheLoader.loadNode(JDBCCacheLoader.java:1071)
      at org.jboss.cache.loader.JDBCCacheLoader.get(JDBCCacheLoader.java:263)
      at org.jboss.cache.loader.AsyncCacheLoader.get(AsyncCacheLoader.java:171)
      at org.jboss.cache.interceptors.CacheLoaderInterceptor.loadData(CacheLoaderInterceptor.java:415)
      at org.jboss.cache.interceptors.CacheLoaderInterceptor.loadNode(CacheLoaderInterceptor.java:326)
      at org.jboss.cache.interceptors.CacheLoaderInterceptor.invoke(CacheLoaderInterceptor.java:165)
      at org.jboss.cache.interceptors.Interceptor.invoke(Interceptor.java:68)
      at org.jboss.cache.interceptors.UnlockInterceptor.invoke(UnlockInterceptor.java:32)
      at org.jboss.cache.interceptors.Interceptor.invoke(Interceptor.java:68)
      at org.jboss.cache.interceptors.ReplicationInterceptor.invoke(ReplicationInterceptor.java:34)
      at org.jboss.cache.interceptors.Interceptor.invoke(Interceptor.java:68)
      at org.jboss.cache.interceptors.CacheStoreInterceptor.invoke(CacheStoreInterceptor.java:135)
      at org.jboss.cache.interceptors.Interceptor.invoke(Interceptor.java:68)
      at org.jboss.cache.interceptors.TxInterceptor.handleNonTxMethod(TxInterceptor.java:345)
      at org.jboss.cache.interceptors.TxInterceptor.invoke(TxInterceptor.java:156)
      at org.jboss.cache.interceptors.Interceptor.invoke(Interceptor.java:68)
      at org.jboss.cache.interceptors.CacheMgmtInterceptor.invoke(CacheMgmtInterceptor.java:138)
      at org.jboss.cache.TreeCache.invokeMethod(TreeCache.java:5517)
      at org.jboss.cache.TreeCache.get(TreeCache.java:3468)
      at org.jboss.cache.TreeCache.get(TreeCache.java:3449)
      at org.jboss.cache.TreeCache.get(TreeCache.java:3245)
      at org.jboss.cache.aop.InternalDelegate.get(InternalDelegate.java:123)
      at org.jboss.cache.aop.InternalDelegate.getAopInstance(InternalDelegate.java:71)
      at org.jboss.cache.aop.InternalDelegate.getPojo(InternalDelegate.java:221)
      at org.jboss.cache.aop.TreeCacheAopDelegate._putObject(TreeCacheAopDelegate.java:171)
      at org.jboss.cache.aop.PojoCache._putObject(PojoCache.java:731)
      at org.jboss.cache.aop.PojoCache.putObject(PojoCache.java:462)
      at org.jboss.cache.aop.PojoCache.putObject(PojoCache.java:423)
      at test.examples.StudentMaintTest.testObjectPutInCache(StudentMaintTest.java:142)
      at test.examples.StudentMaintTest.init(StudentMaintTest.java:123)
      at test.examples.StudentMaintTest.setUp(StudentMaintTest.java:58)
      at test.examples.StudentMaintTest.main(StudentMaintTest.java:252)

      I have configured JDBBC cacheloader in the xml file as follows:


      <!-- if passivation is true, only the first cache loader is used; the rest are ignored -->
      false

      /aop

      true

      <!-- we can now have multiple cache loaders, which get chained -->

      org.jboss.cache.loader.JDBCCacheLoader
      <!-- same as the old CacheLoaderConfig attribute -->

      cache.jdbc.table.name=jbosscache
      cache.jdbc.table.create=true
      cache.jdbc.table.drop=false
      cache.jdbc.table.primarykey=jbosscache_pk
      cache.jdbc.fqn.column=fqn
      cache.jdbc.fqn.type=varchar(255)
      cache.jdbc.node.column=node
      cache.jdbc.node.type=longblob
      cache.jdbc.parent.column=parent
      cache.jdbc.driver=com.mysql.jdbc.Driver
      cache.jdbc.url=jdbc:mysql://localhost:3306/test
      cache.jdbc.user=root
      cache.jdbc.password=secretpassword

      <!-- whether the cache loader writes are asynchronous -->
      true
      <!-- only one cache loader in the chain may set fetchPersistentState to true.
      An exception is thrown if more than one cache loader sets this to true. -->
      false
      <!-- determines whether this cache loader ignores writes - defaults to false. -->
      false
      <!-- if set to true, purges the contents of this cache loader when the cache starts up.
      Defaults to false. -->
      false

      Could you please help me out and point me in the right direction?

        • 1. Re: Urgent- Socket error when adding object to DB using JDBC
          mitra123

          By the way I am running on WinXP professional edition with SP2

          • 2. Re: Urgent- Socket error when adding object to DB using JDBC
            manik

            This is possibly due to the fact that you run out of DB connections. Are you running JBC within an app server? I'd recommend using a pool of connections.

            • 3. Re: Urgent- Socket error when adding object to DB using JDBC
              mitra123

              Hi,
              Thanks for the response, I wanted to make sure I am not doing something wrong.
              And no we don't have an app server, and I was just using the JDBCCacheLoader API connecting to MYSQL via JDBC.
              For the sake of the testing I will try to write a piece of code to use connection pools but I suppose in the long run we need to use an app server to do the pooling for us.

              Thanks again.

              • 4. Re: Urgent- Socket error when adding object to DB using JDBC
                malek256

                After running a large number of tests, all of which repeatably and consistently fail in and around approximately 1970-1990 cache updates, I have made the following change.

                After this change, the code then fails between 3900-4000 updates. It appears that this is a minor defect or at least a suboptimization with the original connection code.

                The connection is stored into TLS during the NonManagedConnectionFactory.prepare() method but it seems it is not put in place after the null detect/create code within NonManagedConnectionFactory.getConnection.

                I urge you to examine this.

                e.g.
                ...
                public Connection getConnection()
                {
                Connection con = (Connection) connection.get();
                if(con == null)
                {
                try
                {
                con = DriverManager.getConnection(url, usr, pwd);
                // CODE ADDED HERE
                connection.set(con);
                // END CODE MODIFICATION
                }
                catch(SQLException e)
                ...

                • 5. Re: Urgent- Socket error when adding object to DB using JDBC
                  manik
                  • 6. Re: Urgent- Socket error when adding object to DB using JDBC
                    malek256

                    Adding the following additional change allowed for 10,000 consecutive updates without error.

                    public NonManagedConnectionFactory(String url, String usr, String pwd)
                    {
                    this.url = url;
                    this.usr = usr;
                    this.pwd = pwd;

                    // what if I do a connection right off?
                    // BEGIN MODIFICATION: GJF - October 10, 2006
                    Connection con = getConnection();
                    try {
                    con.setAutoCommit(false);
                    } catch( SQLException e ){
                    // don't care
                    }
                    // END MODIFICATION
                    }

                    • 7. Re: Urgent- Socket error when adding object to DB using JDBC
                      manik

                      What does this change actually do? It just seems to create a new connection and store it in threadlocal when the cacheloader is constructed, and that's it.

                      So all it does is store a conn for a particular thread (the one that starts the cache). Other threads will still have to call getConnection() each time with a prepare().

                      Am I missing something, how does this result in being able to deal with capacity better?

                      • 8. Re: Urgent- Socket error when adding object to DB using JDBC
                        malek256

                         

                        "manik.surtani@jboss.com" wrote:
                        What does this change actually do? It just seems to create a new connection and store it in threadlocal when the cacheloader is constructed, and that's it.

                        So all it does is store a conn for a particular thread (the one that starts the cache). Other threads will still have to call getConnection() each time with a prepare().

                        Am I missing something, how does this result in being able to deal with capacity better?


                        Correct, note that it also enforces setting autocommit off.

                        I am only fleetingly familiar with this code base - however I cannot deny the evidence of the tests.

                        I can (now) successfully run up to 50,000 stores without issue. This does in fact seem to address the issue.

                        Perhaps you can identify the full reason as to why.

                        • 9. Re: Urgent- Socket error when adding object to DB using JDBC
                          manik

                          Ah, here's a reason why - are you running your test within a transaction?

                          Non-tx-related calls call cf.close(), which results in:

                           public void close(Connection con)
                           {
                           if(con != null && con != connection.get())
                           {
                           try
                           {
                           con.close();
                           if(log.isTraceEnabled())
                           {
                           //log.trace("closed non tx connection: " + con);
                           }
                           }
                           catch(SQLException e)
                           {
                           log.warn("Failed to close connection: " + e.getMessage());
                           }
                           }
                           }
                          
                          


                          So the connection is only closed if it is NOT the one in ThreadLocal, otherwise it is kept open.

                          If you set the connection in ThreadLocal (and even in getConnection() for that matter, rather than prepare()) these connections will not get closed and you have some sort of a connection pool going, based on the number of processor threads. I presume this could exhaust your DB backend if these are high.

                          Could you confirm whether you're using txs in your test?

                          • 10. Re: Urgent- Socket error when adding object to DB using JDBC
                            malek256

                            We are not using transactions.

                            Keeping the connections open and reusable was the intent; our information obtained earlier was that this scenario could not run at all and we needed to use a connection pool - however that does not suit our purpose as it would require significant alteration of the existing code OR use of a JNDI interface plus app server which is 100% against the scope of what we are trying to achieve.

                            • 11. Re: Urgent- Socket error when adding object to DB using JDBC
                              manik

                              Fair enough. The problem here though is that when using transactions that span multiple calls, this breaks things.

                              A proper solution would be to use a rudimentary internal pool (HashMap of open conns) for the non-managed factory.

                              • 12. Re: Urgent- Socket error when adding object to DB using JDBC
                                malek256

                                Agreed - however we are doing limited, time-bound feasibility studies of many competing combinatorials in order to see if we should make a recommendation for a specific direction. If this is the direction we take, a connection pool is mandatory - but likely would be the job of the container / app server we select.

                                Thanks much for your assistance.

                                • 13. Re: Urgent- Socket error when adding object to DB using JDBC
                                  manik

                                  Even if you don't use a full app server, there are several connection pooling libraries you could use:

                                  http://www.primrose.org.uk/home.jsp
                                  http://sourceforge.net/projects/c3p0
                                  http://jakarta.apache.org/commons/dbcp/

                                  • 14. Re: Urgent- Socket error when adding object to DB using JDBC
                                    malek256

                                    Thank you very much for the links!