jdbc catalog - error read datetime column

[maulanaady]

I have table in mariadb server with datetime columns, when I query it using mysql client, it shows the row, but when I query it using starrocks via jdbc catalog (I used mysql client, too), it shows errors.

Steps to reproduce the behavior (Required)

1. My DDL table in mariadb: CREATE TABLE mydb.mytable ( ID int(11) NOT NULL, INVOICE_TYPE varchar(30) NOT NULL, INVOICE_DATE datetime DEFAULT NULL, DUE_DATE datetime DEFAULT NULL, DATE_CREATED datetime NOT NULL DEFAULT current_timestamp(), DATE_MODIFIED datetime DEFAULT NULL, MODIFIED_BY varchar(50) DEFAULT NULL, PAYMENT_DATE datetime DEFAULT NULL, PROFORMA_DATE datetime DEFAULT NULL, FLAG_TERMIN varchar(5) DEFAULT NULL, PRIMARY KEY (ID) ) ENGINE=InnoDB;

2. insert into `mydb.mytable` values (291860,"1",'2023-11-05 00:00:00','2023-12-01 00:00:00', '2023-11-02 13:30:43','2023-12-04 13:25:34','dummy_name','0000-00-00 00:00:00',NULL,NULL);

3. Create jdbc catalog: CREATE EXTERNAL CATALOG mariadb PROPERTIES ( "type"="jdbc", "user"="myuser", "password"="mypassword", "jdbc_uri"="jdbc:mysql://host:port", "driver_url"="https://repo1.maven.org/maven2/mysql/mysql-connector-java/8.0.28/mysql-connector-java-8.0.28.jar", "driver_class"="com.mysql.cj.jdbc.Driver" );

4. SELECT mariadb.mydb.mytable :

Expected behavior:

+--------+--------------+---------------------+---------------------+---------------------+---------------------+-------------+---------------------+---------------+-------------+ | ID | INVOICE_TYPE | INVOICE_DATE | DUE_DATE | DATE_CREATED | DATE_MODIFIED | MODIFIED_BY | PAYMENT_DATE | PROFORMA_DATE | FLAG_TERMIN | +--------+--------------+---------------------+---------------------+---------------------+---------------------+-------------+---------------------+---------------+-------------+ | 291860 | 1 | 2023-11-05 00:00:00 | 2023-12-01 00:00:00 | 2023-11-02 13:30:43 | 2023-12-04 13:25:34 | dummy_name | 0000-00-00 00:00:00 | NULL | NULL | +--------+--------------+---------------------+---------------------+---------------------+---------------------+-------------+---------------------+---------------+-------------+

Real behavior:

SQL Error [1064] [42000]: 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.translateException(SQLExceptionsMapping.java:99), com.mysql.cj.jdbc.result.ResultSetImpl.getObject(ResultSetImpl.java:1285), com.zaxxer.hikari.pool.HikariProxyResultSet.getObject(HikariProxyResultSet.java), com.starrocks.jdbcbridge.JDBCScanner.getNextChunk(JDBCScanner.java:145)]

StarRocks version

• 3.1.9

[zombee0]

could you add ?ZeroDateTimeBehavior=convertToNull to jdbc_uri and it's supported from 3.1.10

[maulanaady]

same issue already sent before my post, https://github.com/StarRocks/starrocks/issues/24277

[zombee0]

have you upgrade to 3.1.10?