mysql-connector-python InterfaceError: Failed getting warnings when executing a query with multiple statements with get_warnings=True

Question

Using Python 3.7, I execute a query against a MySQL database, with multiple statements, with get_warnings enabled:

import mysql.connector
cnx = mysql.connector.connect(host='xxx',
                                user='xxx',
                                password='xxx',
                                database='xxx',
                                use_pure=False,
                                get_warnings=True)
# Test 1, works:
cur = cnx.cursor()
cur.execute('SELECT "a"+1')
for row in cur:
    print(row)
print(cur.fetchwarnings())
cur.close()

# Test 2, InterfaceError:
cur = cnx.cursor()
for rs in cur.execute('SELECT "a"+1; SELECT 2', multi=True):
    for row in rs:
        print(row)
    print(rs.fetchwarnings())

The first test executes a single statement, iterates over the cursor, fetches data, and finally prints warnings. Output as expected:

(1.0,)
[('Warning', 1292, "Truncated incorrect DOUBLE value: 'a'")]

The second test, (you can remove the first test altogether), will execute print(row) once, then an Exception happens. Output:

Traceback (most recent call last):
  File "C:\Program Files\Python37\lib\site-packages\mysql\connector\connection_cext.py", line 472, in cmd_query
    raw_as_string=raw_as_string)
_mysql_connector.MySQLInterfaceError: Commands out of sync; you can't run this command now

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "C:\Program Files\Python37\lib\site-packages\mysql\connector\cursor_cext.py", line 138, in _fetch_warnings
    _ = self._cnx.cmd_query("SHOW WARNINGS")
  File "C:\Program Files\Python37\lib\site-packages\mysql\connector\connection_cext.py", line 475, in cmd_query
    sqlstate=exc.sqlstate)
mysql.connector.errors.DatabaseError: 2014 (HY000): Commands out of sync; you can't run this command now

During handling of the above exception, another exception occurred:
....etc....

Did anyone encounter the same problem? How did you solve it? What am I doing wrong? Could this be a bug in the connector?

Other things I've tried:

If you set get_warnings to False, no error happens and fetchwarnings() returns None
If you remove the problem from the SQL code, no error happens and fetchwarnings() returns None
use_pure can be True or False, the only difference is a slightly different traceback
Using fetchall() instead of for row in rs gives the same result
Many other variations give the same error.

System:

Connector version is mysql-connector-python-8.0.17 but 8.0.16 has the same issue.
Python 3.7.3 (v3.7.3:ef4ec6ed12, Mar 25 2019, 22:22:05) [MSC v.1916 64 bit (AMD64)] on win32
MySQL 5.7

spencer7593 spencer7593 · Accepted Answer · 2019-08-09T14:16:17

The "Commands out of sync" is because MySQL client interface calls are performed in a wrong order. This is not a bug in the connector. This is expected behavior.

Executing that first SELECT returns a MySQL resultset.

Before the client issues another statement that returns a MySQL resultset, we have to do something with the resultset that is already returned. That is, there needs to be calls to either mysql_use_result and mysql_free_result, or a call to mysql_store_result. Once the client does that, then the client can execute another SQL statement that returns a result.

(Note that the execution of the MySQL SHOW WARNINGS statement returns a MySQL resultset.)

Again, this is expected behavior, as documented here:

https://dev.mysql.com/doc/refman/8.0/en/commands-out-of-sync.html

The references to mysql_free_result, mysql_store_result and mysql_use_result aren't specific to a Python interface; these reference the underlying library routines in the MySQL client code. e.g. https://dev.mysql.com/doc/refman/8.0/en/mysql-use-result.html

FOLLOWUP

I suspect the author of the MySQL Python connector didn't anticipate this use case, or if it was anticipated, the observed behavior was judged to be correct.

As far as avoiding the problem, I would avoid the use of the multii=True and do a separate execute for each SQL statement. Following the same pattern as in Test 1, we could add an outer loop to loop through the SQL statements

# Test 1.2
sqls = ['SELECT "a"+1', 'SELECT 2', ]
for sql in sqls:
    cur = cnx.cursor()
    cur.execute(sql)
    for row in cur:
        print(row)
    print(cur.fetchwarnings())
    cur.close()

Another option would be to avoid the call to the fetchwarnings. That is what is causing the SHOW WARNINGS statement to be executed (only after it first verifies that the count of warnings is greater than zero.) We can issue a SHOW WARNINGS statement separately, and loop through the results from that like it were the return from a SELECT.

# Test 1.3
cur = cnx.cursor()
for rs in cur.execute('SELECT "a"+1; SHOW WARNINGS; SELECT 2; SHOW WARNINGS', multi=True):
    for row in rs:
        print(row)
cur.close()

mysql-connector-python InterfaceError: Failed getting warnings when executing a query with multiple statements with get_warnings=True

1 Answers