[jira] [Updated] (FLINK-38851) Support passing arbitrary database options to JDBC Catalog

["Yuepeng Pan (Jira)" <ji...@apache.org>]

     [ https://issues.apache.org/jira/browse/FLINK-38851?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Yuepeng Pan updated FLINK-38851:
--------------------------------
    Fix Version/s: jdbc-4.1.0
                   jdbc-3.4.0
                       (was: jdbc-3.3.0)

> 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
>            Assignee: CHANHAE OH
>            Priority: Blocker
>              Labels: pull-request-available
>             Fix For: jdbc-4.1.0, jdbc-3.4.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)