# java.net.SocketException: Broken pipe



## DaRolla (11. Aug 2006)

Lieber Leser,

ich habe ein Problem mit meiner MySQL Datenbank und der JDBC Bridge. Ich habe für meine Webapplication einen eigenen ConnectionPool geschrieben, der eigentlich auch funktionieren sollte. Wenn aber Tomcat 8 Stunden läuft und keiner das Servlet aufruft, geht irgendwie die Connection kaputt. Bevor ich die Connection aus dem Pool gebe überprüfe ich sie folgender massen:


```
/**
* Ist die Connection valide ?
* @param con <code>Connection</code> eine Connection zur Datenbank.
* @return <code>boolean</code> - true wenn die Connection valide ist.
*/
private synchronized boolean isValid( Connection con ) {

   try {
      if( con == null ) {
         logger.warn( "Connection ist NULL." );
         return( false );
      }
      
      if( con.isClosed() ) {
         logger.warn( "Connection ist geschlossen." );
         return( false );            
      }
               
      con.getMetaData();
      return( true );
   }
   catch( Exception ignored ) {         
   }         
   
   logger.warn( "Connection ist nicht valide." );      
   return( false );   
}
```

Leider wird die Connection nicht als "nicht valide" erkannt. Vielleicht bin ich aber auch auf dem Holzweg. Wer kann mir helfen?

Liebe Grüße
DaRolla


```
javax.servlet.ServletException: Communications link failure due to underlying exception: 

** BEGIN NESTED EXCEPTION ** 

java.net.SocketException
MESSAGE: Broken pipe

STACKTRACE:

java.net.SocketException: Broken pipe
   at java.net.SocketOutputStream.socketWrite0(Native Method)
   at java.net.SocketOutputStream.socketWrite(SocketOutputStream.java:92)
   at java.net.SocketOutputStream.write(SocketOutputStream.java:136)
   at java.io.BufferedOutputStream.flushBuffer(BufferedOutputStream.java:65)
   at java.io.BufferedOutputStream.flush(BufferedOutputStream.java:123)
   at com.mysql.jdbc.MysqlIO.send(MysqlIO.java:2739)
   at com.mysql.jdbc.MysqlIO.send(MysqlIO.java:2650)
   at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1581)
   at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1695)
   at com.mysql.jdbc.Connection.execSQL(Connection.java:3004)
   at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1128)
   at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1222)
```


----------



## Caffè Latte (11. Aug 2006)

Hi,

ist die Verbindung längere Zeit untätig (das ist doch die Übersetzung von idle?)? Dann lies mal diesen Thread:

http://forums.mysql.com/read.php?39,42763,42763


----------



## DaRolla (11. Aug 2006)

Hallo und Danke für Deine Antwort.

Ich habe schon ne Menge gegoogelt und gelesen bevor ich diesen Post verfasst habe. Die Lösung in den meisten Fällen scheint ein AutoReconnect=true im Driver zu sein. Das benutze ich schon und dennoch taucht das Problem auf.



```
/**
* Es wird eine neue Connection aufgebaut.
* @throws JdbcException bei einem Fehler mit der Datenbank. 
*/
private synchronized Connection createConnection() throws JdbcException {
        
  try {
    String     url = "jdbc:mysql://" + getHost() + "/" + getName() + "?autoReconnect=true";            
    Connection con = DriverManager.getConnection( url, getUser(), getPassword() );

    created++;              
                
    return( con );
  } 
  catch( SQLException e ) {
    throw new JdbcException( e );
  }
}
```



```
/**
* Es wird eine Connection der ConnectionList entnommen und zurueckgegeben.
* @return <code>Connection</code> - die Verbindung zur Datenbank.
* @throws JdbcException bei einem Fehler mit der Datenbank. 
*/
public synchronized Connection leaseConnection() throws JdbcException {
        
  // Variabeln
  Connection con = null;


  // neue Connection erzeugen wenn Pool leer laeuft
  if( pool.isEmpty() ) {                  
    poolConnection( createConnection() );
  }

  // Connection der Liste von unten entnehmen
  con = pool.getConnection( 0 );
  pool.removeConnection( 0 );                    
        
  leased++;              
        
  // evtl neue Connection erzeugen.
  if( ! isValid( con ) ) {
    con = createConnection();
  }
        
  return( con );
}
```


