You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@doris.apache.org by zy...@apache.org on 2023/06/28 14:51:24 UTC
[doris] branch master updated: [fix](jdbc) Handling Zero DateTime Values in Non-nullable Columns for JDBC Catalog Reading MySQL (#21296)
This is an automated email from the ASF dual-hosted git repository.
zykkk pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/doris.git
The following commit(s) were added to refs/heads/master by this push:
new 449c8d4568 [fix](jdbc) Handling Zero DateTime Values in Non-nullable Columns for JDBC Catalog Reading MySQL (#21296)
449c8d4568 is described below
commit 449c8d4568cfb9d0bd5e787a0c16e31f0d98425b
Author: zy-kkk <zh...@gmail.com>
AuthorDate: Wed Jun 28 22:51:17 2023 +0800
[fix](jdbc) Handling Zero DateTime Values in Non-nullable Columns for JDBC Catalog Reading MySQL (#21296)
---
.../docker-compose/mysql/init/03-create-table.sql | 22 +++++++++++++---------
.../docker-compose/mysql/init/04-insert.sql | 3 +++
docs/en/docs/lakehouse/multi-catalog/jdbc.md | 14 +++++++++++++-
docs/zh-CN/docs/lakehouse/multi-catalog/jdbc.md | 14 +++++++++++++-
.../doris/external/jdbc/JdbcMySQLClient.java | 12 ++++++++++++
.../jdbc_catalog_p0/test_mysql_jdbc_catalog.out | 4 ++++
.../jdbc_catalog_p0/test_mysql_jdbc_catalog.groovy | 4 +++-
7 files changed, 61 insertions(+), 12 deletions(-)
diff --git a/docker/thirdparties/docker-compose/mysql/init/03-create-table.sql b/docker/thirdparties/docker-compose/mysql/init/03-create-table.sql
index 9845db17bf..f1c45b7b1e 100644
--- a/docker/thirdparties/docker-compose/mysql/init/03-create-table.sql
+++ b/docker/thirdparties/docker-compose/mysql/init/03-create-table.sql
@@ -291,12 +291,16 @@ CREATE TABLE `doris_test`.`auto_default_t` (
PRIMARY KEY (`id`)
) engine=innodb charset=utf8;
-create table doris_test.dt (
- `timestamp0` timestamp(0),
- `timestamp1` timestamp(1),
- `timestamp2` timestamp(2),
- `timestamp3` timestamp(3),
- `timestamp4` timestamp(4),
- `timestamp5` timestamp(5),
- `timestamp6` timestamp(6)
-) engine=innodb charset=utf8;
\ No newline at end of file
+CREATE TABLE doris_test.dt (
+ `timestamp0` timestamp(0) DEFAULT CURRENT_TIMESTAMP(0),
+ `timestamp1` timestamp(1) DEFAULT CURRENT_TIMESTAMP(1),
+ `timestamp2` timestamp(2) DEFAULT CURRENT_TIMESTAMP(2),
+ `timestamp3` timestamp(3) DEFAULT CURRENT_TIMESTAMP(3),
+ `timestamp4` timestamp(4) DEFAULT CURRENT_TIMESTAMP(4),
+ `timestamp5` timestamp(5) DEFAULT CURRENT_TIMESTAMP(5),
+ `timestamp6` timestamp(6) DEFAULT CURRENT_TIMESTAMP(6)
+) ENGINE=INNODB CHARSET=utf8;
+
+CREATE TABLE doris_test.dt_null (
+ `dt` datetime NOT NULL
+) ENGINE=INNODB CHARSET=utf8;
\ No newline at end of file
diff --git a/docker/thirdparties/docker-compose/mysql/init/04-insert.sql b/docker/thirdparties/docker-compose/mysql/init/04-insert.sql
index c164bb3b82..fbbe2221bb 100644
--- a/docker/thirdparties/docker-compose/mysql/init/04-insert.sql
+++ b/docker/thirdparties/docker-compose/mysql/init/04-insert.sql
@@ -1143,3 +1143,6 @@ INSERT INTO doris_test.dt (`timestamp0`, `timestamp1`, `timestamp2`, `timestamp3
VALUES ('2023-06-17 10:00:00', '2023-06-17 10:00:01.1', '2023-06-17 10:00:02.22', '2023-06-17 10:00:03.333',
'2023-06-17 10:00:04.4444', '2023-06-17 10:00:05.55555', '2023-06-17 10:00:06.666666');
+SET SESSION sql_mode=(SELECT REPLACE(@@sql_mode,'STRICT_TRANS_TABLES',''));
+INSERT INTO doris_test.dt_null
+VALUES ('2023-06-17 10:00:00'),('0000-00-00 00:00:00');
diff --git a/docs/en/docs/lakehouse/multi-catalog/jdbc.md b/docs/en/docs/lakehouse/multi-catalog/jdbc.md
index 9e1d804928..7be1ae4557 100644
--- a/docs/en/docs/lakehouse/multi-catalog/jdbc.md
+++ b/docs/en/docs/lakehouse/multi-catalog/jdbc.md
@@ -675,4 +675,16 @@ When Doris connects to OceanBase, it will automatically recognize that OceanBase
unable to find valid certification path to requested target". ClientConnectionId:a92f3817-e8e6-4311-bc21-7c66
```
- In the create Catalog `jdbc_url` the JDBC connection string finally increase `encrypt=false`, such as `"jdbc_url" = "jdbc:sqlserver://127.0.0.1:1433;DataBaseName=doris_test;encrypt=false"`
\ No newline at end of file
+ In the create Catalog `jdbc_url` the JDBC connection string finally increase `encrypt=false`, such as `"jdbc_url" = "jdbc:sqlserver://127.0.0.1:1433;DataBaseName=doris_test;encrypt=false"`
+
+11. Error encountered when reading MySQL datetime type
+
+ ```
+ ERROR 1105 (HY000): errCode = 2, detailMessage = (10.16.10.6)[INTERNAL_ERROR]UdfRuntimeException: get next block failed:
+ CAUSED BY: SQLException: Zero date value prohibited
+ CAUSED BY: DataReadException: Zero date value prohibited
+ ```
+
+ This happens because JDBC can't handle the datetime format 0000-00-00 00:00:00.
+ To address this, append zeroDateTimeBehavior=convertToNull to the jdbc_url when creating the Catalog, e.g., "jdbc_url" = "jdbc:mysql://127.0.0.1:3306/test?zeroDateTimeBehavior=convertToNull".
+ In this case, JDBC will convert 0000-00-00 00:00:00 to null, and then Doris will handle the DateTime column as a nullable type, allowing for successful reading.
\ No newline at end of file
diff --git a/docs/zh-CN/docs/lakehouse/multi-catalog/jdbc.md b/docs/zh-CN/docs/lakehouse/multi-catalog/jdbc.md
index b02180ce82..e706bf3521 100644
--- a/docs/zh-CN/docs/lakehouse/multi-catalog/jdbc.md
+++ b/docs/zh-CN/docs/lakehouse/multi-catalog/jdbc.md
@@ -670,4 +670,16 @@ CREATE CATALOG jdbc_oceanbase PROPERTIES (
unable to find valid certification path to requested target". ClientConnectionId:a92f3817-e8e6-4311-bc21-7c66
```
- 可在创建 Catalog 的 `jdbc_url` 把JDBC连接串最后增加 `encrypt=false` ,如 `"jdbc_url" = "jdbc:sqlserver://127.0.0.1:1433;DataBaseName=doris_test;encrypt=false"`
\ No newline at end of file
+ 可在创建 Catalog 的 `jdbc_url` 把JDBC连接串最后增加 `encrypt=false` ,如 `"jdbc_url" = "jdbc:sqlserver://127.0.0.1:1433;DataBaseName=doris_test;encrypt=false"`
+
+11. 读取 MySQL datetime 类型出现异常
+
+ ```
+ ERROR 1105 (HY000): errCode = 2, detailMessage = (10.16.10.6)[INTERNAL_ERROR]UdfRuntimeException: get next block failed:
+ CAUSED BY: SQLException: Zero date value prohibited
+ CAUSED BY: DataReadException: Zero date value prohibited
+ ```
+
+ 这是因为 JDBC 并不能处理 0000-00-00 00:00:00 这种时间格式，
+ 需要在创建 Catalog 的 `jdbc_url` 把JDBC连接串最后增加 `zeroDateTimeBehavior=convertToNull` ,如 `"jdbc_url" = "jdbc:mysql://127.0.0.1:3306/test?zeroDateTimeBehavior=convertToNull"`
+ 这种情况下，JDBC 会把 0000-00-00 00:00:00 转换成 null，然后 Doris 会把 DateTime 类型的列按照可空类型处理，这样就可以正常读取了。
\ No newline at end of file
diff --git a/fe/fe-core/src/main/java/org/apache/doris/external/jdbc/JdbcMySQLClient.java b/fe/fe-core/src/main/java/org/apache/doris/external/jdbc/JdbcMySQLClient.java
index c5ceefb89d..a6c0ce29a2 100644
--- a/fe/fe-core/src/main/java/org/apache/doris/external/jdbc/JdbcMySQLClient.java
+++ b/fe/fe-core/src/main/java/org/apache/doris/external/jdbc/JdbcMySQLClient.java
@@ -36,8 +36,12 @@ import java.util.Map;
import java.util.function.Consumer;
public class JdbcMySQLClient extends JdbcClient {
+
+ private static boolean convertDateToNull = false;
+
protected JdbcMySQLClient(JdbcClientConfig jdbcClientConfig) {
super(jdbcClientConfig);
+ convertDateToNull = isConvertDatetimeToNull(jdbcClientConfig);
}
@Override
@@ -281,6 +285,9 @@ public class JdbcMySQLClient extends JdbcClient {
if (scale > 6) {
scale = 6;
}
+ if (convertDateToNull) {
+ fieldSchema.setAllowNull(true);
+ }
return ScalarType.createDatetimeV2Type(scale);
}
case "FLOAT":
@@ -331,4 +338,9 @@ public class JdbcMySQLClient extends JdbcClient {
return Type.UNSUPPORTED;
}
}
+
+ private boolean isConvertDatetimeToNull(JdbcClientConfig jdbcClientConfig) {
+ // Check if the JDBC URL contains "zeroDateTimeBehavior=convertToNull".
+ return jdbcClientConfig.getJdbcUrl().contains("zeroDateTimeBehavior=convertToNull");
+ }
}
diff --git a/regression-test/data/jdbc_catalog_p0/test_mysql_jdbc_catalog.out b/regression-test/data/jdbc_catalog_p0/test_mysql_jdbc_catalog.out
index e1a5431b00..458b5fa14a 100644
--- a/regression-test/data/jdbc_catalog_p0/test_mysql_jdbc_catalog.out
+++ b/regression-test/data/jdbc_catalog_p0/test_mysql_jdbc_catalog.out
@@ -252,6 +252,10 @@ VIEW_TABLE_USAGE
-- !dt --
2023-06-17T10:00 2023-06-17T10:00:01.100 2023-06-17T10:00:02.220 2023-06-17T10:00:03.333 2023-06-17T10:00:04.444400 2023-06-17T10:00:05.555550 2023-06-17T10:00:06.666666
+-- !dt_null --
+\N
+2023-06-17T10:00
+
-- !test_insert1 --
doris1 18
diff --git a/regression-test/suites/jdbc_catalog_p0/test_mysql_jdbc_catalog.groovy b/regression-test/suites/jdbc_catalog_p0/test_mysql_jdbc_catalog.groovy
index 81373b502d..5551217c98 100644
--- a/regression-test/suites/jdbc_catalog_p0/test_mysql_jdbc_catalog.groovy
+++ b/regression-test/suites/jdbc_catalog_p0/test_mysql_jdbc_catalog.groovy
@@ -50,6 +50,7 @@ suite("test_mysql_jdbc_catalog", "p0") {
String test_insert2 = "test_insert2";
String auto_default_t = "auto_default_t";
String dt = "dt";
+ String dt_null = "dt_null";
sql """drop catalog if exists ${catalog_name} """
@@ -57,7 +58,7 @@ suite("test_mysql_jdbc_catalog", "p0") {
"type"="jdbc",
"user"="root",
"password"="123456",
- "jdbc_url" = "jdbc:mysql://127.0.0.1:${mysql_port}/doris_test?useSSL=false",
+ "jdbc_url" = "jdbc:mysql://127.0.0.1:${mysql_port}/doris_test?useSSL=false&zeroDateTimeBehavior=convertToNull",
"driver_url" = "https://doris-community-test-1308700295.cos.ap-hongkong.myqcloud.com/jdbc_driver/mysql-connector-java-8.0.25.jar",
"driver_class" = "com.mysql.cj.jdbc.Driver"
);"""
@@ -103,6 +104,7 @@ suite("test_mysql_jdbc_catalog", "p0") {
order_qt_information_schema """ show tables from information_schema; """
order_qt_auto_default_t """insert into ${auto_default_t}(name) values('a'); """
order_qt_dt """select * from ${dt}; """
+ order_qt_dt_null """select * from ${dt_null} order by 1; """
// test insert
String uuid1 = UUID.randomUUID().toString();
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org