Sequel Pro and MySQL connection failed

Question

I just installed mysql on mac from Homebrew

brew install mysql

mysql -V

mysql  Ver 8.0.11 for osx10.13 on x86_64 (Homebrew)

from terminal it works and I can login to mysql but from Sequel Pro it says

Unable to connect to host 127.0.0.1, or the request timed out.

Be sure that the address is correct and that you have the necessary privileges, or try increasing the connection timeout (currently 10 seconds).

MySQL said: Authentication plugin 'caching_sha2_password' cannot be loaded: dlopen(/usr/local/lib/plugin/caching_sha2_password.so, 2): image not found

can't figure out what I am missing

Maciej Kwas · Accepted Answer · 2021-01-06T21:58:16.347

209

This is because Sequel Pro is not ready yet for a new kind of user login, as the error states: there is no driver.

mysql + homebrew

Basically you will have to perform some actions manually, however- your database data won't be deleted like in solution below

Go to my.cnf file and in section [mysqld] add line:

default-authentication-plugin=mysql_native_password
Login to mysql server from terminal: run mysql -u root -p, then inside shell execute this command (replacing [password] with your actual password):

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '[password]';
exit from mysql shell with exit and run brew services restart mysql.

Should work.

Quick fix (destructive method)

Quick fix for non-homebrew installs:

Apple Logo > System Preferences > MySQL > Initialize Database, then type your new password and select 'Use legacy password'

After restart you should be able to connect. Do it only on fresh installs, because you may lost your db tables otherwise.

my.cnf

The my.cnf file is located in /etc/my.cnf on Unix/Linux

Alternatives

For those who is still struggling with Sequel Pro problems: Sequel Pro was a great product, but with tons of unresolved issues and last release being dated to 2016 perhaps it's a good idea to look for some alternatives. There is a fork of SequelPro called SequelAce that seems to be pretty stable and up-to-date, it keeps similar functionality, similar look and feel, yet at the same time it is devoid of old Sequel Pro problems

edited Jan 06 '21 at 21:58

answered Jul 04 '18 at 21:23

Maciej Kwas

5,417
2
24
43

15

I can't find MySQL in System Preferences – Hattori Hanzō Jul 05 '18 at 06:15
2

thanks for explain, but can't locate `my.cnf` file on my machine(macOS High Sierra 10.13.5) – Hattori Hanzō Jul 05 '18 at 14:36
2

If there is no such file, you will have to create one: please, have a look at this thread: https://stackoverflow.com/questions/7973927/for-homebrew-mysql-installs-wheres-my-cnf – Maciej Kwas Jul 05 '18 at 21:37
I also don't have mysql in my system preferences, and didn't find the my.cnf inside `/etc`, but found it via `$ brew list mysql | grep my.cnf` in `/usr/local/Cellar/mysql/8.0.12/.bottle/etc/my.cnf` on macOS and mysql installed via homebrew. Editing it there and then following the commands from this answer solved OPs problem for me which I also faced. – swiknaba Sep 27 '18 at 23:52
Hmm... followed the proposed solution. Authentication appears to work, however, now the schema/tables fail to load and a generic "Unexpected error occurred". Issue detailed on the repo here: https://github.com/sequelpro/sequelpro/issues/2699 – TolkienWASP Oct 10 '18 at 19:25
15

