0

Services Used:

  • Google Compute Engine (GCE) (Always-On or Preemptible)
  • Ubuntu 16.04 LTS
  • PHP
  • Google Cloud SQL
  • Google Cloud SQL Proxy

Problem: Google Cloud SQL Proxy Connection is closed before the Google Shutdown Script is started.

Issue Caused: "MySQL server has gone away" and similar error messages in PHP during the shutdown.

After a lot of investigating, it appears that during a shutdown of an instance, the database connection that was started during the Google Cloud Startup script is shutdown before the Shutdown Script starts.

I have tried to change the order of the systemctl services, but I am not able to find the right order to load things.

During image creation, this content is inserted into /lib/systemd/system/google-shutdown-scripts.service.d/bizzycloud.conf

[Unit]
After=apache2.service haproxy.service
Wants=apache2.service haproxy.service google-startup-scripts.service

Startup Script Snippet:

sudo /cloud_sql_proxy -dir=/cloudsql -instances_metadata /project/attributes/DB_Connections &

Shutdown Script Snippet

echo "Shutting Down"
curl "http://localhost/run/shutdowntest.php" # < This reports database errors
sudo /cloud_sql_proxy -dir=/cloudsql -instances_metadata /project/attributes/DB_Connections & # < Reconnect back to the database
curl "http://localhost/run/shutdowntest.php" # < This reports database is ok

There are no problems with the database while the servers are up, but as soon as they start to shutdown, we start noticing issues.

My google-shutdown-scripts.service.d/bizzycloud.conf file does not seem to change the order of the Startup and Shutdown scripts.

This could well be related to similar issues that people have been having with random Cloud SQL errors during "Health Check Recovery" or servers shutting down.

Would someone be able to help come up with a solution to this as our setup is very volatile and servers scale up and down all the time so we are getting a lot of these errors.

Thank you

Carl Smith
  • 23
  • 4

1 Answers1

0

Sorry for posting my own answer, but this may be of use to someone.

So rather than going down the route of trying to change the order of the Google Startup and Shutdown scripts, I went down the route of creating my own service (sounded scary to start with).

Created a file called /cloudsqlservice/cloudsql-startup.sh with the contents 

#!/bin/bash

echo "Starting up Google Cloud SQL"
sudo /cloud_sql_proxy -dir=/cloudsql -instances_metadata /project/attributes/DB_Connections

Created a systemd service by creating a file called /lib/systemd/system/bizzycloud-sql.service

[Install]
WantedBy=multi-user.target

[Unit]
Description=Google Cloud Compute Engine SQL Proxy
After=networking.service
Before=google-shutdown-scripts.service

[Service]
Type=simple
WorkingDirectory=/cloudsqlservice
ExecStart=/cloudsqlservice/cloudsql-startup.sh
StandardOutput=journal
User=root

Note: Before= : Although this looks like it is telling it to run before the shutdown script, actually, during a system shutdown the Before and After Settings are inverted, so the shutdown of this service should be running after the shutdown script

Modified from: running the proxy as a service

sudo chmod 0644 /lib/systemd/system/bizzycloud-sql.service
sudo systemctl start bizzycloud-sql.service

Hope this helps anyone else that comes across the same issue.

Carl Smith
  • 23
  • 4