Postgresql – Database Exceptions with Jetty 8.1.0 with BTM

postgresql

We are running Jetty 8.1.0 with BTM 2.1.2.

We are using Postgres as our DB.

Everything works great except that when we run any query with a condition where sometable's primary=null, the server loses its connection to the DB. The following is the exception we see:

org.postgresql.util.PSQLException: ERROR: invalid input syntax for
integer: "null"
at
…
…long stack dump ensues…
…
Jun 15, 2012
12:20:19 PM org.eclipse.jetty.server.AsyncHttpConnection handle INFO:
EndPoint making nolong progress:stack 100dump
SCEP@162b333{l(/74.93.245.209:60506)<->r(/10ensues.99.21.87:80),d=true,open=true,ishut=false,oshut=false,rb=false,wb=false,w=false,i=5r}-{AsyncHttpConnection@18fc7ca,g=HttpGenerator{s=3,h=-1,b=20909,c=-1},p=HttpParser{s=0,l=16,c=0},r=1}
AsyncHttpConnection@18fc7ca,g=HttpGenerator{s=3,h=-1,b=20909,c=-1},p=HttpParser{s=0,l=16,c=0},r=1
Jun 15, 2012 12:20:19 PM
org.eclipse.jetty.server.AsyncHttpConnection
handle WARNING: Closing EndPoint making no progress: 200
SCEP@162b333{l(/74.93.245.209:60506)<->r(/10.99.21.87:80),d=true,open=true,ishut=false,oshut=false,rb=false,wb=false,w=false,i=5r}-{AsyncHttpConnection@18fc7ca,g=HttpGenerator{s=3

Any query after this incident breaks with the following exception

It's happening on our Production server! What is causing this problem?

UPDATE:

Here is the code!

I am aware that we shouldn't query with primarykey = null as primary can never be null. But its a parameter and null validations were missed!

Surprisingly, Jetty 6 works great with it! Even after running this query, it has no problem at all. Its just Jetty 8-1-0 thats lossing DB connectivity permanently upon running such query. Until I restart the ser

String query = "select * from table where primarykey="+parameter; 
Statement lStatement   = null;  
Connection lConnection  = null;  
ResultSet lResultSet   = null;  
try  {
   lConnection = DBAccess.getConnection();   
   lStatement  = lConnection.createStatement();
   lResultSet  =  lStatement.executeQuery( query );     
   while ( lResultSet.next() )    {
       mLogger.info("The result set is : "+lResultSet.toString());    
   }
}  catch( Exception e )  {   
    e.printStackTrace();   
    mLogger.error("Exception occurred while trying to runQuery : "+e.getMessage());  
}  finally  {   
    DBAccess.closeResultSet( lResultSet );   
    DBAccess.closeStatement( lStatement );   
    DBAccess.closeDBConnection( lConnection );  
}

UPDATE

can Someone please help me with this. I am still looking for an answer. I am running into different problems day by day with Jetty 8-1-0.

I removed the Transaction management config in Jetty.xml and deployed with default JNDI config. Things were smooth for sometime.

Last week, the server started crying with "Too Many open files" issue. It got fixed once I increase the number of files that can be open on the server.
Today, Jetty is dead again with OutOfMemoryError.
Whats noteworthy here is the Memory leaks happen at the same place where the "Too May Open Files" error occurs. I am guess its the HTTPClient thats causing the problem. (Well, I will post the same in stackoverflow. I figure its not a DBA issue. But all these started at the same place. so, I want to keep a record of it here if that makes sense.

Best Answer

It looks like the null in the parameter variable is single quoted, like 'null'. In the context of SQL query nulls are going without quotation.

This is how it should be:

=# select 1 from t where i = null;
 ?column? 
----------
(0 rows)

And this is what you have:

=# select 1 from t where i = 'null';
ERROR:  invalid input syntax for integer: "null"
LINE 2: select 1 from t where i = 'null';

So you either need to remove any quotation from the parameter variable or to use parameter binding with the database adapter. The second option is preferable.

Related Question