Java – the storm collapsed 23 hours later

Hello, I have a basic storm application setup, which receives a tweet stream and stores them in the MySQL database The application worked well for about 23 hours before the following errors began:

sql Exception
sql State: 08003

He did it several times and died I am connecting to the database from Java using standard jbdc connector The function codes for storing and setting DB connections 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 apparently crashed because of the 08003 error, I decided that if it threw the error, it should retry the connection settings, but it didn't help Can anyone point me in the right direction to solve this problem?

Solution

There are basically two problems to be solved:

>Why is the connection lost first? > Why not try to reconnect successfully?

For the first question, you should check the MySQL log to see if there are any signs In addition, in the (repeat) "state

Check SQL exception before "3" exception The latter just tells you that the previous connection has died

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

>The MySQL server has timed out its connection due to inactivity If this is a problem, you can change the connection timeout in the MySQL configuration. > Your application may be slowly leaking JDBC connections

For the second question, the general method is correct, but your code does not match the description In fact, every time the execute method is called, it always seems to try to establish a new database connection This makes reconnection calls in exception handlers meaningless (Otoh, the code shows that someone has been "beating it" to try to make it work... This is probably part of the problem.)

I'll check if setupconnection is called when needed and look for any exceptions that might be thrown In addition, you should make sure to explicitly close () dead connection objects... And reconsider / recode your connection management so that it does not leak

For record purposes, there is a connection URL parameter called "autoreconnect", which has been used in the past to "handle" lost connections Unfortunately, the initial implementation was unsafe, so they effectively disabled it; For more information, see this question: why does autoreconnect = true not see to work?