Closed jovezhong closed 2 years ago
Root cause is the com.clickhouse.jdbc.ClickhouseDrver will call this SQL as part of the connection setup. We need to change the SQL
Pls change the SQL during JDBC connection setup. This blocks other Java based integration.
I can update the code if you can advise what SQL to change
I am going to fix the JDBC driver myself.
A working SQL can be
select 'default' as currentUser, timezone(), version(),if_null((select to_uint8(value) from system.settings where name='readonly'),0) as readonly
After changing the SQL and I got the other error for Nullable
Exception in thread "main" java.lang.ExceptionInInitializerError
at com.clickhouse.client.http.ClickHouseHttpClient.postRequest(ClickHouseHttpClient.java:90)
at com.clickhouse.client.http.ClickHouseHttpClient.execute(ClickHouseHttpClient.java:114)
at com.clickhouse.client.ClickHouseRequest.execute(ClickHouseRequest.java:1385)
at com.clickhouse.jdbc.internal.ClickHouseConnectionImpl.getServerInfo(ClickHouseConnectionImpl.java:78)
at com.clickhouse.jdbc.internal.ClickHouseConnectionImpl.
further changed Nullable and I got "java.sql.SQLFeatureNotSupportedException: Sorry this driver only supports ClickHouse server 20.7 or above"
Then I changed the version to 21
The JDBC connection can be setup but running select * from table(car_live_data) limit 10
failed with
Apr. 05, 2022 10:51:34 P.M. com.clickhouse.client.data.ClickHouseStreamResponse
SEVERE: Failed to create stream response, closing input stream
java.sql.SQLException: Reached end of input stream after reading 28 of 46 bytes, server ClickHouseNode(addr=http:localhost/
Event select 1
failed
Update: Ken fixed the proton and also patches the JDBC driver. I added one more change and now pure JDBC is okay, and dbeaver is okay. But airbyte is blocked with other reasons
DBeaver UI
JDBC driver is basically. However I still cannot make the airbyte connector working.
but I will close this ticket
(Jove Github Bot) moved this ticket out of the GitHub project(up to 1200 tickets for one project).
The connection can be setup, but when we load the tables, it failed.
Error in Proton (I highly recommend proton can provide an option to print SQL it is about to execute, so that we know exactly what's wrong with the SQL. Currently we only know there is a readonly in the SQL, but don't know the whole SQL)
Update SELECT query from system.query_log where query like '%readonly%'
the SQL is
2022.03.30 21:25:23.028145 [ 399 ] {a1434d43-9333-4833-82c9-faff5cd5acc1} DynamicQueryHandler: Code: 62. DB::Exception: Syntax error: failed at position 123 ('readonly'): readonly FORMAT RowBinaryWithNamesAndTypes. Expected one of: FILTER, OVER, token, DoubleColon, MOD, DIV, NOT, BETWEEN, LIKE, ILIKE, NOT LIKE, NOT ILIKE, IN, NOT IN, GLOBAL IN, GLOBAL NOT IN, IS, AND, OR, QuestionMark, alias, AS, Comma, FROM, PREWHERE, WHERE, GROUP BY, WITH, HAVING, WINDOW, ORDER BY, LIMIT, OFFSET, EMIT, SETTINGS, UNION, EXCEPT, INTERSECT, INTO OUTFILE, FORMAT, end of query. (SYNTAX_ERROR), Stack trace (when copying this message, always include the lines below):
Error from airbyte
airbyte-worker | 2022-03-30 21:25:22 INFO i.a.w.p.a.DefaultAirbyteStreamFactory(lambda$create$0):61 - 2022-03-30 21:25:22 WARN c.n.s.JsonMetaSchema(newValidator):338 - Unknown keyword airbyte_secret - you should define your own Meta Schema. If the keyword is irrelevant for validation, just use a NonValidationKeyword airbyte-worker | 2022-03-30 21:25:23 ERROR i.a.c.i.LineGobbler(voidCall):82 - Exception in thread "main" java.sql.SQLException: Cannot create PoolableConnectionFactory (Code: 62. DB::Exception: Syntax error: failed at position 123 ('readonly'): readonly FORMAT RowBinaryWithNamesAndTypes. Expected one of: FILTER, OVER, token, DoubleColon, MOD, DIV, NOT, BETWEEN, LIKE, ILIKE, NOT LIKE, NOT ILIKE, IN, NOT IN, GLOBAL IN, GLOBAL NOT IN, IS, AND, OR, QuestionMark, alias, AS, Comma, FROM, PREWHERE, WHERE, GROUP BY, WITH, HAVING, WINDOW, ORDER BY, LIMIT, OFFSET, EMIT, SETTINGS, UNION, EXCEPT, INTERSECT, INTO OUTFILE, FORMAT, end of query. (SYNTAX_ERROR) (version 1.0.22) airbyte-worker | 2022-03-30 21:25:23 ERROR i.a.c.i.LineGobbler(voidCall):82 - , server ClickHouseNode(addr=http:localhost/:8123, db=default)@-1694930311)
airbyte-worker | 2022-03-30 21:25:23 ERROR i.a.c.i.LineGobbler(voidCall):82 - at org.apache.commons.dbcp2.BasicDataSource.createPoolableConnectionFactory(BasicDataSource.java:669)
airbyte-worker | 2022-03-30 21:25:23 ERROR i.a.c.i.LineGobbler(voidCall):82 - at org.apache.commons.dbcp2.BasicDataSource.createDataSource(BasicDataSource.java:544)
airbyte-worker | 2022-03-30 21:25:23 ERROR i.a.c.i.LineGobbler(voidCall):82 - at org.apache.commons.dbcp2.BasicDataSource.getConnection(BasicDataSource.java:753)
airbyte-worker | 2022-03-30 21:25:23 ERROR i.a.c.i.LineGobbler(voidCall):82 - at io.airbyte.db.jdbc.DefaultJdbcDatabase.getMetaData(DefaultJdbcDatabase.java:77)
airbyte-worker | 2022-03-30 21:25:23 ERROR i.a.c.i.LineGobbler(voidCall):82 - at io.airbyte.integrations.source.jdbc.AbstractJdbcSource.createDatabase(AbstractJdbcSource.java:300)
airbyte-worker | 2022-03-30 21:25:23 ERROR i.a.c.i.LineGobbler(voidCall):82 - at io.airbyte.integrations.source.jdbc.AbstractJdbcSource.createDatabase(AbstractJdbcSource.java:63)
airbyte-worker | 2022-03-30 21:25:23 ERROR i.a.c.i.LineGobbler(voidCall):82 - at io.airbyte.integrations.source.relationaldb.AbstractDbSource.createDatabaseInternal(AbstractDbSource.java:491)
airbyte-worker | 2022-03-30 21:25:23 ERROR i.a.c.i.LineGobbler(voidCall):82 - at io.airbyte.integrations.source.relationaldb.AbstractDbSource.discover(AbstractDbSource.java:82)
airbyte-worker | 2022-03-30 21:25:23 ERROR i.a.c.i.LineGobbler(voidCall):82 - at io.airbyte.integrations.base.IntegrationRunner.runInternal(IntegrationRunner.java:144)
airbyte-worker | 2022-03-30 21:25:23 ERROR i.a.c.i.LineGobbler(voidCall):82 - at io.airbyte.integrations.base.IntegrationRunner.run(IntegrationRunner.java:105)
airbyte-worker | 2022-03-30 21:25:23 ERROR i.a.c.i.LineGobbler(voidCall):82 - at io.airbyte.integrations.source.timeplus.TimeplusSource.main(TimeplusSource.java:164)
airbyte-worker | 2022-03-30 21:25:23 ERROR i.a.c.i.LineGobbler(voidCall):82 - Caused by: java.sql.SQLException: Code: 62. DB::Exception: Syntax error: failed at position 123 ('readonly'): readonly FORMAT RowBinaryWithNamesAndTypes. Expected one of: FILTER, OVER, token, DoubleColon, MOD, DIV, NOT, BETWEEN, LIKE, ILIKE, NOT LIKE, NOT ILIKE, IN, NOT IN, GLOBAL IN, GLOBAL NOT IN, IS, AND, OR, QuestionMark, alias, AS, Comma, FROM, PREWHERE, WHERE, GROUP BY, WITH, HAVING, WINDOW, ORDER BY, LIMIT, OFFSET, EMIT, SETTINGS, UNION, EXCEPT, INTERSECT, INTO OUTFILE, FORMAT, end of query. (SYNTAX_ERROR) (version 1.0.22)
airbyte-worker | 2022-03-30 21:25:23 ERROR i.a.c.i.LineGobbler(voidCall):82 - , server ClickHouseNode(addr=http:localhost/:8123, db=default)@-1694930311
airbyte-worker | 2022-03-30 21:25:23 ERROR i.a.c.i.LineGobbler(voidCall):82 - at com.clickhouse.jdbc.SqlExceptionUtils.handle(SqlExceptionUtils.java:55)
airbyte-worker | 2022-03-30 21:25:23 ERROR i.a.c.i.LineGobbler(voidCall):82 - at com.clickhouse.jdbc.SqlExceptionUtils.handle(SqlExceptionUtils.java:70)
airbyte-worker | 2022-03-30 21:25:23 ERROR i.a.c.i.LineGobbler(voidCall):82 - at com.clickhouse.jdbc.internal.ClickHouseConnectionImpl.getServerInfo(ClickHouseConnectionImpl.java:85)
airbyte-worker | 2022-03-30 21:25:23 ERROR i.a.c.i.LineGobbler(voidCall):82 - at com.clickhouse.jdbc.internal.ClickHouseConnectionImpl.(ClickHouseConnectionImpl.java:245)
airbyte-worker | 2022-03-30 21:25:23 ERROR i.a.c.i.LineGobbler(voidCall):82 - at com.clickhouse.jdbc.internal.ClickHouseConnectionImpl.(ClickHouseConnectionImpl.java:213)
airbyte-worker | 2022-03-30 21:25:23 ERROR i.a.c.i.LineGobbler(voidCall):82 - at com.clickhouse.jdbc.ClickHouseDriver.connect(ClickHouseDriver.java:145)
airbyte-worker | 2022-03-30 21:25:23 ERROR i.a.c.i.LineGobbler(voidCall):82 - at com.clickhouse.jdbc.ClickHouseDriver.connect(ClickHouseDriver.java:41)
airbyte-worker | 2022-03-30 21:25:23 ERROR i.a.c.i.LineGobbler(voidCall):82 - at org.apache.commons.dbcp2.DriverConnectionFactory.createConnection(DriverConnectionFactory.java:55)
airbyte-worker | 2022-03-30 21:25:23 ERROR i.a.c.i.LineGobbler(voidCall):82 - at org.apache.commons.dbcp2.PoolableConnectionFactory.makeObject(PoolableConnectionFactory.java:355)
airbyte-worker | 2022-03-30 21:25:23 ERROR i.a.c.i.LineGobbler(voidCall):82 - at org.apache.commons.dbcp2.BasicDataSource.validateConnectionFactory(BasicDataSource.java:115)
airbyte-worker | 2022-03-30 21:25:23 ERROR i.a.c.i.LineGobbler(voidCall):82 - at org.apache.commons.dbcp2.BasicDataSource.createPoolableConnectionFactory(BasicDataSource.java:665)
airbyte-worker | 2022-03-30 21:25:23 ERROR i.a.c.i.LineGobbler(voidCall):82 - ... 10 more
airbyte-worker | 2022-03-30 21:25:23 ERROR i.a.c.i.LineGobbler(voidCall):82 - Caused by: java.io.IOException: Code: 62. DB::Exception: Syntax error: failed at position 123 ('readonly'): readonly FORMAT RowBinaryWithNamesAndTypes. Expected one of: FILTER, OVER, token, DoubleColon, MOD, DIV, NOT, BETWEEN, LIKE, ILIKE, NOT LIKE, NOT ILIKE, IN, NOT IN, GLOBAL IN, GLOBAL NOT IN, IS, AND, OR, QuestionMark, alias, AS, Comma, FROM, PREWHERE, WHERE, GROUP BY, WITH, HAVING, WINDOW, ORDER BY, LIMIT, OFFSET, EMIT, SETTINGS, UNION, EXCEPT, INTERSECT, INTO OUTFILE, FORMAT, end of query. (SYNTAX_ERROR) (version 1.0.22)
airbyte-worker | 2022-03-30 21:25:23 ERROR i.a.c.i.LineGobbler(voidCall):82 -
airbyte-worker | 2022-03-30 21:25:23 ERROR i.a.c.i.LineGobbler(voidCall):82 - at com.clickhouse.client.http.HttpUrlConnectionImpl.checkResponse(HttpUrlConnectionImpl.java:130)
airbyte-worker | 2022-03-30 21:25:23 ERROR i.a.c.i.LineGobbler(voidCall):82 - at com.clickhouse.client.http.HttpUrlConnectionImpl.post(HttpUrlConnectionImpl.java:201)
airbyte-worker | 2022-03-30 21:25:23 ERROR i.a.c.i.LineGobbler(voidCall):82 - at com.clickhouse.client.http.ClickHouseHttpClient.postRequest(ClickHouseHttpClient.java:88)
airbyte-worker | 2022-03-30 21:25:23 ERROR i.a.c.i.LineGobbler(voidCall):82 - at com.clickhouse.client.http.ClickHouseHttpClient.execute(ClickHouseHttpClient.java:114)
airbyte-worker | 2022-03-30 21:25:23 ERROR i.a.c.i.LineGobbler(voidCall):82 - at com.clickhouse.client.ClickHouseRequest.execute(ClickHouseRequest.java:1385)
airbyte-worker | 2022-03-30 21:25:23 ERROR i.a.c.i.LineGobbler(voidCall):82 - at com.clickhouse.jdbc.internal.ClickHouseConnectionImpl.getServerInfo(ClickHouseConnectionImpl.java:78)
airbyte-worker | 2022-03-30 21:25:23 ERROR i.a.c.i.LineGobbler(voidCall):82 - ... 18 more
airbyte-worker | 2022-03-30 21:25:23 INFO i.a.w.t.TemporalAttemptExecution(lambda$getWorkerThread$2):158 - Completing future exceptionally...
airbyte-worker | io.airbyte.workers.WorkerException: Discover job subprocess finished with exit code 1
airbyte-worker | at io.airbyte.workers.DefaultDiscoverCatalogWorker.run(DefaultDiscoverCatalogWorker.java:74) ~[io.airbyte-airbyte-workers-0.35.62-alpha.jar:?]
airbyte-worker | at io.airbyte.workers.DefaultDiscoverCatalogWorker.run(DefaultDiscoverCatalogWorker.java:24) ~[io.airbyte-airbyte-workers-0.35.62-alpha.jar:?]
airbyte-worker | at io.airbyte.workers.temporal.TemporalAttemptExecution.lambda$getWorkerThread$2(TemporalAttemptExecution.java:155) ~[io.airbyte-airbyte-workers-0.35.62-alpha.jar:?]
airbyte-worker | at java.lang.Thread.run(Thread.java:833) [?:?]
airbyte-worker | 2022-03-30 21:25:23 INFO i.a.w.t.TemporalAttemptExecution(get):131 - Stopping cancellation check scheduling...
airbyte-worker | 2022-03-30 21:25:23 WARN i.t.i.a.POJOActivityTaskHandler(activityFailureToResult):307 - Activity failure. ActivityId=a148f3a9-7e06-3b19-8f72-46e7e7c9f428, activityType=Run, attempt=1
airbyte-worker | java.util.concurrent.ExecutionException: io.airbyte.workers.WorkerException: Discover job subprocess finished with exit code 1
airbyte-worker | at java.util.concurrent.CompletableFuture.reportGet(CompletableFuture.java:396) ~[?:?]
airbyte-worker | at java.util.concurrent.CompletableFuture.get(CompletableFuture.java:2073) ~[?:?]
airbyte-worker | at io.airbyte.workers.temporal.TemporalAttemptExecution.get(TemporalAttemptExecution.java:129) ~[io.airbyte-airbyte-workers-0.35.62-alpha.jar:?]
airbyte-worker | at io.airbyte.workers.temporal.discover.catalog.DiscoverCatalogActivityImpl.run(DiscoverCatalogActivityImpl.java:82) ~[io.airbyte-airbyte-workers-0.35.62-alpha.jar:?]
airbyte-worker | at jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[?:?]
airbyte-worker | at jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:77) ~[?:?]
airbyte-worker | at jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[?:?]
airbyte-worker | at java.lang.reflect.Method.invoke(Method.java:568) ~[?:?]
airbyte-worker | at io.temporal.internal.activity.POJOActivityTaskHandler$POJOActivityInboundCallsInterceptor.execute(POJOActivityTaskHandler.java:214) ~[temporal-sdk-1.8.1.jar:?]
airbyte-worker | at io.temporal.internal.activity.POJOActivityTaskHandler$POJOActivityImplementation.execute(POJOActivityTaskHandler.java:180) ~[temporal-sdk-1.8.1.jar:?]
airbyte-worker | at io.temporal.internal.activity.POJOActivityTaskHandler.handle(POJOActivityTaskHandler.java:120) ~[temporal-sdk-1.8.1.jar:?]
airbyte-worker | at io.temporal.internal.worker.ActivityWorker$TaskHandlerImpl.handle(ActivityWorker.java:204) ~[temporal-sdk-1.8.1.jar:?]
airbyte-worker | at io.temporal.internal.worker.ActivityWorker$TaskHandlerImpl.handle(ActivityWorker.java:164) ~[temporal-sdk-1.8.1.jar:?]
airbyte-worker | at io.temporal.internal.worker.PollTaskExecutor.lambda$process$0(PollTaskExecutor.java:93) ~[temporal-sdk-1.8.1.jar:?]
airbyte-worker | at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1136) [?:?]
airbyte-worker | at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:635) [?:?]
airbyte-worker | at java.lang.Thread.run(Thread.java:833) [?:?]
airbyte-worker | Caused by: io.airbyte.workers.WorkerException: Discover job subprocess finished with exit code 1
airbyte-worker | at io.airbyte.workers.DefaultDiscoverCatalogWorker.run(DefaultDiscoverCatalogWorker.java:74) ~[io.airbyte-airbyte-workers-0.35.62-alpha.jar:?]
airbyte-worker | at io.airbyte.workers.DefaultDiscoverCatalogWorker.run(DefaultDiscoverCatalogWorker.java:24) ~[io.airbyte-airbyte-workers-0.35.62-alpha.jar:?]
airbyte-worker | at io.airbyte.workers.temporal.TemporalAttemptExecution.lambda$getWorkerThread$2(TemporalAttemptExecution.java:155) ~[io.airbyte-airbyte-workers-0.35.62-alpha.jar:?]
airbyte-worker | ... 1 more
airbyte-worker | 2022-03-30 21:25:23 ERROR i.t.i.s.WorkflowExecuteRunnable(logWorkflowExecutionException):125 - Workflow execution failure WorkflowId=a1c68e05-79e7-4b05-b59c-92c949d2f336, RunId=e034d3cc-b0c3-4bfb-924c-350317561d98, WorkflowType=DiscoverCatalogWorkflow
airbyte-worker | io.temporal.failure.ActivityFailure: scheduledEventId=5, startedEventId=6, activityType='Run', activityId='a148f3a9-7e06-3b19-8f72-46e7e7c9f428', identity='1@d2843eb2d78b', retryState=RETRY_STATE_MAXIMUM_ATTEMPTS_REACHED
airbyte-worker | at java.lang.Thread.getStackTrace(Thread.java:1610) ~[?:?]
airbyte-worker | at io.temporal.internal.sync.ActivityStubBase.execute(ActivityStubBase.java:48) ~[temporal-sdk-1.8.1.jar:?]
airbyte-worker | at io.temporal.internal.sync.ActivityInvocationHandler.lambda$getActivityFunc$0(ActivityInvocationHandler.java:77) ~[temporal-sdk-1.8.1.jar:?]
airbyte-worker | at io.temporal.internal.sync.ActivityInvocationHandlerBase.invoke(ActivityInvocationHandlerBase.java:70) ~[temporal-sdk-1.8.1.jar:?]
airbyte-worker | at jdk.proxy2.$Proxy44.run(Unknown Source) ~[?:?]
airbyte-worker | at io.airbyte.workers.temporal.discover.catalog.DiscoverCatalogWorkflowImpl.run(DiscoverCatalogWorkflowImpl.java:28) ~[io.airbyte-airbyte-workers-0.35.62-alpha.jar:?]
airbyte-worker | at jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[?:?]
airbyte-worker | at jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:77) ~[?:?]
airbyte-worker | at jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[?:?]
airbyte-worker | at java.lang.reflect.Method.invoke(Method.java:568) ~[?:?]
airbyte-worker | at io.temporal.internal.sync.POJOWorkflowImplementationFactory$POJOWorkflowImplementation$RootWorkflowInboundCallsInterceptor.execute(POJOWorkflowImplementationFactory.java:317) ~[temporal-sdk-1.8.1.jar:?]
airbyte-worker | at io.temporal.internal.sync.POJOWorkflowImplementationFactory$POJOWorkflowImplementation.execute(POJOWorkflowImplementationFactory.java:292) ~[temporal-sdk-1.8.1.jar:?]