MySQL Spring Boot：Jdbc javax.net.ssl.SSLException：在接收对等方的 close_notify 之前关闭入站

Spring Boot: Jdbc javax.net.ssl.SSLException: closing inbound before receiving peer's close_notify

提问by Dup Park

I am currently learning more about implementing JDBC and using databases in a Spring Boot webapp, and I encountered the following Stack Trace written in the bottom of the post.

我目前正在学习更多关于在 Spring Boot webapp 中实现 JDBC 和使用数据库的知识，我遇到了下面写在帖子底部的 Stack Trace。

I have created a simple Employee model, and I am trying to execute some database code on the same class which my main() lies in. The model and the main class are the only two java files existing in this whole project. I am trying to implement the following run() code that overrides the one from the interface, CommandLineRunner, but I do not get the logs that should come after log.info("Part A:"):

我创建了一个简单的 Employee 模型，我试图在 main() 所在的同一个类上执行一些数据库代码。模型和主类是整个项目中仅有的两个 java 文件。我正在尝试实现以下 run() 代码，该代码覆盖了接口 CommandLineRunner 中的代码，但我没有得到应该在log.info("Part A:")之后出现的日志：

log.info("Part A:")
employees.forEach(employee -> {log.info(employee.toString());
        log.info("part a");});

--Things I noticed:

——我注意到的事情：

I noticed that the last line of the log before the stack trace starts comes from: "Thread-1" instead of "main". I think that means that a thread from somewhere other than the main encountered an error and closed connection before when it should close normally.

我注意到堆栈跟踪开始之前日志的最后一行来自：“Thread-1”而不是“main”。我认为这意味着来自主线程以外的某个线程在它应该正常关闭之前遇到了错误并关闭了连接。

Also, I think that because HikariPool closed before "peer's close_notify", which I presume that it refers to the normal closure of HikariPool, I am not able to see the final bit of logging that I have kept trying to procure. The final bit of logging that I want to see is the logging of the employee that has become inserted into my database.

另外，我认为因为HikariPool 在 "peer's close_notify" 之前关闭，我认为它指的是 HikariPool 的正常关闭，所以我无法看到我一直试图获取的最后一点日志记录。我想查看的最后一点日志记录是插入到我的数据库中的员工的日志记录。

The final bit of logging that I want to see should be procured from this line of code:

我想看到的最后一点日志应该从这行代码中获得：

employees.forEach(employee -> {log.info(employee.toString());
        log.info("part a");});

--Things to note:

——注意事项：

Because of this line in the log, I thought I would see the employee inserted into my database, but when I queried directly on MySQL Command Line Client, it returned an empty set:

因为日志中的这一行，我以为我会看到员工插入我的数据库，但是当我直接在MySQL命令行客户端上查询时，它返回了一个空集：

2018-11-03 21:08:35.362  INFO 2408 --- [           main] c.j.jdbctest1.JdbcTest1Application       : rows affected: 1

I don't understand why a row has been affected when nothing has been inserted into the database.

我不明白为什么没有在数据库中插入任何内容时一行会受到影响。

The stacktrace and logs: (The stacktrace pasted below actually repeats itself several more times but I cut it off for brevity.)

堆栈跟踪和日志：（下面粘贴的堆栈跟踪实际上又重复了几次，但为了简洁起见，我将其切断了。）

