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?