记更新MySQL 8.0后踩过的那些坑

• 1、Establishing SSL connection without server’s identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must be established by default if explicit option isn’t set. For compliance with existing applications not using SSL the verifyServerCertificate property is set to ‘false’. You need either to explicitly disable SSL by setting useSSL=false, or set useSSL=true and provide truststore for server certificate verification.

---

解释：原因是以上版本的MySQL如果未设置显式选项，则必须默认建立SSL连接，为了符合不使用SSL的现有应用程序，您可以将verifyServerCertificate属性设置为false，您需要通过设置useSSL=false显式禁用SSL，或者设置useSSL=true并提供用于服务器证书验证的信任库
解决：在MySQL连接字符串后加上参数&useSSL=false,例如：

jdbc:mysql://localhost:3306/mail_server?useSSL=false&serverTimezone=UTC
<!-- xml配置文件中&符号需要转义为&amp;，如果是字符串配置则不需要转义 -->

---

• 2、You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘admin admin0_ where admin0_.account=’296293760@belief” at line 1

---

解释：MySQL 8.0 不再允许数据库表名为ADMIN，因此需要修改数据库表名，例如报错：

mysql> SELECT * FROM ADMIN;
1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ADMIN' at line 1

---

• 3、Unable to perform unmarshalling at line number 0 and column 0 in RESOURCE hibernate.cfg.xml. Message: null

---

解释：hibernate.cfg.xml中配置错误，例如需要转义的字符没有转义：

jdbc:mysql://localhost:3306/mail_server?useSSL=false&serverTimezone=UTC

解决：将&serverTimezone=UTC替换为&serverTimezone=UTC

---

• 4、Unable to create requested service [org.hibernate.engine.jdbc.connections.spi.ConnectionProvider]

---

解释：hibernate.cfg.xml中属性配置错误，或hibernate找不到配置的属性对应需要加载的类
解决：导入缺失的jar包或删除错误属性配置

---

• 5、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.

---

解释：MySQL 8.0不能用老版的com.mysql.jdbc.Driver驱动了，需要换成mysql-connector-java-8.0.11.jar包中的com.mysql.cj.jdbc.Driver新版驱动

---

• 6、java.sql.SQLException: The server time zone value ‘???ú±ê×??±??’ is unrecognized or represents more than one time zone.

---

解释：服务器时区值不可识别，应该在MySQL连接字符串后面加上属性：serverTimezone=UTC
最后贴一个完整的MySQL 8.0下Hibernate的配置文件：

<?xml version="1.0" encoding="UTF-8"?>
<!-- ~ Hibernate, Relational Persistence for Idiomatic Java ~ ~ License: 
    GNU Lesser General Public License (LGPL), version 2.1 or later. ~ See the 
    lgpl.txt file in the root directory or <http://www.gnu.org/licenses/lgpl-2.1.html>. -->
<!DOCTYPE hibernate-configuration PUBLIC
    "-//Hibernate/Hibernate Configuration DTD 3.0//EN"
    "http://www.hibernate.org/dtd/hibernate-configuration-3.0.dtd">

<hibernate-configuration>
    <session-factory>
        <property name="hibernate.connection.driver_class">com.mysql.cj.jdbc.Driver</property>
        <!-- xml配置文件中不允许出现&符号等 -->
        <property name="hibernate.connection.url">jdbc:mysql://localhost:3306/mail_server?useSSL=false&amp;serverTimezone=UTC</property>
        <property name="hibernate.connection.username">root</property>
        <property name="hibernate.connection.password">LYY1996*</property>

        <!-- connection.pool_size:配置JDBC连接池 -->
        <property name="connection.pool_size">20</property>

        <!-- dialect:配置数据库方言 -->
        <property name="hibernate.dialect">org.hibernate.dialect.MySQL8Dialect</property>

        <!-- show_sql:操作数据库时，会向控制台打印sql语句 -->
        <property name="show_sql">true</property>

        <!-- format_sql:打印sql语句前，会将sql先格式化 -->
        <!-- <property name="format_sql">true</property> -->

        <!-- hbm2ddl.auto:是否自动生成表结构 -->
        <!-- <property name="hbm2ddl.auto">update</property> -->

        <!-- 列出所有映射文件 -->
        <mapping resource="com/belief/model/User.hbm.xml" />
        <mapping resource="com/belief/model/Mail.hbm.xml" />
        <mapping resource="com/belief/model/Admin.hbm.xml" />
        <mapping resource="com/belief/model/Adjunct.hbm.xml" />
        <mapping resource="com/belief/model/Address.hbm.xml" />
    </session-factory>
</hibernate-configuration>

---

• 7、启动MySQL Command Line Client闪退（安装时显示开始菜单快捷键创建失败），但是Navicat连接却正常

---

排查：打开开始菜单快捷方式的位置，我的是在：
C:\ProgramData\Microsoft\Windows\Start Menu\Programs\MySQL（这是一个隐藏文件夹）
右键快捷方式属性可以看到：

快捷方式的链接地址是：
"C:\Program Files\MySQL\MySQL Server 8.0\bin\mysql.exe" "--defaults-file=C:\Program Files\MySQL\MySQL Server 8.0\my.ini" "-uroot" "-p"

如果你没有这个my.ini文件，那么就自行创建一个，内容为：

[mysqld]
port=3306
innodb_buffer_pool_size=503M
feedback=ON
character-set-server=utf8
[client]
port=3306
plugin-dir=C:\Program Files\MySQL\MySQL Server 8.0\lib\plugin

---

• 8、mysql不是内部命令或外部命令，也不是可运行的程序

---

解决：需要配置MySQL环境变量：