Analysis and solution for MySQL connection interruption problem

1. Error background

1.1 Error log analysis

In Flask application, a database query is triggered when a user uploads a file, but the MySQL connection is unexpectedly interrupted, causing the request to fail. The key error message is as follows:

: (2013, 'Lost connection to MySQL server during query')

The complete call stack is displayed, SQLAlchemy is executingSELECTThe underlying PyMySQL connection is lost during query.

1.2 Error effects

• User request returns500Error affects user experience.
• Database query failure may result in inconsistent data or interruption of business logic.

2. Analysis of the cause of errors

2.1 MySQL server timeout

MySQL defaultwait_timeoutandinteractive_timeoutUsually set to28800seconds (8 hours), but if the connection is idle for a long time, MySQL will actively close it. If the app does not manage the connection pool correctly, you may try to use a closed connection.

2.2 Network instability

• If MySQL is deployed on a remote server, network fluctuations can cause TCP connection to be interrupted.
• A firewall or proxy server may proactively terminate an idle connection for a long time.

2.3 The query execution time is too long

If the query involves large table scans or complex calculations, it may exceed MySQL'smax_execution_timeRestriction, causing the connection to be terminated.

2.4 Database server issues

• MySQL service crashes or restarts.
• The server resources (CPU, memory, disk) are insufficient, resulting in the connection being forced to be closed.

2.5 Improper connection pool management

If you use SQLAlchemy or PyMySQL connection pooling, it may be that the failed connection is returned without a health check.

3. Solution

3.1 Adjust MySQL timeout settings

-- Check the current timeout settings
SHOW VARIABLES LIKE 'wait_timeout';
SHOW VARIABLES LIKE 'interactive_timeout';

-- Modify timeout（unit：Second）
SET GLOBAL wait_timeout = 28800;
SET GLOBAL interactive_timeout = 28800;

Optimization suggestions:

• If the application has a long-term idle connection, it can be appropriately addedwait_timeout。
• exist(MySQL configuration file) permanently takes effect:

[mysqld]
wait_timeout = 28800
interactive_timeout = 28800

3.2 Optimizing SQL Query

Ensure efficient queries and avoid full table scanning:

-- Check index status
EXPLAIN SELECT * FROM user WHERE id = 11;

-- Add an index（If missing）
ALTER TABLE user ADD INDEX idx_id (id);

Optimization suggestions:

• useEXPLAINAnalyze query performance.
• avoidSELECT *, only query the necessary fields.

3.3 Optimization of connection pool using SQLAlchemy

from sqlalchemy import create_engine
from  import QueuePool

engine = create_engine(
    'mysql+pymysql://user:password@host:3306/db',
    pool_size=10,          # Connection pool size    max_overflow=5,        # Temporary connections allowed when pool_size is exceeded    pool_recycle=3600,     # Recycle the connection after 1 hour to avoid being closed by MySQL    pool_pre_ping=True,    # Check whether the connection is valid before execution    pool_timeout=30        # Get the timeout of the connection (seconds))

Optimization suggestions:

• pool_recycleShould be less than MySQLwait_timeout, avoid using failed connections.
• pool_pre_ping=TrueIts validity is checked before each use of the connection, but it increases slightly latency.

3.4 Adding automatic retry mechanism

from  import OperationalError
import time

def execute_with_retry(session, query, max_retries=3, retry_delay=1):
    for attempt in range(max_retries):
        try:
            result = (query)
            return result
        except OperationalError as e:
            if attempt == max_retries - 1:
                raise  # The number of retrys is exhausted and an exception is thrown            (retry_delay)
            continue

Optimization suggestions:

• Applicable to key business logic, such as order processing, payment, etc.
• Avoid unlimited retry and set reasonablemax_retries(such as 3 times).

3.5 Monitoring and Alarm

usePrometheus + GrafanaMonitor MySQL connection status:

#Configuration examplescrape_configs:
  - job_name: 'mysql'
    static_configs:
      - targets: ['mysql-exporter:9104']

Key monitoring indicators:

• mysql_global_status_aborted_connects (number of exception connections)
• mysql_global_status_connection_errors_total (total number of connection errors)
• mysql_global_variables_wait_timeout (current timeout setting)

4. Preventive measures

4.1 Regularly maintain database

-- Optimize table structure
OPTIMIZE TABLE user;

-- Clean useless connections regularly
KILL IDLE CONNECTION <process_id>;

4.2 Using the Health Check Middleware

Add database health check endpoints in Flask:

from flask import Flask, jsonify
import sqlalchemy

app = Flask(__name__)

@('/health')
def health_check():
    try:
        with () as conn:
            ("SELECT 1")
        return jsonify({"status": "healthy"})
    except :
        return jsonify({"status": "unhealthy"}), 500

4.3 Using a high availability architecture

• Master-slave replication: Avoid single point of failure.
• Read and write separation: Reduce the pressure on the main library.
• Cloud database: such as AWS RDS or Alibaba Cloud RDS, which provides automatic failover.

5. Summary

By rationally configuring MySQL, optimizing query, managing connection pools, and adding an automatic retry mechanism, it can effectively reduce connection interruption problems and improve system stability.

The above is the detailed content of MySQL connection interruption problem analysis and solution. For more information about MySQL connection interruption problem, please pay attention to my other related articles!