Cloudsql-proxy: Cloud SQL - Private IP - Same VPC Kubernetes cluster Spring Boot access not working

[satscreate]

Hi,
i have been setting up below stuff and getting it connected but looks like am getting connectivity issues.

Setup:
Terraform 14
GCP - MY SQL 8 - With Private IP
GCP - VPC network + Private service connection + peering + firewall(ssh:22)
Kubernetes - Cluster + Pod [cloudsql-proxy + spring-boot application]

Issue or Exception:
From SpringBoot application:

2021-03-09T13:41:03.290155499Zcom.mysql.cj.jdbc.exceptions.CommunicationsException: Communications link failure
2021-03-09T13:41:03.290162434ZThe last packet sent successfully to the server was 0 milliseconds ago. The driver has not received any packets from the server.

Full Error:

2021-03-09T14:01:10.961745979Zorg.springframework.beans.factory.BeanCreationException: Error creating bean with name 'entityManagerFactory' defined in class path resource [org/springframework/boot/autoconfigure/orm/jpa/HibernateJpaConfiguration.class]: Invocation of init method failed; nested exception is org.hibernate.service.spi.ServiceException: Unable to create requested service [org.hibernate.engine.jdbc.env.spi.JdbcEnvironment] at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1788) ~[spring-beans-5.3.3.jar:5.3.3] at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:609) ~[spring-beans-5.3.3.jar:5.3.3] at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:531) ~[spring-beans-5.3.3.jar:5.3.3] at org.springframework.beans.factory.support.AbstractBeanFactory.lambda$doGetBean$0(AbstractBeanFactory.java:335) ~[spring-beans-5.3.3.jar:5.3.3] at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:234) ~[spring-beans-5.3.3.jar:5.3.3] at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:333) ~[spring-beans-5.3.3.jar:5.3.3] at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:208) ~[spring-beans-5.3.3.jar:5.3.3] at org.springframework.context.support.AbstractApplicationContext.getBean(AbstractApplicationContext.java:1159) ~[spring-context-5.3.3.jar:5.3.3] at org.springframework.context.support.AbstractApplicationContext.finishBeanFactoryInitialization(AbstractApplicationContext.java:913) ~[spring-context-5.3.3.jar:5.3.3] at org.springframework.context.support.AbstractApplicationContext.refresh(AbstractApplicationContext.java:588) ~[spring-context-5.3.3.jar:5.3.3] at org.springframework.boot.web.servlet.context.ServletWebServerApplicationContext.refresh(ServletWebServerApplicationContext.java:144) ~[spring-boot-2.4.2.jar:2.4.2] at org.springframework.boot.SpringApplication.refresh(SpringApplication.java:767) [spring-boot-2.4.2.jar:2.4.2] at org.springframework.boot.SpringApplication.refresh(SpringApplication.java:759) [spring-boot-2.4.2.jar:2.4.2] at org.springframework.boot.SpringApplication.refreshContext(SpringApplication.java:426) [spring-boot-2.4.2.jar:2.4.2] at org.springframework.boot.SpringApplication.run(SpringApplication.java:326) [spring-boot-2.4.2.jar:2.4.2] at org.springframework.boot.SpringApplication.run(SpringApplication.java:1311) [spring-boot-2.4.2.jar:2.4.2] at org.springframework.boot.SpringApplication.run(SpringApplication.java:1300) [spring-boot-2.4.2.jar:2.4.2] at com.example.accessingdatamysql.AccessingDataMysqlApplication.main(AccessingDataMysqlApplication.java:10) [classes/:na] Caused by: org.hibernate.service.spi.ServiceException: Unable to create requested service [org.hibernate.engine.jdbc.env.spi.JdbcEnvironment] at org.hibernate.service.internal.AbstractServiceRegistryImpl.createService(AbstractServiceRegistryImpl.java:275) ~[hibernate-core-5.4.27.Final.jar:5.4.27.Final] at org.hibernate.service.internal.AbstractServiceRegistryImpl.initializeService(AbstractServiceRegistryImpl.java:237) ~[hibernate-core-5.4.27.Final.jar:5.4.27.Final] at org.hibernate.service.internal.AbstractServiceRegistryImpl.getService(AbstractServiceRegistryImpl.java:214) ~[hibernate-core-5.4.27.Final.jar:5.4.27.Final] at org.hibernate.id.factory.internal.DefaultIdentifierGeneratorFactory.injectServices(DefaultIdentifierGeneratorFactory.java:152) ~[hibernate-core-5.4.27.Final.jar:5.4.27.Final] at org.hibernate.service.internal.AbstractServiceRegistryImpl.injectDependencies(AbstractServiceRegistryImpl.java:286) ~[hibernate-core-5.4.27.Final.jar:5.4.27.Final] at org.hibernate.service.internal.AbstractServiceRegistryImpl.initializeService(AbstractServiceRegistryImpl.java:243) ~[hibernate-core-5.4.27.Final.jar:5.4.27.Final] at org.hibernate.service.internal.AbstractServiceRegistryImpl.getService(AbstractServiceRegistryImpl.java:214) ~[hibernate-core-5.4.27.Final.jar:5.4.27.Final] at org.hibernate.boot.internal.InFlightMetadataCollectorImpl.<init>(InFlightMetadataCollectorImpl.java:176) ~[hibernate-core-5.4.27.Final.jar:5.4.27.Final] at org.hibernate.boot.model.process.spi.MetadataBuildingProcess.complete(MetadataBuildingProcess.java:127) ~[hibernate-core-5.4.27.Final.jar:5.4.27.Final] at org.hibernate.jpa.boot.internal.EntityManagerFactoryBuilderImpl.metadata(EntityManagerFactoryBuilderImpl.java:1224) ~[hibernate-core-5.4.27.Final.jar:5.4.27.Final] at org.hibernate.jpa.boot.internal.EntityManagerFactoryBuilderImpl.build(EntityManagerFactoryBuilderImpl.java:1255) ~[hibernate-core-5.4.27.Final.jar:5.4.27.Final] at org.springframework.orm.jpa.vendor.SpringHibernateJpaPersistenceProvider.createContainerEntityManagerFactory(SpringHibernateJpaPersistenceProvider.java:58) ~[spring-orm-5.3.3.jar:5.3.3] at org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean.createNativeEntityManagerFactory(LocalContainerEntityManagerFactoryBean.java:365) ~[spring-orm-5.3.3.jar:5.3.3] at org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.buildNativeEntityManagerFactory(AbstractEntityManagerFactoryBean.java:409) ~[spring-orm-5.3.3.jar:5.3.3] at org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.afterPropertiesSet(AbstractEntityManagerFactoryBean.java:396) ~[spring-orm-5.3.3.jar:5.3.3] at org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean.afterPropertiesSet(LocalContainerEntityManagerFactoryBean.java:341) ~[spring-orm-5.3.3.jar:5.3.3] at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.invokeInitMethods(AbstractAutowireCapableBeanFactory.java:1847) ~[spring-beans-5.3.3.jar:5.3.3] at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1784) ~[spring-beans-5.3.3.jar:5.3.3] ... 17 common frames omitted Caused by: org.hibernate.HibernateException: Access to DialectResolutionInfo cannot be null when 'hibernate.dialect' not set at org.hibernate.engine.jdbc.dialect.internal.DialectFactoryImpl.determineDialect(DialectFactoryImpl.java:100) ~[hibernate-core-5.4.27.Final.jar:5.4.27.Final] at org.hibernate.engine.jdbc.dialect.internal.DialectFactoryImpl.buildDialect(DialectFactoryImpl.java:54) ~[hibernate-core-5.4.27.Final.jar:5.4.27.Final] at org.hibernate.engine.jdbc.env.internal.JdbcEnvironmentInitiator.initiateService(JdbcEnvironmentInitiator.java:137) ~[hibernate-core-5.4.27.Final.jar:5.4.27.Final] at org.hibernate.engine.jdbc.env.internal.JdbcEnvironmentInitiator.initiateService(JdbcEnvironmentInitiator.java:35) ~[hibernate-core-5.4.27.Final.jar:5.4.27.Final] at org.hibernate.boot.registry.internal.StandardServiceRegistryImpl.initiateService(StandardServiceRegistryImpl.java:101) ~[hibernate-core-5.4.27.Final.jar:5.4.27.Final] at org.hibernate.service.internal.AbstractServiceRegistryImpl.createService(AbstractServiceRegistryImpl.java:263) ~[hibernate-core-5.4.27.Final.jar:5.4.27.Final] ... 34 common frames omitted

