谷歌云平台 – Spring Boot应用与Cloud SQL上的MySQL连接问题

huangapple go评论83阅读模式
英文:

Google Cloud Platform - Spring Boot application connection issues with MySQL on Cloud SQL

问题

因此,我有一个最小的Spring Boot应用程序,应该连接到托管在GCP Cloud SQL上的MySQL数据库。

当我在本地在Google App Engine上运行我的应用程序时,它成功地连接到托管在GCP上的远程MySQL数据库。但是,当我在GAE标准环境上部署完全相同的应用程序时,该应用程序(显然已成功部署在GCP上)无法连接到MySQL数据库。

这正是我的application.properties文件的内容:

谷歌云平台 – Spring Boot应用与Cloud SQL上的MySQL连接问题

在GCP日志部分查看时,我看到了以下关键错误:

从servlet捕获的未捕获异常\njava.lang.RuntimeException:
org.springframework.beans.factory.BeanCreationException: 在com.winery.backend.services.UserRepository中定义的名称为'userRepository'的bean在@EnableJpaRepositories上声明的定义,
EnableJpaRepositoriesConfiguration: 在设置bean属性'mappingContext'时无法解析对bean 'jpaMappingContext'的引用;嵌套异常是
org.springframework.beans.factory.BeanCreationException: 创建bean时出错,名称为'jpaMappingContext'的bean的名称为

等等,持续了数千行。userRepository恰好是我自定义的带有@Repository注释的接口。再次强调,从本地到云端都能正常工作。

我看到有一些类似的讨论,但是它们都没有提供实际的解决方案。

英文:

So, I have a minimal Spring Boot Application which is supposed to connect to a MySQL database on GCP-Cloud SQL.

When I run my application on Google App Engine locally, it manages to connect to the remote MySQL database hosted on GCP. When I deploy the very same application on GAE standard, the application (apparently deployed successfully on GCP) doesn't manage to connect to the MySQL database.

This is exactly what my application.properties file looks like:

谷歌云平台 – Spring Boot应用与Cloud SQL上的MySQL连接问题

Giving a look in the GCP logging section I see the following critical error:

Uncaught exception from servlet\njava.lang.RuntimeException:
org.springframework.beans.factory.BeanCreationException: Error creating bean 
with name 'userRepository' defined in com.winery.backend.services.UserRepository 
defined in @EnableJpaRepositories declared onJpaRepositoriesRegistrar.
EnableJpaRepositoriesConfiguration: Cannot resolve reference to bean 'jpaMappingContext' 
while setting bean property 'mappingContext'; nested exception is
org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'jpaMappingContext':

and so on for a few thousand lines. userRepository happens to be my custom @Repository annotated interface.
Again, it works like a charm from local to cloud.

I've seen there are a few similar threads, but none of them actually provided a working solution.

答案1

得分: 1

当您尝试使用Cloud SQL的公共IP地址进行连接时,您需要确定应用程序的公共IP,并将其添加到授权列表,以便连接到您的实例。这不适用于App Engine,因为它不提供静态IP地址。

最佳实践是使用Cloud SQL连接器,并将以下依赖项添加到您的pom.xml文件中:

<dependency>
    <groupId>com.google.cloud.sql</groupId>
    <artifactId>mysql-socket-factory-connector-j-8</artifactId>
    <version>1.1.0</version>
</dependency>

然后,根据这样的格式创建JDBC URL:

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

在GCP控制台的Cloud SQL中查看实例连接名称。格式应为“project-id:region:instance-name”。

您还可以通过创建连接池来改进此过程:

private static final String CLOUD_SQL_CONNECTION_NAME = System.getenv("CLOUD_SQL_CONNECTION_NAME");
private static final String DB_USER = System.getenv("DB_USER");
private static final String DB_PASS = System.getenv("DB_PASS");
private static final String DB_NAME = System.getenv("DB_NAME");

