Unable to connect to clickhouse through HAProxy

[wezell]

I have a clickhouse instance running behind haproxy, which is doing SSL termination. I can connect to clickhouse and run queries using curl, it just won't work with clickhouse4j. To test, I used dBeaver and I can also connect to HTTPS using dBeaver and the official clickhouse driver and this works. But if I try to connect using clickhouse4j - same connection settings, just different driver, I get a Your browser sent an invalid request. message. This is the same result I am seeing in my java code. Note that my SSL cert is valid and there are no server name / ssl type issues.

curl http works

> curl 'http://xxx.xxxx.com:8123/?user=default&password=xxxxxxxxxxx&' --data-binary "SELECT timezone()"
Etc/UCT

curl https works

> curl 'https://xxx.xxxx.com:8443//?user=default&password=xxxxxxxxxxx&' --data-binary "SELECT timezone()"
Etc/UCT

dBeaver + official driver https works

I can connect to HTTPS using dBeaver and the official clickhouse driver:

dBeaver + clickhouse4j driver does not work

[doom369]

@wezell thank you for detailed report. I'll check.

[doom369]

@wezell okey, I did some tests and all seems to be working. At least direct ssl connection without proxy.

Do you use self generated certificates?
Do you setSsl(true) in your code?
Does connection without proxy work?
What does CH/proxy log says?

2019.12.11 23:24:24.671147 [ 31 ] {} <Trace> HTTPSHandler-factory: HTTP Request for HTTPSHandler-factory. Method: POST, Address: xx.xx.xx.xx:35570, User-Agent: Java/1.8.0_201, Length: 55, Content Type: text/plain; charset=UTF-8, Transfer Encoding: identity
2019.12.11 23:24:24.671454 [ 31 ] {} <Trace> HTTPHandler: Request URI: /?database=default&user=default&compress=1
2019.12.11 23:24:24.671767 [ 31 ] {03257cc4-3b35-4e92-9f39-9ce345fe26b1} <Debug> executeQuery: (from xx.xx.xx.xx:35570) select timezone() FORMAT TabSeparatedWithNamesAndTypes 
2019.12.11 23:24:24.672126 [ 31 ] {03257cc4-3b35-4e92-9f39-9ce345fe26b1} <Trace> InterpreterSelectQuery: FetchColumns -> Complete
2019.12.11 23:24:24.672252 [ 31 ] {03257cc4-3b35-4e92-9f39-9ce345fe26b1} <Debug> executeQuery: Query pipeline:
Expression
 Expression
  One

[wezell]

setSsl set to true and the cert is not self signed. It is a legit cert. sslType is none anyway. Without the proxy, clickhouse4j can connect without ssl. Keep in mind that curl connects to https and queries fine.

[doom369]

Without the proxy, clickhouse4j can connect without ssl

Does ssl connection work without proxy?

Keep in mind that curl connects to https and queries fine.

I understand, I trying to narrow down the problem.

Here a test I used:

import cc.blynk.clickhouse.ClickHouseConnection;
import cc.blynk.clickhouse.ClickHouseDataSource;
import cc.blynk.clickhouse.settings.ClickHouseProperties;
import org.testng.Assert;
import org.testng.annotations.AfterTest;
import org.testng.annotations.BeforeTest;
import org.testng.annotations.Test;

import java.sql.ResultSet;
import java.sql.Statement;

public class ClickHouseStatementImplSSLTest {

    private ClickHouseDataSource dataSource;
    private ClickHouseConnection connection;

    @BeforeTest
    public void setUp() throws Exception {
        ClickHouseProperties properties = new ClickHouseProperties();
        properties.setSsl(true);
        dataSource = new ClickHouseDataSource("jdbc:clickhouse://real-host.com:8443?password=&user=default", properties);
        connection = dataSource.getConnection();
    }

    @AfterTest
    public void tearDown() throws Exception {
        if (connection != null) {
            connection.close();
        }
    }

    @Test
    public void testAnySelect() throws Exception {
        Statement stmt = connection.createStatement();
        ResultSet rs = stmt.executeQuery("SELECT dummy FROM system.one");
        Assert.assertEquals(stmt.getUpdateCount(), -1);
        rs.next();
        Assert.assertEquals(stmt.getUpdateCount(), -1);
        rs.close();
        stmt.close();
    }

}

[wezell]

Dmitriy -

To remove variables, I took out the SSL and it is reproducible through just HAProxy. The yandex jdbc driver works fine:

import static org.junit.Assert.assertEquals;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import org.junit.Test;
/*
import cc.blynk.clickhouse.ClickHouseDataSource;
import cc.blynk.clickhouse.settings.ClickHouseProperties;
 */

import ru.yandex.clickhouse.ClickHouseDataSource;
import ru.yandex.clickhouse.settings.ClickHouseProperties;

public class ClickHouse4JTest {
    @Test
    public void testUpdateCountForSelect() throws SQLException {

        ClickHouseProperties properties = new ClickHouseProperties();
        properties.setSsl(false);
        ClickHouseDataSource dataSource = new ClickHouseDataSource(
                        "jdbc:clickhouse://realhost.com:8080?password=xxxxxx&user=default", properties);
        try (Connection connection = dataSource.getConnection()) {

            Statement stmt = connection.createStatement();
            ResultSet rs = stmt.executeQuery("SELECT dummy FROM system.one");
            assertEquals(stmt.getUpdateCount(), -1);
            rs.next();
            assertEquals(stmt.getUpdateCount(), -1);
            rs.close();
            stmt.close();
        }
    }
}

but clickhouse4j does not

import static org.junit.Assert.assertEquals;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import org.junit.Test;
import cc.blynk.clickhouse.ClickHouseDataSource;
import cc.blynk.clickhouse.settings.ClickHouseProperties;
/*
import ru.yandex.clickhouse.ClickHouseDataSource;
import ru.yandex.clickhouse.settings.ClickHouseProperties;
 */

public class ClickHouse4JTest {
    @Test
    public void testUpdateCountForSelect() throws SQLException {

        ClickHouseProperties properties = new ClickHouseProperties();
        properties.setSsl(false);
        ClickHouseDataSource dataSource = new ClickHouseDataSource(
                        "jdbc:clickhouse://realhost.com:8080?password=xxxxxx&user=default", properties);
        try (Connection connection = dataSource.getConnection()) {

            Statement stmt = connection.createStatement();
            ResultSet rs = stmt.executeQuery("SELECT dummy FROM system.one");
            assertEquals(stmt.getUpdateCount(), -1);
            rs.next();
            assertEquals(stmt.getUpdateCount(), -1);
            rs.close();
            stmt.close();
        }
    }

}

Truly, HAProxy's config could not be more simple. Nothing exotic at all. My guess is that haproxy doesn't like the http handshake or request - maybe an missing/extra line break, an missing/unexpected header something like that. Here is my HAProxy config.

global
   tune.ssl.default-dh-param 4096
   maxconn 2048

defaults
    mode http
    option forwardfor
    option redispatch
    retries 3
    timeout http-request 10s
    timeout queue 5s
    timeout client 50s
    timeout server 50s
    timeout check 10s
    maxconn 3000
    timeout connect 30s
    timeout client-fin 30s

frontend http
    bind  *:8080
    default_backend clickhouse_http

frontend https
    bind  *:8443  ssl crt /etc/haproxy/xxxxxx.pem
    default_backend clickhouse_http

backend clickhouse_http
    balance roundrobin
    option forwardfor
    server server_1 localhost:8123 check
    http-request set-header X-Forwarded-Port %[dst_port]
    http-request add-header X-Forwarded-Proto https if { ssl_fc }