Increase read timeout when setting up a connection to MySQL database

nablabits · August 9, 2022, 7:06pm

Hi Metabase community,

Hope you are doing well. I'm trying to set up a connection between metabase and mindsdb such that we can query the prediction tables created there.

I successfully managed to connect both local instances on my machine as the settings and checks on the mindsdb server are just a few and therefore the connection is quite fast.

However due to the configuration of the cloud server (cloud.mindsdb.com) is a bit more elaborated we are hitting a read timeout . I tried several combinations of connection strings like socketTimeout=10000, networkTimeout=10000 or connectTimeout=10000 to no luck. We are trying to optimise the server response but in the meantime is there a way to increase these timeouts?

This is what I get when I connect to the cloud server through the shell:

mysql> status
--------------
mysql  Ver 8.0.30-0ubuntu0.20.04.2 for Linux on x86_64 ((Ubuntu))

Connection id:		1
Current database:	
Current user:		cloud
SSL:			Cipher in use is TLS_AES_256_GCM_SHA384
Current pager:		less
Using outfile:		''
Using delimiter:	;
Server version:		5.7.1-MindsDB-1.0 (MindsDB)
Protocol version:	10
Connection:		cloud.mindsdb.com via TCP/IP

And this is the full stack trace for the error

2022-07-31 15:59:33,108 ERROR api.database :: Cannot connect to Database
java.lang.Exception: Could not connect to address=(host=cloud.mindsdb.com)(port=3306)(type=master) : Read timed out
	at metabase.driver.util$can_connect_with_details_QMARK_.invokeStatic(util.clj:55)
	at metabase.driver.util$can_connect_with_details_QMARK_.doInvoke(util.clj:39)
	at clojure.lang.RestFn.invoke(RestFn.java:442)
	at metabase.api.database$test_database_connection.invokeStatic(database.clj:514)
	at metabase.api.database$test_database_connection.doInvoke(database.clj:503)
	at clojure.lang.RestFn.invoke(RestFn.java:425)
	at metabase.api.database$fn__74783$test_connection_details__74788$fn__74789.invoke(database.clj:563)
	at metabase.api.database$fn__74783$test_connection_details__74788.invoke(database.clj:544)
	at metabase.api.database$fn__74815.invokeStatic(database.clj:580)
	at metabase.api.database$fn__74815.invoke(database.clj:566)
	at compojure.core$wrap_response$fn__28036.invoke(core.clj:160)
	at compojure.core$wrap_route_middleware$fn__28020.invoke(core.clj:132)
	at compojure.core$wrap_route_info$fn__28025.invoke(core.clj:139)
	at compojure.core$wrap_route_matches$fn__28029.invoke(core.clj:151)
	at compojure.core$routes$fn__28048$f__28049.invoke(core.clj:198)
	at compojure.core$routes$fn__28048.invoke(core.clj:200)
	at metabase.server.middleware.auth$enforce_authentication$fn__66776.invoke(auth.clj:14)
	at compojure.core$routes$fn__28048$f__28049.invoke(core.clj:198)
	at compojure.core$routes$fn__28048.invoke(core.clj:200)
	at compojure.core$make_context$handler__28076.invoke(core.clj:289)
	at compojure.core$make_context$fn__28080.invoke(core.clj:299)
	at compojure.core$routes$fn__28048$f__28049.invoke(core.clj:198)
	at compojure.core$routes$fn__28048$f__28049$respond_SINGLEQUOTE___28050.invoke(core.clj:197)
	at compojure.core$make_context$fn__28080.invoke(core.clj:300)
	at compojure.core$routes$fn__28048$f__28049.invoke(core.clj:198)
	at compojure.core$routes$fn__28048$f__28049$respond_SINGLEQUOTE___28050.invoke(core.clj:197)
	at compojure.core$make_context$fn__28080.invoke(core.clj:300)
	at compojure.core$routes$fn__28048$f__28049.invoke(core.clj:198)
	at compojure.core$routes$fn__28048$f__28049$respond_SINGLEQUOTE___28050.invoke(core.clj:197)
	at compojure.core$make_context$fn__28080.invoke(core.clj:300)
	at compojure.core$routes$fn__28048$f__28049.invoke(core.clj:198)
	at compojure.core$routes$fn__28048$f__28049$respond_SINGLEQUOTE___28050.invoke(core.clj:197)
	at compojure.core$make_context$fn__28080.invoke(core.clj:300)
	at compojure.core$routes$fn__28048$f__28049.invoke(core.clj:198)
	at compojure.core$routes$fn__28048$f__28049$respond_SINGLEQUOTE___28050.invoke(core.clj:197)
	at compojure.core$make_context$fn__28080.invoke(core.clj:300)
	at compojure.core$routes$fn__28048$f__28049.invoke(core.clj:198)
	at compojure.core$routes$fn__28048$f__28049$respond_SINGLEQUOTE___28050.invoke(core.clj:197)
	at compojure.core$make_context$fn__28080.invoke(core.clj:300)
	at compojure.core$routes$fn__28048$f__28049.invoke(core.clj:198)
	at compojure.core$routes$fn__28048$f__28049$respond_SINGLEQUOTE___28050.invoke(core.clj:197)
	at compojure.core$make_context$fn__28080.invoke(core.clj:300)
	at compojure.core$routes$fn__28048$f__28049.invoke(core.clj:198)
	at compojure.core$routes$fn__28048$f__28049$respond_SINGLEQUOTE___28050.invoke(core.clj:197)
	at metabase.api.routes$fn__78140$fn__78143.invoke(routes.clj:59)
	at compojure.core$routes$fn__28048$f__28049.invoke(core.clj:198)
	at compojure.core$routes$fn__28048.invoke(core.clj:200)
	at clojure.lang.AFn.applyToHelper(AFn.java:160)
	at clojure.lang.AFn.applyTo(AFn.java:144)
	at clojure.core$apply.invokeStatic(core.clj:667)
	at clojure.core$apply.invoke(core.clj:662)
	at metabase.server.routes$fn__78290$fn__78291.doInvoke(routes.clj:67)
	at clojure.lang.RestFn.invoke(RestFn.java:436)
	at compojure.core$routes$fn__28048$f__28049.invoke(core.clj:198)
	at compojure.core$routes$fn__28048.invoke(core.clj:200)
	at compojure.core$make_context$handler__28076.invoke(core.clj:289)
	at compojure.core$make_context$fn__28080.invoke(core.clj:299)
	at compojure.core$routes$fn__28048$f__28049.invoke(core.clj:198)
	at compojure.core$routes$fn__28048$f__28049$respond_SINGLEQUOTE___28050.invoke(core.clj:197)
	at compojure.core$wrap_route_matches$fn__28029.invoke(core.clj:153)
	at compojure.core$routes$fn__28048$f__28049.invoke(core.clj:198)
	at compojure.core$routes$fn__28048$f__28049$respond_SINGLEQUOTE___28050.invoke(core.clj:197)
	at compojure.core$wrap_route_matches$fn__28029.invoke(core.clj:153)
	at compojure.core$routes$fn__28048$f__28049.invoke(core.clj:198)
	at compojure.core$routes$fn__28048$f__28049$respond_SINGLEQUOTE___28050.invoke(core.clj:197)
	at compojure.core$wrap_route_matches$fn__28029.invoke(core.clj:153)
	at compojure.core$routes$fn__28048$f__28049.invoke(core.clj:198)
	at compojure.core$routes$fn__28048$f__28049$respond_SINGLEQUOTE___28050.invoke(core.clj:197)
	at metabase.server.routes$fn__78276$fn__78278.invoke(routes.clj:45)
	at compojure.core$routes$fn__28048$f__28049.invoke(core.clj:198)
	at compojure.core$routes$fn__28048.invoke(core.clj:200)
	at metabase.server.middleware.exceptions$catch_uncaught_exceptions$fn__75102.invoke(exceptions.clj:98)
	at metabase.server.middleware.exceptions$catch_api_exceptions$fn__75099.invoke(exceptions.clj:86)
	at metabase.server.middleware.log$log_api_call$fn__78760$fn__78761$fn__78762.invoke(log.clj:211)
	at metabase.driver.sql_jdbc.execute.diagnostic$do_with_diagnostic_info.invokeStatic(diagnostic.clj:15)
	at metabase.driver.sql_jdbc.execute.diagnostic$do_with_diagnostic_info.invoke(diagnostic.clj:9)
	at metabase.server.middleware.log$log_api_call$fn__78760$fn__78761.invoke(log.clj:203)
	at toucan.db$_do_with_call_counting.invokeStatic(db.clj:216)
	at toucan.db$_do_with_call_counting.invoke(db.clj:209)
	at metabase.server.middleware.log$log_api_call$fn__78760.invoke(log.clj:202)
	at metabase.server.middleware.browser_cookie$ensure_browser_id_cookie$fn__81996.invoke(browser_cookie.clj:38)
	at metabase.server.middleware.security$add_security_headers$fn__60369.invoke(security.clj:148)
	at metabase.server.middleware.json$wrap_json_body$fn__81135.invoke(json.clj:63)
	at metabase.server.middleware.json$wrap_streamed_json_response$fn__81153.invoke(json.clj:99)
	at metabase.server.middleware.offset_paging$handle_paging$fn__60393.invoke(offset_paging.clj:42)
	at ring.middleware.keyword_params$wrap_keyword_params$fn__82263.invoke(keyword_params.clj:55)
	at ring.middleware.params$wrap_params$fn__82282.invoke(params.clj:77)
	at metabase.server.middleware.misc$maybe_set_site_url$fn__35191.invoke(misc.clj:59)
	at metabase.server.middleware.session$bind_current_user$fn__46663$fn__46664.invoke(session.clj:291)
	at metabase.server.middleware.session$do_with_current_user.invokeStatic(session.clj:270)
	at metabase.server.middleware.session$do_with_current_user.invoke(session.clj:259)
	at metabase.server.middleware.session$bind_current_user$fn__46663.invoke(session.clj:290)
	at metabase.server.middleware.session$wrap_current_user_info$fn__46645.invoke(session.clj:240)
	at metabase.server.middleware.session$wrap_session_id$fn__46629.invoke(session.clj:173)
	at metabase.server.middleware.auth$wrap_api_key$fn__66784.invoke(auth.clj:27)
	at ring.middleware.cookies$wrap_cookies$fn__82183.invoke(cookies.clj:216)
	at metabase.server.middleware.misc$add_content_type$fn__35174.invoke(misc.clj:27)
	at metabase.server.middleware.misc$disable_streaming_buffering$fn__35199.invoke(misc.clj:76)
	at ring.middleware.gzip$wrap_gzip$fn__82225.invoke(gzip.clj:86)
	at metabase.server.middleware.misc$bind_request$fn__35202.invoke(misc.clj:93)
	at metabase.server.middleware.ssl$redirect_to_https_middleware$fn__82012.invoke(ssl.clj:38)
	at metabase.server$async_proxy_handler$fn__78532.invoke(server.clj:73)
	at metabase.server.proxy$org.eclipse.jetty.server.handler.AbstractHandler$ff19274a.handle(Unknown Source)
	at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:127)
	at org.eclipse.jetty.server.Server.handle(Server.java:516)
	at org.eclipse.jetty.server.HttpChannel.lambda$handle$1(HttpChannel.java:400)
	at org.eclipse.jetty.server.HttpChannel.dispatch(HttpChannel.java:645)
	at org.eclipse.jetty.server.HttpChannel.handle(HttpChannel.java:392)
	at org.eclipse.jetty.server.HttpConnection.onFillable(HttpConnection.java:277)
	at org.eclipse.jetty.io.AbstractConnection$ReadCallback.succeeded(AbstractConnection.java:311)
	at org.eclipse.jetty.io.FillInterest.fillable(FillInterest.java:105)
	at org.eclipse.jetty.io.ChannelEndPoint$1.run(ChannelEndPoint.java:104)
	at org.eclipse.jetty.util.thread.QueuedThreadPool.runJob(QueuedThreadPool.java:883)
	at org.eclipse.jetty.util.thread.QueuedThreadPool$Runner.run(QueuedThreadPool.java:1034)
	at java.base/java.lang.Thread.run(Unknown Source)
