Dear All,
I got an application which have many sql select statement and some are done withing the outler select statement. The problem is that sometimes I get this error com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: No operations allowed after statement closed.? What could be the cause is it because I am using the statements wrongly?
newbie14 0 Posting Pro
Anuradha Mandal -28 Junior Poster
I dont know whats wrong. but it could be Java database driver problem.Do you have installed the SQL connector for Java?
newbie14 0 Posting Pro
Dear Mandal,
Yes I have install. The best part after I restart then all is fine. Why ya?
peter_budo 2,532 Code tags enforcer Team Colleague Featured Poster
@Anuradha Mandal then do not reply or use Google search to come up with some reasonable answer.
@newbie14 sound like you are relaying on open connection that on MySQL in ideal situation will be open for up to 8 hours but then it is closed. You can amend that with db config change of wait_timeout. Better solution is to use connection pool using a pooling API such as Apache DBCP or move application to JPA or Hibernate (down to personal preferences)
Anuradha Mandal commented: N/A. +0
newbie14 0 Posting Pro
Dear Peter,
Beside that point I would like to ask is my use of statements is correct or not? But I am curious is why only sometimes it happens then once I reset the server everthing moves fine that is why I cant figure out if my syntax is correct?
peter_budo 2,532 Code tags enforcer Team Colleague Featured Poster
Beside that point I would like to ask is my use of statements is correct or not?
>> How would I know, I didn't see any code
But I am curious is why only sometimes it happens then once I reset the server everthing moves fine that is why I cant figure out if my syntax is correct?
>> There may be a period of time when no request is sent to DB for 8 hours and because of that connection is closed. You can easily try to monitor this by logging any DB request into log file and monitoring what exact error is thrown, from where (sometimes this may pin-point problem in the query)
newbie14 0 Posting Pro
Dear Peter,
Below is my codes. I have remove all the actual statements. Right below it the error. So the error is now pointed here ResultSet rs1 = stmt.executeQuery(selectQuery1); Yes you are quite right when there is time where is no request made for nearly 8 hours plus then this happens. So what is the cause and what should I do ? Thank you.
public void run()
{
// open database connection
try
{
dbconn = DriverManager.getConnection("jdbc:mysql://localhost:3306/***?"+"user=******&password=*****");
stmt = dbconn.createStatement();
while (true)
{
try
{
String selectQuery1 = //Select Statement
ResultSet rs1 = stmt.executeQuery(selectQuery1);
if(rs1.next())
{
associateID = rs1.getInt("associateID");
//
}
int geoFenceIDFound=0;
if(associateID>0)
{
geoFenceIDFound=0;
String selectQuery3 = //select statement
ResultSet rs3 = stmt.executeQuery(selectQuery3);
stmt1 = dbconn.createStatement();
stmt2 = dbconn.createStatement();
while(rs3.next())
{
geoFenceID = rs3.getInt("geoFenceID");
if(geoFenceIDFound>0)
{
break;
}
else
{
if(geoFenceType.equals("POINT"))
{
String selectQuery4= //select statement
ResultSet rs4 = stmt1.executeQuery(selectQuery4);
if(rs4.next())
{
geoFenceDistance = rs4.getDouble("distance");
if(geoFenceDistance*1000<=geoFenceRadius)
{
geoFenceIDFound=geoFenceID;
if(lastGeoFenceID==geoFenceID) {
{
break;
}
else
{
if(previousGeoFenceID==geoFenceID &&previousTimeDifferenceInt<0) //check the funny problem double entry back into the geo fence
{
lastGeoFenceID=geoFenceID;
break;
}
else
{
String updateQuery2 = //update statement
count = stmt.executeUpdate(updateQuery2);
if(lastGeoFenceID>0)
{
String insertQuery2 = //insert statement
count = stmt.executeUpdate(insertQuery2);
String insertQuery3 =//insert statement;
count = stmt.executeUpdate(insertQuery3);
}
else
{
String insertQuery3 =//insert statement;
count = stmt.executeUpdate(insertQuery3);
}
break;
}
}
}
}
}
else
{
String selectQuery4 =//select statement
ResultSet rs4 = stmt1.executeQuery(selectQuery4);
if(rs4.next())
{
//rs4.getInt(("geoFenceStatus")
if(rs4.getInt("geoFenceStatus")==1)
{
geoFenceIDFound=geoFenceID;
if(lastGeoFenceID==geoFenceID)
{
break;
}
else
{
if(previousGeoFenceID==geoFenceID &&previousTimeDifferenceInt<0) //check the funny problem double entry back into the geo fence
{
lastGeoFenceID=geoFenceID;
break;
}
else
{
String updateQuery2 = //update statement
count = stmt.executeUpdate(updateQuery2);
if(lastGeoFenceID>0)
{
String insertQuery2 =//insert statement;
count = stmt.executeUpdate(insertQuery2);
String insertQuery3 =//insert statement;
count = stmt.executeUpdate(insertQuery3);
}
else
{
String insertQuery3 =//insert statement;
count = stmt.executeUpdate(insertQuery3);
}
break;
}
}
}
}
}
}
}//while next compare all the geoFences.
if(geoFenceIDFound==0)
{
geoFenceID=0;
if(lastGeoFenceID>0)
{
String updateQuery2 = //update statement
count = stmt.executeUpdate(updateQuery2);
String insertQuery3 =//insert statement
count = stmt.executeUpdate(insertQuery3);
}
}
if(!eventAlertType.equals(""))
{
if(evenAlertCode.substring(0,1).equals("B"))
{
String selectQuery4 = //select statement
ResultSet rs4 = stmt.executeQuery(selectQuery4);
while(rs4.next())
{
slaveEventUnitID = rs4.getInt("slaveID");
}
String insertQuery4 =//insert statement;
count = stmt.executeUpdate(insertQuery4);
}
else
{
String insertQuery5 =//insert statement;
count = stmt.executeUpdate(insertQuery5);
if(evenAlertCode.equals("602"))
{
String updateQuery3 = //update statement;
count = stmt.executeUpdate(updateQuery3);
}
if(evenAlertCode.equals("612"))
{
String updateQuery4 = //update statement;
count = stmt.executeUpdate(updateQuery4);
String updateQuery5 = //update statement;
count = stmt.executeUpdate(updateQuery5);
String updateQuery6 = //update statement;
count = stmt.executeUpdate(updateQuery6);
String updateQuery7 = //update statement;
count = stmt.executeUpdate(updateQuery7);
String updateQuery8 = //update statement;
count = stmt.executeUpdate(updateQuery8);
}
}
}
}
String selectQuery2 = //select statement;
String updateQuery = "";
if(rs2.next())
{
updateQuery = //update statement
}
else
{
updateQuery = //update statement
}
count = stmt.executeUpdate(updateQuery);
String insertQuery = //insert statement;
count = stmt.executeUpdate(insertQuery);
if(addExist!=-1)
{
for(int iSlave=1; iSlave<slave.length ; iSlave++)
{
String insertQuery2 = //insert statement
count = stmt.executeUpdate(insertQuery2);
}
}
}
catch (Exception e)
{
e.printStackTrace();
}
}//while true
}
catch (SQLException ex)
{
System.out.println("MyError:Error SQL Exception : "+ex.toString());
ex.printStackTrace(System.out);
}
finally
{
try
{
if ( stmt != null )
{
stmt.close();
}
else
{
System.out.println("MyError:stmt is null in finally close");
}
}
catch(SQLException ex)
{
System.out.println("MyError:SQLException has been caught for stmt close");
ex.printStackTrace(System.out);
}
try
{
if ( dbconn != null )
{
dbconn.close();
}
else
{
//logger.log(Level.SEVERE, "MyError:dbconn is null in finally close", "");
System.out.println("MyError:dbconn is null in finally close");
}
}
catch(SQLException ex)
{
System.out.println("MyError:SQLException has been caught for dbconn close");
ex.printStackTrace(System.out);
}
}//finally
}//runHere is the error.
com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: No operations allowed after statement closed.
Service commServer|11-08-29 21:20:11| at sun.reflect.GeneratedConstructorAccessor1.newInstance(Unknown Source)
Service commServer|11-08-29 21:20:11| at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructo rAccessorImpl.java:27)
Service commServer|11-08-29 21:20:11| at java.lang.reflect.Constructor.newInstance(Constructor.java:513)
Service commServer|11-08-29 21:20:11| at com.mysql.jdbc.Util.handleNewInstance(Util.java:407)
Service commServer|11-08-29 21:20:11| at com.mysql.jdbc.Util.getInstance(Util.java:382)
Service commServer|11-08-29 21:20:11| at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1013)
Service commServer|11-08-29 21:20:11| at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:987)
Service commServer|11-08-29 21:20:11| at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:982)
Service commServer|11-08-29 21:20:11| at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:927)
Service commServer|11-08-29 21:20:11| at com.mysql.jdbc.StatementImpl.checkClosed(StatementImpl.java:402)
Service commServer|11-08-29 21:20:11| at com.mysql.jdbc.StatementImpl.executeQuery(StatementImpl.java:1339)
Service commServer|11-08-29 21:20:11| at commServer$DatabaseProcessor.run(commServer.java:312)
Service commServer|11-08-29 21:20:11| at java.lang.Thread.run(Thread.java:619) mKorbel 274 Veteran Poster
you closing "stmt" in one of another subquery statements
1/ you have to declare stmt twice because concur thread that used statemet can closed that as you can see in your error from JDBC Driver
2/ declare only variables valid for concrete try-catch-finally block or void
newbie14 0 Posting Pro
Dear Korbel,
I am closing stmt only toward the end in the finally.
1. I do not get you why you ask me to declare stmt twice would not that cause another error? So where to declare another stmt? When you say this "concur thread that used statemet can closed that as you can see in your error from JDBC Driver" which part of the error is pointing this? I though each thread should not disturb another thread's connection right?
2. What are valid variable? Can you describe what is my mistakes further? Beside that am I using stmt properly because I feel I am using it for too many queries?
if ( stmt != null )
{
stmt.close();
}
else
{
System.out.println("MyError:stmt is null in finally close");
}
JamesCherrill 4,733 Most Valuable Poster Team Colleague Featured Poster
Hi guys.
I'm jumping in here because I was involved in the original architecture of this app and want to share a bit more background.
Transactions arrive unpredictably from multiple clients at a server. The server simply captures the transaction and adds it to a LinkedBlockingQueue.
A separate database thread creates a connection, then loops taking transactions from the queue and processing them sequentially. Thus a single connection may be used for many transactions, or may sit unused for some time while the processing loop waits taking the next transaction from the queu.
So how does any database limit on connection lifetime work? Is it total lifetime, or just idle time, or what?
Anyway, there's no difficulty checking for this problem as each transaction is taken from the queue, and re-opening the connection if necessary. Anyone see a problem with that?
Be a part of the DaniWeb community
We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.