Connecting Spring Boot running on gcloud VM to cloud SQL instance

Question

im trying to connect spring boot running on google cloud vm to cloud mysql using

<dependency>
            <groupId>org.springframework.cloud</groupId>
            <artifactId>spring-cloud-gcp-starter-sql-mysql</artifactId>
        </dependency>

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

this is the end of the stacktrace, i dont know hot to copy the whole stacktrace through SSH in browser

at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:557) ~[spring-beans-5.2.3.RELEASE.jar:5.2.3.RELEASE]
        at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:517) ~[spring-beans-5.2.3.RELEASE.jar:5.2.3.RELEASE]
        at org.springframework.beans.factory.support.AbstractBeanFactory.lambda$doGetBean$0(AbstractBeanFactory.java:323) ~[spring-beans-5.2.3.RELEASE.jar:5.2.3.RELEASE]
        at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:222) ~[spring-beans-5.2.3.RELEASE.jar:5.2.3.RELEASE]
        at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:321) ~[spring-beans-5.2.3.RELEASE.jar:5.2.3.RELEASE]
        at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:202) ~[spring-beans-5.2.3.RELEASE.jar:5.2.3.RELEASE]
        at org.springframework.beans.factory.config.DependencyDescriptor.resolveCandidate(DependencyDescriptor.java:276) ~[spring-beans-5.2.3.RELEASE.jar:5.2.3.RELEASE]
        at org.springframework.beans.factory.support.DefaultListableBeanFactory.doResolveDependency(DefaultListableBeanFactory.java:1287) ~[spring-beans-5.2.3.RELEASE.jar:5.2.3.RELEASE]
        at org.springframework.beans.factory.support.DefaultListableBeanFactory.resolveDependency(DefaultListableBeanFactory.java:1207) ~[spring-beans-5.2.3.RELEASE.jar:5.2.3.RELEASE]
        at org.springframework.beans.factory.support.ConstructorResolver.resolveAutowiredArgument(ConstructorResolver.java:885) ~[spring-beans-5.2.3.RELEASE.jar:5.2.3.RELEASE]
        at org.springframework.beans.factory.support.ConstructorResolver.createArgumentArray(ConstructorResolver.java:789) ~[spring-beans-5.2.3.RELEASE.jar:5.2.3.RELEASE]
        ... 47 common frames omitted
Caused by: org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'defaultMySqlJdbcInfoProvider' defined in class path resource [org/springframework/cloud/gcp/autoconfigure/sql/GcpCloudSqlAutoConfiguration$MySqlJdbcInfoProviderConfiguration.class]: Bean instantiation via factory method failed; nested exception is org.springframework.beans.BeanInstantiationException: Failed to instantiate [org.springframework.cloud.gcp.autoconfigure.sql.CloudSqlJdbcInfoProvider]: Factory method 'defaultMySqlJdbcInfoProvider' threw exception; nested exception is java.lang.IllegalArgumentException: A database name must be provided.
        at org.springframework.beans.factory.support.ConstructorResolver.instantiate(ConstructorResolver.java:656) ~[spring-beans-5.2.3.RELEASE.jar:5.2.3.RELEASE]
        at org.springframework.beans.factory.support.ConstructorResolver.instantiateUsingFactoryMethod(ConstructorResolver.java:636) ~[spring-beans-5.2.3.RELEASE.jar:5.2.3.RELEASE]
        at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.instantiateUsingFactoryMethod(AbstractAutowireCapableBeanFactory.java:1338) ~[spring-beans-5.2.3.RELEASE.jar:5.2.3.RELEASE]
        at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBeanInstance(AbstractAutowireCapableBeanFactory.java:1177) ~[spring-beans-5.2.3.RELEASE.jar:5.2.3.RELEASE]
        at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:557) ~[spring-beans-5.2.3.RELEASE.jar:5.2.3.RELEASE]
        at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:517) ~[spring-beans-5.2.3.RELEASE.jar:5.2.3.RELEASE]
        at org.springframework.beans.factory.support.AbstractBeanFactory.lambda$doGetBean$0(AbstractBeanFactory.java:323) ~[spring-beans-5.2.3.RELEASE.jar:5.2.3.RELEASE]
        at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:222) ~[spring-beans-5.2.3.RELEASE.jar:5.2.3.RELEASE]
        at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:321) ~[spring-beans-5.2.3.RELEASE.jar:5.2.3.RELEASE]
        at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:202) ~[spring-beans-5.2.3.RELEASE.jar:5.2.3.RELEASE]
        at org.springframework.beans.factory.config.DependencyDescriptor.resolveCandidate(DependencyDescriptor.java:276) ~[spring-beans-5.2.3.RELEASE.jar:5.2.3.RELEASE]
        at org.springframework.beans.factory.support.DefaultListableBeanFactory.doResolveDependency(DefaultListableBeanFactory.java:1287) ~[spring-beans-5.2.3.RELEASE.jar:5.2.3.RELEASE]
        at org.springframework.beans.factory.support.DefaultListableBeanFactory.resolveDependency(DefaultListableBeanFactory.java:1207) ~[spring-beans-5.2.3.RELEASE.jar:5.2.3.RELEASE]
        at org.springframework.beans.factory.support.ConstructorResolver.resolveAutowiredArgument(ConstructorResolver.java:885) ~[spring-beans-5.2.3.RELEASE.jar:5.2.3.RELEASE]
        at org.springframework.beans.factory.support.ConstructorResolver.createArgumentArray(ConstructorResolver.java:789) ~[spring-beans-5.2.3.RELEASE.jar:5.2.3.RELEASE]
        ... 61 common frames omitted
