MySQL server has gone away error when updated Amazon RDS certificates with the 2019 certificate bundle

Products

• Operations Manager

Issue/Introduction

Pre-checks

• One or more of the following options are configured for using Amazon RDS (tested with MariaDB) instance as external database for Bosh or PAS
  • Operations Manager UI -> Bosh Director tile -> Director Config tab -> Database location -> External MySQL database*
  • Operations Manager UI -> PAS tile -> UAA tab -> Choose the location of your UAA database -> other external database*
  • Operations Manager UI -> PAS tile -> Credhub tab -> Choose the location of your CredHub database -> other external database*
  • Operations Manager UI -> PAS tile ->  Databases -> Choose the location of your system databases -> External Databases - (e.g. AWS RDS)*
• One of the following conditions holds true when we are configuring certificates for external databases:
  • Either we already have 2015 RDS bundle applied and are adding the new 2019 RDS Bundle
  • Or we are configuring the external database to use 2019 RDS Bundle
• The following are specific locations where we are adding certificates:
  • Operations Manager UI -> Bosh Director tile -> Director Config tab -> Database location
    • Enable TLS is checked
    • TLS CA is where we put certificates
  • Operations Manager UI -> PAS tile -> UAA tab -> Choose the location of your UAA database -> other external database -> CA Certificate
  • Operations Manager UI -> PAS tile -> Credhub tab -> Choose the location of your CredHub database -> other external database -> Database CA Certificate
  •  Operations Manager UI -> PAS tile ->  Databases -> Choose the location of your system databases -> External Databases -> Database CA Certificate

*This is where we put the RDS instance credentials and certificates

Symptoms

• Apply change fails with "Mysql2::Error: MySQL server has gone away" error:

Task 233 debug logs shows the following UPDATE query to "deployments" database failed with MySQL server has gone away error:

E, [2019-10-28T05:31:18.087192 #4426] [task:233] ERROR -- DirectorJobRunner: Mysql2::Error: MySQL server has gone away: (conn: 47089563510420) UPDATE `deployments` SET `name` = 'cf', 
`manifest` = '---\naddons: []\ninstance_groups:\n- azs:\n  - us-west-1a\n  - us-west-1b\n  env:\n    bosh:\n      password: \"$6$5ee878bd422106b9$EOpyR7AsPPZcGsCvRY0N8xdnYC5IPeI5dL6ojqHaZK/FFyPriPApF9lJBLtB9uUxa9ZP/rruNotVuY36cBuVE/\"\n  instances: 0\n  jobs:\n  - consumes: {}\n    name: loggregator_agent\n    properties:\n      loggregator:\n        tls:\n          agent:\n            cert: \"((/opsmgr/cf/doppler/metron_tls_cert.cert_pem))\"\n            key: \"((/opsmgr/cf/doppler/metron_tls_cert.private_key_pem))\"\n          ca_cert: <BIG CHUNK OF DATA>

Cause

Check out the general reasons and resolution for "MySQL server has gone away" when connecting to my Amazon RDS for MySQL DB instance? and specifically this resolution statement:

"If the query that is generating the error is retrieving a large data set, increase the max_allowed_packet size parameter by using a custom parameter group. For more information, see Modifying Parameters in a DB Parameter Group"

Environment

Product Version: 2.6
OS: Linux

Resolution

• The default max_allowed_packet size is 4MB based on this doc: Best Practices for configuring parameters for Amazon RDS for MySQL
• 2019 Certificate bundle which was added in Bosh and TAS configurations sent an UPDATE query from Director to deployments database residing in RDS with a packet size which was > 4MB
• This resulted in RDS not being able to process the UPDATE query and thus "MySQL Server has gone away" error.
• To resolve this issue, modify the parameter group in Amazon RDS which is used for each database instance that Bosh or TAS external databases know about. (Amazon RDS -> DB instances -> Click DB name/identifier -> Configuration tab -> Parameter group -> Click parameter group name -> Edit Parameters -> Edit max_allowed_packet with a value in bytes (Eg. Recommendation - 64000000 bytes(64MB))
• Save changes, wait for the modification to propagate properly.
• Reboot the RDS instance (Amazon RDS -> DB instances -> Select DB name -> Click Actions dropdown -> Select Reboot)
• Wait for the reboot to complete and then run the Apply Changes