Caused by: java.sql.SQLNonTransientConnectionException: Could not connect to address=(host=cloud.mindsdb.com)(port=3306)(type=master) : Read timed out
	at org.mariadb.jdbc.internal.util.exceptions.ExceptionFactory.createException(ExceptionFactory.java:73)
	at org.mariadb.jdbc.internal.util.exceptions.ExceptionFactory.create(ExceptionFactory.java:197)
	at org.mariadb.jdbc.internal.protocol.AbstractConnectProtocol.connectWithoutProxy(AbstractConnectProtocol.java:1394)
	at org.mariadb.jdbc.internal.util.Utils.retrieveProxy(Utils.java:635)
	at org.mariadb.jdbc.MariaDbConnection.newConnection(MariaDbConnection.java:150)
	at org.mariadb.jdbc.Driver.connect(Driver.java:89)
	at java.sql/java.sql.DriverManager.getConnection(Unknown Source)
	at java.sql/java.sql.DriverManager.getConnection(Unknown Source)
	at clojure.java.jdbc$get_driver_connection.invokeStatic(jdbc.clj:271)
	at clojure.java.jdbc$get_driver_connection.invoke(jdbc.clj:250)
	at clojure.java.jdbc$get_connection.invokeStatic(jdbc.clj:411)
	at clojure.java.jdbc$get_connection.invoke(jdbc.clj:274)
	at clojure.java.jdbc$db_query_with_resultset_STAR_.invokeStatic(jdbc.clj:1111)
	at clojure.java.jdbc$db_query_with_resultset_STAR_.invoke(jdbc.clj:1093)
	at clojure.java.jdbc$query.invokeStatic(jdbc.clj:1182)
	at clojure.java.jdbc$query.invoke(jdbc.clj:1144)
	at clojure.java.jdbc$query.invokeStatic(jdbc.clj:1160)
	at clojure.java.jdbc$query.invoke(jdbc.clj:1144)
	at metabase.driver.sql_jdbc.connection$can_connect_with_spec_QMARK_.invokeStatic(connection.clj:264)
	at metabase.driver.sql_jdbc.connection$can_connect_with_spec_QMARK_.invoke(connection.clj:261)
	at metabase.driver.sql_jdbc.connection$can_connect_QMARK_.invokeStatic(connection.clj:272)
	at metabase.driver.sql_jdbc.connection$can_connect_QMARK_.invoke(connection.clj:268)
	at metabase.driver.sql_jdbc$fn__83585.invokeStatic(sql_jdbc.clj:35)
	at metabase.driver.sql_jdbc$fn__83585.invoke(sql_jdbc.clj:33)
	at metabase.driver.mysql$fn__81405.invokeStatic(mysql.clj:72)
	at metabase.driver.mysql$fn__81405.invoke(mysql.clj:68)
	at clojure.lang.MultiFn.invoke(MultiFn.java:234)
	at metabase.driver.util$can_connect_with_details_QMARK_$fn__32669.invoke(util.clj:51)
	at metabase.util$do_with_timeout$fn__6204.invoke(util.clj:362)
	at clojure.core$binding_conveyor_fn$fn__5772.invoke(core.clj:2034)
	at clojure.lang.AFn.call(AFn.java:18)
	at java.base/java.util.concurrent.FutureTask.run(Unknown Source)
	at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)
	at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
	... 1 more