This worked great on a fresh `brew install mysql` using `/usr/local/etc/my.cnf` – mintwhip Oct 12 '18 at 23:08
Worked for me. Thanks dude! – Angel S. Moreno Oct 25 '18 at 21:10
didnt work for me, after I did that I started getting more errors :( ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2) – jpganz18 Dec 27 '18 at 23:55
Updated my.cnf file, logged into mysql server, command to change password returned `ERROR 1726 (HY000): Storage engine 'MyISAM' does not support system tables. [mysql.db]` A full uninstall en reinstall fixed this for me: https://coderwall.com/p/os6woq/uninstall-all-those-broken-versions-of-mysql-and-re-install-it-with-brew-on-mac-mavericks – JKL Jan 25 '19 at 09:27
Please take this well deserved and fully earned medal – Xhezairi Mar 06 '19 at 21:39
Is the fault of Sequel Pro? "This is because Sequel Pro is not ready yet for a new kind of user login" sounds pretty vague. Should we use a better client? – Jonny Mar 15 '19 at 02:49
@Jonny at the time of writing the answer none of the clients I knew were able to connect without errors. I don't know how the situation looks like now, but 'better client' question is pretty subjective here, Sequel Pro is a good option and I'm still using it, if you will find better alternative is only up to you. – Maciej Kwas Mar 15 '19 at 15:30
I ended up using an older MySQL version in my docker composer so I could stay with Sequel Pro. – Jonny Mar 15 '19 at 15:34
you are my hero for this. remember that! – aRtoo May 26 '19 at 22:32
It's a great if you are fine with *changing your car, because you are in love with a tire brand.* A secure alternative is changing the tire brand you are in love with. See my answer below. – Aidin May 31 '19 at 22:11
9

I didn't use a password, so I used `ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '';` - this worked for me – Vincent Tang Jun 03 '19 at 03:32
worked for me, just remembering to set it password when connection on your CLI as well (dbeaver, workbench etc...) – Fábio BC Souza Sep 10 '20 at 03:59
On OSX Mojave, the my.cnf file is at /usr/local/etc/my.cnf – Matt Schwartz Oct 25 '20 at 19:14
1

This worked, however only unintentionally adopting the destructive method, which unfortunately comes first. Let me suggest to flip the order, since, like me, someone can ruin their DB, without being aware of this implication. – Gil Shapir Jan 05 '21 at 22:35
Using SequelAce is an easy solution. Thanks. – Joe Huang Apr 23 '21 at 06:18

Aidin · Answer 2 · 2020-11-02T07:37:54.290

40

TL;DR: Sequel Pro is dead since 2016. Don't downgrade your DB because of a tool. Move on to an alternative tool.

Update 2020: Sequel Pro is officially dead but unofficially alive! You can find the "nightly" builds that don't have this issue (i.e. support Mysql 8 auth) in here: https://sequelpro.com/test-builds

All the other solutions here are recommending changing your DB settings (making it less secure, as advertised by MySQL) for the tool you are using. That's not acceptable to me.

I have always been a huge fan of Sequel Pro, even donated to it. But, with all my passion and love, I am sorry if the tool doesn't have any release since 2016. YOLO, and I need to move on!

