This is an automated email from the ASF dual-hosted git repository.
yuqi1129 pushed a commit to branch main
in repository https://gitbox.apache.org/repos/asf/gravitino.git
The following commit(s) were added to refs/heads/main by this push:
new b15b420b6c [#13592] fix(jdbc): Use driver validation for catalog
connection pools (#13600)
b15b420b6c is described below
commit b15b420b6c530d89aac598d8d571289f8b5a99bf
Author: Qi Yu <[email protected]>
AuthorDate: Fri Oct 9 14:48:35 2026 +0800
[#13592] fix(jdbc): Use driver validation for catalog connection pools
(#13600)
### What changes were proposed in this pull request?
Remove the forced `SELECT 1` validation query from the shared JDBC
datasource utility. All JDBC catalogs now use DBCP's default driver
validation (`Connection.isValid()`) unless the catalog property
`gravitino.bypass.validationQuery` is supplied. Preserve
validation-on-borrow and timeout settings. Document the rationale and
query option in a shared JDBC catalog guide linked from all seven JDBC
provider pages.
### Why are the changes needed?
DBCP caches the validation prepared statement. With a MySQL URL that has
no default database, switching catalogs makes that statement fail, so
the pool discards healthy connections and creates replacements on
subsequent borrows. Connector/J executes a prepared statement in the
database captured when the statement was created, switching databases
when necessary. Restoring an empty original database after
`setCatalog()` can fail even on a healthy connection. Connector/J's
`isValid()` uses ping, which avoids this statement-specific database
switch.
The benefit is restoring physical connection reuse and avoiding
connection setup and authentication caused by validation failures. The
tests verify reuse and replacement of disconnected connections; no
general throughput improvement is claimed.
Fix: #13592
### Does this PR introduce _any_ user-facing change?
JDBC catalog pools use driver validation by default, and explicitly
configured validation queries are honored rather than overwritten. MySQL
connections can be reused after catalog switching even without a default
database. No API changes or new configuration keys.
### How was this patch tested?
148 tests passed across JDBC common, MySQL, and PostgreSQL with no
failures or skips. Coverage includes:
- Real MySQL (`TestMysqlDataSource` in `catalog-jdbc-mysql`): 100
sequential borrows reuse the same physical connection after switching
catalogs, with and without a default database; killed connections are
replaced. The reuse regression failed before the fix.
- Real PostgreSQL (`TestPostgreSqlDataSource` in
`catalog-jdbc-postgresql`): 100 sequential borrows reuse the same
physical connection after schema changes; terminated connections are
replaced.
- SQLite: default validation preserves an in-memory database across
borrows and replaces a closed connection.
- Explicit validation queries: valid queries work and invalid queries
reject connections; validation-on-borrow and timeout settings are
preserved.
- Unsupported driver: a fixture throws `SQLFeatureNotSupportedException`
from `isValid()`. Through `JdbcCatalogOperations.initialize()`, the
bypass-prefixed query enables repeated borrows; an unprefixed query is
filtered out, and disabling validation on borrow alone still fails
connection-factory validation.
The Docker-based datasource tests live in the provider catalog modules,
so `catalog-jdbc-common` needs no database containers or drivers for
them.
Spotless and JDBC common Javadoc checks passed.
```bash
./gradlew spotlessApply :catalogs:catalog-jdbc-common:test
:catalogs:catalog-jdbc-mysql:test :catalogs:catalog-jdbc-postgresql:test
:catalogs:catalog-jdbc-common:javadoc -PskipITs -PskipDockerTests=false
```
---
.../catalog/jdbc/utils/DataSourceUtils.java | 11 +-
.../jdbc/TestJdbcCatalogPropertiesMetadata.java | 58 +++++++
.../jdbc/utils/TestDataSourceValidation.java | 167 +++++++++++++++++++++
catalogs/catalog-jdbc-mysql/build.gradle.kts | 1 +
.../catalog/mysql/TestMysqlDataSource.java | 144 ++++++++++++++++++
catalogs/catalog-jdbc-postgresql/build.gradle.kts | 1 +
.../postgresql/TestPostgreSqlDataSource.java | 126 ++++++++++++++++
docs/jdbc-catalog-connection-validation.md | 57 +++++++
docs/jdbc-clickhouse-catalog.md | 3 +
docs/jdbc-doris-catalog.md | 3 +
docs/jdbc-hologres-catalog.md | 3 +
docs/jdbc-mysql-catalog.md | 3 +
docs/jdbc-oceanbase-catalog.md | 3 +
docs/jdbc-postgresql-catalog.md | 3 +
docs/jdbc-starrocks-catalog.md | 3 +
15 files changed, 580 insertions(+), 6 deletions(-)
diff --git
a/catalogs/catalog-jdbc-common/src/main/java/org/apache/gravitino/catalog/jdbc/utils/DataSourceUtils.java
b/catalogs/catalog-jdbc-common/src/main/java/org/apache/gravitino/catalog/jdbc/utils/DataSourceUtils.java
index 91c4a6bb4e..40f4fc90dd 100644
---
a/catalogs/catalog-jdbc-common/src/main/java/org/apache/gravitino/catalog/jdbc/utils/DataSourceUtils.java
+++
b/catalogs/catalog-jdbc-common/src/main/java/org/apache/gravitino/catalog/jdbc/utils/DataSourceUtils.java
@@ -38,9 +38,6 @@ import org.apache.gravitino.utils.JdbcUrlUtils;
*/
public class DataSourceUtils {
- /** SQL statements for database connection pool testing. */
- private static final String POOL_TEST_QUERY = "SELECT 1";
-
// DBCP2 connection-pool properties that must never come from catalog
configuration. The whole
// config map is handed to BasicDataSourceFactory, so allowing these would
either run arbitrary
// code or let a raw property override the validated canonical connection
fields:
@@ -136,6 +133,10 @@ public class DataSourceUtils {
private static DataSource createDBCPDataSource(JdbcConfig jdbcConfig) throws
Exception {
JdbcUrlUtils.validateJdbcConfig(
jdbcConfig.getJdbcDriver(), jdbcConfig.getJdbcUrl(),
jdbcConfig.getAllConfig());
+ // Keep DBCP's default Connection.isValid() validation unless a
validationQuery is explicitly
+ // configured. DBCP caches a validation query as a prepared statement, and
MySQL Connector/J
+ // switches back to the database captured at prepare time when executing
it. That switch fails
+ // when the captured database is empty, so healthy connections would be
discarded.
BasicDataSource basicDataSource =
BasicDataSourceFactory.createDataSource(getProperties(jdbcConfig));
String jdbcUrl = jdbcConfig.getJdbcUrl();
@@ -163,10 +164,8 @@ public class DataSourceUtils {
maxIdle >= 0
? Math.min(jdbcConfig.getPoolMinSize(), maxIdle)
: jdbcConfig.getPoolMinSize());
- // Set each time a connection is taken out from the connection pool, a
test statement will be
- // executed to confirm whether the connection is valid.
+ // Validate connections on borrow when enabled.
basicDataSource.setTestOnBorrow(jdbcConfig.getTestOnBorrow());
- basicDataSource.setValidationQuery(POOL_TEST_QUERY);
basicDataSource.setMaxWait(Duration.ofMillis(jdbcConfig.getMaxWaitMs()));
return basicDataSource;
}
diff --git
a/catalogs/catalog-jdbc-common/src/test/java/org/apache/gravitino/catalog/jdbc/TestJdbcCatalogPropertiesMetadata.java
b/catalogs/catalog-jdbc-common/src/test/java/org/apache/gravitino/catalog/jdbc/TestJdbcCatalogPropertiesMetadata.java
index 1974134071..6aa9f1b04c 100644
---
a/catalogs/catalog-jdbc-common/src/test/java/org/apache/gravitino/catalog/jdbc/TestJdbcCatalogPropertiesMetadata.java
+++
b/catalogs/catalog-jdbc-common/src/test/java/org/apache/gravitino/catalog/jdbc/TestJdbcCatalogPropertiesMetadata.java
@@ -27,15 +27,21 @@ import org.apache.gravitino.catalog.jdbc.config.JdbcConfig;
import
org.apache.gravitino.catalog.jdbc.converter.JdbcColumnDefaultValueConverter;
import org.apache.gravitino.catalog.jdbc.converter.JdbcTypeConverter;
import
org.apache.gravitino.catalog.jdbc.converter.SqliteColumnDefaultValueConverter;
+import org.apache.gravitino.catalog.jdbc.converter.SqliteExceptionConverter;
import org.apache.gravitino.catalog.jdbc.converter.SqliteTypeConverter;
import org.apache.gravitino.catalog.jdbc.operation.JdbcDatabaseOperations;
import org.apache.gravitino.catalog.jdbc.operation.JdbcTableOperations;
import org.apache.gravitino.catalog.jdbc.operation.SqliteDatabaseOperations;
import org.apache.gravitino.catalog.jdbc.operation.SqliteTableOperations;
+import
org.apache.gravitino.catalog.jdbc.utils.TestDataSourceValidation.UnsupportedValidationDriver;
+import org.apache.gravitino.connector.CatalogInfo;
+import org.apache.gravitino.exceptions.ConnectionFailedException;
import org.apache.gravitino.meta.AuditInfo;
import org.apache.gravitino.meta.CatalogEntity;
import org.junit.jupiter.api.Assertions;
import org.junit.jupiter.api.Test;
+import org.junit.jupiter.params.ParameterizedTest;
+import org.junit.jupiter.params.provider.CsvSource;
public class TestJdbcCatalogPropertiesMetadata {
@@ -90,6 +96,58 @@ public class TestJdbcCatalogPropertiesMetadata {
.get(JdbcConfig.POOL_MAX_IDLE.getKey()));
}
+ /** Verifies the actual catalog configuration path for drivers without
isValid support. */
+ @ParameterizedTest
+ @CsvSource({
+ "none, true",
+ "validationQuery, true",
+ "gravitino.bypass.validationQuery, true",
+ "none, false"
+ })
+ public void testValidationQueryBypassForUnsupportedDriver(
+ String queryProperty, boolean validateOnBorrow) {
+ ImmutableMap.Builder<String, String> builder =
+ ImmutableMap.<String, String>builder()
+ .put(JdbcConfig.JDBC_URL.getKey(), "jdbc:sqlite::memory:")
+ .put(JdbcConfig.JDBC_DRIVER.getKey(),
UnsupportedValidationDriver.class.getName())
+ .put(JdbcConfig.USERNAME.getKey(), "user")
+ .put(JdbcConfig.PASSWORD.getKey(), "password")
+ .put(JdbcConfig.TEST_ON_BORROW.getKey(),
Boolean.toString(validateOnBorrow));
+ if (!queryProperty.equals("none")) {
+ builder.put(queryProperty, "SELECT 42");
+ }
+ Map<String, String> properties = builder.build();
+ CatalogInfo info =
+ new CatalogInfo(
+ 1L,
+ "jdbc_catalog",
+ Catalog.Type.RELATIONAL,
+ "jdbc-test",
+ "test",
+ properties,
+
AuditInfo.builder().withCreator("test").withCreateTime(Instant.EPOCH).build(),
+ Namespace.of("metalake"));
+ try (JdbcCatalogOperations operations =
+ new JdbcCatalogOperations(
+ new SqliteExceptionConverter(),
+ new SqliteTypeConverter(),
+ new SqliteDatabaseOperations(":memory:"),
+ new SqliteTableOperations(),
+ new SqliteColumnDefaultValueConverter())) {
+ operations.initialize(properties, info, new TestJdbcCatalog());
+ if (queryProperty.equals("gravitino.bypass.validationQuery")) {
+ // Repeated borrows must use SQL validation, never the unsupported
isValid method.
+ for (int i = 0; i < 3; i++) {
+ Assertions.assertDoesNotThrow(operations::getDiverInfo);
+ }
+ } else {
+ ConnectionFailedException exception =
+ Assertions.assertThrows(ConnectionFailedException.class,
operations::getDiverInfo);
+ Assertions.assertTrue(exception.getMessage().contains("isValid is not
supported"));
+ }
+ }
+ }
+
private static class TestJdbcCatalog extends JdbcCatalog {
@Override
public String shortName() {
diff --git
a/catalogs/catalog-jdbc-common/src/test/java/org/apache/gravitino/catalog/jdbc/utils/TestDataSourceValidation.java
b/catalogs/catalog-jdbc-common/src/test/java/org/apache/gravitino/catalog/jdbc/utils/TestDataSourceValidation.java
new file mode 100644
index 0000000000..2636c8ba00
--- /dev/null
+++
b/catalogs/catalog-jdbc-common/src/test/java/org/apache/gravitino/catalog/jdbc/utils/TestDataSourceValidation.java
@@ -0,0 +1,167 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied. See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+package org.apache.gravitino.catalog.jdbc.utils;
+
+import java.lang.reflect.InvocationTargetException;
+import java.lang.reflect.Proxy;
+import java.sql.Connection;
+import java.sql.ResultSet;
+import java.sql.SQLException;
+import java.sql.SQLFeatureNotSupportedException;
+import java.sql.Statement;
+import java.time.Duration;
+import java.util.HashMap;
+import java.util.Map;
+import java.util.Properties;
+import javax.annotation.Nullable;
+import org.apache.commons.dbcp2.BasicDataSource;
+import org.apache.gravitino.catalog.jdbc.config.JdbcConfig;
+import org.junit.jupiter.api.Assertions;
+import org.junit.jupiter.api.Test;
+import org.junit.jupiter.params.ParameterizedTest;
+import org.junit.jupiter.params.provider.ValueSource;
+import org.sqlite.JDBC;
+import org.sqlite.SQLiteConnection;
+
+/** Tests default driver validation and explicitly configured SQL validation.
*/
+public class TestDataSourceValidation {
+
+ /** Verifies default validation preserves a live physical SQLite connection
across borrows. */
+ @Test
+ public void testReuseWithDefaultValidation() throws SQLException {
+ try (BasicDataSource dataSource = createDataSource(Map.of())) {
+ Assertions.assertNull(dataSource.getValidationQuery());
+ Assertions.assertTrue(dataSource.getTestOnBorrow());
+ try (Connection connection = dataSource.getConnection();
+ Statement statement = connection.createStatement()) {
+ statement.execute("CREATE TABLE pool_marker (id INTEGER)");
+ statement.execute("INSERT INTO pool_marker VALUES (1)");
+ }
+ // Each physical connection has its own in-memory database, so the
marker proves reuse.
+ for (int i = 0; i < 3; i++) {
+ try (Connection connection = dataSource.getConnection();
+ Statement statement = connection.createStatement();
+ ResultSet result = statement.executeQuery("SELECT id FROM
pool_marker")) {
+ Assertions.assertTrue(result.next());
+ Assertions.assertEquals(1, result.getInt(1));
+ }
+ }
+ }
+ }
+
+ /** Verifies default validation replaces a closed physical connection. */
+ @Test
+ public void testClosedConnectionIsReplaced() throws SQLException {
+ try (BasicDataSource dataSource = createDataSource(Map.of())) {
+ SQLiteConnection oldConnection;
+ try (Connection connection = dataSource.getConnection()) {
+ oldConnection = connection.unwrap(SQLiteConnection.class);
+ }
+ oldConnection.close();
+ try (Connection connection = dataSource.getConnection()) {
+ Assertions.assertNotSame(oldConnection,
connection.unwrap(SQLiteConnection.class));
+ Assertions.assertTrue(connection.isValid(1));
+ }
+ }
+ }
+
+ /** Verifies a supplied validation query is executed instead of being
overwritten. */
+ @ParameterizedTest
+ @ValueSource(strings = {"SELECT 42", "SELECT * FROM
missing_validation_table"})
+ public void testExplicitValidationQuery(String query) throws SQLException {
+ try (BasicDataSource dataSource =
createDataSource(Map.of("validationQuery", query))) {
+ Assertions.assertEquals(query, dataSource.getValidationQuery());
+ if (query.contains("missing_validation_table")) {
+ Assertions.assertThrows(
+ SQLException.class,
+ () -> {
+ try (Connection connection = dataSource.getConnection()) {
+ Assertions.assertFalse(connection.isClosed());
+ }
+ });
+ } else {
+ try (Connection connection = dataSource.getConnection()) {
+ Assertions.assertTrue(connection.isValid(1));
+ }
+ }
+ }
+ }
+
+ /** Verifies validation settings remain configurable when using default
driver validation. */
+ @Test
+ public void testValidationSettingsPreserved() throws SQLException {
+ try (BasicDataSource dataSource =
+ createDataSource(
+ Map.of("validationQueryTimeout", "5",
JdbcConfig.TEST_ON_BORROW.getKey(), "false"))) {
+ Assertions.assertNull(dataSource.getValidationQuery());
+ Assertions.assertEquals(
+ Duration.ofSeconds(5),
dataSource.getValidationQueryTimeoutDuration());
+ Assertions.assertFalse(dataSource.getTestOnBorrow());
+ try (Connection connection = dataSource.getConnection()) {
+ Assertions.assertTrue(connection.isValid(1));
+ }
+ }
+ }
+
+ /** A driver fixture whose connections do not implement JDBC connection
validation. */
+ public static class UnsupportedValidationDriver extends JDBC {
+ /**
+ * Creates a SQLite connection that rejects calls to {@code isValid()}.
+ *
+ * @param url the JDBC URL
+ * @param properties the connection properties
+ * @return a connection without JDBC validation support
+ * @throws SQLException if opening the connection fails
+ */
+ @Override
+ @Nullable
+ public Connection connect(String url, Properties properties) throws
SQLException {
+ Connection delegate = super.connect(url, properties);
+ if (delegate == null) {
+ return null;
+ }
+ return (Connection)
+ Proxy.newProxyInstance(
+ getClass().getClassLoader(),
+ new Class<?>[] {Connection.class},
+ (proxy, method, args) -> {
+ if (method.getName().equals("isValid")) {
+ throw new SQLFeatureNotSupportedException("isValid is not
supported");
+ }
+ try {
+ return method.invoke(delegate, args);
+ } catch (InvocationTargetException exception) {
+ throw exception.getCause();
+ }
+ });
+ }
+ }
+
+ private static BasicDataSource createDataSource(Map<String, String>
overrides) {
+ Map<String, String> properties = new HashMap<>();
+ properties.put(JdbcConfig.JDBC_DRIVER.getKey(), "org.sqlite.JDBC");
+ properties.put(JdbcConfig.JDBC_URL.getKey(), "jdbc:sqlite::memory:");
+ properties.put(JdbcConfig.USERNAME.getKey(), "test");
+ properties.put(JdbcConfig.PASSWORD.getKey(), "test");
+ properties.put(JdbcConfig.POOL_MAX_SIZE.getKey(), "1");
+ properties.put(JdbcConfig.POOL_MIN_SIZE.getKey(), "1");
+ properties.putAll(overrides);
+ return (BasicDataSource) DataSourceUtils.createDataSource(properties);
+ }
+}
diff --git a/catalogs/catalog-jdbc-mysql/build.gradle.kts
b/catalogs/catalog-jdbc-mysql/build.gradle.kts
index 712e0b5282..1671975993 100644
--- a/catalogs/catalog-jdbc-mysql/build.gradle.kts
+++ b/catalogs/catalog-jdbc-mysql/build.gradle.kts
@@ -51,6 +51,7 @@ dependencies {
testImplementation(project(":server-common"))
testImplementation(libs.awaitility)
+ testImplementation(libs.commons.dbcp2)
testImplementation(libs.junit.jupiter.api)
testImplementation(libs.junit.jupiter.params)
testImplementation(libs.mysql.driver)
diff --git
a/catalogs/catalog-jdbc-mysql/src/test/java/org/apache/gravitino/catalog/mysql/TestMysqlDataSource.java
b/catalogs/catalog-jdbc-mysql/src/test/java/org/apache/gravitino/catalog/mysql/TestMysqlDataSource.java
new file mode 100644
index 0000000000..3a06099404
--- /dev/null
+++
b/catalogs/catalog-jdbc-mysql/src/test/java/org/apache/gravitino/catalog/mysql/TestMysqlDataSource.java
@@ -0,0 +1,144 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied. See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+package org.apache.gravitino.catalog.mysql;
+
+import java.sql.Connection;
+import java.sql.DriverManager;
+import java.sql.ResultSet;
+import java.sql.SQLException;
+import java.sql.Statement;
+import java.util.HashMap;
+import java.util.Map;
+import javax.annotation.Nullable;
+import org.apache.commons.dbcp2.BasicDataSource;
+import org.apache.gravitino.catalog.jdbc.config.JdbcConfig;
+import org.apache.gravitino.catalog.jdbc.utils.DataSourceUtils;
+import org.junit.jupiter.api.AfterAll;
+import org.junit.jupiter.api.Assertions;
+import org.junit.jupiter.api.BeforeAll;
+import org.junit.jupiter.api.Tag;
+import org.junit.jupiter.params.ParameterizedTest;
+import org.junit.jupiter.params.provider.ValueSource;
+import org.testcontainers.containers.MySQLContainer;
+
+/** Exercises catalog switching and connection validation against a real MySQL
server. */
+@Tag("gravitino-docker-test")
+public class TestMysqlDataSource {
+ private static final String TARGET_DATABASE = "pool_target";
+ private static final MySQLContainer<?> MYSQL =
+ new MySQLContainer<>("mysql:8.0").withDatabaseName("pool_bootstrap");
+
+ /** Starts MySQL and creates a target database distinct from the default
database. */
+ @BeforeAll
+ public static void startMysql() throws SQLException {
+ MYSQL.start();
+ try (Connection connection =
+ DriverManager.getConnection(MYSQL.getJdbcUrl(), "root",
MYSQL.getPassword());
+ Statement statement = connection.createStatement()) {
+ statement.execute("CREATE DATABASE " + TARGET_DATABASE);
+ statement.execute(
+ "GRANT ALL ON " + TARGET_DATABASE + ".* TO '" + MYSQL.getUsername()
+ "'@'%'");
+ }
+ }
+
+ /** Stops the test server. */
+ @AfterAll
+ public static void stopMysql() {
+ MYSQL.stop();
+ }
+
+ /** Verifies repeated catalog switches reuse the same physical connection. */
+ @ParameterizedTest
+ @ValueSource(booleans = {false, true})
+ public void testReuseAfterSwitchingCatalog(boolean defaultDatabase) throws
SQLException {
+ try (BasicDataSource dataSource = createDataSource(defaultDatabase)) {
+ long expectedId;
+ try (Connection connection = dataSource.getConnection()) {
+ // Guard the URL rewrite: without a default database, the case must
start with none.
+ Assertions.assertEquals(
+ defaultDatabase ? MYSQL.getDatabaseName() : null,
currentDatabase(connection));
+ connection.setCatalog(TARGET_DATABASE);
+ expectedId = connectionId(connection);
+ }
+ // An idle count of one also occurs when validation destroys and
replaces the connection.
+ // Check the physical server connection ID on every borrow instead.
+ for (int i = 0; i < 100; i++) {
+ try (Connection connection = dataSource.getConnection()) {
+ connection.setCatalog(TARGET_DATABASE);
+ Assertions.assertEquals(expectedId, connectionId(connection));
+ Assertions.assertEquals(TARGET_DATABASE,
currentDatabase(connection));
+ }
+ }
+ }
+ }
+
+ /** Verifies borrow validation rejects and replaces a disconnected
connection. */
+ @ParameterizedTest
+ @ValueSource(booleans = {false, true})
+ public void testDeadConnectionIsReplaced(boolean defaultDatabase) throws
SQLException {
+ try (BasicDataSource dataSource = createDataSource(defaultDatabase)) {
+ long oldId;
+ try (Connection connection = dataSource.getConnection()) {
+ connection.setCatalog(TARGET_DATABASE);
+ oldId = connectionId(connection);
+ }
+ try (Connection observer =
+ DriverManager.getConnection(MYSQL.getJdbcUrl(), "root",
MYSQL.getPassword());
+ Statement statement = observer.createStatement()) {
+ statement.execute("KILL CONNECTION " + oldId);
+ }
+ try (Connection connection = dataSource.getConnection()) {
+ connection.setCatalog(TARGET_DATABASE);
+ Assertions.assertNotEquals(oldId, connectionId(connection));
+ }
+ }
+ }
+
+ private static BasicDataSource createDataSource(boolean defaultDatabase) {
+ Map<String, String> properties = new HashMap<>();
+ String url = MYSQL.getJdbcUrl();
+ if (!defaultDatabase) {
+ url = url.replace("/" + MYSQL.getDatabaseName(), "/");
+ }
+ properties.put(JdbcConfig.JDBC_URL.getKey(), url);
+ properties.put(JdbcConfig.JDBC_DRIVER.getKey(),
MYSQL.getDriverClassName());
+ properties.put(JdbcConfig.USERNAME.getKey(), MYSQL.getUsername());
+ properties.put(JdbcConfig.PASSWORD.getKey(), MYSQL.getPassword());
+ properties.put(JdbcConfig.POOL_MAX_SIZE.getKey(), "1");
+ properties.put(JdbcConfig.POOL_MIN_SIZE.getKey(), "1");
+ return (BasicDataSource) DataSourceUtils.createDataSource(properties);
+ }
+
+ @Nullable
+ private static String currentDatabase(Connection connection) throws
SQLException {
+ try (Statement statement = connection.createStatement();
+ ResultSet result = statement.executeQuery("SELECT DATABASE()")) {
+ Assertions.assertTrue(result.next());
+ return result.getString(1);
+ }
+ }
+
+ private static long connectionId(Connection connection) throws SQLException {
+ try (Statement statement = connection.createStatement();
+ ResultSet result = statement.executeQuery("SELECT CONNECTION_ID()")) {
+ Assertions.assertTrue(result.next());
+ return result.getLong(1);
+ }
+ }
+}
diff --git a/catalogs/catalog-jdbc-postgresql/build.gradle.kts
b/catalogs/catalog-jdbc-postgresql/build.gradle.kts
index a394596ea4..3bb6e1d801 100644
--- a/catalogs/catalog-jdbc-postgresql/build.gradle.kts
+++ b/catalogs/catalog-jdbc-postgresql/build.gradle.kts
@@ -49,6 +49,7 @@ dependencies {
testImplementation(project(":server-common"))
testImplementation(libs.awaitility)
+ testImplementation(libs.commons.dbcp2)
testImplementation(libs.junit.jupiter.api)
testImplementation(libs.junit.jupiter.params)
testImplementation(libs.mysql.driver)
diff --git
a/catalogs/catalog-jdbc-postgresql/src/test/java/org/apache/gravitino/catalog/postgresql/TestPostgreSqlDataSource.java
b/catalogs/catalog-jdbc-postgresql/src/test/java/org/apache/gravitino/catalog/postgresql/TestPostgreSqlDataSource.java
new file mode 100644
index 0000000000..cd3c896d7e
--- /dev/null
+++
b/catalogs/catalog-jdbc-postgresql/src/test/java/org/apache/gravitino/catalog/postgresql/TestPostgreSqlDataSource.java
@@ -0,0 +1,126 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied. See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+package org.apache.gravitino.catalog.postgresql;
+
+import java.sql.Connection;
+import java.sql.DriverManager;
+import java.sql.ResultSet;
+import java.sql.SQLException;
+import java.sql.Statement;
+import java.util.HashMap;
+import java.util.Map;
+import org.apache.commons.dbcp2.BasicDataSource;
+import org.apache.gravitino.catalog.jdbc.config.JdbcConfig;
+import org.apache.gravitino.catalog.jdbc.utils.DataSourceUtils;
+import org.junit.jupiter.api.AfterAll;
+import org.junit.jupiter.api.Assertions;
+import org.junit.jupiter.api.BeforeAll;
+import org.junit.jupiter.api.Tag;
+import org.junit.jupiter.api.Test;
+import org.testcontainers.containers.PostgreSQLContainer;
+
+/** Exercises default validation against a real PostgreSQL server. */
+@Tag("gravitino-docker-test")
+public class TestPostgreSqlDataSource {
+ private static final PostgreSQLContainer<?> POSTGRES = new
PostgreSQLContainer<>("postgres:13");
+
+ /** Starts PostgreSQL and creates a schema for connection state changes. */
+ @BeforeAll
+ public static void startPostgres() throws SQLException {
+ POSTGRES.start();
+ try (Connection connection = observerConnection();
+ Statement statement = connection.createStatement()) {
+ statement.execute("CREATE SCHEMA pool_target");
+ }
+ }
+
+ /** Stops the test server. */
+ @AfterAll
+ public static void stopPostgres() {
+ POSTGRES.stop();
+ }
+
+ /** Verifies schema changes do not prevent reuse with default driver
validation. */
+ @Test
+ public void testReuseAfterSwitchingSchema() throws SQLException {
+ try (BasicDataSource dataSource = createDataSource()) {
+ Assertions.assertNull(dataSource.getValidationQuery());
+ long expectedId;
+ try (Connection connection = dataSource.getConnection()) {
+ connection.setSchema("pool_target");
+ expectedId = connectionId(connection);
+ }
+ for (int i = 0; i < 100; i++) {
+ try (Connection connection = dataSource.getConnection()) {
+ connection.setSchema("pool_target");
+ Assertions.assertEquals(expectedId, connectionId(connection));
+ try (Statement statement = connection.createStatement();
+ ResultSet result = statement.executeQuery("SELECT
current_schema()")) {
+ Assertions.assertTrue(result.next());
+ Assertions.assertEquals("pool_target", result.getString(1));
+ }
+ }
+ }
+ }
+ }
+
+ /** Verifies borrow validation replaces a terminated PostgreSQL connection.
*/
+ @Test
+ public void testDeadConnectionIsReplaced() throws SQLException {
+ try (BasicDataSource dataSource = createDataSource()) {
+ long oldId;
+ try (Connection connection = dataSource.getConnection()) {
+ oldId = connectionId(connection);
+ }
+ try (Connection observer = observerConnection();
+ Statement statement = observer.createStatement();
+ ResultSet result = statement.executeQuery("SELECT
pg_terminate_backend(" + oldId + ")")) {
+ Assertions.assertTrue(result.next());
+ Assertions.assertTrue(result.getBoolean(1));
+ }
+ try (Connection connection = dataSource.getConnection()) {
+ Assertions.assertNotEquals(oldId, connectionId(connection));
+ }
+ }
+ }
+
+ private static BasicDataSource createDataSource() {
+ Map<String, String> properties = new HashMap<>();
+ properties.put(JdbcConfig.JDBC_URL.getKey(), POSTGRES.getJdbcUrl());
+ properties.put(JdbcConfig.JDBC_DRIVER.getKey(),
POSTGRES.getDriverClassName());
+ properties.put(JdbcConfig.USERNAME.getKey(), POSTGRES.getUsername());
+ properties.put(JdbcConfig.PASSWORD.getKey(), POSTGRES.getPassword());
+ properties.put(JdbcConfig.POOL_MAX_SIZE.getKey(), "1");
+ properties.put(JdbcConfig.POOL_MIN_SIZE.getKey(), "1");
+ return (BasicDataSource) DataSourceUtils.createDataSource(properties);
+ }
+
+ private static Connection observerConnection() throws SQLException {
+ return DriverManager.getConnection(
+ POSTGRES.getJdbcUrl(), POSTGRES.getUsername(), POSTGRES.getPassword());
+ }
+
+ private static long connectionId(Connection connection) throws SQLException {
+ try (Statement statement = connection.createStatement();
+ ResultSet result = statement.executeQuery("SELECT pg_backend_pid()")) {
+ Assertions.assertTrue(result.next());
+ return result.getLong(1);
+ }
+ }
+}
diff --git a/docs/jdbc-catalog-connection-validation.md
b/docs/jdbc-catalog-connection-validation.md
new file mode 100644
index 0000000000..b313982491
--- /dev/null
+++ b/docs/jdbc-catalog-connection-validation.md
@@ -0,0 +1,57 @@
+---
+title: "JDBC Catalog Connection Validation"
+slug: "/jdbc-catalog-connection-validation"
+keywords:
+- jdbc
+- catalog
+- connection-pool
+license: "This software is licensed under the Apache License version 2."
+---
+
+This page describes connection validation for JDBC catalogs' datasource pools.
The settings apply
+to the MySQL, PostgreSQL, Doris, StarRocks, ClickHouse, Hologres, and
OceanBase catalogs.
+
+## Connection Validation
+
+JDBC catalogs validate connections on borrow by default using the JDBC driver's
+`Connection.isValid()` method. Gravitino does not force a validation SQL
query, so the driver
+can validate the connection after catalog or schema changes.
+
+The default avoids a connection-reuse problem with MySQL URLs that have no
default database.
+DBCP caches SQL validation as a prepared statement. MySQL Connector/J retains
the database used
+when that statement was prepared and tries to switch back to it when executing
the statement.
+After an application calls `setCatalog()`, switching back to an empty original
database can fail
+even though the connection is still healthy. DBCP then discards that
connection and opens another.
+Connector/J's `Connection.isValid()` uses a ping instead of the cached
statement, so validation
+does not need to restore the statement's original database.
+
+The benefit is preserving healthy pooled connections and avoiding repeated
connection setup and
+authentication caused by failed validation. Validation remains enabled and
disconnected connections
+are still replaced. This does not establish a general throughput improvement:
other drivers may
+implement `isValid()` using SQL, and validation can still require a network
round trip.
+
+For a driver that does not support `Connection.isValid()`, set the catalog
property
+`gravitino.bypass.validationQuery` to a SQL SELECT statement that returns at
least one row.
+Explicit validation queries are passed to DBCP without being overwritten. The
+`jdbc.pool.test-on-borrow` property controls validation on borrow. See the
+[DBCP configuration
reference](https://commons.apache.org/proper/commons-dbcp/configuration.html)
+for other pool validation settings. DBCP-specific properties require the
`gravitino.bypass.`
+prefix in catalog configuration; the prefix is removed before passing them to
DBCP.
+
+For example, to use SQL validation with a driver that does not support
`Connection.isValid()`:
+
+```json
+{
+ "jdbc.pool.test-on-borrow": "true",
+ "gravitino.bypass.validationQuery": "SELECT 1",
+ "gravitino.bypass.validationQueryTimeout": "5"
+}
+```
+
+Choose a validation query supported by the driver and database. Disabling
validation on borrow
+alone does not make a driver without `Connection.isValid()` support
compatible: DBCP also validates
+a connection when initializing its connection factory.
+
+For MySQL drivers affected by the catalog-switching problem, explicitly
setting `SELECT 1` restores
+the cached-statement validation path and can reproduce connection churn. Leave
the query unset when
+the driver supports `Connection.isValid()`.
diff --git a/docs/jdbc-clickhouse-catalog.md b/docs/jdbc-clickhouse-catalog.md
index c1e17bb59d..d01c601285 100644
--- a/docs/jdbc-clickhouse-catalog.md
+++ b/docs/jdbc-clickhouse-catalog.md
@@ -46,6 +46,9 @@ Use your staging validation results and the official
ClickHouse documentation as
### Catalog Properties
+See [JDBC catalog connection
validation](./jdbc-catalog-connection-validation.md) for the default
+validation behavior and SQL validation configuration for drivers without
`Connection.isValid()` support.
+
Pass any JDBC pool property that Gravitino does not define by adding the
`gravitino.bypass.` prefix (for example `gravitino.bypass.maxWaitMillis`). See
[commons-dbcp
configuration](https://commons.apache.org/proper/commons-dbcp/configuration.html)
for details.
When using the JDBC catalog you must provide `jdbc-url`, `jdbc-driver`,
`jdbc-user`, and `jdbc-password`. Common catalog properties are listed
[here](./gravitino-server-config.md#catalog-properties-configuration);
ClickHouse adds no extra catalog-scoped keys.
diff --git a/docs/jdbc-doris-catalog.md b/docs/jdbc-doris-catalog.md
index 74ea49106b..df94982bfa 100644
--- a/docs/jdbc-doris-catalog.md
+++ b/docs/jdbc-doris-catalog.md
@@ -31,6 +31,9 @@ Gravitino saves some system information in schema and table
comments, like
### Catalog Properties
+See [JDBC catalog connection
validation](./jdbc-catalog-connection-validation.md) for the default
+validation behavior and SQL validation configuration for drivers without
`Connection.isValid()` support.
+
Pass to a Doris data source any property that isn't defined by Gravitino by
adding
`gravitino.bypass.` prefix as a catalog property. For example, catalog property
`gravitino.bypass.maxWaitMillis` will pass `maxWaitMillis` to the data source
property.
diff --git a/docs/jdbc-hologres-catalog.md b/docs/jdbc-hologres-catalog.md
index 5bea59a76d..4bd30f1fa4 100644
--- a/docs/jdbc-hologres-catalog.md
+++ b/docs/jdbc-hologres-catalog.md
@@ -36,6 +36,9 @@ Gravitino saves some system information in schema and table
comment, like `(From
### Catalog Properties
+See [JDBC catalog connection
validation](./jdbc-catalog-connection-validation.md) for the default
+validation behavior and SQL validation configuration for drivers without
`Connection.isValid()` support.
+
Pass to a Hologres data source any property that isn't defined by Gravitino by
adding `gravitino.bypass.` prefix as a catalog property. For example, catalog
property `gravitino.bypass.maxWaitMillis` will pass `maxWaitMillis` to the data
source property.
Check the relevant data source configuration in [data source
properties](https://commons.apache.org/proper/commons-dbcp/configuration.html)
diff --git a/docs/jdbc-mysql-catalog.md b/docs/jdbc-mysql-catalog.md
index 9c5126e4ef..978ffdb6aa 100644
--- a/docs/jdbc-mysql-catalog.md
+++ b/docs/jdbc-mysql-catalog.md
@@ -32,6 +32,9 @@ Gravitino saves some system information in schema and table
comment, like `(From
### Catalog Properties
+See [JDBC catalog connection
validation](./jdbc-catalog-connection-validation.md) for the default
+validation behavior and SQL validation configuration for drivers without
`Connection.isValid()` support.
+
Pass to a MySQL data source any property that isn't defined by Gravitino by
adding `gravitino.bypass.` prefix as a catalog property. For example, catalog
property `gravitino.bypass.maxWaitMillis` will pass `maxWaitMillis` to the data
source property.
Check the relevant data source configuration in [data source
properties](https://commons.apache.org/proper/commons-dbcp/configuration.html)
diff --git a/docs/jdbc-oceanbase-catalog.md b/docs/jdbc-oceanbase-catalog.md
index 1d26c0b083..cd44ed518d 100644
--- a/docs/jdbc-oceanbase-catalog.md
+++ b/docs/jdbc-oceanbase-catalog.md
@@ -32,6 +32,9 @@ Apache Gravitino provides the ability to manage OceanBase
metadata.
### Catalog Properties
+See [JDBC catalog connection
validation](./jdbc-catalog-connection-validation.md) for the default
+validation behavior and SQL validation configuration for drivers without
`Connection.isValid()` support.
+
Pass to a OceanBase data source any property that isn't defined by Gravitino
by adding `gravitino.bypass.` prefix as a catalog property. For example,
catalog property `gravitino.bypass.maxWaitMillis` will pass `maxWaitMillis` to
the data source property.
Check the relevant data source configuration in [data source
properties](https://commons.apache.org/proper/commons-dbcp/configuration.html)
diff --git a/docs/jdbc-postgresql-catalog.md b/docs/jdbc-postgresql-catalog.md
index 60ff95dd1b..eac6d40ee0 100644
--- a/docs/jdbc-postgresql-catalog.md
+++ b/docs/jdbc-postgresql-catalog.md
@@ -31,6 +31,9 @@ Gravitino saves some system information in schema and table
comment, like `(From
### Catalog Properties
+See [JDBC catalog connection
validation](./jdbc-catalog-connection-validation.md) for the default
+validation behavior and SQL validation configuration for drivers without
`Connection.isValid()` support.
+
Any property that isn't defined by Gravitino can pass to PostgreSQL data
source by adding `gravitino.bypass.` prefix as a catalog property. For example,
catalog property `gravitino.bypass.maxWaitMillis` will pass `maxWaitMillis` to
the data source property.
Check the relevant data source configuration in [data source
properties](https://commons.apache.org/proper/commons-dbcp/configuration.html)
diff --git a/docs/jdbc-starrocks-catalog.md b/docs/jdbc-starrocks-catalog.md
index f23221a632..f7b9dbd06a 100644
--- a/docs/jdbc-starrocks-catalog.md
+++ b/docs/jdbc-starrocks-catalog.md
@@ -30,6 +30,9 @@ Gravitino saves some system information in table comments,
like
### Catalog Properties
+See [JDBC catalog connection
validation](./jdbc-catalog-connection-validation.md) for the default
+validation behavior and SQL validation configuration for drivers without
`Connection.isValid()` support.
+
Pass to a StarRocks data source any property that isn't defined by Gravitino
by adding
`gravitino.bypass.` prefix as a catalog property. For example, catalog property
`gravitino.bypass.maxWaitMillis` will pass `maxWaitMillis` to the data source
property.
