mysqli_real_connect() getting SSL3_GET_SERVER_CERTIFICATE:certificate verify failed

Question

We just upgraded to php 5.6 from php 5.4, and everything was working fine with our MySQL connecting using MySQLi and SSL.

Our connection looks like:

mysqli_real_connect($db, $host, $username, $password, $database, $port, $socket, MYSQLI_CLIENT_SSL);
mysqli_set_charset($db, "utf8");

Howerver, now when we try and connect to MySQL over SSL using php 5.6 we are getting:

Warning: mysqli_real_connect(): SSL operation failed with code 1. OpenSSL Error messages: error:14090086:SSL routines:SSL3_GET_SERVER_CERTIFICATE:certificate verify failed in /MySQLConnection.php on line 29

Warning: mysqli_real_connect(): Cannot connect to MySQL by using SSL in /MySQLConnection.php on line 29

Warning: mysqli_real_connect(): [2002] (trying to connect via tcp://mysql1.ourdomain.com:3306) in /MySQLConnection.php on line 29

Warning: mysqli_real_connect(): (HY000/2002): in /MySQLConnection.php on line 29

I tried setting:

mysqli_options($db, MYSQLI_OPT_SSL_VERIFY_SERVER_CERT, false);

But that does not help.

UPDATE

I added:

$mysql_certs_path = "/full/path/to/certs/mysql";
mysqli_ssl_set($db, $mysql_certs_path . "/client-key.pem", $mysql_certs_path . "/client-cert.pem", $mysql_certs_path . "/ca-cert.pem", null, null);

And still getting:

Warning: mysqli_real_connect(): SSL operation failed with code 1. OpenSSL Error messages: error:14090086:SSL routines:SSL3_GET_SERVER_CERTIFICATE:certificate verify failed in /MySQLConnection.php on line 31

Warning: mysqli_real_connect(): Cannot connect to MySQL by using SSL in /MySQLConnection.php on line 31

Aramen

Fix the server certificate? – Strontianite

Please post the server's certificate. Use openssl x509 -in <file> -inform <PEM|DER> -text -noout. Also, you need to supply the exact way you are connecting (hostname, IP address, etc). – Goodall

Please post the exact URL you are using to connect to the server, and post the output of openssl s_client -connect <hostname>:<port> -tls1 -servername <hostname> | openssl x509 -text -noout. Do so by adding it to your question by clicking Edit (and don't post it as a comment). Otherwise, there's not enough information to help troubleshoot it. – Goodall

Answer 1

@jww, I have written a post that shares exactly the problems @Justin is facing here. According to my post, the CN of the certificate issued by Google Cloud SQL has the pattern:

CN=project-name:instance-id

Kansu

Answer 2

Had something similar to this happen to me. When I upgraded PHP to 5.6, it worked. But when I entered:

sudo apt-get install php5-mysqlnd

On Ubuntu 15.04, the SSL to AWS RDS stopped working, even though it worked with the libmysql driver. Running:

sudo apt-get install php5-mysql

Installed the old drivers and it started working again. Near as I can figure out, it's failing the peer-name validation for connecting to RDS. I do not know how to fix that, and because it uses PHP streams for its connection, the settings don't seem to matter that you pass in.

This is a known bug:

https://bugs.php.net/bug.php?id=68344

So I wrote this method, modified from here: How to know if MySQLnd is the active driver?

public function getMySQLIType()
{
    $mysqlType = [
        'mysql' => false,
        'mysqli' => false,
        'mysqlnd' => false,
    ];

    if (function_exists('mysql_connect')) {
        $mysqlType['mysql'] = true;
    }

    if (function_exists('mysqli_connect')) {
        $mysqlType['mysqli'] = true;
    }

    if (function_exists('mysqli_get_client_stats')) {
        $mysqlType['mysqlnd'] = true;
    }

    return $mysqlType;
}

If the array returns true for mysqlnd, I disable SSL. If returns false, then I enable it. Thus far, it works. Yes, this is a hack fix but I do not know how to legitimately fix this issue.

Pumpernickel