From cloud-sql-proxy :

couldn't connect to "test-project:europe-west1:mysql-01": dial tcp 10.121.225.1:3307: connect: connection timed out

Note: 10.121.225.1 is my privateIP as example

More common log where both spring boot app and cloud sql proxy connects:

2021-03-09T17:38:39.728403007Z2021-03-09 17:38:39.727 INFO 1 --- [ main] com.zaxxer.hikari.HikariDataSource : HikariPool-1 - Starting...
Error
2021-03-09T17:38:39.983214948Z2021/03/09 17:38:39 New connection for "test-project:europe-west1:mysql-01"
Info
2021-03-09T17:39:11.091356223Z2021-03-09 17:39:11.090 ERROR 1 --- [ main] com.zaxxer.hikari.pool.HikariPool : HikariPool-1 - Exception during pool initialization.

Steps i did:
Using Terraform:

1. Created VPC

// vpc module

resource "google_compute_network" "vpc" {
  name                    = var.name
  routing_mode            = "GLOBAL"
  auto_create_subnetworks = true
}

# We need to allocate an IP block for private IPs. We want everything in the VPC
# to have a private IP. This improves security and latency, since requests to
# private IPs are routed through Google's network, not the Internet.
resource "google_compute_global_address" "private_ip_sql" {
  name         = "private-ip-sql"
  description  = "A block of private IP addresses that are accessible only from within the VPC."
  purpose      = "VPC_PEERING"
  address_type = "INTERNAL"
  ip_version   = "IPV4"
  # We don't specify a address range because Google will automatically assign one for us.
  prefix_length = 20 # ~4k IPs
  network       = google_compute_network.vpc.self_link
}

