com.clickhouse:clickhouse-jdbc port corruption

[yhojann-cl]

High Level Area Labels:

• jdbc-v2

Specific Area Labels:

• sql-parser

Description

The com.clickhouse:clickhouse-jdbc 0.9.6 package is broken, make a duplicated ports:

• My JDBC URL: jdbc:ch://cms_clickhouse:9000/dev
• My DataSource cofig:

package com.example.demo.config;

import javax.sql.DataSource;
import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.beans.factory.annotation.Qualifier;


@Configuration
public class ClickHouseDataSourceConfig {

    @Value("${app.logdb.url}")
    private String url;

    @Value("${app.logdb.username}")
    private String username;

    @Value("${app.logdb.password}")
    private String password;

    @Bean(name="clickhouseDataSource")
    public DataSource clickhouseDataSource() {

        // Compose ClickHouse config
        HikariConfig config = new HikariConfig();
        config.setJdbcUrl(url);
        config.setUsername(username);
        config.setPassword(password);
        config.setDriverClassName("com.clickhouse.jdbc.ClickHouseDriver");
        config.setMaximumPoolSize(20);
        config.setConnectionTimeout(60000);

        return new HikariDataSource(config);
    }

    @Bean(name="clickhouseJdbc")
    public NamedParameterJdbcTemplate clickhouseJdbc(
            @Qualifier("clickhouseDataSource") DataSource ds) {
        return new NamedParameterJdbcTemplate(ds);
    }
}

But when try run any query using the datasource says:

Caused by: java.lang.IllegalArgumentException: Endpoint should be a valid URL string, but was http://cms_clickhouse:9000:9000
        at com.clickhouse.client.api.Client$Builder.addEndpoint(Client.java:307) ~[client-v2-0.9.6.jar:client-v2 0.9.6 (revision: 5777f5a)]
        at com.clickhouse.jdbc.internal.JdbcConfiguration.applyClientProperties(JdbcConfiguration.java:303) ~[jdbc-v2-0.9.6.jar:jdbc-v2 0.9.6 (revision: 5777f5a)]
        at com.clickhouse.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:104) ~[jdbc-v2-0.9.6.jar:clickhouse-jdbc 0.9.6 (revision: 5777f5a)]
        ... 154 common frames omitted
Caused by: java.net.MalformedURLException: Error at index 4 in: "9000:9000"
        at java.base/java.net.URL.<init>(URL.java:806) ~[na:na]
        at java.base/java.net.URL.<init>(URL.java:654) ~[na:na]
        at java.base/java.net.URL.<init>(URL.java:590) ~[na:na]
        at com.clickhouse.client.api.Client$Builder.addEndpoint(Client.java:297) ~[client-v2-0.9.6.jar:client-v2 0.9.6 (revision: 5777f5a)]
        ... 156 common frames omitted
Caused by: java.lang.NumberFormatException: Error at index 4 in: "9000:9000"
        at java.base/java.lang.NumberFormatException.forCharSequence(NumberFormatException.java:83) ~[na:na]
        at java.base/java.lang.Integer.parseInt(Integer.java:743) ~[na:na]
        at java.base/java.net.URLStreamHandler.parseURL(URLStreamHandler.java:236) ~[na:na]
        at java.base/java.net.URL.<init>(URL.java:801) ~[na:na]
        ... 159 common frames omitted

Says 9000:9000?, I try see the config.getJdbcUrl(), and works fine with only one port, the config datasource works fine, but the clickhouse driver append a second port parameter.

Environment

Ubuntu 24.04 LTS over Docker.

My build.gradle:

plugins {
	id 'java'
	id 'org.springframework.boot' version '3.4.4'
	id 'io.spring.dependency-management' version '1.1.7'
}

group = 'com.example'
version = '0.0.1-beta'

java {
	toolchain {
		languageVersion = JavaLanguageVersion.of(21)
	}
}

configurations {
	compileOnly {
		extendsFrom annotationProcessor
	}
}

repositories {
	mavenCentral()
}

