failed to create mysql bridge

[Ranglage]

json config in /etc/clickhouse-jdbc-bridge/config/datasources/mysql.json

{
  "$schema": "../datasource.jschema",
  "mysql": {
    "driverUrls": [
      "https://repo1.maven.org/maven2/mysql/mysql-connector-java/8.0.26/mysql-connector-java-8.0.26.jar"
    ],
    "driverClassName": "com.mysql.cj.jdbc.Driver",
    "jdbcUrl": "jdbc:mysql://127.0.0.1:3306?allowPublicKeyRetrieval=true&useSSL=false&useCompression=false&useOldAliasMetadataBehavior=true&allowMultiQueries=true",
    "username": "root",
    "password": "root",
    "initializationFailTimeout": 0,
    "minimumIdle": 0,
    "maximumPoolSize": 10
  }
}

however, when i execute

SELECT * FROM jdbc('mysql', 'select * from schema.table');

it fails with

[2022-02-03 17:00:06] [INFO   ] Raw query:
show databases
[2022-02-03 17:00:22] [WARNING] Thread Thread[vert.x-eventloop-thread-0,5,main]=Thread[vert.x-eventloop-thread-0,5,main] has been blocked for 15257 ms, time limit is 15000 ms
[2022-02-03 17:00:23] [WARNING] Thread Thread[vert.x-eventloop-thread-0,5,main]=Thread[vert.x-eventloop-thread-0,5,main] has been blocked for 16258 ms, time limit is 15000 ms
[2022-02-03 17:00:24] [WARNING] Thread Thread[vert.x-eventloop-thread-0,5,main]=Thread[vert.x-eventloop-thread-0,5,main] has been blocked for 17257 ms, time limit is 15000 ms
[2022-02-03 17:00:25] [WARNING] Thread Thread[vert.x-eventloop-thread-0,5,main]=Thread[vert.x-eventloop-thread-0,5,main] has been blocked for 18257 ms, time limit is 15000 ms
[2022-02-03 17:00:26] [WARNING] Thread Thread[vert.x-eventloop-thread-0,5,main]=Thread[vert.x-eventloop-thread-0,5,main] has been blocked for 19257 ms, time limit is 15000 ms
[2022-02-03 17:00:27] [WARNING] Thread Thread[vert.x-eventloop-thread-0,5,main]=Thread[vert.x-eventloop-thread-0,5,main] has been blocked for 20257 ms, time limit is 15000 ms
[2022-02-03 17:00:28] [WARNING] Thread Thread[vert.x-eventloop-thread-0,5,main]=Thread[vert.x-eventloop-thread-0,5,main] has been blocked for 21257 ms, time limit is 15000 ms
[2022-02-03 17:00:29] [WARNING] Thread Thread[vert.x-eventloop-thread-0,5,main]=Thread[vert.x-eventloop-thread-0,5,main] has been blocked for 22258 ms, time limit is 15000 ms
[2022-02-03 17:00:30] [WARNING] Thread Thread[vert.x-eventloop-thread-0,5,main]=Thread[vert.x-eventloop-thread-0,5,main] has been blocked for 23258 ms, time limit is 15000 ms
[2022-02-03 17:00:31] [WARNING] Thread Thread[vert.x-eventloop-thread-0,5,main]=Thread[vert.x-eventloop-thread-0,5,main] has been blocked for 24258 ms, time limit is 15000 ms
[2022-02-03 17:00:32] [WARNING] Thread Thread[vert.x-eventloop-thread-0,5,main]=Thread[vert.x-eventloop-thread-0,5,main] has been blocked for 25258 ms, time limit is 15000 ms
[2022-02-03 17:00:33] [WARNING] Thread Thread[vert.x-eventloop-thread-0,5,main]=Thread[vert.x-eventloop-thread-0,5,main] has been blocked for 26259 ms, time limit is 15000 ms
[2022-02-03 17:00:34] [WARNING] Thread Thread[vert.x-eventloop-thread-0,5,main]=Thread[vert.x-eventloop-thread-0,5,main] has been blocked for 27258 ms, time limit is 15000 ms
[2022-02-03 17:00:35] [WARNING] Thread Thread[vert.x-eventloop-thread-0,5,main]=Thread[vert.x-eventloop-thread-0,5,main] has been blocked for 28259 ms, time limit is 15000 ms
[2022-02-03 17:00:36] [WARNING] Thread Thread[vert.x-eventloop-thread-0,5,main]=Thread[vert.x-eventloop-thread-0,5,main] has been blocked for 29259 ms, time limit is 15000 ms
[2022-02-03 17:00:36] [SEVERE ] Failed to respond

