ClickHouse / ClickHouse/ClickHouse

extra_float_digits incompatibility on PostgreSQL port (9005)

Open
#97,816 0 comments 0 reactions 0 assignees View on GitHub
comp-protocols external potential bug
Dominant language
C++
Stars
49.9k
Forks
9k
Avg merge
21h 32m
Merged PRs (30d)
515

Description

### Company or project name

A single hello world project for test how to connect java spring boot on multiple sources (postgres as main database and clickhouse as logger events database).

### Describe what's wrong

From Java Spring using a JDBC postgres driver to connect to 9005 port and works with flyway with a single `SELECT 1` says:

```
Caused by: org.postgresql.util.PSQLException: ERROR: Query execution failed.
DB::Exception: Setting extra_float_digits is neither a builtin setting nor started with the prefix 'SQL_' registered for user-defined settings
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2733) ~[postgresql-42.7.5.jar:42.7.5]
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2420) ~[postgresql-42.7.5.jar:42.7.5]
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:372) ~[postgresql-42.7.5.jar:42.7.5]
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:330) ~[postgresql-42.7.5.jar:42.7.5]
at org.postgresql.core.SetupQueryRunner.run(SetupQueryRunner.java:57) ~[postgresql-42.7.5.jar:42.7.5]
at org.postgresql.core.v3.ConnectionFactoryImpl.runInitialQueries(ConnectionFactoryImpl.java:922) ~[postgresql-42.7.5.jar:42.7.5]
at org.postgresql.core.v3.ConnectionFactoryImpl.openConnectionImpl(ConnectionFactoryImpl.java:337) ~[postgresql-42.7.5.jar:42.7.5]
at org.postgresql.core.ConnectionFactory.openConnection(ConnectionFactory.java:54) ~[postgresql-42.7.5.jar:42.7.5]
at org.postgresql.jdbc.PgConnection.(PgConnection.java:273) ~[postgresql-42.7.5.jar:42.7.5]
at org.postgresql.Driver.makeConnection(Driver.java:446) ~[postgresql-42.7.5.jar:42.7.5]
at org.postgresql.Driver.connect(Driver.java:298) ~[postgresql-42.7.5.jar:42.7.5]
at com.zaxxer.hikari.util.DriverDataSource.getConnection(DriverDataSource.java:137) ~[HikariCP-5.1.0.jar:na]
at com.zaxxer.hikari.pool.PoolBase.newConnection(PoolBase.java:360) ~[HikariCP-5.1.0.jar:na]
at com.zaxxer.hikari.pool.PoolBase.newPoolEntry(PoolBase.java:202) ~[HikariCP-5.1.0.jar:na]
at com.zaxxer.hikari.pool.HikariPool.createPoolEntry(HikariPool.java:461) ~[HikariCP-5.1.0.jar:na]
at com.zaxxer.hikari.pool.HikariPool.checkFailFast(HikariPool.java:550) ~[HikariCP-5.1.0.jar:na]
... 147 common frames omitted
```

My datasource:

```java
package com.example.demo.config;

import javax.sql.DataSource;
import java.util.Properties;
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.clickhouse.datasource.url}")
private String url;

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

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

@Value("${app.clickhouse.datasource.driver-class-name}")
private String driverClassName;

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

HikariConfig config = new HikariConfig();
config.setJdbcUrl(this.url);
config.setUsername(this.username);
config.setPassword(this.password);
// config.setDriverClassName("com.clickhouse.jdbc.ClickHouseDriver");
// config.setDriverClassName("com.github.housepower.jdbc.ClickHouseDriver");
config.setDriverClassName(this.driverClassName);
config.setMaximumPoolSize(5);
config.setConnectionTimeout(30000);
config.setAutoCommit(true); // ClickHouse does not support transactions
config.setConnectionTestQuery("SELECT 1");

Properties dsProps = new Properties();
dsProps.setProperty("connectTimeout", "30");
dsProps.setProperty("socketTimeout", "300");
dsProps.setProperty("tcpKeepAlive", "true");

// Incompatibilities with Clickhouse
dsProps.setProperty("sendExtraFloatDigits", "false");
dsProps.setProperty("extra_float_digits", "0");
dsProps.setProperty("prepareThreshold", "0");
dsProps.setProperty("preparedStatementCacheQueries", "0");
dsProps.setProperty("preparedStatementCacheSizeMiB", "0");
dsProps.setProperty("defaultRowFetchSize", "1000");
dsProps.setProperty("binaryTransfer", "false");
dsProps.setProperty("preferQueryMode", "simple");
dsProps.setProperty("assumeMinServerVersion", "9.0");

return new HikariDataSource(config);
}

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

And the Flyway config:

```java
package com.example.demo.config;