The alternative I found (from https://stackoverflow.com/a/55235533/2321594, thanks to @arcseldon) is DBeaver which supports MySQL 8's new authentication (non-legacy) method.

PS. The only trick in the tool side, not the DB side is when you are creating a MySQL 8 connection you might need to go to "Driver Properties" (later can be found in Edit Connection) and turn the value of allowPublicKeyRetrieval to true.

I needed this to connect to my MySQL container created using Docker. To have the IP of MySQL be visible to the outside, for any other application in your ecosystem (not just this tool), you should either create a new user in MySQL, or pass -e MYSQL_ROOT_HOST=% in the run-time or as an ENV.

edited Nov 02 '20 at 07:37

answered May 31 '19 at 22:09

Aidin

7,505
2
35
33

I believe @Aidin you gave me a downvote just because my answer does not resolves the OP problem. It's just a local development and local settings- so what's the point? Do I really have to leave software I am familiar with and I like to work with just because you say so? – Maciej Kwas Jun 01 '19 at 15:59
6

Please don't take it personal @MaciejKwas. Despite providing more explanation here, which I appreciate, I am still standing by my vote. The three main reasons are: 1) Maintaining different setup between dev/prod is not recommended in modern software development. It's, in fact, factor X of https://12factor.net/dev-prod-parity . 2) People can use these tools for checking prod as well. You can create a ssh-tunnel/proxy and get to your prod DB via a GUI tool. In such cases, downgrading the DB auth is not an option. 3) Not adapting to change and upgrade is something I don't value, personally. – Aidin Jun 04 '19 at 06:18
1

I'm coming back to a MySQL project I haven't ran locally in a couple years b/c it's been rock solid and i'm so sad to find out that sequel pro is dead :( – Catfish Dec 04 '19 at 00:00
Also choose the MySQL +8 driver in DBeaver as there are multiple drivers to choose from. In the driver properties set allowPublicKeyRetrieval to true and save. – Peheje Mar 07 '20 at 16:51

Ilias Karim · Answer 3 · 2019-11-07T19:17:03.357

20

Assuming you don't have a mysql configuration, echo the following to ~/.my.conf

[mysqld]
default-authentication-plugin=mysql_native_password

Sign into mysql with mysql -u root -p
Set the root user password with ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '[PASSWORD]'; where [PASSWORD] is a password of your choosing.
Restart mysql with e.g. brew services restart mysql

edited Nov 07 '19 at 19:17

answered Jan 16 '19 at 23:56

Ilias Karim

3,228
26
46

score 12 · Answer 4 · answered Jul 15 '20 at 23:40

12

Sequel Pro is officially dead and no longer supports newer MySql features. However, the good news is that it was replaced by Sequel Ace which is available on GitHub and App store. The app is free of charge and looks like official replacement for Sequel Pro as the post was made by one of the collaborators of Sequel Pro.

Ps. I decided to post this as an answer as others have not mentioned that there is now an up-to-date replacement for Sequel Pro

answered Jul 15 '20 at 23:40

Alexander Kucheryuk

435
5
14

Thanks for news, much appreciated. – Hattori Hanzō Jul 16 '20 at 12:33
Finally! Been waiting for a while. – Jonny Jul 24 '20 at 13:41

score 6 · Answer 5 · answered Dec 19 '18 at 13:15

6

If you connect to MySQL via root@127.0.0.1, make sure that you reset its password too!

ALTER USER 'root'@'127.0.0.1' IDENTIFIED WITH mysql_native_password BY '[password]';

answered Dec 19 '18 at 13:15

Xeno

61
1
4

I am getting: `ERROR 3009 (HY000): Column count of mysql.user is wrong. Expected 50, found 49. Created with MySQL 80012, now running 80013. Please use mysql_upgrade to fix this error.` – Chance Smith Jan 12 '19 at 15:35
try this on the terminal: **mysql_upgrade --force -uroot -p** – Xeno Jan 14 '19 at 06:39
mysql_upgrade has now been deprecated and won't work, use mysqld --upgrade=FORCE instead. – ilovecookiez11 Sep 19 '19 at 18:42

score 2 · Answer 6 · answered Jul 23 '20 at 15:26

2

It's working for me. if you are getting this error :

Unable to connect to host 127.0.0.1, or the request timed out.

Be sure that the address is correct and that you have the necessary privileges, or try increasing the connection timeout (currently 10 seconds).

MySQL said: Authentication plugin 'caching_sha2_password' cannot be loaded: dlopen(/usr/local/lib/plugin/caching_sha2_password.so, 2):

Plz try this solution

answered Jul 23 '20 at 15:26

Golam Sorwar

789
8
11

1

Worked like a charm. – Jimmy Adaro Nov 03 '20 at 16:54

score 1 · Answer 7 · answered Feb 18 '20 at 09:59

1

If anyone facing this issue and installed MySQL version >8 through .dmg download from the official link. In that case please use this guideline.

answered Feb 18 '20 at 09:59

amrendra

175
1
10

1

Using a old style password worked for me. Thank you! – Richard Witherspoon Mar 30 '20 at 19:30

Sequel Pro and MySQL connection failed

7 Answers7

mysql + homebrew

Quick fix (destructive method)

my.cnf

Alternatives

Linked