Connections to MySQL failing with BOSH DNS errors in Pivotal Cloud Foundry (PCF)

Products

• VMware Tanzu SQL

Issue/Introduction

Symptoms:

Applications fail connecting to their bound MySQL database with an error similar to the following:

Failed to obtain JDBC Connection; nested exception is java.sql.SQLNonTransientConnectionException: Could not connect to address=(host=q-n3s3y1.q-g19944.bosh)(port=3306)(type=master) : q-n3s3y1.q-g19944.bosh

Environment

Cause

There are BOSH DNS Health certificates stored in CredHub and on the MySQL VMs associated with the service instances. If either of these are expired, you face problems when attempting to connect to MySQL.

To determine if the BOSH DNS Heath certificates in CredHub are expired, use your Fully Qualified Domain Name (FQDN). For example, direct your browser to https://ops-man-fqdn/api/v0/deployed/certificates?expires_within=3m. This will identify any expired certificates, as well as certificates that will expire in 3 months.

To determine if the BOSH DNS Heath certificates on the MySQL VM are expired, first SSH into a MySQL VM associated with a service instance used by an application having a connection problem. Check the /var/vcap/sys/log/bosh-dns/bosh_dns_health.stderr.log for errors similar to:

2019/05/16 13:54:14 http: TLS handshake error from 192.168.0.1:40166: remote error: tls: bad certificate

Given the "bad certificate" error and the client IP with a high ephemeral port, a problem with the MySQL node's server certificate is the likely problem. To check both the MySQL node's server and client certificates, use the commands:

openssl x509 -in /var/vcap/jobs/bosh-dns/config/certs/health/client.crt -text -noout
openssl x509 -in /var/vcap/jobs/bosh-dns/config/certs/health/server.crt -text -noout

Resolution

If CredHub Bosh DNS Heath certificates are expired, the non-configurable certificates will need to be rotated. This process is described at https://docs.pivotal.io/pivotalcf/2-3/security/pcf-infrastructure/api-cert-rotation.html#regenerate.

If the MySQL VMs client or server BOSH DNS Heath certificates are expired, the MySQL tile's "upgrade-all-service-instances" errand should be run. This will cause the certificates to be regenerated for all MySQL v2 service instances. 

Once the certificates have been regenerated, the applications should start successfully.