Caused by: java.sql.SQLNonTransientConnectionException: Read timed out
	at org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol.handleIoException(AbstractQueryProtocol.java:2091)
	at org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol.readPacket(AbstractQueryProtocol.java:1541)
	at org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol.getResult(AbstractQueryProtocol.java:1520)
	at org.mariadb.jdbc.internal.protocol.AbstractConnectProtocol.readPipelineAdditionalData(AbstractConnectProtocol.java:1033)
	at org.mariadb.jdbc.internal.protocol.AbstractConnectProtocol.postConnectionQueries(AbstractConnectProtocol.java:885)
	at org.mariadb.jdbc.internal.protocol.AbstractConnectProtocol.createConnection(AbstractConnectProtocol.java:600)
	at org.mariadb.jdbc.internal.protocol.AbstractConnectProtocol.connectWithoutProxy(AbstractConnectProtocol.java:1389)
	... 32 more
Caused by: java.net.SocketTimeoutException: Read timed out
	at java.base/java.net.SocketInputStream.socketRead0(Native Method)
	at java.base/java.net.SocketInputStream.socketRead(Unknown Source)
	at java.base/java.net.SocketInputStream.read(Unknown Source)
	at java.base/java.net.SocketInputStream.read(Unknown Source)
	at java.base/java.io.FilterInputStream.read(Unknown Source)
	at org.mariadb.jdbc.internal.io.input.ReadAheadBufferedStream.fillBuffer(ReadAheadBufferedStream.java:131)
	at org.mariadb.jdbc.internal.io.input.ReadAheadBufferedStream.read(ReadAheadBufferedStream.java:104)
	at org.mariadb.jdbc.internal.io.input.StandardPacketInputStream.getPacketArray(StandardPacketInputStream.java:247)
	at org.mariadb.jdbc.internal.io.input.StandardPacketInputStream.getPacket(StandardPacketInputStream.java:218)
	at org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol.readPacket(AbstractQueryProtocol.java:1539)
	... 37 more
