4 Replies Latest reply on Feb 16, 2004 5:50 AM by Kapilesh

    Problem with Connection pool in MYSQL

    Kapilesh Newbie

      Hi Adrian
      .I am using JBoss 3.2.1 and Tomcat 4.2.1.24 . Also I am using mysql-connector-java-3.0.8-stable-bin.. After a few hours of load the jboss spits out an error " makeConnection org.jboss.util.NestedSQLException: No ManagedConnections Available!"
      I have checked a lot of times if we are missing out on closing connections.
      I use the following utilility class to create connections. I get the connection object , create a statement, execute the selectFrom Table() which returs me a result set.Use the result set and then close the result set. Close the Statement . I also get a message closing statement Please do your own house Kepping,but infact I am closing resultsets as well as statements and then closing connection in every part of my code

      I am also not sure if this is the problem with my MYSQLConnect class or the MYSQL Connection
      I have also pasted my mysql-ds.xml

      /*********************************************************/

      ****/


      import java.io.*;
      import javax.naming.*;
      import javax.sql.*;
      import java.sql.*;
      import java.util.*;



      public class MYSQLConnect {

      Connection conn = null;
      Context ctx =null;
      int rs_count=0;
      int stmt_count=0;

      public void makeConnection(){
      try{
      ctx = new InitialContext();
      if(ctx == null ){
      System.out.println("Initiall Context Not Set");
      }
      System.out.println("Initial Context Set");
      DataSource ds = (DataSource)ctx.lookup("java:/jdbc/expressionsDS");
      if (ds != null) {
      System.out.println("Got DataSource");
      conn = ds.getConnection();
      if(conn != null) {
      System.out.println("Got Connection");
      }
      else{
      System.out.println("No Connection");
      }
      }
      else{
      System.out.println("No DataSource");
      }
      }
      catch(Exception e){
      System.out.println("Error In MYSQLConnect:makeConnection "+ e.toString());
      }
      }

      public void showCounts(){
      System.out.println("The no od Result set remained open are"+rs_count
      + " and the no of Statements="+stmt_count);
      }

      public void disconnectConnection(){
      try{

      conn.close();
      }
      catch(Exception e){
      System.out.println("Error in MYSQLConnect:disconnectConnection "+ e.toString());
      }

      }

      public void closeResultSet(ResultSet rs){
      try{
      if(rs!=null){
      System.out.println("Closing ResultSet");
      rs.close();
      }

      }
      catch(Exception e){
      System.out.println("Error in MYSQLConnect:disconnectConnection "+ e.toString());
      }

      }




      public void closeStatement(Statement stmt){
      try{
      if(stmt!=null){
      System.out.println("Closing Statement");
      stmt.close();
      }

      }
      catch(Exception e){
      System.out.println("Error in MYSQLConnect:disconnectConnection "+ e.toString());
      }
      }

      public Statement createMYSQLStatement(){
      try {
      return conn.createStatement();
      }
      catch(Exception exp){
      return null;
      }


      }


      public ResultSet selectFromTable(String query,Statement stmt){
      ResultSet rs=null;
      try{
      stmt = conn.createStatement();
      stmt_count++;
      stmt.execute(query);
      rs = stmt.getResultSet();

      }
      catch(Exception e){
      try{
      if(stmt!=null){
      stmt.close();

      }
      }
      catch(Exception exp){
      System.out.println("{Query="+ query+"}");
      System.out.println("Error in MYSQLConnect:selectFromTable Close Statement"+ exp.toString());
      }
      System.out.println("{Query="+ query+"}");
      System.out.println("Error in MYSQLConnect:selectFromTable "+ e.toString());

      }
      return rs;
      }



      public int insertIntoTable(String query){
      Statement stmt_ins=null;

      try{
      stmt_ins = conn.createStatement();
      stmt_ins.execute(query);
      }
      catch(Exception e){
      try{
      System.out.println("Caught Exception in insertIntoTable");
      if(stmt_ins!=null){
      stmt_ins.close();
      }
      }
      catch(Exception exp){
      System.out.println("{Query="+ query+"}");
      System.out.println("Error in MYSQLConnect:insertIntoTable Close Statement"+ exp.toString());
      }
      System.out.println("{Query="+ query+"}");
      System.out.println("Error in MYSQLConnect:insertIntoTable "+ e.toString());
      return 1;
      }
      finally{
      try{
      if(stmt_ins!=null){
      stmt_ins.close();
      }
      }
      catch(Exception exp1){
      System.out.println("Could Not Close Statement after Insert");
      }
      }
      return 0;
      }

      public int deleteFromTable(String query){
      Statement stmt_del=null;
      try{
      stmt_del = conn.createStatement();
      stmt_del.execute(query);
      }
      catch(Exception e){
      try{
      System.out.println("Caught Exception in deleteFromTable");
      if(stmt_del!=null){
      stmt_del.close();
      }
      }
      catch(Exception exp){
      System.out.println("{Query="+ query+"}");
      System.out.println("Error in MYSQLConnect:deleteFromTable Close Statement"+ exp.toString());
      }
      System.out.println("{Query="+ query+"}");
      System.out.println("Error in MYSQLConnect:deleteFromTable "+ e.toString());
      return 1;
      }
      finally{
      try{
      if(stmt_del!=null){
      stmt_del.close();
      }
      }
      catch(Exception exp1){
      System.out.println("Could Not Close stmt after Delete");

      }
      }
      return 0;
      }


      }
      /**************************************************/
      This is my mysql-ds.xml
      /**************************************************/

      <?xml version="1.0" encoding="UTF-8"?>

      <local-tx-datasource>
      <jndi-name>jdbc/expressionsDS</jndi-name>
      <connection-url>jdbc:mysql://localhost:3306/expressions</connection-url>
      <driver-class>org.gjt.mm.mysql.Driver</driver-class>
      <user-name>expressions</user-name>
      expressions
      <min-pool-size>5</min-pool-size>
      <max-pool-size>50</max-pool-size>
      <idle-timeout-millis>100</idle-timeout-millis>
      </local-tx-datasource>




      /****************************************************/

        • 1. Re: Problem with Connection pool in MYSQL
          Adrian Brock Master

          You don't show the usage.
          You should have something like:

          Connection c = ds.getConnection();
          try
          {
          ...
          }
          finally
          {
          c.close();
          }

          There are also some known bugs in 3.2.1 where some ResourceExceptions
          weren't trapped by the pool causing a connection leak.
          Do you see ResourceExceptions on the console?

          Regards,
          Adrian

          • 2. Re: Problem with Connection pool in MYSQL
            Kapilesh Newbie

            Dear Adrian
            Thanks for your help .Yes I get Resource Exception

            Got DataSource
            2004-01-30 13:05:43,312 INFO [org.apache.jk.common.ChannelSocket] connection timeout reached
            2004-01-30 13:05:45,953 INFO [STDOUT] Error In MYSQLConnect:makeConnection org.jboss.util.NestedSQLException: No ManagedConnections Available!; - nested throwable: (javax.resource.ResourceException: No ManagedConnections Available!)

            --------------------------------------------------------
            How could I overcome this them

            Regards
            Kapilesh Arekar

            • 3. Re: Problem with Connection pool in MYSQL
              Oleg Gusakov Newbie

              I am getting the same kind of pool leakage in 3.2.3 on SuSE 9.0 and HP/UX 11.0, connecting to Oracle via either the old classes12.jar or the new ojdbc14.jar driver.

              Our code is standardized to wrap any database access into

              Connection conn = null;
              try {
              conn = ...
              ...
              }
              ...
              finally {
              Util.closeConnection( conn );
              }

              The connection is described as per docs:

              <local-tx-datasource>
              <jndi-name>datasource_nameDS</jndi-name>
              <connection-url>jdbc:oracle:thin:@10.1.x.x:1521:db</connection-url>
              <driver-class>oracle.jdbc.OracleDriver</driver-class>
              <user-name>user</user-name>
              pass
              <min-pool-size>8</min-pool-size>
              <max-pool-size>32</max-pool-size>
              <blocking-timeout-millis>5000</blocking-timeout-millis>
              <idle-timeout-minutes>10</idle-timeout-minutes>
              <valid-connection-checker-class-name>org.jboss.resource.adapter.jdbc.vendor.OracleValidConnectionChecker</valid-connection-checker-class-name>
              <exception-sorter-class-name>org.jboss.resource.adapter.jdbc.vendor.OracleExceptionSorter</exception-sorter-class-name>
              </local-tx-datasource>

              When I check the connection pool MBean for datasource_nameDS - 0 connections available and the Resource manager exceptions on the console.

              That becomes more funny - in two cases we are getting no connection and those console exceptions "No ManagedConnections Available!" and yet MBean reports all 32 connections available.

              I am considering either monitoring the connection pool and restarting it when it fails from a scheduler or exception handler, or using Apache DBCP for the connection pooling.

              Has anyone find a nicer solution or workaround? I'd rather stick to JBoss pool.

              • 4. Re: Problem with Connection pool in MYSQL
                Kapilesh Newbie

                I had a work around though it is not a solution to the problem ,but atleast your connections dont get exhausted.Dont give your idle time out in minutes just give it 3 seconds,So if the connection pool is idle for 3 seconds it will return the connection to the pool. Atleast you done have to restart the server
                <?xml version="1.0" encoding="UTF-8"?>

                <local-tx-datasource>
                <jndi-name>jdbc/expressionsDS</jndi-name>
                <connection-url>jdbc:mysql://localhost:3306/expressions</connection-url>
                <driver-class>org.gjt.mm.mysql.Driver</driver-class>
                <user-name>expressions</user-name>
                expressions
                <min-pool-size>5</min-pool-size>
                <max-pool-size>50</max-pool-size>
                <idle-timeout-millis>3000</idle-timeout-millis>
                </local-tx-datasource>

                Adrian Can you suggest a solution.



                Thanks