Connecting to Cloud SQL In Cloud Build Through VPC... - Page 2

vincemcgaj · 07-25-2023 09:45 AM

I am trying to connect to a Cloud SQL database during a Cloud Build step so I can run database migrations for my app. But I keep running into errors such as:

SQLSTATE[HY000] [2002] Operation timed out

I have followed the instructions here very closely:

https://cloud.google.com/sql/docs/mysql/connect-build#php_2

I am able to connect to the database through the Cloud SQL Proxy using a public IP address just fine. I would like to switch to private for stronger security so we may disable the public connection.
I have a VPC network set up. It's not the default network. I have my Cloud SQL Instance connected to this network. I am able to connect to my Cloud SQL Instance using a private connection through BigQuery, Cloud Run, and even Cloud Functions but not Cloud Build for some reason.
I have set up a private connection between the VPC network of my Cloud SQL Instance and the Service Producer network. I have allocated an IP range and made a private connection. I am fairly certain this step is done correctly but I'm still rather new to networks with GCP.
I created a Cloud Build Worker Pool. I have tried both a private pool in the VPC network and a private pool in the Service Producer network (when you do not provide a project ID/name or VPC network). Both options produced the same results.
I have tried connecting to the database through the Cloud Build step inside the cloudbuild.yaml itself and from a separate file as seen in the documentation, they both produce the same error.
I have tried creating and assigning a Service Account to the Cloud Build Trigger and have assigned it all of the proper permissions needed to connect to the Cloud SQL instance. I got the same error.
I have given my built-in Cloud Build service account permissions. This service account is in the same project as the VPC and the Cloud SQL instance. Whenever I tried to assign a role it just give me a message saying "No changes were applied, this account already has those permissions".
My app is build in Laravel and PHP. I use cloudbuild.yaml for all the cloud build steps and I have a Dockerfile for installing all the necessary dependencies for the app.
I am fairly certain this is a network issue. I have tried random solutions such as making Firewall rules but nothing works. I have tried using a Virtual Machine but am thinking that is unnecessary because Cloud Build already runs in a VM by default. After viewing the documentation, it does not seem that you need a VM at all for this.

Here is my cloudbuild.yaml:

steps:

- name: gcr.io/cloud-builders/docker

args:

- build

- '--no-cache'

- '-t'

- '$_GCR_HOSTNAME/$PROJECT_ID/$REPO_NAME/$_SERVICE_NAME:$COMMIT_SHA'

- .

- '-f'

- Dockerfile

id: Build

- name: gcr.io/cloud-builders/docker

args:

- push

- '$_GCR_HOSTNAME/$PROJECT_ID/$REPO_NAME/$_SERVICE_NAME:$COMMIT_SHA'

id: Push

- name: 'gcr.io/google.com/cloudsdktool/cloud-sdk:slim'

args:

- run

- services

- update

- $_SERVICE_NAME

- '--platform=managed'

- '--image=$_GCR_HOSTNAME/$PROJECT_ID/$REPO_NAME/$_SERVICE_NAME:$COMMIT_SHA'

- >-

--labels=managed-by=gcp-cloud-build-deploy-cloud-run,commit-sha=$COMMIT_SHA,gcb-build-id=$BUILD_ID,gcb-trigger-id=$_TRIGGER_ID,$_LABELS

- '--region=$_DEPLOY_REGION'

- '--quiet'

id: Deploy

entrypoint: gcloud

# This step fails to make a connection.

- id: "Connect"

name: "$_GCR_HOSTNAME/$PROJECT_ID/$REPO_NAME/$_SERVICE_NAME:$COMMIT_SHA"

dir: sql-private-pool

env:

- "CLOUD_BUILD_DB_NAME=$_DB_DATABASE"

secretEnv: ['CLOUD_BUILD_DB_USERNAME', 'CLOUD_BUILD_DB_PASSWORD', 'CLOUD_BUILD_DB_HOST']

entrypoint: php

args: ["/app/sql-private-pool/migrate.php"]

# This step also fails to make a connection.

- name: 'gcr.io/google-appengine/exec-wrapper'

entrypoint: 'bash'

args:

- -c

- |

/buildstep/execute.sh \

-i $_GCR_HOSTNAME/$PROJECT_ID/$REPO_NAME/$_SERVICE_NAME:$COMMIT_SHA \