private DataSource createConnectionPool() {
    HikariConfig config = new HikariConfig();

    config.setJdbcUrl(String.format("jdbc:mysql:///%s", DB_NAME));
    config.setUsername(DB_USER); // 例如:"root"、"postgres"
    config.setPassword(DB_PASS); // 例如:"my-password"
    config.addDataSourceProperty("socketFactory", "com.google.cloud.sql.mysql.SocketFactory");
    config.addDataSourceProperty("cloudSqlInstance", CLOUD_SQL_CONNECTION_NAME);
    DataSource pool = new HikariDataSource(config);
    return pool;
}

要查看完整内容,请参阅此指南

英文:

When you are trying to connect using Cloud SQL's public IP address, you need to determine the public IP of your application and authorize it to connect to your instance. This won't apply for App Engine since it doesn't provide static IP address.

It's best practice for you use a Cloud SQL connector and include this dependency on your pom.xml:

&lt;dependency&gt;
    &lt;groupId&gt;com.google.cloud.sql&lt;/groupId&gt;
    &lt;artifactId&gt;mysql-socket-factory-connector-j-8&lt;/artifactId&gt;
    &lt;version&gt;1.1.0&lt;/version&gt;
&lt;/dependency&gt;

Then create a JDBC URL like this:

jdbc:mysql:///&lt;DATABASE_NAME&gt;?cloudSqlInstance=&lt;INSTANCE_CONNECTION_NAME&gt;&amp;socketFactory=com.google.cloud.sql.mysql.SocketFactory&amp;user=&lt;MYSQL_USER_NAME&gt;&amp;password=&lt;MYSQL_USER_PASSWORD&gt;

> See the instance connection name on Cloud SQL in GCP console. The format should be "project-id:region:instance-name".

You can also improve this by creating a connection pool:


private static final String CLOUD_SQL_CONNECTION_NAME = System.getenv(&quot;CLOUD_SQL_CONNECTION_NAME&quot;);
private static final String DB_USER = System.getenv(&quot;DB_USER&quot;);
private static final String DB_PASS = System.getenv(&quot;DB_PASS&quot;);
private static final String DB_NAME = System.getenv(&quot;DB_NAME&quot;);

private DataSource createConnectionPool() {
    HikariConfig config = new HikariConfig();

    config.setJdbcUrl(String.format(&quot;jdbc:mysql:///%s&quot;, DB_NAME));
    config.setUsername(DB_USER); // e.g. &quot;root&quot;, &quot;postgres&quot;
    config.setPassword(DB_PASS); // e.g. &quot;my-password&quot;
    config.addDataSourceProperty(&quot;socketFactory&quot;, &quot;com.google.cloud.sql.mysql.SocketFactory&quot;);
    config.addDataSourceProperty(&quot;cloudSqlInstance&quot;, CLOUD_SQL_CONNECTION_NAME);
    DataSource pool = new HikariDataSource(config);
    return pool;
  }

To see the full context, see this guide.

答案2

得分: 0

也许是范围的问题,如果数据库和应用程序都在同一主机上,可能无法通过https连接,因为无法解析主机。

它们是否在同一主机/公共IP上?

如果您正在使用Docker:

  • 尝试使用替代配置文件application-gcp.yml()来替换公共IP为Docker服务名称(并确保创建桥接网络类型)

如果没有使用Docker:

  • 尝试使用localhost代替公共IP。
英文:

Maybe it's the scope, if both (database and app) it's in the same host, maybe cant connect via https because cant resolve the host.

is it both in the same host/public IP?

if you are using docker:

  • Try to use an alternative profile application-gcp.yml () a replace public_ip for docker service name (and be sure to create a network type bridge)

if you not:

  • Try to use localhost instead of public IP

huangapple
  • 本文由 发表于 2020年10月27日 04:01:55
  • 转载请务必保留本文链接:https://go.coder-hub.com/64544191.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定