dependencies {

	// Spring boot base
	implementation 'org.springframework.boot:spring-boot-starter-web'
	implementation 'org.springframework.boot:spring-boot-starter-security'
	implementation 'org.springframework.boot:spring-boot-starter-data-jpa'
	implementation 'org.springframework.boot:spring-boot-starter-validation'
	implementation 'org.springframework.boot:spring-boot-starter-mail'
	implementation 'org.springframework.boot:spring-boot-starter-thymeleaf' // For render email templates

	// Database & migrations
	implementation 'org.flywaydb:flyway-core'
	implementation 'org.flywaydb:flyway-database-postgresql'
	implementation 'com.clickhouse:clickhouse-jdbc:0.9.6' // Duplicated port bug
	// implementation 'org.lz4:lz4-java:1.8.1' // Many vulnerabilities
	runtimeOnly 'org.flywaydb:flyway-database-clickhouse:10.24.0' // For activity log database
	runtimeOnly 'org.postgresql:postgresql' // For business database

	// Extra security
    implementation 'at.favre.lib:bcrypt:0.10.2'
	implementation 'com.auth0:java-jwt:4.5.0'
	implementation 'org.apache.commons:commons-text:1.13.1'
	implementation 'org.springframework.security:spring-security-config' // Spring security over annotations
	implementation 'org.springframework.security:spring-security-web' // For spEL evaluation access

	// TOTP/2FA
	implementation 'dev.samstevens.totp:totp:1.7.1'

	// Easy object management
	compileOnly 'org.projectlombok:lombok'
	annotationProcessor 'org.projectlombok:lombok'
	
	// Swagger UI
	implementation 'org.springdoc:springdoc-openapi-starter-webmvc-ui:2.8.6'

	// Documentation generator
	implementation 'org.apache.maven.plugins:maven-javadoc-plugin:3.11.2'

	// Image upload
	implementation 'com.twelvemonkeys.imageio:imageio-jpeg:3.12.0'

	// Development
	implementation 'org.springframework.boot:spring-boot-devtools'

	testImplementation 'org.springframework.boot:spring-boot-starter-test'
	testImplementation 'org.springframework.security:spring-security-test'
	testRuntimeOnly 'org.junit.platform:junit-platform-launcher'
}

tasks.named('test') {
	useJUnitPlatform()
}

test {
    testLogging {
        exceptionFormat = 'full'
        events = [ "passed", "failed", "skipped" ]
	    showStandardStreams = true
    }
}

ClickHouse Server

• ClickHouse Server version: From Dockerhub image clickhouse/clickhouse-server:latest
• ClickHouse env from docker-compose:

  cms_clickhouse:
    image: clickhouse/clickhouse-server:latest
    container_name: cms_clickhouse
    ulimits:
      nofile:
        soft: 262144
        hard: 262144
    environment:
      CLICKHOUSE_DB: dev
      CLICKHOUSE_USER: dev
      CLICKHOUSE_PASSWORD: dev
    volumes:
      - ${VOLUME_PATH}/clickhouse:/var/lib/clickhouse
    ports:
      - "127.0.0.1:8123:8123" # Web UI
      - "127.0.0.1:9000:9000"
    restart: unless-stopped
    networks:
      - cms_network

---

Same error from 0.9.1 to 0.9.6 version, but:

v0.9.0 says:

Caused by: java.lang.ArrayIndexOutOfBoundsException: Index 0 out of bounds for length 0
        at com.clickhouse.jdbc.PreparedStatementImpl.setString(PreparedStatementImpl.java:197) ~[jdbc-v2-0.9.0.jar:clickhouse-jdbc 0.9.0 (revision: 27ccb8e)]
        at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.setString(HikariProxyPreparedStatement.java) ~[HikariCP-5.1.0.jar:na]
        at org.flywaydb.core.internal.jdbc.JdbcTemplate.prepareStatement(JdbcTemplate.java:322) ~[flyway-core-10.20.1.jar:na]
        at org.flywaydb.core.internal.jdbc.JdbcTemplate.queryForInt(JdbcTemplate.java:110) ~[flyway-core-10.20.1.jar:na]
        at org.flywaydb.community.database.clickhouse.ClickHouseSchema.doExists(ClickHouseSchema.java:46) ~[flyway-database-clickhouse-10.24.0.jar:na]
        at org.flywaydb.core.internal.database.base.Schema.exists(Schema.java:55) ~[flyway-core-10.20.1.jar:na]
        at org.flywaydb.core.internal.command.DbValidate.validate(DbValidate.java:89) ~[flyway-core-10.20.1.jar:na]
        at org.flywaydb.core.Flyway.doValidate(Flyway.java:493) ~[flyway-core-10.20.1.jar:na]
        at org.flywaydb.core.Flyway.lambda$migrate$1(Flyway.java:187) ~[flyway-core-10.20.1.jar:na]
        at org.flywaydb.core.FlywayExecutor.execute(FlywayExecutor.java:213) ~[flyway-core-10.20.1.jar:na]
        at org.flywaydb.core.Flyway.migrate(Flyway.java:176) ~[flyway-core-10.20.1.jar:na]
        at com.example.demo.config.ClickHouseFlywayConfig.clickhouseFlyway(ClickHouseFlywayConfig.java:22) ~[main/:na]
        at com.example.demo.config.ClickHouseFlywayConfig$$SpringCGLIB$$0.CGLIB$clickhouseFlyway$0(<generated>) ~[main/:na]
        at com.example.demo.config.ClickHouseFlywayConfig$$SpringCGLIB$$FastClass$$1.invoke(<generated>) ~[main/:na]
        at org.springframework.cglib.proxy.MethodProxy.invokeSuper(MethodProxy.java:258) ~[spring-core-6.2.5.jar:6.2.5]
        at org.springframework.context.annotation.ConfigurationClassEnhancer$BeanMethodInterceptor.intercept(ConfigurationClassEnhancer.java:370) ~[spring-context-6.2.5.jar:6.2.5]
        at com.example.demo.config.ClickHouseFlywayConfig$$SpringCGLIB$$0.clickhouseFlyway(<generated>) ~[main/:na]
        at java.base/jdk.internal.reflect.DirectMethodHandleAccessor.invoke(DirectMethodHandleAccessor.java:103) ~[na:na]
        at java.base/java.lang.reflect.Method.invoke(Method.java:580) ~[na:na]
        at org.springframework.beans.factory.support.SimpleInstantiationStrategy.lambda$instantiate$0(SimpleInstantiationStrategy.java:171) ~[spring-beans-6.2.5.jar:6.2.5]
        ... 123 common frames omitted

