Unable to connect Spring boot Mysql to GCP cloud

Question

I created SQL instance in GCP cloud account and it is working fine if i run in local. But it is throwing error after deploying Spring app to GCP.

Error:

    org.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 javax.persistence.PersistenceException: [PersistenceUnit: default] Unable to build Hibernate SessionFactory; nested exception is org.hibernate.exception.JDBCConnectionException: Unable to open JDBC Connection for DDL execution
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean (AbstractAutowireCapableBeanFactory.java:1796)
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean (AbstractAutowireCapableBeanFactory.java:595)
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean (AbstractAutowireCapableBeanFactory.java:517)
at org.springframework.beans.factory.support.AbstractBeanFactory.lambda$doGetBean$0 (AbstractBeanFactory.java:323)
at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton (DefaultSingletonBeanRegistry.java:226)
at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean (AbstractBeanFactory.java:321)
at org.springframework.beans.factory.support.AbstractBeanFactory.getBean (AbstractBeanFactory.java:202)
at org.springframework.context.support.AbstractApplicationContext.getBean (AbstractApplicationContext.java:1108)
at org.springframework.context.support.AbstractApplicationContext.finishBeanFactoryInitialization (AbstractApplicationContext.java:868)
at org.springframework.context.support.AbstractApplicationContext.refresh (AbstractApplicationContext.java:550)
at org.springframework.boot.web.servlet.context.ServletWebServerApplicationContext.refresh (ServletWebServerApplicationContext.java:141)
at org.springframework.boot.SpringApplication.refresh (SpringApplication.java:747)
at org.springframework.boot.SpringApplication.refreshContext (SpringApplication.java:397)
at org.springframework.boot.SpringApplication.run (SpringApplication.java:315)
at org.springframework.boot.SpringApplication.run (SpringApplication.java:1226)
at org.springframework.boot.SpringApplication.run (SpringApplication.java:1215)
at inner.stories.StoriesApplication.main (StoriesApplication.java:10)

application.properties:

spring.datasource.driver-class-name=com.mysql.jdbc.Driver
spring.datasource.url=jdbc:mysql: **private-IP-of-sql-instance**
spring.datasource.username=*****
spring.datasource.password=*****
spring.jpa.properties.hibernate.dialect = org.hibernate.dialect.MySQL5InnoDBDialect
spring.jpa.hibernate.ddl-auto=update

pom.xml:

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

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>


       <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-jpa</artifactId>
        </dependency>


       <dependency>
            <groupId>org.springframework.cloud</groupId>
            <artifactId>spring-cloud-gcp-starter-sql</artifactId>
            <version>1.0.0.BUILD-SNAPSHOT</version>
        </dependency>

UPDATE

I can see different error in the gcloud command logs.My page is loading half and then showing SocketTimeoutException in gcloud command logs.

My Home page after sometime

 Error: Server Error
The server encountered an error and could not complete your request.
Please try again in 30 seconds.

Gcloud command logs:

    The last packet sent successfully to the server was 0 milliseconds ago. The driver has not received any packets from the server.