2022-07-31 15:59:33,157 DEBUG middleware.log :: POST /api/database 400 3.1 s (1 DB calls) 
{:valid false,
 :dbname "Could not connect to address=(host=cloud.mindsdb.com)(port=3306)(type=master) : Read timed out",
 :message "Could not connect to address=(host=cloud.mindsdb.com)(port=3306)(type=master) : Read timed out"}

Thanks in advance

flamber · August 9, 2022, 7:30pm

Hi @nablabits
Post "Diagnostic Info" from Admin > Troubleshooting.

You're seeing this timeout within 3.1 seconds. Not quite sure what is happening. Are you able to see the query log on the database side to see if Metabase is able to make a connection and query?

Metabase uses the MariaDB Connector/J as driver for MySQL and MariaDB databases.
I don't know if MindsDB requires MySQL Connector/J, but it could look like it, so that would require a custom driver to support MindsDB.

nablabits · August 10, 2022, 4:36am

Hi @flamber thanks for the blazing fast response

Are you able to see the query log on the database side to see if Metabase is able to make a connection and query?

Oh, not me myself, sorry, but one of the devs at mindsdb managed to get a connection up and running by tweaking the config and turning off some checks (he reduced the connection time to 0.5s) .

Metabase uses the MariaDB Connector/J as driver for MySQL and MariaDB databases.
I don't know if MindsDB requires MySQL Connector/J, but it could look like it, so that would require a custom driver to support MindsDB.