# This enables private services access. This makes it possible for instances
# within the VPC and Google services to communicate exclusively using internal
# IP addresses. Details here:
#   https://cloud.google.com/sql/docs/postgres/configure-private-services-access
resource "google_service_networking_connection" "private_vpc_connection" {
  network                 = google_compute_network.vpc.self_link
  service                 = "servicenetworking.googleapis.com"
  reserved_peering_ranges = [google_compute_global_address.private_ip_sql.name]
}

# We'll need this to connect to the Cloud SQL Proxy. this is for VM.
resource "google_compute_firewall" "allow_ssh_sql" {
  name        = "allow-ssh-sql"
  description = "Allow SSH traffic to any instance tagged with 'ssh-enabled'"
  network     = google_compute_network.vpc.name
  direction   = "INGRESS"

  allow {
    protocol = "tcp"
    ports    = ["22"]
  }

  target_tags = ["ssh-enabled"]
}

1. Created MYSQL with private IP

// db module

resource "google_sql_database" "main" {
  name     = "main"
  instance = google_sql_database_instance.main_primary.name
}

resource "google_sql_database_instance" "main_primary" {
  name             = "mysql-01"
  database_version = "MYSQL_8_0"
  depends_on       = [var.db_depends_on]

  settings {
    tier              = var.instance_type
    availability_type = "ZONAL" # use "REGIONAL" for prod to distribute data storage across zones
    disk_size         = var.disk_size

    ip_configuration {
      ipv4_enabled    = false        # don't give the db a public IPv4
      private_network = var.vpc_link # the VPC where the db will be assigned a private IP
    }
  }
}

resource "google_sql_user" "db_user" {
  name     = var.user
  instance = google_sql_database_instance.main_primary.name
  password = var.password
}

Note: root file has a depends_on tag on each other. so trust me it does create mysql with privateIP.

1. Created service account

