Python/Flask/MySQL应用启动报错：max_user_connections资源超限问题排查与解决求助

我在运行一个基于Python/Flask/MySQL的应用时，执行python -m flask run启动就遇到了这个错误：

mysql.connector.errors.ProgrammingError: 1226 (42000): User [username] has exceeded the 'max_user_connections' resource (current value: 10)

最奇怪的是，我甚至还没尝试建立任何数据库连接就触发了这个错误。之后我在代码里加了try/except/else/finally块来确保连接关闭，但问题还是没解决。

from flask import Flask, redirect, render_template, request, session
from flask_session import Session
from tempfile import mkdtemp
from werkzeug.security import check_password_hash, generate_password_hash
from helpers import login_required, check_available_chars
import mysql.connector.pooling

app = Flask(__name__)

app.config["TEMPLATES_AUTO_RELOAD"] = True
app.config["SESSION_FILE_DIR"] = mkdtemp()
app.config["SESSION_PERMANENT"] = False
app.config["SESSION_TYPE"] = "filesystem"
app.secret_key = [secret key]
app.config["SESSION_PERMANENT"] = True
app.config["SESSION_TYPE"] = "filesystem"
Session(app)

cnxpool = mysql.connector.pooling.MySQLConnectionPool(
    pool_name="name",
    pool_size=10,
    autocommit=True,
    user=[user name],
    password=[password],
    host=[host],
    database=[database]
)

@app.route("/login", methods=["GET", "POST"])
def login():
    """Log user in"""
    # Forget any user_id
    session.clear()

    # User reached route via POST (as by submitting a form via POST)
    if request.method == "POST":
        # Ensure username was submitted
        if not request.form.get("businessName"):
            return render_template("login.html", errorMessage = "must provide business name")

        # apostrophes and semicolons not allowed in username, to avoid SQL conflicts
        if ';' in request.form.get("businessName") or '\'' in request.form.get("businessName"):
            return render_template("login.html", businessName=request.form.get("businessName"), errorMessage = "Invalid business name")

        # Ensure password was submitted
        elif not request.form.get("password"):
            return render_template("login.html", businessName=request.form.get("businessName"), errorMessage = "must provide password")

        try:
            conn = cnxpool.get_connection()
            cur = conn.cursor(dictionary=True)
            cur.execute("SELECT * FROM businesses WHERE name = %s", (request.form.get("businessName"),))
            rows = cur.fetchall()
        except mysql.connector.Error as sqlerr:
            print(sqlerr)
        except Exception as err:
            print(err)
        else:
            # Ensure username exists and password is correct
            if len(rows) != 1 or not check_password_hash(rows[0]['password'], request.form.get("password")):
                cur.close()
                conn.close()
                return render_template("login.html", businessName=request.form.get("businessName"), errorMessage = "Invalid business name and/or password")
            else:
                # Remember which user has logged in (if in login page)
                session["user_id"] = rows[0]['businessID']
                # Redirect user to message entry page
                cur.close()
                conn.close()
                return redirect("/")
        finally:
            if (cur):
                cur.close()
            if (conn):
                conn.close()

    # User reached route via GET (as by clicking a link or via redirect)
    else:
        return render_template("login.html")

• 将连接池大小（pool size）设置为大于10；
• 通过MySQL Workbench连接数据库，执行SHOW PROCESSLIST查看进程并使用KILL [id]终止进程；
• 在Heroku控制台重启所有dyno；
• 在Visual Studio Code中执行命令heroku restart --app [appname]重启应用。

这个问题我之前维护Flask应用时也碰到过，核心原因其实是连接池在Flask应用初始化阶段就会尝试建立所有配置的池连接，而不是等到你第一次调用get_connection()的时候。你设置的pool_size=10刚好等于MySQL用户的max_user_connections上限，加上Heroku环境下旧连接可能没有被正确回收，导致应用启动时直接占满所有允许的连接，触发了错误。

下面是几个有效的解决步骤：

1. 降低连接池大小，预留余量
   把pool_size设置为小于10的值，比如8，这样给其他可能存在的连接（比如你用Workbench的会话）留一些空间：

   cnxpool = mysql.connector.pooling.MySQLConnectionPool(
       pool_name="name",
       pool_size=8,  # 设为小于max_user_connections的值
       autocommit=True,
       user=[user name],
       password=[password],
       host=[host],
       database=[database]
   )
   

2. 添加连接池的超时和自动回收配置
   给连接池加上空闲超时和连接重置参数，确保失效的空闲连接被自动回收，并且获取连接时验证有效性：

   cnxpool = mysql.connector.pooling.MySQLConnectionPool(
       pool_name="name",
       pool_size=8,
       autocommit=True,
       user=[user name],
       password=[password],
       host=[host],
       database=[database],
       pool_reset_session=True,  # 回收连接时重置会话
       connection_timeout=30     # 30秒后回收空闲连接
   )
   

   另外，获取连接后可以执行SELECT 1;简单测试连接是否存活，避免使用失效连接。

3. 延迟连接池初始化
   不要在应用启动时就创建连接池，而是在第一次需要数据库操作的时候再初始化，这样可以避免启动阶段就占用所有连接：

   cnxpool = None
   
   def get_db_pool():
       global cnxpool
       if not cnxpool:
           cnxpool = mysql.connector.pooling.MySQLConnectionPool(
               pool_name="name",
               pool_size=8,
               autocommit=True,
               user=[user name],
               password=[password],
               host=[host],
               database=[database],
               pool_reset_session=True,
               connection_timeout=30
           )
       return cnxpool
   
   # 在login路由里这样使用：
   conn = get_db_pool().get_connection()
   

4. 用上下文管理器优化连接释放
   虽然你加了finally块，但使用with上下文管理器可以更可靠地自动管理游标和连接的关闭，避免遗漏：

   try:
       conn = get_db_pool().get_connection()
       with conn.cursor(dictionary=True) as cur:
           cur.execute("SELECT * FROM businesses WHERE name = %s", (request.form.get("businessName"),))
           rows = cur.fetchall()
       # 游标会被with块自动关闭
   except mysql.connector.Error as sqlerr:
       print(sqlerr)
   finally:
       if conn.is_connected():
           conn.close()
   

5. 确认Heroku MySQL插件的实际限制
   如果你用的是Heroku的MySQL插件（比如ClearDB），可以通过执行SHOW VARIABLES LIKE 'max_user_connections';查看准确的连接上限。部分免费/基础套餐可能会预留少量连接给插件内部使用，所以你的池大小要比显示的上限再小一点。

内容的提问来源于stack exchange，提问作者Ian McInnes