Question

Can't connect to MySQL instance in managed databases

Posted August 21, 2019 4.3k views
MySQL DigitalOcean

Managed MySQL instances were just released yesterday so I decided to spin one up to test it out. I have not been able to connect to the instance using any tool at my disposal. Running OSX 10.14.6

  • Sequel Pro (nightly build) produces this error: MySQL said: SSL connection error: socket layer receive error
  • TablePlus produces this error: SSL connection error: error:00000005:lib(0):func(0):DH lib
  • MySQL cli using this command mysql -u doadmin -*********** -h db-mysql-***-71474-do-user-******-0.db.ondigitalocean.com -P 25060 -D defaultdb produces this error: ERROR 2026 (HY000): SSL connection error: error:00000000:lib(0):func(0):reason(0)

What is required in order to connect to this database? I’ve downloaded the CA Cert that was provided. I’ve installed that via Keychain. I’ve also provided it to Sequel Pro and TablePlus in the proper fields on the login dialogs. What am I missing? This doesn’t seem to be addressed in the connection docs (https://www.digitalocean.com/docs/databases/mysql/how-to/connect/) or the troubleshooting docs (https://www.digitalocean.com/docs/databases/mysql/resources/troubleshoot-connections/).

Add a comment
jhazel
By:
jhazel
Add a comment

These answers are provided by our Community. If you find them useful, show some love by clicking the heart. If you run into issues leave a comment, or add your own answer to help others.

6 answers
jhazel August 22, 2019

Answer:
Okay I think I’ve gotten to the bottom of this. I can’t edit my original question but leaving this here in case someone else runs into it.

The use of the non-standard port must have been blocked on my network. After changing networks and verifying the port is open, it now connects. So I’ve learned from this:

  • This cryptic error that MySQL cli gives you might indicate a blocked port: ERROR 2026 (HY000): SSL connection error: error:00000000:lib(0):func(0):reason(0)
  • There is a (minor) bug in TablePlus 2.8.1 when connecting with ssl. If the connection is initially blocked like it was in my case, the saved instance of that connection will be fubared even if you move to a good network. You have to create a whole new connection and enter credentials and cert again.
  • Sequel Pro (build 5446): As far as I can tell at the moment, it simply does not work with DO managed databases. Always returns an unknown SSL error.
Reply Report
  • vbrodrigo August 22, 2019

    Unfortunately Sequel Pro has no more support. It was my favorite Mysql Client. If you can know how to fix, post it here.

    I can connect mysql8 using TablePlus, but i can’t do anything (SELECT, INSERT and more), using DO standard user. Have you fixed it?

    And have you connected with PHP (using mysqli_connect)?

    Reply Report
    • jhazel August 22, 2019

      Sequel Pro is still under active development. Technically it never really had that great of support. It’s FOSS so you were kind of on your own if you had an issue. The test builds do fix a lot of issues. But at the moment, it’s not working with DO managed stuff.

      TablePlus - I’m finding this to not be a very good piece of software. On top of being unintuitive, it seems to have quite a few bugs. I did finally get commands to work (SELECT, INSERT, etc..) but only after opening the connection, inserting data, then closing and reopening the connection and running the query again.

      As far as mysqli_connect I’m afraid I can’t help with that directly. I’d recommend dumping mysqli for PDO. https://www.saotn.org/how-to-use-ssl-in-php-data-objects-pdo-mysql/

      Reply Report
    • jhazel August 22, 2019

      vbrodrigo, no clue why but my original response to you was flagged and apparently deleted. What I said was simply that I got TablePlus working for queries but it was a hassle and I found several bugs along the way. I’m giving up on it. Querious appears to work much closer to Sequel Pro interface-wise although I’m not totally sold on it yet. As for your issue with PHP can you try doing it using the PDO connection instead of mysqli?

      Reply Report
  • jhazel August 22, 2019
    Reply Report
  • aflores November 12, 2019

    I’m having a difficult time trying to figure out why DO managed databases require you to upgrade local to the same version of mysql that the managed database server is using.

    Doesn’t this seem very inefficient? In order to use an external database, I have to first install it on every machine that intends on using it? Is this because managed databases don’t use SSH, but rather SSL only?

    Reply Report
bobbyiliev August 22, 2019

Hello,

Regarding the MySQL cli, as far as I know on OSX the default MySQL client is 5.7 and you can not use MySQL 5.7 client to connect to MySQL 8. I tested that and I’m getting the same SSL error as you do when using MySQL 5.7 client.

Regarding TablePlus, are you selecting MySQL 8 from the drop-down menu? Also you need to make sure that you are adding the SSL certificate to the CA filed under your TablePlus SSL settings.

Let me know how it goes!
Regards,
Bobby

Reply Report
vbrodrigo August 22, 2019

What is the PHP version compatible with authentication plugin caching_sha2_password? The problem is exactly the this authentication.

Reply Report
PHF August 23, 2019

You can use the latest tet build from sequal pro. That one works.
https://sequelpro.com/test-builds

I hope someone can help me..

After ALTER USER '...'@'%' IDENTIFIED WITH mysql_native_password BY '...';

I can connect commandline via mysql -u etc..

Via php(7.1.12) on 1 server i get SQLSTATE[HY000] [2054] The server requested authentication method unknown to the client

Via php(7.1.12) on another server i get Error: SQLSTATE[HY000] [2006] MySQL server has gone away

How can i use mysql8 with php PDO?

Reply Report
  • jhazel August 23, 2019

    Are you confirming that you’ve gotten Sequel Pro build 5446 working? Cause that’s what I’m on now and I cannot get it to connect.

    Reply Report
vidda22 September 11, 2019

@PHF, @jhazel, I have tried the latest sequel pro build (https://sequelpro.com/test-builds) and it does not work. I used TCP/IP connection with the SSL enabled and passing ca-certificate.crt to it.

Would be lovely to see some good alternatives or ideas for what to try to have Sequel Pro connecting :/

Reply Report
brunokakele December 20, 2019

Updating the mysql-client worked for me.

mysql -V
mysql  Ver 14.14 Distrib 5.7.28, for Linux (x86_64) using  EditLine wrapper

(It was using the 5.7.27 previously)

Reply Report
Submit an Answer

Related

Looking for something else?

Ask a new question Search for more help