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.

  • How does codehawkins.com resolve on that system? Is Postgress listening on that IP address? Is there a firewall preventing db connection? – Gerard H. Pille Aug 1 at 2:49
  • Sorry. Forgot to mention, no firewall directly on the server. It is at AWS using AWS security groups. port 3306 is open, and I can connect from my desktop using the same account with either DataGrip or MySQL Workbench. Connection works from remote by either domain or IP. nslookup shows: Server: 127.0.0.53 Address: 127.0.0.53#53 Non-authoritative answer: Name: codehawkins.com Address: 34.208.22.26 That last address is correct. I tried putting the IP address directly into the website config just to be sure, but no luck there either. :-( – HumanJHawkins Aug 1 at 4:17
  • So, on that AWS system, you can run a "telnet codehawkins.com 3306" that doesn't hang? You didn't answer my "is postgress listening ...". "lsof -Pn | grep LISTEN | grep 3306" gives what? The timeout is 30 seconds, certainly reminds me of a network problem. – Gerard H. Pille Aug 1 at 6:47
  • "lsof -Pn | grep LISTEN | grep 3306" gives many rows similar to this (though none with IPv4 in the line): mysqld 879 908 mysql 20u IPv6 19421 0t0 TCP *:3306 (LISTEN) mysqld 879 908 mysql 23u IPv6 19652 0t0 TCP *:33060 (LISTEN) "telnet codehawkins.com 3306" does hang, but it resolves the address. output is: Trying 34.208.22.26... Thanks. – HumanJHawkins Aug 1 at 7:01
  • Sorry about the postgress, "what the heart is full off", eh? – Gerard H. Pille Aug 1 at 7:06
up vote 1 down vote accepted

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.

  • This looked like the answer... I've set bind-address = 0.0.0.0 (any IPv4) just to test. I'm guessing it solved part of the problem, but it still refuses to connect. If I do "lsof -Pn | grep LISTEN | grep mysqld | grep", I now get many lines with IPv4 such as: "mysqld 1796 1807 mysql 20u IPv4 29908 0t0 TCP *:3306 (LISTEN)" – HumanJHawkins Aug 1 at 8:00
  • Oddly, "bind-address = 0.0.0.0" is supposed to mean IPv4 and only IPv4. However, I still see many rows similar to "IPv6 26492 0t0 TCP *:33060 (LISTEN)" Also, this was a complete default install... Very odd that it would not be listening on IPv4 3306 from the start. I used mysql --help to identify all the .cnf files, and none of them had a bind-address line until I added it. – HumanJHawkins Aug 1 at 8:04
  • So, I just checked again... I could before and can still connect with no problem using the IPv4 address (or FQDN) from a remote workstation using the domain name or IP address. Wondering how that is possible if it was not listening on IPv4 3306 (though lsof said not?). This is a single server running the full LEMP stack... Would it be something to do with having PHP and MySQL on the same machine? (Many thanks for your thoughts!) – HumanJHawkins Aug 1 at 8:14
  • "IPv6 26492 0t0 TCP *:33060 (LISTEN)" is not 3306! – Gerard H. Pille Aug 1 at 8:15
  • Right... But the IPv4 lines are all 3306. Was wondering about that, but did not pursue since I am trying to connect with IPv4. – HumanJHawkins Aug 1 at 8:16

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.

  • Appreciate and am upvoting this. Accepting Gerard's also correct answer as it came in and solved the issue first, though an edit review and a time zone issue delayed that showing up until now. Thanks much! – HumanJHawkins Aug 4 at 9:00

Your Answer

 

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.