ClickHouse / ClickHouse/ClickHouse
extra_float_digits incompatibility on PostgreSQL port (9005)
- 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
Assessment
This issue has not been assessed yet.