search

StarRocks / starrocks

StarRocks, a Linux Foundation project, is a next-generation sub-second MPP OLAP database for full analytics scenarios, including multi-dimensional analytics, real-time analytics, and ad-hoc queries.
https://starrocks.io
Apache License 2.0
8.27k stars 1.67k forks source link

jdbc catalog in mysql, jdbc_uri can not pass jdbc parameters #40507

Open wangtf418 opened 5 months ago

wangtf418 commented 5 months ago

jdbc mysql does not support passing jdbc parameters。 When I pass parameters to jdbc_url，database name is placed after parameters，not before "?".

e.g. "jdbc_uri"="jdbc:mysql://172.16.0.241:3306?zeroDateTimeBehavior=CONVERT_TO_NULL", wrong： jdbc:mysql://172.16.0.241:3306?zeroDateTimeBehavior=CONVERT_TO_NULL/zz_med_stg right： jdbc:mysql://172.16.0.241:3306/zz_med_stg?zeroDateTimeBehavior=CONVERT_TO_NULL

Steps to reproduce the behavior (Required)

  1. create catalog CREATE EXTERNAL CATALOG mysql_zz_med_stg PROPERTIES ( "type"="jdbc", "user"="starrocks", "password"="pwdXXXX", "jdbc_uri"="jdbc:mysql://172.16.0.241:3306?zeroDateTimeBehavior=CONVERT_TO_NULL", "driver_url"="file:///opt/modules/starrocks/StarRocks-3.2.2/fe/lib/mysql-connector-j-8.0.33.jar", "driver_class"="com.mysql.jdbc.Driver" );
  2. set CATALOG mysql_zz_med_stg;
  3. use zz_med_stg;
  4. select * from z_user limit 0,1;

Real behavior (Required)

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/zz_med_stg' is not acceptable.

StarRocks version (Required)

Fanvadar commented 3 months ago

I'm also having this issue. Originally I got this error SQL Error [1064] [42000]: getNextChunk failed, error: java.sql.SQLException: Zero date value prohibited, which led me to add the jdbc parameter zeroDateTimeBehavior, which of course does not work...