v0.8.6 says:

Caused by: java.lang.IllegalArgumentException: Host cannot be null or empty string
        at com.clickhouse.data.ClickHouseChecker.newException(ClickHouseChecker.java:19) ~[clickhouse-jdbc-0.8.6.jar:clickhouse-jdbc 0.8.6 (revision: 2d305b7)]
        at com.clickhouse.data.ClickHouseChecker.nonEmpty(ClickHouseChecker.java:250) ~[clickhouse-jdbc-0.8.6.jar:clickhouse-jdbc 0.8.6 (revision: 2d305b7)]
        at com.clickhouse.client.ClickHouseNode.<init>(ClickHouseNode.java:892) ~[clickhouse-jdbc-0.8.6.jar:clickhouse-jdbc 0.8.6 (revision: 2d305b7)]
        at com.clickhouse.client.ClickHouseNode.of(ClickHouseNode.java:797) ~[clickhouse-jdbc-0.8.6.jar:clickhouse-jdbc 0.8.6 (revision: 2d305b7)]
        at com.clickhouse.client.api.Client.lambda$new$0(Client.java:161) ~[jdbc-v2-0.8.6.jar:jdbc-v2 0.8.6 (revision: 2d305b7)]
        at java.base/java.lang.Iterable.forEach(Iterable.java:75) ~[na:na]
        at com.clickhouse.client.api.Client.<init>(Client.java:160) ~[jdbc-v2-0.8.6.jar:jdbc-v2 0.8.6 (revision: 2d305b7)]
        at com.clickhouse.client.api.Client.<init>(Client.java:115) ~[jdbc-v2-0.8.6.jar:jdbc-v2 0.8.6 (revision: 2d305b7)]
        at com.clickhouse.client.api.Client$Builder.build(Client.java:1064) ~[jdbc-v2-0.8.6.jar:jdbc-v2 0.8.6 (revision: 2d305b7)]
        at com.clickhouse.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:99) ~[jdbc-v2-0.8.6.jar:clickhouse-jdbc 0.8.6 (revision: 2d305b7)]
        ... 154 common frames omitted

v0.7.2 says:

Caused by: java.net.ConnectException: Connect to http://localhost:9000 [localhost/127.0.0.1, localhost/0:0:0:0:0:0:0:1] failed: Connection refused
        at com.clickhouse.client.http.ApacheHttpConnectionImpl.post(ApacheHttpConnectionImpl.java:307) ~[clickhouse-jdbc-0.7.2.jar:clickhouse-jdbc 0.7.2 (revision: 33b1fad)]
        at com.clickhouse.client.http.ClickHouseHttpClient.send(ClickHouseHttpClient.java:195) ~[clickhouse-jdbc-0.7.2.jar:clickhouse-jdbc 0.7.2 (revision: 33b1fad)]
        at com.clickhouse.client.AbstractClient.execute(AbstractClient.java:280) ~[clickhouse-jdbc-0.7.2.jar:clickhouse-jdbc 0.7.2 (revision: 33b1fad)]
        at com.clickhouse.client.ClickHouseClientBuilder$Agent.sendOnce(ClickHouseClientBuilder.java:282) ~[clickhouse-jdbc-0.7.2.jar:clickhouse-jdbc 0.7.2 (revision: 33b1fad)]
        at com.clickhouse.client.ClickHouseClientBuilder$Agent.send(ClickHouseClientBuilder.java:294) ~[clickhouse-jdbc-0.7.2.jar:clickhouse-jdbc 0.7.2 (revision: 33b1fad)]
        at com.clickhouse.client.ClickHouseClientBuilder$Agent.execute(ClickHouseClientBuilder.java:349) ~[clickhouse-jdbc-0.7.2.jar:clickhouse-jdbc 0.7.2 (revision: 33b1fad)]
        at com.clickhouse.client.ClickHouseClient.executeAndWait(ClickHouseClient.java:881) ~[clickhouse-jdbc-0.7.2.jar:clickhouse-jdbc 0.7.2 (revision: 33b1fad)]
        at com.clickhouse.client.ClickHouseRequest.executeAndWait(ClickHouseRequest.java:2154) ~[clickhouse-jdbc-0.7.2.jar:clickhouse-jdbc 0.7.2 (revision: 33b1fad)]
        at com.clickhouse.jdbc.internal.ClickHouseConnectionImpl.getServerInfo(ClickHouseConnectionImpl.java:120) ~[clickhouse-jdbc-0.7.2.jar:clickhouse-jdbc 0.7.2 (revision: 33b1fad)]
        ... 157 common frames omitted