Using Kubernetes:

1. created cluster
   gcloud container clusters create cloud-sql-java-cluster --region europe-west1 --machine-type=n1-standard-1 --max-nodes=10 --min-nodes=1 --network=cloud-mysql-vpc-001 (which is my VPC name)

1. gcloud container clusters get-credentials cloud-sql-java-cluster --zone europe-west1-d
2. created the keys for service_acc
3. load them into kube secret
4. calling yaml file for deployment
   deployment.yaml:

apiVersion: apps/v1
kind: Deployment
metadata:
  name: kubeclusersql
  labels:
    app: kubeclusersql
spec:
  selector:
    matchLabels:
      app: kubeclusersql
  template:
    metadata:
      labels:
        app: kubeclusersql
    spec:
      # This section describes the containers that make up the deployment
      containers:
        - name: kubeclusersql
          # CHANGE [PROJECT_ID] to the project where your Cloud SQL instance lives
          image: gcr.io/test-project/cloud-sql-java:v1
        # Change <INSTANCE_CONNECTION_NAME> here to include your GCP
        # project, the region of your Cloud SQL instance and the name
        # of your Cloud SQL instance. The format is $PROJECT:$REGION:$INSTANCE
        # Note too, that the tcp port will be different if you're not doing
        # MySQL. If you're using Postgres for example, it should be 5432
        - name: cloudsql-proxy
          image: gcr.io/cloudsql-docker/gce-proxy:1.16
          command: ["/cloud_sql_proxy",
                    "-instances=test-project:europe-west1:mysql-01=tcp:127.0.0.1:3306",
                    "-credential_file=/secrets/cloudsql/mysql_credentials.json",
                    "-ip_address_types=PRIVATE"]
          volumeMounts:
            - name: my-secrets-volume
              mountPath: /secrets/cloudsql
              readOnly: true
      volumes:
        - name: my-secrets-volume
          secret:
            secretName: sql-sec-name

Testing:

1. i am able to connect to MYSQL using privateIP with cloud-sql-proxy from SSH via VM which is created using same private network and even local laptop i can connect successfully. But not from spring boot application cluster and pod.
2. Did i made any mistake in creating kubernetes clusters?
3. Have tested same spring boot app from local machine connected using Bastion VM and SSH and port forward and using cloudsql. But from kubernetes its not working.

Question:

1. VM + MY SQL Private IP with same network connecting
2. MY SQL Private IP + CloudSQL proxy + Spring boot with same network not connecting why?

Bonus log from cloud sql proxy: Error log from cluster
2021-03-09T13:51:18.929607560Z2021/03/09 13:51:18 Listening on 127.0.0.1:3306 for test-project:europe-west1:mysql-01

Spring Boot application.properties: MYSQL has already db: db_example & user : springuser with password: password

spring.jpa.hibernate.ddl-auto=update
spring.datasource.url=jdbc:mysql://127.0.0.1:3306/db_example
spring.datasource.username=springuser
spring.datasource.password=password

using https://spring.io/guides/gs/accessing-data-mysql/ as sample spring boot application to connect from.

[shubha-rajan]

Hi @satscreate, thanks for opening this issue.

From the information you provided, I suspect that the issue most likely has to do with your Kubernetes configuration.

First, it looks like you're using gce-proxy:1.16 which is a very outdated version of the proxy image. There have been a lot of bugfixes implemented since then, so we recommend updating to the latest version, v1.20.2.

Second, from comparing your YAML file to our example kubernetes configuration file, it looks like your configuration file is missing the following section:

        securityContext:
          # The default Cloud SQL proxy image runs as the
          # "nonroot" user and group (uid: 65532) by default.
          runAsNonRoot: true

The default nonuser and group were added in v1.17: https://github.com/GoogleCloudPlatform/cloudsql-proxy/pull/402

So to start, I suggest changing the version for the proxy image and adding the securityContext section and see if that resolves it.

[shubha-rajan]

Update: actually it looks like your application is connecting to the proxy, but the proxy itself isn't connecting. Can you verify that the Cloud SQL instance and your application are in the same VPC?

