Storm Crashing after 23 hours

Question

Hello all I have a basic Storm application set up where it receives a stream of tweets and stores them in a MySQL database. The application works great for the first ~23 hours or so then it starts giving the following error:

SQL Exception
SQL State: 08003

After it does this a few times it dies. I'm using the standard JBDC connector to connect to the database from Java. The code for the functions for storing and setting up the DB connection are as follows:

private String _db="";
private Connection conn = null;
private PreparedStatement pst = null;

public ArchiveBolt(String db){
    _db = db;
}

private void setupConnection() {
    //Connect to the database
    try {
        Class.forName("com.mysql.jdbc.Driver");
        conn = DriverManager.getConnection("jdbc:mysql://localhost:8889/twitter_recording", "root", "root");
    } catch (Exception e){
        e.printStackTrace();
    }
}

public void execute(Tuple tuple, BasicOutputCollector collector) {

    Status s = (Status) tuple.getValue(0);

    //setup the connection on the first run through or if the connection got closed down
    try {
        setupConnection();
    } catch (Exception e) {
        // TODO: handle exception
        System.out.println(e.toString());
    }


    try {

        pst = conn.prepareStatement("INSERT INTO " + _db + " (tweet)" +
                                    "VALUES (?);");

        pst.setString(1, s.toString());

        //execute the SQL
        pst.executeUpdate();

    } catch (SQLException ex) {
        // handle any errors
        System.out.println("SQLException: " + ex.getMessage());
        System.out.println("SQLState: " + ex.getSQLState());
        System.out.println("VendorError: " + ex.getErrorCode());

        if(ex.getSQLState().equals("08003")){
            setupConnection();
        }

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

After it became apparent that it was crashing because of a 08003 error I decided that if it threw that error it should retry the set up of the connection, however that didn't help either. Could anyone point me in the right direction for solving this issue?

Answer 1

After it became apparent that it was crashing because of a 08003 error I decided that if it threw that error it should retry the set up of the connection, however that didn't help either. Could anyone point me in the right direction for solving this issue?

There are basically two problems here that need to be solved:

• Why are the connections getting lost in the first place?
• Why isn't your attempt to reconnect succeeding?

For the first problem, you should take a look at the MySQL logs to see if there are any indications there. And also, check for SQL exceptions immediately prior to the (repeated) "state 080003" exceptions. The latter are simply telling you that the connection has died previously.

My guess is that the problem is one of the following:

• The MySQL server has timed out the connection due to inactivity. You can change the connection timeout in the MySQL configs if this is the problem.

• Your application may be slowly leaking JDBC connections.

For the second problem, the general approach is correct, but your code doesn't match the description. In fact, it looks like it is always trying to establish a new database connection each time your execute method is called. This renders the reconnection call in your exception handler pointless. (OTOH, the code show signs that someone has been "beating on it" to try to get it to work ... and that could well be part of the problem.)

I would check that setupConnection is being called when it needs to be, and look for any exception that might be thrown. In addition, you should make sure that you explicitly close() the dead connection object ... and rethink / recode your connection management so that it doesn't leak.

---

For the record, there is a connection URL parameter called "autoReconnect" that in the distant past used to "deal" with lost connections. Unfortunately, the original implementation was unsafe, so they effectively disabled it; see this Question for details: Why does autoReconnect=true not seem to work?