Description:
When running multiple queries at once via cursor.execute while the connection has get_warnings=True, certain combinations of queries cause a "Commands out of sync; you can't run this command now" error even when results are being fetched correctly. This seems to happen when a warning is generated in the middle of the statements. It does not matter if the results are being handled via cursor.fetchsets, cursor.fetchall & cursor.nextset(), etc.
As an example, these three statements will generate a warning for the "DROP TABLE IF EXISTS..." portion assuming the table doesn't exist:
"SELECT TRUE; DROP TABLE IF EXISTS foo; SELECT FALSE;"
The next execute statement will receive an 'commands out of sync' error even if the results of each statement are properly handled. This is only true if the 'get_warnings' argument is True; if False, it works as expected.
Swapping the order of the commands so the statement generating the warning occurs last works fine with get_warnings on and off:
"SELECT TRUE; SELECT FALSE; DROP TABLE IF EXISTS foo;"
This allows the result sets to be handled properly and subsequent execute statements to proceed as normal.
This is using MySQL 8.0.32, Python 3.9.18, and mysql-connector-python 9.3.0.
How to repeat:
connection = mysql.connector.connect(
host=host,
user=user,
passwd=passwd,
database=db,
connection_timeout=10000,
allow_local_infile=True,
raise_on_warnings=False,
autocommit=True,
get_warnings=True,
)
cur = connection.cursor(
buffered=True,
)
# Works
sql = "SELECT TRUE; SELECT FALSE; DROP TABLE IF EXISTS foo;"
cur.execute(sql, map_results=True)
for statement, result_set in cur.fetchsets():
print(statement)
print(result_set)
# Does not work
sql = "SELECT TRUE; DROP TABLE IF EXISTS foo; SELECT FALSE;"
cur.execute(sql, map_results=True)
for statement, result_set in cur.fetchsets():
print(statement)
print(result_set)
Description: When running multiple queries at once via cursor.execute while the connection has get_warnings=True, certain combinations of queries cause a "Commands out of sync; you can't run this command now" error even when results are being fetched correctly. This seems to happen when a warning is generated in the middle of the statements. It does not matter if the results are being handled via cursor.fetchsets, cursor.fetchall & cursor.nextset(), etc. As an example, these three statements will generate a warning for the "DROP TABLE IF EXISTS..." portion assuming the table doesn't exist: "SELECT TRUE; DROP TABLE IF EXISTS foo; SELECT FALSE;" The next execute statement will receive an 'commands out of sync' error even if the results of each statement are properly handled. This is only true if the 'get_warnings' argument is True; if False, it works as expected. Swapping the order of the commands so the statement generating the warning occurs last works fine with get_warnings on and off: "SELECT TRUE; SELECT FALSE; DROP TABLE IF EXISTS foo;" This allows the result sets to be handled properly and subsequent execute statements to proceed as normal. This is using MySQL 8.0.32, Python 3.9.18, and mysql-connector-python 9.3.0. How to repeat: connection = mysql.connector.connect( host=host, user=user, passwd=passwd, database=db, connection_timeout=10000, allow_local_infile=True, raise_on_warnings=False, autocommit=True, get_warnings=True, ) cur = connection.cursor( buffered=True, ) # Works sql = "SELECT TRUE; SELECT FALSE; DROP TABLE IF EXISTS foo;" cur.execute(sql, map_results=True) for statement, result_set in cur.fetchsets(): print(statement) print(result_set) # Does not work sql = "SELECT TRUE; DROP TABLE IF EXISTS foo; SELECT FALSE;" cur.execute(sql, map_results=True) for statement, result_set in cur.fetchsets(): print(statement) print(result_set)