Troubleshooting MySQL DB over SSH connection

I have been using two local MySQL databases and I really like what Metabase is able to do with the data so I’m trying to connect to my production instances. They require connecting over SSH with keys, though, and I’m having trouble setting that up correctly in Metabase.

I’ve setup all the keys and user accounts and can verify that I’m able to SSH on the command line from the Metabase server to the production database server.

I’ve copied all the SSH user info info Metabase when trying to add the new database, but all I get is a message “Couldn’t connect to the database. Please check the connection details.” When I check the Metabase logs (through the UI), all I can find are these entries (some identifying data obscured):
[e2ebc18c-225b-4697-a5e4-87b318650243] 2020-10-01T12:31:14-04:00 ERROR metabase.driver.util Database connection error [e2ebc18c-225b-4697-a5e4-87b318650243] 2020-10-01T12:31:14-04:00 ERROR metabase.api.database Cannot connect to Database
[e2ebc18c-225b-4697-a5e4-87b318650243] 2020-10-01T12:31:14-04:00 DEBUG metabase.middleware.log POST /api/database/validate 200 49.4 ms (0 DB calls) App DB connections: 0/7 Jetty threads: 3/50 (4 idle, 0 queued) (67 total active threads) Queries in flight: 0 (0 queued)
[e2ebc18c-225b-4697-a5e4-87b318650243] 2020-10-01T12:31:14-04:00 WARN org.apache.sshd.client.keyverifier.AcceptAllServerKeyVerifier Server at /XXX:22 presented unverified EC key: SHA256:XXX

Can anyone help me narrow down what to troubleshoot? I’ve also included Metabase’s debug info below.

Thanks for any assistance!

{
“browser-info”: {
“language”: “en-US”,
“platform”: “MacIntel”,
“userAgent”: “Mozilla/5.0 (Macintosh; Intel Mac OS X 10_14_6) AppleWebKit/605.1.15 (KHTML, like Gecko) Version/13.0.1 Safari/605.1.15”,
“vendor”: “Apple Computer, Inc.”
},
“system-info”: {
“file.encoding”: “UTF-8”,
“java.runtime.name”: “OpenJDK Runtime Environment”,
“java.runtime.version”: “11.0.8+10-post-Ubuntu-0ubuntu120.04”,
“java.vendor”: “Ubuntu”,
“java.vendor.url”: “https://ubuntu.com/”,
“java.version”: “11.0.8”,
“java.vm.name”: “OpenJDK 64-Bit Server VM”,
“java.vm.version”: “11.0.8+10-post-Ubuntu-0ubuntu120.04”,
“os.name”: “Linux”,
“os.version”: “5.4.0-48-generic”,
“user.language”: “en”,
“user.timezone”: “Etc/UTC”
},
“metabase-info”: {
“databases”: [
“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”: “2020-09-15”,
“tag”: “v0.36.6”,
“branch”: “release-0.36.x”,
“hash”: “cb258fb”
},
“settings”: {
“report-timezone”: “US/Eastern”
}
}
}

Hi @Greg
It seems like the same problem as Error Trying to Add Database
Also, you should migrate away from H2 if you’re using Metabase in production:
https://www.metabase.com/docs/latest/operations-guide/migrating-from-h2.html

Thanks for the reply. I will def take your advice on the H2 migration later today.

I’m not sure how helpful the problem you linked to is, unfortunately. It seems like it might be the same problem, or at least closely related, and I’ll certainly keep trying re-entering the information I have (since I don’t have any other leads!).

I’d love if there was just a way to get a more informative error message that at least told me whether or not the SSH connection was successful or not so I’d have a little more focus on what to troubleshoot.

@Greg You could try starting with extra logging on that namespace - example:

java -Dlog4j.configuration="https://log4j.us/templates/metabase?debug=org.apache.sshd.client" -jar metabase.jar

Extra logging?? You’re speaking my language!

I will def give that a shot this weekend. Thanks for the tip. I might be back! :slight_smile:

Welp, I did the migration to MySQL for Metabase’s database, then tried entering all the information again and what do you know… worked like a charm! I wish I knew what happened, but I’ll settle for having it all working as desired. :slight_smile:

Thanks for your time, @flamber.

I’m getting this myself. Not much on the Googles about this one and re-entering hasn’t resolved the issue.

What actually is going on with this error?

2021-02-14 04:22:48,672 WARN keyverifier.AcceptAllServerKeyVerifier :: Server at /REDACTED:22 presented unverified EC key: SHA256:U0q8q0msqFXZT6kawdf6ck3n5thESzDw4m/Van4uF3Q
2021-02-14 04:22:48,777 ERROR driver.util :: Database connection error
java.lang.NullPointerException: null

The AcceptAllServerKeyVerifier class implies to me that it should be accepting any new key and the log line is only a WARN line. That java.lang.NullPointerException looks more problematic but it’s unclear if they are actually related to each other.

1 Like

Did you verify that you’re able to connect over the tunnel from the command line just using mysql?

For the record, to simplify things I ended up using autossh to persist the tunnel and expose it as a different local port (3307). That let me enter in the database connection information without asking Metabase to bother with the SSH bit. (In my head, I’m also pretending there’s some small performance improvement with this, as well.)

So there is no solution without migrating to mysql database?

I don’t think anyone said that.

The upcoming 0.39.0 will have more stable SSH tunnels - and reconnect, when down. This should solve some of the problems that you might have been facing.

Alternatively is to not use the built-in tunnel, but manually create it outside of Metabase:
https://www.metabase.com/docs/latest/administration-guide/01-managing-databases.html#what-if-the-built-in-ssh-tunnels-dont-fit-my-needs