The WEB project on the server repeatedly fails to connect to the MySQL database.

The WEB project on the server repeatedly fails to connect to the MySQL database.

• One reason is that the scheduled task plan for MySQL is not closed. MySQL runs an automatically updated scheduled task plan by default every morning. If the scheduled task plan is not closed, the connection is automatically disconnected.
  Solution:
  1. This is a basic permission issue. Go to the MySQL installation directory, right-click the MySQL folder, go to the Security tab, click "edit user group", and select your computer user from "group and user, select all items that are allowed, apply them, and disable them.
  2. This is a Windows Task Scheduler Service. Right-click the task scheduler and choose delete from the shortcut menu, choose computer management, scheduler, scheduler library, MySQL, Installer, and ManifestUpdate, right-click and select "Disable ".

• Another reason is that the database connection of the MySQL database has a life limit. If no database connection object is operated within the specified time, the connection will be closed. That is, the eight-hour MySQL problem.

• The default value of "wait_timeout" For MySQL server Connection is 8 hours. That is to say, if a Connection is idle for more than 8 hours, MySQL will automatically disconnect the Connection. However, the database connection pool does not know that the connection has been disconnected. If the program uses this disconnected connection, the program will report an error.

Let's take a look at the database connection pool:

• To operate a database using JAVA code, a database connection object is required. A user must use at least one connection. Now, if there are more than one million users, a very large number of connected objects will be created, which puts the database under great pressure. To solve this problem, a technology has emerged, this is the database connection pool.

• The so-called database connection pool can be seen as creating a "pool" between the user and the database. There are several connection objects in this pool. When the user wants to connect to the database, he must first obtain the connection object from the connection pool, then operate the database. Once the connection object in the connection pool is removed, the next user who wants to operate the database must wait, wait for another user to release the connection object and put it back in the connection pool. At this time, the user waiting for the connection object can get the connection object, to operate the database.

• The following is a simple implementation of the two connection pools ~
  Code from: https://www.cnblogs.com/vmax-tam/p/4158802.html
  Code from: https://www.cnblogs.com/xiaotiaosi/p/6398371.html

I will not move the code here. I will focus on how to solve the problem.

Method 1: Modify the database wait time

1. First restart the MySQL service, start → right-click → Computer Management → service, find the MySQL service, and restart

2. Set the maximum MySQL wait time, run MySQL 5.7 Command Line Client, enter the password, and copy and run the following code segment:

show variables LIKE '%timeout%';show global variables LIKE '%timeout%';set global wait_timeout=2147483;set wait_timeout=2147483;set global interactive_timeout=31536000;set interactive_timeout=31536000;

3. Restart Tomcat

Method 2: When the database connection pool Code Compiled by myself is used to generate a database connection object, an automatic refresh function is added to automatically generate a new database connection object at an interval. That is, regular use of connections in the connection pool so that they will not be disconnected by MySQL due to idle timeout.

Method 3: Add autoReconnect = true when connecting to the database:

jdbc:mysql://localhost:3306/accounant?useUnicode=true&characterEncoding=UTF-8&autoReconnect=true

Method 4: Reduce the connection life cycle in the connection pool so that it is smaller than the value of "wait_timeout" set in the preceding items.

Method 5: Set "testOnBorrow" to false, "testWhileIdle" to true, and "testBetweenEvictionRunsMillis" to be set (less than 8 hours ). The connections that are closed by MySQL will not be cleared out, avoiding the "eight-hour problem ".
Example: http://www.totcms.com/html/201602-29/20160229114145.htm

For configuration examples in the Apache official documentation, see: http://tomcat.apache.org/tomcat-9.0-doc/