[enocom]

Like @shubha-rajan said, given that the proxy is starting and connecting to an instance, I'm inclined to say it's a VPC configuration issue. But you might try the suggestions above and report back.

[satscreate]

Hi @satscreate, thanks for opening this issue.

From the information you provided, I suspect that the issue most likely has to do with your Kubernetes configuration.

First, it looks like you're using gce-proxy:1.16 which is a very outdated version of the proxy image. There have been a lot of bugfixes implemented since then, so we recommend updating to the latest version, v1.20.2.

Second, from comparing your YAML file to our example kubernetes configuration file, it looks like your configuration file is missing the following section:

        securityContext:
          # The default Cloud SQL proxy image runs as the
          # "nonroot" user and group (uid: 65532) by default.
          runAsNonRoot: true

The default nonuser and group were added in v1.17: #402

So to start, I suggest changing the version for the proxy image and adding the securityContext section and see if that resolves it.

Hi @shubha-rajan , as you suggested have added the securitycontext in my yaml file and reran the setup. But not fixed anything. also have added latest cloud-sql-proxy as you mentioned.
Here is my log:

2021-03-10T06:41:53.615608293Z2021/03/10 06:41:53 Listening on 127.0.0.1:3306 for test-project:europe-west1:mysql-01
Error
2021-03-10T06:41:53.616844083Z2021/03/10 06:41:53 Ready for new connections
Error
2021-03-10T06:41:58.576834481Z2021/03/10 06:41:58 New connection for "test-project:europe-west1:mysql-01"
Error
2021-03-10T06:42:37.719645567Z2021/03/10 06:42:37 New connection for "test-project:europe-west1:mysql-01"
Error
2021-03-10T06:43:28.041104463Z2021/03/10 06:43:28 New connection for "test-project:europe-west1:mysql-01"
Error
2021-03-10T06:44:10.456210499Z2021/03/10 06:44:10 couldn't connect to "test-project:europe-west1:mysql-01": dial tcp 10.121.225.1:3307: connect: connection timed out

Config:

apiVersion: apps/v1
kind: Deployment
metadata:
  name: kubeclusersql
  labels:
    app: kubeclusersql
spec:
  selector:
    matchLabels:
      app: kubeclusersql
  template:
    metadata:
      labels:
        app: kubeclusersql
    spec:
      # This section describes the containers that make up the deployment
      containers:
        - name: kubeclusersql
          # CHANGE [PROJECT_ID] to the project where your Cloud SQL instance lives
          image: gcr.io/test-project/cloud-sql-java:v1
        # Change <INSTANCE_CONNECTION_NAME> here to include your GCP
        # project, the region of your Cloud SQL instance and the name
        # of your Cloud SQL instance. The format is $PROJECT:$REGION:$INSTANCE
        # Note too, that the tcp port will be different if you're not doing
        # MySQL. If you're using Postgres for example, it should be 5432
        - name: cloudsql-proxy
          image: gcr.io/cloudsql-docker/gce-proxy:1.16
          command:
            - "/cloud_sql_proxy"
            - "-ip_address_types=PRIVATE"
            - "-instances=test-project:europe-west1:mysql-01=tcp:127.0.0.1:3306"
            - "-credential_file=/secrets/cloudsql/mysql_credentials.json"
          securityContext:
            runAsNonRoot: true
          volumeMounts:
            - name: my-secrets-volume
              mountPath: /secrets/cloudsql
              readOnly: true
      volumes:
        - name: my-secrets-volume
          secret:
            secretName: sql-sec-name

[satscreate]

Update: actually it looks like your application is connecting to the proxy, but the proxy itself isn't connecting. Can you verify that the Cloud SQL instance and your application are in the same VPC?

Yes. As you can see my deployment yaml file has only one container and two names,

