Connecting SpringBoot to Cloud SQL in Cloud Run error, connection refused

Question

I'm deploying my Maven Springboot project to Google Cloud Run and trying to connect to Cloud SQL from Cloud Run. I'm using https://github.com/spring-cloud/spring-cloud-gcp/tree/master/spring-cloud-gcp-samples/spring-cloud-gcp-data-jpa-sample as a helper to connect to Cloud SQL. Working locally, I can connect to the instance using this library and gcloud auth application-default login to create a proxy. However, when deployed to Cloud Run, I got this exception:

I'm also running different profiles on Cloud Run (profile: qa) and local (default profile)

# For QA - application-qa.yml
spring:
  jpa:
    hibernate:
      ddl-auto: create-drop
    properties:
      hibernate:
        temp:
          use_jdbc_metadata_defaults: false
  datasource:
    username: ${DATABASE_USERNAME}
    password: ${DATABASE_PASSWORD}
  cloud:
    gcp:
      sql:
        database-name: ${DATABASE_NAME}
        instance-connection-name: ${DATABASE_CONNECTION_NAME}
        enabled: true
      logging:
        enabled: true

Clarification: My local environment uses MySql from Docker image meaning that I connect to local database, that's why I have datasource.url inside application.yml for default profile.

# Default - application.yml
spring:
  datasource:
    url: jdbc:mysql://${DATABASE_HOSTNAME}:${DATABASE_PORT}/${DATABASE_NAME}?createDatabaseIfNotExist=true&characterEncoding=UTF-8&allowPublicKeyRetrieval=true&useSSL=false&${DATABASE_ARGS:}
    username: ${DATABASE_USERNAME}
    password: ${DATABASE_PASSWORD}
    driver-class-name: com.mysql.cj.jdbc.Driver

Moreover, I already tried to delete datasource.url from applcation.yml (default profile) to test if this makes spring confused about connection target or not, but turns out the error is still there.

Edit 1

In case needed, I already set cloud-sql-instance attached to my Cloud Run service. My SQL Instance allows both public and private IPs and allows non-secure (no-SSL) connections. IAM policy for accounts:

${PROJECT_ID}-compute@developer.gserviceaccount.com (default SA) : Project Editor / Cloud SQL Admin
<Cloud Run Service Agent> : Cloud Run Service Agent

because official doc said that Cloud Run will authenticate to Cloud SQL using specific service account which, in this case, is default one so I didn't grant Cloud SQL Admin/Client to <Cloud Run Service Agent>

Edit 2

