Bug #19411 getConnection to down database instance hangs all following getConnections
Submitted: 27 Apr 2006 20:14 Modified: 28 Apr 2006 19:07
Reporter: Pat Conlin Email Updates:
Status: Not a Bug Impact on me:
None 
Category:Connector / J Severity:S1 (Critical)
Version:5.0.16 OS:Client XP, Server FreeBSD6Stable
Assigned to: CPU Architecture:Any

[27 Apr 2006 20:14] Pat Conlin
Description:
I have multiple instances of MySql running. In testing failover, I turn down a database server. Connections through the driver will hang if a connection to the down server is in the process of being obtained and neither will time out of course.

No timeout parameters are passed in the url used in the call to getConnection(String url). Our connection pool library (3rd party) is not respecting the socket timeout property and that has brought this issue to our attention.

We are looking for a workaround to this issue. We need the connection pool to handle transactions. I've included a simple test case but the urls will have to be modified as described.

Thanks

How to repeat:
Test Case:
Run the following with: 

getUrl() returning a url that should work, 
getBadUrl() returning a url that exists but has a MySQL instance not running

package com.tvidia.av.dao.mysql;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

import com.mysql.jdbc.util.BaseBugReport;

/**
 * It doesn't seem as though the MySql driver is multi threaded.
 * 
 *  Run the following with: 
 *  getUrl() returning a url that should work, 
 *  getBadUrl() returning a url that exists but has a MySQL instance not running
 *  
 *  Do not include timeouts in the parameter list of either urls
 *  
 *  Why does Thread 3 never complete it's task? 
 *  Is the driver blocking on Thread 2's getConnection()? 
 *  That doesn't seem correct.
 *  
 *  Running: 
 *  MySql 5.0.1.6 
 *  ConnectorJ v3.1.12
 *  Java 5
 *
 */
public class MYSqlBug extends BaseBugReport {
	ConnectionThread conn_1 = null;
	ConnectionThread conn_2 = null;
	ConnectionThread conn_3 = null;

	public void setUp(){
		try{
			Class.forName("com.mysql.jdbc.Driver").newInstance();
		}catch(ClassNotFoundException e){
			e.printStackTrace();
		}catch(IllegalAccessException e){
			e.printStackTrace();
		}catch(InstantiationException e){
			e.printStackTrace();
		}
	}
	
	public void runTest(){
		conn_1 = new ConnectionThread(getUrl());
		conn_2 = new ConnectionThread(getBadUrl());
		conn_3 = new ConnectionThread(getUrl());
		
		Thread t1 = new Thread(conn_1, conn_1.toString() + " 1" );
		Thread t2 = new Thread(conn_2, conn_2.toString() + " 2" );
		Thread t3 = new Thread(conn_3, conn_3.toString() + " 3" );

		t1.start();
		t2.start();
		t3.start();
		
		while(true){
			System.out.println("Thread 1 alive? " + t1.isAlive());
			System.out.println("Thread 2 alive? " + t2.isAlive());
			System.out.println("Thread 3 alive? " + t3.isAlive());
			System.out.println("--- Next update in five seconds");
			try{
				Thread.sleep(5000);				
			}catch(InterruptedException e){
				e.printStackTrace();
			}
		}
	}
	
	public String getUrl(){
		return "jdbc:mysql://ValidRunningHost:3306/avdev?autoReconnect=true&user=user&password=pass";
	}
	public String getBadUrl(){
		return "jdbc:mysql://ValidNotRunningHost:3306/avdev?autoReconnect=true&user=user&password=pass";
	}
	
	public void tearDown(){
		try{
			if(conn_1.getConnection()!=null)
				conn_1.getConnection().close();
			if(conn_2.getConnection()!=null)
				conn_2.getConnection().close();
			if(conn_3.getConnection()!=null)
				conn_3.getConnection().close();
		}catch(SQLException e){
			e.printStackTrace();
		}
	}

	public static void main(String[] args) throws Exception {
	      new MYSqlBug().run();
	 }
}
class ConnectionThread implements Runnable{
	String theDSUrl = null;
	Connection connection = null;
	
	ConnectionThread(String url){
		theDSUrl = url;
	}
	public void run(){
		try{
			connection = (Connection) DriverManager.getConnection(theDSUrl);
		}catch(SQLException sqle){
			sqle.printStackTrace();
		}
	}
	
	public Connection getConnection(){
		return connection;
	}
	
	public String toString(){
		return theDSUrl; 
	}
}

Suggested fix:
Can the driver be multithreaded?
[28 Apr 2006 15:31] Pat Conlin
I missed the severity when this was initially logged.
[28 Apr 2006 19:07] Pat Conlin
This is a problem in DriverManager, not ConnectorJ.

DriverManager.getConnection is synchronized. That's why I see this problem.

Sorry about that.