import java.util.Map;
import javax.sql.DataSource;
import org.flywaydb.core.Flyway;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

/**
*
*/
@Configuration
public class ClickHouseFlywayConfig {

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

@Bean
public Flyway clickhouseFlyway(@Qualifier("clickhouseDataSource") DataSource ds) {
Flyway flyway = Flyway.configure()
.dataSource(ds)
.locations("classpath:db/migration/clickhouse")
.baselineOnMigrate(true)
.configuration(Map.of(
"flyway.placeholders.dbname", this.extractDatabaseName(clickhouseUrl)
))
.placeholderPrefix("${{")
.placeholderSuffix("}}")
.skipDefaultCallbacks(true) // Disable transactions
.load();

flyway.migrate();
return flyway;
}

private String extractDatabaseName(String jdbcUrl) {
if (jdbcUrl != null && jdbcUrl.contains("/")) {
String[] parts = jdbcUrl.split("/");
return parts[parts.length - 1].split("\\?")[0];
}
return "localhost";
}
}
```

The source URL is `jdbc:postgresql://cms_clickhouse:9005/dev?sslmode=disable`.

### Does it reproduce on the most recent release?

Yes

### How to reproduce

Connect to ClickHouse using the datasource exposed in this issue.

### Expected behavior

Connect to Clickhouse and run Flyway correctly.

### Error message and/or stacktrace

```
Caused by: org.postgresql.util.PSQLException: ERROR: Query execution failed.
DB::Exception: Setting extra_float_digits is neither a builtin setting nor started with the prefix 'SQL_' registered for user-defined settings
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2733) ~[postgresql-42.7.5.jar:42.7.5]
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2420) ~[postgresql-42.7.5.jar:42.7.5]
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:372) ~[postgresql-42.7.5.jar:42.7.5]
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:330) ~[postgresql-42.7.5.jar:42.7.5]
at org.postgresql.core.SetupQueryRunner.run(SetupQueryRunner.java:57) ~[postgresql-42.7.5.jar:42.7.5]
at org.postgresql.core.v3.ConnectionFactoryImpl.runInitialQueries(ConnectionFactoryImpl.java:922) ~[postgresql-42.7.5.jar:42.7.5]
at org.postgresql.core.v3.ConnectionFactoryImpl.openConnectionImpl(ConnectionFactoryImpl.java:337) ~[postgresql-42.7.5.jar:42.7.5]
at org.postgresql.core.ConnectionFactory.openConnection(ConnectionFactory.java:54) ~[postgresql-42.7.5.jar:42.7.5]
at org.postgresql.jdbc.PgConnection.(PgConnection.java:273) ~[postgresql-42.7.5.jar:42.7.5]
at org.postgresql.Driver.makeConnection(Driver.java:446) ~[postgresql-42.7.5.jar:42.7.5]
at org.postgresql.Driver.connect(Driver.java:298) ~[postgresql-42.7.5.jar:42.7.5]
at com.zaxxer.hikari.util.DriverDataSource.getConnection(DriverDataSource.java:137) ~[HikariCP-5.1.0.jar:na]
at com.zaxxer.hikari.pool.PoolBase.newConnection(PoolBase.java:360) ~[HikariCP-5.1.0.jar:na]
at com.zaxxer.hikari.pool.PoolBase.newPoolEntry(PoolBase.java:202) ~[HikariCP-5.1.0.jar:na]
at com.zaxxer.hikari.pool.HikariPool.createPoolEntry(HikariPool.java:461) ~[HikariCP-5.1.0.jar:na]
at com.zaxxer.hikari.pool.HikariPool.checkFailFast(HikariPool.java:550) ~[HikariCP-5.1.0.jar:na]
... 147 common frames omitted
```

### Additional context

I use Docker image from dockerhub `clickhouse/clickhouse-server:latest`.

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.