[Python] MariaDB Connector/Python - Prepared Statements Not Releasing

OS: Ubuntu 20.04.6 LTS

Python library mariadb versions: 1.1.10 and 1.1.12 (current)

MariaDB server versions: 10.3 and 11.7.1-rc

The following script raises the error mariadb.OperationalError: Can't create more than max_prepared_stmt_count statements (current value: 16382), which arises when the number of stored prepared statements in the backend database exceed a defined limit.

I could reproduce and would expect this error if I kept a list cursor objects, one per call and explicitly did not close the cursors.

However since I am using a with block to manage the cursor object, it should be closing (and indeed the error persists even when I explicity add cursor.close() to the for loop).

(The error occurs after 16382 calls of 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()


This error does not occur when using the mypysql connector with similar logic.

The error also does not occur when I swap the TEXT field for the new MariaDB 11.7.1-rc vector feature:

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()


However oddly, the error returns using the same logic that was just working, when I wrap the MariaDB part of the code into a Python Object:

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()

Continue reading...