php mysqli_connect: authentication method unknown to the client [caching_sha2_password]

Question

I am using php mysqli_connect for login to a MySQL database (all on localhost)

<?php
//DEFINE ('DB_USER', 'user2');
//DEFINE ('DB_PASSWORD', 'pass2');
DEFINE ('DB_USER', 'user1');
DEFINE ('DB_PASSWORD', 'pass1');
DEFINE ('DB_HOST', '127.0.0.1');
DEFINE ('DB_NAME', 'dbname');

$dbc = mysqli_connect(DB_HOST, DB_USER, DB_PASSWORD, DB_NAME);

if(!$dbc){
    die('error connecting to database');    
}
?>

this is the mysql.user table:

MySQL Server ini File:

[mysqld]
# The default authentication plugin to be used when connecting to the server
default_authentication_plugin=caching_sha2_password
#default_authentication_plugin=mysql_native_password

with caching_sha2_password in the MySQL Server ini file, it's not possible at all to login with user1 or user2;

error: mysqli_connect(): The server requested authentication method unknown to the client [caching_sha2_password] in...

with mysql_native_password in the MySQL Server ini file, it's possible to login with user1, but with user2, same error;

---

how can I login using caching_sha2_password on the mySql Server?

Solution 1

As of PHP 7.4, this is no longer an issue. Support for caching_sha2 authentication method has been added to mysqlnd.

---

Currently, PHP mysqli extension do not support new caching_sha2 authentication feature. You have to wait until they release an update.

Check related post from MySQL developers: https://mysqlserverteam.com/upgrading-to-mysql-8-0-default-authentication-plugin-considerations/

They didn't mention PDO, maybe you should try to connect with PDO.

Solution 2

I solve this by SQL command:

ALTER USER 'mysqlUsername'@'localhost' IDENTIFIED WITH mysql_native_password BY 'mysqlUsernamePassword';

which is referenced by https://dev.mysql.com/doc/refman/8.0/en/alter-user.html

if you are creating new user

 CREATE USER 'jeffrey'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';

which is referenced by https://dev.mysql.com/doc/refman/8.0/en/create-user.html

this works for me

Solution 3

ALTER USER 'mysqlUsername'@'localhost' IDENTIFIED WITH mysql_native_password BY 'mysqlUsernamePassword';

Remove quotes (') after ALTER USER and keep quote (') after mysql_native_password BY

It is working for me also.

Solution 4

If you're on Windows and it's not possible to use caching_sha2_password at all, you can do the following:

1. rerun the MySQL Installer
2. select "Reconfigure" next to MySQL Server (the top item)
3. click "Next" until you get to "Authentication Method"
4. change "Use Strong Password Encryption for Authentication (RECOMMENDED)" to "Use Legacy Authentication Method (Retain MySQL 5.X Compatibility)
5. click "Next"
6. enter your Root Account Password in Accounts and Roles, and click "Check"
7. click "Next"
8. keep clicking "Next" until you get to "Apply Configuration"
9. click "Execute"

The Installer will make all the configuration changes needed for you.

Solution 5

Like many many people, I have had the same problem. Although the user is set to use mysql_native_password, and I can connect from the command line, the only way I could get mysqli() to connect is to add

default-authentication-plugin=mysql_native_password

to the [mysqld] section of, in my setup on ubuntu 19.10, /etc/mysql/mysql.conf.d/mysqld.cnf

View more solutions

Comments

[tadman]

Does PDO support this? I've seen other reports about mysqli.

[FIL]

Answer posted by @黃皓哲 should be marked as accepted answer.

[user3512810]

From terminal login to mysql with this command : mysql -u root -p then enter root password, then paste the following command ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'root';

[Machavity]

Good answer. The gotcha here is you might have to do this every time you add a new user, depending on the server settings

[Accountant م]

Thank you very much I was looking for a way to convert the new password hashes to the older ones (like user2 format to user1 format in the question) and your answer did exactly the job

[MountainMan]

And so ended a 4 day struggle to get the php-7.2.9/11 playing nice with MySQL-8.012. Could someone on the PHP team be convinced to note this in the INSTALL in the tarball? It would save thousands of person hours over the next year or two.

