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:
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',
$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!');
}
LinkedIn
Twitter