So, is the connect sussessed? Why the sql timeout?

[zhicwu]

Judging from the logs you provided, it seems the query timed out after 30 seconds. Perhaps you can increasesocket_timeout in ClickHouse jdbc driver?

There are couple of timeout you may want to tweak:

• datasource timeout, for example: max_execution_time in MariaDB
• JDBC driver timeout, for example: connectTimeout and socketTimeout in MariaDB Connector/J
• JDBC bridge timeout, for examples: queryTimeout in config/server.json, and maxWorkerExecuteTime in config/vertx.json
• ClickHouse timeout like max_execution_time and keep_alive_timeout etc.
• Client timeout, for example: socketTimeout in ClickHouse JDBC driver

[Ranglage]

My MySQL engine is InnoDB. Also, mysql server and ch server are in same machine, so, i can't image "show databases" query will last for 30 sec (, which, after test, will return result asap in mysql client). Any other timeout possibility?

[zhicwu]

Are you running jdbc bridge in a docker container? Which version are you using? Also you'd better download mysql jdbc driver to a local folder and update datasources configuration accordingly, otherwise jdbc bridge will download the driver each time you start the server.

[Ranglage]

Thank you for your reply. Happy Lunar New Year!

I run jdbc bridge barely in ubuntu server. The version is 2.0.7. emm..I've download mysql-driver in /etc/clickhouse-jdbc-bridge/drivers/mysql-connector-java-8.0.26.jar then set driverUrls as

    "driverUrls": [
      "file:///etc/clickhouse-jdbc-bridge/drivers/mysql-connector-java-8.0.26.jar"
    ],
    "driverClassName": "com.mysql.cj.jdbc.Driver",

however jdbc bridge warned

[2022-02-04 08:08:44] [INFO   ] mysql - Starting...
[2022-02-04 08:08:44] [WARNING] Registered driver with driverClassName=com.mysql.cj.jdbc.Driver was not found, trying direct instantiation.
[2022-02-04 08:08:45] [INFO   ] mysql - Start completed.

also, I assigned environment CLASSPATH=/etc/clickhouse-jdbc-bridge/drivers/, but it doesn't work.

Another Question

I found another solution last night just create a table with MySQL engine. So...What is the advantage of jdbc?

[zhicwu]

Happy Chinese New Year :)

The warning message was generated by HikariCP, the connection pool used by JDBC bridge. It's not error so you can omit that.

Have you tried simple query like select * from jdbc('mysql', 'select 1')? Basically, show datasources can be used to understand if all defined datasources were created successfully, while you still need to issue a simple query to test if connection can be made.

I'd suggest you to use docker-compose to spin up the environment to play with.

I found another solution last night just create a table with MySQL engine. So...What is the advantage of jdbc?

As far as I know, it's convenient to use MySQL engine because it's build-in function, and requires no additional process running and/or complex configuration. It's like trino/presto in concept, but there's no connection pool(not sure about latest version) and does not support native query. JDBC bridge on the other hand provides connection pool, load balance(behind a proxy), and native query(e.g. call SP/UDF on MySQL or a long query involving multiple tables etc.).

[Ranglage]

thank you very much busy on developing maybe more question to boring you..