The `zeroDateTimeBehavior` parameter cannot be set for jdbc_uri of JDBC Catalog

[xiaomokk]

Steps to reproduce the behavior (Required)

1. create table item_tmp in mysql, database name: perfn

   CREATE TABLE item_tmp
   (
      i_item_sk        integer  NOT NULL,
      i_item_id        char(16) NOT NULL,
      i_rec_start_date date,
      i_rec_end_date   date,
      PRIMARY KEY (i_item_sk)
   );

2. load data to mysql table from csv

   The contents of the '/tmp/item_tmp.dat' file are as follows:

   18|AAAAAAAAABAAAAAA|2001-10-27||

   login mysql client:

   mysql --local-infile=1 -u example -p

   load data to perfn.item_tmp:

   mysql> load data local infile '/tmp/item_tmp.dat' into table perfn.item_tmp fields terminated BY '|';
   Query OK, 1 row affected, 1 warning (0.00 sec)
   Records: 1  Deleted: 0  Skipped: 0  Warnings: 1
   
   mysql> select * from perfn.item_tmp;
   +-----------+------------------+------------------+----------------+
   | i_item_sk | i_item_id        | i_rec_start_date | i_rec_end_date |
   +-----------+------------------+------------------+----------------+
   |        18 | AAAAAAAAABAAAAAA | 2001-10-27       | 0000-00-00     |
   +-----------+------------------+------------------+----------------+
   1 row in set (0.00 sec)

3. create external catalog in StarRocks and query mysql data from StarRocks

   CREATE EXTERNAL CATALOG mysql_catalog_1
   PROPERTIES
   (
      "type"="jdbc",
      "user"="test_user",
      "password"="example_pwd",
      "jdbc_uri"="jdbc:mysql://127.0.0.1:3306",
      "driver_url"="file:/opt/starrocks/jdbc/mysql-connector-java-8.0.28.jar",
      "driver_class"="com.mysql.cj.jdbc.Driver"
   );

   mysql> use mysql_catalog_1.perfn;
   Reading table information for completion of table and column names
   You can turn off this feature to get a quicker startup with -A
   
   Database changed
   mysql> 
   mysql> select i_item_sk, i_rec_start_date, i_rec_end_date from item_tmp;
   ERROR 1064 (HY000): getNextChunk failed, error: java.sql.SQLException: Zero date value prohibited[com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:129), com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97), com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:89), com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:63), com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:73), com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.tr
   mysql> 

4. create external catalog in StarRocks with 'zeroDateTimeBehavior'

   CREATE EXTERNAL CATALOG mysql_catalog_2
   PROPERTIES
   (
      "type"="jdbc",
      "user"="test_user",
      "password"="example_pwd",
      "jdbc_uri"="jdbc:mysql://127.0.0.1:3306?zeroDateTimeBehavior=CONVERT_TO_NULL",
      "driver_url"="file:/opt/starrocks/jdbc/mysql-connector-java-8.0.28.jar",
      "driver_class"="com.mysql.cj.jdbc.Driver"
   );

   mysql> 
   mysql> use mysql_catalog_2.perfn;
   Reading table information for completion of table and column names
   You can turn off this feature to get a quicker startup with -A
   
   Database changed
   mysql> 
   mysql> 
   mysql> select i_item_sk, i_rec_start_date, i_rec_end_date from item_tmp;
   ERROR 1064 (HY000): open JDBCScanner failed, error: com.zaxxer.hikari.pool.HikariPool$PoolInitializationException: Failed to initialize pool: The connection property 'zeroDateTimeBehavior' acceptable values are: 'CONVERT_TO_NULL', 'EXCEPTION' or 'ROUND'. The value 'CONVERT_TO_NULL/perfn' is not acceptable.[com.zaxxer.hikari.pool.HikariPool.throwPoolInitializationException(HikariPool.java:595), com.zaxxer.hikari.pool.HikariPool.checkFailFast(HikariPool.java:581), com.zaxxer.hikari.pool.HikariPool.<init>(HikariPool.java:115), c
   mysql> 

Expected behavior (Required)

set zeroDateTimeBehavior=CONVERT_TO_NULL, and then zero date will be converted to null

Real behavior (Required)

Looks like the parameter is incorrectly parsed.

StarRocks version (Required)

• 3.0.0 48f4d81

[righBai]

up

[righBai]

Is there any other way to add parameters? I have also encountered similar issues

[maulanaady]

Up, I have same issue.. I have a table with datetime column and NOT NULL constraint for that column, when I insert value '0000-00-00 00:00:00' to that column, and execute select query from the table via mysql client, it shows the row, but when I query it using jdbc catalog, it shows error: SQL Error [1064] [42000]: Unexpected NULL value occurs on NOT NULL column[DUE_DATE]

[zombee0]

have you upgrade to 3.1.10, from 3.1.10 we support this config.