为googlecloudsql示例启用“只允许ssl连接”,并从ssl配置的spring应用程序连接到它

4si2a6ki  于 2021-06-25  发布在  Mysql
关注(0)|答案(1)|浏览(408)

我有一个spring应用程序,它运行在tomcat(7.0.68)上,在我的本地机器上,使用googlecloudmysql示例。我还使用c3p0(0.9.1.2)作为连接池。您可以在daocontext.xml文件和catalina\u选项中找到一部分:

....
<bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource">
        <property name="driverClass" value="com.mysql.jdbc.Driver"/>
        <property name="jdbcUrl" value="jdbc:mysql://sql_instance_ip:3306/table_name?verifyServerCertificate=true&useSSL=true&requireSSL=true&trustCertificateKeyStoreUrl=file:/path/to/trustStore.jks&trustCertificateKeyStorePassword=****&characterEncoding=UTF-8&useUnicode=true&autoReconnect=true&useServerPrepStmts=false&rewriteBatchedStatements=true&failOverReadOnly=false&zeroDateTimeBehavior=convertToNull"/>
        <property name="user" value="***"/>
        <property name="password" value="****"/>
        <property name="acquireIncrement" value="1"/>
        <property name="initialPoolSize" value="1"/>
        <property name="minPoolSize" value="1"/>
        <property name="maxPoolSize" value="5"/>
        <property name="maxIdleTime" value="120"/>
</bean>
...

CATALINA_OPTS="... -Djavax.net.ssl.keyStore=/path/to/keyStore.jks -Djavax.net.ssl.keyStorePassword=****..."

仅供参考,这些密钥存储是从我的sql示例的证书创建的,我的本地ip是这个mysql示例的白名单。https://cloud.google.com/sql/docs/mysql/configure-ssl-instance
当我从 Jmeter 板禁用“只允许ssl连接”选项时,我的应用程序运行时没有任何db连接问题。另外,当我通过wireshark检查应用程序和sql示例之间的连接时,我看到数据包是ssl加密的。
启用此选项时,我的tomcat应用程序会出现以下错误:

May 07, 2018 5:02:49 PM com.mchange.v2.c3p0.impl.AbstractPoolBackedDataSource getPoolManager
INFO: Initializing c3p0 pool... com.mchange.v2.c3p0.ComboPooledDataSource [ acquireIncrement -> 1, acquireRetryAttempts -> 30, acquireRetryDelay -> 1000, autoCommitOnClose -> false, automaticTestTable -> null, breakAfterAcquireFailure -> false, checkoutTimeout -> 0, connectionCustomizerClassName -> null, connectionTesterClassName -> com.mchange.v2.c3p0.impl.DefaultConnectionTester, dataSourceName -> z8kfsx9u1h2ypospw6bfm|37fcf007, debugUnreturnedConnectionStackTraces -> false, description -> null, driverClass -> com.mysql.jdbc.Driver, factoryClassLocation -> null, forceIgnoreUnresolvedTransactions -> false, identityToken -> z8kfsx9u1h2ypospw6bfm|37fcf007, idleConnectionTestPeriod -> 0, initialPoolSize -> 1, jdbcUrl -> jdbc:mysql://sql_instance_ip:3306/table_name?verifyServerCertificate=true&useSSL=true&requireSSL=true&trustCertificateKeyStoreUrl=file:/path/to/trustStore.jks&trustCertificateKeyStorePassword=****&characterEncoding=UTF-8&useUnicode=true&autoReconnect=true&useServerPrepStmts=false&rewriteBatchedStatements=true&failOverReadOnly=false&zeroDateTimeBehavior=convertToNull"/, maxAdministrativeTaskTime -> 0, maxConnectionAge -> 0, maxIdleTime -> 120, maxIdleTimeExcessConnections -> 0, maxPoolSize -> 5, maxStatements -> 0, maxStatementsPerConnection -> 0, minPoolSize -> 1, numHelperThreads -> 3, numThreadsAwaitingCheckoutDefaultUser -> 0, preferredTestQuery -> null, properties -> {user=******, password=******}, propertyCycle -> 0, testConnectionOnCheckin -> false, testConnectionOnCheckout -> false, unreturnedConnectionTimeout -> 0, usesTraditionalReflectiveProxies -> false ]
May 07, 2018 5:03:09 PM com.mchange.v2.async.ThreadPoolAsynchronousRunner$DeadlockDetector run
WARNING: com.mchange.v2.async.ThreadPoolAsynchronousRunner$DeadlockDetector@274517d0 -- APPARENT DEADLOCK!!! Creating emergency threads for unassigned pending tasks!
May 07, 2018 5:03:09 PM com.mchange.v2.async.ThreadPoolAsynchronousRunner$DeadlockDetector run
WARNING: com.mchange.v2.async.ThreadPoolAsynchronousRunner$DeadlockDetector@274517d0 -- APPARENT DEADLOCK!!! Complete Status:
    Managed Threads: 3
    Active Threads: 3
    Active Tasks:
        com.mchange.v2.resourcepool.BasicResourcePool$AcquireTask@68bff773 (com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread-#1)
        com.mchange.v2.resourcepool.BasicResourcePool$AcquireTask@67cb9623 (com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread-#2)
        com.mchange.v2.resourcepool.BasicResourcePool$AcquireTask@599d5f2f (com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread-#0)
    Pending Tasks:
        com.mchange.v2.resourcepool.BasicResourcePool$AcquireTask@1f0360c0
        com.mchange.v2.resourcepool.BasicResourcePool$AcquireTask@890083c
Pool thread stack traces:
    Thread[com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread-#1,5,main]
        java.lang.Thread.sleep(Native Method)
        com.mysql.jdbc.ConnectionImpl.connectWithRetries(ConnectionImpl.java:2376)
        com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2306)
        com.mysql.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:834)
        com.mysql.jdbc.JDBC4Connection.<init>(JDBC4Connection.java:47)
        sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
....

这些错误日志并不特定于此问题,但它们与我的应用程序无法连接数据库时得到的日志类似。你对这个问题有什么想法或建议吗?是否有人使用类似的设置并遇到此问题?
任何形式的帮助和建议都将不胜感激!

ukdjmx9f

ukdjmx9f1#

似乎是配置问题。我建议您再次遵循官方文档中描述的所有步骤,并检查您的服务器证书是否仍然有效。
作为一种解决方法,我建议使用cloudsql代理。

相关问题