5

I am working on macOS ver 10.13.5 - the newest one actually.

I usually work with Laravel projects (5.6), and so I am using brew services isntaller to make local enviroment working.

I am using PHP v 7.1.16, MySQL 8.0.11 and Valet in v. 2.0.12

PHP and MySQL installed through Brew.

I've been working without any problems for like 3 months in the past, but the day when I had to reinstall my os finally come.

After reinstalling macOS with the fresh copy, and installing each service in the same version as it's been before, I am getting some weird error while trying to connect to the MySQL database through SequelPro.

Here's some logs:

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

That's weird, as I've been working with the same stuff without any problems for quite a while...

So I'd appreciate any advices on how to deal with this problem. Also I'd like to notice that I've read almost everything about this problem, but I wasn't able to find at least one with configuration like mine (most of them described this problem as while working with local database provided by mysql or some other services provider).

Ash
  • 6,483
  • 7
  • 29
  • 37
  • You can check default plugin used for all users after login as root user in mysql server `select user, plugin from mysql.user \G;` if it is caching_sha2_password then you need to change it to mysql_native_password by using `ALTER USER root@localhost IDENTIFIED WITH mysql_native_password BY 'PASSWORD';` – Girish Gupta Sep 29 '22 at 07:25

1 Answers1

18

The error message appears because MySQL v8 changed the default authentication plugin / method, which has no backwards compatibility with older clients. The error can be worked around by using the legacy authentication method, which can be enabled for the root user by doing the following:

After installing MySQL, authenticate using the CLI e.g

mysql -uroot

Then run the following command to use the old authentication method:

ALTER USER root@localhost IDENTIFIED WITH mysql_native_password BY 'PASSWORD';

Lastly, flush the privileges:

FLUSH PRIVILEGES;

Now you should able to connect using SequelPro again (using the specified password).

Ash
  • 6,483
  • 7
  • 29
  • 37
  • I've tried it, but after the first command (alter for root), I've got error 1396: ERROR 1396 (HY000): Operation ALTER USER failed for 'root'@'%', and then after flushing priviliges I recive:ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'PRIVILIGES' at line 1. –  Jun 17 '18 at 23:32
  • 1
    Try the alter user statement again but with `root@localhost` instead of just `root` (I edited my answer to reflect this change and to fix a typo). – Ash Jun 18 '18 at 00:03
  • Great, now it worked, even with the fact that i didn't have to flush priviliges, thanks :) –  Jun 18 '18 at 00:10
  • Glad it worked, in the end I couldn't use SequelPro as it would crash every time a database was selected/refreshed. Hopefully this isn't the case for you as well :) – Ash Jun 18 '18 at 01:16
  • Hah, unfortunately it is, so I downloaded other mysql tool for this and it works. –  Jun 18 '18 at 08:00
  • Great fix, although the fix that now allows my SequelPro to connect, also now causes SequelPro to crash as already mentioned... I love tech XD – j5Dev Jun 20 '18 at 22:09
  • Indeed, it seems the fix is a bit of a double-edged sword. If SequelPro crashes and you don't want to swap to another tool, an older version of MySQL(5.7) or MariaDB can be used instead, granted, it isn't an ideal 'fix'. – Ash Jun 23 '18 at 05:37
  • `mysql -u root` does not work -- it gives the same error message – Tilo Dec 06 '18 at 18:36