2018-11-03 21:08:32.997  INFO 2408 --- [           main] c.j.jdbctest1.JdbcTest1Application       : Starting JdbcTest1Application on KitKat with PID 2408 (C:\Users\Nano\Downloads\jdbc-test1\jdbc-test1\target\classes started by Nano in C:\Users\Nano\Downloads\jdbc-test1\jdbc-test1)
2018-11-03 21:08:33.003  INFO 2408 --- [           main] c.j.jdbctest1.JdbcTest1Application       : No active profile set, falling back to default profiles: default
2018-11-03 21:08:33.770  INFO 2408 --- [           main] c.j.jdbctest1.JdbcTest1Application       : Started JdbcTest1Application in 1.024 seconds (JVM running for 1.778)
2018-11-03 21:08:33.770  INFO 2408 --- [           main] c.j.jdbctest1.JdbcTest1Application       : Creating tables
2018-11-03 21:08:33.770  INFO 2408 --- [           main] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Starting...
2018-11-03 21:08:34.082  INFO 2408 --- [           main] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Start completed.
2018-11-03 21:08:35.135  INFO 2408 --- [           main] c.j.jdbctest1.JdbcTest1Application       : Inserting Baggins Hopkins
2018-11-03 21:08:35.362  INFO 2408 --- [           main] c.j.jdbctest1.JdbcTest1Application       : rows affected: 1
2018-11-03 21:08:35.362  INFO 2408 --- [           main] c.j.jdbctest1.JdbcTest1Application       : Querying for employee
2018-11-03 21:08:36.065  INFO 2408 --- [           main] c.j.jdbctest1.JdbcTest1Application       : Part A:
2018-11-03 21:08:36.065  INFO 2408 --- [       Thread-1] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Shutdown initiated...
Sat Nov 03 21:08:36 KST 2018 WARN: Caught while disconnecting...

EXCEPTION STACK TRACE:



** BEGIN NESTED EXCEPTION ** 

javax.net.ssl.SSLException
MESSAGE: closing inbound before receiving peer's close_notify

STACKTRACE:

javax.net.ssl.SSLException: closing inbound before receiving peer's close_notify
    at java.base/sun.security.ssl.Alert.createSSLException(Alert.java:129)
    at java.base/sun.security.ssl.Alert.createSSLException(Alert.java:117)
    at java.base/sun.security.ssl.TransportContext.fatal(TransportContext.java:308)
    at java.base/sun.security.ssl.TransportContext.fatal(TransportContext.java:264)
    at java.base/sun.security.ssl.TransportContext.fatal(TransportContext.java:255)
    at java.base/sun.security.ssl.SSLSocketImpl.shutdownInput(SSLSocketImpl.java:645)
    at java.base/sun.security.ssl.SSLSocketImpl.shutdownInput(SSLSocketImpl.java:624)
    at com.mysql.cj.protocol.a.NativeProtocol.quit(NativeProtocol.java:1312)
    at com.mysql.cj.NativeSession.quit(NativeSession.java:182)
    at com.mysql.cj.jdbc.ConnectionImpl.realClose(ConnectionImpl.java:1750)
    at com.mysql.cj.jdbc.ConnectionImpl.close(ConnectionImpl.java:720)
    at com.zaxxer.hikari.pool.PoolBase.quietlyCloseConnection(PoolBase.java:135)
    at com.zaxxer.hikari.pool.HikariPool.lambda$closeConnection(HikariPool.java:441)
    at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128)
    at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628)
    at java.base/java.lang.Thread.run(Thread.java:834)


** END NESTED EXCEPTION **

The Java Code:

Java代码：

@SpringBootApplication
public class JdbcTest1Application implements CommandLineRunner {
    private static final Logger log = LoggerFactory.getLogger(JdbcTest1Application.class);

    @Autowired
    JdbcTemplate jdbcTemplate;

    public static void main(String[] args) {
        SpringApplication.run(JdbcTest1Application.class, args);
    }

    @Override
    public void run(String... args) throws Exception {
        log.info("Creating tables");

        jdbcTemplate.execute("DROP TABLE IF EXISTS employees");
        jdbcTemplate.execute("CREATE TABLE employees (emp_id int, name varchar(100), role varchar(100), status varchar(100))");

        log.info("Inserting Baggins Hopkins");
        int rowsAffected = jdbcTemplate.update("INSERT INTO EMPLOYEE(EMP_ID, NAME, ROLE, STATUS)"
                + " VALUES(1,'Baggins Hopkins','thief','WORKING')");
        log.info("rows affected: "+ Integer.toString(rowsAffected));
        log.info("Querying for employee");
        String sql = "SELECT emp_id,name,role,status FROM employees";
        List<Employee> employees = jdbcTemplate.query(sql,(rs, rowNum)-> 
        new Employee(rs.getInt("emp_id"), rs.getString("name"),
                rs.getString("role"),Status.valueOf(rs.getString("status"))));
        log.info("Part A:");
        employees.forEach(employee -> {log.info(employee.toString());
            log.info("part a");});

    }
}

