MariaDB Connector/Python预编译语句未释放引发超出最大预编译语句数错误的问题求助

大家好，我遇到了一个MariaDB Connector/Python的奇怪问题，折腾了好久没找到根源，想请教下社区的各位：

环境信息 #

• 操作系统：Ubuntu 20.04.6 LTS
• MariaDB Python库版本：1.1.10 和 1.1.12（当前使用1.1.12版本）
• MariaDB服务器版本：10.3 和 11.7.1-rc（两个版本均能复现问题）

问题现象 #

我写了一个批量插入数据的脚本，运行到一定次数后会抛出错误：

mariadb.OperationalError: Can't create more than max_prepared_stmt_count statements (current value: 16382)

这个错误说明后端数据库的预编译语句数量已经达到了配置上限。按道理说，如果我每次执行后都保留游标对象不关闭，触发这个错误很正常，但我明明用了with上下文管理器来管理游标——理论上with块结束后游标会自动关闭，而且我甚至在循环里手动加了cursor.close()，错误还是会出现，并且刚好是在执行了16382次cursor.executemany()之后触发的。

我的测试脚本如下：

import mariadb
import json

test_connection_params = {
    'user': MARIADB_USERNAME,
    'password': MARIADB_PASSWORD,
    'host': MARIADB_HOST_ADDRESS,
    'port': 3306,
    'database': MARIADB_DATABASE_NAME
}

def main():
    conn = mariadb.connect(**test_connection_params)

    with conn.cursor() as cursor:
        cursor.execute("""
            CREATE TABLE IF NOT EXISTS my_table (
                id INT AUTO_INCREMENT PRIMARY KEY,
                name VARCHAR(128),
                description VARCHAR(2000),
                embedding TEXT NOT NULL
            );
        """)
        conn.commit()

    data = [(f"Product {i}", f"Description for product {i}", json.dumps([i*0.01, i*0.02, i*0.03, i*0.04])) for i in range(1_000_000)]
    query = """
    INSERT INTO my_table (name, description, embedding)
    VALUES (?, ?, ?)
    """

    chunk_size = 10

    for i in range(0, len(data), chunk_size):
        with conn.cursor() as cursor:
            cursor.executemany(query, data[i:i + chunk_size])
            conn.commit()

    conn.close()

    cursor.close()
    conn.close()

我做的其他测试 #

1. 换用mypyql连接器：用完全相同的批量插入逻辑，不会触发这个预编译语句超限的错误；
2. 改用MariaDB VECTOR字段：把表中的embedding TEXT字段换成MariaDB 11.7.1-rc的新特性VECTOR(4)，调整插入语句用VEC_FromText()转换数据后，错误消失了，脚本如下：

import mariadb
import json

test_connection_params = {
    'user': MARIADB_USERNAME,
    'password': MARIADB_PASSWORD,
    'host': MARIADB_HOST_ADDRESS,
    'port': 3306,
    'database': MARIADB_DATABASE_NAME
}

def main():
    conn = mariadb.connect(**test_connection_params)

    with conn.cursor() as cursor:
        query = """
        CREATE TABLE IF NOT EXISTS products (
            id INT AUTO_INCREMENT PRIMARY KEY,
            name VARCHAR(128),
            description VARCHAR(2000),
            embedding VECTOR(4) NOT NULL
        );
        """
        cursor.execute(query)
        conn.commit()

    conn = mariadb.connect(**test_connection_params)

    data = [(f"Product {i}", f"Description for product {i}", json.dumps([i*0.01, i*0.02, i*0.03, i*0.04])) for i in range(1_000_000)]
    query = "INSERT INTO products (name, description, embedding) VALUES (?, ?, VEC_FromText(?))"

    chunk_size = 10
    for i in range(0, len(data), chunk_size):
        with conn.cursor() as cursor:
            cursor.executemany(query, data[i:i + chunk_size])
            conn.commit()

    conn.close()

3. 封装成Python类后问题复现：但当我把数据库操作封装成DatabaseManager类之后，哪怕用的是VECTOR字段，之前已经消失的错误又回来了！脚本如下：

import mariadb
import json

test_connection_params = {
    'user': MARIADB_USERNAME,
    'password': MARIADB_PASSWORD,
    'host': MARIADB_HOST_ADDRESS,
    'port': 3306,
    'database': MARIADB_DATABASE_NAME
}

class DatabaseManager:
    def __init__(self, config):
        self.connection = mariadb.connect(**config)

    def create_table_if_not_exists(self):
        query = """
        CREATE TABLE IF NOT EXISTS products (
            id INT AUTO_INCREMENT PRIMARY KEY,
            name VARCHAR(128),
            description VARCHAR(2000),
            embedding VECTOR(4) NOT NULL
        );
        """
        with self.connection.cursor() as cursor:
            cursor.execute(query)
            self.connection.commit()

    def insert_rows(self, chunk):
        query = """
        INSERT INTO products (name, description, embedding)
        VALUES (?, ?, VEC_FromText(?))
        """
        with self.connection.cursor() as cursor:
            cursor.executemany(query, chunk)

    def close_connection(self):
        if self.connection:
            self.connection.close()

def main():
    db_manager = DatabaseManager(test_connection_params)
    db_manager.create_table_if_not_exists()

    data = [(f"Product {i}", f"Description for product {i}", json.dumps([i * 0.01, i * 0.02, i * 0.03, i * 0.04])) for i
            in range(1_000_000)]

    chunk_size = 10
    for i in range(0, len(data), chunk_size):
        db_manager.insert_rows(data[i:i + chunk_size])

    db_manager.close_connection()

我的疑问 #

• 为什么用with块管理游标（甚至手动关闭）都无法正确释放预编译语句？
• 为什么封装成类之后，原本正常的VECTOR字段插入逻辑又会触发预编译语句超限的错误？
• 有没有办法在不调整MariaDB的max_prepared_stmt_count配置的前提下，解决这个问题？

真心求各位大佬帮忙分析一下，谢谢了！

备注：内容来源于stack exchange，提问作者waveform_tinker