jdbc - 汇合: ERROR Failed to run query for table TimestampIncrementingTableQuerier mysql-jdbc

Question

我正在尝试对 MySQL 使用模式时间戳，由于我的表大小为 2.6 GB，因此行数有限。

以下是我正在使用的连接器属性:

{
        "name": "jdbc_source_mysql_registration_query",
        "config": {
                 "connector.class": "io.confluent.connect.jdbc.JdbcSourceConnector",
                 "key.converter": "io.confluent.connect.avro.AvroConverter",
                 "key.converter.schema.registry.url": "http://localhost:8081",
                 "value.converter": "io.confluent.connect.avro.AvroConverter",
                 "value.converter.schema.registry.url": "http://localhost:8081",
                 "connection.url": "jdbc:mysql://localhost:3310/users?zeroDateTimeBehavior=ROUND&useCursorFetch=true&defaultFetchSize=1000&user=kotesh&password=kotesh",
                 "query": "SELECT matriid,DateUpdated  from users.employee WHERE date(DateUpdated)>='2018-11-28' ",
                 "mode": "timestamp",
                 "timestamp.column.name": "DateUpdated",
                 "validate.non.null": "false",
                 "topic.prefix": "mysql-prod-kot-"
        }
}

我得到如下:

INFO TimestampIncrementingTableQuerier{table=null, query='SELECT matriid,DateUpdated from users.employee WHERE date(DateUpdated)>='2018-11-28'', topicPrefix='mysql-prod-kot-', incrementingColumn='', timestampColumns=[DateUpdated]} prepared SQL query: SELECT matriid,DateUpdated from users.employee WHERE date(DateUpdated)>='2018-11-28' WHERE DateUpdated > ? AND DateUpdated < ? ORDER BY DateUpdated ASC (io.confluent.connect.jdbc.source.TimestampIncrementingTableQuerier:161) [2018-11-29 17:29:00,981] ERROR Failed to run query for table TimestampIncrementingTableQuerier{table=null, query='SELECT matriid,DateUpdated from users.employee WHERE date(DateUpdated)>='2018-11-28'', topicPrefix='mysql-prod-kot-', incrementingColumn='', timestampColumns=[DateUpdated]}: {} (io.confluent.connect.jdbc.source.JdbcSourceTask:328) 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 'WHERE DateUpdated > '1970-01-01 00:00:00.0' AND DateUpdated < '2018-11-29 17' at line 1

Answer 1

发生这种情况是因为您试图同时使用 "mode": "timestamp" 和 query。 TimestampIncrementingTableQuerier 将 WHERE 子句附加到与 query 中现有 WHERE 子句冲突的查询。

JDBC source connector docs对此很清楚:

query

If specified, the query to perform to select new or updated rows. Use this setting if you want to join tables, select subsets of columns in a table, or filter data. If used, this connector will only copy data using this query -- whole-table copying will be disabled. Different query modes may still be used for incremental updates, but in order to properly construct the incremental query, it must be possible to append a WHERE clause to this query (i.e. no WHERE clauses may be used). If you use a WHERE clause, it must handle incremental queries itself.

作为解决方法，您可以将查询修改为(取决于您使用的 SQL 风格)

SELECT * FROM ( SELECT * FROM table WHERE ...)

或

WITH a AS
   SELECT * FROM b
    WHERE ...
SELECT * FROM a

例如，在您的情况下，查询应该是

"query":"SELECT * FROM (SELECT matriid,DateUpdated  from users.employee WHERE date(DateUpdated)>='2018-11-28') o"