MySQL 和 Spring MVC App 之间的连接失败

Question

我真的试图找到解决这个问题的方法 - 但我似乎无法弄清楚。我真的希望你们知道该怎么做。

我的Spring MVC应用程序开始失去与数据库的连接，我不知道为什么。这让我发疯。

弹簧数据库设置：

spring.datasource.driverClassName=com.mysql.jdbc.Driver
spring.datasource.url=jdbc:mysql://ip/database-name
spring.jpa.database-platform=org.hibernate.dialect.MySQL5Dialect
spring.jpa.show-sql=false
spring.datasource.tomcat.initialSize=5
spring.datasource.tomcat.maxActive=55
spring.datasource.tomcat.maxIdle=21
spring.datasource.tomcat.minIdle=13
spring.datasource.tomcat.testWhileIdle=true
spring.datasource.tomcat.timeBetweenEvictionRunsMillis=34000
spring.datasource.tomcat.minEvictableIdleTimeMillis=55000
spring.datasource.tomcat.validationInterval=34000
spring.datasource.tomcat.testOnBorrow=true
spring.datasource.tomcat.validationQuery=SELECT 1
spring.datasource.tomcat.removeAbandoned=true
spring.datasource.tomcat.removeAbandonedTimeout=233
spring.jpa.hibernate.ddl-auto=update

我试图指定"autoReconnect=true"，因为我找到了答案，这表明了这一点 - 但它并没有解决我的问题。

错误日志：

WARN 5220 --- [-nio-443-exec-6] o.h.engine.jdbc.spi.SqlExceptionHelper   : SQL Error: 0, SQLState: 08S01
ERROR 5220 --- [-nio-443-exec-6] o.h.engine.jdbc.spi.SqlExceptionHelper   : The last packet successfully received from the server was 60,401,089 milliseconds ago.  The last packet sent successfully to the server was 60,401,089 milliseconds ago. is longer than the server configured value of 'wait_timeout'. You should consider either expiring and/or testing connection val
idity before use in your application, increasing the server configured values for client timeouts, or using the Connector/J connection property 'autoReconnect=true' to avoid this problem.
ERROR 5220 --- [-nio-443-exec-6] w.a.UsernamePasswordAuthenticationFilter : An internal error occurred while trying to authenticate the user.
Caused by: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: The last packet successfully received from the server was 60,401,089 milliseconds ago.  The last packet sent successfully to the server was 60,401,089 milliseconds ago. is longer than the server configured value of 'wait_timeout'. You should consider either expiring and/or testing connection validity before use in your application, increasing the server configured values for client timeouts, or using the Connector/J connection property 'autoReconnect=true' to avoid this problem.
at sun.reflect.GeneratedConstructorAccessor277.newInstance(Unknown Source) ~[na:na]
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) ~[na:1.8.0_91]
at java.lang.reflect.Constructor.newInstance(Constructor.java:423) ~[na:1.8.0_91]
at com.mysql.jdbc.Util.handleNewInstance(Util.java:404) ~[mysql-connector-java-5.1.38.jar:5.1.38]
at com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:981) ~[mysql-connector-java-5.1.38.jar:5.1.38]
at com.mysql.jdbc.MysqlIO.send(MysqlIO.java:3652) ~[mysql-connector-java-5.1.38.jar:5.1.38]
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2460) ~[mysql-connector-java-5.1.38.jar:5.1.38]
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2625) ~[mysql-connector-java-5.1.38.jar:5.1.38]
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2551) ~[mysql-connector-java-5.1.38.jar:5.1.38]
at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1861) ~[mysql-connector-java-5.1.38.jar:5.1.38]
at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1962) ~[mysql-connector-java-5.1.38.jar:5.1.38]
at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:82) ~[hibernate-core-4.3.11.Final.jar:4.3.11.Final]
... 111 common frames omitted
Caused by: java.net.SocketException: Broken pipe
at java.net.SocketOutputStream.socketWrite0(Native Method) ~[na:1.8.0_91]
at java.net.SocketOutputStream.socketWrite(SocketOutputStream.java:109) ~[na:1.8.0_91]
at java.net.SocketOutputStream.write(SocketOutputStream.java:153) ~[na:1.8.0_91]
at java.io.BufferedOutputStream.flushBuffer(BufferedOutputStream.java:82) ~[na:1.8.0_91]
at java.io.BufferedOutputStream.write(BufferedOutputStream.java:126) ~[na:1.8.0_91]
at com.mysql.jdbc.MysqlIO.send(MysqlIO.java:3633) ~[mysql-connector-java-5.1.38.jar:5.1.38]
... 117 common frames omitted

谢谢。

Answer 1

我遇到了类似的问题，请尝试 2 件事：

1. 将spring.datasource.url=jdbc:mysql://ip/database-name更改为spring.datasource.url=jdbc:mysql://ip/database-name?autoReconnect=true

2. 我在我的应用程序中使用 c3p0，配置 idleConnectionTestPeriod 和首选测试查询解决了我的问题

   <!-- DB Configuration -->
   <bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource">
     <property name="jdbcUrl" value="${jdbc_url}" />
     <property name="user" value="${jdbc_username}" />
     <property name="password" value="${jdbc_password}" />
     <property name="driverClass" value="${jdbc_driver_class}" />
     <!-- these are C3P0 properties -->
     <property name="acquireIncrement" value="1" /> <!-- Determines how many connections at a time c3p0 will try to acquire when the pool is exhausted. -->
     <property name="minPoolSize" value="${jdbc_min_pool_size}" />
     <property name="maxPoolSize" value="${jdbc_max_pool_size}" />
     <property name="maxIdleTime" value="100" /> <!-- Seconds a Connection can remain pooled but unused before being discarded. Zero means idle connections never expire. -->
     <!--set this value less then mysql wait_timeout -->
     <property name="idleConnectionTestPeriod" value="100"/>   <!-- If this is a number greater than 0, C3P0 will test all idle, pooled but unchecked-out connections, every this number of seconds-->
     <property name="preferredTestQuery" value="select 1"/>   <!--a query used to test connections-->
   </bean>
   

您的财产spring.datasource.tomcat.validationInterval似乎也是如此。从错误来看，在您的情况下，MySQL wait_timeout似乎是 60,401,089 毫秒，因此 validationInterval 似乎小于此，但仍然尝试将其减少到 100 并查看错误是否再次发生。如果这不起作用，请尝试配置 c3p0 并使用我提到的配置。