记录一次搭建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