2020-05-04 21:34:35 default[20200504t172700]    at java.base/jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) ~[na:na]
2020-05-04 21:34:35 default[20200504t172700]    at java.base/jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62) ~[na:na]
2020-05-04 21:34:35 default[20200504t172700]    at java.base/jdk.internal.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) ~[na:na]
2020-05-04 21:34:35 default[20200504t172700]    at java.base/java.lang.reflect.Constructor.newInstance(Constructor.java:490) ~[na:na]
2020-05-04 21:34:35 default[20200504t172700]    at com.mysql.jdbc.Util.handleNewInstance(Util.java:425) ~[mysql-connector-java-5.1.44.jar!/:5.1.44]
2020-05-04 21:34:35 default[20200504t172700]    at com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:989) ~[mysql-connector-java-5.1.44.jar!/:5.1.44]
2020-05-04 21:34:35 default[20200504t172700]    at com.mysql.jdbc.MysqlIO.<init>(MysqlIO.java:341) ~[mysql-connector-java-5.1.44.jar!/:5.1.44]
2020-05-04 21:34:35 default[20200504t172700]    at com.mysql.jdbc.ConnectionImpl.coreConnect(ConnectionImpl.java:2189) ~[mysql-connector-java-5.1.44.jar!/:5.1.44]
2020-05-04 21:34:35 default[20200504t172700]    at com.mysql.jdbc.ConnectionImpl.connectOneTryOnly(ConnectionImpl.java:2222) ~[mysql-connector-java-5.1.44.jar!/:5.1.44]
2020-05-04 21:34:35 default[20200504t172700]    at com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2017) ~[mysql-connector-java-5.1.44.jar!/:5.1.44]
2020-05-04 21:34:35 default[20200504t172700]    at com.mysql.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:779) ~[mysql-connector-java-5.1.44.jar!/:5.1.44]
2020-05-04 21:34:35 default[20200504t172700]    at com.mysql.jdbc.JDBC4Connection.<init>(JDBC4Connection.java:47) ~[mysql-connector-java-5.1.44.jar!/:5.1.44]
2020-05-04 21:34:35 default[20200504t172700]    at java.base/jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) ~[na:na]
2020-05-04 21:34:35 default[20200504t172700]    at java.base/jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62) ~[na:na]
2020-05-04 21:34:35 default[20200504t172700]    at java.base/jdk.internal.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) ~[na:na]
2020-05-04 21:34:35 default[20200504t172700]    at java.base/java.lang.reflect.Constructor.newInstance(Constructor.java:490) ~[na:na]
2020-05-04 21:34:35 default[20200504t172700]    at com.mysql.jdbc.Util.handleNewInstance(Util.java:425) ~[mysql-connector-java-5.1.44.jar!/:5.1.44]
2020-05-04 21:34:35 default[20200504t172700]    at com.mysql.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:389) ~[mysql-connector-java-5.1.44.jar!/:5.1.44]
2020-05-04 21:34:35 default[20200504t172700]    at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:330) ~[mysql-connector-java-5.1.44.jar!/:5.1.44]
2020-05-04 21:34:35 default[20200504t172700]    at com.zaxxer.hikari.util.DriverDataSource.getConnection(DriverDataSource.java:138) ~[HikariCP-3.4.3.jar!/:na]
2020-05-04 21:34:35 default[20200504t172700]    at com.zaxxer.hikari.pool.PoolBase.newConnection(PoolBase.java:358) ~[HikariCP-3.4.3.jar!/:na]
2020-05-04 21:34:35 default[20200504t172700]    at com.zaxxer.hikari.pool.PoolBase.newPoolEntry(PoolBase.java:206) ~[HikariCP-3.4.3.jar!/:na]
2020-05-04 21:34:35 default[20200504t172700]    at com.zaxxer.hikari.pool.HikariPool.createPoolEntry(HikariPool.java:477) ~[HikariCP-3.4.3.jar!/:na]
2020-05-04 21:34:35 default[20200504t172700]    at com.zaxxer.hikari.pool.HikariPool.checkFailFast(HikariPool.java:560) ~[HikariCP-3.4.3.jar!/:na]
2020-05-04 21:34:35 default[20200504t172700]    at com.zaxxer.hikari.pool.HikariPool.<init>(HikariPool.java:115) ~[HikariCP-3.4.3.jar!/:na]
2020-05-04 21:34:35 default[20200504t172700]    at com.zaxxer.hikari.HikariDataSource.getConnection(HikariDataSource.java:112) ~[HikariCP-3.4.3.jar!/:na]
2020-05-04 21:34:35 default[20200504t172700]    at org.hibernate.engine.jdbc.connections.internal.DatasourceConnectionProviderImpl.getConnection(DatasourceConnectionProviderImpl.java:122) ~[hibernate-core-5.4.14.Final.jar!/:5.4.14.Final]
2020-05-04 21:34:35 default[20200504t172700]    at org.hibernate.engine.jdbc.env.internal.JdbcEnvironmentInitiator$ConnectionProviderJdbcConnectionAccess.obtainConnection(JdbcEnvironmentInitiator.java:180) ~[hibernate-core-5.4.14.Final.jar!/:5.4.14.Final]
2020-05-04 21:34:35 default[20200504t172700]    at org.hibernate.resource.transaction.backend.jdbc.internal.DdlTransactionIsolatorNonJtaImpl.getIsolatedConnection(DdlTransactionIsolatorNonJtaImpl.java:43) ~[hibernate-core-5.4.14.Final.jar!/:5.4.14.Final]
2020-05-04 21:34:35 default[20200504t172700]    ... 43 common frames omitted
2020-05-04 21:34:35 default[20200504t172700]  Caused by: java.net.SocketTimeoutException: connect timed out
2020-05-04 21:34:35 default[20200504t172700]    at java.base/java.net.PlainSocketImpl.socketConnect(Native Method) ~[na:na]
2020-05-04 21:34:35 default[20200504t172700]    at java.base/java.net.AbstractPlainSocketImpl.doConnect(AbstractPlainSocketImpl.java:399) ~[na:na]
2020-05-04 21:34:35 default[20200504t172700]    at java.base/java.net.AbstractPlainSocketImpl.connectToAddress(AbstractPlainSocketImpl.java:242) ~[na:na]
2020-05-04 21:34:35 default[20200504t172700]    at java.base/java.net.AbstractPlainSocketImpl.connect(AbstractPlainSocketImpl.java:224) ~[na:na]
2020-05-04 21:34:35 default[20200504t172700]    at java.base/java.net.SocksSocketImpl.connect(SocksSocketImpl.java:403) ~[na:na]
2020-05-04 21:34:35 default[20200504t172700]    at java.base/java.net.Socket.connect(Socket.java:609) ~[na:na]
2020-05-04 21:34:35 default[20200504t172700]    at com.mysql.jdbc.StandardSocketFactory.connect(StandardSocketFactory.java:211) ~[mysql-connector-java-5.1.44.jar!/:5.1.44]
2020-05-04 21:34:35 default[20200504t172700]    at com.mysql.jdbc.MysqlIO.<init>(MysqlIO.java:300) ~[mysql-connector-java-5.1.44.jar!/:5.1.44]
2020-05-04 21:34:35 default[20200504t172700]    ... 65 common frames omitted

