14 Replies Latest reply on Jun 22, 2003 8:22 AM by pushpendra

    No Managed Connections Available

    pushpendra

      Hi:
      I am using Jboss 3.2.1 with Tomcat.
      I have servlet accessing the BMP Entity Beans. My database server is MySQL and I am using MySQL Connector/J driver. My mysql-ds.xml xml file is as follows:


      <local-tx-datasource>
      <jndi-name>MySQL</jndi-name>
      <connection-url>jdbc:mysql://localhost/AhmedabadSale</connection-url>
      <driver-class>com.mysql.jdbc.Driver</driver-class>
      <user-name>pushpendra_raval</user-name>
      rawal3
      <min-pool-size>50</min-pool-size>
      <max-pool-size>100</max-pool-size>
      <blocking-timeout-millis>10000</blocking-timeout-millis>
      <idle-timeout-minutes>15</idle-timeout-minutes>
      </local-tx-datasource>


      First I was getting No Managed Connections Available and now I am getting following error after I include:

      <min-pool-size>50</min-pool-size>
      <max-pool-size>100</max-pool-size>
      <blocking-timeout-millis>10000</blocking-timeout-millis>
      <idle-timeout-minutes>15</idle-timeout-minutes>

      in mysql-ds.xml file.

      javax.ejb.EJBException: Unable to connect to database. Could not create connection; - nested throwable: (java.sql.SQLException: Unable to connect to any hosts due to exception: java.net.SocketException: No buffer space available (maximum connections reached?): connect); - nested throwable: (org.jboss.resource.JBossResourceException: Could not create connection; - nested throwable: (java.sql.SQLException: Unable to connect to any hosts due to exception: java.net.SocketException: No buffer space available (maximum connections reached?): connect))
      at AutoBean.setEntityContext(Unknown Source)
      at org.jboss.ejb.EntityEnterpriseContext.(EntityEnterpriseContext.java:69)
      at org.jboss.ejb.plugins.EntityInstancePool.create(EntityInstancePool.java:52)
      at org.jboss.ejb.plugins.AbstractInstancePool.get(AbstractInstancePool.java:146)
      at org.jboss.ejb.plugins.EntityInstanceInterceptor.invokeHome(EntityInstanceInterceptor.java:79)
      at org.jboss.ejb.plugins.EntityLockInterceptor.invokeHome(EntityLockInterceptor.java:61)
      at org.jboss.ejb.plugins.EntityCreationInterceptor.invokeHome(EntityCreationInterceptor.java:28)
      at org.jboss.ejb.plugins.AbstractTxInterceptor.invokeNext(AbstractTxInterceptor.java:88)
      at org.jboss.ejb.plugins.TxInterceptorCMT.runWithTransactions(TxInterceptorCMT.java:243)
      at org.jboss.ejb.plugins.TxInterceptorCMT.invokeHome(TxInterceptorCMT.java:74)
      at org.jboss.ejb.plugins.SecurityInterceptor.invokeHome(SecurityInterceptor.java:92)
      at org.jboss.ejb.plugins.LogInterceptor.invokeHome(LogInterceptor.java:120)
      at org.jboss.ejb.plugins.ProxyFactoryFinderInterceptor.invokeHome(ProxyFactoryFinderInterceptor.java:93)
      at org.jboss.ejb.EntityContainer.internalInvokeHome(EntityContainer.java:477)
      at org.jboss.ejb.Container.invoke(Container.java:694)
      at org.jboss.ejb.plugins.local.BaseLocalProxyFactory.invokeHome(BaseLocalProxyFactory.java:272)
      at org.jboss.ejb.plugins.local.LocalHomeProxy.invoke(LocalHomeProxy.java:110)
      at $Proxy28.findByPrimaryKey(Unknown Source)
      at DatabaseView_Autos.getInstanceVariableValues(Unknown Source)
      at AutoSubroutine.executeAutoDisplay(Unknown Source)
      at CategoriesServlet.doPost(Unknown Source)
      at javax.servlet.http.HttpServlet.service(HttpServlet.java:760)
      at javax.servlet.http.HttpServlet.service(HttpServlet.java:853)
      at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:247)
      at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:193)
      at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:256)
      at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:643)
      at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:480)
      at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:995)
      at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:191)
      at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:643)
      at org.apache.catalina.valves.CertificatesValve.invoke(CertificatesValve.java:246)
      at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:641)
      at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:480)
      at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:995)
      at org.apache.catalina.core.StandardContext.invoke(StandardContext.java:2415)
      at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:180)
      at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:643)
      at org.apache.catalina.valves.ErrorDispatcherValve.invoke(ErrorDispatcherValve.java:171)
      at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:641)
      at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:172)
      at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:641)
      at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:509)
      at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:641)
      at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:480)
      at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:995)
      at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:174)
      at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:643)
      at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:480)
      at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:995)
      at org.apache.coyote.tomcat4.CoyoteAdapter.service(CoyoteAdapter.java:223)
      at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:594)
      at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.processConnection(Http11Protocol.java:392)
      at org.apache.tomcat.util.net.TcpWorkerThread.runIt(PoolTcpEndpoint.java:565)
      at org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:619)
      at java.lang.Thread.run(Thread.java:536)


      Can anybody shine light as to why I get the error. When I use same Entity bean twice from two different servlet I get the error. Does it have to do with servlet configuration?

      Or has it to do with CPU running out of memory? I am currently using P 200 MHz with 128MB RAM for Jboss 3.2.1 and MySQL 3.0.

      Regards,

      Pushpendra Raval

        • 1. Re: No Managed Connections Available

          I think you're just forgetting to close some of your DB connections in your BMP code thus exhausting your connection pool. Remember to always close connections in a finally block for your connection acquisition.

          • 2. Re: No Managed Connections Available
            pushpendra

            Hi,

            I have made very sure to close all the connections including ResultSet, PreparedStatement and also Connection object. So I am sure my coding is absolutely right.

            It looks like a bug to me. Because even drivers I am using right and also datasource file is right. So why this problem?

            Regards,

            Pushpendra Raval

            • 3. Re: No Managed Connections Available

              If it is a bug you'd have to demonstrate it with a reproducible test case. A bug is possible but chances are much greater that your code is not absolutely correct.

              • 4. Re: No Managed Connections Available
                pushpendra

                Hi,

                First I have tested my same code on HypersonicSQL DB and I am not getting an error. So ineffect I can say with certaintly there is some problem with MySQL Database server. Now I think the problem has to do with JBOSS Configuration files for MySQL Database server.

                I am also attaching my EJB Code along with this email and you will see the code is absolutely perfect.

                Regards,

                Pushpendra Raval

                • 5. It is a widespread problem of No Managed Connection availabl
                  pushpendra

                  Hi:
                  I have also gone through JCA Forum of jboss and this problem of No Managed Connections available is widespread. I cannot find any leads to solve this problem. Can anybody help? I am stuck with this problem for over a week.

                  Regards,

                  Pushpendra

                  • 6. Re: It is a widespread problem of No Managed Connection avai

                    There's not a single finally block in your code. In case something unexpected happens your connection IS NOT returned to the db pool (and you eventually exhaust the pool).

                    Please fix your code.

                    • 7. Re: It is a widespread problem of No Managed Connection avai

                      In fact, it looks like you're trying to allocate a connection per bean instance. This will not work. You need to retrieve the db connection from the pool when you need it and close() it within the same method invocation to return it back to the pool.

                      • 8. Re: It is a widespread problem of No Managed Connection avai
                        pushpendra

                        Hi:
                        Can you send me an example code to do it. Because I have gone through many BMP Code on the net and have followed that practice.

                        Secondly about finally {} code, Yes You are right I should do it. But that does not solve the error I am getting. I have tested the same code on Hypersonic SQL DB and it works fine. so why not on MySQL Server. I will put finally code and it will still give me same error.

                        I would appreciate if you kindly show me how to make connection and close it not at bean level but at method level.

                        Regards,

                        Pushpendra Raval

                        • 9. Re: It is a widespread problem of No Managed Connection avai
                          jonlee

                          Have a look at the code sample for a stateless session bean method - get the connection, use the connection, release the connection during the method. The same principle applies for BMP.
                          http://www.jboss.org/modules/bb/index.html?module=bb&op=viewtopic&t= the connection for the minimum amount of time you can. Do not allocate the connection to the bean when the bean is created and release it when the bean dies as the bean can have a long life-time and no other EJB can use the connection in the meantime. The design principles here are the same as for processes using shared, scarce resources in a multi-user operating system.

                          • 10. Re: It is a widespread problem of No Managed Connection avai
                            jonlee

                            This is a BMP example straight from ECperf 1.1:
                            public BomEntPK ejbFindByPrimaryKey(BomEntPK pk) throws FinderException {

                            Connection conn = null;
                            PreparedStatement stmt = null;

                            if (debugging)
                            debug.println(3, "ejbFindByPrimaryKey");

                            try {
                            conn = dataSource.getConnection();
                            stmt = conn.prepareStatement(
                            "SELECT * FROM M_bom WHERE b_comp_id = ? AND b_assembly_id = ? AND b_line_no = ?");

                            stmt.setString(1, pk.componentId);
                            stmt.setString(2, pk.assemblyId);
                            stmt.setInt(3, pk.lineNo);

                            ResultSet resultSet = stmt.executeQuery();

                            if (!resultSet.next()) {
                            if (debugging)
                            debug.println(
                            1, "No keys found in M_bom where componentId = "
                            + componentId);

                            throw new FinderException(className
                            + "(ejbFindByPrimaryKey)");
                            } else {
                            return (pk);
                            }
                            } catch (SQLException e) {
                            if (debugging)
                            debug.println(
                            1, "SQLException SELECT * from M_bom where b_comp_id = "
                            + componentId + " : failed");
                            debug.printStackTrace(e);

                            throw new EJBException(e);
                            } finally {
                            Util.closeConnection(conn, stmt);
                            }
                            }

                            • 11. Re: It is a widespread problem of No Managed Connection avai
                              jonlee

                              Hope these examples help.

                              • 12. Code is similar to yours
                                pushpendra

                                Hi,

                                This is my code for ejbFindByPrimaryKey(PrimaryKey pK):

                                /**
                                * method ejbFindByPrimaryKey() validates whether primaryKey exists in database.
                                */
                                public AutoBeanPK ejbFindByPrimaryKey(AutoBeanPK primaryKey) throws FinderException {
                                System.out.println("Inside ejbFindByPrimaryKey method");

                                /* selectByPrimaryKey() method actually implements this method. The if statement
                                * is true then the primaryKey AutoKey is returned or else exception is returned
                                * indicating primaryKey is not found.
                                */
                                try {
                                if (selectByPrimaryKey(primaryKey)) {
                                System.out.println("success selectByPrimaryKey method()");
                                return primaryKey;
                                } else {
                                throw new ObjectNotFoundException("Row for id " + primaryKey + " not found.");
                                }
                                } catch (Exception ex) {
                                throw new EJBException("ejbFindByPrimaryKey: " + ex.getMessage());
                                }

                                }

                                and the SQL Coding is done in: selectByPrimaryKey(primaryKey):

                                private boolean selectByPrimaryKey(AutoBeanPK primaryKey) throws SQLException, NamingException {
                                String selectStatement = "SELECT stamp FROM default WHERE stamp=?";

                                Connection con = null;
                                PreparedStatement prepStmt = null;
                                boolean result = false;
                                ResultSet rs = null;
                                con = makeConnection();
                                prepStmt = con.prepareStatement(selectStatement);
                                System.out.println(primaryKey.getStamp()+"STAMP");
                                prepStmt.setTimestamp(1, primaryKey.getStamp());
                                rs = prepStmt.executeQuery();
                                result = rs.next();
                                rs.close();
                                dbClose(con, prepStmt);
                                return result;
                                }

                                I also have makeConnection method and Connection Close Method in bean:

                                /**
                                * method makeConnection() looks up for database and then connects to database
                                */
                                private Connection makeConnection() throws NamingException, SQLException {
                                InitialContext ic = new InitialContext();
                                /* Looks up for database */
                                DataSource ds = (DataSource) ic.lookup(logicalDBName);
                                /* connects to database */
                                con = ds.getConnection();
                                return con;
                                }

                                private void dbClose(Connection c,PreparedStatement p) throws SQLException{
                                if (p!=null) { p.close(); }
                                if (c!=null) {
                                c.close();
                                }
                                }

                                It looks like my code is similar to yours only difference is I am not using finally clause to make absolutely sure connection closes. But if the code has no error my code should work fine as it works in HypersonicSQL DB. Also I have followed Sun's Specification in coding my bean.

                                Can there be error in: jboss-local-jdbc.rar (Resource Adapter code)? I am planning to look in that code?

                                Regards,

                                Pushpendra Raval

                                • 13. Re: Code is similar to yours

                                  You are allocating connections in setEntityContext and not releasing them properly. This is NOT correct. The bug is in your code.

                                  • 14. FINALLY I DID IT
                                    pushpendra

                                    Hi:
                                    You are absolutely right, I cannot have Connection object as instance variable and it should be created in the method that needs database connection. After I made these changes my APPLICATION IN MYSQL SERVER WORKS.

                                    Thanks for your patience.

                                    Regards,

                                    Pushpendra Raval