Here is my pom.xml

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.1.8.RELEASE</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>
    <groupId>com.backend</groupId>
    <artifactId>backend</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <name>backend</name>
    <description>Spring Project</description>
    <properties>
        <java.version>1.8</java.version>
        <kotlin.version>1.2.71</kotlin.version>
        <testcontainers.version>1.12.2</testcontainers.version>
    </properties>

    <repositories>
        <repository>
            <id>plugin-release</id>
            <name>spring-plugin</name>
            <url>https://repo.spring.io/plugins-release</url>
        </repository>
        <repository>
            <snapshots>
                <enabled>false</enabled>
            </snapshots>
            <id>jcenter-releases</id>
            <name>jcenter</name>
            <url>http://jcenter.bintray.com</url>
        </repository>
    </repositories>

    <dependencyManagement>
        <dependencies>
            <dependency>
                <groupId>org.springframework.cloud</groupId>
                <artifactId>spring-cloud-gcp-dependencies</artifactId>
                <version>${project.version}</version>
                <type>pom</type>
            </dependency>
        </dependencies>
    </dependencyManagement>

    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-actuator</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-jdbc</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-jpa</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-mongodb</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-validation</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-security</artifactId>
        </dependency>
        <dependency>
            <groupId>net.sargue</groupId>
            <artifactId>mailgun</artifactId>
            <version>1.9.0</version>
        </dependency>
        <dependency>
            <groupId>com.fasterxml.jackson.module</groupId>
            <artifactId>jackson-module-kotlin</artifactId>
        </dependency>
        <dependency>
            <groupId>org.jetbrains.kotlin</groupId>
            <artifactId>kotlin-reflect</artifactId>
        </dependency>
        <dependency>
            <groupId>org.jetbrains.kotlin</groupId>
            <artifactId>kotlin-stdlib-jdk8</artifactId>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-configuration-processor</artifactId>
            <optional>true</optional>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-test-autoconfigure</artifactId>
        </dependency>
        <dependency>
            <groupId>commons-io</groupId>
            <artifactId>commons-io</artifactId>
            <version>2.6</version>
        </dependency>
        <dependency>
            <groupId>io.springfox</groupId>
            <artifactId>springfox-swagger-ui</artifactId>
            <version>2.9.2</version>
        </dependency>
        <dependency>
            <groupId>io.springfox</groupId>
            <artifactId>springfox-swagger2</artifactId>
            <version>2.9.2</version>
        </dependency>
        <dependency>
            <groupId>com.amazonaws</groupId>
            <artifactId>aws-java-sdk-s3</artifactId>
            <version>1.11.560</version>
        </dependency>
        <dependency>
            <groupId>org.modelmapper</groupId>
            <artifactId>modelmapper</artifactId>
            <version>2.3.0</version>
        </dependency>
        <dependency>
            <groupId>org.modelmapper.extensions</groupId>
            <artifactId>modelmapper-jackson</artifactId>
            <version>2.3.0</version>
        </dependency>
        <dependency>
            <groupId>org.modelmapper.extensions</groupId>
            <artifactId>modelmapper-spring</artifactId>
            <version>2.3.0</version>
        </dependency>
        <dependency>
            <groupId>org.apache.commons</groupId>
            <artifactId>commons-text</artifactId>
            <version>1.6</version>
        </dependency>
        <dependency>
            <groupId>org.apache.tika</groupId>
            <artifactId>tika-core</artifactId>
            <version>1.21</version>
        </dependency>
        <dependency>
            <groupId>io.github.swagger2markup</groupId>
            <artifactId>swagger2markup</artifactId>
            <version>1.3.3</version>
        </dependency>
        <dependency>
            <groupId>com.corundumstudio.socketio</groupId>
            <artifactId>netty-socketio</artifactId>
            <version>1.7.17</version>
        </dependency>
        <dependency>
            <groupId>com.fasterxml.jackson.datatype</groupId>
            <artifactId>jackson-datatype-jsr310</artifactId>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
        </dependency>

        <dependency>
            <groupId>org.glassfish.jersey.inject</groupId>
            <artifactId>jersey-hk2</artifactId>
            <version>2.28</version>
        </dependency>
        <dependency>
            <groupId>org.hibernate</groupId>
            <artifactId>hibernate-search-orm</artifactId>
            <version>5.11.1.Final</version>
        </dependency>
        <dependency>
            <groupId>com.h2database</groupId>
            <artifactId>h2</artifactId>
            <version>1.4.197</version>
        </dependency>
        <dependency>
            <groupId>org.testcontainers</groupId>
            <artifactId>testcontainers</artifactId>
            <version>${testcontainers.version}</version>
            <scope>test</scope>
        </dependency>
        <dependency>
            <groupId>org.testcontainers</groupId>
            <artifactId>mysql</artifactId>
            <version>1.12.2</version>
            <scope>test</scope>
        </dependency>
        <dependency>
            <groupId>org.springframework.security</groupId>
            <artifactId>spring-security-test</artifactId>
            <scope>test</scope>
        </dependency>
        <dependency>
            <groupId>com.google.code.gson</groupId>
            <artifactId>gson</artifactId>
            <version>2.8.5</version>
        </dependency>
        <dependency>
            <groupId>org.hibernate.validator</groupId>
            <artifactId>hibernate-validator</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.cloud</groupId>
            <artifactId>spring-cloud-gcp-starter-sql-mysql</artifactId>
            <version>1.1.3.RELEASE</version>
        </dependency>
    </dependencies>

    <build>
        <sourceDirectory>${project.basedir}/src/main/kotlin</sourceDirectory>
        <testSourceDirectory>${project.basedir}/src/test/kotlin</testSourceDirectory>
        <extensions>
            <extension>
                <groupId>kr.motd.maven</groupId>
                <artifactId>os-maven-plugin</artifactId>
                <version>1.6.1</version>
            </extension>
        </extensions>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
            <plugin>
                <groupId>org.jetbrains.kotlin</groupId>
                <artifactId>kotlin-maven-plugin</artifactId>
                <configuration>
                    <compilerPlugins>
                        <plugin>jpa</plugin>
                        <plugin>spring</plugin>
                    </compilerPlugins>
                    <args>
                        <arg>-Xjsr305=strict</arg>
                    </args>
                    <languageVersion>1.2</languageVersion>
                </configuration>
                <dependencies>
                    <dependency>
                        <groupId>org.jetbrains.kotlin</groupId>
                        <artifactId>kotlin-maven-noarg</artifactId>
                        <version>${kotlin.version}</version>
                    </dependency>
                    <dependency>
                        <groupId>org.jetbrains.kotlin</groupId>
                        <artifactId>kotlin-maven-allopen</artifactId>
                        <version>${kotlin.version}</version>
                    </dependency>
                </dependencies>
            </plugin>
            <plugin>
                <groupId>org.xolstice.maven.plugins</groupId>
                <artifactId>protobuf-maven-plugin</artifactId>
                <version>0.6.1</version>
                <configuration>
                    <protocArtifact>
                        com.google.protobuf:protoc:3.3.0:exe:${os.detected.classifier}
                    </protocArtifact>
                    <pluginId>grpc-java</pluginId>
                    <pluginArtifact>
                        io.grpc:protoc-gen-grpc-java:1.4.0:exe:${os.detected.classifier}
                    </pluginArtifact>
                </configuration>
                <executions>
                    <execution>
                        <goals>
                            <goal>compile</goal>
                            <goal>compile-custom</goal>
                        </goals>
                    </execution>
                </executions>
            </plugin>
        </plugins>
    </build>

