You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@kafka.apache.org by "Phil Prudich (JIRA)" <ji...@apache.org> on 2017/05/17 01:46:04 UTC
[jira] [Commented] (KAFKA-5067) java.sql.SQLDataException on
TimeStamp column when using AWS Redshift as a JDBC source
[ https://issues.apache.org/jira/browse/KAFKA-5067?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16013370#comment-16013370 ]
Phil Prudich commented on KAFKA-5067:
-------------------------------------
I don't have this test environment set up, but the "+00" trailing the timestamp value is probably causing the problem -- CURRENT_TIMESTAMP is returned as a string including those offset characters, and they're probably confusing the JDBC Driver's getTimestamp() conversion method.
Per Amazon's docs, current_timestamp is deprecated anyway: http://docs.aws.amazon.com/redshift/latest/dg/Date_functions_header.html#date-functions-deprecated
The docs above suggest SYSDATE or GETDATE(). It would be better to use SYSDATE, as GETDATE() appears to truncate fractional seconds.
On the other hand, the reason that CURRENT_TIMESTAMP is deprecated is because it's executed exclusively on the Redshift leader node, while SYSDATE can be executed on compute nodes -- is that property something that Kafka relies on? If so, then an alternative fix would be "select CAST(CURRENT_TIMESTAMP as TIMESTAMP);", although that relies on the deprecated function.
> java.sql.SQLDataException on TimeStamp column when using AWS Redshift as a JDBC source
> --------------------------------------------------------------------------------------
>
> Key: KAFKA-5067
> URL: https://issues.apache.org/jira/browse/KAFKA-5067
> Project: Kafka
> Issue Type: Bug
> Components: KafkaConnect
> Affects Versions: 0.10.2.0
> Reporter: Curtis Wilde
> Priority: Minor
>
> Kafka Connect throws java.sql.SQLDataException when attempting to use Redshift as a data source.
> When I run the query "select CURRENT_TIMESTAMP;" in a SQL editor it returns:
> 2017-04-13 16:11:25.204925+00
> Full stack trace:
> [2017-04-13 09:44:09,910] ERROR Failed to get current time from DB using query select CURRENT_TIMESTAMP; on database PostgreSQL (io.confluent.connect.jdbc.util.JdbcUtils:205)
> java.sql.SQLDataException: [Amazon][JDBC](10140) Error converting value to Timestamp.
> at com.amazon.exceptions.ExceptionConverter.toSQLException(Unknown Source)
> at com.amazon.utilities.conversion.TypeConverter.convertToTimestamp(Unknown Source)
> at com.amazon.utilities.conversion.TypeConverter.toTimestamp(Unknown Source)
> at com.amazon.jdbc.common.SForwardResultSet.getTimestamp(Unknown Source)
> at io.confluent.connect.jdbc.util.JdbcUtils.getCurrentTimeOnDB(JdbcUtils.java:201)
> at io.confluent.connect.jdbc.source.TimestampIncrementingTableQuerier.executeQuery(TimestampIncrementingTableQuerier.java:169)
> at io.confluent.connect.jdbc.source.TableQuerier.maybeStartQuery(TableQuerier.java:84)
> at io.confluent.connect.jdbc.source.TimestampIncrementingTableQuerier.maybeStartQuery(TimestampIncrementingTableQuerier.java:55)
> at io.confluent.connect.jdbc.source.JdbcSourceTask.poll(JdbcSourceTask.java:200)
> at org.apache.kafka.connect.runtime.WorkerSourceTask.execute(WorkerSourceTask.java:162)
> at org.apache.kafka.connect.runtime.WorkerTask.doRun(WorkerTask.java:139)
> at org.apache.kafka.connect.runtime.WorkerTask.run(WorkerTask.java:182)
> at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511)
> at java.util.concurrent.FutureTask.run(FutureTask.java:266)
> at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
> at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
> at java.lang.Thread.run(Thread.java:745)
> [2017-04-13 09:44:09,912] ERROR Failed to run query for table TimestampIncrementingTableQuerier{name='null', query='<some query>', topicPrefix='<some prefix>', timestampColumn='<timestamp column name>', incrementingColumn='null'}: {} (io.confluent.connect.jdbc.source.JdbcSourceTask:221)
> java.sql.SQLDataException: [Amazon][JDBC](10140) Error converting value to Timestamp.
> at com.amazon.exceptions.ExceptionConverter.toSQLException(Unknown Source)
> at com.amazon.utilities.conversion.TypeConverter.convertToTimestamp(Unknown Source)
> at com.amazon.utilities.conversion.TypeConverter.toTimestamp(Unknown Source)
> at com.amazon.jdbc.common.SForwardResultSet.getTimestamp(Unknown Source)
> at io.confluent.connect.jdbc.util.JdbcUtils.getCurrentTimeOnDB(JdbcUtils.java:201)
> at io.confluent.connect.jdbc.source.TimestampIncrementingTableQuerier.executeQuery(TimestampIncrementingTableQuerier.java:169)
> at io.confluent.connect.jdbc.source.TableQuerier.maybeStartQuery(TableQuerier.java:84)
> at io.confluent.connect.jdbc.source.TimestampIncrementingTableQuerier.maybeStartQuery(TimestampIncrementingTableQuerier.java:55)
> at io.confluent.connect.jdbc.source.JdbcSourceTask.poll(JdbcSourceTask.java:200)
> at org.apache.kafka.connect.runtime.WorkerSourceTask.execute(WorkerSourceTask.java:162)
> at org.apache.kafka.connect.runtime.WorkerTask.doRun(WorkerTask.java:139)
> at org.apache.kafka.connect.runtime.WorkerTask.run(WorkerTask.java:182)
> at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511)
> at java.util.concurrent.FutureTask.run(FutureTask.java:266)
> at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
> at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
> at java.lang.Thread.run(Thread.java:745)
> [2017-04-13 09:44:12,236] INFO Kafka Connect stopping (org.apache.kafka.connect.runtime.Connect:66)
--
This message was sent by Atlassian JIRA
(v6.3.15#6346)