Caused by: org.springframework.beans.BeanInstantiationException: Failed to instantiate [org.springframework.cloud.gcp.autoconfigure.sql.CloudSqlJdbcInfoProvider]: Factory method 'defaultMySqlJdbcInfoProvider' threw exception; nested exception is java.lang.IllegalArgumentException: A database name must be provided.
        at org.springframework.beans.factory.support.SimpleInstantiationStrategy.instantiate(SimpleInstantiationStrategy.java:185) ~[spring-beans-5.2.3.RELEASE.jar:5.2.3.RELEASE]
        at org.springframework.beans.factory.support.ConstructorResolver.instantiate(ConstructorResolver.java:651) ~[spring-beans-5.2.3.RELEASE.jar:5.2.3.RELEASE]
        ... 75 common frames omitted
Caused by: java.lang.IllegalArgumentException: A database name must be provided.
        at org.springframework.util.Assert.hasText(Assert.java:284) ~[spring-core-5.2.3.RELEASE.jar:5.2.3.RELEASE]
        at org.springframework.cloud.gcp.autoconfigure.sql.DefaultCloudSqlJdbcInfoProvider.<init>(DefaultCloudSqlJdbcInfoProvider.java:39) ~[spring-cloud-gcp-autoconfigure-1.2.2.RELEASE.jar:1.2.2.RELEASE]
        at org.springframework.cloud.gcp.autoconfigure.sql.GcpCloudSqlAutoConfiguration$MySqlJdbcInfoProviderConfiguration.defaultMySqlJdbcInfoProvider(GcpCloudSqlAutoConfiguration.java:87) ~[spring-cloud-gcp-autoconfigure-1.2.2.RELEASE.jar:1.2.2.RELEASE]
        at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:na]
        at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[na:na]
        at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:na]
        at java.base/java.lang.reflect.Method.invoke(Method.java:566) ~[na:na]
        at org.springframework.beans.factory.support.SimpleInstantiationStrategy.instantiate(SimpleInstantiationStrategy.java:154) ~[spring-beans-5.2.3.RELEASE.jar:5.2.3.RELEASE]
        ... 76 common frames omitted

I've found some articles and other answers about this on StackOverflow, made sure my settings in application.properties are correct but still getting same error. Don't know where to search next.

spring.datasource.driverClassName=com.mysql.jdbc.GoogleDriver
spring.jpa.hibernate.ddl-auto=none
spring.cloud.gcp.sql.database-name=teamplanner
spring.datasource.url=jdbc:mysql://34.107.103.23:3306/teamplanner
spring.datasource.username=teamplanner
spring.datasource.password=xxxxxxxxxx
spring.datasource.initialization-mode=always
server.port = 8080
spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.MySQL5Dialect
spring.cloud.gcp.sql.instance-connection-name=teamplanner-springboot-rest:europe-west3:teamplanner-springboot-rest-mysql

please help

-----------------EDIT Here's the database name, user and connection properties

Dependency versions:

this is the parent dependency for cloud gcp, it defines the version for spring-cloud-gcp-starter-sql-mysql dependency. While developing on my own pc I was connecting to MySql installed on my pc, I didnt have spring-cloud-gcp-dependencies and spring-cloud-gcp-starter-sql-mysql, only mysql-connector-java and everything was working perfectly.

<dependencyManagement>
        <dependencies>
            <dependency>
                <groupId>org.springframework.cloud</groupId>
                <artifactId>spring-cloud-gcp-dependencies</artifactId>
                <version>1.2.2.RELEASE</version>
                <type>pom</type>
                <scope>import</scope>
            </dependency>
        </dependencies>
</dependencyManagement>

and this is for the spring boot starter dependencies

<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>2.2.4.RELEASE</version>

I am able to successfully connect to mysql from my cloud VM SSH with this command mysql --host=34.107.103.23 --user=teamplanner --password then it prompts me for password, i enter xxxxxxxxx and I'm connected.

