Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

migrated from stackoverflow.com Jun 21 '12 at 21:22

This question came from our site for professional and enthusiast programmers.

    
not seeing jetty being the culprit here, good luck though –  jesse mcconnell Jun 16 '12 at 10:55
    
I think its the Bitronix transaction Manager thats causing the issue! They did a great job with Jetty 6! I am still trying to find a way to fix it! –  Valli Nanduri Jun 18 '12 at 13:47
    
@ValliNanduri ... the stack dump is helpful, but could you provide the code chunk, and perhaps the query/database interface that is causing this exception? –  swasheck Jun 21 '12 at 20:58
    
The initial error itself looks like the string "NULL" is attempting to be forced into an INTEGER column (NULL may be what you're looking for). Is this something that is coded, or is this user input? Losing connection with the database server is more problematic. Can you isolate some postgres errors in the error log around the time of when this behavior is observed? How about any Jetty error logs? Thanks –  swasheck Jun 21 '12 at 21:20
1  
The condition primary=null is invalid. You need to fix the SQL generation that causes this faulty syntax. It seems that the transaction manager than thinks because of the error that the connection is "broken" and needs to be closed. Postgres will not close a connection due to a syntax error. –  a_horse_with_no_name Jun 21 '12 at 21:26

1 Answer 1

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.