v0.6.5:all says:

Caused by: java.lang.ClassNotFoundException: com.clickhouse.client.ClickHouseClient
        at java.base/jdk.internal.loader.BuiltinClassLoader.loadClass(BuiltinClassLoader.java:641) ~[na:na]
        at java.base/jdk.internal.loader.ClassLoaders$AppClassLoader.loadClass(ClassLoaders.java:188) ~[na:na]
        at java.base/java.lang.ClassLoader.loadClass(ClassLoader.java:526) ~[na:na]
        ... 155 common frames omitted

[chernser]

@yhojann-cl
thank you for reporting the issue!
Will be fixed in the next release.

Root cause is that URI.create may not always parse port correctly (like in your case) and port will be -1 what is wrong.

[chernser]

@yhojann-cl

I see that your host name is not valid for HTTP URL because it has _ (see https://datatracker.ietf.org/doc/html/rfc1738 ).
Where is the use-case for it?

[yhojann-cl]

I was unaware of the inability to include underscores in the hostname; it's actually the internal hostname that Docker associates with the container. All my Docker Compose containers use an underscore-based prefix like cms_api, cms_webapp, cms_postgres, cms_clickhouse, cms_sendmail, etc. In that case, the library should indicate the actual URL rejection error so developers know where the problem lies. Thanks for the information; I'll stop using underscores.

Reading about it, there are cases like Kubernetes where it does allow the use of underscores and there are even cases where it generates them. I also saw that at the URL level it is not allowed, but it is allowed at the hostname level by RFC 1035 in dns services and discovery services like as _sip._tcp.microsoft.com but not browseable. I understand that it is not a good idea to create hostnames with underscores, but in many cases it can happen and work correctly with fault tolerance, as is the case with websites with underscores.

Anyway, the JDBC URL isn't HTTP. If it's transformed afterward, that's another story. If ClickHouse didn't use HTTP and instead used an endpoint under a different protocol, perhaps this wouldn't have happened. I think it should tolerate the use of underscores despite it not being a best practice like as my nginx reverse proxy when get the api and webapp using underscore in the hostname.

[chernser]

Thank you @yhojann-cl !

I will investigate what we can do on our end. Because only fix in JDBC would not help - we need to make many places compatible with underscores.

[yhojann-cl]

I confirm that changing the container name by replacing the underscore with a hyphen no longer gives the connection and port duplication error.

Now I'm getting another error, but I suppose it will go in another issue:

Caused by: com.clickhouse.client.api.ServerException: Code: 0. DB::Exception: <Unreadable error message> (transport error: 400)
        at com.clickhouse.client.api.internal.HttpAPIClientHelper.readError(HttpAPIClientHelper.java:403) ~[client-v2-0.9.6.jar:client-v2 0.9.6 (revision: 5777f5a)]
        at com.clickhouse.client.api.internal.HttpAPIClientHelper.executeRequest(HttpAPIClientHelper.java:467) ~[client-v2-0.9.6.jar:client-v2 0.9.6 (revision: 5777f5a)]
        at com.clickhouse.client.api.Client.lambda$query$5(Client.java:1599) ~[client-v2-0.9.6.jar:client-v2 0.9.6 (revision: 5777f5a)]
        at com.clickhouse.client.api.Client.runAsyncOperation(Client.java:2012) ~[client-v2-0.9.6.jar:client-v2 0.9.6 (revision: 5777f5a)]
        at com.clickhouse.client.api.Client.query(Client.java:1644) ~[client-v2-0.9.6.jar:client-v2 0.9.6 (revision: 5777f5a)]
        at com.clickhouse.client.api.Client.queryAll(Client.java:1728) ~[client-v2-0.9.6.jar:client-v2 0.9.6 (revision: 5777f5a)]
        ... 141 common frames omitted

[chernser]

@yhojann-cl
yes, please create another issue.

From what I see:

• code 0 means response doesn't have even error code in the header
• http response code is 400 (Bad Request)

Try executing simple query with curl like this (credentials may be required):

curl 'http://localhost:8123/?query=SELECT%201'