Also just in case this matters, I pasted this code from application.properties:

同样为了以防万一，我从 application.properties 粘贴了这段代码：

spring.datasource.url=jdbc:mysql://localhost:3306/employee_database
spring.datasource.username=employee
spring.datasource.password=employee
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver

采纳答案by Dup Park

To solve this problem, it took me about three days.

为了解决这个问题，我花了大约三天的时间。

(Edit: This is a workaround for testing and not actually a solution.)

（编辑：这是一种用于测试的解决方法，而不是实际的解决方案。）

At first, I started solving the problem from trying to configure my own SSL for mysql, and I spent quite a few hours on that. Too much time had passed until I realized configuring it had to do with Cmake and C++, which made me give up. It was very frustrating. However I did not give up and tried to disable SSL entirely through a method that hasn't been found. And I eventually did find the method. Here it is:

起初，我从尝试为 mysql 配置我自己的 SSL 开始解决问题，为此我花了好几个小时。过了太多时间，直到我意识到配置它与 Cmake 和 C++ 有关，这让我放弃了。这非常令人沮丧。但是我并没有放弃并尝试通过一种尚未找到的方法完全禁用 SSL。我最终确实找到了方法。这里是：

1. You have to use the legacy password for MySQL. The legacy password is the way MySQL authenticated things in version 5.7x.

1. 您必须使用 MySQL 的旧密码。旧密码是 MySQL 在 5.7x 版本中验证事物的方式。

Open up the MySQL installer again, and reconfigure the MySQL Server settings. When you get there you will see this screen:

再次打开 MySQL 安装程序，并重新配置 MySQL 服务器设置。当你到达那里时，你会看到这个屏幕：

The screen that you should get to

你应该到达的屏幕

You might get some errors when reaching the final stage of the reconfiguration:

I had problems at the final stage I had no idea how to fix so I uninstalled MySQL altogether. I use windows. I deleted the MySQL project root directory from Program Files to uninstall MySQL. I also deleted the databases saved in Program Data (a hidden folder in the C Drive) because I wanted to start afresh(WARNING: this will delete all your previously saved data!). Uninstalling MySQL from the control panel might not be enough to completely erase MySQL from your computer.

到达重新配置的最后阶段时，您可能会遇到一些错误：

我在最后阶段遇到了问题，我不知道如何解决，所以我完全卸载了 MySQL。我用窗户。我从 Program Files 中删除了 MySQL 项目根目录以卸载 MySQL。我还删除了保存在 Program Data（C 驱动器中的隐藏文件夹）中的数据库，因为我想重新开始（警告：这将删除您以前保存的所有数据！）。从控制面板卸载 MySQL 可能不足以从您的计算机中完全删除 MySQL。

1. Delete all *.pem files in C:\ProgramData\MySQL\MySQL Server 8.0\Data. (or move it somewhere else, which is what I did)

2. 删除 C:\ProgramData\MySQL\MySQL Server 8.0\Data 中的所有 *.pem 文件。（或将其移到其他地方，这就是我所做的）

You might not see ProgramData in the C Drive. That is because it is a hidden folder. In order to see hidden folders:

search for folder options in the control panel.

Go to view.

Under 'Advanced settings' and under 'Hidden files and folders' of that, click "Show hidden files, folders, and drives."

您可能在 C 驱动器中看不到 ProgramData。那是因为它是一个隐藏文件夹。要查看隐藏文件夹：

在控制面板中搜索文件夹选项。

去查看。

