[
https://issues.apache.org/jira/browse/FLINK-38851?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
CHANHAE OH updated FLINK-38851:
-------------------------------
Summary: Support passing arbitrary database options to JDBC Catalog (was:
[Connector/JDBC] Support passing arbitrary database options to JDBC Catalog)
> Support passing arbitrary database options to JDBC Catalog
> ----------------------------------------------------------
>
> Key: FLINK-38851
> URL: https://issues.apache.org/jira/browse/FLINK-38851
> Project: Flink
> Issue Type: Improvement
> Components: Connectors / JDBC
> Affects Versions: jdbc-4.0.0
> Reporter: CHANHAE OH
> Priority: Blocker
> Fix For: jdbc-4.0.0
>
>
> h1. 1. Motivation
> Currently, when creating a {{JDBC Catalog}} in Flink SQL, there is no way to
> pass arbitrary connection {{properties}} to the JDBC driver. This is a
> significant limitation compared to {{{}JDBC Dynamic Tables{}}}, which support
> a generic properties option.
> This missing feature can lead to connection failures for certain databases
> that require specific driver properties. A common example is connecting to a
> MySQL or MariaDB server with a non-UTC timezone (e.g., 'KST'), which results
> in the following error:
> {code:java}
> com.mysql.cj.exceptions.InvalidConnectionAttributeException: The server time
> zone value 'KST' is unrecognized or represents more than one time zone. You
> must configure either the server or JDBC driver (via the serverTimezone
> configuration property) to use a more specific time zone value if you want to
> utilize time zone support.
> {code}
>
> To resolve this, the user needs to be able to pass properties like
> {{{}serverTimezone=UTC{}}}. Similarly, other use cases, such as passing
> {{stringtype=unspecified}} for PostgreSQL, are not possible with the current
> JDBC Catalog implementation.
> Previous attempts to address this (e.g., PR
> [#74|https://github.com/apache/flink-connector-jdbc/pull/74], PR
> [#83|https://github.com/apache/flink-connector-jdbc/pull/83]) were initiated
> but not completed. This PR provides a complete and robust solution to this
> long-standing issue.
> h1. 2. Solution
> This PR introduces a new configuration option, {{{}database-options{}}}, to
> the {{{}JdbcCatalogFactory{}}}. This option allows users to specify a string
> of key-value pairs that will be appended to the JDBC connection URL.
> * For most databases, the options are appended as a query string, starting
> with ? for the first parameter and using & for subsequent ones.
> * For DB2, which uses a semicolon `;` as a separator, the implementation
> correctly handles this specific syntax.
> This approach provides a flexible and universal way to configure any required
> JDBC driver property without adding database-specific options.
> h1. 3. Implementation Details
> The changes were implemented as follows:
> # JdbcCatalogFactory.java:
> A new ConfigOption named {{database-options}} was added.
> This option is registered in {{optionalOptions()}} to make it non-mandatory.
> # AbstractJdbcCatalog.java:
> The {{getDatabaseUrl()}} method was modified to check for the presence of
> {{{}database-options{}}}.
> If the options exist, they are appended to the base JDBC URL using the
> correct separator ({{{}?{}}} or {{;}} based on the database dialect).
> # Factories and Loaders:
> The database-options value is now passed through the factory chain:
> JdbcCatalogFactory -> JdbcFactoryLoader -> database-specific factories
> (MySqlFactory, PostgresFactory, etc.).
> The method signatures in JdbcFactory.java and JdbcFactoryLoader.java were
> overloaded to accept the new dbOptions parameter.
> # Database-Specific Implementations:
> All existing database-specific catalog implementations (MySQL, PostgreSQL,
> Derby, OceanBase, etc.) and their corresponding factories were updated to
> accept and pass the dbOptions parameter to the AbstractJdbcCatalog
> constructor.
> # Usage Example
> With this change, a user can now create a JDBC catalog for MySQL and specify
> the required timezone property as follows:
> CREATE CATALOG my_mariadb_catalog WITH ( 'type' = 'jdbc', 'base-url' =
> 'jdbc:mysql://localhost:3306', 'username' = 'user', 'password' = 'pass',
> 'default-database' = 'my_db', 'database-options' =
> 'serverTimezone=UTC&tinyInt1isBit=false');
>
> Related PR : [https://github.com/apache/flink-connector-jdbc/pull/183]
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
