This article is participating in the Java Theme Month – Java Debug Notes Event, see the event link for details

The problem background

The operation personnel selected a batch of data in the system, and then submitted the request for processing. After a period of time, the operation personnel went to the development and said, Part of the data I submitted was successful, and the rest part is still in the processing state and has not been updated.

Location analysis idea

Through kibana log tracing (business log in code), as shown in the figure, the request was indeed accepted and the dependent microservice returned the result, but the final modification of the database corresponding data state was not performed!

At first, I thought it was a code problem, but the code review did not find the problem again. Moreover, the operation department said it was partially successful, and they were asked to operate again. Now that the review code doesn’t find the problem, look again at the log to see if there are any other errors.

After discovering the log.info() log from the code, the next step is to update the data operation, so I want to see the surrounding log to see what’s going on, click here, as shown

Found one near anothererror log

To analyze the meaning of this passage:

discard connection: Communications link failure: He last packet successfully received from the server was 1,927,021 milliseconds ago. The last packet sent Successfully to the server was 0 milliseconds ago: The last time it received a packet from the mysql server was 1,927,021 milliseconds ago. The last successful message sent to the server was 0 milliseconds agoCopy the code

To solve the problem

Cause of the error: The MySQL service was disconnected after a long period of disconnection. The first request after disconnection will throw this exception

  1. Check that your database connection address (the URL in the configuration file) is correct.
  2. Restart the application. (Not recommended, treating the symptoms rather than the root cause)
  3. mysql5Previous versions can be attached directly to the CONFIGURATION of JDBC connection urlsautoReconnect=true.
  4. Mysql > select * from system where connection time is specified;show variables like '%timeout%'
    • Interactive_timeout: Default value: 28800 seconds (8 hours) Number of seconds that the server waits for the interactive connection to be closed. (Interactive clients are defined as clients that use the CLIENT_INTERACTIVE option in mysQL_real_connect ())
    • Wait_timeout: Default value: 28800 seconds (8 hours)The number of seconds the server waits to be active before closing a non-interactive connection. At thread startup, according to globalwait_timeoutValue or globalinteractive_timeoutValue initializes the sessionwait_timeoutValue, depending on the client type (defined by the connection option CLIENT_INTERACTIVE of mysQL_real_connect ()).

Mysql5 closes a database Connection (java.SQL.Connection) if it has been waiting for wait_timeout seconds. At this point, your Java application’s connection pool still legally holds a reference to that connection. The above error occurs when the connection is used for database operations.

  1. Change mysql global wait_timeout to the maximum value. Looking at the mysql5 manual, you can see that the maximum wait_timeout for Windows and Linux is 24 and 365 days, respectively.

    1. Add a line at the end of the file my.ini: wait_timeout=1814400. (This file is in the mysql installation directory on Windows and /etc/my.ini on Linux.)
    2. Restart the mysql.

  2. If your problem is still not resolved after the above steps, you are advised to change the version of the mysql driver in your application.

Expand the knowledge

Mysql JDBC URL format

jdbc:mysql://[host:port],[host:port]... /[database][? Parameter name 1][= parameter name 1][& parameter name 2][= parameter name 2]... When using database connection pooling, it is best to set the following two parametersautoReconnect=true&failOverReadOnly=false

Mysql and JDBC operation documents

Mysql5.1 JDBC official documentation

Mysql8.0 JDBC official documentation

Connection leakage problem

Forgetting to close a connection after getting a connection from the connection pool, connections to the pool will gradually reach maxActive until the connection pool is no longer available. You can use the following methods to avoid this problem:

# to connect a long connection closed druid. RemoveAbandoned: true # if the connection is established more than 30 minutes, Is forced to shut it down druid. RemoveAbandonedTimeout: 1800 # current closing motion logged druid. LogAbandoned: trueCopy the code

RemoveAbandoned is an advanced feature of connection pooling. In theory, this configuration should not occur in a real production environment because sometimes applications perform transactions and, in this case, can be mistakenly reclaimed by connection pooling. RemoveAbandoned is usually enabled during application testing to locate specific code locations of connection leaks. Closing connections in production should be guaranteed by the program itself.

👍 ❤️ Don’t get lost

The article continues to update every week, you can search wechat "ten minutes to learn programming" the first time to read and urge more, if this article is not bad, feel something if you support and recognition, is the biggest power of my creation, we will see the next article!Copy the code