Exception at getConnection from pool
Hello,
I'm using this
...
import snaq.db.ConnectionPool;
...
Class.forName("com.mysql.jdbc.Driver");
cp =new ConnectionPool("bazen",0,0,0,databaseURL,"","");
...
conn = cp.getConnection();// line XXX
...
and sometimes I get this exception
** BEGIN NESTED EXCEPTION **
java.net.SocketException
MESSAGE: java.net.BindException: Address already in use: connect
STACKTRACE:
java.net.SocketException: java.net.BindException: Address already in use: connect
at com.mysql.jdbc.StandardSocketFactory.connect(StandardSocketFactory.java:156)
at com.mysql.jdbc.MysqlIO.<init>(MysqlIO.java:276)
at com.mysql.jdbc.Connection.createNewIO(Connection.java:2592)
at com.mysql.jdbc.Connection.<init>(Connection.java:1509)
at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:266)
at java.sql.DriverManager.getConnection(DriverManager.java:525)
at java.sql.DriverManager.getConnection(DriverManager.java:193)
at snaq.db.ConnectionPool.create(ConnectionPool.java:113)
at snaq.util.ObjectPool.checkOut(ObjectPool.java:138)
at snaq.db.ConnectionPool.getConnection(ConnectionPool.java:207)
at DBConnection.connOpen(line xxx)
** END NESTED EXCEPTION **
can someone help me and tell what I should do to stop getting this exception, some advice/solution? Thank you in advance.
[1653 byte] By [
boba5555a] at [2007-11-27 11:20:44]
# 1
I believe your code is off quite a bit. Still use forName, then you get a datasource (passing the url, userID, and password to its constructor).
then this:
Connection conn;
try{
conn=datasource.getConnection();
}catch(SQLException e){
e.printstacktrace();
} finally{
if(conn!=null)
conn.close();
}
There is no need to use connectionPool constructor. You can check on line for more examples.
# 2
looks like you're trying retrieve a connection that is already established...
did you write the ConnectionPool class yourself? or did you use API from somewhere?
if u wrote it yourself, i think you probably want to change a little bit on the getConnection() method, make sure you check whether the returning connection is in use, if yes, create a new connection, and place it into the ConnectionPool...
if u use API, there is nothing much you can do.. but make sure you're retrieving a new connection from the pool... maybe there is method for u to do that in the API..
hope this help..
# 3
@George123:
Connection conn ;
ConnectionPool cp;
// constructor
try{
Class.forName("com.mysql.jdbc.Driver");
cp = new ConnectionPool("bazen",0,0,0,databaseURL, "","");
}
catch(Exception ex) {
ex.printStackTrace();
}
I know I can use something like
conn = DriverManager.getConnection(databaseURL, "", "");
but I want to get connection from connection pool.
At each procedure which I use I call connOpen() and connClose(), which are
private synchronized void connOpen(){
if (opened)
return;
try{
conn = cp.getConnection(); // this is line xxx
opened = true;
}
catch(Exception ex) {
ex.printStackTrace();
}
}
private synchronized void connClose(){
if (!opened)
return;
try{
conn.close();
opened = false;
}
catch(Exception ex) {
ex.printStackTrace();
}
}
I still do not know what I should change :-(
@JWKC-5ive:
I am not using my own connection pool.
I read
Core Java 2 - Volume II - Advanced Features, 7th Edition (2004)
Thinking in Java, 3rd ed. Revision 4.0
some pages on java.sun, but didn't find solution for this kind of problem.
I think that problem appears when I try to get connection "very often". When I want to delete 1000 items from DB and send 100 requests per several seconds, it throws exception after about one minute. For each request I call method for deleting (each time I call connOpen() and connClose()). If it is wrong in my code, can I enlarge queue/memory of connection pool (I suppose it uses queue, but it is not so important)? Can you suggest me some solution, of course if you agree with me that problem becomes when I too often open and close connection... Also, after each use of statement and resultset, I close it.
Message was edited by:
boba5555
And maybe some dukes :)
# 4
deleting 1000 items from DB and send 100 requests per several seconds might be the cause of the problem. i haven't tried any of the public available database framework, but as far as i understand, each connection is (should be) treated as a synchronize connection, which means connection will only be released when the current job is done, and if all the connections in the pool are filled, the next connection request will be placed in a queue until the next available connection slot is opened.
my advice is, first of all, see whether you can reduce the amount of concurrent connection, if that is not possible, try to increase the connection pool size...
hope this help..
# 5
I think I can not reduce, because it can be random rows (with random data), so there is no some rule which I can use to filter query and send one query which will delete more rows.
How I can increase size of pool? How I can know which is current size?
# 6
which API r u currently using?
# 7
I hope this is about you asked me...
import java.sql.*;
import snaq.db.ConnectionPool;
...
Class.forName("com.mysql.jdbc.Driver");
...
# 8
ConnectionPool pool = new ConnectionPool(<poolname>,
<maxpool>,
<maxconn>,
<expiry>,
<url>,
<username>,
<password>);
when u create an instance of ConnectionPool, set the maxpool to something rather than 0... i'm not sure but i think the maxconn should be set to the same value as maxpool...
# 9
You got dukes. Thank you :)
Have you idea where I can check does maxonn and maxpool should have the same value? I can not find :-(
# 10
actually, i think in your case, u should allow the size of the maxconn be sightly higher than the maxpool... that way it will be safer... try to use this... maxconn = 60, and maxpool = 20.. and also, set the <expiry> to 180000 (in milliseconds) so that for any connection that is inactive for 180 seconds, it will be automatically released...
hope this help..
# 11
>> hope this help..
Me too :)
I will try that. Thank you again.
The best regards,
