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](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!'); }