search

microsoft / mysqlnd_azure

mysqlnd_azure is an extension for mysqlnd which enables redirection functionality.
Other
10 stars 17 forks source link

MySQL redirect all of a sudden not working #32

Closed sylus closed 2 years ago

sylus commented 2 years ago

Overview

Hi there I hope this is an ok place to document this issue and I have raised an internal ticket with Microsoft.

All of a sudden a bunch of my Drupal installations using the MySQL redirect functionality aren't working this includes:

The AKS installations specifically have been working for the past year with the mysqlnd_azure redirect functionality enabled and then most recently as of last week I launched an Azure App Service build as well as early as a few days ago that was working. The CI builds for all 3 have shown that we are using mysqlnd_azure v1.1.1 and compiling it ourselves on PHP 8.0.13 and that nothing has changed in this regard php version wise for at least a few months worth of builds and production deployments so I am fairly confident that nothing has changed on our end since everything has been working great.

The error I am getting in the Dev and Prod AKS Drupal installations are:


In Connection.php line 185:

  SQLSTATE[HY000] [2002]  

In Connection.php line 185:

  PDO::__construct(): SSL operation failed with code 1. OpenSSL Error messages:         
  error:1416F086:SSL routines:tls_process_server_certificate:certificate verify failed

In addition my Azure App Service Drupal installation a completely different service also has this problem and won't start due to:

2022-05-14T11:06:49.453381124Z #9 {main}PHP message: PDOException: SQLSTATE[HY000] [2002]  in 
/var/www/html/core/lib/Drupal/Component/DependencyInjection/Container.php on line 258 #0
/var/www/html/core/lib/Drupal/Core/Database/Driver/mysql/Connection.php(185): PDO->__construct('mysql:host=ecdc...'

Reproduction

I ended up writing a test php script on AKS that sets the redirect functionality on and off and the query works with it off but fails with as soon as I set mysqlnd_azure.enableRedirect to On.

<?php

ini_set('display_errors', 'On');
ini_set('mysqlnd_azure.enableRedirect', 'On');

$options = array(
    PDO::MYSQL_ATTR_SSL_CA => '/etc/ssl/mysql/BaltimoreCyberTrustRoot.crt.pem'
);
try {
  $db = new PDO('mysql:host=XXXXX.mysql.database.azure.com;port=3306;dbname=XXXXX', 'XXXXX@XXXXX, getenv('EXTERNAL_PASSWORD'), $options);
  $res = $db->query('SELECT * FROM users');
  var_dump($res);
} catch (Exception $e) {
  var_dump($e);
}

Executing the script withmysqlnd_azure.enableRedirect to On.

bash-5.1$ php test.php 
object(PDOException)#3 (8) {
  ["message":protected]=>
  string(23) "SQLSTATE[HY000] [2002] "
  ["string":"Exception":private]=>
  string(0) ""
  ["code":protected]=>
  int(2002)
  ["file":protected]=>
  string(13) "/tmp/test.php"
  ["line":protected]=>
  int(10)
  ["trace":"Exception":private]=>
  array(1) {
    [0]=>
    array(6) {
      ["file"]=>
      string(13) "/tmp/test.php"
      ["line"]=>
      int(10)
      ["function"]=>
      string(11) "__construct"
      ["class"]=>
      string(3) "PDO"
      ["type"]=>
      string(2) "->"
      ["args"]=>
      array(4) {
        [0]=>
        string(91) "mysql:host=XXXXX.mysql.database.azure.com;port=3306;dbname=XXXXX"
        [1]=>
        string(36) "XXXXX"
        [2]=>
        string(12) "XXXXX"
        [3]=>
        array(1) {
          [1009]=>
          string(46) "/etc/ssl/mysql/BaltimoreCyberTrustRoot.crt.pem"
        }
      }
    }
  }
  ["previous":"Exception":private]=>
  object(PDOException)#2 (9) {
    ["message":protected]=>
    string(162) "PDO::__construct(): SSL operation failed with code 1. OpenSSL Error messages:
error:1416F086:SSL routines:tls_process_server_certificate:certificate verify failed"
    ["string":"Exception":private]=>
    string(0) ""
    ["code":protected]=>
    int(0)
    ["file":protected]=>
    string(13) "/tmp/test.php"
    ["line":protected]=>
    int(10)
    ["trace":"Exception":private]=>
    array(1) {
      [0]=>
      array(6) {
        ["file"]=>
        string(13) "/tmp/test.php"
        ["line"]=>
        int(10)
        ["function"]=>
        string(11) "__construct"
        ["class"]=>
        string(3) "PDO"
        ["type"]=>
        string(2) "->"
        ["args"]=>
        array(4) {
          [0]=>
          string(91) "mysql:host=XXXXX.mysql.database.azure.com;port=3306;dbname=XXXXX"
          [1]=>
          string(36) "XXXXX"
          [2]=>
          string(12) "XXXXX"
          [3]=>
          array(1) {
            [1009]=>
            string(46) "/etc/ssl/mysql/BaltimoreCyberTrustRoot.crt.pem"
          }
        }
      }
    }
    ["previous":"Exception":private]=>
    NULL
    ["errorInfo"]=>
    NULL
    ["severity"]=>
    int(2)
  }
  ["errorInfo"]=>
  array(3) {
    [0]=>
    string(5) "HY000"
    [1]=>
    int(2002)
    [2]=>
    string(0) ""
  }
}