Any help is appreciated.

I would like to point that under `application.properties` you include sensitive information such as the Private IP, username and password of the instance. Please keep in mind that you need to sanitize these data and make them available to the public. I edited the post and flagged too, so moderators take care of this and protect your private data. — tzovourn, May 06 '20 at 10:50
Thank you @tzovourn. The information under application.properties are not real, I have posted the modified credentials. I will keep in mind to post as above format while doing next time. — StackOverflow Asker, May 07 '20 at 22:15

score 0 · Answer 1 · answered May 04 '20 at 05:42

Your issue could be located on the properties file:

spring.datasource.url=jdbc:mysql://google/myBlogcloudSqlInstance=reviewcontainerblog&socketFactory=com.google.cloud.sql.mysql.SocketFactory

You can check Google's community tutorial and at Google Cloud Platform's Github page on "Creating the JDBC URL" :

spring.datasource.url=jdbc:mysql://google/petclinic?cloudSqlInstance=INSTANCE_CONNECTION_NAME&socketFactory=com.google.cloud.sql.mysql.SocketFactory

jdbc:mysql://google/<DATABASE_NAME>?cloudSqlInstance=<INSTANCE_CONNECTION_NAME>&socketFactory=com.google.cloud.sql.mysql.SocketFactory&useSSL=false&user=<MYSQL_USER_NAME>&password=<MYSQL_USER_PASSWORD>

So, it is the Instance Connection Name and NOT the instance name. You can also check the following documentation from Google Community tutorials which may help you fast.

You can also follow this external article where it explains how to connect to CloudSQL from your Spring Application in different ways.

Please let me know if it works for you.

Thank you @Nibrass H for the answer. I tried changing the spring.datasource.url like you mentioned and also tried the steps from the google document but its still not working after uploading it to GCP. Gcloud command logs showing SocketTimeoutException. — StackOverflow Asker, May 04 '20 at 21:50

score 0 · Answer 2 · answered May 06 '20 at 11:09

As I understand from your application properties you are trying to access your instance through its Pivate IP. As per documentation:

If you want to use a private IP address, see Configuring Serverless VPC Access and connect directly using the private IP.

Please follow the steps described in documentation to set up Serverless VPC Access and then the steps mentioned in Configuring private IP in order to connect to your instance through its Private IP.

Hope that helps.

I will try. Thank you. – StackOverflow Asker May 07 '20 at 22:16 — StackOverflow Asker, May 07 '20 at 22:16

Unable to connect Spring boot Mysql to GCP cloud

2 Answers2