在“高级设置”下的“隐藏文件和文件夹”下，单击“显示隐藏的文件、文件夹和驱动器”。

1. Go to C:\ProgramData\MySQL\MySQL Server 8.0 and open my.cnf (or my.ini). Add the following line after [mysqld]:

3. 转到 C:\ProgramData\MySQL\MySQL Server 8.0 并打开 my.cnf（或 my.ini）。在 [mysqld] 之后添加以下行：

ssl=0

ssl=0

Then save. It should work now.

然后保存。它现在应该可以工作了。

References:

参考：

1. https://community.atlassian.com/t5/Confluence-questions/MySQL-Public-Key-Retrieval-is-not-allowed/qaq-p/778956
2. https://scalegrid.io/blog/configuring-and-managing-ssl-on-your-mysql-server/

1. https://community.atlassian.com/t5/Confluence-questions/MySQL-Public-Key-Retrieval-is-not-allowed/qaq-p/778956
2. https://scalegrid.io/blog/configuring-and-managing-ssl-on-your-mysql-server/

回答by Carrier Pigeon Protocol

The SSL connection to the database is failing, try changing your datasource URL to:

与数据库的 SSL 连接失败，尝试将数据源 URL 更改为：

spring.datasource.url=jdbc:mysql://localhost:3306/employee_database?useSSL=false

回答by Michael Técourt

The warning looks like a MySQL driver bug with Java 11 and SSL enabled : https://bugs.mysql.com/bug.php?id=93590
Deactivating encryption because of a driver warningis a bad idea.

该警告看起来像是启用了 Java 11 和 SSL 的 MySQL 驱动程序错误：https: //bugs.mysql.com/bug.php?
id =93590 由于驱动程序警告而停用加密是一个坏主意。

Your insertion problem looks more like a classic transaction issue though, I doubt it is related to the SSL warning.

您的插入问题看起来更像是一个经典的事务问题，但我怀疑它与 SSL 警告有关。

回答by Arvind Kumar

I also faced the same issue. if you look the the stacktrace, it's cleary mentioned what to do -

我也面临同样的问题。如果您查看堆栈跟踪，很明显提到要做什么-

Sat Mar 16 09:00:01 IST 2019 WARN: 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.

so after disabling the ssl by making changes in data source url solved the problem -

因此通过更改数据源 url 禁用 ssl 解决了问题-

spring.datasource.url=jdbc:mysql://localhost:3306/security?useSSL=false

回答by Chethan

I faced a similar issue when I upgraded the Java version in my server to 11 from 8.

当我将服务器中的 Java 版本从 8 升级到 11 时，我遇到了类似的问题。

The spring boot started supportingJava 11 from 2.1 and onwards. So make sure your project's dependencies are also updated accordingly. This is relevant for this answer as SpringBoot also influence MySQL connector, Hibernate core, and other dependencies.

Spring Boot从 2.1开始支持Java 11。因此，请确保您的项目的依赖项也相应地更新。这与此答案相关，因为 SpringBoot 也会影响 MySQL 连接器、Hibernate 核心和其他依赖项。

The inability to connect to DB was resulting in some more NoClassDefFoundErrors. So make sure you solve this first before looking into other errors.

无法连接到数据库导致更多的 NoClassDefFoundErrors。因此，请确保在查看其他错误之前先解决此问题。

An example pom dependency for the SpringBoot starter

SpringBoot starter 的 pom 依赖示例

<parent>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-parent</artifactId>
    <version>2.1.12.RELEASE</version>
    <relativePath />
</parent>

Hope this helps someone.

希望这可以帮助某人。

回答by Hyman J

I had this issue and decided to use Carrier Pigeon Protocol's solutionuntil I accidentally solved it by updating Tomcat from version 9.0.12 to 9.0.16.

我遇到了这个问题并决定使用Carrier Pigeon Protocol 的解决方案，直到我通过将 Tomcat 从 9.0.12 版本更新到 9.0.16 不小心解决了它。