Executed the script with mysqlnd_azure.enableRedirect to Off.

bash-5.1$ php test.php 
object(PDOStatement)#2 (1) {
  ["queryString"]=>
  string(19) "SELECT * FROM users"
}

I have confirmed that my cert is correct and the one referenced here:


bash-5.1$ cat /etc/ssl/mysql/BaltimoreCyberTrustRoot.crt.pem
-----BEGIN CERTIFICATE-----
MIIDdzCCAl+gAwIBAgIEAgAAuTANBgkqhkiG9w0BAQUFADBaMQswCQYDVQQGEwJJ
RTESMBAGA1UEChMJQmFsdGltb3JlMRMwEQYDVQQLEwpDeWJlclRydXN0MSIwIAYD
VQQDExlCYWx0aW1vcmUgQ3liZXJUcnVzdCBSb290MB4XDTAwMDUxMjE4NDYwMFoX
DTI1MDUxMjIzNTkwMFowWjELMAkGA1UEBhMCSUUxEjAQBgNVBAoTCUJhbHRpbW9y
ZTETMBEGA1UECxMKQ3liZXJUcnVzdDEiMCAGA1UEAxMZQmFsdGltb3JlIEN5YmVy
VHJ1c3QgUm9vdDCCASIwDQYJKoZIhvcNAQEBBQADggEPADCCAQoCggEBAKMEuyKr
mD1X6CZymrV51Cni4eiVgLGw41uOKymaZN+hXe2wCQVt2yguzmKiYv60iNoS6zjr
IZ3AQSsBUnuId9Mcj8e6uYi1agnnc+gRQKfRzMpijS3ljwumUNKoUMMo6vWrJYeK
mpYcqWe4PwzV9/lSEy/CG9VwcPCPwBLKBsua4dnKM3p31vjsufFoREJIE9LAwqSu
XmD+tqYF/LTdB1kC1FkYmGP1pWPgkAx9XbIGevOF6uvUA65ehD5f/xXtabz5OTZy
dc93Uk3zyZAsuT3lySNTPx8kmCFcB5kpvcY67Oduhjprl3RjM71oGDHweI12v/ye
jl0qhqdNkNwnGjkCAwEAAaNFMEMwHQYDVR0OBBYEFOWdWTCCR1jMrPoIVDaGezq1
BE3wMBIGA1UdEwEB/wQIMAYBAf8CAQMwDgYDVR0PAQH/BAQDAgEGMA0GCSqGSIb3
DQEBBQUAA4IBAQCFDF2O5G9RaEIFoN27TyclhAO992T9Ldcw46QQF+vaKSm2eT92
9hkTI7gQCvlYpNRhcL0EYWoSihfVCr3FvDB81ukMJY2GQE/szKN+OMY3EU/t3Wgx
jkzSswF07r51XgdIGn9w/xZchMB5hbgF/X++ZRGjD8ACtPhSNzkE1akxehi/oCr0
Epn3o0WC4zxe9Z2etciefC7IpJ5OCBRLbf1wbWsaY71k5h+3zvDyny67G7fyUIhz
ksLi4xaNmjICq44Y3ekQEe5+NauQrz4wlHrQMz2nZQ/1/I6eYs9HRCwBXbsdtTLS
R9I4LtD+gdwyah617jzV/OeBHRnDJELqYzmp
-----END CERTIFICATE-----
ShawnXxy commented 2 years ago

Hi William,

Could you please try to enable open SSL in php.ini and see if this could work?

Thanks.

sylus commented 2 years ago

It turns out i didn't have the full cert chain.

Why was BaltimoreCyberTrustRoot certificate not replaced to DigiCertGlobalRootG2 during this change on February 15, 2021? We evaluated the customer readiness for this change and realized that many customers were looking for extra lead time to manage this change. To provide more lead time to customers for readiness, we decided to defer the certificate change to DigiCertGlobalRootG2 for at least a year, providing sufficient lead time to the customers and end users.

And it has been a year so this has been rolled out once I added the DigiCert one to my existing the problem went away :)

-----BEGIN CERTIFICATE-----
(Root CA1: BaltimoreCyberTrustRoot.crt.pem)
-----END CERTIFICATE-----
-----BEGIN CERTIFICATE-----
(Root CA2: DigiCertGlobalRootG2.crt.pem)
-----END CERTIFICATE-----