</project>

Edit 3

I can now connect to Cloud SQL via locally deployment. Seems like I cannot connect to SQL Instance when deployed via Cloud Build triggers. I'm using connection string as follow: <project_id>:asia-southeast1:farose-test-db Cloud Run is using default service account, which is -compute@developer.gserviceaccount.com` with assigned role as follows:

Cloud SQL Client
Cloud Run Invoker
Editor

Edit 4

Seems like the issue came from missing several files in image during build step which explain why building and deploying locally works just fine. I disabled cache from kaniko executor right now.

Since the connection is working locally, the connection refused error is most likely due to an authentication error. Is the database located in another Google Cloud Platform project? If you try to give the pertinent service accounts mentioned owner permissions does your app works correctly (this later step can help us debug if the connection refused issue is really about authentication)? — Daniel Ocando, Nov 18 '19 at 14:01
@DanielOcando granted owner to service account, still connection refused — hackinteachk, Nov 18 '19 at 14:44
Have you tried updating the Cloud Run QA service (I'm assuming that you mean [services](https://cloud.google.com/run/docs/managing/services) when you are referring to profiles) with the `gcloud beta run services update SERVICE --add-cloudsql-instances INSTANCE-CONNECTION-NAME --set-env-vars INSTANCE-CONNECTION-NAME="INSTANCE-CONNECTION-NAME"` from the Cloud Shell? If you receive an error message after running this command please edit your answer accordingly. — Daniel Ocando, Nov 18 '19 at 15:16
@DanielOcando it throws error : `Container failed to start.` which comes from mysql error. Also my profile is referring to Spginboot's profile and service is the cloud run service) — hackinteachk, Nov 18 '19 at 15:27
I think that your application.yaml succeeds since it's properly authenticated with the `gcloud auth application-default login` and you can specify not to use SSL on the `spring:datasource:url:jdbc:mysql://XXX` field. I noticed that on the documentation's [code sample](https://cloud.google.com/sql/docs/mysql/connect-run#connecting_to) the `useSSL` field is set to false. And it wouldn't hurt to include a `spring.cloud.gcp.sql.credentials.location:` field pointing to the Cloud Run's service account key file with Cloud SQL Admin permissions on your `application-qa.yaml`. — Daniel Ocando, Nov 19 '19 at 11:09
for `useSSL` I already set to `false`. about credentials location, that mean I have to push / host `service-account-key.json` somewhere and put it in container on deployment stage right since currently I use `cloud run` to authenticate service account. — hackinteachk, Nov 19 '19 at 15:29
@DanielOcando I also tried adding `credentials.location` as you mentioned, not working also :( — hackinteachk, Nov 19 '19 at 16:53
@hackinteachk did you use flag `--add-cloudsql-instances` with your instance name in format PROJECT:LOCATION:DB_NAME? — Averi Kitsch, Nov 20 '19 at 01:31
@AveriKitsch yes I use `INSTANCE_CONNECTION_NAME` for that flag, checked with cloud run console — hackinteachk, Nov 20 '19 at 04:26
@hackinteachk did you also updated the [applications.properties file](https://github.com/spring-cloud/spring-cloud-gcp/blob/master/spring-cloud-gcp-samples/spring-cloud-gcp-data-jpa-sample/src/main/resources/application.properties) in order to update the path of the service account private key? — Daniel Ocando, Nov 20 '19 at 08:55
@DanielOcando yes, I also tried with newly created springboot project with just `gcp-cloud-sql-starter` dependency and configuration in `application.yml` and it works! I think it's the Springboot issue now not really cloudsql — hackinteachk, Nov 20 '19 at 10:43
Another suggestion would be to use the the [jib maven plugin](https://github.com/GoogleContainerTools/jib/tree/master/jib-maven-plugin). This tool makes really easy to Dockerize java applications and then deploy them to Cloud Run. You can find an example of Jib with the famous [Petclinic Springboot app](https://codelabs.developers.google.com/codelabs/cloud-spring-petclinic-cloudsql/index.html#0) (which you can modify to use Cloud SQL) on this [video](https://www.youtube.com/watch?v=H6gR_Cv4yWI). — Daniel Ocando, Nov 21 '19 at 16:57
@DanielOcando thank you, I'll try that lib, thank you so much. Really appreciate your help ! — hackinteachk, Nov 22 '19 at 08:35
In Google Cloud Run, there are two ways to connect to a SQL instance. 1) Using the Cloud SQL Proxy (which is a sidecar in Cloud Run) or 2) Using direct `IP:PORT` addressing. Which one are you using? Show the connection string that you have generated. For the second method, Cloud Run permissions do not matter. — John Hanley, Nov 22 '19 at 16:43
@JohnHanley I currently using connection name not really cloud sql proxy, current one is `::` — hackinteachk, Nov 23 '19 at 09:17
@JohnHanley updated, I can now connect to cloud sql BUT WITHOUT cloud build triggers, meaning I can deploy it using local CLI. Any suggestion on this issue ? let me know if you want further information — hackinteachk, Nov 23 '19 at 14:50
1/2) Ok, that narrows it down to you are using method #1 (Cloud SQL Proxy). Connecting to Cloud SQL is easy from Cloud Run. That means you are making a simple mistake. The answer is in the details. Note: I am not sure what you mean by "using connection name not really cloud sql proxy". You are either connecting to Cloud SQL Proxy or you must use IP:PORT addressing. — John Hanley, Nov 23 '19 at 16:39
2/2) 1) What is the connection string? Mask the Project ID, but I want so see the entire connection string. 2) What is the service account that is assigned to Cloud Run? 3) What permissions (roles) are assigned to this service account? Edit your question with this information as the details in your question do not match your comments. — John Hanley, Nov 23 '19 at 16:39
@JohnHanley I tried extract jar file inside image built from cloud build and turns out there are several files are missing so I think it's kaniko executor issue. I disabled cache for now and it works. — hackinteachk, Nov 25 '19 at 05:50

Connecting SpringBoot to Cloud SQL in Cloud Run error, connection refused

Edit 1

Edit 2

Edit 3

Edit 4

0 Answers0