MySQL database connection exception summary (worth collecting)

I found a strange problem when deploying the project on Centos. The database connection kept throwing exceptions. So I spent two hours searching for various causes of database connection anomalies, and finally the problem was solved. At the same time, summarize the abnormal information collected during the solution process to give you ideas when you encounter similar problems. A must-have.

Problem phenomenon

Let me first talk about the problem I encountered. The problems encountered in the project are very strange. The Mysql database is installed on Centos and the project uses Spring Boot.

The project starts locally and connects to the server database normally, the local database client connects to the server database normally, and the server local connection client connects to the database normally. The only exception thrown is when the project is deployed to the server and started.

The exception information is as follows (the exception information was not retained at that time):

com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure

The last packet sent successfully to the server was 0 milliseconds ago. The driver has not received any packets from the server.
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(Unknown Source)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source)
at java.lang.reflect.Constructor.newInstance(Unknown Source)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:425)
at com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:989)
at com.mysql.jdbc.MysqlIO.<init>(MysqlIO.java:341)
at com.mysql.jdbc.ConnectionImpl.coreConnect(ConnectionImpl.java:2196)
at com.mysql.jdbc.ConnectionImpl.connectOneTryOnly(ConnectionImpl.java:2229)
at com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2024)
at com.mysql.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:779)
at com.mysql.jdbc.JDBC4Connection.<init>(JDBC4Connection.java:47)
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(Unknown Source)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source)
at java.lang.reflect.Constructor.newInstance(Unknown Source)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:425)
at com.mysql.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:389)
at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:330)
at java.sql.DriverManager.getConnection(Unknown Source)
at java.sql.DriverManager.getConnection(Unknown Source)
at com.ad.MysqlDemo.main(MysqlDemo.java:32)
Caused by: java.net.ConnectException: Connection refused: connect
at java.net.DualStackPlainSocketImpl.connect0(Native Method)
at java.net.DualStackPlainSocketImpl.socketConnect(Unknown Source)
at java.net.AbstractPlainSocketImpl.doConnect(Unknown Source)
at java.net.AbstractPlainSocketImpl.connectToAddress(Unknown Source)
at java.net.AbstractPlainSocketImpl.connect(Unknown Source)
at java.net.PlainSocketImpl.connect(Unknown Source)
at java.net.SocksSocketImpl.connect(Unknown Source)
at java.net.Socket.connect(Unknown Source)
at com.mysql.jdbc.StandardSocketFactory.connect(StandardSocketFactory.java:211)
at com.mysql.jdbc.MysqlIO.<init>(MysqlIO.java:300)
... 15 more

Abnormal cause

Tried almost all the solutions on the Internet but to no avail. Gradually I began to suspect that JDK was to blame. The JDK version used locally is 1.8.0_151, and the server uses 1.8.0.242. In theory, there is no impact.

So I uninstalled the jdk on the server, downloaded the installation package from the official website, and reinstalled 1.8.0_241. The database connection problem disappeared.

Later I thought about it carefully, and realized that it was not a problem with the minor version number, but a problem with the installed JDK version. The JDK installed on this machine was downloaded from the Oracle official website, and the storage on the server was installed directly using the yum command for convenience. OpenJDK is installed by default on centos. We know that after jdk7, JDK and OpenJDK belong to two versions with different licensing agreements, and the OpenJDK source code is incomplete and OpenIDK only contains the most streamlined JDK.

Below we share other reasons found during the search for the above exceptions that lead to similar exceptions and their solutions.

sock path problem

The problem phenomenon is the same as above. Except that the server-deployed application cannot connect to the server, other methods can connect to the database.

The cause of the problem is: the server has two disks, and the mv command was executed in the middle to move the storage content of the database, and at the same time modified the datadir to point to the new directory.

Result: The local connection using the JAVA program failed, and the org.apache.commons.dbcp.SQLNestedException: Cannot create PoolableConnectionFactory (Communications link failure exception was thrown.

Solution: After modifying the corresponding datadir configuration, check the configuration of the mysql.sock file path. By default it will be in /var/lib/mysql/mysql.sock or /temp/mysql.sock. Then, modify all terminals ([client], [mysql], [mysqld], etc.) to use the same path.

SSL connection issues

If the following abnormal information appears in a message:

javax.net.ssl.SSLHandshakeException: No appropriate protocol (protocol is disabled or cipher suites are inappropriate)
at sun.security.ssl.Handshaker.activate(Handshaker.java:529)
at sun.security.ssl.SSLSocketImpl.kickstartHandshake(SSLSocketImpl.java:1492)
at sun.security.ssl.SSLSocketImpl.performInitialHandshake(SSLSocketImpl.java:1361)
at sun.security.ssl.SSLSocketImpl.startHandshake(SSLSocketImpl.java:1413)
at sun.security.ssl.SSLSocketImpl.startHandshake(SSLSocketImpl.java:1397)

There may be a problem with the SSL connection. A friend on the Internet encountered the above exception after upgrading to jdk1.8.

Solution: Remove SSLv3. Find the configuration related to jdk.tls.disabledAlgorithns=SSLv3,... in the JAVA_HOME/jre/lib/security/java.security file and delete the SSLv3 part. Deleting SSLv3 allows SSL calls.

Regarding the SSL connection problem, there is another situation where Mysql uses SSL connection. For how to configure, please refer to this article: https://www.jb51.net/article/100432.htm.

Database connection timeout

This situation is the mainstream information on the Internet, and there are a large number of articles, but they often do not explain the specific scenario: an exception similar to the above occurs during the use of the application. Note that this is during use, not during startup.

The reason for the exception during use is that the default "wait_timeout" of the MySQL server is 8 hours (28800 seconds), that is, if a connection is idle (inactive) for more than 8 hours, MySQL will automatically disconnect the connection. However, the connection pool believes that the connection is still valid (because the validity of the connection is not verified). When the application applies to use the connection, it will cause the above error.

Solution: Modify the my.ini configuration, increase the timeout period or add "&autoReconnect=true" to the connection URL.

Add the following configuration below port=3306:

wait_timeout=31536000
interactive_timeout=31536000

Then restart MySQL.

This situation may also be caused by the maxIdleTime configuration of the database connection pool.

<!-- Maximum idle time, if not used within 60 seconds, the connection will be discarded. If it is 0, it will never be discarded. Default: 0 -->  
<property name="maxIdleTime" value="0"></property>

Since the MySQL connection is closed if it is idle for more than 8 hours, but the connection pool never discards the connection and considers the connection to be valid (because the validity of the connection is not verified), when the application applies to use the connection, it will cause the above error.

Solution: Set value to 20.

Other causes

Of course, there are many other reasons for similar exceptions in MySQL connections:

• Database account access rights issue: specify IP and account authorization;
• Network permission issue: Is the firewall enabled with corresponding access permissions?
• Port problem: Is the accessed port correct? Is the firewall permission enabled on the port?
• Account password problem: The account password is incorrect or the account does not have access rights to the specified IP address;
• Database driver problem: The database driver version does not match the database version.
• Network stability issues: problems caused by unstable network.
• Database connection pool problem: The database connection pool is configured too large, resulting in insufficient default MySQL connections.
• The problem of ipv4 and ipv6.

The above is the detailed summary of MySQL database connection exceptions. For more information about MySQL database connection exceptions, please pay attention to other related articles on 123WORDPRESS.COM!