Fixing multiple instances at the same time

[thatkookooguy]

The Power of Chat GPT: Real-Life Usage

Problem

mattes/gce-cloudsql-proxy-action have a few problems for our usage:

• The github action was last updated 2 years ago. This might not mean the package is not maintained anymore, but based on the last issue opened (with a PR attached) that wasn't finished, seems like the project is kinda dying.

• The original GitHub Action only supports connecting to a single instance per time. that's because the fact the action adds a name attribute to the docker run command, making docker complain with the following error:

  docker: Error response from daemon: Conflict. The container name "/gce-cloudsql-proxy" is already in use by container "fa8e929a09fe5d01a6b80a73a35898e9a2c7a928447dcce915db3328551fed80". You have to remove (or rename) that container to be able to reuse that name.

  An issue and a PR were open to fix this, but that code didn't work as well when tested
  

Solution

It's easy to see from the original code and the PR related to the issue that the action basically runs docker run and uses a temp folder to store the service account json key for the docker container to use. We need to allow multiple docker run commands to run in parallel with different ports, and different instances. for now, we don't mind sharing the same service account (for our use-case), so this is not a direct goal.

Since we understand this, but we don't know bash scripting that well, we decided to ask chat GPT for help! 💬

[thatkookooguy]

First, we pasted the action code (the yaml file) and asked it the following question:

🗣️ Question: I have this github action code. please explain to me what it does

This was done for a baseline, and to check chat GPT understands the code.

[thatkookooguy]

🗣️ Question: Can you re-write this action in a way that allows it to run this multiple times in parallel?

I want to be able to call this action twice within a pipeline and do actions on both databases (using a different port for each proxy)

[thatkookooguy]

🤖 ANSWER: Can you re-write this action in a way that allows it to run this multiple times in parallel?

Certainly! Here's a modified version of the GitHub Actions code that allows running the Cloud SQL Proxy action multiple times in parallel:

name: "Google Cloud SQL Proxy"
description: "Start Google CloudSQL Proxy"

branding:
  icon: "database"
  color: "red"

inputs:
  creds:
    description: "Contents of a Service Account JSON Key"
    required: true
  instances:
    description: "CloudSQL instances (comma-separated)"
    required: true
  ports:
    description: "Listen ports (comma-separated)"
    required: false
    default: "5432"
  proxy_version:
    description: "CloudSQL Proxy Version"
    required: false
    default: "1.21.0"

