Spring Boot + Spring Data on GAE with CloudSql - Database Connection

Question

I'm unable to deploy a Spring Boot + Spring Data application to Google App Engine and have the app connect to a Google CloudSql (MySQL) database.

The GAE application and CloudSql instance are in the same project.

I'm able to run the entire app and connect to CloudSql from localhost with no issue.

Spring Boot without a database connection works correctly without error on GAE. Adding Spring Data throws a "com.mysql.cj.jdbc.exceptions.CommunicationsException: Communications link failure" error.

In the pom.xml I have included:

<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>6.0.6</version>
</dependency>
<dependency>
    <groupId>com.google.cloud.sql</groupId>
    <artifactId>mysql-socket-factory</artifactId>
    <version>1.0.2</version>
</dependency>

Also, I've tried to use only the mysql dependency:

<dependency>
     <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>6.0.6</version>
</dependency>

In my research, I have not found any good examples or related issues to Spring Boot + Spring Data + GAE + CloudSql.

What are the correct settings for connecting to a CloudSql DB in the application.properties file? I've tried the following, but all threw various errors:

spring.jpa.hibernate.ddl-auto:none

# tried with GoogleDriver and without:
# spring.datasource.driver-class-name=com.mysql.jdbc.GoogleDriver

# urls configs attempted:
# spring.datasource.url=jdbc:mysql://google/**db-name**?cloudSqlInstance=***instance-name***b&socketFactory=com.google.cloud.sql.mysql.SocketFactory
# spring.datasource.url=jdbc:mysql://***instance-name***/**db-name**?user=root
# spring.datasource.url=jdbc:mysql://localhost:3306/**db-name**
spring.datasource.url=jdbc:mysql://127.0.0.1:3306:3306/**db-name**
# spring.datasource.url=jdbc:mysql://***mysql ip address***:3306/**db-name**

spring.datasource.username=***user***
spring.datasource.password=***password***

Answer 1

I had the same issue, below properties in Spring boot application.properties worked for me.

database=mysql
spring.datasource.driverClassName=com.mysql.jdbc.Driver
spring.datasource.url=jdbc:mysql://google/{db-schema}?socketFactory=com.google.cloud.sql.mysql.SocketFactory&cloudSqlInstance={Cloud-sql-Instance-Name}

spring.datasource.username=root
spring.datasource.password=password

Answer 2

You have to follow following steps :

1) Enable Cloud SQL API by going to following link:

https://console.cloud.google.com/flows/enableapi?apiid=sqladmin&redirect=https:%2F%2Fconsole.cloud.google.com

2) Download service account JSON key file from https://console.cloud.google.com/apis/credentials/serviceaccountkey

3) Set this filepath as environment variable with key GOOGLE_APPLICATION_CREDENTIALS.

 on Windows OS open command prompt and run command: set GOOGLE_APPLICATION_CREDENTIALS=[File path] 

 on Linux OS on console run command : export GOOGLE_APPLICATION_CREDENTIALS=[File path]

4) Add following dependencies in pom.xml :

 `<dependency>
      <groupId>mysql</groupId>
      <artifactId>mysql-connector-java</artifactId>
      <version>6.0.6</version>
  </dependency>
  <dependency>
      <groupId>com.google.cloud.sql</groupId>
      <artifactId>mysql-socket-factory</artifactId>
      <version>1.0.2</version>
  </dependency>
  <dependency>
      <groupId>com.google.api-client</groupId>
      <artifactId>google-api-client</artifactId>
      <version>1.23.0</version>
  </dependency>

`

5) Use following properties in application.properties file :

spring.datasource.url=jdbc:mysql://google/[Database-Name]?cloudSqlInstance=[instance-Connection-Name]&socketFactory=com.google.cloud.sql.mysql.SocketFactory&useSSL=false spring.datasource.username=[Database-Username] spring.datasource.password=[Database-Password]

Hope this helps.