Liebe Grüße
DaRolla


----------



## DaRolla (11. Aug 2006)

Vielleicht habe ich ja auch einen Denkfehler in der Philosophie des Pools.

Ich habe das ganze so verstanden:

- bei einer Webapplikation mit Datenbank dauert eine es lange eine Connection zu erzeugen
- deswegen werden am Anfang ein paar Connections geöffnet und im Pool gehalten
- fordert nun eine Methode eine Connection an (lease) wird diese aus dem Pool bereitgestellt
- am Ende der Methode kommt die Connection falls sie noch gültig ist wieder in den Pool (release)
- zerstörte Connections werden entsorgt und dafür neue erzeugt und bereitgestellt

Jetzt kann es ja passieren dass die Webapplikation 8 Stunden (ein voreingestellter Wert der MySql Konfiguration) nichts tut (idle). Aus resourcen-schonender Sicht werden dann diese Idle Connections geschlossen von Seiten des JDBC. Der Connection Pool hält in dem Fall geschlossene Connections. Deswegen überprüfe ich in dem leaseConnection() ob diese noch valide ist (isValid). Leider greift mein isValid() Mechanismus nicht, denn die geschlossenen Connections sind weder .closed() noch NULL noch schmeissen sie eine Exception wenn ich die Metadaten anfordere.

autoReconnect=true ist dafür da, dass eine Connection, falls sie vom JDBC geschlossen wurde, neu erzeugt werden kann. Im Grunde genommen nicht so wichtig für mich, da ich diese Überprüfung mit isValid() ja selber mache.

Wer kann mir bei meinem Denkfehler helfen?

Liebe Grüße
DaRolla


----------



## OPERATOR76 (31. Dez 2007)

Hi,

ich habe das selbe Problem und auch einen Pool im Einsatz. Hab schon viel probiert und bis dato keine Lösung gefunden, auch das autoreconnect hilft nichts. Bei mir ist es auch sehr komsich, bis vor kurzen hatte ich einmal pro Tag das Problem und seit zwei Tagen tritt das Problem ständig auf. Es wurde am MySQL und an meinem Java-Programm sowie der VM nichts verändert. 

Aber vielleicht bist Du schon weiter gekommen?

lg

Alex


----------



## maki (31. Dez 2007)

Wirf mal die JDBC-ODBC Bridge raus.


----------



## OPERATOR76 (31. Dez 2007)

Wie mach ich das auf einem Linux Rechner der Local auf den mysql zugreift. Dachte ODBC ist eine reine Windows Geschichte. 

Hier meine Connect line:

jdbc:mysql://localhost/database?connectTimeout=2000&socketTimeout=2000&autoReconnect=true&maxReconnects=10000

vielleicht liegt auch hier der Fehler, nur versteh ich nicht warum das erst seit zwei Tagen extrem auftritt. Ich mach derzeit im Pool ein ,,Select 1" bevor ich die Connection zurück gib und wenn sie tot ist wird sie gekillt und eine neue aufgemacht. Nur kann das nicht die Lösung meines Problems sein. 

Es muss irgendwie möglich sein eine stabile Verbindung zwischen Java und der MySQL Datenbank zu bekommen. Hier noch ein paar Infos zu den Versionen die ich verwende:

- Debian
- BEA JRockit 1.5.0-b64
- JDBC von der alten 3.x bis zur neuen 5.x alles Probiert
- MySQL 5.0.32-Debian_7etch1

Vielleicht gibt‘s da noch eine Idee zu der ganzen Sache. Ich hatte diese Fehler auch als MySQL auf einer extra Maschine lief.

lg & ein frohes neues Jahr