runs:
  using: "composite"
  steps:
    - name: Start Google Cloud SQL Proxy
      id: proxy
      run: |
        instances=(${{ inputs.instances }})
        ports=(${{ inputs.ports }})
        proxy_version="${{ inputs.proxy_version }}"

        # Split comma-separated inputs into arrays
        IFS=',' read -ra instances <<< "$instances"
        IFS=',' read -ra ports <<< "$ports"

        # Check if the number of instances and ports match
        if [[ ${#instances[@]} -ne ${#ports[@]} ]]; then
          echo "Error: Number of instances and ports should match."
          exit 1
        fi

        # Start Google Cloud SQL Proxy for each instance
        for i in "${!instances[@]}"; do
          instance="${instances[$i]}"
          port="${ports[$i]}"

          # write google application credentials to a temporary file to be used inside the container
          mkdir -p /tmp/gce-cloudsql-proxy-$i
          echo '${{ inputs.creds }}' > /tmp/gce-cloudsql-proxy-$i/key.json

          # start container
          docker run -d --net host --name gce-cloudsql-proxy-$i --restart on-failure \
          -v /tmp/gce-cloudsql-proxy-$i:/tmp/gce-cloudsql-proxy \
          gcr.io/cloudsql-docker/gce-proxy:$proxy_version \
          /cloud_sql_proxy \
            -credential_file /tmp/gce-cloudsql-proxy-$i/key.json \
            -dir /tmp \
            -instances=$instance=tcp:127.0.0.1:$port

          # wait until connections are accepted
          sleep 3
          isready=0
          for j in {1..10}; do
            echo "Wait for connections to be ready for instance $instance on port $port ... $j/10"
            (${{ github.action_path }}/wait-for-it.sh --quiet --timeout=3 --host=127.0.0.1 --port=$port || exit $?) && true # escape bash's pipefail
            isready=$?
            if [[ $isready -eq 0 ]]; then
              break
            fi
            sleep 2
          done

          # print container logs
          docker logs gce-cloudsql-proxy-$i

          # exit with error code if we couldn't connect
          if [[ $isready -ne 0 ]]; then
            exit $isready
          fi
        done

        # Set the output to be used by subsequent steps
        echo "::set-output name=proxy_ids::$(docker ps -aq --filter 'name=gce-cloudsql-proxy-'*)"

In this modified version, the inputs have been adjusted to accommodate multiple instances and ports:

• The instances input now expects a comma-separated list of CloudSQL instance names.
• The ports input expects a comma

-separated list of ports corresponding to each CloudSQL instance. If not provided, it defaults to port 5432.

Here's how the modified code works:

1. The instances and ports inputs are split into arrays using IFS=',' read -ra instances <<< "$instances" and IFS=',' read -ra ports <<< "$ports", respectively.

2. A check is performed to ensure that the number of instances matches the number of ports.

3. A loop is used to start the Cloud SQL Proxy for each instance. Inside the loop, the code follows a similar structure to the original version.

4. A temporary directory is created for each instance using a unique index $i to avoid conflicts.

5. The Cloud SQL Proxy Docker container is started for each instance with the appropriate instance name and port.

6. After starting the container, the code waits for connections to be established, similar to the original version.

7. Logs from each container are printed.

8. If connections couldn't be established, the script exits with the corresponding error code.

9. Finally, the docker ps command is used to retrieve the IDs of all running Cloud SQL Proxy containers. These IDs are then set as the output proxy_ids, which can be used by subsequent steps in the workflow.

With this modified version, you can provide multiple instances and ports as inputs, and the Cloud SQL Proxy will be started for each instance in parallel.

[thatkookooguy]

Released a new version, but we saw errors

🗣️ Question: I ran your code and I get the following error:

2023/05/28 15:51:55 invalid json file "/tmp/gce-cloudsql-proxy-0/key.json": open /tmp/gce-cloudsql-proxy-0/key.json: no such file or directory

can you fix it?

[thatkookooguy]

🤖 ANSWER: I ran your code and I get the following error. Can you fix it?

Apologies for the oversight. It seems that the temporary directory for the Service Account JSON Key file is not being mounted correctly inside the Docker container. Please use the updated code below:

name: "Google Cloud SQL Proxy"
description: "Start Google CloudSQL Proxy"

branding:
  icon: "database"
  color: "red"

inputs:
  creds:
    description: "Contents of a Service Account JSON Key"
    required: true
  instances:
    description: "CloudSQL instances (comma-separated)"
    required: true
  ports:
    description: "Listen ports (comma-separated)"
    required: false
    default: "5432"
  proxy_version:
    description: "CloudSQL Proxy Version"
    required: false
    default: "1.21.0"

runs:
  using: "composite"
  steps:
    - name: Start Google Cloud SQL Proxy
      id: proxy
      run: |
        instances=(${{ inputs.instances }})
        ports=(${{ inputs.ports }})
        proxy_version="${{ inputs.proxy_version }}"

        # Split comma-separated inputs into arrays
        IFS=',' read -ra instances <<< "$instances"
        IFS=',' read -ra ports <<< "$ports"

        # Check if the number of instances and ports match
        if [[ ${#instances[@]} -ne ${#ports[@]} ]]; then
          echo "Error: Number of instances and ports should match."
          exit 1
        fi

        # Start Google Cloud SQL Proxy for each instance
        for i in "${!instances[@]}"; do
          instance="${instances[$i]}"
          port="${ports[$i]}"

          # write google application credentials to a temporary file to be used inside the container
          mkdir -p $HOME/gce-cloudsql-proxy/$i
          echo '${{ inputs.creds }}' > $HOME/gce-cloudsql-proxy/$i/key.json

          # start container
          docker run -d --net host --name gce-cloudsql-proxy-$i --restart on-failure \
          -v $HOME/gce-cloudsql-proxy/$i:/tmp/gce-cloudsql-proxy \
          gcr.io/cloudsql-docker/gce-proxy:$proxy_version \
          /cloud_sql_proxy \
            -credential_file /tmp/gce-cloudsql-proxy/key.json \
            -dir /tmp \
            -instances=$instance=tcp:127.0.0.1:$port

          # wait until connections are accepted
          sleep 3
          isready=0
          for j in {1..10}; do
            echo "Wait for connections to be ready for instance $instance on port $port ... $j/10"
            (${{ github.action_path }}/wait-for-it.sh --quiet --timeout=3 --host=127.0.0.1 --port=$port || exit $?) && true # escape bash's pipefail
            isready=$?
            if [[ $isready -eq 0 ]]; then
              break
            fi
            sleep 2
          done

          # print container logs
          docker logs gce-cloudsql-proxy-$i

          # exit with error code if we couldn't connect
          if [[ $isready -ne 0 ]]; then
            exit $isready
          fi
        done

        # Set the output to be used by subsequent steps
        echo "::set-output name=proxy_ids::$(docker ps -aq --filter 'name=gce-cloudsql-proxy-'*)"

In the updated code, we are now using the $HOME directory to create a separate directory for each instance of the Cloud SQL Proxy. This ensures that the Service Account JSON

Key file is correctly mounted inside the Docker container. The line mkdir -p $HOME/gce-cloudsql-proxy/$i creates a unique directory for each instance, where $i is the loop index.

Please give it a try, and it should resolve the issue you encountered.

[thatkookooguy]

Created another release with the fix

And tested it out.

🎉 Success! looks like we can run this on multiple sql instances with several proxies.

Updated the documentation a little and called it a day. It's being used by one of our pipelines