239

I'm running MySQL version 8 on PHP 7.0.

I'm getting the following error when I try to connect to my database from PHP:

Connect Error: SQLSTATE[HY000] [2054] The server requested authentication method unknown to the client

PHP might show this error

Warning: mysqli_connect(): The server requested authentication method unknown to the client [caching_sha2_password] in D:\xampp\htdocs\reg\server.php on line 10

How can I fix this problem?

Improve this question
2
  • I did some Google work. Maybe this will work? github.com/laradock/laradock/issues/1392 (not the same problem, but it's the same error message) Commented Sep 17, 2018 at 9:18
  • For folks who stumble across this and are having no success: I also got this error message when I had the wrong IP for the database in my .env file. Commented Jun 16, 2022 at 13:58

8 Answers 8

Reset to default
530

@mohammed, this is usually attributed to the authentication plugin that your mysql database is using.

By default and for some reason, mysql 8 default plugin is auth_socket. Applications will most times expect to log in to your database using a password.

If you have not yet already changed your mysql default authentication plugin, you can do so by:
1. Log in as root to mysql
2. Run this sql command: 

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

Replace 'password' with your root password. In case your application does not log in to your database with the root user, replace the 'root' user in the above command with the user that your application uses.

Digital ocean expounds some more on this here Installing Mysql

Improve this answer
Sign up to request clarification or add additional context in comments.

10 Comments

The main reason for this problem is that Mysql's default authentication plug-in support has changed in the 8.0 release of MariaDB. For example, the list of Mysql authentication plug-ins supported by 'PHP7.3' can be seen through the 'phpinfo()' function:` mysqlnd debug_trace, auth_plugin_mysql_native_password auth_plugin_mysql_clear_password, auth_plugin_sha256_password `. So change the authentication plugin mechanism in the Mysql server restricted account to 'sha256_password' or 'mysql_native_password': ALTER USER 'root'@'%' IDENTIFIED WITH sha256_password BY 'password';
I get ERROR 1396 (HY000): Operation ALTER USER failed for 'root'@'localhost' for this. My password is '' (empty)
It doesn't work for 8.0.20. The command ALTER USER succeeded but the warning still appears.
Thanks for this. I added [mysqld] default-authentication-plugin=mysql_native_password to mysql (8) config file but this did the trick.
This worked for me BUT with one added caveat on macOS Big Sur, MySQL 8.0.15: ALTER USER CURRENT_USER() IDENTIFIED WITH mysql_native_password BY 'password';
|
172

You have to change MySQL settings. Edit my.cnf file and put this setting in mysqld section:

[mysqld]
default_authentication_plugin= mysql_native_password

Then run following command:

FLUSH PRIVILEGES;

Above command will bring into effect the changes of default authentication mechanism.

Improve this answer

12 Comments

and then recreate the users. ALTER USER $user IDENTIFIED WITH mysql_native_password BY 'passwd' (ref ALTER USER manual page
$user is not a valid variable. not usefull
Found my.cnf on Ububtu at /etc/mysql/my.cnf
@AkshayHazari Please follow this thread: stackoverflow.com/questions/10757169/…
Where is my.cnf file on Windows 7?
|
125

I've tried a lot of other solutions, but only this works for me.

Thanks for the workaround.

Check your .env

MYSQL_VERSION=latest

Then type this command

$ docker-compose exec mysql bash
$ mysql -u root -p

(login as root)

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'root';
ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY 'root';
ALTER USER 'default'@'%' IDENTIFIED WITH mysql_native_password BY 'secret';

Then go to phpmyadmin and login as :

  • host -> mysql
  • user -> root
  • password -> root
Improve this answer

11 Comments

this solution worked for me as a charm. U saved my day also! what I did additionally was to fork mysql docker and encase the solution in the build of mysql container: github.com/cybalex/mysql/commit/…
Thank you. it helps a lot. I just need that last alter query to run this on docker mysql
[HY000][1396] Operation ALTER USER failed for 'default'@'%' happens on the last alter... any ideas?
@TaioliFrancesco thanks for your answer it works, but do we have to run theses queries each time w start mysql container ? is there a way to do it via docker compose ?
@SlimenTN no, only once and then the container rember the mysql setting
|
15

None of the answers here worked for me. What I had to do is:

  1. Re-run the installer.
  2. Select the quick action 're-configure' next to product 'MySQL Server'
  3. Go through the options till you reach Authentication Method and select 'Use Legacy Authentication Method'

After that it works fine.

Improve this answer

3 Comments

If you used the installer and you're using the classic username/password method for connecting to your database, this method is the way to go.
I'm not finding Authentication Method in the installer :(
@CharlesWood You need to use the MYSQL Installer dev.mysql.com/downloads/installer
11

Faced the same problem, I was not able to run wordpress docker container with mysql version 8 as its default authentication mechanism is caching_sha2_password instead of mysql_native_password.

In order to fix this problem we must reset default authentication mechanism to mysql_native_password.

Find my.cnf file in your mysql installation, usually on a linux machine it is at the following location - /etc/mysql

Edit my.cnf file and add following line just under heading [mysqld]

default_authentication_plugin= mysql_native_password

Save the file then log into mysql command line using root user

run command FLUSH PRIVILEGES;

Improve this answer

1 Comment

This solution solves my problem, but I don't have to flush the privileges.
2

I'm using Laravel Lumen to build a small application.
For me it was because I didn't had the DB_USERNAME defined in my .env file.

DB_USERNAME=root

Setting this solved my problem.

Improve this answer

Comments

0

In my.cnf file check below 2 steps.

  • check this value -

    old_passwords=0;

    it should be 0.

  • check this also-

    [mysqld] default_authentication_plugin= mysql_native_password Another value to check is to make sure

    [mysqld] section should be like this.

Improve this answer

2 Comments

Where would you find the "my.cnf" file?
laradock/mysql/my.cnf
0 
preferences -> mysql -> initialize database -> use legacy password encryption(instead of strong) -> entered same password

as my config.inc.php file, restarted the apache server and it worked. I was still suspicious about it so I stopped the apache and mysql server and started them again and now it's working.

Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.