----------



## maki (31. Dez 2007)

> Wie mach ich das auf einem Linux Rechner der Local auf den mysql zugreift. Dachte ODBC ist eine reine Windows Geschichte.


Der Themenstarter nutzt die JDBC-ODBC Bridge.

Zeig mal deinen Pool.


----------



## OPERATOR76 (31. Dez 2007)

```
/**
 * DbConnectionBroker.
 * @version 1.0.13 3/12/02
 * @author Marc A. Mnich
 * @modifed Alexander Biringer 2002-2007
 */
package com.javaexchange.dbConnectionBroker;

import java.io.*;
import java.net.ConnectException;
import java.sql.*;
import java.text.SimpleDateFormat;
import java.util.Date;

/**
 * DbConnectionBroker
 * A servlet-based broker for database connections.
 * Creates and manages a pool of database connections.
 * @version 1.0.13 3/12/02
 * @author Marc A. Mnich
 */
public class DbConnectionBroker implements Runnable {
    private Thread runner;

    private Connection[] connPool;
    private int[] connStatus;

    private long[] connLockTime, connCreateDate;
    private String[] connID;
    private String dbDriver, dbServer, dbLogin, dbPassword, logFileString;
    private int currConnections, connLast, minConns, maxConns, maxConnMSec,
	maxCheckoutSeconds, debugLevel;

    //available: set to false on destroy, checked by getConnection()
    private boolean available=true;

    private PrintWriter log;
    private SQLWarning currSQLWarning;
    private String pid;

    private final int DEFAULTMAXCHECKOUTSECONDS=60;
    private final int DEFAULTDEBUGLEVEL=2;

    /**
     * Creates a new Connection Broker

     * dbDriver:        JDBC driver. e.g. 'oracle.jdbc.driver.OracleDriver'

     * dbServer:        JDBC connect string. e.g. 'jdbc:oracle:thin:@203.92.21.109:1526:orcl'

     * dbLogin:         Database login name.  e.g. 'Scott'

     * dbPassword:      Database password.    e.g. 'Tiger'

     * minConns:        Minimum number of connections to start with.

     * maxConns:        Maximum number of connections in dynamic pool.

     * logFileString:   Absolute path name for log file. e.g. 'c:/temp/mylog.log' 

     * maxConnTime:     Time in days between connection resets. (Reset does a basic cleanup)

     * logAppend:       Append to logfile (optional)

     * maxCheckoutSeconds:       Max time a connection can be checked out before being recycled. Zero value turns option off, default is 60 seconds.
     * debugLevel:      Level of debug messages output to the log file.  0 -> no messages, 1 -> Errors, 2 -> Warnings, 3 -> Information
     */
    public DbConnectionBroker(String dbDriver, String dbServer, String dbLogin,
        String dbPassword, int minConns, int maxConns,
            String logFileString, double maxConnTime) throws IOException {

	setupBroker(dbDriver, dbServer, dbLogin, dbPassword, minConns,
		    maxConns, logFileString, maxConnTime, false,
		    DEFAULTMAXCHECKOUTSECONDS, DEFAULTDEBUGLEVEL);
    }

    /*
     * Special constructor to handle logfile append
     */
    public DbConnectionBroker(String dbDriver, String dbServer, String dbLogin,
        String dbPassword, int minConns, int maxConns,
            String logFileString, double maxConnTime, boolean logAppend)
	throws IOException {

	setupBroker(dbDriver, dbServer, dbLogin, dbPassword, minConns,
		    maxConns, logFileString, maxConnTime, logAppend,
		    DEFAULTMAXCHECKOUTSECONDS, DEFAULTDEBUGLEVEL);
    }

    /*
     * Special constructor to handle connection checkout expiration
     */
    public DbConnectionBroker(String dbDriver, String dbServer, String dbLogin,
        String dbPassword, int minConns, int maxConns,
            String logFileString, double maxConnTime, boolean logAppend,
			      int maxCheckoutSeconds, int debugLevel)
	throws IOException {

	setupBroker(dbDriver, dbServer, dbLogin, dbPassword, minConns,
		    maxConns, logFileString, maxConnTime, logAppend,
		    maxCheckoutSeconds, debugLevel);
    }



    private void setupBroker(String dbDriver, String dbServer, String dbLogin,
        String dbPassword, int minConns, int maxConns,
            String logFileString, double maxConnTime, boolean logAppend,
			     int maxCheckoutSeconds, int debugLevel)
	throws IOException {

        connPool = new Connection[maxConns];
        connStatus = new int[maxConns];
        connLockTime = new long[maxConns];
        connCreateDate = new long[maxConns];
        connID = new String[maxConns];
        currConnections = minConns;
        this.maxConns = maxConns;
        this.dbDriver = dbDriver;
        this.dbServer = dbServer;
        this.dbLogin = dbLogin;
        this.dbPassword = dbPassword;
        this.logFileString = logFileString;
	this.maxCheckoutSeconds = maxCheckoutSeconds;
	this.debugLevel = debugLevel;
        maxConnMSec = (int)(maxConnTime * 86400000.0);  //86400 sec/day
        if(maxConnMSec < 30000) {  // Recycle no less than 30 seconds.
            maxConnMSec = 30000;
        }


        try {
	    log = new PrintWriter(new FileOutputStream(logFileString,
						       logAppend),true);

	    // Can't open the requested file. Open the default file.
        } catch (IOException e1) {
	    try {
		log = new PrintWriter(new FileOutputStream("DCB_" +
					   System.currentTimeMillis() + ".log",
					   logAppend),true);

	    } catch (IOException e2) {
		throw new IOException("Can't open any log file");
	    }
        }



	// Write the pid file (used to clean up dead/broken connection)
	SimpleDateFormat formatter
	    = new SimpleDateFormat ("yyyy.MM.dd G 'at' hh:mm:ss a zzz");
	Date nowc = new Date();
	pid = formatter.format(nowc);

	BufferedWriter pidout = new BufferedWriter(new
	    FileWriter(logFileString + "pid"));
	pidout.write(pid);
	pidout.close();

        log.println("-----------------------------------------");
        log.println("-----------------------------------------");
        log.println("Starting DbConnectionBroker Version 1.0.13:");
        log.println("dbDriver = " + dbDriver);
        log.println("dbServer = " + dbServer);
        log.println("dbLogin = " + dbLogin);
        log.println("log file = " + logFileString);
        log.println("minconnections = " + minConns);
        log.println("maxconnections = " + maxConns);
        log.println("Total refresh interval = " + maxConnTime + " days");
        log.println("logAppend = " + logAppend);
        log.println("maxCheckoutSeconds = " + maxCheckoutSeconds);
	log.println("debugLevel = " + debugLevel);
        log.println("-----------------------------------------");


        // Initialize the pool of connections with the mininum connections:
        // Problems creating connections may be caused during reboot when the
        //    servlet is started before the database is ready.  Handle this
        //    by waiting and trying again.  The loop allows 5 minutes for
        //    db reboot.
        boolean connectionsSucceeded=false;
        int dbLoop=20;

        try {
            for(int i=1; i < dbLoop; i++) {
                try {
                    for(int j=0; j < currConnections; j++) {
                        createConn(j);
                    }
                    connectionsSucceeded=true;
                    break;
                } catch (SQLException e){
		    if(debugLevel > 0) {
			log.println("--->Attempt (" + String.valueOf(i) +
				    " of " + String.valueOf(dbLoop) +
				    ") failed to create new connections set at startup: ");
			log.println("    " + e);
			log.println("    Will try again in 15 seconds...");
		    }
		    try { Thread.sleep(15000); }
                    catch(InterruptedException e1) {}
                }
            }
            if(!connectionsSucceeded) { // All attempts at connecting to db exhausted
		if(debugLevel > 0) {
		    log.println("\r\nAll attempts at connecting to Database exhausted");
		}
                throw new IOException();
            }
        } catch (Exception e) {
            throw new IOException();
        }

        // Fire up the background housekeeping thread

        runner = new Thread(this);
        runner.setName("Connection Pool");
        runner.setDaemon(true);
        runner.start();

    }//End DbConnectionBroker()


    /**
     * Housekeeping thread.  Runs in the background with low CPU overhead.
     * Connections are checked for warnings and closure and are periodically
     * restarted.
     * This thread is a catchall for corrupted
     * connections and prevents the buildup of open cursors. (Open cursors
     * result when the application fails to close a Statement).
     * This method acts as fault tolerance for bad connection/statement programming.
     */
    public void run() {
        boolean forever = true;
        Statement stmt=null;
        String currCatalog=null;
	long maxCheckoutMillis = maxCheckoutSeconds * 1000;


        while(forever) {

	    // Make sure the log file is the one this instance opened
	    // If not, clean it up!
	    try {
		BufferedReader in = new BufferedReader(new
				       FileReader(logFileString + "pid"));
		String curr_pid = in.readLine();
		if(curr_pid.equals(pid)) {
		    //log.println("They match = " + curr_pid);
		} else {
		    //log.println("No match = " + curr_pid);
		    log.close();

		    // Close all connections silently - they are definitely dead.
		    for(int i=0; i < currConnections; i++) {
			try {
			    connPool[i].close();
			} catch (SQLException e1) {} // ignore
		    }
		    // Returning from the run() method kills the thread
		    return;
		}

		in.close();

	    } catch (IOException e1) {
		log.println("Can't read the file for pid info: " +
			    logFileString + "pid");
	    }


	    // Get any Warnings on connections and print to event file
	    for(int i=0; i < currConnections; i++) {
		try {
		    currSQLWarning = connPool[i].getWarnings();
		    if(currSQLWarning != null) {
			if(debugLevel > 1) {
			    log.println("Warnings on connection " +
					String.valueOf(i) + " " + currSQLWarning);
			}
			connPool[i].clearWarnings();
		    }
		} catch(SQLException e) {
		    if(debugLevel > 1) {
			log.println("Cannot access Warnings: " + e);
		    }
		}

	    }

	    for(int i=0; i < currConnections; i++) { // Do for each connection
		long age = System.currentTimeMillis() - connCreateDate[i];


		try {  // Test the connection with createStatement call
		    synchronized(connStatus) {
			if(connStatus[i] > 0) { // In use, catch it next time!

			    // Check the time it's been checked out and recycle
			    long timeInUse = System.currentTimeMillis() -
				connLockTime[i];
			    if(debugLevel > 2) {
				log.println("Warning.  Connection " + i +
					    " in use for " + timeInUse +
					    " ms");
			    }
			    if(maxCheckoutMillis != 0) {
				if(timeInUse > maxCheckoutMillis) {
				    if(debugLevel > 1) {
					log.println("Warning. Connection " +
						    i + " failed to be returned in time.  Recycling...");
				    }
				    throw new SQLException();
				}
			    }

			    continue;
			}
			connStatus[i] = 2; // Take offline (2 indicates housekeeping lock)
		    }


		    if(age > maxConnMSec) {  // Force a reset at the max conn time
			throw new SQLException();
		    }

		    stmt = connPool[i].createStatement();
		    connStatus[i] = 0;  // Connection is O.K.
		    //log.println("Connection confirmed for conn = " +
		    //             String.valueOf(i));

		    // Some DBs return an object even if DB is shut down
		    if(connPool[i].isClosed()) {
			throw new SQLException();
		    }


		    // Connection has a problem, restart it
		} catch(SQLException e) {

		    if(debugLevel > 1) {
			log.println(new Date().toString() +
				    " ***** Recycling connection " +
				    String.valueOf(i) + ":"+e);
		    }

		    try {
			connPool[i].close();
		    } catch(SQLException e0) {
			if(debugLevel > 0) {
			    log.println("Error!  Can't close connection!  Might have been closed already.  Trying to recycle anyway... (" + e0 + ")");
			}
		    }

		    try {
				createConn(i);
		    }catch(Exception e1) {
			if(debugLevel > 0) {
			    log.println(new Date().toString()+"Failed to create connection: " + getStackTrace(e1));
			}
			connStatus[i] = 0;  // Can't open, try again next time
		    }
		} finally {
		    try{if(stmt != null) {stmt.close();}} catch(SQLException e1){};
		}

	    }

	    try { Thread.sleep(20000); }  // Wait 20 seconds for next cycle

	    catch(InterruptedException e) {
		// Returning from the run method sets the internal
		// flag referenced by Thread.isAlive() to false.
		// This is required because we don't use stop() to
		// shutdown this thread.
		return;
	    }

        }

    } // End run

    /**
     * This method hands out the connections in round-robin order.
     * This prevents a faulty connection from locking
     * up an application entirely.  A browser 'refresh' will
     * get the next connection while the faulty
     * connection is cleaned up by the housekeeping thread.
     *
     * If the min number of threads are ever exhausted, new
     * threads are added up the the max thread count.
     * Finally, if all threads are in use, this method waits
     * 2 seconds and tries again, up to ten times.  After that, it
     * returns a null.
     */
    public Connection getConnection() {

        Connection conn=null;

        if(available){
            boolean gotOne = false;

            for(int outerloop=1; outerloop<=10; outerloop++) {

                try  {
                    int loop=0;
                    int roundRobin = connLast + 1;
                    if(roundRobin >= currConnections) roundRobin=0;

                    do {
                         synchronized(connStatus) {
                            if((connStatus[roundRobin] < 1) && (! connPool[roundRobin].isClosed())) {
                                    conn = connPool[roundRobin];
                                    connStatus[roundRobin]=1;
                                    connLockTime[roundRobin] = System.currentTimeMillis();
                                    connLast = roundRobin;
                                    gotOne = true;
                                    break;
                            } else {
                                loop++;
                                roundRobin++;
                                if(roundRobin >= currConnections) roundRobin=0;
                            }
                         }
                    }
                    while((gotOne==false)&&(loop < currConnections));

                }
                catch (SQLException e1) {
		    log.println("Error: " + getStackTrace(e1));
		}

                if(gotOne) {
                    break;
                } else {
                    synchronized(this) {  // Add new connections to the pool
                        if(currConnections < maxConns) {

                            try {
                            	try{
                                	createConn(currConnections);
                            	}catch (ConnectException e1){
									if(debugLevel > 0) {
										log.println("Error: Unable to create new connection refused: " + getStackTrace(e1));
									}                          		
                            	}
				currConnections++;
                            } catch(SQLException e) {
				if(debugLevel > 0) {
				    log.println("Error: Unable to create new connection: " + getStackTrace(e));
				}
                            }
                        }
                    }

                    try { Thread.sleep(2000); }
                    catch(InterruptedException e) {}
		    if(debugLevel > 0) {
			log.println("-----> Connections Exhausted!  Will wait and try again in loop " +
				    String.valueOf(outerloop));
		    }
                }

            } // End of try 10 times loop

        } else {
	    if(debugLevel > 0) {
		log.println("Unsuccessful getConnection() request during destroy()");
	    }
        } // End if(available)

	if(debugLevel > 2) {
	    log.println("Handing out connection " +
			idOfConnection(conn) + " --> " +
			(new SimpleDateFormat("MM/dd/yyyy  hh:mm:ss a")).format(new java.util.Date()));
	}

        return conn;

    }

    /**
     * Returns the local JDBC ID for a connection.
     */
    public int idOfConnection(Connection conn) {
        int match;
        String tag;

        try {
            tag = conn.toString();
        }
        catch (NullPointerException e1) {
            tag = "none";
        }

        match=-1;

        for(int i=0; i< currConnections; i++) {
            if(connID[i].equals(tag)) {
                match = i;
                break;
            }
        }
        return match;
    }

    /**
     * Frees a connection.  Replaces connection back into the main pool for
     * reuse.
     */
    public String freeConnection(Connection conn) {
        String res="";

        int thisconn = idOfConnection(conn);
        if(thisconn >= 0) {
            connStatus[thisconn]=0;
            res = "freed " + conn.toString();
            //log.println("Freed connection " + String.valueOf(thisconn) +
            //            " normal exit: ");
        } else {
	    if(debugLevel > 0) {
		log.println("----> Error: Could not free connection!!!");
	    }
        }

        return res;

    }

    /**
     * Returns the age of a connection -- the time since it was handed out to
     * an application.
     */
    public long getAge(Connection conn) { // Returns the age of the connection in millisec.
        int thisconn = idOfConnection(conn);
        return System.currentTimeMillis() - connLockTime[thisconn];
    }

    private void createConn(int i)

        throws SQLException,ConnectException  {

        Date now = new Date();

        try {
            Class.forName (dbDriver);
            connPool[i] = DriverManager.getConnection(dbServer,dbLogin,dbPassword);
            connStatus[i]=0;
            connID[i]=connPool[i].toString();
            connLockTime[i]=0;
            connCreateDate[i] =  now.getTime();
        } catch (ClassNotFoundException e2) {
	    if(debugLevel > 0) {
		log.println("Error creating connection: " + getStackTrace(e2));
	    }
	}

        log.println(now.toString() + "  Opening connection " + String.valueOf(i) +
                    " " + connPool[i].toString() + ":");
    }

    /**
     * Shuts down the housekeeping thread and closes all connections
     * in the pool. Call this method from the destroy() method of the servlet.
     */

    /**
     * Multi-phase shutdown.  having following sequence:
     * [list=1]
     * <LI><code>getConnection()</code> will refuse to return connections.
     * <LI>The housekeeping thread is shut down.

     *    Up to the time of <code>millis</code> milliseconds after shutdown of
     *    the housekeeping thread, <code>freeConnection()</code> can still be
     *    called to return used connections.
     * <LI>After <code>millis</code> milliseconds after the shutdown of the
     *    housekeeping thread, all connections in the pool are closed.
     * <LI>If any connections were in use while being closed then a
     *    <code>SQLException</code> is thrown.
     * <LI>The log is closed.
     * [/list]

     * Call this method from a servlet destroy() method.
     *
     * @param      millis   the time to wait in milliseconds.
     * @exception  SQLException if connections were in use after
     * <code>millis</code>.
     */
    public void destroy(int millis) throws SQLException {

        // Checking for invalid negative arguments is not necessary,
        // Thread.join() does this already in runner.join().

        // Stop issuing connections
        available=false;

        // Shut down the background housekeeping thread
        runner.interrupt();

        // Wait until the housekeeping thread has died.
        try { runner.join(millis); }
        catch(InterruptedException e){} // ignore

        // The housekeeping thread could still be running
        // (e.g. if millis is too small). This case is ignored.
        // At worst, this method will throw an exception with the
	// clear indication that the timeout was too short.

        long startTime=System.currentTimeMillis();

        // Wait for freeConnection() to return any connections
        // that are still used at this time.
        int useCount;
        while((useCount=getUseCount())>0 && System.currentTimeMillis() - startTime <=  millis) {
            try { Thread.sleep(500); }
            catch(InterruptedException e) {} // ignore
        }

        // Close all connections, whether safe or not
        for(int i=0; i < currConnections; i++) {
            try {
                connPool[i].close();
            } catch (SQLException e1) {
		if(debugLevel > 0) {
		    log.println("Cannot close connections on Destroy");
		}
            }
        }

        if(useCount > 0) {
            //bt-test successful
            String msg="Unsafe shutdown: Had to close "+useCount+
		" active DB connections after "+millis+"ms";
            log.println(msg);
            // Close all open files
            log.close();
            // Throwing following Exception is essential because servlet authors
            // are likely to have their own error logging requirements.
            throw new SQLException(msg);
        }

        // Close all open files
        log.close();

    }//End destroy()


    /**
     * Less safe shutdown.  Uses default timeout value.
     * This method simply calls the <code>destroy()</code> method
     * with a <code>millis</code>
     * value of 10000 (10 seconds) and ignores <code>SQLException</code>
     * thrown by that method.
     * @see     #destroy(int)
     */
    public void destroy() {
        try {
            destroy(10000);
        }
        catch(SQLException e) {}
    }



    /**
     * Returns the number of connections in use.
     */
    // This method could be reduced to return a counter that is
    // maintained by all methods that update connStatus.
    // However, it is more efficient to do it this way because:
    // Updating the counter would put an additional burden on the most
    // frequently used methods; in comparison, this method is
    // rarely used (although essential).
    public int getUseCount() {
        int useCount=0;
        synchronized(connStatus) {
            for(int i=0; i < currConnections; i++) {
                if(connStatus[i] > 0) { // In use
                    useCount++;
                }
            }
        }
        return useCount;
    }//End getUseCount()

    /**
     * Returns the number of connections in the dynamic pool.
     */
    public int getSize() {
        return currConnections;
    }//End getSize()
	public static final String getStackTrace(Exception e) {
		//
		// StringWriter will contain text of stack trace
		//
		StringWriter stringWriter = new StringWriter();

		//
		// Need to encapsulate the StringWriter into a Printwriter object
		// to fill up with stack trace
		//
		PrintWriter printWriter = new PrintWriter(stringWriter);

		//
		// Get the stack trace and fill the PrintWriter
		//
		e.printStackTrace(printWriter);

		//
		// StringBuffer to hold stack trace
		//
		StringBuffer error = stringWriter.getBuffer();

		//
		// Close writers
		//
		printWriter.close();

		//
		// Return value
		//
		return error.toString();
	}
}// End class
```

