启用get_warnings执行多语句查询触发InterfaceError问题求助

I've run into this exact issue before with the mysql-connector-python versions you're using (8.0.16/8.0.17) — this appears to be a bug in the connector's handling of warnings when using multi-statement queries. Let's break down what's happening and the workarounds you can use:

Root Cause #

When get_warnings=True is enabled, the connector automatically tries to fetch warnings immediately after executing a statement. However, when using multi=True to run multiple queries in one call, the connector's internal state gets confused: it tries to fetch warnings before fully consuming the current result set, leading to the "Commands out of sync" error. This conflict happens regardless of whether use_pure is set to True or False.

Workarounds #

1. Disable get_warnings for multi-statement queries #

If you don't need to capture warnings during this specific multi-statement execution, temporarily disable the setting before running the queries, then re-enable it if needed:

import mysql.connector
cnx = mysql.connector.connect(host='xxx', user='xxx', password='xxx', database='xxx', use_pure=False, get_warnings=True)

# Temporarily disable warnings for multi-statement query
cnx.get_warnings = False
cur = cnx.cursor()
for rs in cur.execute('SELECT "a"+1; SELECT 2', multi=True):
    for row in rs:
        print(row)
    print(rs.fetchwarnings())  # Will return None since get_warnings is disabled
cur.close()

# Re-enable warnings if needed for subsequent queries
cnx.get_warnings = True

2. Split multi-statement queries into separate execute() calls #

This avoids the sync issue entirely, and you can still capture warnings for each individual query:

cur = cnx.cursor()
# First query
cur.execute('SELECT "a"+1')
for row in cur:
    print(row)
print(cur.fetchwarnings())
# Second query
cur.execute('SELECT 2')
for row in cur:
    print(row)
print(cur.fetchwarnings())
cur.close()

3. Upgrade mysql-connector-python (if possible) #

Later versions of the connector (post-8.0.17) have addressed this warning-handling conflict with multi-statement queries. If your project allows, upgrading to the latest stable version might resolve the issue without needing workarounds.

Verification #

You already confirmed that disabling get_warnings or removing warning-triggering statements fixes the error — that aligns perfectly with the root cause we identified. The bug is specific to how the connector interleaves result set traversal and warning fetching in multi-statement mode.

内容的提问来源于stack exchange，提问作者Barend Scholtus