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
  • 390
  • 1
  • 2
  • 11
  • 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 01 '18 at 02: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 01 '18 at 04: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 01 '18 at 06: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 01 '18 at 07:01
  • Sorry about the postgress, "what the heart is full off", eh? – Gerard H. Pille Aug 01 '18 at 07:06
  • Can you connect through a command line client? – Col. Shrapnel Aug 02 '18 at 06:32
  • The error message mentioned on the manual page is a different one. All Connection timed out errors are firewall-related. So you need to make sure that all other nethods are apparently work and reflect this fact in your question, – Col. Shrapnel Aug 02 '18 at 08:10

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
  • 237,123
  • 42
  • 477
  • 940
  • 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 04 '18 at 09:00
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
  • 390
  • 1
  • 2
  • 11
Gerard H. Pille
  • 2,469
  • 1
  • 12
  • 10
  • 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 01 '18 at 08: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 01 '18 at 08: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 01 '18 at 08:14
  • "IPv6 26492 0t0 TCP *:33060 (LISTEN)" is not 3306! – Gerard H. Pille Aug 01 '18 at 08: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 01 '18 at 08:16
  • Can you add "netstat -rn" output to your question. I'm looking for something (eg. a firewall) that prevents a client on the AWS to connect to the server on the same system. – Gerard H. Pille Aug 01 '18 at 08:22
  • So, everything goes to 172.31.32.1 ?? What does "ifconfig eth0" show? – Gerard H. Pille Aug 01 '18 at 08:43
  • according to your phpinfo, server address is 172.31.37.13, which your route config does not allow to reach. Your MySQL is not running in a docker or something? – Gerard H. Pille Aug 01 '18 at 08:50
  • Editing the question to clarify environment. Not Docker, but it is AWS EC2 (a virtual server). – HumanJHawkins Aug 01 '18 at 09:06
  • "route -v add -net 172.31.37.0 netmask 255.255.255.0 dev eth0" and use 172.31.37.13 as host for PDO. When you run "ifconfig", do you see the loopback connector? Can you ping localhost? – Gerard H. Pille Aug 01 '18 at 09:29
  • I wonder if this shouldn't be handled through the AWS Management Console. Perhaps you configured your DB as being publicly accessible only. – Gerard H. Pille Aug 01 '18 at 09:40
  • Tried rout -v add and the private IP with no luck. I'm confident this is not an AWS config issue, as AWS is external to the server and the same systems that would potentially cause problems, do not interfere with other (Datagrip, etc) connections. Regarding configuring the server for public only, I dropped all testuser logins and remade them for 'localhost', 127.0.0.1, 172.31.37.0, 34.208.22.26, and codehawkins.com. Then tested both local (localhost) and remote via FQDN. – HumanJHawkins Aug 01 '18 at 22:12
  • At this point, I'll be happy to redeploy a server to continue testing if significant new info is found... I want to understand this. But toward having a working system I am going to shelf this and roll to earlier known-good technology. – HumanJHawkins Aug 01 '18 at 22:14
  • I'll try and configure an aws comparable to yours, and get back to you with the results. That'll be during the day, it's 1 am here. Still wondering if you have loopback connector. – Gerard H. Pille Aug 01 '18 at 23:02
  • Sorry... I can ping localhost and do have loopback. Adding ifconfig info to the question. Also putting a link to the exact setup steps used for this server in latest addendum. – HumanJHawkins Aug 02 '18 at 03:08
  • I thought to open a "free tier account" on AWS, but they want my credit card data. I'm not willing to hand that to mr. Beezos, he's rich enough as it is. – Gerard H. Pille Aug 02 '18 at 07:40
  • Do you know the "RDS management console"? "You do need to set the VPC security group to ensure that you can access the database", says https://www.dummies.com/programming/cloud-computing/amazon-web-services/access-aws-rds-management-console/ – Gerard H. Pille Aug 02 '18 at 18:11
  • Regarding RDS, this build is not using it. RDS is an alternate (seperate server) way of hosting a DB. This system is just using an EC2 instance, which is simply a virtual server. There really isn't anything at Amazon to get in the way of it, except for their firewall. And other successful connections from outside the firewall corroborate the idea that it is not the problem. – HumanJHawkins Aug 02 '18 at 20:14
  • I am happy to give you root access if you are interested in poking around. If/when a solution is found, I plan to wipe it out and rebuild to confirm and document the process of getting this working. There is nothing confidential on the server, etc. I don't see a way to send private messages on StackExchange. I have a nearly obsolete email you could contact me on if you like... jhawkins@locutius.com. I don't want to post my regular email publicly, due to the spam that invites. – HumanJHawkins Aug 02 '18 at 20:31
  • That is great, I'm really curious what is going on. Send me a pm, use one of my addresses, ghpille at hotmail or gmail dotcom. But I'll have some sleep first. – Gerard H. Pille Aug 02 '18 at 22:39
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
  • 2