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?

Dear Mandal,
Yes I have install. The best part after I restart then all is fine. Why ya?

@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)

Comments

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?

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)

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
     }//run

Here 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)

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

Edited 5 Years Ago by mKorbel: n/a

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");
	        }

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?

This article has been dead for over six months. Start a new discussion instead.