Ich hab dann noch eine Funktion die ich verwende im SQL Befehle auszuführen und dort verwendet ich folgendes um die Exception zu fangen:


```
public ResultSet makesql(String SqlString, VirtualHostObjects VHost)
	{

            try{
                Connection conn=VHost.getConnectionBroker().getConnection();
                ResultSet rset = null;
                if(conn!=null){
                    /**
                     * try connection for conneced and reconnect if it dosn't work
                     * the java.net.SocketException: Broken pipe is a hard problem with the mysql connection
                     * so we must take a look for alive connection witch SELECT 1 for alive check. if this check failed
                     * we destroy the connections and take a new one. 
                     **/
                    int brokenpipeerrorcounter=0;
                    boolean brokenpipeerror=false;
                    do{
                        brokenpipeerror=false;
                        try{
                            Statement stmt = conn.createStatement();
                            rset = stmt.executeQuery("SELECT 1");
                        } catch (Exception e5)  {
                             if(conn!=null) conn.close();
                             conn=VHost.getConnectionBroker().getConnection();
                             VHost.incresebrokenlinkerror();
                             brokenpipeerrorcounter++;
                             brokenpipeerror=true;
                        }
                    }while (brokenpipeerror &&  brokenpipeerrorcounter<10);
                    if(brokenpipeerrorcounter==9){
                        Logger.errorlog("to many SQL Error - no connection left - give up");
                        return null;
                    }
                     Statement stmt = conn.createStatement();
                     if(SqlString.startsWith("SELECT") || SqlString.startsWith("SHOW") || SqlString.startsWith("DESCRIBE")){
                         rset = stmt.executeQuery(SqlString);
                     }else{
                         stmt.execute(SqlString);
                         stmt.close();
                         VHost.getConnectionBroker().freeConnection(conn);
                     }
                 }
                 return rset;
            } catch (Exception e5)  {
                 Logger.errorlog("SQL Error:"+ExceptionUtils.getStackTrace(e5)+" sql:"+SqlString);
            }

        return null;
    }
```


----------

