PHP 7.2.8 PDO Fails to Connect to MySQL 8.0.12 (GA) in AWS EC2 LEMP Stack

HumanJHawkins asked:

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 understand the password security changed with MySQL 8 (and I chose to use the new stronger security 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?

The PHP installs were executed with:

apt install -y php7.2 php7.2-fpm php7.2-mbstring php7.2-xml php7.2-curl php7.2-mysql

The website loads the following configs:

// DB Connection info.
$GLOBALS['DB_SERVER']   = $GLOBALS['SITE_DOMAIN'];  // 'codehawkins.com' in this case.
$GLOBALS['DB_USERNAME'] = <Proven Correct Username>;
$GLOBALS['DB_PASSWORD'] = <Proven Correct Password>;
$GLOBALS['DB_DATABASE'] = 'communityPress';
$GLOBALS['DB_PORT'] = '3306';
$GLOBALS['DB_CHARACTER_SET'] = 'utf8';
$GLOBALS['DB_OPTIONS'] =
    [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC, PDO::ATTR_EMULATE_PREPARES => false,];
$GLOBALS['DB_DSN'] =
    "mysql:host=" . $GLOBALS['DB_SERVER'] . ";dbname=" . $GLOBALS['DB_DATABASE'] . ";" . "port=" . $GLOBALS['DB_PORT'] .
    ";charset=" . $GLOBALS['DB_CHARACTER_SET'];

The function failing to create the connection is:

function getDBPDO() {
  try {
    $pdo = new PDO($GLOBALS['DB_DSN'], $GLOBALS['DB_USERNAME'], $GLOBALS['DB_PASSWORD'], $GLOBALS['DB_OPTIONS']);
  } catch (PDOException $e) {
    // Catch default and throw new exception to prevent password exposure.
    throw new Exception('"Could not connect to database."');
  }
  return $pdo;
}

Debug output of $GLOBALS[‘DB_DSN’] shows:
$GLOBALS[‘DB_DSN’] is: mysql:host=codehawkins.com;dbname=communityPress;port=3306;charset=utf8

If I temporarily remove the try/catch block, I get a little more info about the failure. (password starred out, but was intact for test):

Fatal error: Uncaught PDOException: SQLSTATE[HY000] [2002] Connection timed out in /var/www/codehawkins.com/html/functions.php:301 Stack trace: #0 /var/www/codehawkins.com/html/functions.php(301): PDO->__construct('mysql:host=code...', 'testuser', '********', Array) #1 /var/www/codehawkins.com/html/sessionStart.php(21): getDBPDO() #2 /var/www/codehawkins.com/html/index.php(2): include('/var/www/codeha...') #3 {main} thrown in /var/www/codehawkins.com/html/functions.php on line 301

The error log has:

2018/07/31 23:38:23 [error] 3224#3224: *5 FastCGI sent in stderr: "PHP message: PHP Fatal error:  Uncaught Exception: "Could not connect to database." in /var/www/codehawkins.com/html/functions.php:303
Stack trace:
#0 /var/www/codehawkins.com/html/sessionStart.php(21): getDBPDO()
#1 /var/www/codehawkins.com/html/index.php(2): include('/var/www/codeha...')
#2 {main}
  thrown in /var/www/codehawkins.com/html/functions.php on line 303" while reading response header from upstream, client: 70.59.153.212, server: codehawkins.com, request: "GET /index.php HTTP/2.0", upstream: "fastcgi://unix:/var/run/php/php7.2-fpm.sock:", host: "codehawkins.com"

The access log has:

70.59.153.212 - - [31/Jul/2018:23:41:26 +0000] "GET /index.php HTTP/2.0" 200 396 "-" "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/67.0.3396.99 Safari/537.36" "-"

The site is currently up at https://codehawkins.com, with a hangman game and about page that show working non-DB related code. I’ve temporarily put phpInfo() output at https://codehawkins.com/phpinfo.php, showing that PHP is working and giving further info on PHP config (all of which is default). And, the failed DB access can be seen at https://codehawkins.com/index.php.

Any info, or suggestions on how I could get better info towards resolving this will be much appreciated.

Addendum 1: netstat -rn output is

Kernel IP routing table
Destination     Gateway         Genmask         Flags   MSS Window  irtt Iface
0.0.0.0         172.31.32.1     0.0.0.0         UG        0 0          0 eth0
172.31.32.0     0.0.0.0         255.255.240.0   U         0 0          0 eth0
172.31.32.1     0.0.0.0         255.255.255.255 UH        0 0          0 eth0

Addendum 2: This is an Amazon EC2 instance with public “elastic” IP of 34.208.22.26 and private IP of 172.31.37.13. In past servers the elastic IP always just worked. The FQDN for this (codehawkins.com) resolves to 34.208.22.26.

Addendum 3: Exact steps used to setup this server are at: https://docs.google.com/document/d/e/2PACX-1vT87q2OQMer_OY8OUJpdiHQKwmm4NagKrJF7UUZxa75cifUq2qYY-U0-zC3nUsLXd7ByX7V85XNeKeZ/pub

ifconfig output is:

eth0: flags=4163<UP,BROADCAST,RUNNING,MULTICAST>  mtu 9001
        inet 172.31.37.13  netmask 255.255.240.0  broadcast 172.31.47.255
        inet6 fe80::416:6ff:fe1f:5616  prefixlen 64  scopeid 0x20<link>
        ether 06:16:06:1f:56:16  txqueuelen 1000  (Ethernet)
        RX packets 1061  bytes 435045 (435.0 KB)
        RX errors 0  dropped 0  overruns 0  frame 0
        TX packets 1081  bytes 202137 (202.1 KB)
        TX errors 0  dropped 0 overruns 0  carrier 0  collisions 0

lo: flags=73<UP,LOOPBACK,RUNNING>  mtu 65536
        inet 127.0.0.1  netmask 255.0.0.0
        inet6 ::1  prefixlen 128  scopeid 0x10<host>
        loop  txqueuelen 1000  (Local Loopback)
        RX packets 140  bytes 11056 (11.0 KB)
        RX errors 0  dropped 0  overruns 0  frame 0
        TX packets 140  bytes 11056 (11.0 KB)
        TX errors 0  dropped 0 overruns 0  carrier 0  collisions 0

Addendum 4: 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. Initially, port 3306 was not fully open (was restricted by IP) however, opening that port wide has not helped. 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.

My answer:


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.


View the full question and any other answers on Server Fault.

Creative Commons License
This work is licensed under a Creative Commons Attribution-ShareAlike 3.0 Unported License.