Redshift server closes connection after 10 minutes

Question

I have a statement that takes about 20 minutes to run, which is of the form:

create table new_table diststyle key distkey(column1) sortkey(column2) 
as (select ....);

When I run it using an SQL IDE or with the psql command line client, the statement executes successfully but when I run it from my Java program, the server closes the connection after 10 minutes with the following exception:

    org.springframework.jdbc.UncategorizedSQLException: StatementCallback; uncategorized SQLException for SQL [create table new_table diststyle key distkey(column1) sortkey(column2) as (select ....);]; 
SQL state [HY000]; error code [600001]; [Amazon](600001) The server closed the connection.; 
nested exception is java.sql.SQLException: [Amazon](600001) The server closed the connection.
    at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:84) ~[spring-jdbc-4.3.4.RELEASE.jar:4.3.4.RELEASE]
    at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81) ~[spring-jdbc-4.3.4.RELEASE.jar:4.3.4.RELEASE]
    at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81) ~[spring-jdbc-4.3.4.RELEASE.jar:4.3.4.RELEASE]
    at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:419) ~[spring-jdbc-4.3.4.RELEASE.jar:4.3.4.RELEASE]
    at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:538) ~[spring-jdbc-4.3.4.RELEASE.jar:4.3.4.RELEASE]
    at com.abc.mypackage.MyClass.myMethod(Myclass.java:123) [classes/:?]
Caused by: java.sql.SQLException: [Amazon](600001) The server closed the connection.
    at com.amazon.support.channels.TLSSocketChannel.read(Unknown Source) ~[?:?]
Caused by: com.amazon.support.exceptions.GeneralException: [Amazon](600001) The server closed the connection.
    at com.amazon.support.channels.TLSSocketChannel.read(Unknown Source) ~[?:?]

I'm using org.apache.commons.dbcp2.BasicDataSource to create connections. I've tried extending the timeout via defaultQueryTimeout, maxConnLifetimeMillis and socketTimeout but to no avail. The server keeps closing the connection after the same 10 minutes.

    dataSource = new BasicDataSource();
    dataSource.setUsername(dbUser);
    dataSource.setPassword(dbPassword);
    dataSource.setUrl(dbUrl);
    dataSource.setDefaultAutoCommit(true);
    dataSource.setTestOnBorrow(true);
    dataSource.setTestOnReturn(true);
    dataSource.setDriverClassName("com.amazon.redshift.jdbc41.Driver");
    dataSource.setDefaultQueryTimeout(7200);
    dataSource.setMaxConnLifetimeMillis(7200000);
    dataSource.addConnectionProperty("socketTimeout", "7200");

How do I keep the connection alive for longer?

P.S. I do not have any problems establishing connections and running queries that take less than 10 minutes to finish.

Answer 1

You might want to extend your socket timeout.

Current it is 7200ms only:

dataSource.addConnectionProperty("socketTimeout", "7200");

Answer 2

check if the redshift server have a workload management policy that is timing out queries after 10 minutes. your java code might be setting this policy

Answer 3

You need to set the tcpKeepAlive time to 1 min or less while getting the connection to redshift cluster.

               Properties props = new Properties();
               props.setProperty("user", user);
               props.setProperty("password", password);
               props.setProperty("tcpKeepAlive", "true");
               props.setProperty("TCPKeepAliveMinutes", "1");
               DriverManager.getConnection("jdbc:redshift://"+endpoint+":"
               +port+"/"+database, props);

Answer 4

OP here- I was able to make it work by writing wrappers over BasicDataSource and Connection to poll active connection with isValid(int) every few minutes (any frequency more than once-per-10-minutes works). In hindsight, it seems that most timeout-related properties on BasicDataSource apply to connections which are in the pool but are not being used. setDefaultQueryTimeout and tcpKeepAlive + TCPKeepAliveMinutes did not work.

P.S. It has been a while since I resolved this problem and I do not have the code for the wrappers now. Here's a brief description of the wrappers.

WrappedConnection class takes a Connection object (conn) and a TimerTask object (timerTask) in its constructor and implements the Connection interface by simply calling the methods from conn. timerTask calls this.isValid(100) every few minutes as long as the connection is active. WrappedConnection.close stops timerTask and then calls conn.close.

WrappedBasicDataSource implements the DataSource interface, redirecting methods to a BasicDataSource object. BasicDataSourceWrapper.getConnection gets a connection from the aforementioned BasicDataSource and generates a WrappedConnection using the connection and a new TimerTask object.

I might have missed explaining some details but this is the gist of it.