MySQLNonTransientConnectionException: Could not create connection to database server.

红太狼 2024-03-03 03:02 26阅读 0赞

## 问题描述 ##

SPringBoot项目后断使用MYSQL 5.7.43。Java JDBC驱动依赖选择的是

<groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <version>5.1.47</version>
    </dependency>

驱动参数配置如下：

spring:
      datasource:
        username: root
        password: root
        url: jdbc:mysql://127.0.0.1:3306/data_backend?autoReconnect=true&useUnicode=true&characterEncoding=utf8&serverTimezone=GMT%2B8
        driver-class-name: com.mysql.jdbc.Driver

使用后端接口测试工具测试后端接口是否正常工作时报错如下：

com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: Could not create connection to database server. Attempted reconnect 3 times. Giving up.
    	at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) ~[na:1.8.0_381]
    	at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62) ~[na:1.8.0_381]
    	at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) ~[na:1.8.0_381]
    	at java.lang.reflect.Constructor.newInstance(Constructor.java:423) ~[na:1.8.0_381]
    	at com.mysql.jdbc.Util.handleNewInstance(Util.java:425) ~[mysql-connector-java-5.1.47.jar:5.1.47]
    	at com.mysql.jdbc.Util.getInstance(Util.java:408) ~[mysql-connector-java-5.1.47.jar:5.1.47]
    	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:919) ~[mysql-connector-java-5.1.47.jar:5.1.47]
    	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:898) ~[mysql-connector-java-5.1.47.jar:5.1.47]
    	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:887) ~[mysql-connector-java-5.1.47.jar:5.1.47]
    	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:861) ~[mysql-connector-java-5.1.47.jar:5.1.47]
    	at com.mysql.jdbc.ConnectionImpl.connectWithRetries(ConnectionImpl.java:2105) ~[mysql-connector-java-5.1.47.jar:5.1.47]
    	at com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2030) ~[mysql-connector-java-5.1.47.jar:5.1.47]
    	at com.mysql.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:778) ~[mysql-connector-java-5.1.47.jar:5.1.47]
    	at com.mysql.jdbc.JDBC4Connection.<init>(JDBC4Connection.java:47) ~[mysql-connector-java-5.1.47.jar:5.1.47]
    	at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) ~[na:1.8.0_381]
    	at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62) ~[na:1.8.0_381]
    	at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) ~[na:1.8.0_381]
    	at java.lang.reflect.Constructor.newInstance(Constructor.java:423) ~[na:1.8.0_381]
    	at com.mysql.jdbc.Util.handleNewInstance(Util.java:425) ~[mysql-connector-java-5.1.47.jar:5.1.47]
    	at com.mysql.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:386) ~[mysql-connector-java-5.1.47.jar:5.1.47]
    	at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:330) ~[mysql-connector-java-5.1.47.jar:5.1.47]
    	at com.zaxxer.hikari.util.DriverDataSource.getConnection(DriverDataSource.java:138) ~[HikariCP-4.0.3.jar:na]
    	at com.zaxxer.hikari.pool.PoolBase.newConnection(PoolBase.java:364) ~[HikariCP-4.0.3.jar:na]
    	at com.zaxxer.hikari.pool.PoolBase.newPoolEntry(PoolBase.java:206) ~[HikariCP-4.0.3.jar:na]
    	at com.zaxxer.hikari.pool.HikariPool.createPoolEntry(HikariPool.java:476) [HikariCP-4.0.3.jar:na]
    	at com.zaxxer.hikari.pool.HikariPool.checkFailFast(HikariPool.java:561) [HikariCP-4.0.3.jar:na]
    	at com.zaxxer.hikari.pool.HikariPool.<init>(HikariPool.java:115) [HikariCP-4.0.3.jar:na]
    	at com.zaxxer.hikari.HikariDataSource.getConnection(HikariDataSource.java:112) [HikariCP-4.0.3.jar:na]
    	at org.springframework.jdbc.datasource.DataSourceUtils.fetchConnection(DataSourceUtils.java:159) [spring-jdbc-5.3.22.jar:5.3.22]
    	at org.springframework.jdbc.datasource.DataSourceUtils.doGetConnection(DataSourceUtils.java:117) [spring-jdbc-5.3.22.jar:5.3.22]
    	at org.springframework.jdbc.datasource.DataSourceUtils.getConnection(DataSourceUtils.java:80) [spring-jdbc-5.3.22.jar:5.3.22]
    	at org.mybatis.spring.transaction.SpringManagedTransaction.openConnection(SpringManagedTransaction.java:80) [mybatis-spring-2.0.7.jar:2.0.7]
    	........................................................................................
    ........................................................................................
    ........................................................................................
    ........................................................................................
    ........................................................................................
    ........................................................................................
    Caused by: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure

## 解决方案 ##

Mysql数据库的驱动版本不对。

看看connector/J与MYSQL的版本对应关系：

![e70ec1da30b74e81910fb6621528c1d3.webp][]

好像5.1的驱动也支持5.7的MYSQL数据库唉，但是就是没法用，官方推荐使用或升级到 `8.0` 的驱动版本。试着换成8.0的数据库驱动并更改yml配置文件数据库驱动信息。

把**mysql-Connector-java的版本换成8.0.x**的。即将5.1.47的依赖换成如下依赖：

<dependency>
                <groupId>mysql</groupId>
                <artifactId>mysql-connector-java</artifactId>
                <version>8.0.19</version>
            </dependency>

并将**application.yml中的数据库驱动配置为com.mysql.cj.jdbc.Driver**，如下：

重启项目，后端接口即能够成功访问数据库。

[e70ec1da30b74e81910fb6621528c1d3.webp]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/03/609b5977fcc243d29d1af9f7a8b755e8.png