Trouble Connecting to MySQL database

Hello all,

I’m new to Metabase and trying to get it set up. I’ve installed the app and am currently trying to connect to my local MySQL database. When I enter all the information I get the following error message:

“Couldn’t connect to the database. Please check the connection details.”

I’ve checked the logs for any useful error messages and have not found any other than:

“DEBUG metabase.middleware.log :: POST /api/database/validate 200 9 ms (0 DB calls) Jetty threads: 5/50 (2 idle, 0 queued) (45 total active threads) Queries in flight: 0”

This is the only log message I get when I try to connect. How should I go about debugging this?

Hi @dlarson
Which version of Metabase?
And what do you mean by “installed the app” - are you using the Mac App or how are you running Metabase?

I am using the mac version of Metabase and am using the latest version.

@dlarson
Okay, check that a firewall is not blocking Metabase from outgoing connections - or the firewall on the MySQL not blocking incoming connections from your computer.
Try opening Terminal and see if you can ping your MySQL server.
Remember to check if the port is different from the default 3306.

Hi,

I am having a somewhat similar problem & am unable to connect to Mysql. Running metabase version 0.33 against MySQL 8.0.15-6 (Percona) server on CentOS 7. I am able to connect to the mysql server using a local/remote mysql client with the same user & password without any problems but providing the same credentials via metabase Add Database page(with trustServerCertificate=true), I get “Looks like the username or password is incorrect.” in the log & on the page. When I turn on the toggle button to the left of " This is a large database, so let me choose when Metabase syncs and scans" option & click on Next button, I get the message “Couldn’t connect to the database. Please check the connection details.” on the page but nothing in the log. Here a tail view of the log. I there any way to have more information from the log about what could be the issue. I also looked at /var/log/mysqld.log but that too doesn’t provide any useful information. Please help, I am stuck here for quite a while.

8-30 22:58:06 DEBUG middleware.log :: GET /api/session/properties 200 11.0 ms (1 DB calls) Jetty threads: 8/50 (3 idle, 0 queued) (55 total active threads) Queries in flight: 0
08-30 22:58:06 DEBUG middleware.log :: GET /api/user/current 401 1.6 ms (0 DB calls)
“Unauthenticated”

08-30 22:58:22 DEBUG middleware.log :: POST /api/session 200 225.0 ms (2 DB calls) Jetty threads: 8/50 (3 idle, 0 queued) (55 total active threads) Queries in flight: 0
08-30 22:58:22 DEBUG middleware.log :: GET /api/user/current 200 7.3 ms (3 DB calls) Jetty threads: 8/50 (3 idle, 0 queued) (55 total active threads) Queries in flight: 0
08-30 22:58:22 DEBUG middleware.log :: GET /api/database 200 8.4 ms (3 DB calls) Jetty threads: 8/50 (3 idle, 0 queued) (55 total active threads) Queries in flight: 0
08-30 22:58:22 DEBUG middleware.log :: GET /api/collection/root/items 200 8.7 ms (6 DB calls) Jetty threads: 8/50 (3 idle, 0 queued) (55 total active threads) Queries in flight: 0
08-30 22:58:22 DEBUG middleware.log :: GET /api/collection/root 200 4.3 ms (2 DB calls) Jetty threads: 8/50 (3 idle, 0 queued) (55 total active threads) Queries in flight: 0
08-30 22:58:22 DEBUG middleware.log :: GET /api/database 200 9.2 ms (3 DB calls) Jetty threads: 8/50 (3 idle, 0 queued) (55 total active threads) Queries in flight: 0
08-30 22:58:22 DEBUG middleware.log :: GET /api/automagic-dashboards/database/1/candidates 200 23.4 ms (6 DB calls) Jetty threads: 8/50 (3 idle, 0 queued) (55 total active threads) Queries in flight: 0
08-30 22:58:29 DEBUG middleware.log :: GET /api/session/properties 200 12.7 ms (1 DB calls) Jetty threads: 8/50 (3 idle, 0 queued) (55 total active threads) Queries in flight: 0
08-30 22:58:29 DEBUG middleware.log :: GET /api/setting 200 2.4 ms (0 DB calls) Jetty threads: 8/50 (3 idle, 0 queued) (55 total active threads) Queries in flight: 0
08-30 22:58:29 DEBUG middleware.log :: GET /api/setup/admin_checklist 200 19.3 ms (13 DB calls) Jetty threads: 8/50 (3 idle, 0 queued) (55 total active threads) Queries in flight: 0
08-30 22:59:29 DEBUG middleware.log :: POST /api/database/validate 200 48.4 ms (0 DB calls) Jetty threads: 8/50 (5 idle, 0 queued) (50 total active threads) Queries in flight: 0
08-30 23:00:00 INFO task.send-pulses :: Sending scheduled pulses…
08-30 23:00:14 DEBUG middleware.log :: POST /api/database 400 56.6 ms (0 DB calls)
{:valid false, :dbname “Looks like the username or password is incorrect.”, :message “Looks like the username or password is incorrect.”}

08-30 23:00:14 DEBUG middleware.log :: GET /api/database 200 10.2 ms (3 DB calls) Jetty threads: 8/50 (4 idle, 0 queued) (50 total active threads) Queries in flight: 0
08-30 23:00:24 DEBUG middleware.log :: POST /api/database/validate 200 43.6 ms (0 DB calls) Jetty threads: 8/50 (4 idle, 0 queued) (50 total active threads) Queries in flight: 0
08-30 23:00:31 DEBUG middleware.log :: POST /api/database 400 41.4 ms (0 DB calls)
{:valid false, :dbname “Looks like the username or password is incorrect.”, :message “Looks like the username or password is incorrect.”}

08-30 23:00:31 DEBUG middleware.log :: GET /api/database 200 13.8 ms (3 DB calls) Jetty threads: 8/50 (4 idle, 0 queued) (50 total active threads) Queries in flight: 0
08-30 23:16:38 DEBUG middleware.log :: POST /api/database/validate 200 57.6 ms (0 DB calls) Jetty threads: 8/50 (1 idle, 0 queued) (46 total active threads) Queries in flight: 0

Hi @aejazm
I don’t know the default configuration of Percona, but MySQL 8 needs it’s authentication to be backwards compatible, since Metabase uses the MariaDB connector.
Cannot connect to myql 8 (using password: NO)

Thanks. I did two additional things as follows,

  • Upgraded to Percona version 8.0.16-7 which is the latest from Percona although there is an additional version 8.0.17 release by MySQL(no Percona) although I don’t know if that would make any difference. After the upgrade I still get “Looks like the username or password is incorrect” message on screen & in log
  • By default Percona seems to coming with the stricter authentication so I added the following to mysql configuration file & restarted Percona.

default-authentication-plugin=mysql_native_password

Then the attempt to add the database from the web page gives this error, “Client does not support authentication protocol requested by server. Consider upgrading MariaDB client. plugin was = caching_sha2_password” , Seems like MariaDB’s connector included with Metabase is also enabled with the default MySQL 8 authentication level as I was getting wrong username/password message previously.

All along I am able to successfully connect with the database server using mysql client using same username & password & metabase is running on same machine as the database. I am giving the Host value as localhost & port as 3306.

I feel that the real error is hidden somewhere. Is there any way to increase the log level of metabase to get additional information.

Thanks

@aejazm

You might get better help from Percona forums, but the MariaDB Connector/J does not support MySQL’s new authentication.
I’m not sure what changing the default authentication is going to do with already existing users. Look at the stackoverflow link in the previous link.

But the errors you’re getting are a little different from what is normally returned, when using MySQL 8 with the new authentication module.

Try to use MySQL, or check which settings are different between Percona and MySQL, and adjust those accordingly.

References:
https://github.com/metabase/metabase/blob/release-0.33.x/docs/troubleshooting-guide/datawarehouse.md#mysql-unable-to-log-in-with-correct-credentials
https://jira.mariadb.org/browse/CONJ-663

Thanks a lot for the additional information. I will try what is recommended & will let you know.

After creating a new user in Mysql & then altering the authentication using mysql_native_password finally made it work.

Thanks again.

1 Like