mysql链接问题 #4078
Comments
|
这个问题应该是nacos设置的数据库连接的有效期大于了数据库那边设置的有效期， 导致连接池继续复用已被数据库测关闭的连接导致的。 可以尝试修改加长mysql的连接有效期，如果不方便修改数据库，可以在jdbcurl上增加 后续版本我们会优化nacos中连接池和数据库的使用。 |
KomachiSion
added
the
kind/enhancement
|
@KomachiSion 嗨，我这边也遇到类似的问题了，1.3.2版本的使用mysql 5.7 作为存储时过一会就会挂掉，回推到1.2.1又是正常的 2020-09-23 11:53:52,188 ERROR [db-error] master db 10.xx.xxx.xxx down. 2020-09-23 11:53:52,206 ERROR [master-db] master db not found. 2020-09-23 11:54:02,192 ERROR [db-error] master db 10.xx.xxx.xxx down. 2020-09-23 11:54:02,209 ERROR [master-db] master db not found. 2020-09-23 11:54:12,195 ERROR [db-error] master db 10.xx.xxx.xxx down. 2020-09-23 11:54:12,212 ERROR [master-db] master db not found. 2020-09-23 11:54:22,199 ERROR [db-error] master db 10.xx.xxx.xxx down. 2020-09-23 11:54:22,226 ERROR [master-db] master db not found. 2020-09-23 11:54:32,203 ERROR [db-error] master db 10.xx.xxx.xxx down. 2020-09-23 11:54:32,229 ERROR [master-db] master db not found. 2020-09-23 11:54:42,208 ERROR [db-error] master db 10.xx.xxx.xxx down. 2020-09-23 11:54:42,232 ERROR [master-db] master db not found. 2020-09-23 11:54:52,212 ERROR [db-error] master db 10.xx.xxx.xxx down. 2020-09-23 11:54:52,236 ERROR [master-db] master db not found. 2020-09-23 11:54:54,534 ERROR [db-error] org.springframework.jdbc.CannotGetJdbcConnectionException: Failed to obtain JDBC Connection; nested exception is java.sql.SQLTransientConnectionException: HikariPool-1 - Connection is not available, request timed out after 600095ms. org.springframework.jdbc.CannotGetJdbcConnectionException: Failed to obtain JDBC Connection; nested exception is java.sql.SQLTransientConnectionException: HikariPool-1 - Connection is not available, request timed out after 600095ms. The last packet successfully received from the server was 1,583,328 milliseconds ago. The last packet sent successfully to the server was 1,583,328 milliseconds ago. The last packet successfully received from the server was 1,583,328 milliseconds ago. The last packet sent successfully to the server was 1,583,328 milliseconds ago. 2020-09-23 11:55:02,239 ERROR [master-db] master db not found. 2020-09-23 11:59:35,034 ERROR [db-error] org.springframework.jdbc.CannotGetJdbcConnectionException: Failed to obtain JDBC Connection; nested exception is java.sql.SQLTransientConnectionException: HikariPool-1 - Connection is not available, request timed out after 600103ms. org.springframework.jdbc.CannotGetJdbcConnectionException: Failed to obtain JDBC Connection; nested exception is java.sql.SQLTransientConnectionException: HikariPool-1 - Connection is not available, request timed out after 600103ms. The last packet successfully received from the server was 1,863,237 milliseconds ago. The last packet sent successfully to the server was 1,863,237 milliseconds ago. The last packet successfully received from the server was 1,863,237 milliseconds ago. The last packet sent successfully to the server was 1,863,237 milliseconds ago. 下面是 nacos.log 的日志 2020-09-23 11:38:31,910 WARN HikariPool-1 - Failed to validate connection com.mysql.cj.jdbc.ConnectionImpl@6ca7bd5e (Communications link failure The last packet successfully received from the server was 2,363,249 milliseconds ago. The last packet sent successfully to the server was 2,363,256 milliseconds ago.). Possibly consider using a shorter maxLifetime value. 2020-09-23 11:39:01,877 WARN HikariPool-1 - Failed to validate connection com.mysql.cj.jdbc.ConnectionImpl@102c7352 (Communications link failure The last packet successfully received from the server was 2,393,118 milliseconds ago. The last packet sent successfully to the server was 2,393,119 milliseconds ago.). Possibly consider using a shorter maxLifetime value. 2020-09-23 11:39:04,859 WARN HikariPool-1 - Failed to validate connection com.mysql.cj.jdbc.ConnectionImpl@161edd55 (Communications link failure The last packet successfully received from the server was 2,395,725 milliseconds ago. The last packet sent successfully to the server was 2,395,732 milliseconds ago.). Possibly consider using a shorter maxLifetime value. 2020-09-23 11:39:09,582 INFO [capacityManagement] start correct usage 2020-09-23 11:39:09,588 INFO [capacityManagement] end correct usage, cost: 0s 2020-09-23 11:39:09,674 WARN clearConfigHistory start 2020-09-23 11:49:09,588 INFO [capacityManagement] start correct usage 2020-09-23 11:49:09,594 INFO [capacityManagement] end correct usage, cost: 0s 2020-09-23 11:49:09,677 WARN clearConfigHistory start 2020-09-23 11:50:57,477 WARN HikariPool-1 - Failed to validate connection com.mysql.cj.jdbc.ConnectionImpl@44101261 (Communications link failure The last packet successfully received from the server was 1,349,854 milliseconds ago. The last packet sent successfully to the server was 1,349,855 milliseconds ago.). Possibly consider using a shorter maxLifetime value. 2020-09-23 11:50:57,480 ERROR CONSOLE org.springframework.jdbc.CannotGetJdbcConnectionException: Failed to obtain JDBC Connection; nested exception is java.sql.SQLTransientConnectionException: HikariPool-1 - Connection is not available, request timed out after 600101ms. The last packet successfully received from the server was 1,349,854 milliseconds ago. The last packet sent successfully to the server was 1,349,855 milliseconds ago. The last packet successfully received from the server was 1,349,854 milliseconds ago. The last packet sent successfully to the server was 1,349,855 milliseconds ago. The last packet successfully received from the server was 1,431,360 milliseconds ago. The last packet sent successfully to the server was 1,431,361 milliseconds ago.). Possibly consider using a shorter maxLifetime value. 2020-09-23 11:53:49,266 ERROR CONSOLE org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; bad SQL grammar [ SELECT COUNT(ID) FROM config_info where tenant_id like ?]; nested exception is java.sql.SQLSyntaxErrorException: Table 'nacos.config_info' doesn't exist org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; bad SQL grammar [ SELECT COUNT(ID) FROM config_info where tenant_id like ?]; nested exception is java.sql.SQLSyntaxErrorException: Table 'nacos.config_info' doesn't exist |
|
@KomachiSion But the same 1.2.1 version is normal （可是同样的 1.2.1 版本是正常的） |
|
It should be some problem in configuration, 1.3.2 and 1.4.0-BETA in my env is ok, which has run more than few weeks. Can you provide your mysql time_out setting. SQL: And tell us the time which the problem will happen after you start nacos. |
your problem might be this. |
|
Please provide the result of SQL: and provide your db configuration of nacos. |
|
@KomachiSion OK |
|
mysql> show variables like '%timeout%'; nacos jdbc config |
|
还有一个现象，让我对nacos工作机制有点迷茫，继上面描述的现象发生后，nacos工作台请求api全部都无响应（配置、服务、命名空间等肯定没法显示 了），查看日志：数据库超时，但是nacos注册和发现服务能正常工作，这一点让我十分不解，，我反复试过了。。。。 Nacos日志如下： The last packet successfully received from the server was 1,729,856 milliseconds ago. The last packet sent successfully to the server was 1,729,897 milliseconds ago. The last packet successfully received from the server was 1,729,856 milliseconds ago. The last packet sent successfully to the server was 1,729,897 milliseconds ago. 注册发现项目启动日志如下： |
|
|
@KomachiSion 也碰到了类似的问题mysql连不上，（nacos1.3.2 mysql5.7.24）。原因是把登录安装mysql的linux服务器的密码跟登录mysql的密码搞混了。我真是傻了- -。 |
|
我想知道这个问题已经修复了吗？ 还是需要配置数据库，我现在也遇到了这个问题 |
|
|
后来有解决吗，我也出现了这个问题 |
|
请问现在用的1.3.2会出现这个问题是不是数据库连接池的问题，定制新版之后还会出现这样的问题吗？ |
|
我似乎也遇到相同问题，正常运行，在机房停电检修恢复后nacos连不上mysql。但是其它服务都可以正常连上mysql了。 nacos使用的默认配置 Connect URL of DB:characterEncoding=utf8&connectTimeout=1000&socketTimeout=3000&autoReconnect=true&useUnicode=true&useSSL=false&serverTimezone=UTC Connection pool configuration: hikariCPdb.pool.config.connectionTimeout=30000 报错信息如下 |
|
我也遇到了，问题现象就是，nacos集群，数据不一致 |
nacos 1.3.2配置好mysql(5.6)会莫名断开连接（服务、配置、命名空间列表全部取不出来），其它项目都连的这台数据库服器是正常的，而且已经跑了2年多了，需要重启nacos能正常工作，偶尔退出nacos帐号登录再重新登录也行的通，十分奇怪，查看nacos日志：
2020-10-28 15:35:04,895 WARN HikariPool-1 - Failed to validate connection com.mysql.cj.jdbc.ConnectionImpl@2c01a219 (Communications link failure
The last packet successfully received from the server was 2,362,858 milliseconds ago. The last packet sent successfully to the server was 2,362,897 milliseconds ago.). Possibly consider using a shorter maxLifetime value.
2020-10-28 15:36:22,043 WARN HikariPool-1 - Failed to validate connection com.mysql.cj.jdbc.ConnectionImpl@24b8500d (Communications link failure
The last packet successfully received from the server was 2,249,110 milliseconds ago. The last packet sent successfully to the server was 2,249,142 milliseconds ago.). Possibly consider using a shorter maxLifetime value.
2020-10-28 15:39:33,055 WARN HikariPool-1 - Failed to validate connection com.mysql.cj.jdbc.ConnectionImpl@5f5607f5 (Communications link failure
The last packet successfully received from the server was 2,111,657 milliseconds ago. The last packet sent successfully to the server was 2,111,696 milliseconds ago.). Possibly consider using a shorter maxLifetime value.
2020-10-28 15:44:21,240 WARN HikariPool-1 - Failed to validate connection com.mysql.cj.jdbc.ConnectionImpl@76bc34bb (Communications link failure
The last packet successfully received from the server was 2,356,463 milliseconds ago. The last packet sent successfully to the server was 2,356,491 milliseconds ago.). Possibly consider using a shorter maxLifetime value.
2020-10-28 15:44:21,244 ERROR clearConfigHistory error : org.springframework.jdbc.CannotGetJdbcConnectionException: Failed to obtain JDBC Connection; nested exception is java.sql.SQLTransientConnectionException: HikariPool-1 - Connection is not available, request timed out after 600092ms.
2020-10-28 15:45:05,009 WARN HikariPool-1 - Failed to validate connection com.mysql.cj.jdbc.ConnectionImpl@544d8117 (Communications link failure
The text was updated successfully, but these errors were encountered: