python mysql.connecotr raise InternalError("Unread result found")

Question

With:

pip install mysql-connector-python
import mysql.connector

I have a db with millions of rows, so the fetchall() method was causing some memory issues.

            db_cursor.execute(sql_query, sql_values)
            for row in db_cursor.fetchall():
                yield row

So i have now changed the code as follows. I am now iterating over the cursor as such:

            db_cursor.execute(sql_query, sql_values)
            for row in db_cursor:
                yield row

It seems to be working fine and the full db is not loaded in memory.

Now the problem is that while I'm iterating over the generator, I need to perform another query.

So to give you an idea, here is the flow.

Start iteration over the cursor.
For each row extract the row ID.

With that row id, perform another query. (and here is the problem, mysql.connector raises)

     raise InternalError("Unread result found")
 mysql.connector.errors.InternalError: Unread result found

I know this is because I haven't finished iterating over the cursor but the odd thing with that is that I'm not using the same cursor.

To perform the new query I use the active connection and create a new cursor:

db_cursor = self._db_connection.cursor(prepared=True, dictionary=True)

So my question is why is it raising the InternalError("Unread result found") even if i'm using a different cursor? Is it because I'm creating the new cursor from the same opened connection? Would I need to create a new connection too?

score 0 · Accepted Answer · answered Jul 15 '23 at 18:45

Since you plan to fetch million of rows I assume that you are using unbuffered cursors.

Unbuffered cursors don't read the entire result set, they fetch row by row from the network buffer. That means, you will not be able to send any command to the server unless all rows were fetched and/or the network buffer was cleared.

Opening a 2nd cursor doesn't make a difference: The 2nd cursor still uses the same connection with the same network buffer.

Alternative solutions:

Open a 2nd connection and a new cursor (there might be pitfalls with transactions)

Use a different driver which supports server side cursors. From my knowledge MariaDB Connector/Python is currently the only one. Server side read only cursors are supported in MySQL and MariaDB since 5.1.

import mariadb

....

cursor1 = self.connection.cursor(cursor_type=CURSOR.READ_ONLY)
cursor2 = self.connection.cursor(cursor_type=CURSOR.READ_ONLY)

cursor1.execute("SELECT * from t1")
row= cursor.fetchone()
cursor2.execute("SELECT * from t2 WHERE id=?", (row[id],))
...

As you said, with a second connection it worked perfectly! only creating a second cursor was not enough. Thanks for the clear explanation too. — 64rl0, Jul 15 '23 at 19:11

python mysql.connecotr raise InternalError("Unread result found")

1 Answers1