Servlet Code error: http 500 Internal Server Error

Question

I'm trying to run the following code but It always results in a "http 500 Internal Server Error"

Could someone help me debug this error

I just started learning Servlets and JSP..So please excuse me if I miss any details in the question. Looking in the MYSQL database error logs, I found the following entries:

Aborted connection 44 to db: 'sakila' user: 'root' host: 'localhost' (Got an error reading communication packets)

protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
    // TODO Auto-generated method stub
    response.setContentType("text/html");
    PrintWriter out=response.getWriter();
    final String DB_URL="jdbc:mysql://localhost:3306/sakila";
    final String user="root";
    final String password="pass1234";
    Statement stmt=null;
    try {
        Class.forName("com.mysql.jdbc.Driver");
    } catch (ClassNotFoundException e) {
        // TODO Auto-generated catch block
        out.println("Cannot load driver");
    }
    Connection conn=null;

    try {
        conn=DriverManager.getConnection(DB_URL,user,password);
    } catch (SQLException e) {
        // TODO Auto-generated catch block
        out.println("Cannot Connect to Database");
    }

    //out.print("Connected to Database");
    out.println("<html>");
    out.println("<body>");
    String str= "SELECT actor_id, first_name last_name FROM temp where actor_id='1';";

    try {
        ResultSet rs=stmt.executeQuery(str);
        while(rs.next()){
            out.println(rs.getString("actor_id"));

        }

    } catch (SQLException e1) {
        // TODO Auto-generated catch block
        e1.printStackTrace();
    }       
    /*
    try {
        ResultSet rs;
        rs = stmt.executeQuery(str);
        while(rs.next()){
            int i=rs.getInt("actor_id");
            String fn= rs.getString("first_name");
            String ln=rs.getString("last_name");

            out.print(i+"::");
            out.print(fn+"::");
            out.print(ln+"::");
        }
    } catch (SQLException e1) {
        // TODO Auto-generated catch block
        e1.printStackTrace();
    }

    */
    out.print("hkshfdkhfakfshdkha");




    try {
        conn.close();
    } catch (SQLException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    }

    out.println("</body>");
    out.println("</html>");


}

Do not catch and ignore exceptions. Printing the exception without terminating process flow is still *ignoring* the exception. In addition, without **looking at the server log**, where the actual error has been written, it's impossible to say exactly what is causing the error. We can guess (others have in answer below), but there are really way too many possibilities for that to be useful. — Andreas, Apr 08 '17 at 03:37
I figured the issue. I forgot to instantiate the Statement object: stmt=conn.createStatement(); — avg998877, Apr 08 '17 at 14:57

score 2 · Answer 1 · edited May 23 '17 at 12:10

If you check your application logs, it is my assumption that you will see the stacktrace generated by

e1.printStackTrace();

This is because you have an error in you SQL syntax.

SELECT actor_id, first_name last_name FROM temp where actor_id='1';
                          /\ add missing comma

On a side note - you should not be establishing the connection to the databse inside each request. This slows everything down.

Instead, you should be using a connection pool, I recommend C3P0.

The reason for abrupt termination in your database logs, is because your application throws an exception and abandons the connection without properly closing it.

Thanks Matt, I will give the Connection-pool a try. (Though I haven't read much into it though.) — avg998877, Apr 08 '17 at 13:07

score 2 · Answer 2 · answered Apr 08 '17 at 03:33

Aborted connection 44 to db: 'sakila' user: 'root' host: 'localhost' (Got an error reading communication packets)

This error trace is appearing in your console because you are trying to making new db connection to the mysql database on every doGet() request without properly closing your db connection.

And thats why whenever a communication error occurs it increments the status counter for either Aborted_clients or Aborted_connects, which describe the number of connections that were aborted because the client died without closing the connection properly and the number of failed attempts to connect to MySQL server (respectively).

Out of the various reasons causing this issue, here are few important ones that you might want to check.

The client connected successfully but terminated improperly (and may relate to not closing the connection properly)
The client slept for longer than the defined wait_timeout or interactive_timeout seconds (which ends up causing the connection to sleep for wait_timeout seconds and then the connection gets forcibly closed by the MySQL server)
The client terminated abnormally or exceeded the max_allowed_packet for queries

So as right mentioned by @Matt Clark you should be going for a connection pool mechanism to avoid this issue and also to follow best pratices around interfacing with databases.

Thank you @mhasan. I will try to implement this in a connection-pool-mechanism. — avg998877, Apr 08 '17 at 13:06

Servlet Code error: http 500 Internal Server Error

2 Answers2