5

I am trying to simulate an scenario where my service loses connection to a database and cannot do an INSERT by blocking the connection with iptables, but I can't make the executeQuery() method to timeout.

What I did is setting a timeout for the PreparedStatement like this statement.setQueryTimeout(5). Here is the code.

HikariConfig config = new HikariConfig();

config.setJdbcUrl("jdbc:mysql://db-url/db");
config.setUsername("user");
config.setPassword("passwd");

config.setMaximumPoolSize(10);
config.setAutoCommit(false);
config.setConnectionTimeout(5000);
config.setDriverClassName("com.mysql.jdbc.Driver");
config.addDataSourceProperty("cachePrepStmts", "true");
config.addDataSourceProperty("prepStmtCacheSize", "250");
config.addDataSourceProperty("prepStmtCacheSqlLimit", "2048");
config.addDataSourceProperty("autoReconnect", "true");

final HikariDataSource pool = new HikariDataSource(config);

final String query = "INSERT INTO xtable VALUES (?, ?, ?, ?, ?)";

try ( Connection connection = pool.getConnection() )
{
    try ( PreparedStatement statement = connection.prepareStatement(query) )
    {
        // this is what I expect to work
        statement.setQueryTimeout(5);

        for ( Info info : infos )
        {
            statement.setString(1, info.getValue1());
            statement.setString(2, info.getValue2());
            statement.setString(3, info.getValue3());
            statement.setString(4, info.getValue4());
            statement.setString(5, info.getValue5());

            try
            {
                System.out.println("Waiting");
                Thread.sleep(5000);
                // I use this sleep to ban the database url with iptables
                // to simulate a disconnection
                System.out.println("Waited");
            }
            catch ( InterruptedException e )
            {
                e.printStackTrace();
            }

            System.out.println("Before executeQuery");
            statement.executeQuery();
            // I assumed that this would timeout after 5 seconds
            // But it never reaches the next System.out.print
            System.out.println("After executeQuery");
        }
    }

    System.out.println("Before commit");
    connection.commit();
    System.out.println("After commit");
}
catch ( SQLException e )
{
    log.error("Couldn't execute query", e);
}

The output would be:

Waiting
Waited
Before executeQuery

and then it hangs forever... What can I do to make it throw an Exception?

fcasanova
  • 197
  • 1
  • 4
  • 15

2 Answers2

3

Call Connection.setNetworkTimeout() in a try-finally.

private final static Executor immediateExecutor = Runnable::run;

try ( Connection connection = pool.getConnection() ) {
   int timeout = connection.getNetworkTimeout();
   connection.setNetworkTimeout(immediateExecutor, TimeUnit.SECONDS.toMillis(5));
   ...
   try (PreparedStatement...) {
      ...
   }
   finally {
      connection.setNetworkTimeout(timeout);
   }
}
finally {
   ...
}

You are suffering from unacknowledged TCP traffic, which can hang a connection if a network timeout is not set.

brettw
  • 10,664
  • 2
  • 42
  • 59
  • It seems to be working just fine, thanks! Two things bother me: one, in the connection.close() it thows and uncatched Exception because it cannot be closed since the network timeout happened. Also: do I have to set this timeout every time? If I have a Hikari pool it seems nonsense to set it for every time I need a connection, right? I mean, mostly because I'd keep on creating Executors. – fcasanova Feb 09 '18 at 15:30
  • 1
    Another option is to set network timeout at the driver level — globally. In that case, I would suggest setting it to 3x your longest query time. – brettw Feb 11 '18 at 12:41
  • 1
    Just one last note. You do not need to keep creating Executors. As the example above, you can (should) create a static Executor, and that executor (in the case of MySQL) should execute the Runnable immediately, rather than queueing or passing off to another thread. – brettw Feb 13 '18 at 22:20
  • Good advise, I'll try that. I couldn't set the network timeout at the jdbc driver level. Thanks – fcasanova Feb 14 '18 at 10:21
-1

You always could:

try{
     preparedstatement = connection.prepareStatement(query);
     preparedstatement.setQueryTimeout(seconds);
}

Instead of what you did. Maybe that would work better. Same thing goes for the:

Connection connection = pool.getConnection()
Thabiso Motswagole
  • 146
  • 1
  • 2
  • 17