5

I'm testing a new LEMP webserver built with Ubuntu Server 18.04 LTS, NGINX 1.14, PHP 7.2.8, and MySQL 8.0.12. It is an AWS EC2 instance using the official Canonical AMI. Web and PHP work as intended aside from database connection. But I cannot connect to a MySQL database using PDO settings and functions that have worked well in other (MySQL 5.7 via PHP 7.1) cases.

I can connect reliably on server (MySQL via SSH) in the terminal using the same credentials as the PDO attempts to use. Also reliably from remote using MySQL Workbench and the same credentials. There is no on-server firewall. Firewall is via Amazon's Security Groups only. With port 3306 wide open, PHP still won't connect using a PDO. Haven't tried using MySQLi, but one purpose of this is to determine and document how to connect via PDO in this environment. So that wouldn't be a solution.

I understand the password security changed with MySQL 8 (and I chose to use the new stronger default password during install). But I also got the impression from http://php.net/manual/en/ref.pdo-mysql.php that as of PHP 7.2.4, this should work... I don't really understand what they are trying to say in the relevant paragraphs there though...

Is this not expected to work yet?

EDIT: Removed config and troubleshooting info that turned out to be irrelevant. Hoping this cleaner/shorter edit of the question is more useful to those that find it later.

HumanJHawkins
  • 560

3 Answers3

4

The text on the PHP documentation page you linked to is:

MySQL 8

When running a PHP version before 7.1.16, or PHP 7.2 before 7.2.4, set MySQL 8 Server's default password plugin to mysql_native_password or else you will see errors similar to The server requested authentication method unknown to the client [caching_sha2_password] even when caching_sha2_password is not used.

This is because MySQL 8 defaults to caching_sha2_password, a plugin that is not recognized by the older PHP (mysqlnd) releases. Instead, change it by setting default_authentication_plugin=mysql_native_password in my.cnf. The caching_sha2_password plugin will be supported in a future PHP release. In the meantime, the mysql_xdevapi extension does support it.

This doesn't mean that PHP currently supports caching_sha2_password. It does not. It means that PHP no longer throws fatal errors when connecting to a MySQL 8.0 server that advertises support for caching_sha2_password.

You can install the named PECL extension if you wish, but it has a different API to mysqlnd, so it is not a drop-in replacement, and will not work with existing PHP code that uses mysqlnd's API.

For the moment, until a new version of PHP mysqlnd is released that actually supports authenticating with caching_sha2_password, your only option is to not use it, going back to mysql_native_password as documented.

Community
  • 1
Michael Hampton
  • 252,907
3

Further investigation allowed us to learn that PHP doesn't support caching_sha2_authentication as yet, mysql had to revert to native.

NOTE: One cannot simply create accounts WITH mysql_native_password, but must set the default to mysql_native_password.

EDIT: Cut info that turned out to be unrelated to the issue.

HumanJHawkins
  • 560
Gerard H. Pille
  • 2,689
0

You can change MYsql8's default authentication method in MySQL Workbench by:

  1. Opening MySQL workbench as an administrator
  2. First going to Options File screen highlighted with a 1 next to it in the linked screenshot.
  3. Scrolling to then changing the authentication method with the drop-down highlighted with a 2 next to it and hitting apply in the lower right.

MySQL8 workbench screenshot

mikeLundquist
  • 101