Spring Boot 项目 Mysql数据库连接问题

记录一次搭建SpringBoot项目时连接数据库的问题,版本:SpringBoot 2.3.1/mysql 5.6.47

启动项目时出现警告:

2020-06-22 10:19:21.704  INFO 8720 --- [           main] o.a.catalina.core.AprLifecycleListener   : OpenSSL successfully initialized [OpenSSL 1.1.1c  28 May 2019]
2020-06-22 10:19:21.777  INFO 8720 --- [           main] o.a.c.c.C.[Tomcat].[localhost].[/]       : Initializing Spring embedded WebApplicationContext
2020-06-22 10:19:21.777  INFO 8720 --- [           main] w.s.c.ServletWebServerApplicationContext : Root WebApplicationContext: initialization completed in 1120 ms
Loading class `com.mysql.jdbc.Driver‘. This is deprecated. The new driver class is `com.mysql.cj.jdbc.Driver‘. The driver is automatically registered via the SPI and manual loading of the driver class is generally unnecessary.
2020-06-22 10:19:22.184  INFO 8720 --- [           main] o.s.s.concurrent.ThreadPoolTaskExecutor  : Initializing ExecutorService ‘applicationTaskExecutor‘
2020-06-22 10:19:22.247  INFO 8720 --- [           main] o.s.b.a.w.s.WelcomePageHandlerMapping    : Adding welcome page template: index
2020-06-22 10:19:22.419  INFO 8720 --- [           main] o.s.b.w.embedded.tomcat.TomcatWebServer  : Tomcat started on port(s): 8080 (http) with context path ‘‘

 

请求数据库时异常日志如下:

java.sql.SQLException: The server time zone value ‘?й???????‘ is unrecognized or represents more than one time zone. You must configure either the server or JDBC driver (via the ‘serverTimezone‘ configuration property) to use a more specifc time zone value if you want to utilize time zone support.
    at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:129) ~[mysql-connector-java-8.0.20.jar:8.0.20]
    at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97) ~[mysql-connector-java-8.0.20.jar:8.0.20]
    at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:89) ~[mysql-connector-java-8.0.20.jar:8.0.20]
    at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:63) ~[mysql-connector-java-8.0.20.jar:8.0.20]
    at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:73) ~[mysql-connector-java-8.0.20.jar:8.0.20]
    at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:76) ~[mysql-connector-java-8.0.20.jar:8.0.20]
    at com.mysql.cj.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:836) ~[mysql-connector-java-8.0.20.jar:8.0.20]
    at com.mysql.cj.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:456) ~[mysql-connector-java-8.0.20.jar:8.0.20]
    at com.mysql.cj.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:246) ~[mysql-connector-java-8.0.20.jar:8.0.20]
    at com.mysql.cj.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:197) ~[mysql-connector-java-8.0.20.jar:8.0.20]
    at com.zaxxer.hikari.util.DriverDataSource.getConnection(DriverDataSource.java:138) ~[HikariCP-3.4.5.jar:na]
    at com.zaxxer.hikari.pool.PoolBase.newConnection(PoolBase.java:358) ~[HikariCP-3.4.5.jar:na]
    at com.zaxxer.hikari.pool.PoolBase.newPoolEntry(PoolBase.java:206) ~[HikariCP-3.4.5.jar:na]
    at com.zaxxer.hikari.pool.HikariPool.createPoolEntry(HikariPool.java:477) [HikariCP-3.4.5.jar:na]

 

部分配置如下:

spring:
  datasource:
    driver-class-name: com.mysql.jdbc.Driver
    url: jdbc:mysql://localhost:3306/test?setUnicode=true&characterEncoding=utf8
    username: root
    password: root

 

解决过程:

1. 警告日志提示新的驱动名称为  com.mysql.cj.jdbc.Driver 

2. 根据错误日志提示的信息,网上好多博主都是设置serverTimezone

于是修改配置文件为:

spring:
  datasource:
    driver-class-name: com.mysql.cj.jdbc.Driver
    url: jdbc:mysql://localhost:3306/test?setUnicode=true&characterEncoding=utf8&serverTimezone=UTC
    username: root
    password: root

测试发现还是不行,想到之前项目很多出错的原因都是maven依赖导致的,于是查看mysql的依赖

<!-- mysql连接 -->
<dependency>
       <groupId>mysql</groupId>
       <artifactId>mysql-connector-java</artifactId>
       <scope>runtime</scope>
</dependency>

Ctrl + 左键 点击 mysql-connector-java  查看详细信息

<modelVersion>4.0.0</modelVersion>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.20</version>
<packaging>jar</packaging>

发现版本为8.0.20,这样就明白为什么会有上面的警告日志了,再考虑到我的数据库是5.6版本,于是尝试降低mysql依赖的版本,最终的解决方式如下

pom文件,指定mysql依赖版本

<!-- mysql连接 -->
<dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <version>5.1.20</version>
        <scope>runtime</scope>
</dependency>

修改yml配置文件

spring:
  datasource:
    driver-class-name: com.mysql.jdbc.Driver
    url: jdbc:mysql://localhost:3306/test?setUnicode=true&characterEncoding=utf8
    username: root
    password: root

 建议还是要设置一下serverTimezone时区问题 

url: jdbc:mysql://localhost:3306/test?setUnicode=true&characterEncoding=utf8&serverTimezone=UTC

Spring Boot 项目 Mysql数据库连接问题

上一篇:深入剖析Redis高可用系列:持久化 AOF和RDB


下一篇:CSV文件导入到数据库中读取数据详解(接着上个帖子)