[
https://issues.apache.org/jira/browse/SPARK-50809?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17912767#comment-17912767
]
pengqli commented on SPARK-50809:
---------------------------------
1. MYSQL table: canned_report_summary_daily
{code:java}
CREATE TABLE test.`canned_report_summary_daily` ( `day` date NOT NULL,
`hgssiteid` bigint NOT NULL, `num_of_meetings` bigint DEFAULT NULL, `source`
varchar(50) NOT NULL, `updatetime` timestamp DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`hgssiteid`,`day`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
COLLATE=utf8mb4_bin; {code}
column `hgssiteid` bigint
2. Try to connect the MYSQL by `spark.sql.catalog.tidb.url`
{code:java}
spark.sql("SELECT `day`,`hgssiteid`,`num_of_meetings`,`source`" +
"FROM `tidb`.`test`.`canned_report_summary_daily`" +
"WHERE day >= '2024-12-10' AND day <= '2025-01-09' AND hgssiteid in
('101772291401');") {code}
hgssiteid in ('101772291401') => hgssiteid bigint converted to String
'101772291401' to query
3. spark SQL statement
(`day` IS NOT NULL) AND (`hgssiteid` IS NOT NULL) AND (`day` >= '2024-12-10')
AND (`day` <= '2025-01-09') AND (CAST(`hgssiteid` AS LONGTEXT) =
'101772291401') !image-2025-01-14-15-14-44-639.png|width=740,height=197!
[https://dev.mysql.com/doc/refman/8.4/en/cast-functions.html]
The statement `(CAST(`hgssiteid` AS LONGTEXT) = '101772291401')` is not work in
the jdbc MYSQL/TIDB
The error:
{code:java}
Job aborted due to stage failure: Task 0 in stage 0.0 failed 1 times, most
recent failure: Lost task 0.0 in stage 0.0 (TID 0) (10.140.216.204 executor
driver): java.sql.SQLSyntaxErrorException: You have an error in your SQL
syntax; check the manual that corresponds to your MySQL server version for the
right syntax to use near 'LONGTEXT) = '101772291401') LIMIT 21' at line 1
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:121)
at
com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)
at
com.mysql.cj.jdbc.ClientPreparedStatement.executeInternal(ClientPreparedStatement.java:912)
at
com.mysql.cj.jdbc.ClientPreparedStatement.executeQuery(ClientPreparedStatement.java:968)
at
org.apache.spark.sql.execution.datasources.jdbc.JDBCRDD.compute(JDBCRDD.scala:275)
at org.apache.spark.rdd.RDD.computeOrReadCheckpoint(RDD.scala:367) at
org.apache.spark.rdd.RDD.iterator(RDD.scala:331) at
org.apache.spark.rdd.MapPartitionsRDD.compute(MapPartitionsRDD.scala:52) at
org.apache.spark.rdd.RDD.computeOrReadCheckpoint(RDD.scala:367) at
org.apache.spark.rdd.RDD.iterator(RDD.scala:331) at
org.apache.spark.rdd.MapPartitionsRDD.compute(MapPartitionsRDD.scala:52) at
org.apache.spark.rdd.RDD.computeOrReadCheckpoint(RDD.scala:367) at
org.apache.spark.rdd.RDD.iterator(RDD.scala:331) at
org.apache.spark.scheduler.ResultTask.runTask(ResultTask.scala:93) at
org.apache.spark.TaskContext.runTaskWithListeners(TaskContext.scala:166) at
org.apache.spark.scheduler.Task.run(Task.scala:141) at
org.apache.spark.executor.Executor$TaskRunner.$anonfun$run$4(Executor.scala:620)
at
org.apache.spark.util.SparkErrorUtils.tryWithSafeFinally(SparkErrorUtils.scala:64)
at
org.apache.spark.util.SparkErrorUtils.tryWithSafeFinally$(SparkErrorUtils.scala:61)
at org.apache.spark.util.Utils$.tryWithSafeFinally(Utils.scala:94) at
org.apache.spark.executor.Executor$TaskRunner.run(Executor.scala:623) at
java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128)
at
java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628)
at java.base/java.lang.Thread.run(Thread.java:829)
Driver stacktrace:org.apache.spark.SparkException: Job aborted due to stage
failure: Task 0 in stage 0.0 failed 1 times, most recent failure: Lost task 0.0
in stage 0.0 (TID 0) (10.140.216.204 executor driver):
java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax; check
the manual that corresponds to your MySQL server version for the right syntax
to use near 'LONGTEXT) = '101772291401') LIMIT 21' at line 1
{code}
4.Update the sql statement
`(CAST(`hgssiteid` AS LONGTEXT) = '101772291401')` => `(CAST(`hgssiteid` AS
CHAR) = '101772291401')`
> JDBC MySQL BIGINT Type Conversion Issue
> ---------------------------------------
>
> Key: SPARK-50809
> URL: https://issues.apache.org/jira/browse/SPARK-50809
> Project: Spark
> Issue Type: Bug
> Components: Connect
> Affects Versions: 3.5.4
> Reporter: pengqli
> Priority: Major
> Attachments: image-2025-01-14-15-14-44-639.png
>
>
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
