Mixing
Data source rejected establishment of connection, message from server: “Too many connections” [closed]
I am trying to make connections to my database using connection pooling library: DBPool. Here's my source code.
DBUtils.java
package DB;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import javax.sql.ConnectionPoolDataSource;
import snaq.db.ConnectionPool;
import com.mysql.jdbc.Driver;
/**
* @author decorrea
*/
public class DBUtils {
public static String jdbc_driver_name = "com.mysql.jdbc.Driver";
private static String server_name ;
private static String database;
private static String username;
private static String password;
public String getServer_name() {
return server_name;
}
public void setServer_name(String serverName) {
server_name = serverName;
}
public String getDatabase() {
return database;
}
public void setDatabase(String database) {
this.database = database;
}
public String getUsername() {
return username;
}
public void setUsername(String username) {
this.username = username;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
/*
* Creates a MySQL DB connection from a pool
*/
public Connection createConnection(ConnectionPool pool){
Connection connection = null;
try {
// Load the JDBC driver
Class driver_class = Class.forName(jdbc_driver_name);
Driver driver = (Driver)driver_class.newInstance();
DriverManager.registerDriver(driver);
connection = pool.getConnection();
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
} catch (IllegalAccessException e) {
e.printStackTrace();
} catch (InstantiationException e) {
e.printStackTrace();
}
return connection;
}
/*
* Creates a MySQL DB connection
*/
public Connection createConnection(){
Connection connection = null;
try {
// Load the JDBC driver
Class driver_class = Class.forName(jdbc_driver_name);
Driver driver = (Driver)driver_class.newInstance();
DriverManager.registerDriver(driver);
String url = "jdbc:mysql://" + server_name + "/" + database;
connection = DriverManager.getConnection(url);
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
} catch (IllegalAccessException e) {
e.printStackTrace();
} catch (InstantiationException e) {
e.printStackTrace();
}
return connection;
}
}
TwitterAPI.java
/**
* @author decorrea
*/
public class TwitterAPI {
private static String server_name = "127.0.0.1";
private static String twitter_databse = "twitter";
private static String username = "root";
private static String password = "password";
public static Connection startDBConnection(String server_name, String database, String username, String password) {
//Set DB parameters
DBUtils mysql_obj = setDBParams(server_name, database, username, password);
String url = "jdbc:mysql://" + server_name + "/" + database;
ConnectionPool pool = new ConnectionPool("local",1, 1, 1, 180000, url, username, password);
Connection connection = mysql_obj.createConnection(pool);
return connection;
}
public static DBUtils setDBParams(String server_name, String database, String username, String password){
DBUtils mysql_obj = new DBUtils();
mysql_obj.setServer_name(server_name);
mysql_obj.setDatabase(database);
mysql_obj.setUsername(username);
mysql_obj.setPassword(password);
return mysql_obj;
}
public static String getTweets(BigInteger id){
Connection connection = startDBConnection(server_name,twitter_databse,username,password);
ResultSet resultSet = null;
String tweet = new String();
try {
Statement statement = connection.createStatement();
String query = SQL_queries.get_tweets_on_id + id.toString();
//Execute the query
resultSet = statement.executeQuery(query);
while(resultSet.next()){
tweet = resultSet.getString("content");
}
resultSet.close();
statement.close();
} catch (SQLException e) {
e.printStackTrace();
}
finally{
try {
connection.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
return tweet;
}
}
I am new to the business of connection pooling and decided to do so only because I was receiving a "Communications Link failure" without it.
Update 1: To add I also tried Apache DBCP and tried this example but still receive the same error.
org.apache.commons.dbcp.SQLNestedException: Cannot create PoolableConnectionFactory (Data source rejected establishment of connection, message from server: "Too many connections")
at org.apache.commons.dbcp.BasicDataSource.createPoolableConnectionFactory(BasicDataSource.java:1549)
at org.apache.commons.dbcp.BasicDataSource.createDataSource(BasicDataSource.java:1388)
at org.apache.commons.dbcp.BasicDataSource.getConnection(BasicDataSource.java:1044)
at Twitter.TwitterAPI.startDBConnection(TwitterAPI.java:55)
at Twitter.TwitterAPI.getTweets(TwitterAPI.java:84)
at Twitter.TwitterAPI.main(TwitterAPI.java:235)
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: Data source rejected establishment of connection, message from server: "Too many connections"
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:45)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27)
at java.lang.reflect.Constructor.newInstance(Constructor.java:528)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:409)
at com.mysql.jdbc.Util.getInstance(Util.java:384)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1015)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:989)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:984)
at com.mysql.jdbc.MysqlIO.doHandshake(MysqlIO.java:1105)
at com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2186)
at com.mysql.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:787)
at com.mysql.jdbc.JDBC4Connection.<init>(JDBC4Connection.java:49)
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:45)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27)
at java.lang.reflect.Constructor.newInstance(Constructor.java:528)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:409)
at com.mysql.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:357)
at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:285)
at org.apache.commons.dbcp.DriverConnectionFactory.createConnection(DriverConnectionFactory.java:38)
at org.apache.commons.dbcp.PoolableConnectionFactory.makeObject(PoolableConnectionFactory.java:582)
at org.apache.commons.dbcp.BasicDataSource.validateConnectionFactory(BasicDataSource.java:1556)
at org.apache.commons.dbcp.BasicDataSource.createPoolableConnectionFactory(BasicDataSource.java:1545)
... 5 more
Exception in thread "main" java.lang.NullPointerException
at Twitter.TwitterAPI.getTweets(TwitterAPI.java:108)
at Twitter.TwitterAPI.main(TwitterAPI.java:235)
I also checked the max_connections variable in the my.ini file in MySQL. Here's it's value:
The maximum amount of concurrent sessions the MySQL server will
allow. One of these connections will be reserved for a user with
SUPER privileges to allow the administrator to login even if the
connection limit has been reached.
max_connections=100
The show processlist command on the MySQL terminal shows 101 processes in sleep.
Any kind of help/comments will be appreciated
Update 2 -- Solution::
So, I figured out the solution. I hadn't mentioned the port name in the url connection to the database.
String url = "jdbc:mysql://" + server_name + "/" + database;
Probably, hence it led to many leaking connections. Once done, I tried with the example given here. It now doesn't throw any error. Thanks to BalusC, as I figured this out only due to his comment on changing the port number on MySQL. To add, the way to change the MySQL port number is NOT by changing the my.ini file but by running the MySQL instance config wizard under Start -> Programs -> MySQL Server 5.1 -> MySQL Server Instance Config Wizard. It was also interesting to note the code didn't throw any error when the port number wasn't specified and the program ran smoothly. Probably, JDBC connects to 3306 by default. If anyone has any particular idea about the same, please share.
For my complete source code see my answer below
java mysql jdbc
edited May 23 '17 at 12:01
Community♦
11
asked Jul 8 '10 at 12:45
DexterDexter
4,30173351
closed as too localized by slugster, djechlin, Abhinav Sarkar, ChrisF♦, Mick MacCallum Oct 15 '12 at 21:05
This question is unlikely to help any future visitors; it is only relevant to a small geographic area, a specific moment in time, or an extraordinarily narrow situation that is not generally applicable to the worldwide audience of the internet. For help making this question more broadly applicable, visit the help center. If this question can be reworded to fit the rules in the help center, please edit the question.
add a comment |
I am trying to make connections to my database using connection pooling library: DBPool. Here's my source code.
DBUtils.java
package DB;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import javax.sql.ConnectionPoolDataSource;
import snaq.db.ConnectionPool;
import com.mysql.jdbc.Driver;
/**
* @author decorrea
*/
public class DBUtils {
public static String jdbc_driver_name = "com.mysql.jdbc.Driver";
private static String server_name ;
private static String database;
private static String username;
private static String password;
public String getServer_name() {
return server_name;
}
public void setServer_name(String serverName) {
server_name = serverName;
}
public String getDatabase() {
return database;
}
public void setDatabase(String database) {
this.database = database;
}
public String getUsername() {
return username;
}
public void setUsername(String username) {
this.username = username;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
/*
* Creates a MySQL DB connection from a pool
*/
public Connection createConnection(ConnectionPool pool){
Connection connection = null;
try {
// Load the JDBC driver
Class driver_class = Class.forName(jdbc_driver_name);
Driver driver = (Driver)driver_class.newInstance();
DriverManager.registerDriver(driver);
connection = pool.getConnection();
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
} catch (IllegalAccessException e) {
e.printStackTrace();
} catch (InstantiationException e) {
e.printStackTrace();
}
return connection;
}
/*
* Creates a MySQL DB connection
*/
public Connection createConnection(){
Connection connection = null;
try {
// Load the JDBC driver
Class driver_class = Class.forName(jdbc_driver_name);
Driver driver = (Driver)driver_class.newInstance();
DriverManager.registerDriver(driver);
String url = "jdbc:mysql://" + server_name + "/" + database;
connection = DriverManager.getConnection(url);
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
} catch (IllegalAccessException e) {
e.printStackTrace();
} catch (InstantiationException e) {
e.printStackTrace();
}
return connection;
}
}
TwitterAPI.java
/**
* @author decorrea
*/
public class TwitterAPI {
private static String server_name = "127.0.0.1";
private static String twitter_databse = "twitter";
private static String username = "root";
private static String password = "password";
public static Connection startDBConnection(String server_name, String database, String username, String password) {
//Set DB parameters
DBUtils mysql_obj = setDBParams(server_name, database, username, password);
String url = "jdbc:mysql://" + server_name + "/" + database;
ConnectionPool pool = new ConnectionPool("local",1, 1, 1, 180000, url, username, password);
Connection connection = mysql_obj.createConnection(pool);
return connection;
}
public static DBUtils setDBParams(String server_name, String database, String username, String password){
DBUtils mysql_obj = new DBUtils();
mysql_obj.setServer_name(server_name);
mysql_obj.setDatabase(database);
mysql_obj.setUsername(username);
mysql_obj.setPassword(password);
return mysql_obj;
}
public static String getTweets(BigInteger id){
Connection connection = startDBConnection(server_name,twitter_databse,username,password);
ResultSet resultSet = null;
String tweet = new String();
try {
Statement statement = connection.createStatement();
String query = SQL_queries.get_tweets_on_id + id.toString();
//Execute the query
resultSet = statement.executeQuery(query);
while(resultSet.next()){
tweet = resultSet.getString("content");
}
resultSet.close();
statement.close();
} catch (SQLException e) {
e.printStackTrace();
}
finally{
try {
connection.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
return tweet;
}
}
I am new to the business of connection pooling and decided to do so only because I was receiving a "Communications Link failure" without it.
Update 1: To add I also tried Apache DBCP and tried this example but still receive the same error.
org.apache.commons.dbcp.SQLNestedException: Cannot create PoolableConnectionFactory (Data source rejected establishment of connection, message from server: "Too many connections")
at org.apache.commons.dbcp.BasicDataSource.createPoolableConnectionFactory(BasicDataSource.java:1549)
at org.apache.commons.dbcp.BasicDataSource.createDataSource(BasicDataSource.java:1388)
at org.apache.commons.dbcp.BasicDataSource.getConnection(BasicDataSource.java:1044)
at Twitter.TwitterAPI.startDBConnection(TwitterAPI.java:55)
at Twitter.TwitterAPI.getTweets(TwitterAPI.java:84)
at Twitter.TwitterAPI.main(TwitterAPI.java:235)
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: Data source rejected establishment of connection, message from server: "Too many connections"
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:45)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27)
at java.lang.reflect.Constructor.newInstance(Constructor.java:528)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:409)
at com.mysql.jdbc.Util.getInstance(Util.java:384)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1015)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:989)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:984)
at com.mysql.jdbc.MysqlIO.doHandshake(MysqlIO.java:1105)
at com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2186)
at com.mysql.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:787)
at com.mysql.jdbc.JDBC4Connection.<init>(JDBC4Connection.java:49)
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:45)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27)
at java.lang.reflect.Constructor.newInstance(Constructor.java:528)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:409)
at com.mysql.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:357)
at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:285)
at org.apache.commons.dbcp.DriverConnectionFactory.createConnection(DriverConnectionFactory.java:38)
at org.apache.commons.dbcp.PoolableConnectionFactory.makeObject(PoolableConnectionFactory.java:582)
at org.apache.commons.dbcp.BasicDataSource.validateConnectionFactory(BasicDataSource.java:1556)
at org.apache.commons.dbcp.BasicDataSource.createPoolableConnectionFactory(BasicDataSource.java:1545)
... 5 more
Exception in thread "main" java.lang.NullPointerException
at Twitter.TwitterAPI.getTweets(TwitterAPI.java:108)
at Twitter.TwitterAPI.main(TwitterAPI.java:235)
I also checked the max_connections variable in the my.ini file in MySQL. Here's it's value:
The maximum amount of concurrent sessions the MySQL server will
allow. One of these connections will be reserved for a user with
SUPER privileges to allow the administrator to login even if the
connection limit has been reached.
max_connections=100
The show processlist command on the MySQL terminal shows 101 processes in sleep.
Any kind of help/comments will be appreciated
Update 2 -- Solution::
So, I figured out the solution. I hadn't mentioned the port name in the url connection to the database.
String url = "jdbc:mysql://" + server_name + "/" + database;
Probably, hence it led to many leaking connections. Once done, I tried with the example given here. It now doesn't throw any error. Thanks to BalusC, as I figured this out only due to his comment on changing the port number on MySQL. To add, the way to change the MySQL port number is NOT by changing the my.ini file but by running the MySQL instance config wizard under Start -> Programs -> MySQL Server 5.1 -> MySQL Server Instance Config Wizard. It was also interesting to note the code didn't throw any error when the port number wasn't specified and the program ran smoothly. Probably, JDBC connects to 3306 by default. If anyone has any particular idea about the same, please share.
For my complete source code see my answer below
java mysql jdbc
edited May 23 '17 at 12:01
Community♦
11
asked Jul 8 '10 at 12:45
DexterDexter
4,30173351
closed as too localized by slugster, djechlin, Abhinav Sarkar, ChrisF♦, Mick MacCallum Oct 15 '12 at 21:05
This question is unlikely to help any future visitors; it is only relevant to a small geographic area, a specific moment in time, or an extraordinarily narrow situation that is not generally applicable to the worldwide audience of the internet. For help making this question more broadly applicable, visit the help center. If this question can be reworded to fit the rules in the help center, please edit the question.
Any particular reason for the edit ?
– Dexter
Jul 8 '10 at 13:24
1
you used<pre><code>which caused nested scrollbars in code. Just indent with 4 spaces or select and then press010101button in message editor toolbar orCtrl+Kkey to format code nicely.
– BalusC
Jul 8 '10 at 13:25
BalusC Thanks for the tip. Will keep in mind.
– Dexter
Jul 8 '10 at 13:29
add a comment |
I am trying to make connections to my database using connection pooling library: DBPool. Here's my source code.
DBUtils.java
package DB;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import javax.sql.ConnectionPoolDataSource;
import snaq.db.ConnectionPool;
import com.mysql.jdbc.Driver;
/**
* @author decorrea
*/
public class DBUtils {
public static String jdbc_driver_name = "com.mysql.jdbc.Driver";
private static String server_name ;
private static String database;
private static String username;
private static String password;
public String getServer_name() {
return server_name;
}
public void setServer_name(String serverName) {
server_name = serverName;
}
public String getDatabase() {
return database;
}
public void setDatabase(String database) {
this.database = database;
}
public String getUsername() {
return username;
}
public void setUsername(String username) {
this.username = username;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
/*
* Creates a MySQL DB connection from a pool
*/
public Connection createConnection(ConnectionPool pool){
Connection connection = null;
try {
// Load the JDBC driver
Class driver_class = Class.forName(jdbc_driver_name);
Driver driver = (Driver)driver_class.newInstance();
DriverManager.registerDriver(driver);
connection = pool.getConnection();
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
} catch (IllegalAccessException e) {
e.printStackTrace();
} catch (InstantiationException e) {
e.printStackTrace();
}
return connection;
}
/*
* Creates a MySQL DB connection
*/
public Connection createConnection(){
Connection connection = null;
try {
// Load the JDBC driver
Class driver_class = Class.forName(jdbc_driver_name);
Driver driver = (Driver)driver_class.newInstance();
DriverManager.registerDriver(driver);
String url = "jdbc:mysql://" + server_name + "/" + database;
connection = DriverManager.getConnection(url);
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
} catch (IllegalAccessException e) {
e.printStackTrace();
} catch (InstantiationException e) {
e.printStackTrace();
}
return connection;
}
}
TwitterAPI.java
/**
* @author decorrea
*/
public class TwitterAPI {
private static String server_name = "127.0.0.1";
private static String twitter_databse = "twitter";
private static String username = "root";
private static String password = "password";
public static Connection startDBConnection(String server_name, String database, String username, String password) {
//Set DB parameters
DBUtils mysql_obj = setDBParams(server_name, database, username, password);
String url = "jdbc:mysql://" + server_name + "/" + database;
ConnectionPool pool = new ConnectionPool("local",1, 1, 1, 180000, url, username, password);
Connection connection = mysql_obj.createConnection(pool);
return connection;
}
public static DBUtils setDBParams(String server_name, String database, String username, String password){
DBUtils mysql_obj = new DBUtils();
mysql_obj.setServer_name(server_name);
mysql_obj.setDatabase(database);
mysql_obj.setUsername(username);
mysql_obj.setPassword(password);
return mysql_obj;
}
public static String getTweets(BigInteger id){
Connection connection = startDBConnection(server_name,twitter_databse,username,password);
ResultSet resultSet = null;
String tweet = new String();
try {
Statement statement = connection.createStatement();
String query = SQL_queries.get_tweets_on_id + id.toString();
//Execute the query
resultSet = statement.executeQuery(query);
while(resultSet.next()){
tweet = resultSet.getString("content");
}
resultSet.close();
statement.close();
} catch (SQLException e) {
e.printStackTrace();
}
finally{
try {
connection.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
return tweet;
}
}
I am new to the business of connection pooling and decided to do so only because I was receiving a "Communications Link failure" without it.
Update 1: To add I also tried Apache DBCP and tried this example but still receive the same error.
org.apache.commons.dbcp.SQLNestedException: Cannot create PoolableConnectionFactory (Data source rejected establishment of connection, message from server: "Too many connections")
at org.apache.commons.dbcp.BasicDataSource.createPoolableConnectionFactory(BasicDataSource.java:1549)
at org.apache.commons.dbcp.BasicDataSource.createDataSource(BasicDataSource.java:1388)
at org.apache.commons.dbcp.BasicDataSource.getConnection(BasicDataSource.java:1044)
at Twitter.TwitterAPI.startDBConnection(TwitterAPI.java:55)
at Twitter.TwitterAPI.getTweets(TwitterAPI.java:84)
at Twitter.TwitterAPI.main(TwitterAPI.java:235)
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: Data source rejected establishment of connection, message from server: "Too many connections"
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:45)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27)
at java.lang.reflect.Constructor.newInstance(Constructor.java:528)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:409)
at com.mysql.jdbc.Util.getInstance(Util.java:384)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1015)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:989)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:984)
at com.mysql.jdbc.MysqlIO.doHandshake(MysqlIO.java:1105)
at com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2186)
at com.mysql.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:787)
at com.mysql.jdbc.JDBC4Connection.<init>(JDBC4Connection.java:49)
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:45)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27)
at java.lang.reflect.Constructor.newInstance(Constructor.java:528)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:409)
at com.mysql.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:357)
at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:285)
at org.apache.commons.dbcp.DriverConnectionFactory.createConnection(DriverConnectionFactory.java:38)
at org.apache.commons.dbcp.PoolableConnectionFactory.makeObject(PoolableConnectionFactory.java:582)
at org.apache.commons.dbcp.BasicDataSource.validateConnectionFactory(BasicDataSource.java:1556)
at org.apache.commons.dbcp.BasicDataSource.createPoolableConnectionFactory(BasicDataSource.java:1545)
... 5 more
Exception in thread "main" java.lang.NullPointerException
at Twitter.TwitterAPI.getTweets(TwitterAPI.java:108)
at Twitter.TwitterAPI.main(TwitterAPI.java:235)
I also checked the max_connections variable in the my.ini file in MySQL. Here's it's value:
The maximum amount of concurrent sessions the MySQL server will
allow. One of these connections will be reserved for a user with
SUPER privileges to allow the administrator to login even if the
connection limit has been reached.
max_connections=100
The show processlist command on the MySQL terminal shows 101 processes in sleep.
Any kind of help/comments will be appreciated
Update 2 -- Solution::
So, I figured out the solution. I hadn't mentioned the port name in the url connection to the database.
String url = "jdbc:mysql://" + server_name + "/" + database;
Probably, hence it led to many leaking connections. Once done, I tried with the example given here. It now doesn't throw any error. Thanks to BalusC, as I figured this out only due to his comment on changing the port number on MySQL. To add, the way to change the MySQL port number is NOT by changing the my.ini file but by running the MySQL instance config wizard under Start -> Programs -> MySQL Server 5.1 -> MySQL Server Instance Config Wizard. It was also interesting to note the code didn't throw any error when the port number wasn't specified and the program ran smoothly. Probably, JDBC connects to 3306 by default. If anyone has any particular idea about the same, please share.
For my complete source code see my answer below
java mysql jdbc
edited May 23 '17 at 12:01
Community♦
11
asked Jul 8 '10 at 12:45
DexterDexter
4,30173351
I am trying to make connections to my database using connection pooling library: DBPool. Here's my source code.
DBUtils.java
package DB;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import javax.sql.ConnectionPoolDataSource;
import snaq.db.ConnectionPool;
import com.mysql.jdbc.Driver;
/**
* @author decorrea
*/
public class DBUtils {
public static String jdbc_driver_name = "com.mysql.jdbc.Driver";
private static String server_name ;
private static String database;
private static String username;
private static String password;
public String getServer_name() {
return server_name;
}
public void setServer_name(String serverName) {
server_name = serverName;
}
public String getDatabase() {
return database;
}
public void setDatabase(String database) {
this.database = database;
}
public String getUsername() {
return username;
}
public void setUsername(String username) {
this.username = username;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
/*
* Creates a MySQL DB connection from a pool
*/
public Connection createConnection(ConnectionPool pool){
Connection connection = null;
try {
// Load the JDBC driver
Class driver_class = Class.forName(jdbc_driver_name);
Driver driver = (Driver)driver_class.newInstance();
DriverManager.registerDriver(driver);
connection = pool.getConnection();
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
} catch (IllegalAccessException e) {
e.printStackTrace();
} catch (InstantiationException e) {
e.printStackTrace();
}
return connection;
}
/*
* Creates a MySQL DB connection
*/
public Connection createConnection(){
Connection connection = null;
try {
// Load the JDBC driver
Class driver_class = Class.forName(jdbc_driver_name);
Driver driver = (Driver)driver_class.newInstance();
DriverManager.registerDriver(driver);
String url = "jdbc:mysql://" + server_name + "/" + database;
connection = DriverManager.getConnection(url);
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
} catch (IllegalAccessException e) {
e.printStackTrace();
} catch (InstantiationException e) {
e.printStackTrace();
}
return connection;
}
}
TwitterAPI.java
/**
* @author decorrea
*/
public class TwitterAPI {
private static String server_name = "127.0.0.1";
private static String twitter_databse = "twitter";
private static String username = "root";
private static String password = "password";
public static Connection startDBConnection(String server_name, String database, String username, String password) {
//Set DB parameters
DBUtils mysql_obj = setDBParams(server_name, database, username, password);
String url = "jdbc:mysql://" + server_name + "/" + database;
ConnectionPool pool = new ConnectionPool("local",1, 1, 1, 180000, url, username, password);
Connection connection = mysql_obj.createConnection(pool);
return connection;
}
public static DBUtils setDBParams(String server_name, String database, String username, String password){
DBUtils mysql_obj = new DBUtils();
mysql_obj.setServer_name(server_name);
mysql_obj.setDatabase(database);
mysql_obj.setUsername(username);
mysql_obj.setPassword(password);
return mysql_obj;
}
public static String getTweets(BigInteger id){
Connection connection = startDBConnection(server_name,twitter_databse,username,password);
ResultSet resultSet = null;
String tweet = new String();
try {
Statement statement = connection.createStatement();
String query = SQL_queries.get_tweets_on_id + id.toString();
//Execute the query
resultSet = statement.executeQuery(query);
while(resultSet.next()){
tweet = resultSet.getString("content");
}
resultSet.close();
statement.close();
} catch (SQLException e) {
e.printStackTrace();
}
finally{
try {
connection.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
return tweet;
}
}
I am new to the business of connection pooling and decided to do so only because I was receiving a "Communications Link failure" without it.
Update 1: To add I also tried Apache DBCP and tried this example but still receive the same error.
org.apache.commons.dbcp.SQLNestedException: Cannot create PoolableConnectionFactory (Data source rejected establishment of connection, message from server: "Too many connections")
at org.apache.commons.dbcp.BasicDataSource.createPoolableConnectionFactory(BasicDataSource.java:1549)
at org.apache.commons.dbcp.BasicDataSource.createDataSource(BasicDataSource.java:1388)
at org.apache.commons.dbcp.BasicDataSource.getConnection(BasicDataSource.java:1044)
at Twitter.TwitterAPI.startDBConnection(TwitterAPI.java:55)
at Twitter.TwitterAPI.getTweets(TwitterAPI.java:84)
at Twitter.TwitterAPI.main(TwitterAPI.java:235)
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: Data source rejected establishment of connection, message from server: "Too many connections"
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:45)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27)
at java.lang.reflect.Constructor.newInstance(Constructor.java:528)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:409)
at com.mysql.jdbc.Util.getInstance(Util.java:384)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1015)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:989)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:984)
at com.mysql.jdbc.MysqlIO.doHandshake(MysqlIO.java:1105)
at com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2186)
at com.mysql.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:787)
at com.mysql.jdbc.JDBC4Connection.<init>(JDBC4Connection.java:49)
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:45)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27)
at java.lang.reflect.Constructor.newInstance(Constructor.java:528)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:409)
at com.mysql.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:357)
at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:285)
at org.apache.commons.dbcp.DriverConnectionFactory.createConnection(DriverConnectionFactory.java:38)
at org.apache.commons.dbcp.PoolableConnectionFactory.makeObject(PoolableConnectionFactory.java:582)
at org.apache.commons.dbcp.BasicDataSource.validateConnectionFactory(BasicDataSource.java:1556)
at org.apache.commons.dbcp.BasicDataSource.createPoolableConnectionFactory(BasicDataSource.java:1545)
... 5 more
Exception in thread "main" java.lang.NullPointerException
at Twitter.TwitterAPI.getTweets(TwitterAPI.java:108)
at Twitter.TwitterAPI.main(TwitterAPI.java:235)
I also checked the max_connections variable in the my.ini file in MySQL. Here's it's value:
The maximum amount of concurrent sessions the MySQL server will
allow. One of these connections will be reserved for a user with
SUPER privileges to allow the administrator to login even if the
connection limit has been reached.
max_connections=100
The show processlist command on the MySQL terminal shows 101 processes in sleep.
Any kind of help/comments will be appreciated
Update 2 -- Solution::
So, I figured out the solution. I hadn't mentioned the port name in the url connection to the database.
String url = "jdbc:mysql://" + server_name + "/" + database;
Probably, hence it led to many leaking connections. Once done, I tried with the example given here. It now doesn't throw any error. Thanks to BalusC, as I figured this out only due to his comment on changing the port number on MySQL. To add, the way to change the MySQL port number is NOT by changing the my.ini file but by running the MySQL instance config wizard under Start -> Programs -> MySQL Server 5.1 -> MySQL Server Instance Config Wizard. It was also interesting to note the code didn't throw any error when the port number wasn't specified and the program ran smoothly. Probably, JDBC connects to 3306 by default. If anyone has any particular idea about the same, please share.
For my complete source code see my answer below
java mysql jdbc
java mysql jdbc
edited May 23 '17 at 12:01
Community♦
11
asked Jul 8 '10 at 12:45
DexterDexter
4,30173351
edited May 23 '17 at 12:01
Community♦
11
asked Jul 8 '10 at 12:45
DexterDexter
4,30173351
edited May 23 '17 at 12:01
Community♦
11
edited May 23 '17 at 12:01
Community♦
11
edited May 23 '17 at 12:01
Community♦
11
11
asked Jul 8 '10 at 12:45
DexterDexter
4,30173351
asked Jul 8 '10 at 12:45
DexterDexter
4,30173351
asked Jul 8 '10 at 12:45
DexterDexter
4,30173351
4,30173351
closed as too localized by slugster, djechlin, Abhinav Sarkar, ChrisF♦, Mick MacCallum Oct 15 '12 at 21:05
This question is unlikely to help any future visitors; it is only relevant to a small geographic area, a specific moment in time, or an extraordinarily narrow situation that is not generally applicable to the worldwide audience of the internet. For help making this question more broadly applicable, visit the help center. If this question can be reworded to fit the rules in the help center, please edit the question.
closed as too localized by slugster, djechlin, Abhinav Sarkar, ChrisF♦, Mick MacCallum Oct 15 '12 at 21:05
This question is unlikely to help any future visitors; it is only relevant to a small geographic area, a specific moment in time, or an extraordinarily narrow situation that is not generally applicable to the worldwide audience of the internet. For help making this question more broadly applicable, visit the help center. If this question can be reworded to fit the rules in the help center, please edit the question.
Any particular reason for the edit ?
– Dexter
Jul 8 '10 at 13:24
1
you used<pre><code>which caused nested scrollbars in code. Just indent with 4 spaces or select and then press010101button in message editor toolbar orCtrl+Kkey to format code nicely.
– BalusC
Jul 8 '10 at 13:25
BalusC Thanks for the tip. Will keep in mind.
– Dexter
Jul 8 '10 at 13:29
add a comment |
Any particular reason for the edit ?
– Dexter
Jul 8 '10 at 13:24
1
you used<pre><code>which caused nested scrollbars in code. Just indent with 4 spaces or select and then press010101button in message editor toolbar orCtrl+Kkey to format code nicely.
– BalusC
Jul 8 '10 at 13:25
BalusC Thanks for the tip. Will keep in mind.
– Dexter
Jul 8 '10 at 13:29
Any particular reason for the edit ?
– Dexter
Jul 8 '10 at 13:24
Any particular reason for the edit ?
– Dexter
Jul 8 '10 at 13:24
1
1
you used
<pre><code> which caused nested scrollbars in code. Just indent with 4 spaces or select and then press 010101 button in message editor toolbar or Ctrl+K key to format code nicely.– BalusC
Jul 8 '10 at 13:25
you used
<pre><code> which caused nested scrollbars in code. Just indent with 4 spaces or select and then press 010101 button in message editor toolbar or Ctrl+K key to format code nicely.– BalusC
Jul 8 '10 at 13:25
BalusC Thanks for the tip. Will keep in mind.
– Dexter
Jul 8 '10 at 13:29
BalusC Thanks for the tip. Will keep in mind.
– Dexter
Jul 8 '10 at 13:29
add a comment |
2 Answers
2
active
oldest
votes
org.apache.commons.dbcp.SQLNestedException: Cannot create PoolableConnectionFactory (Data source rejected establishment of connection, message from server: "Too many connections")
This indicates that something is leaking connections. I.e. something keeps acquiring (opening) connections without ever closing them and/or returning to connection pool. Restarting the DB so that it can hard-close all opened connections should temporarily fix the issue. Fixing something so that it is properly closing the connection after use should permanently fix the issue.
Although the posted JDBC code is not following the optimal idiom, it doesn't look like to be the cause of leaking connections. Probably the DB is already running for hours/days and you've during earlier tests acquired too many connections without closing them so that the DB is running out of them.
answered Jul 8 '10 at 13:24
BalusCBalusC
849k29831503222
BalusC, I do close the connections in the getTweets() function in the finally block. It's the only place where I am "making" a connection. What else could be leaking ?
– Dexter
Jul 8 '10 at 13:31
Restart DB and reobserve if the problem would occur again. I brought the last paragraph of my answer in during an edit which you might have missed.
– BalusC
Jul 8 '10 at 13:32
BalusC, Yes I did miss it. 1. I have restarted my DB multiple times and it doesn't have any effect 2. The code isn't optimal agreed but I'm more curious on the problem 3. As told earlier, I have checked the connections via MySQL but none of them seem to be an issue.
– Dexter
Jul 8 '10 at 13:44
1
Well, it much look like that something else is using the DB. Try changing the DB port number (so that something else cannot connect it without being reconfigured) and retry in Java.
– BalusC
Jul 8 '10 at 13:52
BalusC, I tried changing the port number and tried using Java to no success. :-(
– Dexter
Jul 9 '10 at 6:34
|
show 2 more comments
So, I figured out the solution. I hadn't mentioned the port name in the url connection to the database.
String url = "jdbc:mysql://" + server_name + "/" + database;
Probably, hence it led to many leaking connections. Once done, I tried with the example given here. It now doesn't throw any error. Thanks to BalusC, as I figured this out only due to his comment on changing the port number on MySQL. To add, the way to change the MySQL port number is NOT by changing the my.ini file but by running the MySQL instance config wizard under Start -> Programs -> MySQL Server 5.1 -> MySQL Server Instance Config Wizard. It was also interesting to note the code didn't throw any error when the port number wasn't specified and the program ran smoothly. Probably, JDBC connects to 3306 by default. If anyone has any particular idea about the same, please share.
For benefit of folks, here's the source code:
DBUtils.java
package DB;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import javax.sql.DataSource;
import org.apache.commons.dbcp.BasicDataSource;
import com.mysql.jdbc.Driver;
/**
* @author decorrea
*
*/
public class DBUtils {
public static String jdbc_driver_name = "com.mysql.jdbc.Driver";
private static String server_name ;
private static String database;
private static String username;
private static String password;
private static int maxActive = 20;
private static int maxIdle = 2 ;
public String getServer_name() {
return server_name;
}
public void setServer_name(String serverName) {
server_name = serverName;
}
public String getDatabase() {
return database;
}
public void setDatabase(String database) {
this.database = database;
}
public String getUsername() {
return username;
}
public void setUsername(String username) {
this.username = username;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
public static DataSource getDataSource(String server_name, String database, String username, String password){
BasicDataSource datasource = new BasicDataSource();
datasource.setDriverClassName(jdbc_driver_name);
String url = "jdbc:mysql://" + server_name + "/" + database;
System.out.println(url);
datasource.setUsername(username);
datasource.setPassword(password);
datasource.setUrl(url);
datasource.setMaxActive(maxActive);
datasource.setMaxIdle(maxIdle);
return datasource;
}
}
TwitterAPI.java
public class TwitterAPI {
private static String server_name = "localhost:7777";
private static String twitter_databse = "twitter";
private static String username = "root";
private static String password = "password";
public static String twitter_unique_usernames_file = "twitter_unique_usernames_file.txt";
public static String language_model_file = "C:\de\JARS\lingpipe-4.0.0\demos\models\langid-leipzig.classifier";
public static DataSource dataSource = DBUtils.getDataSource(server_name, twitter_databse, username, password);
public static Connection startDBConnection(String server_name, String database, String username, String password) {
//Set DB parameters
//DBUtils mysql_obj = setDBParams(server_name, database, username, password);
Connection connection = null;
//connection = mysql_obj.createConnection();
try {
connection = dataSource.getConnection();
} catch (SQLException e) {
e.printStackTrace();
}
return connection;
}
public static DBUtils setDBParams(String server_name, String database, String username, String password){
DBUtils mysql_obj = new DBUtils();
mysql_obj.setServer_name(server_name);
mysql_obj.setDatabase(database);
mysql_obj.setUsername(username);
mysql_obj.setPassword(password);
return mysql_obj;
}
public static String getTweets(BigInteger id){
Connection connection = startDBConnection(server_name,twitter_databse,username,password);
ResultSet resultSet = null;
String tweet = new String();
try {
Statement statement = connection.createStatement();
String query = SQL_queries.get_tweets_on_id + id.toString();
//Execute the query
resultSet = statement.executeQuery(query);
while(resultSet.next()){
tweet = resultSet.getString("content");
}
resultSet.close();
statement.close();
} catch (SQLException e) {
e.printStackTrace();
}
finally{
try {
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
return tweet;
}
Hope this helps.
edited Sep 28 '12 at 0:01
Mr_and_Mrs_D
15.9k23121258
answered Jul 9 '10 at 13:17
DexterDexter
4,30173351
Interesting, I didn't expect that omitting the port number would lead to this behaviour... Does it also happen when you don't use a connection pool?
– BalusC
Jul 9 '10 at 21:58
BalusC, With port number + connection pool : no issues. Without port number + connection pool : can't connect. Without port number + Without connection pool : Can run for a limited time but soon exceeds max_number_of_connections. With port number + without connection pool: haven't tried as yet.
– Dexter
Jul 10 '10 at 9:53
2
For whoever is still wondering where this port was set check theserver_namefield inTwitterAPI
– Mr_and_Mrs_D
Sep 28 '12 at 0:06
add a comment |
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
org.apache.commons.dbcp.SQLNestedException: Cannot create PoolableConnectionFactory (Data source rejected establishment of connection, message from server: "Too many connections")
This indicates that something is leaking connections. I.e. something keeps acquiring (opening) connections without ever closing them and/or returning to connection pool. Restarting the DB so that it can hard-close all opened connections should temporarily fix the issue. Fixing something so that it is properly closing the connection after use should permanently fix the issue.
Although the posted JDBC code is not following the optimal idiom, it doesn't look like to be the cause of leaking connections. Probably the DB is already running for hours/days and you've during earlier tests acquired too many connections without closing them so that the DB is running out of them.
answered Jul 8 '10 at 13:24
BalusCBalusC
849k29831503222
BalusC, I do close the connections in the getTweets() function in the finally block. It's the only place where I am "making" a connection. What else could be leaking ?
– Dexter
Jul 8 '10 at 13:31
Restart DB and reobserve if the problem would occur again. I brought the last paragraph of my answer in during an edit which you might have missed.
– BalusC
Jul 8 '10 at 13:32
BalusC, Yes I did miss it. 1. I have restarted my DB multiple times and it doesn't have any effect 2. The code isn't optimal agreed but I'm more curious on the problem 3. As told earlier, I have checked the connections via MySQL but none of them seem to be an issue.
– Dexter
Jul 8 '10 at 13:44
1
Well, it much look like that something else is using the DB. Try changing the DB port number (so that something else cannot connect it without being reconfigured) and retry in Java.
– BalusC
Jul 8 '10 at 13:52
BalusC, I tried changing the port number and tried using Java to no success. :-(
– Dexter
Jul 9 '10 at 6:34
|
show 2 more comments
org.apache.commons.dbcp.SQLNestedException: Cannot create PoolableConnectionFactory (Data source rejected establishment of connection, message from server: "Too many connections")
This indicates that something is leaking connections. I.e. something keeps acquiring (opening) connections without ever closing them and/or returning to connection pool. Restarting the DB so that it can hard-close all opened connections should temporarily fix the issue. Fixing something so that it is properly closing the connection after use should permanently fix the issue.
Although the posted JDBC code is not following the optimal idiom, it doesn't look like to be the cause of leaking connections. Probably the DB is already running for hours/days and you've during earlier tests acquired too many connections without closing them so that the DB is running out of them.
answered Jul 8 '10 at 13:24
BalusCBalusC
849k29831503222
BalusC, I do close the connections in the getTweets() function in the finally block. It's the only place where I am "making" a connection. What else could be leaking ?
– Dexter
Jul 8 '10 at 13:31
Restart DB and reobserve if the problem would occur again. I brought the last paragraph of my answer in during an edit which you might have missed.
– BalusC
Jul 8 '10 at 13:32
BalusC, Yes I did miss it. 1. I have restarted my DB multiple times and it doesn't have any effect 2. The code isn't optimal agreed but I'm more curious on the problem 3. As told earlier, I have checked the connections via MySQL but none of them seem to be an issue.
– Dexter
Jul 8 '10 at 13:44
1
Well, it much look like that something else is using the DB. Try changing the DB port number (so that something else cannot connect it without being reconfigured) and retry in Java.
– BalusC
Jul 8 '10 at 13:52
BalusC, I tried changing the port number and tried using Java to no success. :-(
– Dexter
Jul 9 '10 at 6:34
|
show 2 more comments
org.apache.commons.dbcp.SQLNestedException: Cannot create PoolableConnectionFactory (Data source rejected establishment of connection, message from server: "Too many connections")
This indicates that something is leaking connections. I.e. something keeps acquiring (opening) connections without ever closing them and/or returning to connection pool. Restarting the DB so that it can hard-close all opened connections should temporarily fix the issue. Fixing something so that it is properly closing the connection after use should permanently fix the issue.
Although the posted JDBC code is not following the optimal idiom, it doesn't look like to be the cause of leaking connections. Probably the DB is already running for hours/days and you've during earlier tests acquired too many connections without closing them so that the DB is running out of them.
answered Jul 8 '10 at 13:24
BalusCBalusC
849k29831503222
org.apache.commons.dbcp.SQLNestedException: Cannot create PoolableConnectionFactory (Data source rejected establishment of connection, message from server: "Too many connections")
This indicates that something is leaking connections. I.e. something keeps acquiring (opening) connections without ever closing them and/or returning to connection pool. Restarting the DB so that it can hard-close all opened connections should temporarily fix the issue. Fixing something so that it is properly closing the connection after use should permanently fix the issue.
Although the posted JDBC code is not following the optimal idiom, it doesn't look like to be the cause of leaking connections. Probably the DB is already running for hours/days and you've during earlier tests acquired too many connections without closing them so that the DB is running out of them.
answered Jul 8 '10 at 13:24
BalusCBalusC
849k29831503222
answered Jul 8 '10 at 13:24
BalusCBalusC
849k29831503222
answered Jul 8 '10 at 13:24
BalusCBalusC
849k29831503222
answered Jul 8 '10 at 13:24
BalusCBalusC
849k29831503222
849k29831503222
BalusC, I do close the connections in the getTweets() function in the finally block. It's the only place where I am "making" a connection. What else could be leaking ?
– Dexter
Jul 8 '10 at 13:31
Restart DB and reobserve if the problem would occur again. I brought the last paragraph of my answer in during an edit which you might have missed.
– BalusC
Jul 8 '10 at 13:32
BalusC, Yes I did miss it. 1. I have restarted my DB multiple times and it doesn't have any effect 2. The code isn't optimal agreed but I'm more curious on the problem 3. As told earlier, I have checked the connections via MySQL but none of them seem to be an issue.
– Dexter
Jul 8 '10 at 13:44
1
Well, it much look like that something else is using the DB. Try changing the DB port number (so that something else cannot connect it without being reconfigured) and retry in Java.
– BalusC
Jul 8 '10 at 13:52
BalusC, I tried changing the port number and tried using Java to no success. :-(
– Dexter
Jul 9 '10 at 6:34
|
show 2 more comments
BalusC, I do close the connections in the getTweets() function in the finally block. It's the only place where I am "making" a connection. What else could be leaking ?
– Dexter
Jul 8 '10 at 13:31
Restart DB and reobserve if the problem would occur again. I brought the last paragraph of my answer in during an edit which you might have missed.
– BalusC
Jul 8 '10 at 13:32
BalusC, Yes I did miss it. 1. I have restarted my DB multiple times and it doesn't have any effect 2. The code isn't optimal agreed but I'm more curious on the problem 3. As told earlier, I have checked the connections via MySQL but none of them seem to be an issue.
– Dexter
Jul 8 '10 at 13:44
1
Well, it much look like that something else is using the DB. Try changing the DB port number (so that something else cannot connect it without being reconfigured) and retry in Java.
– BalusC
Jul 8 '10 at 13:52
BalusC, I tried changing the port number and tried using Java to no success. :-(
– Dexter
Jul 9 '10 at 6:34
BalusC, I do close the connections in the getTweets() function in the finally block. It's the only place where I am "making" a connection. What else could be leaking ?
– Dexter
Jul 8 '10 at 13:31
BalusC, I do close the connections in the getTweets() function in the finally block. It's the only place where I am "making" a connection. What else could be leaking ?
– Dexter
Jul 8 '10 at 13:31
Restart DB and reobserve if the problem would occur again. I brought the last paragraph of my answer in during an edit which you might have missed.
– BalusC
Jul 8 '10 at 13:32
Restart DB and reobserve if the problem would occur again. I brought the last paragraph of my answer in during an edit which you might have missed.
– BalusC
Jul 8 '10 at 13:32
BalusC, Yes I did miss it. 1. I have restarted my DB multiple times and it doesn't have any effect 2. The code isn't optimal agreed but I'm more curious on the problem 3. As told earlier, I have checked the connections via MySQL but none of them seem to be an issue.
– Dexter
Jul 8 '10 at 13:44
BalusC, Yes I did miss it. 1. I have restarted my DB multiple times and it doesn't have any effect 2. The code isn't optimal agreed but I'm more curious on the problem 3. As told earlier, I have checked the connections via MySQL but none of them seem to be an issue.
– Dexter
Jul 8 '10 at 13:44
1
1
Well, it much look like that something else is using the DB. Try changing the DB port number (so that something else cannot connect it without being reconfigured) and retry in Java.
– BalusC
Jul 8 '10 at 13:52
Well, it much look like that something else is using the DB. Try changing the DB port number (so that something else cannot connect it without being reconfigured) and retry in Java.
– BalusC
Jul 8 '10 at 13:52
BalusC, I tried changing the port number and tried using Java to no success. :-(
– Dexter
Jul 9 '10 at 6:34
BalusC, I tried changing the port number and tried using Java to no success. :-(
– Dexter
Jul 9 '10 at 6:34
|
show 2 more comments
So, I figured out the solution. I hadn't mentioned the port name in the url connection to the database.
String url = "jdbc:mysql://" + server_name + "/" + database;
Probably, hence it led to many leaking connections. Once done, I tried with the example given here. It now doesn't throw any error. Thanks to BalusC, as I figured this out only due to his comment on changing the port number on MySQL. To add, the way to change the MySQL port number is NOT by changing the my.ini file but by running the MySQL instance config wizard under Start -> Programs -> MySQL Server 5.1 -> MySQL Server Instance Config Wizard. It was also interesting to note the code didn't throw any error when the port number wasn't specified and the program ran smoothly. Probably, JDBC connects to 3306 by default. If anyone has any particular idea about the same, please share.
For benefit of folks, here's the source code:
DBUtils.java
package DB;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import javax.sql.DataSource;
import org.apache.commons.dbcp.BasicDataSource;
import com.mysql.jdbc.Driver;
/**
* @author decorrea
*
*/
public class DBUtils {
public static String jdbc_driver_name = "com.mysql.jdbc.Driver";
private static String server_name ;
private static String database;
private static String username;
private static String password;
private static int maxActive = 20;
private static int maxIdle = 2 ;
public String getServer_name() {
return server_name;
}
public void setServer_name(String serverName) {
server_name = serverName;
}
public String getDatabase() {
return database;
}
public void setDatabase(String database) {
this.database = database;
}
public String getUsername() {
return username;
}
public void setUsername(String username) {
this.username = username;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
public static DataSource getDataSource(String server_name, String database, String username, String password){
BasicDataSource datasource = new BasicDataSource();
datasource.setDriverClassName(jdbc_driver_name);
String url = "jdbc:mysql://" + server_name + "/" + database;
System.out.println(url);
datasource.setUsername(username);
datasource.setPassword(password);
datasource.setUrl(url);
datasource.setMaxActive(maxActive);
datasource.setMaxIdle(maxIdle);
return datasource;
}
}
TwitterAPI.java
public class TwitterAPI {
private static String server_name = "localhost:7777";
private static String twitter_databse = "twitter";
private static String username = "root";
private static String password = "password";
public static String twitter_unique_usernames_file = "twitter_unique_usernames_file.txt";
public static String language_model_file = "C:\de\JARS\lingpipe-4.0.0\demos\models\langid-leipzig.classifier";
public static DataSource dataSource = DBUtils.getDataSource(server_name, twitter_databse, username, password);
public static Connection startDBConnection(String server_name, String database, String username, String password) {
//Set DB parameters
//DBUtils mysql_obj = setDBParams(server_name, database, username, password);
Connection connection = null;
//connection = mysql_obj.createConnection();
try {
connection = dataSource.getConnection();
} catch (SQLException e) {
e.printStackTrace();
}
return connection;
}
public static DBUtils setDBParams(String server_name, String database, String username, String password){
DBUtils mysql_obj = new DBUtils();
mysql_obj.setServer_name(server_name);
mysql_obj.setDatabase(database);
mysql_obj.setUsername(username);
mysql_obj.setPassword(password);
return mysql_obj;
}
public static String getTweets(BigInteger id){
Connection connection = startDBConnection(server_name,twitter_databse,username,password);
ResultSet resultSet = null;
String tweet = new String();
try {
Statement statement = connection.createStatement();
String query = SQL_queries.get_tweets_on_id + id.toString();
//Execute the query
resultSet = statement.executeQuery(query);
while(resultSet.next()){
tweet = resultSet.getString("content");
}
resultSet.close();
statement.close();
} catch (SQLException e) {
e.printStackTrace();
}
finally{
try {
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
return tweet;
}
Hope this helps.
edited Sep 28 '12 at 0:01
Mr_and_Mrs_D
15.9k23121258
answered Jul 9 '10 at 13:17
DexterDexter
4,30173351
Interesting, I didn't expect that omitting the port number would lead to this behaviour... Does it also happen when you don't use a connection pool?
– BalusC
Jul 9 '10 at 21:58
BalusC, With port number + connection pool : no issues. Without port number + connection pool : can't connect. Without port number + Without connection pool : Can run for a limited time but soon exceeds max_number_of_connections. With port number + without connection pool: haven't tried as yet.
– Dexter
Jul 10 '10 at 9:53
2
For whoever is still wondering where this port was set check theserver_namefield inTwitterAPI
– Mr_and_Mrs_D
Sep 28 '12 at 0:06
add a comment |
So, I figured out the solution. I hadn't mentioned the port name in the url connection to the database.
String url = "jdbc:mysql://" + server_name + "/" + database;
Probably, hence it led to many leaking connections. Once done, I tried with the example given here. It now doesn't throw any error. Thanks to BalusC, as I figured this out only due to his comment on changing the port number on MySQL. To add, the way to change the MySQL port number is NOT by changing the my.ini file but by running the MySQL instance config wizard under Start -> Programs -> MySQL Server 5.1 -> MySQL Server Instance Config Wizard. It was also interesting to note the code didn't throw any error when the port number wasn't specified and the program ran smoothly. Probably, JDBC connects to 3306 by default. If anyone has any particular idea about the same, please share.
For benefit of folks, here's the source code:
DBUtils.java
package DB;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import javax.sql.DataSource;
import org.apache.commons.dbcp.BasicDataSource;
import com.mysql.jdbc.Driver;
/**
* @author decorrea
*
*/
public class DBUtils {
public static String jdbc_driver_name = "com.mysql.jdbc.Driver";
private static String server_name ;
private static String database;
private static String username;
private static String password;
private static int maxActive = 20;
private static int maxIdle = 2 ;
public String getServer_name() {
return server_name;
}
public void setServer_name(String serverName) {
server_name = serverName;
}
public String getDatabase() {
return database;
}
public void setDatabase(String database) {
this.database = database;
}
public String getUsername() {
return username;
}
public void setUsername(String username) {
this.username = username;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
public static DataSource getDataSource(String server_name, String database, String username, String password){
BasicDataSource datasource = new BasicDataSource();
datasource.setDriverClassName(jdbc_driver_name);
String url = "jdbc:mysql://" + server_name + "/" + database;
System.out.println(url);
datasource.setUsername(username);
datasource.setPassword(password);
datasource.setUrl(url);
datasource.setMaxActive(maxActive);
datasource.setMaxIdle(maxIdle);
return datasource;
}
}
TwitterAPI.java
public class TwitterAPI {
private static String server_name = "localhost:7777";
private static String twitter_databse = "twitter";
private static String username = "root";
private static String password = "password";
public static String twitter_unique_usernames_file = "twitter_unique_usernames_file.txt";
public static String language_model_file = "C:\de\JARS\lingpipe-4.0.0\demos\models\langid-leipzig.classifier";
public static DataSource dataSource = DBUtils.getDataSource(server_name, twitter_databse, username, password);
public static Connection startDBConnection(String server_name, String database, String username, String password) {
//Set DB parameters
//DBUtils mysql_obj = setDBParams(server_name, database, username, password);
Connection connection = null;
//connection = mysql_obj.createConnection();
try {
connection = dataSource.getConnection();
} catch (SQLException e) {
e.printStackTrace();
}
return connection;
}
public static DBUtils setDBParams(String server_name, String database, String username, String password){
DBUtils mysql_obj = new DBUtils();
mysql_obj.setServer_name(server_name);
mysql_obj.setDatabase(database);
mysql_obj.setUsername(username);
mysql_obj.setPassword(password);
return mysql_obj;
}
public static String getTweets(BigInteger id){
Connection connection = startDBConnection(server_name,twitter_databse,username,password);
ResultSet resultSet = null;
String tweet = new String();
try {
Statement statement = connection.createStatement();
String query = SQL_queries.get_tweets_on_id + id.toString();
//Execute the query
resultSet = statement.executeQuery(query);
while(resultSet.next()){
tweet = resultSet.getString("content");
}
resultSet.close();
statement.close();
} catch (SQLException e) {
e.printStackTrace();
}
finally{
try {
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
return tweet;
}
Hope this helps.
edited Sep 28 '12 at 0:01
Mr_and_Mrs_D
15.9k23121258
answered Jul 9 '10 at 13:17
DexterDexter
4,30173351
Interesting, I didn't expect that omitting the port number would lead to this behaviour... Does it also happen when you don't use a connection pool?
– BalusC
Jul 9 '10 at 21:58
BalusC, With port number + connection pool : no issues. Without port number + connection pool : can't connect. Without port number + Without connection pool : Can run for a limited time but soon exceeds max_number_of_connections. With port number + without connection pool: haven't tried as yet.
– Dexter
Jul 10 '10 at 9:53
2
For whoever is still wondering where this port was set check theserver_namefield inTwitterAPI
– Mr_and_Mrs_D
Sep 28 '12 at 0:06
add a comment |
So, I figured out the solution. I hadn't mentioned the port name in the url connection to the database.
String url = "jdbc:mysql://" + server_name + "/" + database;
Probably, hence it led to many leaking connections. Once done, I tried with the example given here. It now doesn't throw any error. Thanks to BalusC, as I figured this out only due to his comment on changing the port number on MySQL. To add, the way to change the MySQL port number is NOT by changing the my.ini file but by running the MySQL instance config wizard under Start -> Programs -> MySQL Server 5.1 -> MySQL Server Instance Config Wizard. It was also interesting to note the code didn't throw any error when the port number wasn't specified and the program ran smoothly. Probably, JDBC connects to 3306 by default. If anyone has any particular idea about the same, please share.
For benefit of folks, here's the source code:
DBUtils.java
package DB;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import javax.sql.DataSource;
import org.apache.commons.dbcp.BasicDataSource;
import com.mysql.jdbc.Driver;
/**
* @author decorrea
*
*/
public class DBUtils {
public static String jdbc_driver_name = "com.mysql.jdbc.Driver";
private static String server_name ;
private static String database;
private static String username;
private static String password;
private static int maxActive = 20;
private static int maxIdle = 2 ;
public String getServer_name() {
return server_name;
}
public void setServer_name(String serverName) {
server_name = serverName;
}
public String getDatabase() {
return database;
}
public void setDatabase(String database) {
this.database = database;
}
public String getUsername() {
return username;
}
public void setUsername(String username) {
this.username = username;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
public static DataSource getDataSource(String server_name, String database, String username, String password){
BasicDataSource datasource = new BasicDataSource();
datasource.setDriverClassName(jdbc_driver_name);
String url = "jdbc:mysql://" + server_name + "/" + database;
System.out.println(url);
datasource.setUsername(username);
datasource.setPassword(password);
datasource.setUrl(url);
datasource.setMaxActive(maxActive);
datasource.setMaxIdle(maxIdle);
return datasource;
}
}
TwitterAPI.java
public class TwitterAPI {
private static String server_name = "localhost:7777";
private static String twitter_databse = "twitter";
private static String username = "root";
private static String password = "password";
public static String twitter_unique_usernames_file = "twitter_unique_usernames_file.txt";
public static String language_model_file = "C:\de\JARS\lingpipe-4.0.0\demos\models\langid-leipzig.classifier";
public static DataSource dataSource = DBUtils.getDataSource(server_name, twitter_databse, username, password);
public static Connection startDBConnection(String server_name, String database, String username, String password) {
//Set DB parameters
//DBUtils mysql_obj = setDBParams(server_name, database, username, password);
Connection connection = null;
//connection = mysql_obj.createConnection();
try {
connection = dataSource.getConnection();
} catch (SQLException e) {
e.printStackTrace();
}
return connection;
}
public static DBUtils setDBParams(String server_name, String database, String username, String password){
DBUtils mysql_obj = new DBUtils();
mysql_obj.setServer_name(server_name);
mysql_obj.setDatabase(database);
mysql_obj.setUsername(username);
mysql_obj.setPassword(password);
return mysql_obj;
}
public static String getTweets(BigInteger id){
Connection connection = startDBConnection(server_name,twitter_databse,username,password);
ResultSet resultSet = null;
String tweet = new String();
try {
Statement statement = connection.createStatement();
String query = SQL_queries.get_tweets_on_id + id.toString();
//Execute the query
resultSet = statement.executeQuery(query);
while(resultSet.next()){
tweet = resultSet.getString("content");
}
resultSet.close();
statement.close();
} catch (SQLException e) {
e.printStackTrace();
}
finally{
try {
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
return tweet;
}
Hope this helps.
edited Sep 28 '12 at 0:01
Mr_and_Mrs_D
15.9k23121258
answered Jul 9 '10 at 13:17
DexterDexter
4,30173351
So, I figured out the solution. I hadn't mentioned the port name in the url connection to the database.
String url = "jdbc:mysql://" + server_name + "/" + database;
Probably, hence it led to many leaking connections. Once done, I tried with the example given here. It now doesn't throw any error. Thanks to BalusC, as I figured this out only due to his comment on changing the port number on MySQL. To add, the way to change the MySQL port number is NOT by changing the my.ini file but by running the MySQL instance config wizard under Start -> Programs -> MySQL Server 5.1 -> MySQL Server Instance Config Wizard. It was also interesting to note the code didn't throw any error when the port number wasn't specified and the program ran smoothly. Probably, JDBC connects to 3306 by default. If anyone has any particular idea about the same, please share.
For benefit of folks, here's the source code:
DBUtils.java
package DB;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import javax.sql.DataSource;
import org.apache.commons.dbcp.BasicDataSource;
import com.mysql.jdbc.Driver;
/**
* @author decorrea
*
*/
public class DBUtils {
public static String jdbc_driver_name = "com.mysql.jdbc.Driver";
private static String server_name ;
private static String database;
private static String username;
private static String password;
private static int maxActive = 20;
private static int maxIdle = 2 ;
public String getServer_name() {
return server_name;
}
public void setServer_name(String serverName) {
server_name = serverName;
}
public String getDatabase() {
return database;
}
public void setDatabase(String database) {
this.database = database;
}
public String getUsername() {
return username;
}
public void setUsername(String username) {
this.username = username;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
public static DataSource getDataSource(String server_name, String database, String username, String password){
BasicDataSource datasource = new BasicDataSource();
datasource.setDriverClassName(jdbc_driver_name);
String url = "jdbc:mysql://" + server_name + "/" + database;
System.out.println(url);
datasource.setUsername(username);
datasource.setPassword(password);
datasource.setUrl(url);
datasource.setMaxActive(maxActive);
datasource.setMaxIdle(maxIdle);
return datasource;
}
}
TwitterAPI.java
public class TwitterAPI {
private static String server_name = "localhost:7777";
private static String twitter_databse = "twitter";
private static String username = "root";
private static String password = "password";
public static String twitter_unique_usernames_file = "twitter_unique_usernames_file.txt";
public static String language_model_file = "C:\de\JARS\lingpipe-4.0.0\demos\models\langid-leipzig.classifier";
public static DataSource dataSource = DBUtils.getDataSource(server_name, twitter_databse, username, password);
public static Connection startDBConnection(String server_name, String database, String username, String password) {
//Set DB parameters
//DBUtils mysql_obj = setDBParams(server_name, database, username, password);
Connection connection = null;
//connection = mysql_obj.createConnection();
try {
connection = dataSource.getConnection();
} catch (SQLException e) {
e.printStackTrace();
}
return connection;
}
public static DBUtils setDBParams(String server_name, String database, String username, String password){
DBUtils mysql_obj = new DBUtils();
mysql_obj.setServer_name(server_name);
mysql_obj.setDatabase(database);
mysql_obj.setUsername(username);
mysql_obj.setPassword(password);
return mysql_obj;
}
public static String getTweets(BigInteger id){
Connection connection = startDBConnection(server_name,twitter_databse,username,password);
ResultSet resultSet = null;
String tweet = new String();
try {
Statement statement = connection.createStatement();
String query = SQL_queries.get_tweets_on_id + id.toString();
//Execute the query
resultSet = statement.executeQuery(query);
while(resultSet.next()){
tweet = resultSet.getString("content");
}
resultSet.close();
statement.close();
} catch (SQLException e) {
e.printStackTrace();
}
finally{
try {
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
return tweet;
}
Hope this helps.
edited Sep 28 '12 at 0:01
Mr_and_Mrs_D
15.9k23121258
answered Jul 9 '10 at 13:17
DexterDexter
4,30173351
edited Sep 28 '12 at 0:01
Mr_and_Mrs_D
15.9k23121258
edited Sep 28 '12 at 0:01
Mr_and_Mrs_D
15.9k23121258
edited Sep 28 '12 at 0:01
Mr_and_Mrs_D
15.9k23121258
15.9k23121258
answered Jul 9 '10 at 13:17
DexterDexter
4,30173351
answered Jul 9 '10 at 13:17
DexterDexter
4,30173351
answered Jul 9 '10 at 13:17
DexterDexter
4,30173351
4,30173351
Interesting, I didn't expect that omitting the port number would lead to this behaviour... Does it also happen when you don't use a connection pool?
– BalusC
Jul 9 '10 at 21:58
BalusC, With port number + connection pool : no issues. Without port number + connection pool : can't connect. Without port number + Without connection pool : Can run for a limited time but soon exceeds max_number_of_connections. With port number + without connection pool: haven't tried as yet.
– Dexter
Jul 10 '10 at 9:53
2
For whoever is still wondering where this port was set check theserver_namefield inTwitterAPI
– Mr_and_Mrs_D
Sep 28 '12 at 0:06
add a comment |
Interesting, I didn't expect that omitting the port number would lead to this behaviour... Does it also happen when you don't use a connection pool?
– BalusC
Jul 9 '10 at 21:58
BalusC, With port number + connection pool : no issues. Without port number + connection pool : can't connect. Without port number + Without connection pool : Can run for a limited time but soon exceeds max_number_of_connections. With port number + without connection pool: haven't tried as yet.
– Dexter
Jul 10 '10 at 9:53
2
For whoever is still wondering where this port was set check theserver_namefield inTwitterAPI
– Mr_and_Mrs_D
Sep 28 '12 at 0:06
Interesting, I didn't expect that omitting the port number would lead to this behaviour... Does it also happen when you don't use a connection pool?
– BalusC
Jul 9 '10 at 21:58
Interesting, I didn't expect that omitting the port number would lead to this behaviour... Does it also happen when you don't use a connection pool?
– BalusC
Jul 9 '10 at 21:58
BalusC, With port number + connection pool : no issues. Without port number + connection pool : can't connect. Without port number + Without connection pool : Can run for a limited time but soon exceeds max_number_of_connections. With port number + without connection pool: haven't tried as yet.
– Dexter
Jul 10 '10 at 9:53
BalusC, With port number + connection pool : no issues. Without port number + connection pool : can't connect. Without port number + Without connection pool : Can run for a limited time but soon exceeds max_number_of_connections. With port number + without connection pool: haven't tried as yet.
– Dexter
Jul 10 '10 at 9:53
2
2
For whoever is still wondering where this port was set check the
server_name field in TwitterAPI– Mr_and_Mrs_D
Sep 28 '12 at 0:06
For whoever is still wondering where this port was set check the
server_name field in TwitterAPI– Mr_and_Mrs_D
Sep 28 '12 at 0:06
add a comment |
Any particular reason for the edit ?
– Dexter
Jul 8 '10 at 13:24
1
you used
<pre><code>which caused nested scrollbars in code. Just indent with 4 spaces or select and then press010101button in message editor toolbar orCtrl+Kkey to format code nicely.– BalusC
Jul 8 '10 at 13:25
BalusC Thanks for the tip. Will keep in mind.
– Dexter
Jul 8 '10 at 13:29