Grails and Google app engine deployment errors

Question

I have Grails 3.3.11 application, and I'm trying to deploy it on flexible Google App Engine, however, I have some errors mainly produced from connecting to Google cloud SQL, I made MySQL 5.7 instance on it and able to access it from my IDE and while I run the application locally,I searched for some solutions for theses errors but unfortunately, nothing worked for me, here are the exceptions

org.springframework.beans.factory.UnsatisfiedDependencyException: Error creating bean with name 'methodValidationPostProcessor' defined in class path resource [org/springframework/boot/autoconfigure/validation/ValidationAutoConfiguration.class]: Unsatisfied dependency expressed through method 'methodValidationPostProcessor' parameter 0; nested exception is org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'hibernateDatastoreServiceRegistry': Cannot resolve reference to bean 'hibernateDatastore' while setting constructor argument; nested exception is org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'hibernateDatastore': Bean instantiation via constructor failed; nested exception is org.springframework.beans.BeanInstantiationException: Failed to instantiate [org.grails.orm.hibernate.HibernateDatastore]: Constructor threw exception; nested exception is org.hibernate.exception.JDBCConnectionException: Unable to check JDBC Connection auto-commit in preparation for DDL execution
    at org.springframework.beans.factory.support.ConstructorResolver.createArgumentArray(ConstructorResolver.java:749)
    at org.springframework.beans.factory.support.ConstructorResolver.instantiateUsingFactoryMethod(ConstructorResolver.java:467)
    at 

org.hibernate.resource.transaction.backend.jdbc.internal.DdlTransactionIsolatorNonJtaImpl.getIsolatedConnection(DdlTransactionIsolatorNonJtaImpl.java:46)
"

The last packet sent successfully to the server was 0 milliseconds ago. The driver has not received any packets from the server
com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure
Caused by: java.net.ConnectException: Connection timed out (Connection timed out)
    at java.net.PlainSocketImpl.socketConnect(Native Method)
    at java.net.AbstractPlainSocketImpl.doConnect(AbstractPlainSocketImpl.java:350)
    at java.net.AbstractPlainSocketImpl.connectToAddress(AbstractPlainSocketImpl.java:206)
    at java.net.AbstractPlainSocketImpl.connect(AbstractPlainSocketImpl.java:188)
    at java.net.SocksSocketImpl.connect(SocksSocketImpl.java:392)
    at java.net.Socket.connect(Socket.java:589)
    at com.mysql.jdbc.StandardSocketFactory.connect(StandardSocketFactory.java:211)
    at com.mysql.jdbc.MysqlIO.<init>(MysqlIO.java:299)     

here are the production configurations in application.yml

dataSource:
      
        dbCreate: create-drop
        driverClassName: com.mysql.jdbc.Driver 
        dialect: org.hibernate.dialect.MySQL5Dialect
        username: myUsername
        password: myPassword
        url: jdbc:mysql://DBIP:3306/CusOrderIns 

        properties:
            jmxEnabled: true
            initialSize: 5
            maxActive: 50
            minIdle: 5
            maxIdle: 25
            maxWait: 10000
            maxAge: 600000
            timeBetweenEvictionRunsMillis: 5000
            minEvictableIdleTimeMillis: 60000
            validationQuery: SELECT 1
            validationQueryTimeout: 3
            validationInterval: 15000
            testOnBorrow: true
            testWhileIdle: true
            testOnReturn: false
            jdbcInterceptors: ConnectionState
            defaultTransactionIsolation: 2 

build.gradle

buildscript {
repositories {
    mavenLocal()
    maven { url "https://repo.grails.org/grails/core" }
}
dependencies {
    classpath "org.grails:grails-gradle-plugin:$grailsVersion"
    classpath "com.bertramlabs.plugins:asset-pipeline-gradle:2.15.1"
    classpath "org.grails.plugins:hibernate5:${gormVersion - ".RELEASE"}"
    classpath 'com.google.cloud.tools:appengine-gradle-plugin:1.3.2'
 }
}

provided "org.springframework.boot:spring-boot-starter-jetty" 
 // provided  "org.springframework.boot:spring-boot-starter-tomcat"  //commented

App engine config in src\main\appengine\app.yaml

runtime: java
env: flex

runtime_config:
  jdk: openjdk8
  server: jetty9

resources:
  cpu: 2
  memory_gb: 5
  disk_size_gb: 10

manual_scaling:
  instances: 1

App engine service account roles in Google IAM:

Cloud SQL Admin
Cloud SQL Client
Cloud SQL Editor
Cloud SQL Instance User
Editor
Cloud Run Admin

Anything missed or not configured probably?

Answer 1

I'm following this Google Cloud Community documentation about Deploy a Grails app to App Engine flexible environment. If you are still unable to connect using IP address of Cloud SQL, try using the Instance Connection Name of your Cloud SQL instance in url of dataSource and additional MYSQL dependencies in build.gradle:

application.yml

production:
    dataSource:
        dbCreate: update
        dialect: org.hibernate.dialect.MySQL5InnoDBDialect
        driverClassName: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://google/[DATABASE_NAME]?socketFactory=com.google.cloud.sql.mysql.SocketFactory&cloudSqlInstance=[INSTANCE_NAME]&useSSL=true
        username: root
        password: password

• For [DATABASE_NAME], use the database name you used when you created the database.
• For [INSTANCE_NAME], use your instance name, which is visible in your Cloud SQL instance details.

Added in build.gradle:

runtime 'mysql:mysql-connector-java:8.0.16'
runtime 'com.google.cloud.sql:mysql-socket-factory-connector-j-8:1.0.14'