geoffreytran opened a new issue, #30112: URL: https://github.com/apache/doris/issues/30112
### Search before asking - [X] I had searched in the [issues](https://github.com/apache/doris/issues?q=is%3Aissue) and found no similar issues. ### Version 2.0.3 ### What's Wrong? When inserting a record using a prepared statement with parameters for a datetime column using the MySQL datetime DB type, Doris throws the following exception. This does not occur on UPDATE statements. Inserting with the value as a string works correctly, but some connection drivers automatically bind datetime datatypes as datetime over the MySQL protocol. This behavior is failing for insert statements that bind the MYSQL_TIME structure. https://dev.mysql.com/doc/c-api/5.7/en/c-api-prepared-statement-date-handling.html ``` 2024-01-18 03:47:55,333 WARN (thrift-server-pool-3|172) [Coordinator.updateFragmentExecStatus():2275] one instance report fail, query_id=6fa06d4a60c543f8-b9db8baaae6e6ac7 instance_id=6fa06d4a60c543f8-b9db8baaae6e6ac8, error message: (172.16.81.5)[RUNTIME_ERROR]Illegal column UInt64 of first argument of function toDateTime 2024-01-18 03:47:55,334 WARN (thrift-server-pool-3|172) [Coordinator.updateStatus():1129] one instance report fail throw updateStatus(), need cancel. job id: -1, query id: 6fa06d4a60c543f8-b9db8baaae6e6ac7, instance id: 6fa06d4a60c543f8-b9db8baaae6e6ac8, error message: (172.16.81.5)[RUNTIME_ERROR]Illegal column UInt64 of first argument of function toDateTime 2024-01-18 03:47:55,334 WARN (mysql-nio-pool-20|3459) [StmtExecutor.handleInsertStmt():1805] insert failed: (172.16.81.5)[RUNTIME_ERROR]Illegal column UInt64 of first argument of function toDateTime 2024-01-18 03:47:55,335 WARN (mysql-nio-pool-20|3459) [StmtExecutor.handleInsertStmt():1846] handle insert stmt fail: insert_6fa06d4a60c543f8_b9db8baaae6e6ac7 org.apache.doris.common.DdlException: errCode = 2, detailMessage = (172.16.81.5)[RUNTIME_ERROR]Illegal column UInt64 of first argument of function toDateTime at org.apache.doris.common.ErrorReport.reportDdlException(ErrorReport.java:68) ~[doris-fe.jar:1.2-SNAPSHOT] at org.apache.doris.qe.StmtExecutor.handleInsertStmt(StmtExecutor.java:1806) ~[doris-fe.jar:1.2-SNAPSHOT] at org.apache.doris.qe.StmtExecutor.executeByLegacy(StmtExecutor.java:748) ~[doris-fe.jar:1.2-SNAPSHOT] at org.apache.doris.qe.StmtExecutor.execute(StmtExecutor.java:459) ~[doris-fe.jar:1.2-SNAPSHOT] at org.apache.doris.qe.StmtExecutor.execute(StmtExecutor.java:429) ~[doris-fe.jar:1.2-SNAPSHOT] at org.apache.doris.qe.ConnectProcessor.handleQuery(ConnectProcessor.java:435) ~[doris-fe.jar:1.2-SNAPSHOT] at org.apache.doris.qe.ConnectProcessor.dispatch(ConnectProcessor.java:583) ~[doris-fe.jar:1.2-SNAPSHOT] at org.apache.doris.qe.ConnectProcessor.processOnce(ConnectProcessor.java:834) ~[doris-fe.jar:1.2-SNAPSHOT] at org.apache.doris.mysql.ReadListener.lambda$handleEvent$0(ReadListener.java:52) ~[doris-fe.jar:1.2-SNAPSHOT] at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149) ~[?:1.8.0_342] at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624) ~[?:1.8.0_342] at java.lang.Thread.run(Thread.java:750) ~[?:1.8.0_342] 2024-01-18 03:47:55,336 WARN (mysql-nio-pool-20|3459) [StmtExecutor.executeByLegacy():818] errors when abort txn. stmt[584, 6fa06d4a60c543f8-b9db8baaae6e6ac7] org.apache.doris.transaction.TransactionNotFoundException: errCode = 2, detailMessage = transaction not found at org.apache.doris.transaction.DatabaseTransactionMgr.abortTransaction(DatabaseTransactionMgr.java:1369) ~[doris-fe.jar:1.2-SNAPSHOT] at org.apache.doris.transaction.GlobalTransactionMgr.abortTransaction(GlobalTransactionMgr.java:334) ~[doris-fe.jar:1.2-SNAPSHOT] at org.apache.doris.transaction.GlobalTransactionMgr.abortTransaction(GlobalTransactionMgr.java:318) ~[doris-fe.jar:1.2-SNAPSHOT] at org.apache.doris.qe.StmtExecutor.executeByLegacy(StmtExecutor.java:814) ~[doris-fe.jar:1.2-SNAPSHOT] at org.apache.doris.qe.StmtExecutor.execute(StmtExecutor.java:459) ~[doris-fe.jar:1.2-SNAPSHOT] at org.apache.doris.qe.StmtExecutor.execute(StmtExecutor.java:429) ~[doris-fe.jar:1.2-SNAPSHOT] at org.apache.doris.qe.ConnectProcessor.handleQuery(ConnectProcessor.java:435) ~[doris-fe.jar:1.2-SNAPSHOT] at org.apache.doris.qe.ConnectProcessor.dispatch(ConnectProcessor.java:583) ~[doris-fe.jar:1.2-SNAPSHOT] at org.apache.doris.qe.ConnectProcessor.processOnce(ConnectProcessor.java:834) ~[doris-fe.jar:1.2-SNAPSHOT] at org.apache.doris.mysql.ReadListener.lambda$handleEvent$0(ReadListener.java:52) ~[doris-fe.jar:1.2-SNAPSHOT] at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149) ~[?:1.8.0_342] at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624) ~[?:1.8.0_342] at java.lang.Thread.run(Thread.java:750) ~[?:1.8.0_342] ``` ### What You Expected? INSERT statements using parameters with MYSQL_TIME data structures should correctly be processed. ### How to Reproduce? ```sql create table if not exists profile_segments ( id int not null comment "id", created_at datetime not null default current_timestamp(0) comment "Created at" ) engine=olap unique key (id) distributed by hash(id) properties ( "replication_allocation" = "tag.location.default: 3", "bloom_filter_columns" = "user_id", -- "enable_unique_key_merge_on_write" = "true", "store_row_column" = "true", "light_schema_change" = "true" ); ``` SQL query parameters ``` [ Parameters=[ @p0='018d1d6a-92f2-72b3-854e-711e872c8167' (Nullable = false) (Size = 255), @p1='2024-01-18T16:32:20.7256480Z' (DbType = DateTime) ], CommandType='"Text"', CommandTimeout='30' ] ``` ``` SET AUTOCOMMIT = 1; INSERT INTO `profile_segments` (`id`, `created_at`) VALUES (@p0, @p1, @p2); ``` ### Anything Else? _No response_ ### Are you willing to submit PR? - [ ] Yes I am willing to submit a PR! ### Code of Conduct - [X] I agree to follow this project's [Code of Conduct](https://www.apache.org/foundation/policies/conduct) -- This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. To unsubscribe, e-mail: commits-unsubscr...@doris.apache.org.apache.org For queries about this service, please contact Infrastructure at: us...@infra.apache.org --------------------------------------------------------------------- To unsubscribe, e-mail: commits-unsubscr...@doris.apache.org For additional commands, e-mail: commits-h...@doris.apache.org