containers:
        - **name: kubeclusersql**
          # CHANGE [PROJECT_ID] to the project where your Cloud SQL instance lives
          image: gcr.io/test-project/cloud-sql-java:v1
        # Change <INSTANCE_CONNECTION_NAME> here to include your GCP
        # project, the region of your Cloud SQL instance and the name
        # of your Cloud SQL instance. The format is $PROJECT:$REGION:$INSTANCE
        # Note too, that the tcp port will be different if you're not doing
        # MySQL. If you're using Postgres for example, it should be 5432
        - **name: cloudsql-proxy**
          image: gcr.io/cloudsql-docker/gce-proxy:1.16
          command:
            - "/cloud_sql_proxy"
            - "-ip_address_types=PRIVATE"
            - "-instances=test-project:europe-west1:mysql-01=tcp:127.0.0.1:3306"
            - "-credential_file=/secrets/cloudsql/mysql_credentials.json"
          securityContext:
            runAsNonRoot: true
          volumeMounts:
            - name: my-secrets-volume
              mountPath: /secrets/cloudsql
              readOnly: true
      volumes:
        - name: my-secrets-volume
          secret:
            secretName: sql-sec-name

Before deploying the above yaml iam creating cluster using below command with VPC network. which i used to create MYSQL private IP too.
gcloud container clusters create cloud-sql-java-cluster --region europe-west1 --machine-type=n1-standard-1 --max-nodes=10 --min-nodes=1 --network=cloud-mysql-vpc-001 (which is my VPC name)

Also looking under link
https://console.cloud.google.com/kubernetes/clusters/details/europe-west1/cloud-sql-java-cluster/details?project=test-project
Under Networking i can see the same network being selected,

Private cluster -  Disabled 
Network - cloud-mysql-vpc-001 
Subnet - cloud-mysql-vpc-001 
VPC-native traffic routing - Disabled 

So it does confirm both MySQL & GKE Cluster are under same VPC network.

[satscreate]

One more observation.. from the logs too, @shubha-rajan @enocom

2021-03-10T06:41:53.615608293Z2021/03/10 06:41:53 Listening on 127.0.0.1:3306 for test-project:europe-west1:mysql-01
2021-03-10T06:41:53.616844083Z2021/03/10 06:41:53 Ready for new connections

But when application try to connect to sql-proxy then, i see, 3307?
021-03-10T06:44:10.456210499Z2021/03/10 06:44:10 couldn't connect to "test-project:europe-west1:mysql-01": dial tcp 10.121.225.1:3307: connect: connection timed out

[kurtisvg]

@satscreate This is WAI, the proxy uses port 3307 to connect to the Cloud SQL proxy. You can see more info in our "About the Cloud SQL proxy" page. .

The root cause of the issues connection timed out essentially means that for some reason, the Cloud SQL proxy is unable to reach your instance. Judging by the fact that VPC-native traffic routing - Disabled, that means you aren't using a VPC native cluster, which is a requirement to connect with private IP.

I'd suggest making a VPC native cluster instead and see if that helps.

[satscreate]

@satscreate This is WAI, the proxy uses port 3307 to connect to the Cloud SQL proxy. You can see more info in our "About the Cloud SQL proxy" page. .

The root cause of the issues connection timed out essentially means that for some reason, the Cloud SQL proxy is unable to reach your instance. Judging by the fact that VPC-native traffic routing - Disabled, that means you aren't using a VPC native cluster, which is a requirement to connect with private IP.

I'd suggest making a VPC native cluster instead and see if that helps.

Wow... That really helps. So we should comply with set of instructions to use Cloud SQL Proxy from or inside GKE to connect to if we have privateIP'd Database instances. Thanks a lot for pointing out the configurations. Perhaps a detailed CloudSQL Proxy requirement state could help page also would help a lot. Thanks a lot @kurtisvg & @shubha-rajan @enocom

Solution:
gcloud container clusters create cloud-sql-java-cluster --region europe-west1 --machine-type=n1-standard-1 --max-nodes=10 --min-nodes=1 --network=cloud-mysql-vpc-001 --enable-ip-alias --subnetwork=cloud-mysql-vpc-001

[kurtisvg]

This is a requirement for connecting via private IP, not just when using the proxy. It's listed here on the "About Private IP" page.

I'm going to go ahead and mark this as closed since it seems like we've figured it out.