Analyze several common solutions to MySQL exceptions

Preface

The error is as follows:

Could not open JDBC Connection for transaction; nested exception is java.sql.SQLException: Connections could not be acquired from the underlying database!

For this type of error, just look at the explanation. In one sentence: The JDBC driver threw an exception and could not connect to the database.

1. The database name or password configured by the code is inconsistent with the local database

1.1. Error description

The first and most common error is that the database name or password configured by the code is inconsistent with the local database, and an exception is thrown.

As shown in the figure above, in the configuration file, the name attribute in front is the default and does not need to be changed. For the user name, it is generally root. You can directly view the local configuration through the database management software. The database password is set by you.

1.2 Solution

Modify the name and password of the configuration file.

2. The imported non-local project file does not match the local database version

2.1. Error description

The second common error: The non-local project file imported using IDE (Eclipse for example) does not match the local database version.

For example: the jar package you import into your eclipse project depends on version 8.0, but the MySQL database you installed locally is version 5.0, so an error will naturally occur.

2.2 Solution

Find Referenced Libraries in the project you imported, right-click → Build Path → Configure Build Path…Remove the 8.0 MySQL driver that the project depends on, and add the 5.0 version you installed locally.

3. Error in loading driver package in higher version of MySQL configuration (taking MySQL 8.0 as an example)

This error is caused by the change of the driver package after the MySQL version is updated. The new driver class is com.mysql.cj.jdbc.Driver.

3.1. Error description

The error is as follows:

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.

Error Description: The driver class 'com.mysql.jdbc.Driver' is deprecated. The new driver class is 'com.mysql.cj.jdbc.Driver'. The driver is automatically registered via SPI, so there is usually no need to manually load the driver class.

3.2 Solution

Change the driver class com.mysql.jdbc.Driver used by MySQL database 5.0 to com.mysql.cj.jdbc.Driver. The driver is automatically registered through SPI, and usually there is no need to manually load the driver class. The modified configuration file is shown in the figure below:

<!-- Load database driver-->
<property name="driverClass" value="com.mysql.cj.jdbc.Driver"></property>

3.3. How to view driver classes of different versions of MySQL

For the MySQL driver class, when we check each version, we can directly check where the driver is located by clicking on the driver jar package. For example, the 8.0 version is com.mysql.cj.jdbc.Driver, as shown in the following figure:

4. Database connection string high version configuration error (time zone problem)

Note: You need to configure the time zone after MySQL version 6.0.

4.1. Error description

The error is as follows:

2020-01-14 00:45:30,876 ERROR [DruidDataSource.java:616] : init datasource error 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.

4.2 Solution

We can take advantage of time zone support by configuring the server or JDBC driver (via the serverTimezone configuration property) to use a more specific time zone value.

The configuration files are as follows:

<!-- Database connection string -->
<property name="jdbcUrl" value="jdbc:mysql://localhost:3306/demo?serverTimezone=GMT%2B8&amp;characterEncoding=utf-8"></property>

5. Why is there an 8-hour difference in writing data into the database?

5.1. Error description

Java database connection uses the UTC time zone (Coordinated Universal Time), that is, serverTimezone=UTC, and Beijing time is 8 hours earlier than UTC time, that is, UTC+08:00. If we directly use serverTimezone=UTC, the data written to the database will be 8 hours ahead. If the following configuration is used, the data written into the database will be 8 hours apart:

<!-- Database connection string -->
<property name="jdbcUrl" value="jdbc:mysql://localhost:3306/demo?serverTimezone=UTC&amp;characterEncoding=utf-8"></property>

5.2 Solution

We can modify the serverTimezone to Beijing time GMT%, Shanghai time Asia/Shanghai or Hongkong time Hongkong.

The configuration file is as follows:

<!-- Database connection string -->
<property name="jdbcUrl" value="jdbc:mysql://localhost:3306/demo?serverTimezone=GMT%2B8&amp;characterEncoding=utf-8"></property>

In addition, the my.ini configuration file in MySQL can also be modified. This article is only used to solve the corresponding problem, so it will not be described in detail.

6. SSL connection issues

6.1. Error description

The error is as follows:

Sun Oct 14 00:45:30 CST 2018 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.

Error Description: Establishing an SSL connection without server authentication is not recommended. According to the SSL connection requirements of MySQL 5.5.45+, 5.6.26+, and 5.7.6+, if the connection mode is not set, an SSL connection must be established by default. For existing applications that do not use SSL, the server's verify-certificate property is set to "false". You need to either explicitly disable SSL by setting useSSL=false, or set useSSL=true and provide a verified certificate for the server.

6.2 Solution

• Add useSSL = false to the database URL connection address, which is suitable for testing.
• Add useSSL = true to the database URL connection address and provide the server's authentication certificate.

The configuration file is as follows:

 <!-- Database connection string -->
<property name="jdbcUrl" value="jdbc:mysql://localhost:3306/demo?useSSL=false&amp;serverTimezone=GMT%2B8&amp;characterEncoding=utf-8"></property>

Summarize

By summarizing several common solutions to MySQL exceptions, you can deepen your understanding of the underlying MySQL. As long as you are doing development, mistakes will happen constantly, so you must be good at summarizing. At the same time, you must fully grasp the underlying principles of development, and as a developer you must keep abreast of different version iterations, otherwise you will never keep up with technological development.

The above is a detailed analysis of several common solutions to MySQL exceptions. For more information on solutions to MySQL exceptions, please pay attention to other related articles on 123WORDPRESS.COM!