Answer 1

I saw this same issue using the PostgreSQL spring boot starter, but with slightly newer versions. I am using version 2.0.5 of the spring-cloud-gcp dependencies, and 2.5.5 of spring boot.

Strangely, I think I noticed that the order of the properties in the properties file itself might actually be an issue for the starters. If my properties file looks like this:

spring.datasource.username=postgres
spring.datasource.password=${sm://projects/212050278585/secrets/postgres-user/versions/1}

#name the application
spring.application.name=data-svc

spring.cloud.gcp.sql.database-name=service-db
spring.cloud.gcp.sql.instance-connection-name=gcp-project-331021:us-central1:gcp-poc

Then I receive the error in the stack trace:

java.lang.IllegalArgumentException: A database name must be provided.
    at org.springframework.util.Assert.hasText(Assert.java:289) ~[spring-core-5.3.10.jar:5.3.10]
    at com.google.cloud.spring.autoconfigure.sql.DefaultCloudSqlJdbcInfoProvider.<init>(DefaultCloudSqlJdbcInfoProvider.java:41) ~[spring-cloud-gcp-autoconfigure-2.0.4.jar:2.0.4]
    at com.google.cloud.spring.autoconfigure.sql.CloudSqlEnvironmentPostProcessor.postProcessEnvironment(CloudSqlEnvironmentPostProcessor.java:86) ~[spring-cloud-gcp-autoconfigure-2.0.4.jar:2.0.4]
    at org.springframework.boot.env.EnvironmentPostProcessorApplicationListener.onApplicationEnvironmentPreparedEvent(EnvironmentPostProcessorApplicationListener.java:102) ~[spring-boot-2.5.5.jar:2.5.5]
    at org.springframework.boot.env.EnvironmentPostProcessorApplicationListener.onApplicationEvent(EnvironmentPostProcessorApplicationListener.java:87) ~[spring-boot-2.5.5.jar:2.5.5]
    at org.springframework.context.event.SimpleApplicationEventMulticaster.doInvokeListener(SimpleApplicationEventMulticaster.java:176) ~[spring-context-5.3.10.jar:5.3.10]
    at org.springframework.context.event.SimpleApplicationEventMulticaster.invokeListener(SimpleApplicationEventMulticaster.java:169) ~[spring-context-5.3.10.jar:5.3.10]
    at org.springframework.context.event.SimpleApplicationEventMulticaster.multicastEvent(SimpleApplicationEventMulticaster.java:143) ~[spring-context-5.3.10.jar:5.3.10]
    at org.springframework.context.event.SimpleApplicationEventMulticaster.multicastEvent(SimpleApplicationEventMulticaster.java:131) ~[spring-context-5.3.10.jar:5.3.10]
    at org.springframework.boot.context.event.EventPublishingRunListener.environmentPrepared(EventPublishingRunListener.java:82) ~[spring-boot-2.5.5.jar:2.5.5]
    at org.springframework.boot.SpringApplicationRunListeners.lambda$environmentPrepared$2(SpringApplicationRunListeners.java:63) ~[spring-boot-2.5.5.jar:2.5.5]
    at java.base/java.util.ArrayList.forEach(ArrayList.java:1541) ~[na:na]

But if I re-order the properties to look like this:

spring.cloud.gcp.sql.database-name=service-db
spring.cloud.gcp.sql.instance-connection-name=ascendant-pixel-331021:us-central1:gcp-poc

spring.datasource.username=postgres
spring.datasource.password=${sm://projects/212050278585/secrets/postgres-user/versions/1}

#name the application
spring.application.name=data-svc

The difference being that my spring.cloud.gcp.sql.* properties appear first in the file. I know its weird, but it seems to make a difference for me.

Answer 2

On my PC when I create the project from spring initializr I must have created Resources folder manually with capitalized R. For months I've been running the project from IDE on windows and had no problem. When I moved project to compute engine and started running it with mvn spring-boot:run the application wasn't reading anything from Resources folder because it's not named according to convention as it should be "resources" without capital R. IDE must have done something so that application would read from capitalized folder. Renaming the folder fixed the problem.

Answer 3

The error must be in the application.properties class. Change it like this.

spring.jpa.hibernate.ddl-auto=update
spring.jpa.show-sql=true
spring.datasource.url=jdbc:mysql://34.107.103.23/teamplanner?useSSL=false
spring.datasource.username=teamplanner
spring.datasource.password=**********

This must be work. Here what happens is you use local MySQL server port number in front of the database URL. No need of adding port number as 8080. If it shows error as 8080 is already in use then only add another port number. Otherwise by default it takes port as 8080. Add this dependency to the pom.xml

<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <scope>runtime</scope>
</dependency>