infinite loop "hangs" after some iterations in java code during mysql query

Question

I have a long piece of code in java which uses selenium webdriver and firefox to test my website. Pardon me if I can't reproduce it here. It has an infinite while loop to keep doing its function repeatedly. Thats what its supposed to do. Also, I don't use multi threading.

1. Sometimes, it gets stuck. I use a windows system and the code runs on command prompt. When it gets stuck, no errors or exceptions are thrown. Its something like "it hangs" (only the window in which the code runs hangs). Then I have to use CTRL + C . Sometimes it resumes working after that, other times it gets terminated and I restart it. It works fine but after some loops it "hangs" again. Also, I've noticed that its usually during the execution of one of the methods querying mysql database.

2. The code runs an infinite loop. Each time, it queries the mysql database, fetches a value(whose 'status' field is not 'done') from a particular table (one value in each loop) and proceeds with testing with this value.At the end of the loop, the table is updated (the column 'status' is set to 'done' for that value). When there are no new values having 'status' not equal to 'done' in that particular table, it should ideally display "NO NEW VALUE". However, after all the values have been used, it simply takes up the last used value (even though its status is updated to 'done' at the end of previous loop) and goes ahead. I then have to terminate the execution and run the code again. This time when the infinite loop begins, it queries the database and correctly displays "NO NEW VALUE", queries again, displays the message again and so on(which is what it should do)

I close the sql connection using con.close().

It appears that after running the loop for a few times, some resource is getting exhausted somewhere. But this is only a wild guess.

Can anyone suggest what the problem is and how do I fix it ?

Below is a relevant piece of code :

try{
        String sql = "select something from somewhere where id = ? and          is_deleted = '0';";

        System.out.println("\n"+sql + "\n? = " + pID);

        PreparedStatement selQuery1 = conn.prepareStatement(sql);

        selQuery1.setString(1, pID);

        ResultSet rs1 = selQuery1.executeQuery();

        //Extract data from result set
        while(rs1.next() && i1<6){
            //do something


        }//end while loop

        String sql2 = "select something2 from somewhere2 where id = ? and is_deleted = '0';";
        System.out.println("\n"+sql2 + "\n? = " + pjID);


        PreparedStatement selQuery2 = conn.prepareStatement(sql2);
        selQuery2.setString(1, pjID);
        ResultSet rs2 = selQuery2.executeQuery();



        //Extract data from result set
        while(rs2.next() && i1<6){
            //do something

        }//end while loop

        System.out.println("\nDone.");

        conn.close();
    }catch (SQLException e) {
        flag=false;
}

Please note that no exceptions are thrown anywhere. The window in which the code is running just freezes (that too once in while) after displaying both the query statements.

Answer 1

I also faced the same problem recently. But in my case the issue was with indexes. I am just pointing out here so that it can be helpful to other folks.

In my case I am fetching the menu items from MenuMaster table from database. So after successfully log in, I am hitting a database to fetch the menu items using MySQL connector driver. Here I need to fetch parent menu with their child menus. In my query, in where clause I have not used any primary key or Unique key. So, it was taking a long time. So just make an index of that key, and it worked as charm...

Answer 2

I forgot to close the query and the resultset. Just closing the connection should implicitly close the query and resultset but it doesn't work always.