[Liang]

@MountainMan Saved my life

[Yohanim]

this should be accepted answer for workaround solution

[Casey Plummer]

10 months later. Lost 2 hours on this issue. Thanks for the solution. Appreciated.

[Dimmduh]

ERROR 1819 (HY000): Your password does not satisfy the current policy requirements

[Machavity]

PDO has the same problem

[cgclip]

This worked for me with php7.0 and mysql 8.0 on CentOS just make sure you also restart mysqld

[LUISAO]

Perfect! It work for me too! Just conected to database SYS and execute the Alter command above. Thanks!

[Tigran]

Alter didn't work for me, so, I dropped and created the user instead. Thank you.

[Simon Brown]

Switching to native authentication basically means you weakened the overall security to allow access. That gives you access but can't be considered a long-term, secure solution. And as @Dimmduh pointed out, you may end up with "ERROR 1819 (HY000): Your password does not satisfy the current policy requirements"

[Oleg Popov]

Also, keep in mind if the user is created prior this change it will not work. So, you should FIRST set default-authentication in the config and than create the user in order to work!

[Jodyshop]

That was the only solution worked for me on localhost server using WAMP server :)

[Magnus]

This seemed to fix it on my Mac's homebrew LAMP setup. Didn't experience this problem on debian though.

[Rodrigo]

@cgclip YES: just make sure you also restart mysqld !!

[Gregor Simončič]

This worked for me, as I choosed new authentication type, but my application does not support it. So I had to "downgrade" it and this solved it.

[gen]

@Machavity do you know if this answer is still valid? Ie. it's not outdated yet with newer PHP versions?

[Machavity]

@gen Based on this bug, looks like it's working as of 7.4.2

[Machavity]

Not quite. It's buggy in 7.4.0 and 7.4.1. Fixed in 7.4.2

[danisupr4]

it works, thank you... we must config that 2 things: mysql conf and alter user

[Thanasis]

That should be the accepted answer but unfortunately nothing in life is fair ! Thank you

[ultrasamad]

Upgrading to php7.4 does solved the issue. And I think that is the best approach rather than changing the authentication method to mysql_native_password as suggested by some answers.

[d1jhoni1b]

no spaces here 'root' @ 'localhost' should be read instead ALTER USER 'root'@'localhost' identified with mysql_native_password BY 'root123';

[nykc]

Needed this... Thanks!

[Dinesh]

Thank you, it work for me. I use php 7.3 and mysql 8.0 on macOS catalina

[Code Tree]

man after a year of upvoting this answer my db was hacked...

[Joan Galmés Riera]

I use docker (apache+php in a container and mysql in other container) and this solved my Issue. Thanks!

[fromtheloam]

I'm a little late, but for anyone who is trying to access a remote mysql server over a local IP, the ALTER USER line should also be run for your local internet IP instead of 'localhost'

[Jason]

For those purely beginner, 1) cd /usr/local/mysql/bin 2) ./mysql -u root -p 3) enter the line of code in this post

[Jakub Ujvvary]

Thx. This worked for me. But i want understand why this works (can sb expand this answer) - for me this problem was related with phpmyadmin on fresh mysql server 8.

[lycanthrope10100]

After this restarting mysqld with --default-authentication-plugin=mysql_native_password made it work for me.

[rubo77]

This worked on Ubuntu 20.04 with MySQL 8.0 and PHP 5.6. Note that you must set the tables to INNODB for this too work

[incredimike]

I doubt this was the root cause of your database hack. Thanks for the post OP, it helped me configure MySQL for a legacy application running PHP 5.6.

[RaminS]

PHP 7.4.3 here. Had to change to mysql_native_password for it to work.

[dontdownvoteme]

this answer made me create a password and now I cant even connect, wrong answer.

[Igor]

after adding this to config I get the same error

[tash]

I have PHP 7.4.27 (cli) (built: Jan 20 2022), still I needed mysql_native_password!

[Ken Ingram]

perfect. I'm testing. The other answers require rehauling my entire system, which is currently working just to access one item. I'm think I need a rotating "HOT" (Always updated) server going forward.