-e DB_CONNECTION=mysql \

-e DB_HOST=$$CLOUD_BUILD_DB_HOST \

-e DB_PORT=3306 \

-e CLOUD_SQL_CONNECTION_NAME=$_DB_CONNECTION \

-e DB_DATABASE=$_DB_DATABASE \

-e DB_USERNAME=$$CLOUD_BUILD_DB_USERNAME \

-e DB_PASSWORD=$$CLOUD_BUILD_DB_PASSWORD \

-s $_DB_CONNECTION \

-- php /app/artisan migrate --force

secretEnv: ['CLOUD_BUILD_DB_USERNAME', 'CLOUD_BUILD_DB_PASSWORD', 'CLOUD_BUILD_DB_HOST']

id: Migrate

timeout: 1200s

images:

- '$_GCR_HOSTNAME/$PROJECT_ID/$REPO_NAME/$_SERVICE_NAME:$COMMIT_SHA'

options:

pool:

name: projects/$PROJECT_ID/locations/$WORKERPOOL_LOCATION/workerPools/$VPC_NAME

substitutionOption: ALLOW_LOOSE

availableSecrets:

secretManager:

- versionName: projects/$PROJECT_ID/secrets/CLOUD_BUILD_DB_PASSWORD/versions/latest

env: 'CLOUD_BUILD_DB_PASSWORD'

- versionName: projects/$PROJECT_ID/secrets/CLOUD_BUILD_DB_USERNAME/versions/latest

env: 'CLOUD_BUILD_DB_USERNAME'

- versionName: projects/$PROJECT_ID/secrets/CLOUD_BUILD_DB_HOST/versions/latest

env: 'CLOUD_BUILD_DB_HOST'

tags:

- gcp-cloud-build-deploy-cloud-run

- gcp-cloud-build-deploy-cloud-run-managed

Here is my migrate.php:

<?php

namespace Google\Cloud\Samples\CloudSQL\MySQL;

use PDO;

use PDOException;

use RuntimeException;

use TypeError;

class DatabaseTcp

{

public static function initTcpDatabaseConnection(): PDO

{

try {

$username = getenv('CLOUD_BUILD_DB_USERNAME');

$password = getenv('CLOUD_BUILD_DB_PASSWORD');

$dbName = getenv('CLOUD_BUILD_DB_NAME');

$instanceHost = getenv('CLOUD_BUILD_DB_HOST');

// Connect using TCP

$dsn = sprintf('mysql:dbname=%s;host=%s', $dbName, $instanceHost);

// Connect to the database

$conn = new PDO(

$dsn,

$username,

$password,

[

PDO::ATTR_TIMEOUT => 5,

PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,

]

);

// Check if the connection is successful

if ($conn->isConnected()) {

print("\n");

print("=========================");

print("\n");

print("Connected to the database successfully!");

} else {

print("\n");

print("=========================");

print("\n");

print("Failed to connect to the database.");

}

// Run migration command

exec('cd .. && php /app/artisan migrate --force', $output, $returnVar);

if ($returnVar !== 0) {

throw new RuntimeException('Migration failed: ' . implode("\n", $output));

}

} catch (TypeError $e) {

throw new RuntimeException(

sprintf(

'Invalid or missing configuration! Make sure you have set ' .

'$username, $password, $dbName, and $instanceHost (for TCP mode). ' .

'The PHP error was %s',

$e->getMessage()

),

$e->getCode(),

$e

);

} catch (PDOException $e) {

print($e->getMessage());

throw new RuntimeException(

sprintf(

'Could not connect to the Cloud SQL Database. Check that ' .

'your username and password are correct, that the Cloud SQL ' .

'proxy is running, and that the database exists and is ready ' .

'for use. For more assistance, refer to %s. The PDO error was %s',

'https://cloud.google.com/sql/docs/mysql/connect-external-app',

$e->getMessage()

),

$e->getCode(),

$e

);

}

return $conn;

}

try {

DatabaseTcp::initTcpDatabaseConnection();

print("\n");

print("=========================");

print("\n");

print('Connected to Cloud SQL');

} catch (RuntimeException $e) {

print("\n");

print("=========================");

print("\n");

print('Error: could not connect to Cloud SQL!');

}

Connecting to Cloud SQL In Cloud Build Through VPC Private Connection