He found this clue but not really sure if it's what's happening here. Still, from my understanding, the MariaDB Connector/J works, as per the local connection and the cloud one when it does not spend too much time, so I wonder if there's something on metabase side such that we can minimise the tweaks on mindsdb server . If this is the case, I'm happy to raise an issue on GitHub or even if the change is not very complicated (and with a few guidance ) make a PR

Thanks for your time.

nablabits · August 10, 2022, 4:40am

Ahh, this is the diagnostic info:

{
  "browser-info": {
    "language": "en-US",
    "platform": "Linux x86_64",
    "userAgent": "Mozilla/5.0 (X11; Ubuntu; Linux x86_64; rv:103.0) Gecko/20100101 Firefox/103.0",
    "vendor": ""
  },
  "system-info": {
    "file.encoding": "UTF-8",
    "java.runtime.name": "OpenJDK Runtime Environment",
    "java.runtime.version": "11.0.16+8-post-Ubuntu-0ubuntu120.04",
    "java.vendor": "Ubuntu",
    "java.vendor.url": "https://ubuntu.com/",
    "java.version": "11.0.16",
    "java.vm.name": "OpenJDK 64-Bit Server VM",
    "java.vm.version": "11.0.16+8-post-Ubuntu-0ubuntu120.04",
    "os.name": "Linux",
    "os.version": "5.4.0-122-generic",
    "user.language": "en",
    "user.timezone": "Europe/Madrid"
  },
  "metabase-info": {
    "databases": [
      "h2",
      "mysql"
    ],
    "hosting-env": "unknown",
    "application-database": "h2",
    "application-database-details": {
      "database": {
        "name": "H2",
        "version": "1.4.197 (2018-03-18)"
      },
      "jdbc-driver": {
        "name": "H2 JDBC Driver",
        "version": "1.4.197 (2018-03-18)"
      }
    },
    "run-mode": "prod",
    "version": {
      "date": "2022-06-27",
      "tag": "v0.43.4",
      "branch": "release-x.43.x",
      "hash": "61cc28e"
    },
    "settings": {
      "report-timezone": null
    }
  }
}

flamber · August 10, 2022, 7:58am

@nablabits Please include the link to the Github issue for context. I think only the MindsDB devs would be able to see what is going on, so it's probably better if they try to debug.

nablabits · August 12, 2022, 2:46pm

Hi @flamber, sorry for the late reply and thanks for your interest.

Basically I'm trying to get this done, and unfortunately the conversation with mindsdb devs happened on Slack .

However, when I said raise an issue on GitHub I was meaning something in the metabase code base that could help to reduce the amount of tweaks on mindsdb side but understand that might not be much to do, no worries. If at any point you suddenly have a revelation about something that could work, just let me know and I'll be happy to give it a go

Thanks for your help

flamber · August 12, 2022, 2:56pm

@nablabits Since I don't know what tweaks are needed, then I don't know. But have the MindsDB devs create an issue on our repo with proper details. We will not change some random settings, which might affect existing MySQL and MariaDB setups. If MindsDB would require a custom driver, then they could supply that https://www.metabase.com/docs/latest/developers-guide-drivers.html